· 5 years ago · Nov 23, 2020, 05:28 PM
1use [master]
2drop database if exists [Test];
3create database [Test];
4use [Test];
5
6create table [Tenant] (
7 [ArN] integer identity(1,1) not null,
8
9 [LName] varchar(20) not null,
10 [FName] varchar(20) not null,
11 [MName] varchar(20) not null,
12 [Pasport] char(11) not null,
13 [Addr] varchar(50),
14
15 constraint AK1_Pasport unique ([Pasport]),
16
17 primary key([ArN])
18);
19create table [Office] (
20 [RecN] integer identity(1,1) not null,
21
22 [Offcode] integer not null,
23 [City] varchar(20) not null,
24 [RName] varchar(20) not null,
25 [Addr] varchar(20) not null,
26
27 constraint AK1_Offcode unique ([Offcode]),
28
29 primary key([RecN])
30);
31create table [Car] (
32 [CarN] integer identity(1,1) not null,
33
34 [RegNum] date not null,
35 [Model] varchar(20) not null,
36 [RevDate] date not null,
37 [DailyPay] money not null,
38
39 constraint AK1_RegNum unique ([RegNum]),
40
41 primary key([CarN])
42);
43create table [Contract] (
44 [InvN] integer identity(1,1) not null,
45
46 [ArN] integer not null,
47 [GetRecN] integer not null,
48 [RetRecN] integer not null,
49 [CarN] integer not null,
50
51 [GetDate] date not null,
52 [PlanDays] integer not null,
53 [OverDays] integer,
54 [Fine] money
55
56 primary key([InvN])
57);
58
59alter table [Contract] add foreign key ([ArN]) references [Tenant]([ArN]);
60alter table [Contract] add foreign key ([GetRecN]) references [Office]([RecN]);
61alter table [Contract] add foreign key ([RetRecN]) references [Office]([RecN]);
62alter table [Contract] add foreign key ([CarN]) references [Car]([CarN]);
63
64insert into [Tenant] ([LName] , [FName] , [MName] , [Pasport] , [Addr])
65 values('Vadim' , 'Rubalkin' , 'Ne pomny' , '11 232211' , 'ул. красивая, 4'),
66 ('Ilya' , 'Skoropad' , 'Igorevich' , '11 362638' , 'ул. красивая, 2'),
67 ('Anastsiya' , 'Chernikova' , 'Ne pomny' , '11 128473' , 'ул. красивая, 8'),
68 ('Anton' , 'Solonnikov' , 'Ne pomny' , '11 219393' , 'ул. красивая, 9'),
69 ('Andrey' , 'Orlov' , 'Ne pomny' , '11 384392' , 'ул. красивая, 3'),
70 ('Maria' , 'Luchko' , 'Ne pomny' , '11 323292' , 'ул. красивая, 7'),
71 ('Ivan' , 'Truhin' , 'Ne pomny' , '11 111111' , 'ул. красивая, 1');
72
73insert into [Office] ([Offcode], [City] , [RName] , [Addr] )
74 values(111111 , 'Moscov' , 'Peter' , '....., 1'),
75 (111112 , 'Vologda' , 'Dmitry' , '....., 2'),
76 (111113 , 'Piter' , 'Viktor' , '....., 1'),
77 (111114 , 'Ryazan' , 'Yod' , '....., 4'),
78 (111115 , 'Pskov' , 'Avaria' , '....., 3'),
79 (111116 , 'Vladivostok' , 'Uran' , '....., 1'),
80 (111117 , 'cheta' , 'Plutoniy' , '....., 2');
81
82insert into [Car] ([RegNum] , [Model] , [RevDate] , [DailyPay])
83 values('120325' , 'Tayota selica' , '200920' , 300.25 ),
84 ('121012' , 'lotus' , '200921' , 100.25 ),
85 ('110502' , 'jaguar' , '200921' , 200.25 ),
86 ('141020' , 'honda' , '200922' , 300.25 ),
87 ('121018' , 'volvo' , '200923' , 430.25 ),
88 ('121010' , 'lada 1001' , '200922' , 3.25 ),
89 ('200610' , 'bugatty cheron' , '200921' , 700.00 ),
90 ('200611' , 'tesla' , '200911' , 400.00 ),
91 ('200613' , 'mini cuper' , '200921' , 150.40 ),
92 ('191022' , 'lada granda' , '200920' , 1500 );
93
94
95insert into [Contract] ([ArN] , [GetRecN] , [RetRecN] , [CarN] , [GetDate] , [PlanDays], [OverDays], [Fine] )
96 values(1 , 1 , 1 , 8 , '200921' , 30 , 0 , 0.0 ),
97 (3 , 1 , 1 , 9 , getdate() , 10 , 0 , 0.0 ),
98 (2 , 1 , 1 , 6 , getdate() , 1 , 40 , 5000.0 ),
99 (5 , 1 , 1 , 7 , getdate() , 365 , 0 , 0.0 ),
100 (4 , 1 , 1 , 3 , getdate() , 700 , 1500 , 0.0 ),
101 (6 , 1 , 1 , 7 , getdate() , 999999 , 0 , 0.0 ),
102 (7 , 1 , 1 , 1 , getdate() , 730 , 150 , 9494.0 ),
103 (7 , 1 , 1 , 4 , getdate() , 150 , 50 , 2000.0 ),
104 (4 , 1 , 1 , 10 , getdate() , 2 , 679 , 9999999.0 );
105
106select
107 ten.FName
108 ,ten.LName
109 ,car.Model
110 ,car.DailyPay
111 ,goc.City as [get office]
112 ,roc.City as [ret office]
113 ,con.PlanDays
114 ,con.OverDays
115from
116 [Contract] as con
117 inner join [Tenant] as ten on con.ArN = ten.ArN
118 inner join [Car] as car on car.CarN = con.CarN
119 inner join [Office] as goc on goc.RecN = con.GetRecN
120 inner join [Office] as roc on roc.RecN = con.RetRecN
121