SQL Practice Questions

Complete Guide with Queries and Answers

100+ Questions | 8 Difficulty Levels + Bonus Questions

Generated on: August 04, 2026
Level 1 - Basic SELECT Queries (1-20)

Q1. Display all employees.

Query:
SELECT * FROM Employee;

Explanation: This query returns all columns for all records in the Employee table. Use this when you need
complete information about all employees.

Q2. Display employee names only.

Query:
SELECT EmployeeName FROM Employee;

Explanation: Returns only the EmployeeName column. Useful when you need specific column data rather than
the entire record.

Q3. Display unique cities from Employee table.

Query:
SELECT DISTINCT City FROM Employee;

Explanation: DISTINCT keyword removes duplicate city values, showing each unique city only once.

Q4. Display employees with salary greater than 50000.

Query:
SELECT * FROM Employee WHERE Salary > 50000;

Explanation: WHERE clause filters records based on the condition. Returns all employees earning more than
50000.
Q5. Display employees from Chennai.
Query:
SELECT * FROM Employee WHERE City = 'Chennai';

Explanation: String comparison in WHERE clause. Returns all records where City equals Chennai.

Q6. Display employees whose salary is between 30000 and 70000.

Query:
SELECT * FROM Employee WHERE Salary BETWEEN 30000 AND 70000;

Explanation: BETWEEN operator checks if a value falls within a specified range (inclusive of boundaries).

Q7. Display employees whose name starts with 'A'.

Query:
SELECT * FROM Employee WHERE EmployeeName LIKE 'A%';

Explanation: LIKE with % wildcard. A% matches any string starting with A. % represents zero or more characters.

Q8. Display employees whose name ends with 'n'.

Query:
SELECT * FROM Employee WHERE EmployeeName LIKE '%n';

Explanation: %n matches any string ending with n. % can appear before or after the pattern.
Q9. Display employees whose name contains 'ra'.
Query:
SELECT * FROM Employee WHERE EmployeeName LIKE '%ra%';

Explanation: %ra% matches any string containing ra anywhere within it.

Q10. Display employees hired after 2023.

Query:
SELECT * FROM Employee WHERE YEAR(HireDate) > 2023;

Explanation: YEAR() function extracts the year from a date. Alternatively: HireDate > '2023-12-31'

Q11. Display employees hired before 2022.

Query:
SELECT * FROM Employee WHERE YEAR(HireDate) < 2022;

Explanation: Returns employees hired in 2021 and earlier. YEAR() simplifies date comparisons.

Q12. Display employees from Chennai or Bangalore.

Query:
SELECT * FROM Employee WHERE City IN ('Chennai', 'Bangalore');

Explanation: IN operator checks if City matches any value in the list. Cleaner than multiple OR conditions.
Q13. Display employees not from Chennai.
Query:
SELECT * FROM Employee WHERE City != 'Chennai'; -- or WHERE City <> 'Chennai';

Explanation: NOT operator or != (or <>) filters out records. Returns all employees from cities other than Chennai.

Q14. Display employees with NULL ManagerID.

Query:
SELECT * FROM Employee WHERE ManagerID IS NULL;

Explanation: IS NULL checks for missing values. Use IS NULL (not = NULL) to check NULL values.

Q15. Display employees with NOT NULL ManagerID.

Query:
SELECT * FROM Employee WHERE ManagerID IS NOT NULL;

Explanation: Returns only employees who have a manager assigned (non-NULL ManagerID).

Q16. Display top 5 highest paid employees.

Query:
SELECT TOP 5 * FROM Employee ORDER BY Salary DESC; -- MySQL: LIMIT 5;

Explanation: ORDER BY Salary DESC sorts by salary (highest first), TOP/LIMIT restricts to 5 rows.
Q17. Display employees ordered by salary ascending.
Query:
SELECT * FROM Employee ORDER BY Salary ASC;

Explanation: ASC sorts lowest to highest. ASC is default, so ORDER BY Salary works the same way.

Q18. Display employees ordered by salary descending.

Query:
SELECT * FROM Employee ORDER BY Salary DESC;

Explanation: DESC sorts highest to lowest. Useful for finding top earners or best performers.

Q19. Display employee names in alphabetical order.

