# Subqueries - Exercise

Subqueries - Exercise

# Subqueries

Duration: 10 to 15 minutes.
1. Create a query that returns the lowest paid employee. (This will involve a subquery in the WHERE clause).
2. Create a query that returns the departments where the average salary is less than the average for all employees. (This will involve a subquery in the HAVING clause).
3. Create a query that returns the lowest paid employee for each department. (This will involve a correlated subquery).

## Solution:

Using-Subqueries/Solutions/subquery_solutions.sql
```--
-- Lowest paid employee.
--
SELECT *
FROM employees
WHERE salary = (SELECT min(salary)
FROM employees
);

--
-- Departments where the average
-- salary is less than the average for all employees.
--
SELECT department_name, avg(salary)
FROM emp_details_view
GROUP BY department_name
HAVING avg(salary) < (SELECT avg(salary)
FROM employees
);

--
-- Lowest paid employee for each department.
--
SELECT *
FROM employees e1
WHERE e1.salary = (SELECT min(salary)
FROM employees e2
WHERE e1.department_id = e2.department_id
);```