gistfile1.txt

--INNER JOINS:
 
--1. Показать названия и категории товаров, поставщиками которых являются MSI или Gigabyte
USE Warehouse

SELECT p.name AS Товар,
	   c.name AS Категория,
	   pr.name AS Производитель

FROM Product p JOIN Category c ON p.category_id = c.id
JOIN Producer pr ON pr.id=p.producer_id WHERE pr.name IN('MSI', 'Gigabyte')
 
--2. Выбрать все продажи (в связи с тем что у меня немного иная структура базы), имя производителя которых не содержит букв [MSI], и категория которых не "GPU"
SELECT p.name AS Товар,
	   c.name AS Категория,
	   pr.name AS Производитель,
	   s.date_of_sale AS продажа

FROM Product p 
JOIN Sale s ON p.id = s.product_id
JOIN Producer pr ON p.producer_id=pr.id
JOIN Category c ON c.id = p.category_id 

WHERE pr.name NOT LIKE 'MSI' AND c.name NOT LIKE 'GPU'

--3. Показать названия и категории товаров с указанием поставщика и страны производителя. Условие: страна производителя не Германия,
--цена поставки менее 500 уе, а дата поставки от 10.02.2025 и по сей день
SELECT p.name AS Товар,
	   c.name AS Категория,
	   co.name AS Страна_Производителя,
	   d.date_of_delivery AS дата_доставки

FROM Product p 
JOIN Sale s ON p.id = s.product_id
JOIN Producer pr ON p.producer_id = pr.id
JOIN Category c ON c.id = p.category_id 
JOIN Delivery d ON s.delivery_id = d.id
JOIN Supplier sup ON sup.id = d.supplier_id
JOIN Address ad ON pr.address_id = ad.id
JOIN City ci ON ad.city_id=ci.id
JOIN Region reg ON ci.region_id = reg.id
JOIN Country co ON reg.country_id = co.id

WHERE d.price < 500 AND co.name NOT LIKE 'Deutschland' AND d.date_of_delivery > CAST('09.02.2025' AS datetime)

--4. Показать все GPU и CPU, которых было продано более 5. Показать информацию о поставщике и производителе
SELECT p.name AS Товар,
	   c.name AS Категория,
	   pr.name AS Производитель,
	   sup.name AS поставщик

FROM Product p 
JOIN Sale s ON p.id = s.product_id
JOIN Producer pr ON p.producer_id = pr.id
JOIN Category c ON c.id = p.category_id 
JOIN Delivery d ON s.delivery_id = d.id
JOIN Supplier sup ON sup.id = d.supplier_id

WHERE c.name IN ('CPU', 'GPU') AND s.quantity>5

--5. Сделать запрос на выборку информации о поставке товаров в следующем виде: название товара и его поставщика, категории,
--дата поставки и общую стоимость поставленных товаров. Условие: только трёх указанных в запросе поставщиков.
--Отсортировать названия товаров в алфавитном порядке
SELECT p.name AS Товар,
	   sup.name AS Поставщик,
	   c.name AS Категория,
	   d.date_of_delivery Доставка,
	   (s.price_product * s.quantity) AS Сумма

FROM Product p 
JOIN Sale s ON p.id = s.product_id
JOIN Producer pr ON p.producer_id = pr.id
JOIN Category c ON c.id = p.category_id 
JOIN Delivery d ON s.delivery_id = d.id
JOIN Supplier sup ON sup.id = d.supplier_id

WHERE sup.name LIKE 'DSV' OR sup.name LIKE 'GTS' OR sup.name LIKE 'VVH'
ORDER BY p.name ASC


--6. Сделать запрос на выборку о продажах товаров в следующем виде: название товара и его производителя, полный адрес
--(страна, город, улица) производителя в одной ячейке, категории, дате продажи и общей стоимости продажи.
--Условие: выведенная информация не должна касаться двух указанных в запросе производителей.
--Отсортировать общую стоимость продаж в порядке убывания
SELECT p.name AS Товар,
	   CAST((pr.name+' '+co.name+' '+reg.name+' '+ci.name+' '+ad.street) AS nvarchar(55)) AS Производитель,
	   c.name AS Категория,
	   s.date_of_sale AS дата_продажи,
	   s.price_product * s.quantity AS Сумма

FROM Product p 
JOIN Sale s ON p.id = s.product_id
JOIN Producer pr ON p.producer_id = pr.id
JOIN Category c ON c.id = p.category_id 
JOIN Address ad ON pr.address_id = ad.id
JOIN City ci ON ad.city_id=ci.id
JOIN Region reg ON ci.region_id = reg.id
JOIN Country co ON reg.country_id = co.id

WHERE pr.name  NOT LIKE '%LG%' AND pr.name NOT LIKE '%Asus%'
ORDER BY s.price_product * s.quantity DESC
 
--=======================================================================================================================
 
--OUTER JOINS:
 
--1. Показать названия товаров и их производителей, но и в том числе тех производителей, у которых нет товаров
SELECT p.name AS Товар,
	   pr.name AS Производитель

FROM Product p 
RIGHT JOIN Producer pr ON p.producer_id = pr.id

 
--2. Показать только те категории, к которым не относится ни один товар
SELECT --p.name AS Товар,
	   c.name AS Категория

FROM Category c
LEFT JOIN Product p ON p.category_id = c.id
WHERE p.name IS NULL

 
--3. Показать названия товаров, даты их поставки и поставщиков, в том числе тех поставщиков, которые ничего не успели поставить
SELECT p.name AS Товар,
	   d.date_of_delivery AS доставка,
	   sup.name AS Поставщик

FROM Product p 
JOIN SALE s ON s.product_id = p.id
JOIN Delivery d ON d.id = s.delivery_id
RIGHT JOIN Supplier sup ON sup.id = d.supplier_id
 
--4. Показать области (регионы), в которых нет ни одного производителя (EXCEPT)
SELECT name
FROM Region 
EXCEPT
SELECT DISTINCT r.name --, p.name
FROM Region r JOIN City c ON c.region_id = r.id
JOIN Address a ON a.city_id = c.id
JOIN Producer p ON p.address_id = a.id
 
--5. Показать те названия категорий, где нет товаров фирмы GIGABYTE
 SELECT name
 FROM Category
 EXCEPT
 SELECT c.name
 FROM Category c
 JOIN Product p ON p.category_id = c.id
 JOIN Producer pr ON p.producer_id = pr.id
 WHERE pr.name LIKE '%GIGABYTE%'

--6. Показать производителей, которые не выпускают продукты GPU (EXCEPT)
SELECT pr.name
FROM Producer pr
JOIN Product p ON p.producer_id = pr.id 
JOIN Category c ON p.category_id = c.id
EXCEPT
SELECT pr.name
FROM Product p
JOIN Category c ON p.category_id = c.id
JOIN Producer pr ON pr.id=p.producer_id
WHERE c.name LIKE 'GPU'