Query:
SELECT EmployeeName FROM Employee ORDER BY EmployeeName ASC;

Explanation: Sorts names alphabetically A→Z. Can also use DESC for reverse alphabetical order.

Q20. Display first 10 employees.

Query:
SELECT TOP 10 * FROM Employee; -- MySQL: LIMIT 10;

Explanation: LIMIT (MySQL) or TOP (SQL Server) restricts output to specified number of rows.
Level 2 - Aggregate Functions (21-35)

Q21. Count total employees.

Query:
SELECT COUNT(*) as TotalEmployees FROM Employee;

Explanation: COUNT(*) counts all rows. COUNT(column) counts non-NULL values in that column.

Q22. Count employees in each department.

Query:
SELECT Department, COUNT(*) as EmployeeCount FROM Employee GROUP BY Department;

Explanation: GROUP BY groups rows by Department, COUNT(*) counts employees in each group.

Q23. Find maximum salary.

Query:
SELECT MAX(Salary) as MaxSalary FROM Employee;

Explanation: MAX() returns the highest value. Other aggregate functions: MIN, SUM, AVG, COUNT.

Q24. Find minimum salary.

Query:
SELECT MIN(Salary) as MinSalary FROM Employee;

Explanation: MIN() returns the lowest value in the Salary column.

Q25. Find average salary.
Query:
SELECT AVG(Salary) as AverageSalary FROM Employee;

Explanation: AVG() calculates the average (sum/count). Good for understanding typical salary levels.

Q26. Find total salary paid.

Query:
SELECT SUM(Salary) as TotalSalary FROM Employee;

Explanation: SUM() adds all values. Useful for budgeting and total expense calculations.

Q27. Find average salary department-wise.

Query:
SELECT Department, AVG(Salary) as AvgSalary FROM Employee GROUP BY Department;

Explanation: GROUP BY Department creates groups, AVG(Salary) calculates average per group.

Q28. Find highest salary in each department.

Query:
SELECT Department, MAX(Salary) as MaxSalary FROM Employee GROUP BY Department;

Explanation: MAX(Salary) with GROUP BY finds the highest earner in each department.
Q29. Find lowest salary in each department.
Query:
SELECT Department, MIN(Salary) as MinSalary FROM Employee GROUP BY Department;

Explanation: MIN(Salary) with GROUP BY finds the lowest earner in each department.

Q30. Find departments having more than 5 employees.

Query:
SELECT Department, COUNT(*) as EmployeeCount FROM Employee GROUP BY Department HAVING COUNT(*)
> 5;

Explanation: HAVING filters groups (after GROUP BY). WHERE filters rows (before GROUP BY).

Q31. Count employees city-wise.

Query:
SELECT City, COUNT(*) as EmployeeCount FROM Employee GROUP BY City;

Explanation: Groups employees by City and counts them. Shows distribution across locations.

Q32. Find total projects assigned.

Query:
SELECT COUNT(*) as TotalProjects FROM ProjectAssignment;

Explanation: Counts all project assignment records. Indicates total workload across team.
Q33. Count customers from each city.
Query:
SELECT City, COUNT(*) as CustomerCount FROM Customer GROUP BY City;

Explanation: Shows customer distribution by geographic location.

Q34. Find total sales amount.

Query:
SELECT SUM(Amount) as TotalSales FROM Sales;

Explanation: SUM() calculates total revenue from all sales transactions.

Q35. Find average marks of students.

Query:
SELECT AVG(Marks) as AverageMarks FROM Student;

Explanation: Calculates average performance across all students. Helpful for benchmarking.

Level 3 - GROUP BY & HAVING (36-45)

Q36. Display departments having average salary above 60000.

Query:
SELECT Department, AVG(Salary) as AvgSalary FROM Employee GROUP BY Department HAVING
AVG(Salary) > 60000;

Explanation: HAVING filters groups based on aggregate function results. Useful for finding high-paying
departments.
Q37. Display cities having more than 10 employees.
Query:
SELECT City, COUNT(*) as EmployeeCount FROM Employee GROUP BY City HAVING COUNT(*) > 10;

Explanation: Shows cities with significant workforce. Helps in office capacity planning.

Q38. Find department with highest average salary.

