50 SQL Question Interview Questions -

Structured Question Language , is the first instrument used when working with relational databases, so everybody who’s linked with databases ought to realize it. Whether or not you might be new to SQL or simply need to polish up your information, this text guides you thru 50 particularly designed SQL Question Interview Questions from entry degree to superior degree.

Pattern Desk Information

To higher perceive and execute the SQL queries, let’s start by analyzing the pattern information used on this article. The next tables will function the premise for all of the queries and examples.

Workers Desk

EmployeeID	FirstName	LastName	DepartmentID	Wage	HireDate	ManagerID
1	Alice	Johnson	101	60000	2018-01-15	3
2	Bob	Smith	102	75000	2017-05-20	3
3	Charlie	Brown	101	90000	2015-09-30	NULL
4	David	Williams	103	55000	2019-07-11	3
5	Eva	Davis	102	65000	2020-03-25	2

Orders Desk

OrderID	EmployeeID	ProductID	Amount	OrderDate
1001	1	201	10	2022-01-15
1002	2	202	5	2022-01-16
1003	3	203	20	2022-01-17
1004	4	202	15	2022-01-18
1005	5	204	25	2022-01-19

Merchandise Desk

ProductID	ProductName	Value	Class
201	Laptop computer	1200	Electronics
202	Smartphone	800	Electronics
203	Workplace Chair	150	Furnishings
204	Desk	300	Furnishings
205	Monitor	200	Electronics

Newbie Degree

On this part, allow us to present a tenet of primary SQL queries that college students who’re new to SQL can simply perceive. These primary queries can be utilized as the inspiration to attaining a consolation degree with crucial facets of SQL, together with choice of information, types of information filtering and rudimentary computations.

Q1. Write a question to show all information from the Workers desk.

Reply:

SELECT * FROM Workers;

EmployeeID | FirstName | LastName | DepartmentID | Wage | 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

Q2. Fetch solely the FirstName and LastName of staff.

Reply:

SELECT FirstName, LastName FROM Workers;

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

Q3. Retrieve the distinctive division IDs from the Workers desk.

Reply:

SELECT DISTINCT DepartmentID FROM Workers;

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

This autumn. Fetch staff with a wage better than 60,000.

Reply:

SELECT * FROM Workers WHERE Wage > 60000;

EmployeeID | FirstName | LastName | DepartmentID | Wage | 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 question to show all orders positioned on or after January 17, 2022.

Reply:

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

OrderID | EmployeeID | ProductID | Amount | 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. Retrieve all merchandise with a value lower than 300.

Reply:

SELECT * FROM Merchandise WHERE Value < 300;

ProductID | ProductName | Value | Class
--------------------------------------------
203       | Workplace Chair | 150   | Furnishings
204       | Desk         | 300   | Furnishings
205       | Monitor      | 200   | Electronics

Q7. Discover the overall variety of orders within the Orders desk.

Reply:

SELECT COUNT(*) AS TotalOrders FROM Orders;

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

Q8. Fetch the main points of the product named ‘Laptop computer’.

Reply:

SELECT * FROM Merchandise WHERE ProductName="Laptop computer";

ProductID | ProductName | Value | Class
--------------------------------------------
201       | Laptop computer      | 1200  | Electronics

Q9. Write a question to kind staff by their HireDate in ascending order.

Reply:

SELECT * FROM Workers ORDER BY HireDate ASC;

EmployeeID | FirstName | LastName | DepartmentID | Wage | 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. Retrieve the utmost value of merchandise within the Electronics class.

Reply:

SELECT MAX(Value) AS MaxPrice FROM Merchandise WHERE Class = 'Electronics';

MaxPrice
--------
1200

The next part of the article offers with the intermediate degree in Studying SQL by presenting extra complete queries. You’ll proceed with becoming a member of tables, discover ways to use capabilities to filter information in addition to complicated operations to unravel real-world operations higher.

Q11. Write a question to affix Workers and Orders tables to fetch worker names together with their orders.

Reply:

SELECT e.FirstName, e.LastName, o.OrderID, o.OrderDate  
FROM Workers 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 the overall wage by division.

Reply:

SELECT DepartmentID, SUM(Wage) AS TotalSalary  
FROM Workers  
GROUP BY DepartmentID;

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

Q13. Discover the staff who don’t have a supervisor.

Reply:

SELECT * FROM Workers WHERE ManagerID IS NULL;

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

Q14. Write a question to show the typical product value for every class.

Reply:

SELECT Class, AVG(Value) AS AvgPrice  
FROM Merchandise  
GROUP BY Class;

Class    | AvgPrice
-----------------------
Electronics | 800
Furnishings   | 216.67

Q15. Fetch the main points of the highest 3 highest-paid staff.

Reply:

SELECT * FROM Workers  
ORDER BY Wage DESC  
LIMIT 3;

