kr-3-sql

use [master]
drop database if exists [Test];
create database [Test];
use [Test];

create table [Tenant] (
    [ArN]           integer identity(1,1) not null,

    [LName]         varchar(20)           not null,
    [FName]         varchar(20)           not null,
    [MName]         varchar(20)           not null,
    [Pasport]       char(11)              not null,
    [Addr]          varchar(50),

    constraint AK1_Pasport  unique ([Pasport]),

    primary key([ArN])
);
create table [Office] (
    [RecN]          integer identity(1,1) not null,

    [Offcode]       integer               not null,
    [City]          varchar(20)           not null,
    [RName]         varchar(20)           not null,
    [Addr]          varchar(20)           not null,

    constraint AK1_Offcode unique ([Offcode]),

    primary key([RecN])
);
create table [Car] (
    [CarN]          integer identity(1,1) not null,

    [RegNum]        date                  not null,
    [Model]         varchar(20)           not null,
    [RevDate]       date                  not null,
    [DailyPay]      money                 not null,

    constraint AK1_RegNum unique ([RegNum]),

    primary key([CarN])
);
create table [Contract] (
    [InvN]          integer identity(1,1) not null,

    [ArN]           integer not null,
    [GetRecN]       integer not null,
    [RetRecN]       integer not null,
    [CarN]          integer not null,

    [GetDate]       date    not null,
    [PlanDays]      integer not null,
    [OverDays]      integer,
    [Fine]          money

    primary key([InvN])
);

alter table [Contract] add foreign key ([ArN]) references [Tenant]([ArN]);
alter table [Contract] add foreign key ([GetRecN]) references [Office]([RecN]);
alter table [Contract] add foreign key ([RetRecN]) references [Office]([RecN]);
alter table [Contract] add foreign key ([CarN]) references [Car]([CarN]);

insert into [Tenant] ([LName]     , [FName]      , [MName]     , [Pasport]   , [Addr])
               values('Vadim'     , 'Rubalkin'   , 'Ne pomny'  , '11 232211' , 'ул. красивая, 4'),
                     ('Ilya'      , 'Skoropad'   , 'Igorevich' , '11 362638' , 'ул. красивая, 2'),
                     ('Anastsiya' , 'Chernikova' , 'Ne pomny'  , '11 128473' , 'ул. красивая, 8'),
                     ('Anton'     , 'Solonnikov' , 'Ne pomny'  , '11 219393' , 'ул. красивая, 9'),
                     ('Andrey'    , 'Orlov'      , 'Ne pomny'  , '11 384392' , 'ул. красивая, 3'),
                     ('Maria'     , 'Luchko'     , 'Ne pomny'  , '11 323292' , 'ул. красивая, 7'),
                     ('Ivan'      , 'Truhin'     , 'Ne pomny'  , '11 111111' , 'ул. красивая, 1');

insert into [Office] ([Offcode], [City]        , [RName]    , [Addr]    )
               values(111111   , 'Moscov'      , 'Peter'    , '....., 1'),
                     (111112   , 'Vologda'     , 'Dmitry'   , '....., 2'),
                     (111113   , 'Piter'       , 'Viktor'   , '....., 1'),
                     (111114   , 'Ryazan'      , 'Yod'      , '....., 4'),
                     (111115   , 'Pskov'       , 'Avaria'   , '....., 3'),
                     (111116   , 'Vladivostok' , 'Uran'     , '....., 1'),
                     (111117   , 'cheta'       , 'Plutoniy' , '....., 2');

insert into [Car] ([RegNum] , [Model]          , [RevDate] , [DailyPay])
            values('120325' , 'Tayota selica'  , '200920'  , 300.25    ),
                  ('121012' , 'lotus'          , '200921'  , 100.25    ),
                  ('110502' , 'jaguar'         , '200921'  , 200.25    ),
                  ('141020' , 'honda'          , '200922'  , 300.25    ),
                  ('121018' , 'volvo'          , '200923'  , 430.25    ),
                  ('121010' , 'lada 1001'      , '200922'  , 3.25      ),
                  ('200610' , 'bugatty cheron' , '200921'  , 700.00    ),
                  ('200611' , 'tesla'          , '200911'  , 400.00    ),
                  ('200613' , 'mini cuper'     , '200921'  , 150.40    ),
                  ('191022' , 'lada granda'    , '200920'  , 1500      );


insert into [Contract] ([ArN] , [GetRecN] , [RetRecN] , [CarN] , [GetDate] , [PlanDays], [OverDays], [Fine]     )
                 values(1     , 1         , 1         , 8      , '200921'  , 30        , 0         , 0.0        ),
                       (3     , 1         , 1         , 9      , getdate() , 10        , 0         , 0.0        ),
                       (2     , 1         , 1         , 6      , getdate() , 1         , 40        , 5000.0     ),
                       (5     , 1         , 1         , 7      , getdate() , 365       , 0         , 0.0        ),
                       (4     , 1         , 1         , 3      , getdate() , 700       , 1500      , 0.0        ),
                       (6     , 1         , 1         , 7      , getdate() , 999999    , 0         , 0.0        ),
                       (7     , 1         , 1         , 1      , getdate() , 730       , 150       , 9494.0     ),
                       (7     , 1         , 1         , 4      , getdate() , 150       , 50        , 2000.0     ),
                       (4     , 1         , 1         , 10     , getdate() , 2         , 679       , 9999999.0  );

select
     ten.FName
    ,ten.LName
    ,car.Model
    ,car.DailyPay
    ,goc.City       as [get office]
    ,roc.City       as [ret office]
    ,con.PlanDays
    ,con.OverDays
from
               [Contract] as con
    inner join [Tenant]   as ten on con.ArN  = ten.ArN
    inner join [Car]      as car on car.CarN = con.CarN
    inner join [Office]   as goc on goc.RecN = con.GetRecN
    inner join [Office]   as roc on roc.RecN = con.RetRecN