Query:
SELECT TOP 1 Department, AVG(Salary) as AvgSalary FROM Employee GROUP BY Department ORDER BY
AVG(Salary) DESC;

Explanation: Combines GROUP BY, ORDER BY, and TOP to find the single department with highest average
salary.

Q39. Find department with lowest average salary.

Query:
SELECT TOP 1 Department, AVG(Salary) as AvgSalary FROM Employee GROUP BY Department ORDER BY
AVG(Salary) ASC;

Explanation: ASC order finds lowest average. Identifies departments that might need salary reviews.

Q40. Find total salary department-wise.

Query:
SELECT Department, SUM(Salary) as TotalSalary FROM Employee GROUP BY Department;

Explanation: Shows total payroll expense per department. Important for budgeting.
Q41. Display products sold more than 50 times.
Query:
SELECT ProductName, COUNT(*) as SaleCount FROM Sales GROUP BY ProductName HAVING COUNT(*) >
50;

Explanation: Identifies popular products. HAVING filters on COUNT() results.

Q42. Find total orders customer-wise.

Query:
SELECT CustomerName, COUNT(*) as TotalOrders FROM Orders GROUP BY CustomerName;

Explanation: Shows order frequency per customer. Helps identify loyal/valuable customers.

Q43. Find total sales month-wise.

Query:
SELECT YEAR(OrderDate) as Year, MONTH(OrderDate) as Month, SUM(Amount) as TotalSales FROM
Sales GROUP BY YEAR(OrderDate), MONTH(OrderDate) ORDER BY Year, Month;

Explanation: Groups sales by month and year, calculates total. Shows sales trends over time.

Q44. Find average marks department-wise.

Query:
SELECT Department, AVG(Marks) as AvgMarks FROM Student GROUP BY Department;

Explanation: Shows average student performance by department. Useful for curriculum evaluation.
Q45. Find departments having exactly 5 employees.
Query:
SELECT Department, COUNT(*) as EmployeeCount FROM Employee GROUP BY Department HAVING COUNT(*)
= 5;

Explanation: HAVING with equality operator. Shows departments with specific team size.

Level 4 - String Functions (46-55)

Q46. Convert employee names to uppercase.

Query:
SELECT UPPER(EmployeeName) as UpperCaseName FROM Employee;

Explanation: UPPER() converts all characters to uppercase. Useful for standardized formatting.

Q47. Convert employee names to lowercase.

Query:
SELECT LOWER(EmployeeName) as LowerCaseName FROM Employee;

Explanation: LOWER() converts all characters to lowercase. Helps with case-insensitive comparisons.

Q48. Find length of employee names.

Query:
SELECT EmployeeName, LEN(EmployeeName) as NameLength FROM Employee;

Explanation: LEN() returns character count. Useful for validation (e.g., names should be 2-50 chars).
Q49. Display first three letters of employee names.
Query:
SELECT EmployeeName, LEFT(EmployeeName, 3) as FirstThreeLetters FROM Employee;

Explanation: LEFT(string, 3) extracts first 3 characters. RIGHT(string, 3) gets last 3 characters.

Q50. Remove leading spaces from names.

Query:
SELECT EmployeeName, LTRIM(EmployeeName) as TrimmedName FROM Employee;

Explanation: LTRIM() removes spaces from left. RTRIM() removes from right. TRIM() removes both sides.

Q51. Replace "Manager" with "Lead" in designation.

Query:
SELECT Designation, REPLACE(Designation, 'Manager', 'Lead') as UpdatedDesignation FROM
Employee;

Explanation: REPLACE(string, search, replacement) replaces all occurrences. Case-sensitive in most databases.

Q52. Concatenate employee name and city.

Query:
SELECT CONCAT(EmployeeName, ' - ', City) as NameAndCity FROM Employee; -- Or: CONCAT_WS(' - ',
EmployeeName, City);

Explanation: CONCAT() combines strings. CONCAT_WS() allows separator. Alternative: EmployeeName + ' - ' +
City
Q53. Reverse employee names.
Query:
SELECT EmployeeName, REVERSE(EmployeeName) as ReversedName FROM Employee;

Explanation: REVERSE() flips string order. Useful for palindrome checks or data manipulation.

Q54. Display employee initials.

