# Set Operators Exercise - Exercise

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;```