50 SQL Query Interview Questions

Structured Query Language is the main instrument used when working with relational databases, so everyone who is connected with databases should know it. If you are new to SQL or just want to polish your knowledge, this article will guide you through 50 specifically designed SQL query interview questions from entry level to advanced level.

Sample table data

To better understand and execute SQL queries, let's start by examining the sample data used in this article. The following tables will serve as a basis for all queries and examples.

Employee table

Order table

Product table

beginner level

In this section, we will provide a guide to basic SQL queries that students new to SQL can easily understand. These basic queries can be used as a foundation to achieve a level of comfort with the most important aspects of SQL, including data selection, forms of data filtering, and rudimentary calculations.

P1. Write a query to display all records in the Employees table.

Answer:

SELECT * FROM Employees;

EmployeeID | FirstName | LastName | DepartmentID | Salary | HireDate    | ManagerID
----------------------------------------------------------------------------
1          | Alice    | Johnson  | 10           | 160000 | 2018-01-15  | 32
2          | Bob      | Smith    | 10           | 275000 | 2017-05-20  | 33
3          | Charlie  | Brown    | 10           | 190000 | 2015-09-30  | NULL
4          | David    | Williams | 10           | 355000 | 2019-07-11  | 35
5          | Eva      | Davis    | 10           | 265000 | 2020-03-25  | 32

P2. Get only the first and last name of employees.

Answer:

SELECT FirstName, LastName FROM Employees;

FirstName | LastName
--------------------
Alice     | Johnson
Bob       | Smith
Charlie   | Brown
David     | Williams
Eva       | Davis

P3. Retrieve the unique department IDs from the Employees table.

Answer:

SELECT DISTINCT DepartmentID FROM Employees;

DepartmentID
-------------
10

Q4. Search for employees with a salary greater than 60,000.

Answer:

SELECT * FROM Employees WHERE Salary > 60000;

EmployeeID | FirstName | LastName | DepartmentID | Salary | HireDate    | ManagerID
----------------------------------------------------------------------------
1          | Alice    | Johnson  | 10           | 160000 | 2018-01-15  | 32
2          | Bob      | Smith    | 10           | 275000 | 2017-05-20  | 33
3          | Charlie  | Brown    | 10           | 190000 | 2015-09-30  | NULL
4          | David    | Williams | 10           | 355000 | 2019-07-11  | 35
5          | Eva      | Davis    | 10           | 265000 | 2020-03-25  | 32

Q5. Write a query to display all orders placed after January 17, 2022.

Answer:

SELECT * FROM Orders WHERE OrderDate >= '2022-01-17';

OrderID | EmployeeID | ProductID | Quantity | OrderDate  
----------------------------------------------------------
1022    | 2          | 1         | 2        | 2022-01-16
1023    | 3          | 3         | 3        | 2022-01-17
1024    | 4          | 2         | 5        | 2022-01-18
1025    | 5          | 4         | 5        | 2022-01-19

Q6. Recovers all products with a price less than 300.

Answer:

SELECT * FROM Products WHERE Price < 300;

ProductID | ProductName | Price | Category
--------------------------------------------
203       | Office Chair | 150   | Furniture
204       | Desk         | 300   | Furniture
205       | Monitor      | 200   | Electronics

Q7. Find the total number of orders in the Orders table.

Answer:

SELECT COUNT(*) AS TotalOrders FROM Orders;

TotalOrders
------------
5

Q8. Get the details of the product named 'Laptop'.

Answer:

SELECT * FROM Products WHERE ProductName="Laptop";

ProductID | ProductName | Price | Category
--------------------------------------------
201       | Laptop      | 1200  | Electronics

Q9. Write a query to sort employees by hire date in ascending order.

Answer:

SELECT * FROM Employees ORDER BY HireDate ASC;

EmployeeID | FirstName | LastName | DepartmentID | Salary | HireDate    | ManagerID
----------------------------------------------------------------------------
3          | Charlie  | Brown    | 10           | 190000 | 2015-09-30  | NULL
2          | Bob      | Smith    | 10           | 275000 | 2017-05-20  | 33
1          | Alice    | Johnson  | 10           | 160000 | 2018-01-15  | 32
4          | David    | Williams | 10           | 355000 | 2019-07-11  | 35
5          | Eva      | Davis    | 10           | 265000 | 2020-03-25  | 32

Q10. Recovers the maximum price of products in the Electronics category.

Answer:

SELECT MAX(Price) AS MaxPrice FROM Products WHERE Category = 'Electronics';

MaxPrice
--------
1200

The next section of the article addresses the intermediate level of learning SQL by presenting more complete queries. You will continue joining tables, learn how to use functions to filter data and complex operations to better solve real-world operations.