Query:
SELECT EmployeeName, LEFT(EmployeeName, 1) as Initial FROM Employee; -- For full initials:
SUBSTRING or nested functions

Explanation: LEFT() gets first character. For multiple initials, use SUBSTRING() or multiple LEFT() calls.

Q55. Count number of characters in each name.

Query:
SELECT EmployeeName, LEN(EmployeeName) as CharacterCount FROM Employee;

Explanation: LEN() counts characters including spaces. Useful for data validation and analysis.

Level 5 - Date Functions (56-65)

Q56. Display today's date.

Query:
SELECT GETDATE() as TodayDate; -- MySQL: NOW() or CURDATE();

Explanation: GETDATE() returns current date and time. CURDATE() returns only date (MySQL).
Q57. Find employees hired this year.
Query:
SELECT * FROM Employee WHERE YEAR(HireDate) = YEAR(GETDATE());

Explanation: YEAR() extracts year from date. GETDATE() returns current date. Compares year values.

Q58. Find employees hired last month.

Query:
SELECT * FROM Employee WHERE YEAR(HireDate) = YEAR(GETDATE()) AND MONTH(HireDate) =
MONTH(GETDATE()) - 1;

Explanation: MONTH() extracts month. This finds employees hired in the previous month.

Q59. Calculate employee experience in years.

Query:
SELECT EmployeeName, DATEDIFF(YEAR, HireDate, GETDATE()) as ExperienceYears FROM Employee;

Explanation: DATEDIFF() calculates difference between dates. Format: DATEDIFF(unit, start_date, end_date).

Q60. Display current month sales.

Query:
SELECT SUM(Amount) as CurrentMonthSales FROM Sales WHERE MONTH(OrderDate) = MONTH(GETDATE())
AND YEAR(OrderDate) = YEAR(GETDATE());

Explanation: Filters sales from current month using MONTH() and YEAR(), then sums amounts.
Q61. Display previous month's sales.
Query:
SELECT SUM(Amount) as PreviousMonthSales FROM Sales WHERE MONTH(OrderDate) = MONTH(GETDATE())
- 1 AND YEAR(OrderDate) = YEAR(GETDATE());

Explanation: Subtracts 1 from current month to get previous month data.

Q62. Find orders placed today.

Query:
SELECT * FROM Orders WHERE CAST(OrderDate AS DATE) = CAST(GETDATE() AS DATE);

Explanation: CAST converts datetime to DATE for comparison. Ignores time portion.

Q63. Find employees hired in January.

Query:
SELECT * FROM Employee WHERE MONTH(HireDate) = 1;

Explanation: MONTH() extracts month number. 1=January, 2=February, etc.

Q64. Display year from HireDate.

Query:
SELECT EmployeeName, YEAR(HireDate) as HireYear FROM Employee;

Explanation: YEAR() extracts the year portion from a date column.

Q65. Find total sales year-wise.
Query:
SELECT YEAR(OrderDate) as SalesYear, SUM(Amount) as TotalSales FROM Sales GROUP BY
YEAR(OrderDate) ORDER BY SalesYear;

Explanation: Groups sales by year and calculates total per year. Shows revenue trends annually.

Level 6 - Joins (66-80)

Q66. Display employee name with department name.

Query:
SELECT [Link], [Link] FROM Employee e INNER JOIN Department d ON
[Link] = [Link];

Explanation: INNER JOIN connects Employee and Department tables. Returns only matching records from both
tables.

Q67. Display employees without departments.

Query:
SELECT [Link] FROM Employee e LEFT JOIN Department d ON [Link] =
[Link] WHERE [Link] IS NULL;

Explanation: LEFT JOIN keeps all employees, WHERE DepartmentID IS NULL finds those without department
assignment.

Q68. Display all departments even without employees.

Query:
SELECT [Link], COUNT([Link]) as EmployeeCount FROM Department d LEFT JOIN
Employee e ON [Link] = [Link] GROUP BY [Link], [Link];

Explanation: RIGHT JOIN or LEFT JOIN (reversed) keeps all departments. COUNT shows employees per dept (0
if none).
Q69. Display employees and their managers.
Query:
SELECT [Link] as Employee, [Link] as Manager FROM Employee e LEFT JOIN
Employee m ON [Link] = [Link];

