Subqueries-DDL

Subqueries-DDL.sql

--SUBQUERIES (в решении обязательно использовать минимум один подзапрос!):
--1. Показать самый популярный товар магазина (больше всего раз продавался)
--SELECT TOP 1 
--	p.name AS товар, 
--	(SELECT SUM(s.quantity) 
--	FROM Sale s 
--	WHERE s.id_product = p.id) AS количество
--FROM Product p
--ORDER BY количество DESC

--2. Если общее количество товаров всех категорий принять за 100%, необходимо посчитать, сколько товаров каждой категории (в процентном отношении) было продано
--SELECT 
--	c.name AS категория,
--	COUNT(s.id_product) AS "количество проданных товаров", 
--	(SELECT COUNT(1) FROM Product) AS "общее количество товаров",
--	CAST(CAST(COUNT(s.id_product) * 100.0 / (SELECT COUNT(1) FROM Product) AS DECIMAL(10,2)) AS NVARCHAR) + '%' AS "процент от общего количества"
--FROM Category c
--LEFT JOIN Product p ON p.id_category = c.id
--LEFT JOIN Sale s ON s.id_product = p.id
--GROUP BY c.name
--ORDER BY "процент от общего количества" DESC

--3. Показать названия поставщиков, которые не поставляли йогурт
--SELECT s.name AS поставщик
--FROM Supplier s
--WHERE NOT EXISTS
--	(SELECT *
--	FROM Product p
--	WHERE p.id_supplier = s.id
--	AND p.name LIKE '%йогурт%')
	
--4*. Показать на экран список производителей, которые живут в той же стране, что и фирма 000 "Зеленоглазое такси"
--SELECT pr.name AS производитель
--FROM Producer pr
--WHERE pr.id_country = (
--	SELECT s.id_country
--	FROM Supplier s
--	WHERE s.name = 'Tea Lovers')

--5. Показать всех производителей, количество наименований товаров которых в магазине больше, чем количество наименований всех товаров фирмы ООО «Самтаймс»
--SELECT 
--	pr.name AS производитель,
--	COUNT(p.id) AS "количество товаров"
--FROM Producer pr
--JOIN Product p ON p.id_producer = pr.id
--GROUP BY pr.name
--HAVING COUNT(p.id) > (
--	SELECT COUNT(p.id)
--	FROM Product p
--	JOIN Supplier s ON s.id = p.id_supplier
--	WHERE s.name = 'Tea Lovers')

--6. Показать общее количество продаж по каждому дню, начиная от 15.11.2024, и по сей день. Отсортировать по убыванию даты 
--SELECT 
--	s.date_of_sale AS дата,
--	SUM(s.quantity) AS "количество продаж"
--FROM Sale s
--WHERE s.date_of_sale >= '2024-11-15'
--GROUP BY s.date_of_sale
--ORDER BY s.date_of_sale DESC

--7*. Вычислить количество товаров каждой категории, которые пора списать (их количество известно, и они не продавались вот уже три месяца)
--SELECT c.name AS категория,
--	COUNT(p.id) AS "количество товаров", 
--	MAX(s.date_of_sale) AS "последняя дата продажи"
--FROM Category c
--JOIN Product p ON p.id_category = c.id
--JOIN Sale s ON s.id_product = p.id
--GROUP BY c.name
--HAVING MAX(s.date_of_sale) < DATEADD(MONTH, -3, GETDATE())
--ORDER BY "количество товаров" DESC

--CREATE TABLE:
--1. написать запросы на создание шести таблиц БД Издательства (картинка прилагается тут)
CREATE DATABASE Publishing
GO -- нужен для создания базы
USE Publishing

--Country
CREATE TABLE Country (
	id int PRIMARY KEY identity(1, 1),
	name nvarchar(50) UNIQUE
)

--Themes
CREATE TABLE Themes (
	id int PRIMARY KEY identity(1, 1),
	name nvarchar(50) UNIQUE
)

--Authors
CREATE TABLE Authors (
	id int PRIMARY KEY identity(1, 1),
	firstname nvarchar(50) UNIQUE,
	lastname nvarchar(50) UNIQUE,
	id_country int REFERENCES Country(id)
)

--Shops
CREATE TABLE Shops (
	id int PRIMARY KEY identity(1, 1),
	name nvarchar(50) UNIQUE,
	id_country int REFERENCES Country(id)
)

--Books
CREATE TABLE Books (
	id int PRIMARY KEY identity(1, 1),
	name nvarchar(50) UNIQUE,
	id_theme int REFERENCES Themes(id),
	id_author int REFERENCES Authors(id),
	price money NOT NULL,
	date_of_publish date NOT NULL,
	pages int NOT NULL
)

--Sales
CREATE TABLE Sales (
	id int PRIMARY KEY identity(1, 1),
	id_book int REFERENCES Books(id),
	date_of_sale date NOT NULL,
	price money NOT NULL,
	quantity int NOT NULL,
	id_shop int REFERENCES Shops(id)
)