🧩 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.