Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- 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'),
- (1, 2, 1, 'H123Ko', '96'),
- (3, 5, 3, 'B159OP', '164'),
- (4, 5, 3, 'B159OP', '66')
- GO
- /*INSERT INTO cars(id_car, type_car_id, id_color, car_number, car_number_region) VALUES
- (7, 5, 3, 'B159OP', '164')*//*
- (5, 1, 1, 'a125qw', '96'),
- (6, 2, 2, 'a456hk', '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, '151108', 0),
- (2, 2, 2, '151107', 1),
- (1, 1, 3, '151108', 1),
- (0, 0, 4, '151106', 0),
- (0, 0, 2, '151109', 1),
- (3, 3, 3, '151110', 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
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement