queries

USE master
GO

IF  EXISTS (
        SELECT name
                FROM sys.DATABASES
                WHERE name = N'ElenaBeklenishcheva'
)
ALTER DATABASE ElenaBeklenishcheva SET single_user WITH ROLLBACK immediate
GO

IF  EXISTS (
        SELECT name
                FROM sys.DATABASES
                WHERE name = N'ElenaBeklenishcheva'
)
DROP DATABASE [ElenaBeklenishcheva]
GO

CREATE DATABASE [ElenaBeklenishcheva]
GO

USE [ElenaBeklenishcheva]
GO

IF EXISTS(
  SELECT *
    FROM sys.schemas
   WHERE name = N'Scheme'
)
 DROP SCHEMA Scheme
GO

CREATE SCHEMA Scheme
GO


IF OBJECT_ID('regionsList', 'U') IS NOT NULL
  DROP TABLE regionsList
GO



CREATE TABLE regionsList(
    id_region VARCHAR(3) NOT NULL,
    region_name VARCHAR(50) NOT NULL
)
GO

ALTER TABLE regionsList
ADD CONSTRAINT uniq_regionsList UNIQUE (region_name)
GO

ALTER TABLE regionsList
ADD CONSTRAINT primary_regionsList PRIMARY KEY (id_region)
GO

ALTER TABLE regionsList
ADD CONSTRAINT chk_regionsList CHECK (
    id_region LIKE '[0-9][0-9]'
)
GO

INSERT INTO regionsList(id_region, region_name) VALUES
('01', 'Республика Адыгея'),
('59', 'Пермский край'),
('66', 'Свердловская область'),
('50', 'Московская область'),
('68', 'Тамбовская область'),
('64', 'Саратовская область')


/*INSERT INTO regionsList(id_region, region_name) VALUES
(546, 'Плохая область')*/

UPDATE regionsList SET region_name = UPPER(region_name)
GO

SELECT * FROM regionsList


IF OBJECT_ID('regions', 'U') IS NOT NULL
  DROP TABLE regions
GO

CREATE TABLE regions (
    id_region VARCHAR(3) NOT NULL,
    region_number VARCHAR(3) PRIMARY KEY NOT NULL
    FOREIGN KEY (id_region) REFERENCES regionsList(id_region)
)
GO


ALTER TABLE regions

    ADD CONSTRAINT chk_regions CHECK (
        id_region LIKE '[0-9][0-9]' AND
        (region_number LIKE '[0-9][0-9]' OR
        region_number LIKE '[1|2|7][0-9][0-9]')
    )
GO

INSERT INTO regions(id_region, region_number) VALUES
('01', '01'),
('59', '81'),
('59', '59'),
('59', '159'),
('66', '96'),
('66', '66'),
('50', '90'),
('50', '50'),
('50', '150'),
('50', '190'),
('64', '164'),
('64', '64')

/* несуществующий регион */
/*INSERT INTO regions(id_region, region_number) VALUES
/*(50, 154)*/
(50, 310)
*/


SELECT * FROM regions

IF OBJECT_ID('type_car', 'U') IS NOT NULL
  DROP TABLE type_car
GO

CREATE TABLE type_car (
    type_car_id INT PRIMARY KEY NOT NULL,
    type_car_name VARCHAR(12) UNIQUE NOT NULL
)
GO

UPDATE type_car SET type_car_name = UPPER(type_car_name)
GO

INSERT INTO type_car(type_car_id, type_car_name) VALUES
(1, 'Audi'),
(2, 'Ford'),
(3, 'Renault'),
(4, 'Honda'),
(5, 'Lada')

/*/* повторяющаяся марка*/
INSERT INTO type_car(type_car_id, type_car_name) VALUES
(6, 'Ford'),
(7, 'Ford')
*/


SELECT * FROM type_car


IF OBJECT_ID('color', 'U') IS NOT NULL
    DROP TABLE color
GO



IF OBJECT_ID('color', 'U') IS NOT NULL
  DROP TABLE color
GO

CREATE TABLE color(
    id_color INT PRIMARY KEY NOT NULL,
    color_name VARCHAR(50) UNIQUE NOT NULL
)
GO

INSERT INTO color(id_color, color_name) VALUES
(0, 'ЧЕРНЫЙ'),
(1, 'беЛый'),
(2, 'красныЙ'),
(3, 'жЕлтый')

UPDATE color SET color_name = LOWER(color_name)
GO

SELECT * FROM color
GO

IF OBJECT_ID('cars', 'U') IS NOT NULL
  DROP TABLE cars
GO