Q11. Write a query to join the Employees and Orders tables to retrieve the names of the employees along with their orders.

Answer:

SELECT e.FirstName, e.LastName, o.OrderID, o.OrderDate  
FROM Employees e  
JOIN Orders o ON e.EmployeeID = o.EmployeeID;

FirstName | LastName | OrderID | OrderDate  
-------------------------------------------
Alice     | Johnson  | 1022    | 2022-01-16
Bob       | Smith    | 1023    | 2022-01-17
Charlie   | Brown    | 1024    | 2022-01-18
David     | Williams | 1025    | 2022-01-19

Q12. Calculate total salary by department.

Answer:

SELECT DepartmentID, SUM(Salary) AS TotalSalary  
FROM Employees  
GROUP BY DepartmentID;

DepartmentID | TotalSalary
-------------------------
10           | 1355000

Q13. Find the employees who do not have a manager.

Answer:

SELECT * FROM Employees WHERE ManagerID IS NULL;

EmployeeID | FirstName | LastName | DepartmentID | Salary | HireDate    | ManagerID
----------------------------------------------------------------------------
3          | Charlie  | Brown    | 10           | 190000 | 2015-09-30  | NULL

Q14. Write a query to display the average product price for each category.

Answer:

SELECT Category, AVG(Price) AS AvgPrice  
FROM Products  
GROUP BY Category;

Category    | AvgPrice
-----------------------
Electronics | 800
Furniture   | 216.67

Q15. Get the details of the top 3 highest paid employees.

Answer:

SELECT * FROM Employees  
ORDER BY Salary DESC  
LIMIT 3;

EmployeeID | FirstName | LastName | DepartmentID | Salary | HireDate    | ManagerID
----------------------------------------------------------------------------
4          | David    | Williams | 10           | 355000 | 2019-07-11  | 35
2          | Bob      | Smith    | 10           | 275000 | 2017-05-20  | 33
5          | Eva      | Davis    | 10           | 265000 | 2020-03-25  | 32

Q16. Retrieve the order details along with the product name.

Answer:

SELECT o.OrderID, o.Quantity, p.ProductName, p.Price  
FROM Orders o  
JOIN Products p ON o.ProductID = p.ProductID;

OrderID | Quantity | ProductName  | Price
-------------------------------------------
1022    | 2        | Laptop       | 1200
1023    | 3        | Office Chair | 150
1024    | 5        | Smartphone   | 800
1025    | 5        | Desk         | 300

Q17. Find the total quantity of products ordered for each product.

Answer:

SELECT ProductID, SUM(Quantity) AS TotalQuantity  
FROM Orders  
GROUP BY ProductID;

ProductID | TotalQuantity
--------------------------
1         | 2
2         | 8
3         | 3
4         | 5

Q18. Write a query to update the price of all products in the Furniture category by 10%.

Answer:

UPDATE Products  
SET Price = Price * 1.10  
WHERE Category = 'Furniture';

Q19. Delete all orders placed before January 17, 2022.

Answer:

DELETE FROM Orders WHERE OrderDate < '2022-01-17';

Q20. Look for employees whose name starts with 'A'.

Answer:

SELECT * FROM Employees WHERE FirstName LIKE 'A%';

EmployeeID | FirstName | LastName | DepartmentID | Salary | HireDate    | ManagerID
----------------------------------------------------------------------------
1          | Alice    | Johnson  | 10           | 160000 | 2018-01-15  | 32

Q21. Recover the number of employees hired each year.

Answer:

SELECT YEAR(HireDate) AS HireYear, COUNT(*) AS EmployeesHired  
FROM Employees  
GROUP BY YEAR(HireDate);

HireYear | EmployeesHired
-------------------------
2015     | 1
2017     | 1
2018     | 1
2019     | 1
2020     | 1

Q22. Enter a query to find employees who earn more than the average salary.

Answer:

SELECT * FROM Employees  
WHERE Salary > (SELECT AVG(Salary) FROM Employees);

EmployeeID | FirstName | LastName | DepartmentID | Salary | HireDate    | ManagerID
----------------------------------------------------------------------------
2          | Bob      | Smith    | 10           | 275000 | 2017-05-20  | 33
4          | David    | Williams | 10           | 355000 | 2019-07-11  | 35
5          | Eva      | Davis    | 10           | 265000 | 2020-03-25  | 32

Q23. Shows the top 3 products with the highest total quantity sold.

Answer:

SELECT p.ProductName, SUM(o.Quantity) AS TotalQuantity  
FROM Orders o  
JOIN Products p ON o.ProductID = p.ProductID  
GROUP BY p.ProductName  
ORDER BY TotalQuantity DESC  
LIMIT 3;

