This page provides exercises and solutions to help you practice SQL Aggregate functions. These exercises are based on the ACDB 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 (Customers table).
  2. Display the average monthly payment (Packages table).
  3. Display the highest last name alphabetically (Customers table).
  4. Display the number of internet packages (Packages table).
  5. Display the number of records in Customers table.
  6. Display the number of distinct states  (Customers table).
  7. Display the number of distinct internet speeds (Packages table).
  8. Display the number of values (exclude Nulls) in Fax column (Customers table).
  9. Display the number of Null values in Fax column (Customers table).
  10. Display the highest, lowest and average monthly discount (Customers table).

 

Part 2 – GROUP BY and HAVING clauses

  1. Display the state and the number of customers for each state (Customers table).
  2. Display the internet speed and the average monthly payment for each speed (Packages table).
  3. Display the state and the number of distinct cities for each state (Customers table).
  4. Display the sector number and the highest monthly payment for each sector (Packages table).
  5. Package number and average monthly discount (Customers table) –
    1. Display the package number and the average monthly discount for each package.
    2. Display the package number and the average monthly discount for each package, only for packages whose number equals 22 or 13.
  6. Display the highest, lowest and average monthly payment for each internet speed (Packages table).
  7. The number of customer in each internet package (Customers table) –
    1. Display the package number and the number of customers for each package number.
    2. Modify the query to display the package number and number of customers for each package number, only for the customers whose monthly discount is greater than 20.
    3. Modify the query to display the package number and number of customers for each package number, only for the packages with more than 100 customers.
  8. Display the state, city and number of customers for each state and city.
  9. Cities and the average monthly discount (Customers table) –
    1. Display the city and the average monthly discount for each city
    2. Display the city and the average monthly discount for each city, only for the customers whose monthly discount is greater than 20
  10. States and the lowest monthly discount (Customers table) –
    1. Display the state and the lowest monthly discount for each state.
    2. Display the state and lowest monthly discount for each state, only for states where the lowest monthly discount is greater than 10
  11. Display the internet speed and number of package for each internet speed, only for the internet speeds with more than 8 packages.

 

Solutions

These solutions apply to SQL Server and Oracle.

-- 1
SELECT MIN(last_name) FROM customers 

-- 2
SELECT AVG(monthly_payment) FROM packages 

-- 3
SELECT MAX(last_name) FROM customers

-- 4
SELECT COUNT(*) FROM packages  

-- 5
SELECT COUNT(*) FROM customers 

-- 6
SELECT COUNT(DISTINCT state) FROM customers 

-- 7
SELECT COUNT(DISTINCT speed) FROM packages 

-- 8
SELECT COUNT(fax) FROM customers 

-- 9
SELECT COUNT(*) - COUNT(fax)
FROM customers
-- or
SELECT COUNT(*)
FROM customers
WHERE fax IS NOT NULL 

-- 10
SELECT	MIN(monthly_discount) ,
	MAX(monthly_discount) ,
	AVG(monthly_discount)
FROM customers 

-- 11
SELECT COUNT(*) , state
FROM customers
GROUP BY state 

-- 12
SELECT AVG(monthly_payment) , speed
FROM packages
GROUP BY speed

-- 13
SELECT COUNT(DISTINCT city) , state
FROM customers
GROUP BY state 

-- 14
SELECT MAX(monthly_payment) , sector_id
FROM packages
GROUP BY sector_id 

 -- 15
 -- A
 SELECT pack_id , AVG(monthly_discount)
 FROM customers
 GROUP BY pack_id 

 -- B
  SELECT pack_id , AVG(monthly_discount)
 FROM customers
 WHERE pack_id IN (13, 22)
 GROUP BY pack_id 

 -- C
 SELECT AVG(monthly_discount)
 FROM customers
 WHERE pack_id = 18

 -- 16
 SELECT speed , MIN(monthly_payment) , MAX(monthly_payment) , AVG(monthly_payment)
 FROM   packages
 GROUP BY speed  

 -- 17
 -- A
 SELECT pack_id , COUNT(*)
 FROM customers
 GROUP BY pack_id 

  -- B
 SELECT pack_id , COUNT(*)
 FROM customers
 WHERE monthly_discount > 20
 GROUP BY pack_id 

 -- C
 SELECT pack_id , COUNT(*)
 FROM customers
 GROUP BY pack_id
 HAVING COUNT(*) > 100 

 -- 18
 SELECT state, city, COUNT(*)
 FROM customers
 GROUP BY state, city
 ORDER BY state, city

-- 19
 -- A
 SELECT AVG(monthly_discount) , city
 FROM customers
 GROUP BY city

  -- B
 SELECT AVG(monthly_discount) , city
 FROM customers
 WHERE monthly_discount > 20
 GROUP BY city

 -- 20
 -- A
 SELECT MIN(monthly_discount) , state
 FROM customers
 GROUP BY state 

 -- B
  SELECT MIN(monthly_discount) , state
 FROM customers
 GROUP BY state
 HAVING min(monthly_discount) > 10

 -- 21
 SELECT COUNT(*) , speed
 FROM packages
 GROUP BY speed
 HAVING COUNT(*) > 8