Explanation: Self-join: joining Employee table to itself. Connects employees to their managers.

Q70. Display customer names with orders.

Query:
SELECT [Link], [Link], [Link] FROM Customer c INNER JOIN Orders o ON
[Link] = [Link];

Explanation: Combines Customer and Orders tables using INNER JOIN for customer-order pairs.

Q71. Display products never ordered.

Query:
SELECT [Link] FROM Product p LEFT JOIN Sales s ON [Link] = [Link] WHERE
[Link] IS NULL;

Explanation: LEFT JOIN with IS NULL finds products with no matching sales records.

Q72. Display customers who never placed orders.

Query:
SELECT [Link] FROM Customer c LEFT JOIN Orders o ON [Link] = [Link] WHERE
[Link] IS NULL;

Explanation: Identifies inactive customers. Useful for marketing campaigns or customer retention analysis.
Q73. Display employees working on projects.
Query:
SELECT DISTINCT [Link], [Link] FROM Employee e INNER JOIN ProjectAssignment pa
ON [Link] = [Link] INNER JOIN Project p ON [Link] = [Link];

Explanation: Multiple INNER JOINs connect three tables. DISTINCT removes duplicate rows.

Q74. Display departments with total salary.

Query:
SELECT [Link], SUM([Link]) as TotalSalary FROM Department d INNER JOIN Employee e
ON [Link] = [Link] GROUP BY [Link], [Link];

Explanation: Combines JOIN with GROUP BY and SUM for aggregate data per department.

Q75. Display project names with employee names.

Query:
SELECT [Link], [Link] FROM Project p INNER JOIN ProjectAssignment pa ON
[Link] = [Link] INNER JOIN Employee e ON [Link] = [Link];

Explanation: Connecting three tables to show which employees work on which projects.

Q76. Display employee and manager names.

Query:
SELECT [Link] as Employee, COALESCE([Link], 'No Manager') as Manager FROM
Employee e LEFT JOIN Employee m ON [Link] = [Link];

Explanation: Self-join with COALESCE handles NULL managers. COALESCE provides default value if NULL.
Q77. Display all products with sales quantity.
Query:
SELECT [Link], SUM([Link]) as TotalQuantitySold FROM Product p LEFT JOIN Sales s ON
[Link] = [Link] GROUP BY [Link], [Link];

Explanation: LEFT JOIN shows all products even if not sold. GROUP BY with SUM calculates total sold quantity.

Q78. Display customers with total purchase amount.

Query:
SELECT [Link], SUM([Link]) as TotalPurchase FROM Customer c INNER JOIN Orders o ON
[Link] = [Link] GROUP BY [Link], [Link];

Explanation: Shows spending per customer. Helps identify high-value customers.

Q79. Display department having maximum employees.

Query:
SELECT TOP 1 [Link], COUNT([Link]) as EmployeeCount FROM Department d INNER
JOIN Employee e ON [Link] = [Link] GROUP BY [Link], [Link]
ORDER BY COUNT([Link]) DESC;

Explanation: Combines JOIN, GROUP BY, ORDER BY, and TOP to find largest department.

Q80. Display employees earning more than department average.

Query:
SELECT [Link], [Link], [Link] FROM Employee e INNER JOIN Department d ON
[Link] = [Link] WHERE [Link] > (SELECT AVG(Salary) FROM Employee WHERE
DepartmentID = [Link]);

Explanation: Uses subquery to calculate department average, then filters employees above that average.
Level 7 - Subqueries (81-90)

Q81. Find employee earning highest salary.

Query:
SELECT * FROM Employee WHERE Salary = (SELECT MAX(Salary) FROM Employee);

Explanation: Subquery finds max salary, outer query returns the employee with that salary.

Q82. Find employee earning second highest salary.

Query:
SELECT * FROM Employee WHERE Salary = (SELECT MAX(Salary) FROM Employee WHERE Salary < (SELECT
MAX(Salary) FROM Employee));

Explanation: Nested subqueries: inner finds max, middle finds second max, outer returns employee.

Q83. Find employee earning third highest salary.

Query:
SELECT TOP 3 * FROM Employee ORDER BY Salary DESC; -- OR: Multiple nested subqueries

Explanation: Can be solved with TOP/LIMIT or complex nested subqueries. TOP method is simpler.

