๐งฉ 1. Basic SQL Queries
๐น Retrieve all records from a table
SELECT * FROM Employees;
๐น Retrieve specific columns
SELECT FirstName, LastName, Department FROM Employees;
๐น Filter rows using WHERE
SELECT * FROM Employees
WHERE Department = 'HR';
๐น Sorting data
SELECT * FROM Employees
ORDER BY Salary DESC;
๐น Aliases for readability
SELECT FirstName + ' ' + LastName AS FullName, Salary AS MonthlySalary
FROM Employees;
๐งฎ 2. Aggregate Functions
๐น Count, Sum, Average
SELECT COUNT(*) AS TotalEmployees,
SUM(Salary) AS TotalSalary,
AVG(Salary) AS AverageSalary
FROM Employees;
๐น Minimum and Maximum
SELECT MIN(Salary) AS LowestSalary,
MAX(Salary) AS HighestSalary
FROM Employees;
๐งโ๐คโ๐ง 3. Grouping Data
๐น Group by Department
SELECT Department, COUNT(*) AS EmployeeCount
FROM Employees
GROUP BY Department;
๐น Group by with HAVING (Filter groups)
SELECT Department, AVG(Salary) AS AvgSalary
FROM Employees
GROUP BY Department
HAVING AVG(Salary) > 60000;
๐ 4. Joins (Most Asked in Interviews)
๐น INNER JOIN
SELECT E.FirstName, D.DepartmentName
FROM Employees E
INNER JOIN Departments D ON E.DepartmentID = D.DepartmentID;
๐น LEFT JOIN
SELECT E.FirstName, D.DepartmentName
FROM Employees E
LEFT JOIN Departments D ON E.DepartmentID = D.DepartmentID;
๐น RIGHT JOIN
SELECT E.FirstName, D.DepartmentName
FROM Employees E
RIGHT JOIN Departments D ON E.DepartmentID = D.DepartmentID;
๐น FULL JOIN
SELECT E.FirstName, D.DepartmentName
FROM Employees E
FULL JOIN Departments D ON E.DepartmentID = D.DepartmentID;
๐น CROSS JOIN
SELECT E.FirstName, D.DepartmentName
FROM Employees E
CROSS JOIN Departments D;
๐ง 5. Subqueries
๐น Find employees earning more than average salary
SELECT * FROM Employees
WHERE Salary > (SELECT AVG(Salary) FROM Employees);
๐น Find highest salary using subquery
SELECT * FROM Employees
WHERE Salary = (SELECT MAX(Salary) FROM Employees);
๐งฐ 6. Common Table Expressions (CTE)
๐น Example: Recursive CTE for Employee Hierarchy
WITH EmployeeCTE AS (
SELECT EmployeeID, Name, ManagerID
FROM Employees
WHERE ManagerID IS NULL
UNION ALL
SELECT E.EmployeeID, E.Name, E.ManagerID
FROM Employees E
INNER JOIN EmployeeCTE C ON E.ManagerID = C.EmployeeID
)
SELECT * FROM EmployeeCTE;
๐ต๏ธโโ๏ธ 7. Window Functions (Advanced)
๐น Rank employees by salary
SELECT EmployeeID, FirstName, Salary,
RANK() OVER (ORDER BY Salary DESC) AS SalaryRank
FROM Employees;
๐น Running total of salaries
SELECT EmployeeID, Salary,
SUM(Salary) OVER (ORDER BY EmployeeID) AS RunningTotal
FROM Employees;
โณ 8. Date and Time Queries
๐น Employees joined in last 30 days
SELECT * FROM Employees
WHERE DATEDIFF(DAY, JoinDate, GETDATE()) <= 30;
๐น Extract year and month
SELECT FirstName, YEAR(JoinDate) AS JoinYear, MONTH(JoinDate) AS JoinMonth
FROM Employees;
๐ 9. Constraints & NULL Handling
๐น IS NULL and IS NOT NULL
SELECT * FROM Employees
WHERE ManagerID IS NULL;
๐น COALESCE and ISNULL
SELECT FirstName, COALESCE(Phone, 'Not Available') AS PhoneNumber
FROM Employees;
โ๏ธ 10. DML Operations
๐น INSERT
INSERT INTO Employees (FirstName, LastName, Salary, DepartmentID)
VALUES ('Nitin', 'Kumar', 70000, 2);
๐น UPDATE
UPDATE Employees
SET Salary = Salary + 5000
WHERE Department = 'IT';
๐น DELETE
DELETE FROM Employees
WHERE EmployeeID = 101;
๐ 11. Transactions
BEGIN TRANSACTION;
UPDATE Accounts SET Balance = Balance - 500 WHERE AccountID = 1;
UPDATE Accounts SET Balance = Balance + 500 WHERE AccountID = 2;
IF @@ERROR <> 0
ROLLBACK TRANSACTION;
ELSE
COMMIT TRANSACTION;
โก 12. Performance & Optimization Queries
๐น Check execution plan
SET SHOWPLAN_ALL ON;
SELECT * FROM Employees WHERE Department = 'IT';
SET SHOWPLAN_ALL OFF;
๐น Create an index
CREATE INDEX IX_Employees_Department ON Employees(Department);
๐งฉ 13. String Functions
SELECT UPPER(FirstName), LOWER(LastName), LEN(FirstName), SUBSTRING(FirstName, 1, 3)
FROM Employees;
๐ 14. Pivot and Unpivot
๐น PIVOT Example
SELECT Department, [2024] AS Hires2024, [2025] AS Hires2025
FROM (
SELECT Department, YEAR(JoinDate) AS JoinYear, EmployeeID
FROM Employees
) AS Source
PIVOT (
COUNT(EmployeeID) FOR JoinYear IN ([2024], [2025])
) AS PivotTable;
๐งฉ 15. Find Nth Highest Salary
To find the Nth highest salary (e.g., 3rd highest):
SELECT DISTINCT TOP 1 Salary
FROM Employees E1
WHERE 3 - 1 = (
SELECT COUNT(DISTINCT Salary)
FROM Employees E2
WHERE E2.Salary > E1.Salary
);
๐ง Replace 3 with any number (like 2 for second highest).
๐ 16. Find Duplicate Records in a Table
To find duplicate rows based on name:
SELECT FirstName, LastName, COUNT(*) AS DuplicateCount
FROM Employees
GROUP BY FirstName, LastName
HAVING COUNT(*) > 1;
โ
Shows which names appear more than once.
๐งน 17. Delete Duplicate Records (Keep One)
Remove duplicates while keeping one record:
WITH CTE AS (
SELECT *, ROW_NUMBER() OVER (PARTITION BY FirstName, LastName ORDER BY EmployeeID) AS RowNum
FROM Employees
)
DELETE FROM CTE WHERE RowNum > 1;
โ๏ธ Uses ROW_NUMBER() to identify duplicates.
๐ 18. Clustered vs Non-Clustered Index
-- Clustered Index (physically sorts data)
CREATE CLUSTERED INDEX IX_EmployeeID ON Employees(EmployeeID);
-- Non-Clustered Index (creates separate index structure)
CREATE NONCLUSTERED INDEX IX_Department ON Employees(Department);
๐ Clustered = one per table, Non-clustered = multiple allowed.
๐ฅ 19. Display Employee Count Department-Wise
SELECT Department, COUNT(*) AS EmployeeCount
FROM Employees
GROUP BY Department
ORDER BY EmployeeCount DESC;
๐ Useful for finding which department has most employees.
๐งโ๐ผ 20. Find Employees Without a Manager
SELECT * FROM Employees
WHERE ManagerID IS NULL;
Shows all top-level employees or heads of departments.
๐ฐ 21. Employees Earning More Than Department Average
SELECT E.FirstName, E.Salary, E.Department
FROM Employees E
WHERE E.Salary > (
SELECT AVG(Salary)
FROM Employees
WHERE Department = E.Department
);
๐ Helps identify high-performing employees.
๐งฎ 22. Find Second Highest Salary Using CTE
WITH SalaryCTE AS (
SELECT Salary, DENSE_RANK() OVER (ORDER BY Salary DESC) AS RankNo
FROM Employees
)
SELECT Salary FROM SalaryCTE
WHERE RankNo = 2;
๐ The easiest and most optimized way to get the 2nd highest salary.
๐
23. Employees Joined in Last 60 Days
SELECT * FROM Employees
WHERE DATEDIFF(DAY, JoinDate, GETDATE()) <= 60;
Shows all employees recently joined.
๐ง 24. Employees with Multiple Projects
SELECT EmployeeID, COUNT(ProjectID) AS ProjectCount
FROM EmployeeProjects
GROUP BY EmployeeID
HAVING COUNT(ProjectID) > 1;
๐ Great query to test GROUP BY + HAVING.
๐ผ 25. Highest Paid Employee Per Department
SELECT Department, FirstName, Salary
FROM Employees E1
WHERE Salary = (
SELECT MAX(Salary)
FROM Employees E2
WHERE E2.Department = E1.Department
);
๐
Shows the top earner in every department.
๐งพ 26. Categorize Employees by Salary Range
SELECT FirstName, Salary,
CASE
WHEN Salary > 100000 THEN 'High'
WHEN Salary BETWEEN 50000 AND 100000 THEN 'Medium'
ELSE 'Low'
END AS SalaryCategory
FROM Employees;
๐ก Often asked in interviews to test CASE usage.
๐ซ 27. Employees Who Never Took Leave
SELECT E.EmployeeID, E.FirstName
FROM Employees E
LEFT JOIN Leaves L ON E.EmployeeID = L.EmployeeID
WHERE L.EmployeeID IS NULL;
Shows employees with no leave records โ uses LEFT JOIN.
๐ข 28. Department with Maximum Employees
SELECT TOP 1 Department, COUNT(*) AS EmployeeCount
FROM Employees
GROUP BY Department
ORDER BY COUNT(*) DESC;
๐ฏ Finds the department with the largest team.
๐ 29. Average Salary per Department (Window Function)
SELECT Department, Salary,
AVG(Salary) OVER (PARTITION BY Department) AS AvgDeptSalary
FROM Employees;
โก Window functions are highly efficient for analytical queries.
โ๏ธ 30. Check Query Performance (Execution Plan)
SET SHOWPLAN_ALL ON;
SELECT * FROM Employees WHERE Department = 'Finance';
SET SHOWPLAN_ALL OFF;
๐งฉ Returns the execution plan instead of executing the query โ useful for performance tuning.