📊 SQL Interview Queries – Intermediate Level

━━━━━━━━━━━━━━

❓ Query 01: Find employees earning more than the average salary

SELECT *
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

❓ Query 02: Find department-wise employee count

SELECT department, COUNT(*) AS emp_count
FROM employees
GROUP BY department;

❓ Query 03: Find departments with average salary greater than 60,000

SELECT department
FROM employees
GROUP BY department
HAVING AVG(salary) > 60000;

❓ Query 04: Fetch employees who do not belong to any department

SELECT e.*
FROM employees e
LEFT JOIN departments d
ON e.department_id = d.department_id
WHERE d.department_id IS NULL;

❓ Query 05: Find second highest salary

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

❓ Query 06: Get highest salary in each department

SELECT department, MAX(salary)
FROM employees
GROUP BY department;

❓ Query 07: Fetch employees hired in the last 6 months

SELECT *
FROM employees
WHERE hire_date >= DATE_SUB(CURDATE(), INTERVAL 6 MONTH);

❓ Query 08: Find duplicate email IDs

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

❓ Query 09: Rank employees by salary within each department

SELECT *,
RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rank
FROM employees;

❓ Query 10: Fetch top 2 highest paid employees from each department

SELECT *
FROM (
SELECT *,
DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rnk
FROM employees
) t
WHERE rnk <= 2;

🔥 Show some love with a reaction ❤️