SQL Interview Questions and Answers

Question: Write a SQL query to find the Nth highest salary from a table.

Answer:

To find the Nth highest salary from a table, you can use the following approaches, depending on the SQL dialect you’re working with. Below is a common approach using a subquery and the LIMIT/OFFSET clause (for MySQL) or ROW_NUMBER() function (for SQL Server, PostgreSQL, or Oracle).

---

1. Using LIMIT and OFFSET (for MySQL)

Assuming you have a table called Employees with columns id and salary, and you want to find the Nth highest salary:

SELECT DISTINCT salary
FROM Employees
ORDER BY salary DESC
LIMIT 1 OFFSET N-1;

Explanation:

• ORDER BY salary DESC: Sorts the salaries in descending order (highest to lowest).
• LIMIT 1 OFFSET N-1: Fetches the Nth salary, where N-1 is the number of rows to skip. The LIMIT 1 ensures only one result is returned.

For example, to find the 3rd highest salary, you would use OFFSET 2.

---

2. Using ROW_NUMBER() (for SQL Server, PostgreSQL, Oracle)

If you’re using a database that supports window functions (like SQL Server, PostgreSQL, Oracle), you can use the ROW_NUMBER() function to assign a rank to each row and then filter out the row corresponding to the Nth highest salary.

WITH RankedSalaries AS (
    SELECT salary,
           ROW_NUMBER() OVER (ORDER BY salary DESC) AS rank
    FROM Employees
)
SELECT salary
FROM RankedSalaries
WHERE rank = N;

Explanation:

• ROW_NUMBER() OVER (ORDER BY salary DESC): Assigns a unique number to each salary based on its ranking (highest to lowest).
• WITH RankedSalaries AS (...): Creates a common table expression (CTE) that calculates the rank of each salary.
• WHERE rank = N: Filters out the row where the rank is equal to N.

For example, to find the 3rd highest salary, you would use WHERE rank = 3.

---

3. Using DENSE_RANK() or RANK() (for SQL Server, PostgreSQL, Oracle)

If you want to handle cases where there are ties (e.g., two employees having the same salary), you can use DENSE_RANK() or RANK() instead of ROW_NUMBER().

WITH RankedSalaries AS (
    SELECT salary,
           DENSE_RANK() OVER (ORDER BY salary DESC) AS rank
    FROM Employees
)
SELECT salary
FROM RankedSalaries
WHERE rank = N;

Explanation:

• DENSE_RANK() OVER (ORDER BY salary DESC): This assigns ranks in such a way that if two employees have the same salary, they will receive the same rank (i.e., no gaps between ranks).

---

4. Using Subquery (for all SQL databases)

A more general approach that works across various SQL databases, including MySQL, SQL Server, and others:

SELECT MAX(salary) AS NthHighestSalary
FROM Employees
WHERE salary NOT IN (
    SELECT salary
    FROM Employees
    ORDER BY salary DESC
    LIMIT N-1
);

Explanation:

• The subquery returns the top N-1 salaries from the Employees table, and the outer query selects the maximum salary from the remaining salaries (which corresponds to the Nth highest salary).

---

Example Query for Finding the 3rd Highest Salary:

For MySQL:

SELECT DISTINCT salary
FROM Employees
ORDER BY salary DESC
LIMIT 1 OFFSET 2;

For SQL Server, PostgreSQL, Oracle (using ROW_NUMBER()):

WITH RankedSalaries AS (
    SELECT salary,
           ROW_NUMBER() OVER (ORDER BY salary DESC) AS rank
    FROM Employees
)
SELECT salary
FROM RankedSalaries
WHERE rank = 3;

For Handling Ties with DENSE_RANK() (SQL Server, PostgreSQL, Oracle):

WITH RankedSalaries AS (
    SELECT salary,
           DENSE_RANK() OVER (ORDER BY salary DESC) AS rank
    FROM Employees
)
SELECT salary
FROM RankedSalaries
WHERE rank = 3;

---

Conclusion:

• MySQL: Use LIMIT and OFFSET.
• SQL Server, PostgreSQL, Oracle: Use ROW_NUMBER() or DENSE_RANK() to handle ranking.
• General approach: Use a subquery with NOT IN to exclude the first N-1 salaries and get the maximum from the remaining ones.

This should work for most use cases when you need to find the Nth highest salary from a table.

Question: What are subqueries in SQL?

Answer:

A subquery in SQL is a query nested inside another query. It can be used to perform operations that would require multiple steps in a single query, allowing for more complex queries and logic. Subqueries can return a single value, a list of values, or a table of results, and they are often used with comparison operators (such as =, <, >, IN, EXISTS, etc.) or in the FROM, SELECT, WHERE, or HAVING clauses.

