Retrieving data with SELECT - Assignments

1. Which statement?

Which SQL statement do you use to combine rows from tables, removing duplicates?

1. 🔲 INNER JOIN
2. ☑️ UNION
3. 🔲 LEFT JOIN
4. 🔲 UNION ALL

---

2. Table actors

We are going to use UNION to combine 2 selections from the table actors.

Complete the query below to achieve the following: * Data from the table actors, column film_count. * In one selection, retrieve data with a film_count equal to 3. * In the other selection, retrieve data with a film_count equal to 9. * Duplicates should not be removed.

Do the following: * Create the 2 selections from the table actors. * Filter with WHERE on the column film_count. * Use UNION ALL to combine the selections.

SELECT
  film_count
FROM
  actors
WHERE
  film_count = 3
UNION ALL
SELECT
  film_count
FROM
  actors
WHERE
  film_count = 9;

film_count
3
3
3
3
3
3
9

Columns: 1
Rows: 7

---

3. (Extra) Customers and sales representatives

We have 2 tables:

• customers: Customer data.
• sales_representatives: Sales representative data.

View the contents of the tables:

---

3.1. Table customers

customer_id	name	city	grade	sales_rep_id
3002	Suzy Rimando	New York	100	5001
3007	Brad Davis	New York	200	5001
3005	Graham Zusi	Amsterdam	200	5002
3008	Juliette Green	London	300	5002
3004	Fabiola Johnson	Amsterdam	300	5006
3009	Geoff Cameron	Berlin	100	5003
3003	Jozy Altidor	Moscow	200	5007
3001	Brad Guzan	London		5005

---

3.2. Table sales_representatives

sales_rep_id	name	city	commission
5001	Jessy Hoog	Amsterdam	0.15
5002	Nail Knite	Paris	0.13
5005	Pit Alex	London	0.11
5006	Madalon Lyon	Paris	0.14
5007	Paulina Adam	Amsterdam	0.13
5003	Laura Hen	New York	0.12

---

3.3. Expected outcome

You need to write a query that combines data from the 2 tables.

The result should look like this:

id	name	city
3005	Graham Zusi	Amsterdam
3004	Fabiola Johnson	Amsterdam
5001	Jessy Hoog	Amsterdam
5007	Paulina Adam	Amsterdam

---

3.4. Query with UNION

Write a query using UNION. Combine data from the 2 tables to get the expected outcome.

• Use SELECT, FROM, AS, WHERE, and UNION.

SELECT
  customer_id AS "id",
  name,
  city
FROM
  customers
WHERE
  city = 'Amsterdam'
UNION
SELECT
  sales_rep_id AS "id",
  name,
  city
FROM
  sales_representatives
WHERE
  city = 'Amsterdam';