hw_subqueries

hw_subqueries.txt

-- 1. Показать самый популярный товар магазина (больше всего раз продавался)
SELECT 
    p.name AS product_name,
    SUM(s.quantity) AS total_sales
FROM Product p
JOIN Sale s ON p.id = s.id_product
GROUP BY p.name
HAVING SUM(s.quantity) = (
    SELECT MAX(total_sales)
    FROM (
        SELECT SUM(s.quantity) AS total_sales
        FROM Sale s
        JOIN Product p ON p.id = s.id_product
        GROUP BY p.id
    ) AS sales_summary
)
-- 2. Если общее количество товаров всех категорий принять за 100%, необходимо посчитать, сколько товаров каждой категории(в процентном отношении) было продано
SELECT 
	c.name, 
	COUNT(p.name) AS [product_count], 
	(SELECT COUNT(1) FROM Product) AS total,
	CAST(ROUND(CAST(COUNT(p.name) AS float) * 100 / (SELECT COUNT(1) FROM Product), 2) AS nvarchar) + '%' AS total
FROM Category c
FULL JOIN Product p ON p.id_category = c.id
GROUP BY c.name
ORDER BY 2 DESC


-- 3. Показать названия поставщиков, которые не поставляли йогурт
SELECT name
FROM Supplier
WHERE id NOT IN (
	SELECT d.id_supplier
	FROM Delivery d
	JOIN Product p ON p.id = d.id_product
	WHERE p.name LIKE '%Йогурт%')

-- 5. Показать всех производителей, количество товаров которых в магазине больше, чем количество наименований всех товаров фирмы  «ЭкоВода»
SELECT pr.name AS producer_name, p.quantity AS product_quantity
FROM Producer pr
JOIN Product p ON p.id_producer = pr.id
GROUP BY pr.id, pr.name, p.quantity
HAVING SUM(p.quantity) > (
    SELECT SUM(p.quantity)
    FROM Product p
    JOIN Producer pr ON p.id_producer = pr.id
    WHERE pr.name = 'ЭкоВода'
)


-- 6. Показать общее количество продаж по каждому дню, начиная от 2025-02-01, и по сей день. Отсортировать по убыванию даты
SELECT SUM(s.quantity), s.date_of_sale
FROM Sale s
WHERE s.date_of_sale BETWEEN '2025-02-01' AND CAST(GETDATE() AS DATE)
GROUP BY s.date_of_sale
ORDER BY s.date_of_sale DESC

======================================================================

CREATE DATABASE BooksDB

USE Library

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

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

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

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

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,
	date_of_publish DATE,
	pages int)

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