CREATE DATABASE db

USE db;

CREATE TABLE Users(
User_ID INT IDENTITY(1,1) PRIMARY KEY,
User_Name VARCHAR(300) NOT NULL
);

CREATE TABLE Products(
Product_ID INT IDENTITY(1,1) PRIMARY KEY,
Product_Name VARCHAR(200) NOT NULL
);

CREATE TABLE Orders(
Order_ID INT IDENTITY(1,1) PRIMARY KEY,
User_ID INT FOREIGN KEY REFERENCES Users(User_ID) ON UPDATE CASCADE ON DELETE CASCADE,
Order_Date DATETIME NOT NULL
);

CREATE TABLE Pavments(
Payment_ID INT IDENTITY(1,1) PRIMARY KEY,
Order_ID INT FOREIGN KEY REFERENCES Orders(Order_ID) ON UPDATE CASCADE ON DELETE CASCADE,
Payment_Date DATETIME NOT NULL
);

CREATE TABLE OrderDetails(
Order_Detail_ID INT IDENTITY(1,1) PRIMARY KEY,
Order_ID INT FOREIGN KEY REFERENCES Orders(Order_ID) ON UPDATE CASCADE ON DELETE CASCADE,
Product_ID INT FOREIGN KEY REFERENCES Products(Product_ID) ON UPDATE CASCADE ON DELETE CASCADE,
Quantity VARCHAR(300) NOT NULL
);

Use db;

INSERT INTO Users (User_Name)
VALUES ('Лев Толстой'),
       ('Николай Гоголь'),
       ('Фёдор Достоевский');

INSERT INTO Products (Product_Name)
VALUES ('Война и мир'),
       ('Мёртвые души'),
       ('Преступление и наказание');

INSERT INTO Orders (Order_Date, User_ID)
VALUES ('15.03.1867', 1),
       ('05.04.1842', 2),
       ('07.12.1866', 3);

INSERT INTO OrderDetails (Quantity, Order_ID, Product_ID)
VALUES ('8', 1, 1),
       ('15', 2, 2),
       ('25', 3, 3);

INSERT INTO Pavments (Payment_Date, Order_ID)
VALUES ('12.12.2022', 1),
       ('05.04.2023', 2),
       ('07.12.2021', 3);