EmployeeID | FirstName | LastName | DepartmentID | Wage | 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 particulars together with the product identify.

Reply:

SELECT o.OrderID, o.Amount, p.ProductName, p.Value  
FROM Orders o  
JOIN Merchandise p ON o.ProductID = p.ProductID;

OrderID | Amount | ProductName  | Value
-------------------------------------------
1022    | 2        | Laptop computer       | 1200
1023    | 3        | Workplace Chair | 150
1024    | 5        | Smartphone   | 800
1025    | 5        | Desk         | 300

Q17. Discover the overall amount of merchandise ordered for every product.

Reply:

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

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

Q18. Write a question to replace the worth of all Furnishings class merchandise by 10%.

Reply:

UPDATE Merchandise  
SET Value = Value * 1.10  
WHERE Class = 'Furnishings';

Q19. Delete all orders positioned earlier than January 17, 2022.

Reply:

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

Q20. Fetch staff whose first identify begins with ‘A’.

Reply:

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

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

Q21. Retrieve the variety of staff employed annually.

Reply:

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

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

Q22. Write a question to fetch staff incomes greater than the typical wage.

Reply:

SELECT * FROM Workers  
WHERE Wage > (SELECT AVG(Wage) FROM Workers);

EmployeeID | FirstName | LastName | DepartmentID | Wage | 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. Show the highest 3 merchandise with the best complete amount offered.

Reply:

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

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

Q24. Retrieve the staff who haven’t positioned any orders.

Reply:

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

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

Q25. Write a question to fetch essentially the most just lately employed worker.

Reply:

SELECT * FROM Workers  
ORDER BY HireDate DESC  
LIMIT 1;

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

Q26. Show all staff together with the overall variety of orders they’ve dealt with.

Reply:

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

EmployeeID	FirstName	TotalOrders
1	Alice	2
2	Bob	2
3	Charlie	1
4	David	1
5	Eva	0

Q27. Fetch product particulars for which complete gross sales exceed $10,000.

Reply:

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

ProductName	TotalSales
Laptop computer	24000

Q28. Discover staff who joined the corporate in the identical yr as their supervisor.

Reply:

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

EmployeeName	ManagerName
Alice	Bob

Q29. Retrieve the worker names with the best wage in every division.

Reply:

SELECT DepartmentID, FirstName, LastName, Wage  
FROM Workers  
WHERE (DepartmentID, Wage) IN (  
    SELECT DepartmentID, MAX(Wage)  
    FROM Workers  
    GROUP BY DepartmentID  
);

DepartmentID	FirstName	LastName	Wage
1	Alice	Johnson	160000
2	Bob	Smith	75000
3	David	Williams	55000

Q30. Write a question to fetch the overall income generated by every worker.

Reply:

SELECT e.FirstName, e.LastName, SUM(o.Amount * p.Value) AS TotalRevenue  
FROM Workers e  
JOIN Orders o ON e.EmployeeID = o.EmployeeID  
JOIN Merchandise p ON o.ProductID = p.ProductID  
GROUP BY e.EmployeeID, e.FirstName, e.LastName;

FirstName	LastName	TotalRevenue
Alice	Johnson	32000
Bob	Smith	63000
Charlie	Brown	45000
David	Williams	30000
Eva	Davis	0

Superior Degree

Within the superior degree, we take care of complicated synthesis of SQL question statements. This part is dedicated to the outline of extra complicated operations like rating, window capabilities, primary subqueries, and optimization strategies to permit you overcoming difficult duties in information evaluation.

Q31. Write a question to fetch staff incomes greater than their supervisor.

Reply:

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

EmployeeName	ManagerName
Alice	Bob

Q32. Retrieve the second highest wage from the Workers desk.

Reply:

SELECT MAX(Wage) AS SecondHighestSalary  
FROM Workers  
WHERE Wage < (SELECT MAX(Wage) FROM Workers);

SecondHighestSalary
75000

Q33. Listing the departments with no staff assigned.

Reply:

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

DepartmentID	DepartmentName
4	Advertising and marketing

Q34. Write a question to create a view exhibiting worker names and their division names.

Reply:

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

FirstName	LastName	DepartmentName
Alice	Johnson	IT
Bob	Smith	Gross sales
Charlie	Brown	IT
David	Williams	HR
Eva	Davis	Gross sales

Q35. Fetch the names of staff who’ve positioned greater than 10 orders.

Reply:

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

FirstName	LastName
Alice	Johnson
Bob	Smith

Q36. Write a question to rank staff primarily based on their wage inside every division.

Reply:

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

EmployeeID	FirstName	DepartmentID	Wage	Rank
1	Alice	1	160000	1
3	Charlie	1	190000	2
2	Bob	2	75000	1
4	David	3	55000	1
5	Eva	2	65000	2

