dz5

gistfile1.txt

-- 1
SELECT p.name AS Продукт,
  SUM(s.quantity) AS [Кол-во продаж]
FROM Product p
JOIN Sale s ON p.id = id_product
GROUP BY p.name
HAVING SUM(s.quantity) = 
  (SELECT MAX(total_sales)
  FROM (
    SELECT SUM(quantity) AS total_sales)
    FROM Sale
    GROUP BY id_product
  ) AS seles_per_product
)

-- 2
SELECT c.name AS Категория,
  SUM(s.quantity) AS Кол-во,
  (SUM(s.quantity) * 100.0 / (SELECT SUM(quantity) FROM 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

-- 3
SELECT s.name AS Поставщик
FROM Supplier s
WHERE s.id NOT IN (
  SELECT d.id_supplier
  FROM Delivery d
  JOIN Product p ON p.id = d.id_product
  WHERE p.name = 'yogurt'
)

-- 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 Producer pr ON p.id_producer = pr.id
    WHERE pr.name = 'Name'
)

-- 6
SELECT s.date_of_sale AS Дата,
	SUM(s.quantity) AS [Кол-во продаж]
FROM Sale s
JOIN Product p ON s.id_product = p.id
GROUP BY s.date_of_sale
ORDER BY s.date_of_sale DESC

-- CREATE TABLE

-- 1
CREATE TABLE Books(
id INT PRIMARY KEY AUTO_INCREMENT,
name nvarchar(50) NOT NULL,
FOREIGN KEY (id_theme) REFERENCES Themes(id),
FOREIGN KEY (id_author) REFERENCES Authors(id),
price REAL NOT NULL,
date_of_publish DATE NOT NULL)

CREATE TABLE Themes(
id INT PRIMARY KEY AUTO_INCREMENT,
name nvarchar(50) NOT NULL)

CREATE TABLE Authors(
id INT PRIMARY KEY AUTO_INCREMENT,
firstname nvarchar(50) NOT NULL,
lastname nvarchar(50) NOT NULL,
FOREIGN KEY (id_country) REFERENCES Country(id))

CREATE TABLE Country(
id INT PRIMARY KEY AUTO_INCREMENT,
name nvarchar(50) NOT NULL)

CREATE TABLE Shops(
id INT PRIMARY KEY AUTO_INCREMENT,
name nvarchar(50) NOT NULL,
FOREIGN KEY (id_country) REFERENCES Country(id))

CREATE TABLE Sales(
id INT PRIMARY KEY AUTO_INCREMENT,
FOREIGN KEY (id_book) REFERENCES Books(id),
date_of_sale DATE NOT NULL,
price REAL NOT NULL,
quantity SMALLINT NOT NULL,
FOREIGN KEY (id_shop) REFERENCES Shops(id))