---

Types of Subqueries:

1. Single-Row Subquery:

   • Returns a single value (a single row and column).
   • Commonly used with comparison operators like =, >, <, >=, <=, or <>.

   Example:

   SELECT employee_name, salary
   FROM employees
   WHERE salary = (SELECT MAX(salary) FROM employees);

   Explanation: The inner query (SELECT MAX(salary) FROM employees) returns the highest salary, and the outer query retrieves the employee with that salary.

2. Multiple-Row Subquery:

   • Returns multiple rows but a single column.
   • Commonly used with operators like IN or NOT IN.

   Example:

   SELECT employee_name, department
   FROM employees
   WHERE department IN (SELECT department FROM employees WHERE salary > 50000);

   Explanation: The inner query returns a list of departments where employees have a salary greater than 50,000, and the outer query selects employees who belong to those departments.

3. Multiple-Column Subquery:

   • Returns multiple rows and multiple columns.
   • Can be used with operators like =, IN, etc., when comparing multiple values.

   Example:

   SELECT employee_name, department, salary
   FROM employees
   WHERE (department, salary) IN (SELECT department, salary FROM employees WHERE experience > 5);

   Explanation: The inner query returns a set of (department, salary) pairs where employees have more than 5 years of experience, and the outer query retrieves the employees with the same department and salary.

4. Correlated Subquery:

   • A correlated subquery is a subquery that refers to columns in the outer query. Unlike a regular subquery, which is executed once, a correlated subquery is evaluated once for each row processed by the outer query.

   Example:

   SELECT employee_name, salary
   FROM employees outer
   WHERE salary > (SELECT AVG(salary) FROM employees inner WHERE outer.department = inner.department);

   Explanation: The inner query is correlated with the outer query by the department column. For each employee, the average salary for their department is calculated, and only employees with a salary greater than that average are selected.

---

Subquery Placement:

1. Subquery in the SELECT Clause:

   • Can be used to retrieve additional computed columns for each row.

   Example:

   SELECT employee_name, 
          (SELECT department_name FROM departments WHERE departments.department_id = employees.department_id) AS department_name
   FROM employees;

   Explanation: This subquery returns the department name for each employee by matching the department_id with the departments table.

2. Subquery in the FROM Clause:

   • Treats the subquery as a derived table (or inline view), which can be joined with other tables or used in further queries.

   Example:

   SELECT dept.department_name, emp.employee_name
   FROM (SELECT department_id, department_name FROM departments) AS dept
   JOIN employees AS emp ON dept.department_id = emp.department_id;

   Explanation: The subquery in the FROM clause acts as a derived table, providing a list of departments that is then joined with the employees table.

3. Subquery in the WHERE Clause:

   • Commonly used to filter results based on the results of another query.

   Example:

   SELECT employee_name, salary
   FROM employees
   WHERE department_id = (SELECT department_id FROM departments WHERE department_name = 'Sales');

   Explanation: The inner query returns the department_id for the ‘Sales’ department, which is then used to filter the employees in that department.

---

Important Notes about Subqueries:

1. Efficiency:

   • Subqueries can sometimes result in performance issues if not written efficiently, especially with correlated subqueries, as the inner query is executed multiple times for each row of the outer query.

2. Nesting Subqueries:

   • Subqueries can be nested within other subqueries. However, deep nesting can make queries harder to maintain and can impact performance.

3. Existence of Data:

   • The EXISTS and NOT EXISTS operators are often used with subqueries to check for the existence of rows returned by the inner query.

   Example:

   SELECT employee_name
   FROM employees e
   WHERE EXISTS (SELECT 1 FROM projects p WHERE p.employee_id = e.employee_id);

   Explanation: The query returns employees who are associated with at least one project.

4. Subquery with IN:

   • The IN operator is used when the subquery returns a list of values to compare against.

   Example:

   SELECT employee_name
   FROM employees
   WHERE department_id IN (SELECT department_id FROM departments WHERE location = 'New York');

   Explanation: This query returns employees working in departments located in New York.

---

Conclusion:

Subqueries are a powerful SQL feature that allows for more complex queries. They help in filtering data, performing calculations, and creating temporary result sets without the need for multiple queries. There are different types of subqueries, and understanding when and how to use them can significantly improve the flexibility and readability of your SQL queries.

Read More

If you can’t get enough from this article, Aihirely has plenty more related information, such as SQL interview questions, SQL interview experiences, and details about various SQL job positions. Click here to check it out.