homework/dmedvedev/joins.sql

-- task 1
--✅ Задача: покажи все заказы и информацию о пользователе, который их сделал.

SELECT 
	o.id AS order_id,
	o.order_date AS order_date,
	o.status AS status,
	u.name AS user_name,
	u.country AS country
FROM orders o
INNER JOIN users u ON u.id = o.user_id; 

SELECT 
	o.id AS order_id,
	o.order_date AS order_date,
	o.status AS status,
	u.name AS user_name,
	u.country AS country
FROM orders o
LEFT JOIN users u ON u.id = o.user_id;


/* - С какой таблицы логичнее начать: `orders` или `users`? Почему?
Логичнее начать с orders потому что мы сразу отсекаем пользователей, которые не делали заказ.

Написал в обоих, INNER JOIN покажет только те заказы, у которых есть пользователи. А LEFT  JOIN покажет все заказы, даже там где нет пользователя.
- Какой тип JOIN здесь уместнее: `INNER` или `LEFT? Напиши запрос в обоих вариантах и посмотри, где пропадут строки.
 */


-- task 2
--✅ Задача: покажи состав заказа 101 (что в нём купили).

SELECT 
	o.id AS order_id,
	p.name AS product_name,
	oi.quantity AS quantity,
	oi.price_at_purchase AS price_at_purchase
FROM order_items oi
INNER JOIN orders o ON o.id = oi.order_id
INNER JOIN products p ON p.id = oi.product_id
WHERE order_id = 101; 

/*
Что изменится, если эту фильтрацию order_id = 101 перенести в JOIN (после ON)?

как я понял, в таком случае , заказ 101 привязывается ко всем товарам потому 
что таблицы не соединены между собой
*/

--task 3
--✅ Задача: выведи все заказы и товары внутри заказов (как “чек по магазину”).

SELECT 
	o.id AS order_id,
	u.name AS user_name,
	p.name AS product_name,
	oi.quantity AS quantity,
	oi.price_at_purchase AS price_at_purchase
FROM orders o
INNER JOIN order_items oi ON o.id = oi.order_id
INNER JOIN products p ON oi.product_id = p.id
INNER JOIN users u ON u.id = o.user_id;

/***❓ Ответь на вопросы:**

- почему после JOIN заказ `3` появляется в нескольких строках;
потому что внутри заказа 3 несколько позиций товаров

- чем результат отличается от задания 2 (по количеству строк и информации). 
во второй задаче нужно было вывести информацию только по 101 заказу, здесь по всем. 
и плюс указали кто купил и какое количество товаров
*/

--task 4
--✅ Задача: вывести список всех пользователей и их заказы.
SELECT 
	u.name AS user_name,
	o.id AS order_id,
	o.status AS status
FROM users u
LEFT JOIN orders o ON o.user_id = u.id;

/*
**✅ После выполнения:**
Найди глазами пользователя без заказов (у него **`order_id`** будет **`NULL`**);
Таких имен найдено 3 штуки

Чем этот запрос отличается от такого же, но с **`INNER JOIN`**?
Иннер джоин в таком случае бы просто удалил из выдачи данные с NULL, 
то есть показал бы только пользователей с заказами, а остальных бы скрыл
*/

--task 5
--✅ Задача: найти пользователей, у которых нет ни одного заказа.

SELECT 
	u.id AS user_id,
	u.name AS user_name
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE o.user_id IS NULL;

/* **❓ Ответь на вопросы:** 
Почему тут важно фильтровать именно по колонке из **`orders`**, а не из **`users`**?

Фильтровать нужно по orders потому что в принципе в системе есть люди, 
которые имеют и не имеют заказы, соответственно при выдаче запроса мы увидим ,
что некоторые пользователи не имеют заказов, поэтому и фильтруем по заказам, 
чтобы получить имена
*/

--task 6
--✅ Задача: найти все заказы, где встречается товар Kindle.

SELECT 
	oi.order_id AS order_id,
	u.name AS user_name,
	p.name AS product_name,
	oi.quantity AS quantity
FROM order_items oi
JOIN orders o ON o.id = oi.order_id
JOIN users u ON o.user_id = u.id
JOIN products p ON p.id = oi.product_id
WHERE p.name = 'Kindle';

/*Ответь на вопросы:**
- Почему заказ `2` встречается в выдаче один раз?

Потому что внутри 2го заказа только 1 товар
*/

--task 7
--✅ Задача: вывести все товары и информацию о том, в каких заказах они 
--встречаются (если товар не покупали — тоже показать).

SELECT 
	p.name AS product_name,
	oi.order_id AS order_id,
	oi.quantity AS quantity
FROM products p
LEFT JOIN order_items oi ON p.id = oi.product_id
ORDER BY p.name;

/*❓ Ответь на вопросы:
что будет в order_id, если товар ни разу не покупали?
- в order_id будет NULL если товар ни разу не покупали

чем это отличается от варианта, если использовать INNER JOIN?
если использовать иннер, то в таком случае мы могли бы потерять из выборки 
те заказы, которые не покупали */

--task 8
--Задание 8: Только оплаченные заказы + их состав

SELECT 
	oi.order_id AS order_id,
	u.name AS user_name,
	p.name AS product_name,
	oi.quantity AS quantity,
	oi.price_at_purchase AS price_at_purchase
FROM order_items oi
JOIN orders o ON o.id = oi.order_id
JOIN products p ON p.id = oi.product_id
JOIN users u ON u.id = o.user_id
WHERE o.status = 'paid';

/**❓ Ответь на вопросы:**
- что будет, если условие **`status = 'paid'`** написать в **`JOIN`** с **`orders`** (там же, где **`ON`**)? Изменится ли результат сейчас и почему?

*/

--task 9
--Задание 9: Заказы пользователя Alice (все товары внутри)

SELECT 
	oi.order_id AS order_id,
	o.order_date AS order_date,
	p.name AS product_name,
	oi.quantity AS quantity,
	oi.price_at_purchase AS price_at_purchase
FROM order_items oi
JOIN orders o ON o.id = oi.order_id
JOIN products p ON p.id = oi.product_id
JOIN users u ON u.id = o.user_id
WHERE u.name = 'Alice';

/**❓ Ответь на вопросы:**
- если у Alice нет ни одного заказа, сколько строк вернёт твой запрос и почему?
В таком случае вернет 0 строк.*/

--task 10
--Задание 10: Заказы без товаров (проверка целостности)

SELECT 
	oi.order_id AS order_id,
	o.user_id AS user_id,
	o.status AS status,
	oi.product_id AS product
FROM orders o
LEFT JOIN order_items oi ON o.id = oi.order_id
LEFT JOIN products p ON p.id = oi.product_id
WHERE oi.product_id IS NULL;

/*
**❓ Ответь на вопросы:**
- Что будет, если вместо **`LEFT JOIN`** написать **`INNER JOIN`** — изменится ли результат и как.

В таком случае, пропаду пустые строки
*/