triggers

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) PRIMARY KEY NOT NULL,
    region_name VARCHAR(50) UNIQUE NOT NULL
)
GO


IF OBJECT_ID('trigRegionsList', 'TR') IS NOT NULL
    DROP TRIGGER trigRegionsList
GO

CREATE TRIGGER trigRegionsList ON regionsList FOR INSERT
AS
    IF (EXISTS (SELECT * FROM inserted WHERE (id_region NOT LIKE ('[0-9][0-9]')))) OR
        (NOT EXISTS (SELECT * FROM regionsList AS regions, inserted WHERE inserted.id_region = regions.id_region))
    BEGIN
        print ('Неверно задан код региона.');
        ROLLBACK TRANSACTION;
        RETURN
    END
GO

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

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


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

IF OBJECT_ID('trigRegions', 'TR') IS NOT NULL
    DROP TRIGGER trigRegions
GO

CREATE TRIGGER trigRegions ON regions FOR INSERT
AS
    IF (EXISTS (SELECT * FROM inserted WHERE ((id_region NOT LIKE ('[0-9][0-9]')) OR
                                              (region_number NOT LIKE ('[127][0-9][0-9]')) AND
                                              (region_number NOT LIKE ('[0-9][0-9]'))))) OR
        (NOT EXISTS (SELECT * FROM regions, inserted WHERE ((inserted.id_region = regions.id_region)
                                                        AND (inserted.region_number = regions.region_number))))
    BEGIN
        print ('Неверно задан код региона в таблице regions');
        ROLLBACK TRANSACTION;
        RETURN
    END
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', '654')
/*(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')

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

IF OBJECT_ID('trigCar', 'TR') IS NOT NULL
    DROP TRIGGER trigCar
GO

CREATE TRIGGER trigCar ON cars FOR INSERT
AS
BEGIN
    IF (EXISTS (SELECT * FROM inserted WHERE ((car_number NOT LIKE ('[ABEKMHOPCTYXАВЕКМНОРСТУХ][0-9][0-9][0-9][ABEKMHOPCTYXАВЕКМНОРСТУХ][ABEKMHOPCTYXАВЕКМНОРСТУХ]')) OR
                                              (car_number_region NOT LIKE ('[127][0-9][0-9]')) AND
                                              (car_number_region NOT LIKE ('[0-9][0-9]'))))) OR
        (NOT EXISTS (SELECT * FROM cars, inserted WHERE ((inserted.id_car = cars.id_car)
                                                        AND (inserted.type_car_id = cars.type_car_id)
                                                        AND (inserted.id_color = cars.id_color)
                                                        AND (inserted.car_number = cars.car_number)
                                                        AND (inserted.car_number_region = cars.car_number_region))))
    BEGIN
        print ('Неверно номер или регион в таблице автомобилей');
        ROLLBACK TRANSACTION;
        RETURN
    END

    /*IF ((EXISTS ((SELECT COUNT(cars.id_car) FROM cars)) AND
        ((SELECT COUNT(cars.id_car) FROM cars GROUP BY cars.car_number, cars.car_number_region) > (SELECT COUNT(cars.id_car) FROM cars ))))
    BEGIN
        print ('Встретился повторный номер');
        ROLLBACK TRANSACTION;
        RETURN
    END*/

END
GO


/*IF OBJECT_ID('trigCarNumb', 'TR') IS NOT NULL
    DROP TRIGGER trigCarNumb
GO
CREATE TRIGGER trigCarNumb ON cars FOR INSERT
AS
    IF ((EXISTS ((SELECT COUNT(cars.id_car) FROM cars)) AND
        ((SELECT COUNT(cars.id_car) FROM cars GROUP BY cars.car_number, cars.car_number_region) > (SELECT COUNT(cars.id_car) FROM cars ))))
    BEGIN
        print ('Встретился повторный номер');
        ROLLBACK TRANSACTION;
        RETURN
    END
GO*/


INSERT INTO cars(id_car, type_car_id, id_color, car_number, car_number_region) VALUES
(2, 2, 0, 'A000AA', '96')
GO

INSERT INTO cars(id_car, type_car_id, id_color, car_number, car_number_region) VALUES
(1, 2, 1, 'H123Ko', '96')
GO

INSERT INTO cars(id_car, type_car_id, id_color, car_number, car_number_region) VALUES
(3, 5, 3, 'B159OP', '164')
GO

INSERT INTO cars(id_car, type_car_id, id_color, car_number, car_number_region) VALUES
(4, 5, 3, 'B159OP', '66')
GO

INSERT INTO cars(id_car, type_car_id, id_color, car_number, car_number_region) VALUES
(6, 2, 2, 'a456hk', '164')
GO


INSERT INTO cars(id_car, type_car_id, id_color, car_number, car_number_region) VALUES
(7, 2, 2, '5456hu', '164')
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, 'Давлатова', 'Татьяна')
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)

SELECT *FROM penalties

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) UNIQUE NOT NULL
)

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

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 DATE NOT NULL,
    napr bit NOT NULL

    FOREIGN KEY (human_id) REFERENCES humans(human_id),
    FOREIGN KEY (post_id) REFERENCES GIBDD_posts(post_id),
    FOREIGN KEY (car_id) REFERENCES cars(id_car)
)

INSERT INTO main(human_id, post_id, car_id, date_measure, napr) VALUES
(3, 3, 1, '08:00:00', 0),
(2, 2, 2, '10:43:30', 1),
(1, 1, 3, '02:28:30', 1),
(0, 0, 4, '01:12:05', 0),
(0, 0, 2, '18:25:15', 1),
(3, 3, 3, '20:35:30', 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