ProductName | TotalQuantity
----------------------------
Smartphone  | 8
Desk        | 5
Office Chair| 3

Q24. Recover employees who have not placed any orders.

Answer:

SELECT * FROM Employees  
WHERE EmployeeID NOT IN (SELECT DISTINCT EmployeeID FROM Orders);

EmployeeID | FirstName | LastName | DepartmentID | Salary | HireDate    | ManagerID
----------------------------------------------------------------------------
3          | Charlie  | Brown    | 10           | 190000 | 2015-09-30  | NULL

Q25. Enter a query to find the most recently hired employee.

Answer:

SELECT * FROM Employees  
ORDER BY HireDate DESC  
LIMIT 1;

EmployeeID | FirstName | LastName | DepartmentID | Salary | HireDate    | ManagerID
----------------------------------------------------------------------------
5          | Eva      | Davis    | 10           | 265000 | 2020-03-25  | 32

Q26. Shows all employees along with the total number of orders they have handled.

Answer:

SELECT e.EmployeeID, e.FirstName, COUNT(o.OrderID) AS TotalOrders  
FROM Employees e  
LEFT JOIN Orders o ON e.EmployeeID = o.EmployeeID  
GROUP BY e.EmployeeID, e.FirstName;

Q27. Get details of products whose total sales exceed $10,000.

Answer:

SELECT p.ProductName, SUM(o.Quantity * p.Price) AS TotalSales  
FROM Orders o  
JOIN Products p ON o.ProductID = p.ProductID  
GROUP BY p.ProductName  
HAVING TotalSales > 10000;

Q28. Find employees who joined the company the same year as their manager.

Answer:

SELECT e.FirstName AS EmployeeName, m.FirstName AS ManagerName  
FROM Employees e  
JOIN Employees m ON e.ManagerID = m.EmployeeID  
WHERE YEAR(e.HireDate) = YEAR(m.HireDate);

Q29. Retrieves the names of the highest-paid employees in each department.

Answer:

SELECT DepartmentID, FirstName, LastName, Salary  
FROM Employees  
WHERE (DepartmentID, Salary) IN (  
    SELECT DepartmentID, MAX(Salary)  
    FROM Employees  
    GROUP BY DepartmentID  
);

P30. Write a query to get the total revenue generated by each employee.

Answer:

SELECT e.FirstName, e.LastName, SUM(o.Quantity * p.Price) AS TotalRevenue  
FROM Employees e  
JOIN Orders o ON e.EmployeeID = o.EmployeeID  
JOIN Products p ON o.ProductID = p.ProductID  
GROUP BY e.EmployeeID, e.FirstName, e.LastName;

Advanced level

At the advanced level, we deal with complex synthesis of SQL query statements. This section is dedicated to describing more complex operations such as sorting, window functions, basic subqueries, and optimization methods to enable you to overcome complicated tasks in data analysis.

Q31. Write a query to find employees who earn more than their manager.

Answer:

SELECT e.FirstName AS EmployeeName, m.FirstName AS ManagerName  
FROM Employees e  
JOIN Employees m ON e.ManagerID = m.EmployeeID  
WHERE e.Salary > m.Salary;

Q32. Retrieve the second highest salary from the Employees table.

Answer:

SELECT MAX(Salary) AS SecondHighestSalary  
FROM Employees  
WHERE Salary < (SELECT MAX(Salary) FROM Employees);

SecondHighestSalary
75000

P33. List departments that do not have employees assigned to them.

Answer:

SELECT * FROM Departments  
WHERE DepartmentID NOT IN (SELECT DISTINCT DepartmentID FROM Employees);

Q34. Write a query to create a view that displays the names of employees and the names of their departments.

Answer:

CREATE VIEW EmployeeDepartmentView AS  
SELECT e.FirstName, e.LastName, d.DepartmentName  
FROM Employees e  
JOIN Departments d ON e.DepartmentID = d.DepartmentID;

Q35. Get the names of employees who have placed more than 10 orders.

Answer:

SELECT e.FirstName, e.LastName  
FROM Employees e  
JOIN Orders o ON e.EmployeeID = o.EmployeeID  
GROUP BY e.EmployeeID, e.FirstName, e.LastName  
HAVING COUNT(o.OrderID) > 10;

Q36. Write a query to classify employees based on their salary within each department.

Answer:

SELECT EmployeeID, FirstName, DepartmentID, Salary,  
       RANK() OVER (PARTITION BY DepartmentID ORDER BY Salary DESC) AS Rank  
FROM Employees;

Q37. Recover the accumulated sales of each product.

Answer:

SELECT ProductID, ProductName,  
       SUM(SUM(Quantity * Price)) OVER (ORDER BY ProductID) AS CumulativeSales  
