Set Operators Exercise - Exercise

Contact Us or call 1-877-932-8228
Set Operators Exercise - Exercise

Set Operators Exercise

Duration: 10 to 15 minutes.
  1. Write a simple query to retrieve city and state_province data from the locations table.
  2. Using a set operator, write a query to determine names that appear in both the city and the state_province fields of the location table.
  3. Write a query that returns the same results without using a set operator.
  4. Write a query (using an inline view) that returns a count of how many distinct entries appear in either the city and the state_province fields.
  5. Write a query (using an inline view) that returns a count of how many total entries appear in the city and the state_province fields combined.

Solution:

Using-Set-Operators/Solutions/set_operations_solutions.sql
--
-- Write a simple query to retrieve city and
-- state_province data from the locations table.
--
SELECT city, state_province 
FROM locations;

--
-- Using a set operator, write a query to determine 
-- names that appear in both the city and the 
-- state_province fields of the location table.
--
SELECT city 
FROM locations
INTERSECT
SELECT state_province 
FROM locations;

--
-- Write a query that returns the same results without 
-- using a set operator.
--
SELECT city 
FROM locations
WHERE city = state_province;

--
-- Write a query (using an inline view) that returns a 
-- count of how many distinct entries appear in either 
-- the city and the state_province fields.
--
SELECT count(*) 
FROM 
(
	SELECT city place 
	FROM locations
	UNION 
	SELECT state_province 
	FROM locations
);

--
-- Write a query (using an inline view) that returns a 
-- count of how many <em>total</em>entries appear in the 
-- city and the state_province fields combined.
--
SELECT count(*) 
FROM 
	(
	SELECT city place 
	FROM locations
	UNION ALL
	SELECT state_province 
	FROM locations
);

--
-- (Besides the duplicates identified earlier, there are a number of nulls):
--
SELECT count(*) 
FROM 
(
	SELECT city place 
	FROM locations
	UNION ALL
	SELECT state_province 
	FROM locations
)
WHERE place IS NULL;
Next