SQL Interview & Practice Book
Complete SQL Interview Preparation Guide with 250 Questions, Solutions, and Explanations.
Chapter 1: SQL Basics
Question 1: Retrieve all records from a table.
Write a query to display all rows from the Employees table.
Solution
SELECT * FROM Employees;
Explanation:
Returns every row and column from the table.
Question 2: Select specific columns.
Display EmployeeID and EmployeeName only.
SELECT EmployeeID, EmployeeName
FROM Employees;
Explanation: Retrieves only required columns.
Question 3: Use DISTINCT
Find unique department names.
SELECT DISTINCT Department
FROM Employees;
Question 4: Count Records
SELECT COUNT(*)
FROM Employees;
Question 5: Display Top 10 Rows
SELECT TOP 10 *
FROM Employees;
Chapter 2: Filtering & Sorting
Question 6: Employees with salary greater than 50000
SELECT *
FROM Employees
WHERE Salary > 50000;
Question 7: Sort salary descending
SELECT *
FROM Employees
ORDER BY Salary DESC;
Question 8: Employees in IT department
SELECT *
FROM Employees
WHERE Department='IT';
Question 9: Employees hired after 2024
SELECT *
FROM Employees
WHERE HireDate > '2024-01-01';
Question 10: Use BETWEEN
SELECT *
FROM Employees
WHERE Salary BETWEEN 40000 AND 80000;
Chapter 3: Aggregate Functions
Question 11: Average Salary
SELECT AVG(Salary)
FROM Employees;
Question 12: Maximum Salary
SELECT MAX(Salary)
FROM Employees;
Question 13: Minimum Salary
SELECT MIN(Salary)
FROM Employees;
Question 14: Total Salary
SELECT SUM(Salary)
FROM Employees;
Question 15: Department-wise Salary
SELECT Department,
SUM(Salary)
FROM Employees
GROUP BY Department;
Chapter 4: SQL Joins
Question 16: Inner Join
SELECT e.EmployeeName,
d.DepartmentName
FROM Employees e
INNER JOIN Departments d
ON e.DepartmentID=d.DepartmentID;
Question 17: Left Join
SELECT *
FROM Employees e
LEFT JOIN Departments d
ON e.DepartmentID=d.DepartmentID;
Question 18: Right Join
SELECT *
FROM Employees e
RIGHT JOIN Departments d
ON e.DepartmentID=d.DepartmentID;
Question 19: Full Join
SELECT *
FROM Employees e
FULL OUTER JOIN Departments d
ON e.DepartmentID=d.DepartmentID;
Question 20: Self Join
SELECT A.EmployeeName,
B.EmployeeName AS Manager
FROM Employees A
JOIN Employees B
ON A.ManagerID=B.EmployeeID;
Chapter 5: Subqueries & CTEs
Question 21: Highest Salary Employee
SELECT *
FROM Employees
WHERE Salary=
(
SELECT MAX(Salary)
FROM Employees
);
Question 22: Common Table Expression
WITH HighSalary AS
(
SELECT *
FROM Employees
WHERE Salary > 70000
)
SELECT *
FROM HighSalary;
Chapter 6: Window Functions
Question 23: Rank Employees
SELECT EmployeeName,
Salary,
RANK() OVER
(
ORDER BY Salary DESC
) AS SalaryRank
FROM Employees;
Question 24: Dense Rank
SELECT EmployeeName,
Salary,
DENSE_RANK() OVER
(
ORDER BY Salary DESC
) AS RankNo
FROM Employees;
Question 25: Row Number
SELECT EmployeeName,
ROW_NUMBER() OVER
(
ORDER BY EmployeeID
) AS RowNo
FROM Employees;
Chapter 7: Stock Market SQL Case Studies
Question 26: Daily Average Closing Price
SELECT TradeDate,
AVG(ClosePrice)
FROM StockData
GROUP BY TradeDate;
Question 27: 7-Day Moving Average
SELECT TradeDate,
ClosePrice,
AVG(ClosePrice)
OVER(
ORDER BY TradeDate
ROWS BETWEEN 6 PRECEDING
AND CURRENT ROW
) AS MA7
FROM StockData;
Question 28: Highest Trading Volume
SELECT *
FROM StockData
WHERE Volume=
(
SELECT MAX(Volume)
FROM StockData
);
Continue the same format until Question 250. Add chapters on:
- Indexes
- Views
- Stored Procedures
- Triggers
- Normalization
- Transactions
- Query Optimization
- Execution Plans
- Advanced Window Functions
- Real Interview Questions
- Stock Market Analytics
0 Comments