Subqueries - Exercise

Contact Us or call 1-877-932-8228
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
);
Next