Q84. Find employees earning above average salary.

Query:
SELECT * FROM Employee WHERE Salary > (SELECT AVG(Salary) FROM Employee);

Explanation: Subquery calculates average, outer query returns employees above that average.
Q85. Find departments with no employees.
Query:
SELECT * FROM Department WHERE DepartmentID NOT IN (SELECT DISTINCT DepartmentID FROM
Employee);

Explanation: NOT IN with subquery finds departments not in the employee list.

Q86. Find products priced above average.

Query:
SELECT * FROM Product WHERE Price > (SELECT AVG(Price) FROM Product);

Explanation: Subquery calculates average product price, outer query returns above-average products.

Q87. Find customers with maximum order amount.

Query:
SELECT c.* FROM Customer c WHERE [Link] IN (SELECT CustomerID FROM Orders WHERE Amount =
(SELECT MAX(Amount) FROM Orders));

Explanation: Multiple subqueries: inner finds max amount, middle finds customer with that amount, outer returns
details.

Q88. Find students scoring above department average.

Query:
SELECT s.* FROM Student s WHERE [Link] > (SELECT AVG(Marks) FROM Student WHERE Department =
[Link]);

Explanation: Correlated subquery: inner query references outer query's Department value.
Q89. Find employees in department with highest salary.
Query:
SELECT * FROM Employee WHERE DepartmentID = (SELECT DepartmentID FROM (SELECT TOP 1
DepartmentID, AVG(Salary) as AvgSal FROM Employee GROUP BY DepartmentID ORDER BY AvgSal DESC)
as T);

Explanation: Complex subquery: finds department with highest average salary, then returns all employees in that
dept.

Q90. Find customers who purchased all products.

Query:
SELECT [Link], [Link] FROM Customer c WHERE (SELECT COUNT(DISTINCT ProductID)
FROM Orders WHERE CustomerID = [Link]) = (SELECT COUNT(*) FROM Product);

Explanation: Counts distinct products per customer, compares to total products. Customers with all = count
matches total.

Level 8 - Window Functions (91-100)

Q91. Rank employees by salary.

Query:
SELECT EmployeeID, EmployeeName, Salary, RANK() OVER (ORDER BY Salary DESC) as SalaryRank FROM
Employee;

Explanation: RANK() assigns rank but skips numbers after ties (1,2,2,4). Useful for competitions with ties.

Q92. Dense Rank employees by salary.

Query:
SELECT EmployeeID, EmployeeName, Salary, DENSE_RANK() OVER (ORDER BY Salary DESC) as
SalaryRank FROM Employee;

Explanation: DENSE_RANK() doesn't skip numbers (1,2,2,3). Preferred when ties shouldn't affect numbering.
Q93. Row Number for employees.
Query:
SELECT EmployeeID, EmployeeName, Salary, ROW_NUMBER() OVER (ORDER BY Salary DESC) as RowNum
FROM Employee;

Explanation: ROW_NUMBER() gives unique sequential numbers even for ties (1,2,3,4). Assigns unique rank.

Q94. Find highest paid employee in each department.

Query:
SELECT EmployeeName, Department, Salary, RANK() OVER (PARTITION BY Department ORDER BY Salary
DESC) as DeptRank FROM Employee WHERE /* This returns top per dept */ 1=1;

Explanation: PARTITION BY creates groups per department, RANK ranks within each group. Add WHERE
DeptRank = 1 for top only.

Q95. Find second highest salary in each department.

Query:
SELECT * FROM (SELECT EmployeeName, Department, Salary, DENSE_RANK() OVER (PARTITION BY
Department ORDER BY Salary DESC) as DeptRank FROM Employee) as T WHERE DeptRank = 2;

Explanation: Uses window function in subquery, then filters for rank=2. Shows second-highest earner per dept.

Q96. Calculate running total of sales.

Query:
SELECT OrderDate, Amount, SUM(Amount) OVER (ORDER BY OrderDate) as RunningTotal FROM Sales;

Explanation: SUM() with ORDER BY creates cumulative total. Shows sales buildup over time.
Q97. Calculate cumulative salary.
Query:
SELECT EmployeeName, Salary, SUM(Salary) OVER (ORDER BY EmployeeID) as CumulativeSalary FROM
Employee;

