exercises-05.sql

--- Chapter 5: exercices

--- ex 5-1
--- Gets first and last name, address, and city name of  a customer living in California distrinct
SELECT c.first_name, c.last_name, a.address, ct.city
FROM customer c
	INNER JOIN address a
		ON c.address_id = a.address_id
    INNER JOIN city ct
		ON a.city_id = ct.city_id
WHERE a.district = 'California';

--- ex 5-2
SELECT f.title, a.first_name, a.last_name
FROM film f
	INNER JOIN film_actor fa
		ON f.film_id = fa.film_id
	INNER JOIN actor a
		ON a.actor_id = fa.actor_id
WHERE a.first_name = 'JOHN';

--- ex 5-3
--- all addresses from the same city. Each row must contain 2 addresses, which are different form each other.
SELECT a1.city_id, a1.address, a2.address
FROM address a1
	INNER JOIN address a2
		ON a1.city_id = a2.city_id AND a1.address != a2.address;