use [master];
go

if db_id('Sales_db') is not null
begin
drop database [Sales_db];
end
go

create database [Sales_db];
go

use [Sales_db];
go
create table [Employees]
(
[Id] int not null identity(1, 1) primary key,
[Name] nvarchar(max) not null check ([Name] <> N''),
[Surname] nvarchar(max) not null check ([Surname] <> N''),
[Patronymic] nvarchar(max) not null check ([Patronymic] <> N''),
[Email] nvarchar(max) not null check ([Email]<>N''),
[Phone_Number] nvarchar(50) NOT NULL check (Phone_Number <> N''))
go
insert [Employees](Surname,Name,Patronymic,Email,Phone_Number)values
(N'Petrova',N'Maria',N'Leonidovna',N'masha@gmail.com',N'050-255-00-01'),
(N'Fokina',N'Elena',N'Alekseevna',N'lena@gmail.com',N'067-255-00-01'),
(N'Lapina',N'Olga',N'Ivanovna',N'olya@gmail.com',N'063-255-00-01')
go
create table [Clients]
(
[Id] int not null identity(1, 1) primary key,
[Name] nvarchar(max) not null check ([Name] <> N''),
[Surname] nvarchar(max) not null check ([Surname] <> N''),
[Patronymic] nvarchar(max) not null check ([Patronymic] <> N''),
[Email] nvarchar(max) not null check ([Email]<>N''),
[Phone_Number] nvarchar(50) NOT NULL check (Phone_Number <> N''))
go
insert [Clients](Surname,Name,Patronymic,Email,Phone_Number)values
(N'Ivanov',N'Ivan',N'Ivanovich',N'ivan@gmail.com',N'066-356-56-84'),
(N'Petrov',N'Petr',N'Petrovich',N'petr@gmail.com',N'068-351-45-81'),
(N'Sergeev',N'Sergey',N'Sergeevich',N'sergey@gmail.com',N'098-351-45-56'),
(N'Mihaylov',N'Michael',N'Mihaylovich',N'misha@gmail.com',N'095-125-56-87'),
(N'Pupkin',N'Vasya',N'Petrovich',N'vasya@gmail.com',N'050-258-45-54')
go
create table [SaleOfGoods]
(
[Id] int not null identity(1, 1) primary key,
[EmpId] int not null foreign key references [Employees]([Id]) on delete cascade on update cascade,
[ClientId] int not null foreign key references [Clients]([Id]) on delete cascade on update cascade,
[Product] nvarchar(max) not null check ([Product] <> N''),
[Price] money not null check ([Price] >= 0.0) default 0.0,
[SDate] date not null check (SDate <= getdate()) default getdate())
go
insert [SaleOfGoods](EmpId,ClientId,Product,Price,SDate)values
(1,1,N'Laptop',500,'2020-01-02'),(1,5,N'Book',10,'2020-08-22'),(2,3,N'TV',200,'2020-02-23'),
(3,4,N'Sneakers',100,'2020-10-25'),(2,4,N'Jacket',50,'2020-11-01'),(1,4,N'Smartphone',200,'2020-10-01'),
(2,5,N'Chair',100,'2020-09-28'),(1,2,N'Glasses',50,'2020-07-23'),(1,3,N'Dress',100,'2020-03-17')
go