CREATE TABLE cars (
    id_car INT PRIMARY KEY NOT NULL,
    type_car_id INT NOT NULL,
    id_color INT NOT NULL,
    car_number VARCHAR(6) NOT NULL,
    car_number_region VARCHAR(3) NOT NULL

    /*FOREIGN KEY (car_number_region) REFERENCES regions(region_number),*/
    /*FOREIGN KEY (type_car_id) REFERENCES type_car(type_car_id),*/
    /*FOREIGN KEY (id_color) REFERENCES color(id_color)*/
)
GO

ALTER TABLE cars
ADD CONSTRAINT FK_cars_region FOREIGN KEY(car_number_region)
    REFERENCES regions(region_number)
    ON DELETE CASCADE
GO

ALTER TABLE cars
ADD CONSTRAINT FK_type_car FOREIGN KEY(type_car_id)
    REFERENCES type_car(type_car_id)
    ON DELETE CASCADE
GO

ALTER TABLE cars
ADD CONSTRAINT FK_car_color FOREIGN KEY(id_color)
    REFERENCES color(id_color)
    ON DELETE CASCADE
GO


ALTER TABLE cars
ADD CONSTRAINT chk_cars CHECK (
    car_number LIKE '[ABEKMHOPCTYXАВЕКМНОРСТУХ][0-9][0-9][0-9][ABEKMHOPCTYXАВЕКМНОРСТУХ][ABEKMHOPCTYXАВЕКМНОРСТУХ]'
    AND (
            (car_number_region LIKE '[127][0-9][0-9]'
        OR
            car_number_region LIKE '[0-9][0-9]')
    )
)
GO

ALTER TABLE cars
ADD CONSTRAINT uniq_num UNIQUE (car_number, car_number_region)
GO


INSERT INTO cars(id_car, type_car_id, id_color, car_number, car_number_region) VALUES
(0, 2, 0, 'A000AA', '66'),
(1, 2, 1, 'H123Ko', '96'),
(2, 5, 3, 'B159OP', '164'),
(3, 5, 3, 'B159OP', '50'),
(4, 5, 3, 'A123PO', '164'),
(5, 5, 3, 'H546PO', '164')
GO
/*
INSERT INTO cars(id_car, type_car_id, id_color, car_number, car_number_region) VALUES
(100, 1, 1, 'a125qw', 96),
(101, 2, 2, 'a456hk', 578)
GO*/


UPDATE cars SET car_number = UPPER(car_number)
GO

SELECT * FROM cars
GO

IF OBJECT_ID('humans', 'U') IS NOT NULL
  DROP TABLE humans
GO

CREATE TABLE humans (
    human_id INT PRIMARY KEY NOT NULL,
    human_surname VARCHAR(30) NOT NULL,
    human_name VARCHAR(20) NOT NULL
)
GO
/*ALTER TABLE humans
ADD CONSTRAINT chk_name CHECK*/
/*check name for symbols*/

UPDATE humans SET human_surname = UPPER(human_surname),
                  human_name = UPPER(human_name)
GO

INSERT INTO humans(human_id, human_surname, human_name) VALUES
(0, 'Абрамов', 'Александр'),
(1, 'Середкина', 'Галина'),
(2, 'Васильев', 'Сергей'),
(3, 'Давлатова', 'Татьяна'),
(4, 'Сергеев', 'Алексей')
GO

IF OBJECT_ID('penalties', 'U') IS NOT NULL
  DROP TABLE penalties
GO

CREATE TABLE penalties (
    penalty_id INT PRIMARY KEY NOT NULL,
    penalty_name VARCHAR(500) NOT NULL,
    penalty_cost money NOT NULL
)
GO

