This page provides exercises and solutions to help you practice SQL nested queries. 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.
 

Exercises

  1. Display the first name, last name, city and state for all customers who live in the same state as customer number 170 (Customers table).
  2. Display the package number, internet speed and sector number for all packages whose sector number equals to the sector number of package number 10 (Packages table).
  3. Display the first name, last name and join date for all customers who joined the company after customer number 540 (Customers table).
  4. Display the first name, last name and join date for all customers who joined the company on the same month and on the same year as customer number 372 (Customers table).
  5. Display the first name, last name, city, state and package number for all customers whose internet speed is “5Mbps” (Customers and Packages table).
  6. Display the package number, internet speed and strt_date (the date it became available) for all packages who became available on the same year as package number 7 (Packages table).
  7. Display the first name, monthly discount, package number, main phone number and secondary phone number for all customers whose sector name is Business (Customers, Packages and Sectors tables).
  8. Display the first name, monthly discount and package number for all customers whose monthly payment is greater than the average monthly payment (Customers and Packages table).
  9. Display the first name, city, state, birthdate and monthly discount for all customers who was born on the same date as customer number 179, and whose monthly discount is greater than the monthly discount of customer number 107 (Customers table)
  10. Display all the data from Packages table for packages whose internet speed equals to the internet speed of package number 30, and whose monthly payment is greater than the monthly payment of package number 7 (Packages table).
  11. Display the package number, internet speed, and monthly payment for all packages whose monthly payment is greater than the maximum monthly payment of packages with internet speed equals to “5Mbps” (Packages table).
  12. Display  the package number, internet speed and monthly payment for all packages whose monthly payment is greater than the minimum monthly payment of packages with internet speed equals to “5Mbps” (Packages table).
  13. Display the package number, internet speed and monthly payment for all packages whose monthly payment is lower than the minimum monthly payment of packages with internet speed equals to “5Mbps” (Packages table).
  14. Display the first name, monthly discount and package number for all customers whose monthly discount is lower than the average monthly discount, and whose package number is the same as customer named “Kevin”

 

Solutions

These solutions apply to SQL Server and Oracle.

-- 1
SELECT first_name , last_name , state, city
FROM customers
WHERE state = (SELECT state FROM customers WHERE customer_id = 170)

-- 2
SELECT pack_id, speed , sector_id
FROM packages
WHERE sector_id = (SELECT sector_id FROM packages WHERE pack_id = 10)

-- 3
SELECT first_name , last_name , join_date
FROM customers
WHERE join_date > (SELECT join_date FROM customers WHERE customer_id = 540)

-- 4
SELECT first_name , last_name , join_date
FROM customers
WHERE year(join_date) = (SELECT year(join_date) FROM customers WHERE customer_id = 372)
AND
month(join_date) = (SELECT month(join_date) FROM customers WHERE customer_id = 372)

-- 5
SELECT first_name , last_name , city , state, pack_id
FROM customers
WHERE pack_id IN (SELECT pack_id FROM packages WHERE speed = '5Mbps')

-- 6
SELECT pack_id , speed , strt_date
FROM packages
WHERE year(strt_date) = (SELECT year(strt_date) FROM packages WHERE pack_id =	7)

-- 7
SELECT first_name , monthly_discount , pack_id , main_phone_num ,
					secondary_phone_num
FROM customers
WHERE pack_id IN
		(SELECT pack_id
			FROM packages
			WHERE sector_id IN
					(	SELECT sector_id
						FROM sectors
						WHERE sector_name = 'Business'))

-- 8
SELECT first_name , monthly_discount, pack_id
FROM customers
WHERE pack_id IN
				 (SELECT  pack_id
				  FROM packages
				  WHERE monthly_payment >
							 (SELECT AVG(monthly_payment)
							  FROM packages))

-- 9
SELECT customer_id , first_name , city , state ,birth_date , monthly_discount
FROM customers
WHERE birth_date =
			 (SELECT birth_date FROM customers WHERE customer_id = 179)
			  AND
			  monthly_discount >
				 (SELECT monthly_discount FROM customers WHERE customer_id = 107)

-- 10
SELECT *
FROM packages
WHERE speed =
		 (SELECT speed FROM packages WHERE pack_id = 30)
AND
			 monthly_payment >
			 (SELECT monthly_payment FROM packages WHERE pack_id = 7)

-- 11
SELECT pack_id ,speed , monthly_payment
FROM packages
WHERE monthly_payment
		> ALL(SELECT monthly_payment FROM packages WHERE speed = '5Mbps')

-- 12
SELECT pack_id ,speed , monthly_payment
FROM packages
WHERE monthly_payment >
		 ANY (SELECT monthly_payment FROM packages WHERE speed = '5Mbps')

-- 13
SELECT pack_id ,speed , monthly_payment
FROM packages
WHERE monthly_payment
		 < ANY (SELECT monthly_payment FROM packages WHERE speed = '5Mbps')

-- 14
SELECT first_name ,monthly_discount , pack_id
FROM customers
WHERE monthly_discount <
		(SELECT AVG(monthly_discount) FROM customers)
AND
			 pack_id IN
			 (SELECT pack_id FROM customers WHERE first_name = 'Kevin')