1. How do you find duplicate records in a table?
Use GROUP BY with HAVING COUNT(*) > 1 on the duplicate column(s).
2. How do you delete duplicate records but keep one?
Use ROW_NUMBER() to mark duplicates and delete rows where row number > 1.
3. How do you find the 2nd highest salary?
Use subquery with MAX(salary) less than highest salary, or use DENSE_RANK().
4. How do you find the 3rd highest salary?
Use DENSE_RANK() and filter where rank = 3.
5. How do you find employees earning more than their manager?
Self join employee table and compare employee salary with manager salary.
6. How do you find employees who do not have a manager?
Filter rows where manager_id IS NULL.
7. How do you find departments with no employees?
Use LEFT JOIN from department to employee and filter employee as NULL.
8. How do you find employees who joined in the last 30 days?
Filter using join_date >= CURRENT_DATE - 30.
9. How do you find employees with same salary?
Group by salary and filter HAVING COUNT(*) > 1.
10. How do you fetch only even-numbered rows?
Use row numbering logic and filter rows divisible by 2.
11. How do you fetch only odd-numbered rows?
Use row numbering logic and filter rows not divisible by 2.
12. How do you get the latest record for each customer?
Use ROW_NUMBER() partitioned by customer ordered by date desc.
13. How do you get the first order of each customer?
Use ROW_NUMBER() partitioned by customer ordered by order date asc.
14. How do you find customers who never placed an order?
Use LEFT JOIN customer to orders and filter NULL order records.
15. How do you find customers with more than 5 orders?
Group by customer and use HAVING COUNT(*) > 5.
16. How do you calculate running total?
Use SUM(column) OVER (ORDER BY date).
17. How do you calculate cumulative sales by month?
Use window function with SUM(sales) OVER (ORDER BY month).
18. How do you compare current row with previous row?
Use LAG().
19. How do you compare current row with next row?
Use LEAD().
20. How do you find month-over-month sales growth?
Use LAG(sales) and subtract previous month sales.
https://www.youtube.com/playlist?list=PLQM-BpTd9ZSumxwKgJjuJjlx2OcP_W516
No comments:
Post a Comment