231205 SQL계속(4)

-- 16.

SELECT *
FROM payment;
-- payment_id >> customer_id

SELECT *
FROM customer;
-- customer_id >> store_id

SELECT *
FROM store;
-- store_id >> address_id

SELECT *
FROM address;
-- address_id >> city_id

SELECT *
FROM city;
-- city_id >> country_id


SELECT CONCAT(city, ',', country) store, s.store_id AS "Store ID", SUM(amount) AS "Total Sales"
FROM payment p
         JOIN customer cu ON p.customer_id = cu.customer_id
         JOIN store s ON cu.store_id = s.store_id
         JOIN address a ON s.address_id = a.address_id
         JOIN city ci ON a.city_id = ci.city_id
         JOIN country co ON ci.country_id = co.country_id
GROUP BY 2
;

-- 17

SELECT *
FROM store;
-- store_id >> address_id

SELECT *
FROM address;
-- address_id >> city_id

SELECT *
FROM city;
-- city_id >> country_id


SELECT store_id, city, country
FROM store s
         JOIN address a ON s.address_id = a.address_id
         JOIN city ci ON a.city_id = ci.city_id
         JOIN country co ON ci.country_id = co.country_id;

-- 18

SELECT *
FROM payment;
-- payment_id >> customer_id

SELECT *
FROM customer;
-- customer_id >> address_id

SELECT *
FROM address;
-- address_id >> city_id


SELECT address, email, SUM(amount)
FROM payment p
         JOIN customer c ON p.customer_id = c.customer_id
         JOIN address a ON c.address_id = a.address_id
GROUP BY p.customer_id
ORDER BY 3 DESC
LIMIT 10;

-- 19.
SELECT CONCAT(first_name, ' ', last_name) AS 'Actor Name'
FROM actor;

-- 20.
SELECT *
FROM language;
-- language id 1:English

SELECT title
FROM film
WHERE language_id = (SELECT language_id
                     FROM language
                     WHERE name = 'English')
  AND (title LIKE 'K%' OR title LIKE 'Q%');

 

계속 꾸준히 공부중이다. 이제는 어지간한 문제에서 아예 못풀겠다는 느낌은 없어졌다. 확실히 수업이 도움이 되고 있다!