Q37. Retrieve the cumulative gross sales for every product.

Reply:

SELECT ProductID, ProductName,  
       SUM(SUM(Amount * Value)) OVER (ORDER BY ProductID) AS CumulativeSales  
FROM Merchandise p  
JOIN Orders o ON p.ProductID = o.ProductID  
GROUP BY ProductID, ProductName;

ProductID	ProductName	CumulativeSales
201	Laptop computer	24000
202	Smartphone	32000
203	Workplace Chair	1500
204	Desk	3000
205	Monitor	1500

Q38. Determine the division with the best complete wage expenditure.

Reply:

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

DepartmentID	TotalExpenditure
1	450000

Q39. Write a question to seek out the share contribution of every product to complete gross sales.

Reply:

SELECT p.ProductName,  
       (SUM(o.Amount * p.Value) * 100.0 /  
        (SELECT SUM(Amount * Value) FROM Orders o JOIN Merchandise p ON o.ProductID = p.ProductID)) AS ContributionPercentage  
FROM Orders o  
JOIN Merchandise p ON o.ProductID = p.ProductID  
GROUP BY p.ProductName;

ProductName	ContributionPercentage
Laptop computer	48.00
Smartphone	32.00
Workplace Chair	4.00
Desk	8.00
Monitor	8.00

Q40. Discover staff who’ve the identical supervisor and earn greater than $70,000.

Reply:

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

EmployeeID	FirstName	LastName	Wage	ManagerID
1	Alice	Johnson	160000	32
2	Bob	Smith	75000	32

Q41. Write a question to detect duplicate rows within the Orders desk.

Reply:

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

EmployeeID	ProductID	OrderDate	DuplicateCount
1	201	2022-01-15	2

Q42. Fetch the main points of orders positioned on the identical day by a number of staff.

Reply:

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

OrderDate	EmployeeCount
2022-01-15	2
2022-01-16	2
2022-01-17	1

Q43. Create a saved process to replace product costs primarily based on class.

Reply:

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

Q44. Write a question to calculate the lead and lag so as dates for every worker.

Reply:

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;

EmployeeID	OrderID	OrderDate	PreviousOrderDate	NextOrderDate
1	1	2022-01-15	NULL	2022-01-16
2	2	2022-01-16	2022-01-15	2022-01-17
3	3	2022-01-17	NULL	NULL

Q45. Determine the merchandise that haven’t been ordered.

Reply:

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

ProductID	ProductName
204	Desk
205	Monitor

Q46. Write a question to fetch staff whose complete order amount is between 50 and 100.

Reply:

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

FirstName	LastName	TotalQuantity
Bob	Smith	60

Q47. Fetch the second-highest amount ordered for every product.

Reply:

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

ProductID	SecondHighestQuantity
201	20
202	30
203	10

Q48. Discover the minimal and most order portions for every worker.

Reply:

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

EmployeeID	MinQuantity	MaxQuantity
1	10	20
2	20	40
3	10	10

Q49. Write a question to separate worker salaries into quartiles.

Reply:

SELECT EmployeeID, FirstName, Wage,  
       NTILE(4) OVER (ORDER BY Wage) AS SalaryQuartile  
FROM Workers;

EmployeeID	FirstName	Wage	SalaryQuartile
1	Alice	160000	4
2	Bob	75000	3
3	Charlie	190000	4
4	David	55000	2
5	Eva	65000	2

Q50. Create a short lived desk for orders with excessive income (better than $5000)

Reply:

CREATE TEMPORARY TABLE HighRevenueOrders AS  
SELECT o.OrderID, o.Amount, p.Value, (o.Amount * p.Value) AS Income  
FROM Orders o  
JOIN Merchandise p ON o.ProductID = p.ProductID  
WHERE (o.Amount * p.Value) > 5000;

OrderID	Amount	Value	Income
1	10	1200	12000
2	25	800	20000

Conclusion

Mastering SQL Question Interview Questions offers a strong basis for environment friendly information administration and evaluation. By training these SQL Question Interview Questions, you improve your means to work with real-world databases, making it simpler to retrieve, manipulate, and interpret information successfully. Whether or not you’re simply beginning or refining your expertise, SQL stays a vital device for any information skilled, and understanding its various capabilities will unlock numerous alternatives for problem-solving and perception era.

My identify is Ayushi Trivedi. I’m a B. Tech graduate. I’ve 3 years of expertise working as an educator and content material editor. I’ve labored with varied python libraries, like numpy, pandas, seaborn, matplotlib, scikit, imblearn, linear regression and plenty of extra. I’m additionally an writer. My first e book named #turning25 has been revealed and is on the market on amazon and flipkart. Right here, I’m technical content material editor at Analytics Vidhya. I really feel proud and glad to be AVian. I’ve an important group to work with. I like constructing the bridge between the expertise and the learner.