1. Find employees earning above average salary
SELECT *
FROM employees
WHERE salary > (
    SELECT AVG(salary)
    FROM employees
);

2. Find second highest salary
SELECT MAX(salary) AS second_highest_salary
FROM employees
WHERE salary < (
    SELECT MAX(salary)
    FROM employees
);

4. Find employees whose name starts with 'A'
SELECT *
FROM employees
WHERE name LIKE 'A%';

5. Find employees whose salary is between 30,000 and 60,000
SELECT *
FROM employees
WHERE salary BETWEEN 30000 AND 60000;

11. Find number of employees in each department
SELECT department_id,
       COUNT(*) AS employee_count
FROM employees
GROUP BY department_id;

12. Find average salary department-wise
SELECT department_id,
       AVG(salary) AS average_salary
FROM employees
GROUP BY department_id;

13. Find departments having more than 5 employees
SELECT department_id,
       COUNT(*) AS employee_count
FROM employees
GROUP BY department_id
HAVING COUNT(*) > 5;

14. Get employee names along with department names
SELECT e.name,
       d.department_name
FROM employees e
INNER JOIN departments d
ON e.department_id = d.id;

15. Find employees who do not belong to any department
SELECT *
FROM employees e
LEFT JOIN departments d
ON e.department_id = d.id
WHERE d.id IS NULL;

16. Find departments that have no employees
SELECT *
FROM departments d
LEFT JOIN employees e
ON d.id = e.department_id
WHERE e.id IS NULL;

17. Get all employees and their manager names (Self Join)
SELECT e.name AS employee_name,
       m.name AS manager_name
FROM employees e
LEFT JOIN employees m
ON e.manager_id = m.id;

18. Find employees earning above average salary
SELECT *
FROM employees
WHERE salary >
(
    SELECT AVG(salary)
    FROM employees
);

19. Find employees earning maximum salary
SELECT *
FROM employees
WHERE salary =
(
    SELECT MAX(salary)
    FROM employees
);

20. Find second highest salary
SELECT DISTINCT salary
FROM employees
ORDER BY salary DESC
OFFSET 1
LIMIT 1;

21. Find third highest salary
SELECT DISTINCT salary
FROM employees
ORDER BY salary DESC
OFFSET 2
LIMIT 1;

22. Rank employees based on salary
SELECT id,
       name,
       salary,
       RANK() OVER(ORDER BY salary DESC) AS rank
FROM employees;

23. Find top 3 highest-paid employees
SELECT *
FROM employees
ORDER BY salary DESC
LIMIT 3;
Using DENSE_RANK
SELECT *
FROM (
    SELECT *,
           DENSE_RANK() OVER(ORDER BY salary DESC) AS rank
    FROM employees
) t
WHERE rank <= 3;

24. Find department-wise highest salary employee
SELECT *
FROM (
    SELECT *,
           ROW_NUMBER() OVER(
               PARTITION BY department_id
               ORDER BY salary DESC
           ) AS rn
    FROM employees
) t
WHERE rn = 1;

25. Find duplicate email addresses
SELECT email,
       COUNT(*)
FROM users
GROUP BY email
HAVING COUNT(*) > 1;

26. Blog App - Find all posts with author names
SELECT p.id,
       p.title,
       p.content,
       u.name AS author_name
FROM posts p
INNER JOIN users u
ON p.user_id = u.id;

27. E-commerce - Find customers who never placed an order
SELECT c.*
FROM customers c
LEFT JOIN orders o
ON c.id = o.customer_id
WHERE o.id IS NULL;

28. Social Media - Find user with highest followers
SELECT user_id,
       COUNT(*) AS total_followers
FROM followers
GROUP BY user_id
ORDER BY total_followers DESC
LIMIT 1;

29. Comments System - Find replies of a particular comment
SELECT *
FROM comments
WHERE parent_comment_id = 5;

Or dynamically:

SELECT *
FROM comments
WHERE parent_comment_id = :comment_id;


30. RBAC - Find all permissions assigned to a user
Tables: users, roles, permissions, role_permissions

Query:

SELECT p.*
FROM users u
INNER JOIN roles r
ON u.role_id = r.id

INNER JOIN role_permissions rp
ON r.id = rp.role_id

INNER JOIN permissions p
ON rp.permission_id = p.id

WHERE u.id = 1;