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 Knowledge
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 suggestion of fundamental SQL queries that college students who’re new to SQL can simply perceive. These fundamental queries can be utilized as the inspiration to reaching a consolation stage with a very powerful points of SQL, together with collection 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 | 32Q2. 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 fall. Fetch staff with a wage higher 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 whole 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
--------
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 advanced operations to unravel real-world operations higher.
Q11. Write a question to hitch 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 whole wage by division.
Reply:
SELECT DepartmentID, SUM(Wage) AS TotalSalary
FROM Workers
GROUP BY DepartmentID;
DepartmentID | TotalSalary
-------------------------
10 | 1355000
Q13. Discover the workers who wouldn’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 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 whole 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 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 every year.
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 very best complete amount bought.
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 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 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 whole 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 very 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 whole 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 stage, we take care of advanced synthesis of SQL question statements. This part is dedicated to the outline of extra advanced 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 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
75000Q33. Listing the departments with no staff assigned.
Reply:
SELECT * FROM Departments
WHERE DepartmentID NOT IN (SELECT DISTINCT DepartmentID FROM Workers);| DepartmentID | DepartmentName |
|---|---|
| 4 | Advertising |
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 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 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 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. Establish the division with the very 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 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 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 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 gives a stable basis for environment friendly information administration and evaluation. By training these SQL Question Interview Questions, you improve your skill 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 a necessary 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 lots of extra. I’m additionally an writer. My first e-book named #turning25 has been printed 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 completely satisfied to be AVian. I’ve an awesome workforce to work with. I really like constructing the bridge between the know-how and the learner.