Insights into Company
← SQL learning path

Subqueries and Salary Ranking

Learn SQL from fundamentals to advanced interview and production-level database concepts through practical examples, questions and quizzes.

Complete SQL Learning & Interview Path

Learn SQL by working with realistic databases instead of memorising isolated commands. Every concept can be connected with practical tables such as students, employees, departments, countries, states, customers, products and orders.

SQL Fundamentals SELECT WHERE JOINs GROUP BY Subqueries Indexes Transactions Window Functions Interview Questions
Start Here

SQL through a real database

SQL is used to store, retrieve, modify and analyse structured data. Instead of learning commands independently, imagine that we are building a small company database.

Student table

A student database can contain identity, name, course and marks.

CREATE TABLE students ( id INT PRIMARY KEY, name VARCHAR(100), course VARCHAR(100), marks INT );

Employee table

Employee information can be connected with departments, salaries and managers.

CREATE TABLE employees ( id INT PRIMARY KEY, name VARCHAR(100), department_id INT, salary DECIMAL(12,2) );

Country / State table

Geographic data is useful for understanding relationships and JOINs.

CREATE TABLE states ( id INT PRIMARY KEY, name VARCHAR(100), country_id INT );

Customer / Orders

E-commerce data provides practical examples for aggregation, JOINs and transactions.

CREATE TABLE orders ( id INT PRIMARY KEY, customer_id INT, total DECIMAL(12,2), order_date DATE );
id name course marks
1 Rahul Java 85
2 Priya SQL 92
3 Amit React 76
SQL Learning Block 1

Questions 1 – 7

1
Question 34 of 7

Find employees earning more than the average salary.

Solution

SELECT * FROM employees WHERE salary > (SELECT AVG(salary) FROM employees);
Think before looking at the solution
  • What would the SQL query do?
  • Which rows will match?
  • Which columns will appear?
  • What happens when NULL values are involved?
  • Can the query be improved using an index?
Question 35 of 7

Find the employee with the highest salary.

Solution

SELECT * FROM employees WHERE salary = (SELECT MAX(salary) FROM employees);
Think before looking at the solution
  • What would the SQL query do?
  • Which rows will match?
  • Which columns will appear?
  • What happens when NULL values are involved?
  • Can the query be improved using an index?
Question 36 of 7

Find employees earning the maximum salary in their department.

Solution

SELECT * FROM employees e WHERE salary=(SELECT MAX(e2.salary) FROM employees e2 WHERE e2.department_id=e.department_id);
Think before looking at the solution
  • What would the SQL query do?
  • Which rows will match?
  • Which columns will appear?
  • What happens when NULL values are involved?
  • Can the query be improved using an index?
Question 37 of 7

What is a correlated subquery?

Solution

  • A subquery that depends on values from the current outer-query row.
Think before looking at the solution
  • What would the SQL query do?
  • Which rows will match?
  • Which columns will appear?
  • What happens when NULL values are involved?
  • Can the query be improved using an index?
Question 38 of 7

Find the second-highest salary.

Solution

SELECT MAX(salary) FROM employees WHERE salary < (SELECT MAX(salary) FROM employees);
Think before looking at the solution
  • What would the SQL query do?
  • Which rows will match?
  • Which columns will appear?
  • What happens when NULL values are involved?
  • Can the query be improved using an index?
Question 39 of 7

Find the third-highest salary.

Solution

SELECT MAX(salary) FROM employees WHERE salary < (SELECT MAX(salary) FROM employees WHERE salary < (SELECT MAX(salary) FROM employees));
Think before looking at the solution
  • What would the SQL query do?
  • Which rows will match?
  • Which columns will appear?
  • What happens when NULL values are involved?
  • Can the query be improved using an index?
Question 40 of 7

Find the second-highest DISTINCT salary using DENSE_RANK.

Solution

SELECT salary FROM (SELECT salary,DENSE_RANK() OVER(ORDER BY salary DESC) rnk FROM employees)x WHERE rnk=2;
Think before looking at the solution
  • What would the SQL query do?
  • Which rows will match?
  • Which columns will appear?
  • What happens when NULL values are involved?
  • Can the query be improved using an index?
Knowledge Check

Quiz — Final Learning Block

Complete the remaining questions before finishing this SQL learning path.

1. Find employees earning more than the average salary.
Show explanation 
SELECT * FROM employees WHERE salary > (SELECT AVG(salary) FROM employees);
2. Find the employee with the highest salary.
Show explanation 
SELECT * FROM employees WHERE salary = (SELECT MAX(salary) FROM employees);
3. Find employees earning the maximum salary in their department.
Show explanation 
SELECT * FROM employees e WHERE salary=(SELECT MAX(e2.salary) FROM employees e2 WHERE e2.department_id=e.department_id);
4. What is a correlated subquery?
Show explanation
  • A subquery that depends on values from the current outer-query row.
5. Find the second-highest salary.
Show explanation 
SELECT MAX(salary) FROM employees WHERE salary < (SELECT MAX(salary) FROM employees);
6. Find the third-highest salary.
Show explanation 
SELECT MAX(salary) FROM employees WHERE salary < (SELECT MAX(salary) FROM employees WHERE salary < (SELECT MAX(salary) FROM employees));
7. Find the second-highest DISTINCT salary using DENSE_RANK.
Show explanation 
SELECT salary FROM (SELECT salary,DENSE_RANK() OVER(ORDER BY salary DESC) rnk FROM employees)x WHERE rnk=2;