topQuestionsPractice.sql

1.Find the second highest salary from the Employee table.

SELECT MAX(salary) 
FROM employee 
WHERE salary < (SELECT MAX(salary) FROM employee);

------x--------x--------x---------x---------x-------
2.Find employees who earn more than their manager.

SELECT e.name 
FROM employee e 
JOIN employee m ON e.manager_id = m.id 
WHERE e.salary > m.salary;


------x--------x--------x---------x---------x-------
3.Find duplicate emails in a users table.

SELECT email, COUNT(*) 
FROM users 
GROUP BY email 
HAVING COUNT(*) > 1;


------x--------x--------x---------x---------x-------
4.List all departments that have more than 5 employees.

SELECT department_id, COUNT(*) AS total_employees 
FROM employee 
GROUP BY department_id 
HAVING COUNT(*) > 5;


------x--------x--------x---------x---------x-------
5.Find customers who never placed an order.

SELECT c.name 
FROM customers c 
LEFT JOIN orders o ON c.id = o.customer_id 
WHERE o.id IS NULL;


------x--------x--------x---------x---------x-------
6.Display employee name and department name (using JOIN).

SELECT e.name, d.name AS department 
FROM employee e 
JOIN department d ON e.department_id = d.id;


------x--------x--------x---------x---------x-------
7.Get the nth highest salary (e.g., 3rd highest).

SELECT DISTINCT salary 
FROM employee e1 
WHERE 3 = (
    SELECT COUNT(DISTINCT salary) 
    FROM employee e2 
    WHERE e2.salary >= e1.salary
);


------x--------x--------x---------x---------x-------
8.Get the list of employees who joined in the last 30 days.

SELECT * 
FROM employee 
WHERE join_date >= CURDATE() - INTERVAL 30 DAY;


------x--------x--------x---------x---------x-------
9.Get employees who belong to more than one project.

SELECT employee_id, COUNT(DISTINCT project_id) AS project_count 
FROM employee_project 
GROUP BY employee_id 
HAVING COUNT(DISTINCT project_id) > 1;


------x--------x--------x---------x---------x-------
10.Write a query to fetch employees with same salary.

SELECT salary 
FROM employee 
GROUP BY salary 
HAVING COUNT(*) > 1;


------x--------x--------x---------x---------x-------
11.List the top 5 highest paid employees.

SELECT * 
FROM employee 
ORDER BY salary DESC 
LIMIT 5;


------x--------x--------x---------x---------x-------
12.Find employees whose name starts and ends with 'a'.

SELECT * 
FROM employee 
WHERE name LIKE 'a%' AND name LIKE '%a';


------x--------x--------x---------x---------x-------
13.Get the department-wise highest paid employee.

SELECT e.* 
FROM employee e 
JOIN (
    SELECT department_id, MAX(salary) AS max_salary 
    FROM employee 
    GROUP BY department_id
) d ON e.department_id = d.department_id AND e.salary = d.max_salary;


------x--------x--------x---------x---------x-------
14.Display all employee names in one row separated by commas.

SELECT GROUP_CONCAT(name SEPARATOR ', ') AS all_employees 
FROM employee;


------x--------x--------x---------x---------x-------
15.Swap two column values (e.g., salary and bonus) in a table.

UPDATE employee 
SET salary = salary + bonus,
    bonus = salary - bonus,
    salary = salary - bonus;