**Advanced SQL Queries with Aggregate Functions**
1. Find the department with the highest total salary
SELECT department, SUM(salary) AS total_salary
FROM employee
GROUP BY department
ORDER BY total_salary DESC
LIMIT 1;
Find the department with the lowest average salary
SELECT department, AVG(salary) AS avg_salary
FROM employee
GROUP BY department
ORDER BY avg_salary ASC
LIMIT 1;
3. Count the number of employees in each department, but only show departments with
more than 3 employees
SELECT department, COUNT(*) AS employee_count
FROM employee
GROUP BY department
HAVING COUNT(*) > 3;
4. Find employees earning more than the average salary of all employees
SELECT first_name, last_name, salary
FROM employee
WHERE salary > (SELECT AVG(salary) FROM employee);
5. Find the department with the highest average salary
SELECT department, AVG(salary) AS avg_salary
FROM employee
GROUP BY department
ORDER BY avg_salary DESC
LIMIT 1;
6. Find the second-highest salary in the company
SELECT DISTINCT salary
FROM employee
ORDER BY salary DESC
LIMIT 1 OFFSET 1;
7. Find the most common job title in the company
SELECT job_title, COUNT(*) AS count
FROM employee
GROUP BY job_title
ORDER BY count DESC
LIMIT 1;
8. Calculate the total salary of employees hired after 2020
SELECT SUM(salary) AS total_salary
FROM employee
WHERE hire_date > '2020-01-01';
```
9. Find the employee who has been working for the longest time
SELECT first_name, last_name, hire_date
FROM employee
ORDER BY hire_date ASC
LIMIT 1;
10. Find employees earning the highest salary in each department
SELECT e1.*
FROM employee e1
WHERE salary = (SELECT MAX(salary) FROM employee e2 WHERE e1.department =
e2.department);
11. Calculate the cumulative salary per department using a window function
SELECT department, first_name, last_name, salary,
SUM(salary) OVER (PARTITION BY department ORDER BY salary DESC) AS
cumulative_salary
FROM employee;
12. Find employees who earn more than the average salary of their own department
SELECT first_name, last_name, department, salary
FROM employee e1
WHERE salary > (SELECT AVG(salary) FROM employee e2 WHERE e1.department =
e2.department);
13. Find the difference between the highest and lowest salaries in each department
SELECT department,
MAX(salary) - MIN(salary) AS salary_difference
FROM employee
GROUP BY department;
14. Find the department where the total salary is more than 200,000
SELECT department, SUM(salary) AS total_salary
FROM employee
GROUP BY department
HAVING SUM(salary) > 200000;
16. Find employees who have the same salary as another employee
SELECT e1.first_name, e1.last_name, e1.salary
FROM employee e1
JOIN employee e2 ON e1.salary = e2.salary AND e1.employee_id <> e2.employee_id;
17. Find the percentage contribution of each employee’s salary to their
department’s total salary
SELECT first_name, last_name, department, salary,
(salary * 100.0 / SUM(salary) OVER (PARTITION BY department)) AS
salary_percentage
FROM employee;
18. Find the department with the most employees
SELECT department, COUNT(*) AS employee_count
FROM employee
GROUP BY department
ORDER BY employee_count DESC
LIMIT 1;
19. Find the employee who was hired most recently
SELECT first_name, last_name, hire_date
FROM employee
ORDER BY hire_date DESC
LIMIT 1;
20. Find employees hired before the average hire date of all employees
SELECT first_name, last_name, hire_date
FROM employee
WHERE hire_date < (SELECT AVG(hire_date) FROM employee);
