hw

USE Storik

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

------------------------------------

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. Показать названия поставщиков, которые не поставляли iPhone
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 '%iPhone%')


-- 5. Показать всех производителей, количество товаров которых в магазине больше, чем количество наименований
-- всех товаров фирмы  «Apple»
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 = 'Apple'
)


-- 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 Library

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))
  
--------------------------------------------------------------------------------
USE Storik

-- 1. Показать все продажи, где есть продукты из отдела "Smartphone".
CREATE VIEW PhoneSales AS
	SELECT p.name AS product_name, c.name AS category_name, s.quantity AS sale_quantity
	FROM Product p
	JOIN Category c ON p.id_category = c.id
	JOIN Sale s ON s.id_product = p.id
	WHERE c.name LIKE '%Smartphone%'

SELECT * FROM PhoneSales


-- 2. Показать все продукты из категории "Smartphone", у которых количество на складе меньше 150 единиц.
CREATE VIEW PhonesLess50 AS
SELECT p.name AS product_name, p.quantity AS product_quantity, c.name AS category_name
FROM Product p
	JOIN Category c ON p.id_category = c.id
	WHERE c.name LIKE '%Smartphone%' AND p.quantity < 150

SELECT * FROM PhonesLess50


-- 3. Показать наиболее продаваемые товары из отдела "Smartphone" за последние 3 месяца.
CREATE VIEW TopSalesPhone AS
SELECT TOP 1 p.name AS product_name, c.name AS category_name, SUM(s.quantity) AS total_sales, s.date_of_sale AS sale_date
FROM Product p
JOIN Category c ON p.id_category = c.id
JOIN Sale s ON s.id_product = p.id
-- WHERE c.name LIKE '%Smartphone%' AND s.date_of_sale BETWEEN '2024-11-01' AND CAST(GETDATE() AS DATE)
WHERE c.name LIKE '%Smartphone%' AND s.date_of_sale BETWEEN DATEADD(MONTH, -3, GETDATE()) AND GETDATE()
GROUP BY p.name, c.name, s.date_of_sale
ORDER BY total_sales DESC

SELECT * FROM TopSalesPhone