дз 7

7.sql

11111

--SELECT p.name AS [названные],
--    (
--	SELECT COUNT(1) 
--    FROM Sale s 
--    WHERE s.id_product = p.id) AS [количество]
--FROM Product p
--WHERE p.id IN 
--(
--    SELECT TOP 1 id_product
--    FROM Sale
--    WHERE id_product IS NOT NULL
--    GROUP BY id_product
--    ORDER BY COUNT(1) DESC
--)

2222



--SELECT 
--    c.name AS [категория],
--    COUNT(p.id) AS [количество],
--    CAST(
	
--	COUNT(p.id) * 100.0 / 
--	(SELECT COUNT(1) FROM Product)
--	AS DECIMAL(10,2)
	
	
--	) AS [процент]
	
--FROM Category c
--LEFT JOIN Product p ON p.id_category = c.id
--GROUP BY c.name
--ORDER BY [процент] DESC




3333

--SELECT DISTINCT s.name AS [поставщики]
--FROM Supplier s

--WHERE s.id NOT IN 
--(
--    SELECT DISTINCT d.id_supplier
--    FROM Delivery d
--    JOIN Product p  ON d.id_product = p.id
--    WHERE p.name LIKE '%Йогурт%'
--)
--ORDER BY s.name



555555

--SELECT  p1.name AS [производитель],
--    COUNT(prod.id) AS [количество товаров]

--FROM Producer p1
--JOIN Product prod ON prod.id_producer = p1.id
--GROUP BY p1.name
--HAVING COUNT(prod.id) > 
--(
--    SELECT COUNT(produc.id)
--    FROM Producer p2
--    JOIN Product produc ON produc.id_producer = p2.id
--    WHERE p2.name = 'ООО «Самтаймс»'
--)
--ORDER BY COUNT(prod.id) DESC




66666

--SELECT s.date_of_sale AS [дата],
--    COUNT(s.id) AS [количество продаж]

--FROM Sale s
--WHERE s.date_of_sale >= '2024-11-15' 
--    AND s.date_of_sale <= GETDATE()

--GROUP BY s.date_of_sale
--ORDER BY s.date_of_sale DESC













CREATE DATABASE Publishing_house

GO
USE Publishing_house

GO
CREATE TABLE Themes (
	id int PRIMARY KEY identity(1, 1),
	name nvarchar(50) NOT NULL
)
 
 
CREATE TABLE Country (
	id int PRIMARY KEY identity(1, 1),
	name nvarchar(50) NOT NULL
	
)

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

)

CREATE TABLE Shops (
	id int PRIMARY KEY identity(1, 1),
	name nvarchar(100) NOT NULL,
	id_country int REFERENCES Country(id)

)


CREATE TABLE Books (
	id int PRIMARY KEY identity(1, 1),
	name nvarchar(200) NOT NULL,
	id_theme int REFERENCES Themes(id),
	id_author int REFERENCES Authors(id),
	price decimal(10,2) NOT NULL,
	date_of_publish date,
	pages int

)

CREATE TABLE Sales (
	id int PRIMARY KEY identity(1, 1),

	id_book int REFERENCES Books(id),
	date_of_publish date NOT NULL,
	price decimal(10,2) NOT NULL,
	quantity int NOT NULL,
	id_shop int REFERENCES Shops(id)

)