SQL (Structured Query Language) is a fundamental skill for any data professional. It’s widely used for managing and querying relational databases. During technical interviews, candidates often face tricky SQL query questions designed to test their problem-solving skills and understanding of SQL concepts. This blog will cover some of the top tricky SQL Top SQL Tricky Query Interview Questions and Answers provide detailed answers to help you prepare for your next interview.
Finding the Second Highest Salary
One of the classic tricky questions in SQL interviews is finding the second highest salary from an employee table. The challenge lies in ensuring that you get the correct result even if there are duplicate salaries.
Question: Given a table Employees with columns Id, Name, and Salary, write a query to find the second highest salary.
Answer:
SELECT MAX(Salary) AS SecondHighestSalary
FROM Employees
WHERE Salary < (SELECT MAX(Salary) FROM Employees);
Explanation: This query first identifies the maximum salary and then finds the highest salary less than the maximum, which is the second highest. The subquery (SELECT MAX(Salary) FROM Employees) finds the highest salary, and the main query selects the maximum salary that is less than this value.
Finding Employees with Duplicate Salaries
This question tests the ability to identify duplicate entries based on a specific column.
Question: Write a query to find all employees who have the same salary.
Answer:
SELECT Salary, COUNT(*)
FROM Employees
GROUP BY Salary
HAVING COUNT(*) > 1;
Explanation: The GROUP BY clause groups the rows by the Salary column. The HAVING COUNT(*) > 1 condition filters the groups to include only those with more than one employee, indicating duplicate salaries.
Top N Records per Group
Interviewers often test your ability to work with groups of data. This question requires you to find the top N records within each group.
Question: Given a table Sales with columns Id, ProductId, SalesAmount, write a query to find the top 3 highest sales amounts for each product.
Answer:
WITH RankedSales AS (
SELECT
ProductId,
SalesAmount,
RANK() OVER (PARTITION BY ProductId ORDER BY SalesAmount DESC) AS Rank
FROM Sales
)
SELECT ProductId, SalesAmount
FROM RankedSales
WHERE Rank <= 3;
Explanation: The RANK() OVER (PARTITION BY ProductId ORDER BY SalesAmount DESC) window function assigns a rank to each sale within its product group. The main query selects sales records where the rank is 3 or less, indicating the top 3 sales for each product.
Finding Missing Numbers in a Sequence
This question assesses your ability to work with sequences and identify missing elements.
Question: Given a table Numbers with a single column Number, write a query to find missing numbers in the sequence from 1 to the maximum number in the table.
Answer:
SELECT n.Number + 1 AS MissingNumber
FROM Numbers n
LEFT JOIN Numbers n2 ON n.Number + 1 = n2.Number
WHERE n2.Number IS NULL;
Explanation: The query performs a self-join on the table, matching each number with the next number in the sequence. The LEFT JOIN ensures that even if there is no matching number, the row is included in the result set. The WHERE n2.Number IS NULL condition filters out rows where the next number exists, leaving only the missing numbers.
Recommended to Read Also: Quality assurance certification
Finding Common Elements in Two Tables
Another common SQL problem involves finding common elements across multiple tables.
Question: Given two tables, TableA and TableB, with a common column Value, write a query to find the common values.
Answer:
SELECT Value
FROM TableA
INTERSECT
SELECT Value
FROM TableB;
Explanation: The INTERSECT operation returns the common rows from both TableA and TableB based on the Value column.
Pivot Table Query
Pivoting data, or transforming rows into columns, is a common task in SQL.
Question: Given a table SalesData with columns Year, Quarter, and Revenue, write a query to pivot the data so that each quarter becomes a column, and the revenue is displayed accordingly.
Answer:
SELECT
Year,
SUM(CASE WHEN Quarter = 'Q1' THEN Revenue ELSE 0 END) AS Q1,
SUM(CASE WHEN Quarter = 'Q2' THEN Revenue ELSE 0 END) AS Q2,
SUM(CASE WHEN Quarter = 'Q3' THEN Revenue ELSE 0 END) AS Q3,
SUM(CASE WHEN Quarter = 'Q4' THEN Revenue ELSE 0 END) AS Q4
FROM SalesData
GROUP BY Year;
Explanation: The CASE statement inside the SUM function conditionally sums the revenue for each quarter, effectively pivoting the data. The GROUP BY clause groups the data by year.
Finding the Nth Highest Salary
Similar to finding the second highest salary, this question can be generalized to find the Nth highest salary.
Question: Write a query to find the Nth highest salary from the Employees table.
Answer:
SELECT DISTINCT Salary
FROM Employees
ORDER BY Salary DESC
OFFSET N-1 ROWS
FETCH NEXT 1 ROW ONLY;
Explanation: This query uses the OFFSET and FETCH clauses. The OFFSET N-1 ROWS skips the top N-1 highest salaries, and FETCH NEXT 1 ROW ONLY returns the Nth highest salary. Replace N with the desired rank.
Self-Join to Find Managers and Their Employees
This question tests the understanding of self-joins and hierarchical data.
Question: Given a table Employees with columns Id, Name, ManagerId, write a query to list all employees along with their managers.
Answer:
SELECT e.Name AS Employee, m.Name AS Manager
FROM Employees e
LEFT JOIN Employees m ON e.ManagerId = m.Id;
Explanation: The query performs a self-join on the Employees table, matching each employee with their manager based on the ManagerId. The LEFT JOIN ensures that employees without a manager (such as the CEO) are still included in the result.
Recommended to Read Also: Online courses on quality assurance
Cumulative Sum of Sales
This question involves calculating a cumulative sum, a common requirement in reporting.
Question: Given a table Sales with columns Date and Amount, write a query to calculate the cumulative sum of sales amount by date.
Answer:
SELECT
Date,
Amount,
SUM(Amount) OVER (ORDER BY Date) AS CumulativeAmount
FROM Sales;
Explanation: The SUM(Amount) OVER (ORDER BY Date) window function calculates the cumulative sum of the sales amount, ordered by date. This provides a running total for each date.
Finding Employees Without Projects
Finally, identifying records that do not have corresponding entries in another table is a common SQL task.
Question: Given two tables, Employees and Projects, with Employees.Id and Projects.EmployeeId, write a query to find employees who are not assigned to any projects.
Answer:
SELECT e.Name
FROM Employees e
LEFT JOIN Projects p ON e.Id = p.EmployeeId
WHERE p.EmployeeId IS NULL;
Explanation: The LEFT JOIN ensures that all employees are included in the result set, even if they do not have corresponding entries in the Projects table. The WHERE p.EmployeeId IS NULL condition filters out employees who are assigned to projects, leaving only those who are not.
Conclusion
Tricky SQL query questions can be challenging, but with practice, you can master the necessary skills to tackle them. The questions covered in this blog represent some of the most common and challenging scenarios you might encounter in an interview. Understanding the logic behind these queries and practicing similar problems will help you develop a strong foundation in SQL, making you a more confident and capable candidate. Good luck with your interview preparation!
5 Responses