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'Fed'
- )
- ALTER DATABASE [Fed] set single_user with rollback immediate
- GO
- IF EXISTS (
- SELECT name
- FROM sys.databases
- WHERE name = N'Fed'
- )
- DROP DATABASE [Fed]
- GO
- CREATE DATABASE [Fed]
- GO
- -- СОЗДАЕМ БАЗУ ДАННЫХ
- USE [Fed]
- GO
- IF EXISTS(
- SELECT *
- FROM sys.schemas
- WHERE name = N'lab2'
- )
- DROP SCHEMA lab2
- GO
- CREATE SCHEMA lab2
- GO
- IF OBJECT_ID('[Fed].lab2.Regions', 'U') IS NOT NULL
- DROP TABLE [Fed].lab2.Regions
- GO
- CREATE TABLE [Fed].lab2.Regions
- (
- id int NOT NULL,
- regionName nvarchar(50) NOT NULL,
- CONSTRAINT PK_Regions PRIMARY KEY(id)
- )
- GO
- INSERT INTO [Fed].lab2.Regions (id, regionName)
- VALUES
- (1, N'Свердловская область'),
- (2, N'Хакассия'),
- (3, N'Карелия'),
- (4, N'Калужская область'),
- (5, N'Омская область')
- GO
- IF OBJECT_ID('[Fed].lab2.Codes', 'U') IS NOT NULL
- DROP TABLE [Fed].Codes
- GO
- CREATE TABLE [Fed].lab2.Codes
- (
- code int NOT NULL,
- region int FOREIGN KEY REFERENCES [Fed].lab2.Regions(id) NOT NULL,
- CONSTRAINT PK_Codes PRIMARY KEY(code),
- CHECK ((CODE <= 100) OR (code >= 100 AND code <= 199) OR (code >= 700 AND code <= 799))
- )
- GO
- INSERT INTO [Fed].lab2.Codes (code, region)
- VALUES
- (66, 1),
- (19, 2),
- (10, 3),
- (40, 4),
- (55, 5),
- (96, 1)
- GO
- SELECT
- regionName AS 'Название региона',
- Code AS 'Код Региона'
- FROM [Fed].lab2.Regions
- INNER JOIN [Fed].lab2.Codes
- ON Regions.id = Codes.region
- GO
- IF OBJECT_ID('[Fed].lab2.Cars', 'U') IS NOT NULL
- DROP TABLE [Fed].lab2.Cars
- GO
- CREATE TABLE [Fed].lab2.Cars
- (
- id int NOT NULL,
- carBrand nvarchar(30) NOT NULL,
- color nvarchar(50) NOT NULL,
- carNumber nvarchar(30) NOT NULL,
- regionCode int FOREIGN KEY REFERENCES [Fed].lab2.Codes(code) NOT NULL,
- surname nvarchar(50) NOT NULL,
- CONSTRAINT PK_driver PRIMARY KEY(id),
- CHECK (carNumber LIKE N'[АВЕКМНОРСТУХ][0-9][0-9][0-9][АВЕКМНОРСТУХ][АВЕКМНОРСТУХ]')
- )
- GO
- INSERT INTO [Fed].lab2.Cars (id, carBrand, color, carNumber, regionCode, surname)
- VALUES
- (1, N'Bugatti', N'оранжевый', N'С777АВ', 55, N'Транзитов'),
- (2, N'Pagani', N'белый', N'С777АВ', 55, N'Камазов'),
- (3, N'Aston Martin', N'синий', N'С777АВ', 55, N'Иногородов'),
- (4, N'Alfa romeo', N'красный', N'С777АВ', 10, N'Мусорщиков'),
- (5, N'Lamborghini', N'белоснежный', N'С777АВ', 40, N'Ляпин'),
- (6, N'Ferrari', N'розовый', N'С777АВ', 10, N'Слепов'),
- (7, N'Rolls-Royce', N'синий', N'С777АВ', 19, N'Глухов'),
- (8, N'Koenigsegg', N'серый', N'С777АВ', 96, N'Местных'),
- (9, N'Lada', N'золотой', N'С777АВ', 19, N'Асанов')
- GO
- DECLARE @view nvarchar (30) = N'синий'
- SELECT
- carBrand AS 'Марка',
- color AS 'Цвет',
- carNumber 'Номер авто',
- regionCode AS 'Регион',
- surname AS 'Фамилия'
- FROM [Fed].lab2.Cars
- WHERE Cars.color = @view
- GO
- IF OBJECT_ID('[Fed].lab2.Posts', 'U') IS NOT NULL
- DROP TABLE [Fed].lab2.Posts
- GO
- CREATE TABLE [Fed].lab2.Posts
- (
- id int NOT NULL,
- regionCode int FOREIGN KEY REFERENCES [Fed].lab2.Codes(code) NOT NULL,
- CONSTRAINT PK_posts PRIMARY KEY(id)
- )
- GO
- INSERT INTO [Fed].lab2.Posts (id, regionCode)
- VALUES
- (1, 66),
- (2, 66),
- (3, 66),
- (4, 66),
- (5, 66)
- GO
- IF OBJECT_ID('[Fed].lab2.Passes', 'U') IS NOT NULL
- DROP TABLE [Fed].lab2.Passes
- GO
- CREATE TABLE [Fed].lab2.Passes
- (
- id int IDENTITY(1,1) NOT NULL,
- post int FOREIGN KEY REFERENCES [Fed].lab2.Posts(id) NOT NULL,
- driver int FOREIGN KEY REFERENCES [Fed].lab2.Cars(id) NOT NULL,
- time_act time NOT NULL,
- type_act bit NOT NULL
- )
- GO
- CREATE PROCEDURE checkPassesInsertProc @post AS int, @driver AS int, @time_act AS time, @type_act AS int
- AS
- DECLARE @last_entry int, @last_driver int
- SELECT TOP 1
- @last_driver = Passes.driver,
- @last_entry = Passes.type_act
- FROM [Fed].lab2.Passes
- WHERE Passes.driver = @driver
- ORDER BY Passes.id DESC;
- IF @last_entry = @type_act
- IF @type_act = 1
- RAISERROR ('Ошибка! Нельзя дважды въехать!', 10, 1, @last_driver)
- ELSE
- RAISERROR ('Ошибка! Нельзя дважды выехать!', 10, 1, @last_driver)
- ELSE
- INSERT INTO [Fed].lab2.Passes (post, driver, time_act, type_act)
- VALUES
- (@post, @driver, @time_act, @type_act)
- GO
- EXEC checkPassesInsertProc 1, 1, '10:00', 1
- EXEC checkPassesInsertProc 2, 1, '11:00', 0
- EXEC checkPassesInsertProc 2, 2, '12:00', 0
- EXEC checkPassesInsertProc 3, 2, '13:00', 1
- EXEC checkPassesInsertProc 3, 3, '14:00', 1
- EXEC checkPassesInsertProc 3, 3, '15:00', 0
- EXEC checkPassesInsertProc 2, 8, '18:00', 0
- EXEC checkPassesInsertProc 3, 8, '19:00', 1
- EXEC checkPassesInsertProc 1, 9, '20:00', 0
- EXEC checkPassesInsertProc 2, 9, '21:00', 1
- EXEC checkPassesInsertProc 1, 4, '22:00', 0
- GO
- SELECT
- Cars.surname AS 'Фамилия',
- Cars.carBrand AS 'Марка',
- Cars.carNumber AS 'Номер',
- Cars.color AS 'Цвет',
- regionCodeCar.code AS 'Регион',
- regionNameCar.regionName AS 'Название региона авто',
- CONVERT(nvarchar(20), Passes.time_act, 108) AS 'Время фиксации',
- Passes.type_act AS 'выезд/въезд',
- Posts.id AS 'Номер поста'
- FROM [Fed].lab2.Passes
- INNER JOIN [Fed].lab2.Posts
- ON Passes.post = Posts.id
- INNER JOIN [Fed].lab2.Cars
- ON Passes.driver = Cars.id
- INNER JOIN [Fed].lab2.Codes AS regionCodeCar
- ON Cars.regionCode = regionCodeCar.code
- INNER JOIN [Fed].lab2.Regions AS regionNameCar
- ON regionCodeCar.region = regionNameCar.id
- INNER JOIN [Fed].lab2.Codes AS regionCodeAct
- ON Posts.regionCode = regionCodeAct.code
- INNER JOIN [Fed].lab2.Regions AS regionNameAct
- ON regionCodeAct.region = regionNameAct.id
- GO
- IF OBJECT_ID ('dbo.all_passes', 'U') IS NOT NULL
- DROP TABLE all_passes;
- GO
- SELECT DISTINCT Cars.surname AS 'Фамилия',
- Cars.carBrand AS 'Марка',
- Cars.carNumber AS 'Номер',
- Cars.regionCode AS 'Регион',
- Cars.color AS 'Цвет'
- INTO all_passes
- FROM [Fed].lab2.Passes
- JOIN [Fed].lab2.Cars ON cars.id = Passes.driver
- GO
- SELECT * FROM all_passes
- GO
- IF OBJECT_ID ('dbo.transit', 'U') IS NOT NULL
- DROP TABLE transit;
- GO
- SELECT DISTINCT Cars.surname AS 'Фамилия',
- Cars.carBrand AS 'Марка',
- Cars.carNumber AS 'Номер',
- Cars.regionCode AS 'Регион',
- Cars.color AS 'Цвет'
- INTO transit
- FROM [Fed].lab2.Passes AS pass_1
- JOIN [Fed].lab2.Cars AS cars ON cars.id = pass_1.driver
- JOIN [Fed].lab2.Codes AS region_codes ON cars.regionCode = region_codes.code
- JOIN [Fed].lab2.Regions AS region_names ON region_codes.region = region_names.id
- WHERE EXISTS
- (
- SELECT *
- FROM [Fed].lab2.Passes AS pass_2
- JOIN [Fed].lab2.Cars AS cars ON cars.id = pass_2.driver
- JOIN [Fed].lab2.Codes AS region_codes ON cars.regionCode = region_codes.code
- JOIN [Fed].lab2.Regions AS region_names ON region_codes.region = region_names.id
- WHERE pass_1.driver = pass_2.driver AND pass_1.post != pass_2.post AND pass_1.type_act != pass_2.type_act AND
- pass_1.type_act = 1 AND pass_1.time_act < pass_2.time_act AND region_names.regionName != N'Свердловская область'
- )
- GO
- SELECT *
- FROM transit
- GO
- IF OBJECT_ID ('dbo.nonresident', 'U') IS NOT NULL
- DROP TABLE nonresident;
- GO
- SELECT DISTINCT Cars.surname AS 'Фамилия',
- Cars.carBrand AS 'Марка',
- Cars.carNumber AS 'Номер',
- Cars.regionCode AS 'Регион',
- Cars.color AS 'Цвет'
- INTO nonresident
- FROM [Fed].lab2.Passes AS pass_1
- JOIN [Fed].lab2.Cars AS cars ON cars.id = pass_1.driver
- WHERE EXISTS
- (
- SELECT *
- FROM [Fed].lab2.Passes AS pass_2
- JOIN [Fed].lab2.Cars AS cars ON cars.id = pass_2.driver
- WHERE pass_1.driver = pass_2.driver AND pass_1.post = pass_2.post AND pass_1.type_act != pass_2.type_act AND
- pass_1.type_act = 1 AND pass_1.time_act < pass_2.time_act
- )
- GO
- SELECT *
- FROM nonresident
- GO
- IF OBJECT_ID ('dbo.local_dr', 'U') IS NOT NULL
- DROP TABLE local_dr;
- GO
- SELECT DISTINCT Cars.surname AS 'Фамилия',
- Cars.carBrand AS 'Марка',
- Cars.carNumber AS 'Номер',
- Cars.regionCode AS 'Регион',
- Cars.color AS 'Цвет'
- INTO local_dr
- FROM [Fed].lab2.Passes AS pass_1
- JOIN [Fed].lab2.Cars AS cars ON cars.id = pass_1.driver
- JOIN [Fed].lab2.Codes AS region_codes ON cars.regionCode = region_codes.code
- JOIN [Fed].lab2.Regions AS region_names ON region_codes.region = region_names.id
- WHERE EXISTS
- (
- SELECT *
- FROM [Fed].lab2.Passes AS pass_2
- JOIN [Fed].lab2.Cars AS cars ON cars.id = pass_2.driver
- JOIN [Fed].lab2.Codes AS region_codes ON cars.regionCode = region_codes.code
- JOIN [Fed].lab2.Regions AS region_names ON region_codes.region = region_names.id
- WHERE pass_1.driver = pass_2.driver AND pass_1.type_act != pass_2.type_act AND
- pass_1.type_act = 0 AND pass_1.time_act < pass_2.time_act AND region_names.regionName = N'Свердловская область'
- )
- GO
- SELECT *
- FROM local_dr
- GO
- IF OBJECT_ID ('dbo.union_types', 'U') IS NOT NULL
- DROP TABLE union_types;
- GO
- SELECT *
- INTO union_types
- FROM
- (
- SELECT *
- FROM transit
- UNION
- SELECT *
- FROM nonresident
- UNION
- SELECT *
- FROM local_dr
- ) union_query
- GO
- --SELECT *
- -- FROM union_types
- --GO
- -- разность
- --(SELECT * FROM union_types
- -- EXCEPT
- -- SELECT * FROM all_passes
- --)
- --UNION ALL
- --(SELECT * FROM all_passes
- -- EXCEPT
- -- SELECT * FROM union_types
- --)
- --GO
- ---- Неверный инсерт номера
- --INSERT INTO [Fed].lab2.Cars (id, carBrand, color, carNumber, regionCode, surname)
- -- VALUES
- -- (10, N'BMW', N'чёрный', N'N777NN', 95, N'Ошибкин')
- --GO
- ---- Неверный инсерт на границе
- --EXEC checkPassesInsertProc 1, 4, '22:00', 0
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement