This page provides exercises and solutions to help you practice SQL Group Functions. These exercises are based on the HR database, and may be performed online or by running the sample schema scripts on your local database server. For additional exercises in other subjects, use this link.
 

Part 1 – Basic Usage

  1. Display the lowest last name alphabetically (Employees table).
  2. Display the highest last name alphabetically (Employees table).
  3. Display the number of rows in Employees table.
  4. Display the number of values (exclude NULLs) in commission_pct column (Employees table).
  5. Display the number of NULL values in commission_pct column (Employees table).
  6. Display the highest, lowest, and average salary.

 

Part 2 – GROUP BY and HAVING clauses

  1. Average salary per department
    1. Display the department number and average salary for each department.
    2. Modify your query to display the results only for departments 50 or 80.
  2. Numer of employees per job id
    1. Display the job id and the number of employees for each job id.
    2. Modify your query to display the results only for employees whose salary is greater the 10000.
    3. Modify your query again, this time display the results only for jobs with more than 2 people.
  3. Display the department number, job id, and the average salary for each department and job id.
  4. Managers and highest salary
    1. Display the manager number and the highest salary for each manager number.
    2. Modify your query to display the results only for employees whose salary is greater than 10000.
  5. Display the job id and minimum salary for each job id, for all jobs whose minimum salary is greater than 7000.
  6. Display the department number, and the average salary for each department, for all departments whose number is in the range of 20 and 80, and their average salary is greater than 9000.

 

Solutions – Oracle

These solutions apply to Oracle and SQL Server.

-- 1. 
SELECT MIN(last_name)
FROM employees 
-- 2.  
SELECT MAX(last_name)
FROM employees 
-- 3.  
SELECT COUNT(*) FROM employees 
-- 4. 
SELECT COUNT(commission_pct) FROM employees 
-- 5. 
SELECT COUNT(*) - COUNT(commission_pct) FROM employees
-- 6. 
SELECT MIN(salary) , MAX(salary) , AVG(salary)
FROM employees 
-- 7.  
-- a 
SELECT department_id , AVG(salary)
FROM employees 
GROUP BY department_id 
-- b 
SELECT department_id , AVG(salary)
FROM employees 
WHERE department_id IN (50, 80)
GROUP BY department_id
-- 8.  
-- a 
SELECT job_id , COUNT|(*)
FROM employees 
GROUP BY job_id 
-- b 
SELECT job_id , COUNT(*)
FROM employees 
WHERE salary > 10000
GROUP BY job_id 
-- c 
SELECT job_id , COUNT(*)
FROM employees 
WHERE salary > 10000
GROUP BY job_id 
HAVING count(*) > 2 
-- 9.  
SELECT AVG(salary) , department_id , job_id 
FROM employees 
GROUP BY department_id , job_id 
-- 10.
-- b 
SELECT MAX(salary) , manager_id 
FROM employees
GROUP BY manager_id 
-- b 
SELECT MAX(salary) , manager_id 
FROM employees 
GROUP BY manager_id 
HAVING MAX(salary) > 10000
-- 11. 
SELECT MIN(salary) , job_id 
FROM employees 
GROUP BY job_id
HAVING MIN(salary) > 7000   
-- 12.
SELECT AVG(salary) , department_id 
FROM employees 
WHERE department_id BETWEEN 20 AND 80 
GROUP BY department_id 
HAVING AVG(salary) > 9000