INSERT INTO penalties(penalty_id, penalty_name, penalty_cost) VALUES
(1211, 'Управление ТС, не зарегистрированным в установленном порядке', 800),
(1232, 'Управление ТС водителем, не имеющим при себе документов на право
        управления им, страхового полиса обязательного страхования
        гражданской ответственности владельцев ТС', 500),
(1233, 'Передача управления ТС лицу, не имеющему при себе документов на право управления им', 3000),
(1271, 'Управление ТС водителем, не имеющим права управления ТС (за исключением учебной езды)', 5000),
(1281, 'Управление ТС водителем, находящимся в состоянии опьянения', 30000),
(1292, 'Превышение установленной скорости движения транспортного средства
        на величину более 20, но не более 40 километров в чаc', 500)

ALTER TABLE penalties
ADD CONSTRAINT uniq_penalties UNIQUE NONCLUSTERED (
    penalty_name, penalty_cost
)
GO


IF OBJECT_ID('isPay', 'U') IS NOT NULL
    DROP TABLE isPay
GO

CREATE TABLE isPay (
    is_pay bit PRIMARY KEY DEFAULT 0,
    name_pay VARCHAR(3) NOT NULL
)

ALTER TABLE isPay
ADD CONSTRAINT uniq_pay UNIQUE (name_pay)
GO

INSERT INTO isPay(is_pay, name_pay) VALUES
(0, 'Да'),
(1, 'Нет')
GO


IF OBJECT_ID('human_penalties', 'U') IS NOT NULL
  DROP TABLE human_penalties
GO

CREATE TABLE human_penalties (
    id_node INT PRIMARY KEY NOT NULL,
    human_id INT NOT NULL,
    penalty_id INT NOT NULL,
    is_pay bit DEFAULT 0

    /*FOREIGN KEY (human_id) REFERENCES humans(human_id),
    FOREIGN KEY (penalty_id) REFERENCES penalties(penalty_id)*/
)
GO

ALTER TABLE human_penalties
ADD CONSTRAINT FK_human_penalties_id FOREIGN KEY(human_id)
    REFERENCES humans(human_id)
    ON DELETE CASCADE
GO

ALTER TABLE human_penalties
ADD CONSTRAINT FK_human_penalties FOREIGN KEY(penalty_id)
    REFERENCES penalties(penalty_id)
    ON DELETE CASCADE
GO

ALTER TABLE human_penalties
ADD CONSTRAINT FK_human_penalties_pay FOREIGN KEY(is_pay)
    REFERENCES isPay(is_pay)
    ON DELETE CASCADE
GO

INSERT INTO human_penalties(id_node, human_id, penalty_id, is_pay) VALUES
(0, 0, 1232, 1),
(1, 0, 1292, 0),
(2, 1, 1292, 1)
GO

IF OBJECT_ID('GIBDD_posts', 'U') IS NOT NULL
  DROP TABLE GIBDD_posts
GO

CREATE TABLE GIBDD_posts (
    post_id INT PRIMARY KEY NOT NULL,
    post_address VARCHAR(50) NOT NULL
)
GO

INSERT INTO GIBDD_posts(post_id, post_address) VALUES
(0, 'Южный пост'),
(1, 'Северный пост'),
(2, 'Западный пост'),
(3, 'Восточный пост')
GO


IF OBJECT_ID('main', 'U') IS NOT NULL
  DROP TABLE main
GO

CREATE TABLE main (
    human_id INT NOT NULL,
    post_id INT NOT NULL,
    car_id INT NOT NULL,
    date_measure TIME NOT NULL,
    napr bit NOT NULL
)

ALTER TABLE main
ADD CONSTRAINT FK_human_id FOREIGN KEY(human_id)
    REFERENCES humans(human_id)
    ON DELETE CASCADE
GO

ALTER TABLE main
ADD CONSTRAINT FK_post_id FOREIGN KEY(post_id)
    REFERENCES GIBDD_posts(post_id)
    ON DELETE CASCADE
GO

ALTER TABLE main
ADD CONSTRAINT FK_car_id FOREIGN KEY(car_id)
    REFERENCES cars(id_car)
    ON DELETE CASCADE
GO
/*
ALTER TABLE main
ADD CONSTRAINT chk_napr CHECK(
    SELECT ()
)*/

INSERT INTO main(human_id, post_id, car_id, date_measure, napr) VALUES
--местные
(0, 2, 1, '10:43:30', 0),
(0, 3, 1, '08:00:00', 1),
(2, 2, 0, '22:00:00', 1),
(2, 2, 0, '23:00:00', 0),

--иногородние
(4, 1, 3, '02:28:30', 0),
(4, 1, 3, '03:28:30', 1),

--транзитные
(1, 0, 4, '03:00:00', 1),
(1, 1, 4, '04:00:00', 0),

--остальные
(3, 0, 2, '01:00:00', 0)

--(0, 0, 4, '01:12:05', 0),
--(0, 0, 2, '18:25:15', 1),
--(3, 3, 3, '20:35:30', 1),
--(2, 1, 5, '05:00:00', 0),
--(2, 1, 5, '07:00:00', 1),
--(4, 1, 6, '07:00:00', 0),
--(4, 1, 6, '08:00:00', 1)
GO

--SELECT * FROM main


/*Подсчитывать количество различных категорий автомобилей.*/
/*SELECT type_car.type_car_name as 'Марка машины',
    COUNT(cars.id_car) as 'Количество машин'
FROM cars INNER JOIN
    type_car ON type_car.type_car_id=cars.type_car_id
GROUP BY type_car.type_car_name


SELECT color.color_name as 'Цвет машины',
    COUNT(cars.id_car) as 'Количество машин'
FROM cars INNER JOIN
    color ON color.id_color=cars.id_color
GROUP BY color.color_name
*/

/*поправить, чтобы 66 и 96 считались за один регион*/
/*SELECT regions.region_number as 'Номер региона',
    COUNT(cars.id_car) as 'Количество машин'
FROM cars
    INNER JOIN regions ON regions.region_number = cars.car_number_region
GROUP BY regions.region_number


SELECT humans.human_surname as 'Фамилия водителя',
       humans.human_name as 'Имя водителя',
       penalties.penalty_name as 'Наименование штрафа',
       penalties.penalty_cost as 'Стоимость штрафа',
       isPay.name_pay as 'Оплачен?'
FROM human_penalties
    INNER JOIN humans ON humans.human_id=human_penalties.human_id
    INNER JOIN penalties ON penalties.penalty_id=human_penalties.penalty_id
    INNER JOIN isPay ON isPay.is_pay=human_penalties.is_pay
GROUP BY humans.human_surname, humans.human_name, penalties.penalty_name, penalties.penalty_cost, isPay.name_pay
GO*/

--местные 0 - въехал, 1 -выехал
SELECT humans.human_surname AS 'Фамилия водителя',
       humans.human_name AS 'Имя водителя',
       cars.car_number AS 'Номер автомобиля',
       cars.car_number_region AS 'Номер региона'
FROM main  AS i
    INNER JOIN humans ON humans.human_id=i.human_id
    INNER JOIN GIBDD_posts ON GIBDD_posts.post_id=i.post_id
    INNER JOIN cars ON cars.id_car=i.car_id
WHERE i.napr=1 AND (cars.car_number_region=96 OR cars.car_number_region=66) AND
        i.car_id IN (
        SELECT main.car_id
        FROM main
        WHERE main.napr=0 AND main.date_measure > i.date_measure
    )
GROUP BY humans.human_surname, humans.human_name, cars.car_number, cars.car_number_region
GO

--иногородние
SELECT humans.human_surname AS 'Фамилия водителя',
       humans.human_name AS 'Имя водителя',
       cars.car_number AS 'Номер автомобиля',
       cars.car_number_region AS 'Номер региона'
FROM main  AS i
    INNER JOIN humans ON humans.human_id=i.human_id
    INNER JOIN GIBDD_posts ON GIBDD_posts.post_id=i.post_id
    INNER JOIN cars ON cars.id_car=i.car_id
WHERE i.napr=0 AND cars.car_number_region!=66 AND cars.car_number_region!=96 AND
        i.car_id IN (
        SELECT main.car_id
        FROM main
        WHERE main.napr=1 AND main.post_id=i.post_id
    )
GROUP BY humans.human_surname, humans.human_name, cars.car_number, cars.car_number_region
GO


--транзитные
SELECT humans.human_surname AS 'Фамилия водителя',
       humans.human_name AS 'Имя водителя',
       cars.car_number AS 'Номер автомобиля',
       cars.car_number_region AS 'Номер региона'
FROM main AS i
    INNER JOIN humans ON humans.human_id=i.human_id
    INNER JOIN GIBDD_posts ON GIBDD_posts.post_id=i.post_id
    INNER JOIN cars ON cars.id_car=i.car_id
WHERE i.napr=0 AND cars.car_number_region!=66 AND cars.car_number_region!=96 AND
        i.car_id IN (
        SELECT main.car_id
        FROM main
        WHERE main.napr=1 AND i.post_id!=main.post_id
    )
GROUP BY humans.human_surname, humans.human_name, cars.car_number, cars.car_number_region
GO

SELECT humans.human_surname AS 'Фамилия водителя',
       humans.human_name AS 'Имя водителя',
       cars.car_number AS 'Номер автомобиля',
       cars.car_number_region AS 'Номер региона'
FROM main i
    INNER JOIN humans ON humans.human_id=i.human_id
    INNER JOIN GIBDD_posts ON GIBDD_posts.post_id=i.post_id
    INNER JOIN cars ON cars.id_car=i.car_id
WHERE i.car_id IN (
    ((SELECT main.car_id
    FROM main
    EXCEPT (
    SELECT main.car_id
    FROM main
    WHERE main.napr=1 AND i.post_id!=main.post_id AND i.napr=0) )
    EXCEPT(
    SELECT main.car_id
    FROM main
    WHERE main.napr=1 AND i.post_id=main.post_id AND i.napr=0))
    EXCEPT(
    SELECT main.car_id
    FROM main
    WHERE main.napr=0 AND i.napr=1
    )
)
GROUP BY humans.human_surname, humans.human_name, cars.car_number, cars.car_number_region