CREATE DATABASE dlyapc;

USE dlyapc

CREATE TABLE Catalogs(
Id_Catalog INT IDENTITY(1,1) PRIMARY KEY,
Name VARCHAR(300) NOT NULL

);

CREATE TABLE Products(
ID_Product INT IDENTITY(1,1) PRIMARY KEY,
Name VARCHAR(300) NOT NULL,
Price MONEY NOT NULL,
Count SMALLINT NOT NULL,
Mark FLOAT NOT NULL,
Description VARCHAR(300) NOT NULL,
Id_Catalog INT,
FOREIGN KEY (Id_Catalog) REFERENCES catalogs(Id_Catalog) ON UPDATE CASCADE ON DELETE CASCADE

);

CREATE TABLE Users(
ID_User INT IDENTITY(1,1) PRIMARY KEY,
Surname VARCHAR(300) NOT NULL,
Paronymic VARCHAR(300) NOT NULL,
Name VARCHAR(300) NOT NULL,
Phone VARCHAR(300) NOT NULL,
Email VARCHAR(100) NOT NULL,
Url VARCHAR(300) NOT NULL,
UserStatus VARCHAR(300) NOT NULL
);

CREATE TABLE Orders(
ID_Order INT IDENTITY(1,1) PRIMARY KEY,
ID_User INT FOREIGN KEY REFERENCES Users(ID_User) ON UPDATE CASCADE ON DELETE CASCADE,
OrderTime DATETIME NOT NULL,
Number VARCHAR(200) NOT NULL,
Id_Product INT,
FOREIGN KEY (Id_Product) REFERENCES products(Id_Product) ON UPDATE CASCADE ON DELETE CASCADE

);

USE dlyapc

INSERT INTO Catalogs (Name)
VALUES
('Процессоры'),
('Материнские платы'),
('Видеоадаптеры'),
('Жёсткие диски'),
('Оперативная память'),
('Мониторы'),
('Мыши'),
('Клавиатуры'),
('SSD диски'),
('Флешки');


INSERT INTO Products (Name, Price, Count, Mark, Description, Id_Catalog)
VALUES
('Celeron 1.8GHz', 1995, 10, 3.6,'Процессор 1',1),
('Celeron 2.0GHz', 1969, 2, 3.7,'Процессор 2',2),
('Celeron 2.4GHz', 2109, 42, 3.9,'Процессор 3',3),
('Celeron D 320 2.4GHz', 1962, 32, 4.1, 'Процессор 4',4),
('Celeron D 325 2.5GHz', 2732, 33, 4.3, 'Процессор 5',5),
('Celeron D 320 2.4GHz', 1921, 31, 4.4, 'Процессор 6',6),
('Celeron D 325 2.5GHz', 2711, 41, 4.5, 'Процессор 7',7),
('Celeron D 330 3.0GHz', 1922, 12, 5.1, 'Процессор 8',8),
('Celeron D 335 3.5GHz', 2733, 32, 6.1, 'Процессор 9',9),
('Celeron D 335 3.5GHz', 2733, 12, 6.2, 'Процессор 10',10);

INSERT INTO Users (Surname, Paronymic, Name, Phone, Email, Url, UserStatus)
VALUES
('Иванов', 'Валерьевич', 'Александр', '80-08-00', 'Ivanov@mail.ru', 'https://support.microsoft.com/', 'active'),
('Прокопчук', 'Иванович', 'Сергей', '90512352', 'pro@mail.ru', 'https://support.microsoft.com/', 'active'),
('Семёнов', 'Вячеславович', 'Игорь', '93132322', 'symdyanov@mail.ru', 'https://support.microsoft.com/', 'Pasive'),
('Петров', 'Валерьевич', 'Максим', '1,4.1,20', 'Kuznetsov@mail.ru', 'https://support.microsoft.com/', 'active'),
('Лосев', 'Юрьевич', 'Анатолий', '6,4.1,20', 'Losev@mail.ru', 'https://support.microsoft.com/', 'Pasive'),
('Травкин', 'Владимирович', 'Егор', '1,4.1,20', 'Kuznetsov@mail.ru', 'https://support.microsoft.com/', 'active'),
('Токарев', 'Саныч', 'Денис', '6,4.1,20', 'Losev@mail.ru', 'https://support.microsoft.com/', 'Pasive'),
('Петров', 'Артёмович', 'Данил', '1,4.1,20', 'Kuznetsov@mail.ru', 'https://support.microsoft.com/', 'active'),
('Антропов', 'Денисович', 'Олег', '6,4.1,20', 'Losev@mail.ru', 'https://support.microsoft.com/', 'Pasive'),
('Петров', 'Валерьевич', 'Максим', '1,4.1,20', 'Kuznetsov@mail.ru', 'https://support.microsoft.com/', 'active');

INSERT INTO Orders (OrderTime, Id_User, Number, Id_Product)
VALUES
('01-04-2005 10:39:38',1,1,1),
('02-10-2005 09:40:29',2,2,2),
('18-02-2005 13:41:05',3,3,3),
('03-10-2005 18:20:00',4,4,4),
('17-03-2005 19:15:36',5,5,5),
('11-04-2005 12:32:32',6,6,6),
('03-10-2005 02:42:23',7,7,7),
('14-02-2005 11:42:01',8,8,8),
('05-10-2005 14:22:00',9,9,9),
('11-03-2005 15:12:36',10,10,10);