Structured Question Language , is the first instrument used when working with relational databases, so everybody who’s linked with databases ought to understand it. Whether or not you’re 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 stage to superior stage.
Pattern Desk Information
To higher perceive and execute the SQL queries, let’s start by inspecting the pattern information used on this article. The next tables will function the idea for all of the queries and examples.
Staff 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 Stage
On this part, allow us to present a tenet of fundamental SQL queries that college students who’re new to SQL can simply perceive. These fundamental queries can be utilized as the muse to reaching a consolation stage with an important features of SQL, together with number of information, types of information filtering and rudimentary computations.
Q1. Write a question to show all information from the Staff desk.
Reply:
SELECT * FROM Staff;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 | 32Q2. Fetch solely the FirstName and LastName of staff.
Reply:
SELECT FirstName, LastName FROM Staff;
FirstName | LastName
--------------------
Alice | Johnson
Bob | Smith
Charlie | Brown
David | Williams
Eva | Davis
Q3. Retrieve the distinctive division IDs from the Staff desk.
Reply:
SELECT DISTINCT DepartmentID FROM Staff;
DepartmentID
-------------
10
This fall. Fetch staff with a wage higher than 60,000.
Reply:
SELECT * FROM Staff 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 worth 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 type staff by their HireDate in ascending order.
Reply:
SELECT * FROM Staff 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 worth of merchandise within the Electronics class.
Reply:
SELECT MAX(Value) AS MaxPrice FROM Merchandise WHERE Class = 'Electronics';
MaxPrice
--------
1200The next part of the article offers with the intermediate stage in Studying SQL by presenting extra complete queries. You’ll proceed with becoming a member of tables, learn to use capabilities to filter information in addition to complicated operations to resolve real-world operations higher.
Q11. Write a question to hitch Staff and Orders tables to fetch worker names together with their orders.
Reply:
SELECT e.FirstName, e.LastName, o.OrderID, o.OrderDate
FROM Staff 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 Staff
GROUP BY DepartmentID;
DepartmentID | TotalSalary
-------------------------
10 | 1355000
Q13. Discover the workers who wouldn’t have a supervisor.
Reply:
SELECT * FROM Staff 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 common product worth 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 Staff
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 title.
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 title begins with ‘A’.
Reply:
SELECT * FROM Staff 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 Staff
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 common wage.
Reply:
SELECT * FROM Staff
WHERE Wage > (SELECT AVG(Wage) FROM Staff);
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 very best whole 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 workers who haven’t positioned any orders.
Reply:
SELECT * FROM Staff
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 Staff
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 Staff 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 whole 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 Staff e
JOIN Staff m ON e.ManagerID = m.EmployeeID
WHERE YEAR(e.HireDate) = YEAR(m.HireDate);| EmployeeName | ManagerName |
|---|---|
| Alice | Bob |
Q29. Retrieve the worker names with the very best wage in every division.
Reply:
SELECT DepartmentID, FirstName, LastName, Wage
FROM Staff
WHERE (DepartmentID, Wage) IN (
SELECT DepartmentID, MAX(Wage)
FROM Staff
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 Staff 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 Stage
Within the superior stage, 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, fundamental subqueries, and optimization strategies to permit you overcoming sophisticated 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 Staff e
JOIN Staff m ON e.ManagerID = m.EmployeeID
WHERE e.Wage > m.Wage;| EmployeeName | ManagerName |
|---|---|
| Alice | Bob |
Q32. Retrieve the second highest wage from the Staff desk.
Reply:
SELECT MAX(Wage) AS SecondHighestSalary
FROM Staff
WHERE Wage < (SELECT MAX(Wage) FROM Staff);
SecondHighestSalary
75000Q33. Listing the departments with no staff assigned.
Reply:
SELECT * FROM Departments
WHERE DepartmentID NOT IN (SELECT DISTINCT DepartmentID FROM Staff);| DepartmentID | DepartmentName |
|---|---|
| 4 | Advertising and marketing |
Q34. Write a question to create a view displaying worker names and their division names.
Reply:
CREATE VIEW EmployeeDepartmentView AS
SELECT e.FirstName, e.LastName, d.DepartmentName
FROM Staff 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 Staff 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 based mostly on their wage inside every division.
Reply:
SELECT EmployeeID, FirstName, DepartmentID, Wage,
RANK() OVER (PARTITION BY DepartmentID ORDER BY Wage DESC) AS Rank
FROM Staff;| 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. Establish the division with the very best whole wage expenditure.
Reply:
SELECT DepartmentID, SUM(Wage) AS TotalExpenditure
FROM Staff
GROUP BY DepartmentID
ORDER BY TotalExpenditure DESC
LIMIT 1;| DepartmentID | TotalExpenditure |
|---|---|
| 1 | 450000 |
Q39. Write a question to search out the proportion contribution of every product to whole 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 Staff e1
WHERE ManagerID IS NOT NULL
AND Wage > 70000
AND ManagerID IN (
SELECT ManagerID FROM Staff 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 based mostly 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. Establish 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 whole order amount is between 50 and 100.
Reply:
SELECT e.FirstName, e.LastName, SUM(o.Amount) AS TotalQuantity
FROM Staff 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 Staff;| 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 brief desk for orders with excessive income (higher 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 supplies a strong basis for environment friendly information administration and evaluation. By training these SQL Question Interview Questions, you improve your capability 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 abilities, SQL stays an important device for any information skilled, and understanding its various capabilities will unlock numerous alternatives for problem-solving and perception era.
My title 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 creator. My first e book named #turning25 has been printed and is offered on amazon and flipkart. Right here, I’m technical content material editor at Analytics Vidhya. I really feel proud and joyful to be AVian. I’ve an important workforce to work with. I really like constructing the bridge between the expertise and the learner.