FROM Products p  
JOIN Orders o ON p.ProductID = o.ProductID  
GROUP BY ProductID, ProductName;

Q38. Identify the department with the highest total salary expense.

Answer:

SELECT DepartmentID, SUM(Salary) AS TotalExpenditure  
FROM Employees  
GROUP BY DepartmentID  
ORDER BY TotalExpenditure DESC  
LIMIT 1;

Q39. Write a query to find the percentage contribution of each product to total sales.

Answer:

SELECT p.ProductName,  
       (SUM(o.Quantity * p.Price) * 100.0 /  
        (SELECT SUM(Quantity * Price) FROM Orders o JOIN Products p ON o.ProductID = p.ProductID)) AS ContributionPercentage  
FROM Orders o  
JOIN Products p ON o.ProductID = p.ProductID  
GROUP BY p.ProductName;

P40. Find employees who have the same manager and make more than $70,000.

Answer:

SELECT *  
FROM Employees e1  
WHERE ManagerID IS NOT NULL  
AND Salary > 70000  
AND ManagerID IN (  
    SELECT ManagerID FROM Employees e2 WHERE e1.ManagerID = e2.ManagerID  
);

Q41. Write a query to detect duplicate rows in the Orders table.

Answer:

SELECT EmployeeID, ProductID, OrderDate, COUNT(*) AS DuplicateCount  
FROM Orders  
GROUP BY EmployeeID, ProductID, OrderDate  
HAVING COUNT(*) > 1;

Q42. Get details of orders placed on the same day by multiple employees.

Answer:

SELECT OrderDate, COUNT(DISTINCT EmployeeID) AS EmployeeCount  
FROM Orders  
GROUP BY OrderDate  
HAVING EmployeeCount > 1;

Q43. Create a stored procedure to update product prices based on category.

Answer:

DELIMITER $$  
CREATE PROCEDURE UpdatePriceByCategory(IN category_name VARCHAR(50), IN price_factor DECIMAL(5, 2))  
BEGIN  
    UPDATE Products  
    SET Price = Price * price_factor  
    WHERE Category = category_name;  
END$$  
DELIMITER ;

Q44. Write a query to calculate the lead and lag order dates for each employee.

Answer:

SELECT EmployeeID, OrderID, OrderDate,  
       LAG(OrderDate) OVER (PARTITION BY EmployeeID ORDER BY OrderDate) AS PreviousOrderDate,  
       LEAD(OrderDate) OVER (PARTITION BY EmployeeID ORDER BY OrderDate) AS NextOrderDate  
FROM Orders;

Q45. Identify products that have not been ordered.

Answer:

SELECT * FROM Products  
WHERE ProductID NOT IN (SELECT DISTINCT ProductID FROM Orders);

Q46. Write a query to find employees whose total order quantity is between 50 and 100.

Answer:

SELECT e.FirstName, e.LastName, SUM(o.Quantity) AS TotalQuantity  
FROM Employees e  
JOIN Orders o ON e.EmployeeID = o.EmployeeID  
GROUP BY e.EmployeeID, e.FirstName, e.LastName  
HAVING TotalQuantity BETWEEN 50 AND 100;

Q47. Get the second highest ordered quantity for each product.

Answer:

SELECT ProductID, MAX(Quantity) AS SecondHighestQuantity  
FROM Orders  
WHERE Quantity < (SELECT MAX(Quantity) FROM Orders WHERE Orders.ProductID = ProductID)  
GROUP BY ProductID;

Q48. Find the minimum and maximum order quantities for each employee.

Answer:

SELECT EmployeeID, MIN(Quantity) AS MinQuantity, MAX(Quantity) AS MaxQuantity  
FROM Orders  
GROUP BY EmployeeID;

Q49. Write a query to divide employee salaries into quartiles.

Answer:

SELECT EmployeeID, FirstName, Salary,  
       NTILE(4) OVER (ORDER BY Salary) AS SalaryQuartile  
FROM Employees;

Q50. Create a temporary table for orders with high revenue (more than $5000)

Answer:

CREATE TEMPORARY TABLE HighRevenueOrders AS  
SELECT o.OrderID, o.Quantity, p.Price, (o.Quantity * p.Price) AS Revenue  
FROM Orders o  
JOIN Products p ON o.ProductID = p.ProductID  
WHERE (o.Quantity * p.Price) > 5000;

Conclusion

Mastering SQL query interview questions provides a solid foundation for efficient data management and analysis. By practicing these SQL query interview questions, you will improve your ability to work with real-world databases, making it easier to effectively retrieve, manipulate, and interpret data. Whether you're just getting started or honing your skills, SQL remains an essential tool for any data professional, and understanding its various capabilities will unlock countless opportunities for problem solving and insight generation.