Top SQL Queries for Practice (With Short Answers) - Real-Time Scenario Queries (81–100)

 

81. Find duplicate transactions
    Use GROUP BY transaction_id HAVING COUNT(*) > 1.
82. Find inactive customers
    Find customers with no orders in last 6 months.
83. Find repeat customers
    Group by customer and filter COUNT(order_id) > 1.
84. Find one-time customers
    Group by customer and filter COUNT(order_id) = 1.
85. Find top-selling products
    Group by product and order by total quantity sold desc.
86. Find least-selling products
    Group by product and order by total quantity sold asc.
87. Find average order value
    SUM(order_amount) / COUNT(order_id).
88. Find monthly revenue
    Group by month and sum order amount.
89. Find YoY sales growth
    Compare current year sales with previous year sales.
90. Find MoM sales growth
    Use LAG() on monthly sales.
91. Find customer lifetime value
    Sum total purchase amount by customer.
92. Find customer churn
    Find customers with no recent activity.
93. Find best performing region
    Group by region and sort by sales desc.
94. Find worst performing region
    Group by region and sort by sales asc.
95. Find null-heavy columns
    Use COUNT(*) - COUNT(column).
96. Find bad quality records
    Check NULLs, duplicates, invalid formats.
97. Find orphan records
    Use LEFT JOIN and filter unmatched child records.
98. Validate source vs target count
    Compare COUNT(*) from both tables.
99. Find changed records in ETL
    Compare source and target using hash/timestamp.
100. Find load failures in ETL
     Check audit table, rejected rows, and error logs.

Top SQL Queries for Practice (With Short Answers) - Window Function Queries (61–80)

61. Assign row numbers to employees

    SELECT emp_name, ROW_NUMBER() OVER (ORDER BY salary DESC) rn
    FROM Employee;

62. Rank employees by salary

    SELECT emp_name, RANK() OVER (ORDER BY salary DESC) rnk
    FROM Employee;

63. Dense rank employees by salary

    SELECT emp_name, DENSE_RANK() OVER (ORDER BY salary DESC) drnk
    FROM Employee;

64. Find top 3 salaries

    SELECT *
    FROM (
    SELECT *, DENSE_RANK() OVER (ORDER BY salary DESC) drnk
    FROM Employee
    ) x
    WHERE drnk <= 3;

65. Find top 3 salaries in each department

    SELECT *
    FROM (
    SELECT *, DENSE_RANK() OVER (PARTITION BY dept_id 
    ORDER BY salary DESC) drnk
    FROM Employee
    ) x
    WHERE drnk <= 3;

66. Find highest salary in each department

    SELECT *
    FROM (
    SELECT *, RANK() OVER (PARTITION BY dept_id 
    ORDER BY salary DESC) rnk
    FROM Employee
    ) x
    WHERE rnk = 1;

67. Calculate running salary total

    SELECT emp_name, salary,
    SUM(salary) OVER (ORDER BY emp_id) running_total
    FROM Employee;

68. Calculate cumulative sales

    SELECT month, sales,
    SUM(sales) OVER (ORDER BY month) cumulative_sales
    FROM Sales;

69. Get previous salary value

    SELECT emp_name, salary,
    LAG(salary) OVER (ORDER BY emp_id) prev_salary
    FROM Employee;

70. Get next salary value

    SELECT emp_name, salary,
    LEAD(salary) OVER (ORDER BY emp_id) next_salary
    FROM Employee;

71. Compare current and previous month sales

    SELECT month, sales,
    LAG(sales) OVER (ORDER BY month) prev_month_sales
    FROM Sales;

72. Find salary difference from previous employee

    SELECT emp_name, salary,
    salary - LAG(salary) OVER (ORDER BY emp_id) diff
    FROM Employee;

73. Find first salary in each department

    SELECT *,
    FIRST_VALUE(salary) OVER (PARTITION 
    BY dept_id ORDER BY salary DESC)
    FROM Employee;

74. Find last salary in each department

    SELECT *,
    LAST_VALUE(salary) OVER (PARTITION BY 
    dept_id ORDER BY salary DESC)
    FROM Employee;

75. Find average salary by department without grouping

    SELECT emp_name, dept_id,
    AVG(salary) OVER (PARTITION BY dept_id) avg_salary
    FROM Employee;

76. Find percent contribution of salary

    SELECT emp_name, salary,
    salary * 100.0 / SUM(salary) OVER() pct
    FROM Employee;

77. Find duplicate rows using row_number

    SELECT *
    FROM (
    SELECT *, ROW_NUMBER() OVER (PARTITION 
    BY emp_name ORDER BY emp_id) rn
    FROM Employee
    ) x
    WHERE rn > 1;

78. Delete duplicate rows
    Use above query in CTE and delete where rn > 1.
79. Find latest order per customer

    SELECT *
    FROM (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY 
    customer_id ORDER BY order_date DESC) rn
    FROM Orders
    ) x
    WHERE rn = 1;

80. Find first order per customer

    SELECT *
    FROM (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY 
    customer_id ORDER BY order_date ASC) rn
    FROM Orders
    ) x
    WHERE rn = 1;

---

Top SQL Queries for Practice (With Short Answers) - Basic Select Queries (1–15)

 

1. Fetch all records from Employee table

    SELECT * FROM Employee;

2. Fetch only employee names

    SELECT emp_name FROM Employee;

3. Fetch unique department names

    SELECT DISTINCT department FROM Employee;

4. Fetch employees with salary > 50000

    SELECT * FROM Employee WHERE salary > 50000;

5. Fetch employees from HR department

    SELECT * FROM Employee WHERE department = 'HR';

6. Fetch employees with salary between 30000 and 60000

    SELECT * FROM Employee WHERE salary BETWEEN 30000 AND 60000;

7. Fetch employees in HR or IT

    SELECT * FROM Employee WHERE department IN ('HR','IT');

8. Fetch employees whose name starts with A

    SELECT * FROM Employee WHERE emp_name LIKE 'A%';

9. Fetch employees whose name ends with n

    SELECT * FROM Employee WHERE emp_name LIKE '%n';

10. Fetch employees whose name contains 'ar'

    SELECT * FROM Employee WHERE emp_name LIKE '%ar%';

11. Fetch employees with NULL manager_id

    SELECT * FROM Employee WHERE manager_id IS NULL;

12. Fetch employees sorted by salary ascending

    SELECT * FROM Employee ORDER BY salary ASC;

13. Fetch employees sorted by salary descending

    SELECT * FROM Employee ORDER BY salary DESC;

14. Fetch top 5 highest paid employees

    SELECT TOP 5 * FROM Employee ORDER BY salary DESC;

15. Fetch first 5 rows (MySQL)

    SELECT * FROM Employee LIMIT 5;