USE PETRYASHOV;
GO

DROP TABLE RegInfo;
DROP TABLE Car;
DROP TABLE Driver;
DROP TABLE Post;
DROP TABLE Regions;

CREATE TABLE Regions
(
    NUMBER INT PRIMARY KEY NOT NULL,
    Name VARCHAR(80),
    CONSTRAINT CHK_Regions CHECK (NUMBER < 100 OR NUMBER BETWEEN 100 AND 199 OR NUMBER BETWEEN 200 AND 299 OR NUMBER BETWEEN 700 AND 799)
)

CREATE TABLE RegInfo (
    Id INT PRIMARY KEY NOT NULL,
    PostId INT NOT NULL,
    CarId INT NOT NULL,
    DriverId INT NOT NULL,
    TrevelTime TIME,
    Direction VARCHAR(80),
)

CREATE TABLE Post(
    Id INT PRIMARY KEY NOT NULL,
    Name VARCHAR(80)
)

CREATE TABLE Car(
    Id INT PRIMARY KEY NOT NULL,
    Model VARCHAR(80),
    Color VARCHAR(80),
    GovNum VARCHAR(80),
    Region INT,
    CONSTRAINT CHK_Person CHECK (GovNum LIKE '%[АВЕКМНОРСТУХABEKMHOPCTYX][0-9][0-9][0-9][АВЕКМНОРСТУХABEKMHOPCTYX][АВЕКМНОРСТУХABEKMHOPCTYX]%'
    AND Region < 100 OR Region BETWEEN 100 AND 199 OR Region BETWEEN 200 AND 299 OR Region BETWEEN 700 AND 799)
)

CREATE TABLE Driver(
    Id INT PRIMARY KEY NOT NULL,
    Name VARCHAR(80),
    Surname VARCHAR(80),
)

ALTER TABLE Car ADD
    CONSTRAINT Region FOREIGN KEY (Region)
    REFERENCES Regions (NUMBER)

ALTER TABLE RegInfo ADD
    CONSTRAINT CarId FOREIGN KEY (CarId)
    REFERENCES Car (Id)

ALTER TABLE RegInfo ADD
    CONSTRAINT DriverId FOREIGN KEY (DriverId)
    REFERENCES Driver (Id)

ALTER TABLE RegInfo ADD
    CONSTRAINT PostId FOREIGN KEY (PostId)
    REFERENCES Post (Id)




INSERT Post (Id,Name)
VALUES
        (1,'A'),
        (2,'B'),
        (3,'C')
GO

INSERT Regions (NUMBER,Name)
VALUES
        (96,'Свердловская область')
GO


INSERT Car (Id,Model, Color, GovNum,Region)
VALUES
        (1,'Lada','Red','A303AA',96)
GO

INSERT Driver (Id, Name, Surname)
VALUES
        (1,'Иван','Иванов'),
        (2,'Петр','Петров')
GO

INSERT RegInfo (Id,PostId, CarId,DriverId,TrevelTime,Direction)
VALUES
        (1,1, 1, 1,'13:56:02', 'in'),
        (2,2, 1, 2,'18:56:02', 'in'),
        (3,3, 1,1,'15:56:02', 'in'),
        (4,2, 1, 2,'18:56:02', 'in'),
        (5,3, 1, 1,'18:56:02', 'in'),
        (6,1, 1, 1,'19:56:02', 'out'),
        (7,3, 1, 2,'18:56:02', 'in'),
        (8,2, 1, 1,'12:56:02', 'in'),
        (9,1, 1, 2,'18:56:02', 'in'),
        (10,3, 1,1,'18:56:02', 'out'),
        (11,3, 1,1,'10:56:02', 'in'),
        (12,3, 1,2,'11:56:02', 'in')
GO


SELECT
    *
    FROM (
        SELECT
            TrevelTime AS 'Время',
            Direction AS 'Направление',
            Driver.Name AS 'Имя',
            Driver.Surname AS 'Фамилия',
            Post.Name AS 'Пост',
            Car.Model AS 'Mодель машины',
            Car.Color AS 'Цвет',
            Car.GovNum AS 'Гос. номер',
            Regions.Name AS 'Регион'
         FROM
            RegInfo
            LEFT JOIN Driver ON DriverId=Driver.Id
            LEFT JOIN Car ON CarId=Car.Id
            LEFT JOIN Post ON PostId=Post.Id
) AS sel