JOINS

Question 1

SELECT customer_id, SUM(amount) AS total_spent_by_customer
FROM payment
GROUP BY customer_id
HAVING SUM(amount) > 100;

Answer 1

creating alias of total_spent_by_customer, cannot use alias name in query-will throw an error

Question 2

SELECT * FROM payment
FULL OUTER JOIN customer
ON customer.customer_id = payment.customer_id
WHERE payment.payment_id IS NULL 
OR customer.customer_id IS NULL

Answer 2

produces opposite of an inner join

Question 3

SELECT COUNT(inventory.film_id) AS number_of_copies, store_id, title FROM inventory
FULL JOIN film
ON film.film_id = inventory.film_id
GROUP BY inventory.film_id, store_id, title
ORDER BY title

Answer 3

provides a list of number of copies of movies by store

Question 4

SELECT COUNT(inventory.film_id) AS number_of_copies, store_id, title FROM inventory
FULL JOIN film
ON film.film_id = inventory.film_id
WHERE inventory_id IS null
GROUP BY inventory.film_id, store_id, title
ORDER BY title

Answer 4

provides list of movies that no stores have in possession

Question 5

SELECT film.film_id, title, inventory_id
FROM film
LEFT JOIN inventory 
ON inventory.film_id = film.film_id
WHERE inventory.film_id IS null

Answer 5

left outer join

Question 6

SELECT COUNT(amount) AS num_transactions
FROM payment;

Answer 6

provides alias for COUNT(amount) value

Question 7

SELECT title, first_name, last_name FROM film
INNER JOIN film_actor
ON film_actor.film_id = film.film_id
INNER JOIN actor
ON film_actor.actor_id = actor.actor_id
WHERE first_name = ‘Nick’ AND last_name = ‘Wahlberg’;

Answer 7

used to connect three tables together , you can do multiple inner joins.