1.
Basic SELECT / Filtering
1 Find all employees with salary greater than 50000.
2 Find employees whose name starts with 'A'.
3 Find employees hired in the last 6 months.
4 Find employees with no manager assigned (manager_id IS NULL).
5 List distinct departments from the employees table.
6 Find employees whose salary is between 30000 and 60000.
2. Aggregate + GROUP BY
7 Find the total salary paid per department.
8 Find the number of employees in each department.
9 Find the average salary per department, sorted descending.
10 Find departments having more than 5 employees (HAVING).
11 Find the highest and lowest salary in each department.
12 Find the total number of orders placed by each customer.
3. Second Highest / Nth Highest (classic interview favorite)
13 Find the second highest salary in the employees table.
14 Find the Nth highest salary (using LIMIT/OFFSET or DENSE_RANK).
15 Find the highest salary in each department without using MAX() in some variations (correlated
subquery).
16 Find employees who earn more than the average salary of their department.
4. Duplicates
17 Find duplicate email addresses in a users table.
18 Find duplicate rows across all columns in a table.
19 Delete duplicate rows, keeping only one copy.
20 Count how many times each value appears in a column.
5. Joins
21 List all employees along with their department names (INNER JOIN).
22 Find employees who do NOT belong to any department (LEFT JOIN ... WHERE
NULL).
23 Find departments that have no employees (LEFT JOIN from department side).
24 Find customers who have never placed an order.
25 Find products that have never been ordered.
26 Self-join: Find pairs of employees who work in the same department.
27 Self-join: Find employees who earn more than their manager.
28 Find the manager's name for each employee (self join on manager_id).
6. Subqueries
29 Find employees who earn more than the average salary of the company.
30 Find the department with the highest total salary expenditure.
31 Find customers who have placed more orders than the average number of orders per customer.
32 Find products priced higher than the average price in their category.
7. Window Functions (intermediate/advanced — increasingly asked even for
freshers)
33 Assign rank to employees based on salary within each department (RANK(),
DENSE_RANK(), ROW_NUMBER()).
34 Find the top 3 highest paid employees in each department.
35 Calculate running total of sales per month.
36 Find the difference between an employee's salary and the previous employee's salary (using
LAG()).
37 Find month-over-month growth in sales (LAG/LEAD).
8. Date/Time Based
38 Find employees who joined in the current year.
39 Find the number of orders placed each month.
40 Calculate the age of employees from their date of birth.
41 Find employees who have completed more than 5 years in the company.
9. String Manipulation
42 Extract the domain name from an email column.
43 Find employees whose names contain a specific substring.
44 Concatenate first name and last name into a full name.
45 Convert all names to uppercase/lowercase.
10. Set Operations
46 Find customers who appear in both the "2023 orders" table and "2024 orders" table
(INTERSECT).
47 Find customers who placed orders in 2023 but not in 2024 (EXCEPT/MINUS).
48 Combine two tables of employees from different branches without duplicates (UNION).
11. Conditional Logic
49 Categorize employees into salary bands (Low/Medium/High) using CASE WHEN.
50 Count how many employees fall into each salary band.
51 Find employees who are managers (appear as manager_id for someone else).
12. Real-World Scenario Style (common in product-based companies)
52 Find the top 5 best-selling products by revenue.
53 Find customers who haven't made a purchase in the last 90 days (churn analysis).
54 Find the first order date and last order date for each customer.
55 Find pairs of products frequently bought together (basic version, not ML).
56 Calculate retention: % of customers who ordered in both January and February.
13. Schema Design / Conceptual (often paired with coding)
57 Design a schema for an e-commerce orders system (tables, keys, relationships).
58 Explain how you'd index a table to speed up a specific query.
59 Write a query, then explain how you'd optimize it if it's running slow on a large table.