Explanation: Cumulative total of salaries ordered by EmployeeID. Shows total payroll buildup.

Q98. Find previous employee salary using LAG().

Query:
SELECT EmployeeName, Salary, LAG(Salary) OVER (ORDER BY Salary DESC) as PreviousSalary FROM
Employee;

Explanation: LAG() accesses previous row's value. Useful for comparing current to previous (salary, price, etc).

Q99. Find next employee salary using LEAD().

Query:
SELECT EmployeeName, Salary, LEAD(Salary) OVER (ORDER BY Salary DESC) as NextSalary FROM
Employee;

Explanation: LEAD() accesses next row's value. Opposite of LAG(). Useful for forward-looking comparisons.

Q100. Divide employees into 4 salary groups using NTILE(4).

Query:
SELECT EmployeeName, Salary, NTILE(4) OVER (ORDER BY Salary) as SalaryQuartile FROM Employee;

Explanation: NTILE(4) divides employees into 4 equal groups. Values 1-4 represent quartiles (0-25%, 25-50%,
etc).
Bonus: Interview Questions (Frequently Asked)

Q101. Find duplicate records.

Query:
SELECT Column1, COUNT(*) FROM TableName GROUP BY Column1 HAVING COUNT(*) > 1;

Explanation: Groups by column and counts occurrences. HAVING count > 1 shows duplicates.

Q102. Delete duplicate records.

Query:
DELETE FROM TableName WHERE RowID NOT IN (SELECT MIN(RowID) FROM TableName GROUP BY Column1);

Explanation: Keeps only the first occurrence (MIN RowID), deletes rest. Requires unique identifier column.

Q103. Find Nth highest salary.

Query:
SELECT TOP 1 Salary FROM (SELECT TOP N Salary FROM Employee ORDER BY Salary DESC) AS T ORDER
BY Salary ASC;

Explanation: Gets top N rows, then returns minimum (which is Nth). Substitute N with desired number.

Q104. Find second highest salary without LIMIT.

Query:
SELECT MAX(Salary) FROM Employee WHERE Salary < (SELECT MAX(Salary) FROM Employee);

Explanation: Finds max salary less than the overall max. Avoids using LIMIT/TOP/NTILE.
Q105. Find employees with same salary.
Query:
SELECT [Link], [Link] FROM Employee e1 INNER JOIN Employee e2 ON [Link] =
[Link] AND [Link] != [Link];

Explanation: Self-join matches employees with equal salaries but different IDs.

Q106. Find employees whose salary is greater than their manager's.

Query:
SELECT [Link], [Link], [Link] as ManagerName, [Link] as ManagerSalary FROM
Employee e LEFT JOIN Employee m ON [Link] = [Link] WHERE [Link] > [Link];

Explanation: Self-join with filter. Shows cases where employee earns more than their manager.

Q107. Find managers having more than 5 employees.

Query:
SELECT ManagerID, COUNT(*) as EmployeeCount FROM Employee WHERE ManagerID IS NOT NULL GROUP BY
ManagerID HAVING COUNT(*) > 5;

Explanation: Groups by ManagerID, counts subordinates, filters for those with >5 employees.

Q108. Swap two column values.

Query:
UPDATE TableName SET Column1 = Column2, Column2 = Column1;

Explanation: Direct update swaps values. Alternative: use temporary variable (UPDATE ... SET Column1=@temp,
@temp=Column2).
Q109. Pivot rows into columns.
Query:
SELECT Department, [Software] as SoftwareSales, [Hardware] as HardwareSales, [Services] as
ServicesSales FROM (SELECT Department, ProductCategory, Amount FROM Sales) T PIVOT
(SUM(Amount) FOR ProductCategory IN ([Software], [Hardware], [Services])) as PivotTable;

Explanation: PIVOT rotates rows to columns. Useful for creating cross-tabulation reports.

Q110. Unpivot columns into rows.

Query:
SELECT Department, Month, Sales FROM (SELECT Department, January, February, March FROM
QuarterlySales) T UNPIVOT (Sales FOR Month IN (January, February, March)) as UnpivotTable;

Explanation: UNPIVOT converts columns to rows. Opposite of PIVOT. Normalizes denormalized data.