Advertisement
Guest User

Untitled

a guest
Nov 29th, 2015
81
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 13.14 KB | None | 0 0
  1. USE master
  2.  
  3. GO
  4.  
  5. IF EXISTS (
  6. SELECT name
  7. FROM sys.databases
  8. WHERE name = N'Fed'
  9. )
  10. ALTER DATABASE [Fed] set single_user with rollback immediate
  11. GO
  12.  
  13. IF EXISTS (
  14. SELECT name
  15. FROM sys.databases
  16. WHERE name = N'Fed'
  17. )
  18. DROP DATABASE [Fed]
  19. GO
  20.  
  21. CREATE DATABASE [Fed]
  22. GO
  23. -- СОЗДАЕМ БАЗУ ДАННЫХ
  24.  
  25. USE [Fed]
  26. GO
  27.  
  28. IF EXISTS(
  29. SELECT *
  30. FROM sys.schemas
  31. WHERE name = N'lab2'
  32. )
  33. DROP SCHEMA lab2
  34. GO
  35.  
  36. CREATE SCHEMA lab2
  37. GO
  38.  
  39. IF OBJECT_ID('[Fed].lab2.Regions', 'U') IS NOT NULL
  40. DROP TABLE [Fed].lab2.Regions
  41. GO
  42.  
  43. CREATE TABLE [Fed].lab2.Regions
  44. (
  45. id int NOT NULL,
  46. regionName nvarchar(50) NOT NULL,
  47. CONSTRAINT PK_Regions PRIMARY KEY(id)
  48. )
  49. GO
  50.  
  51. INSERT INTO [Fed].lab2.Regions (id, regionName)
  52. VALUES
  53. (1, N'Свердловская область'),
  54. (2, N'Хакассия'),
  55. (3, N'Карелия'),
  56. (4, N'Калужская область'),
  57. (5, N'Омская область')
  58. GO
  59.  
  60. IF OBJECT_ID('[Fed].lab2.Codes', 'U') IS NOT NULL
  61. DROP TABLE [Fed].Codes
  62. GO
  63.  
  64. CREATE TABLE [Fed].lab2.Codes
  65. (
  66. code int NOT NULL,
  67. region int FOREIGN KEY REFERENCES [Fed].lab2.Regions(id) NOT NULL,
  68. CONSTRAINT PK_Codes PRIMARY KEY(code),
  69. CHECK ((CODE <= 100) OR (code >= 100 AND code <= 199) OR (code >= 700 AND code <= 799))
  70. )
  71. GO
  72.  
  73. INSERT INTO [Fed].lab2.Codes (code, region)
  74. VALUES
  75. (66, 1),
  76. (19, 2),
  77. (10, 3),
  78. (40, 4),
  79. (55, 5),
  80. (96, 1)
  81. GO
  82.  
  83. SELECT
  84. regionName AS 'Название региона',
  85. Code AS 'Код Региона'
  86. FROM [Fed].lab2.Regions
  87. INNER JOIN [Fed].lab2.Codes
  88. ON Regions.id = Codes.region
  89. GO
  90.  
  91. IF OBJECT_ID('[Fed].lab2.Cars', 'U') IS NOT NULL
  92. DROP TABLE [Fed].lab2.Cars
  93. GO
  94.  
  95. CREATE TABLE [Fed].lab2.Cars
  96. (
  97. id int NOT NULL,
  98. carBrand nvarchar(30) NOT NULL,
  99. color nvarchar(50) NOT NULL,
  100. carNumber nvarchar(30) NOT NULL,
  101. regionCode int FOREIGN KEY REFERENCES [Fed].lab2.Codes(code) NOT NULL,
  102. surname nvarchar(50) NOT NULL,
  103. CONSTRAINT PK_driver PRIMARY KEY(id),
  104. CHECK (carNumber LIKE N'[АВЕКМНОРСТУХ][0-9][0-9][0-9][АВЕКМНОРСТУХ][АВЕКМНОРСТУХ]')
  105. )
  106. GO
  107.  
  108. INSERT INTO [Fed].lab2.Cars (id, carBrand, color, carNumber, regionCode, surname)
  109. VALUES
  110. (1, N'Bugatti', N'оранжевый', N'С777АВ', 55, N'Транзитов'),
  111. (2, N'Pagani', N'белый', N'С777АВ', 55, N'Камазов'),
  112. (3, N'Aston Martin', N'синий', N'С777АВ', 55, N'Иногородов'),
  113. (4, N'Alfa romeo', N'красный', N'С777АВ', 10, N'Мусорщиков'),
  114. (5, N'Lamborghini', N'белоснежный', N'С777АВ', 40, N'Ляпин'),
  115. (6, N'Ferrari', N'розовый', N'С777АВ', 10, N'Слепов'),
  116. (7, N'Rolls-Royce', N'синий', N'С777АВ', 19, N'Глухов'),
  117. (8, N'Koenigsegg', N'серый', N'С777АВ', 96, N'Местных'),
  118. (9, N'Lada', N'золотой', N'С777АВ', 19, N'Асанов')
  119. GO
  120.  
  121. DECLARE @view nvarchar (30) = N'синий'
  122. SELECT
  123. carBrand AS 'Марка',
  124. color AS 'Цвет',
  125. carNumber 'Номер авто',
  126. regionCode AS 'Регион',
  127. surname AS 'Фамилия'
  128. FROM [Fed].lab2.Cars
  129. WHERE Cars.color = @view
  130. GO
  131.  
  132. IF OBJECT_ID('[Fed].lab2.Posts', 'U') IS NOT NULL
  133. DROP TABLE [Fed].lab2.Posts
  134. GO
  135.  
  136. CREATE TABLE [Fed].lab2.Posts
  137. (
  138. id int NOT NULL,
  139. regionCode int FOREIGN KEY REFERENCES [Fed].lab2.Codes(code) NOT NULL,
  140. CONSTRAINT PK_posts PRIMARY KEY(id)
  141. )
  142. GO
  143.  
  144. INSERT INTO [Fed].lab2.Posts (id, regionCode)
  145. VALUES
  146. (1, 66),
  147. (2, 66),
  148. (3, 66),
  149. (4, 66),
  150. (5, 66)
  151. GO
  152.  
  153. IF OBJECT_ID('[Fed].lab2.Passes', 'U') IS NOT NULL
  154. DROP TABLE [Fed].lab2.Passes
  155. GO
  156.  
  157. CREATE TABLE [Fed].lab2.Passes
  158. (
  159. id int IDENTITY(1,1) NOT NULL,
  160. post int FOREIGN KEY REFERENCES [Fed].lab2.Posts(id) NOT NULL,
  161. driver int FOREIGN KEY REFERENCES [Fed].lab2.Cars(id) NOT NULL,
  162. time_act time NOT NULL,
  163. type_act bit NOT NULL
  164. )
  165. GO
  166.  
  167. CREATE PROCEDURE checkPassesInsertProc @post AS int, @driver AS int, @time_act AS time, @type_act AS int
  168. AS
  169. DECLARE @last_entry int, @last_driver int
  170. SELECT TOP 1
  171. @last_driver = Passes.driver,
  172. @last_entry = Passes.type_act
  173. FROM [Fed].lab2.Passes
  174. WHERE Passes.driver = @driver
  175. ORDER BY Passes.id DESC;
  176. IF @last_entry = @type_act
  177. IF @type_act = 1
  178. RAISERROR ('Ошибка! Нельзя дважды въехать!', 10, 1, @last_driver)
  179. ELSE
  180. RAISERROR ('Ошибка! Нельзя дважды выехать!', 10, 1, @last_driver)
  181. ELSE
  182. INSERT INTO [Fed].lab2.Passes (post, driver, time_act, type_act)
  183. VALUES
  184. (@post, @driver, @time_act, @type_act)
  185. GO
  186.  
  187. EXEC checkPassesInsertProc 1, 1, '10:00', 1
  188. EXEC checkPassesInsertProc 2, 1, '11:00', 0
  189. EXEC checkPassesInsertProc 2, 2, '12:00', 0
  190. EXEC checkPassesInsertProc 3, 2, '13:00', 1
  191. EXEC checkPassesInsertProc 3, 3, '14:00', 1
  192. EXEC checkPassesInsertProc 3, 3, '15:00', 0
  193. EXEC checkPassesInsertProc 2, 8, '18:00', 0
  194. EXEC checkPassesInsertProc 3, 8, '19:00', 1
  195. EXEC checkPassesInsertProc 1, 9, '20:00', 0
  196. EXEC checkPassesInsertProc 2, 9, '21:00', 1
  197. EXEC checkPassesInsertProc 1, 4, '22:00', 0
  198. GO
  199.  
  200. SELECT
  201. Cars.surname AS 'Фамилия',
  202. Cars.carBrand AS 'Марка',
  203. Cars.carNumber AS 'Номер',
  204. Cars.color AS 'Цвет',
  205. regionCodeCar.code AS 'Регион',
  206. regionNameCar.regionName AS 'Название региона авто',
  207. CONVERT(nvarchar(20), Passes.time_act, 108) AS 'Время фиксации',
  208. Passes.type_act AS 'выезд/въезд',
  209. Posts.id AS 'Номер поста'
  210. FROM [Fed].lab2.Passes
  211. INNER JOIN [Fed].lab2.Posts
  212. ON Passes.post = Posts.id
  213. INNER JOIN [Fed].lab2.Cars
  214. ON Passes.driver = Cars.id
  215. INNER JOIN [Fed].lab2.Codes AS regionCodeCar
  216. ON Cars.regionCode = regionCodeCar.code
  217. INNER JOIN [Fed].lab2.Regions AS regionNameCar
  218. ON regionCodeCar.region = regionNameCar.id
  219. INNER JOIN [Fed].lab2.Codes AS regionCodeAct
  220. ON Posts.regionCode = regionCodeAct.code
  221. INNER JOIN [Fed].lab2.Regions AS regionNameAct
  222. ON regionCodeAct.region = regionNameAct.id
  223. GO
  224.  
  225. IF OBJECT_ID ('dbo.all_passes', 'U') IS NOT NULL
  226. DROP TABLE all_passes;
  227. GO
  228.  
  229. SELECT DISTINCT Cars.surname AS 'Фамилия',
  230. Cars.carBrand AS 'Марка',
  231. Cars.carNumber AS 'Номер',
  232. Cars.regionCode AS 'Регион',
  233. Cars.color AS 'Цвет'
  234. INTO all_passes
  235. FROM [Fed].lab2.Passes
  236. JOIN [Fed].lab2.Cars ON cars.id = Passes.driver
  237. GO
  238.  
  239. SELECT * FROM all_passes
  240. GO
  241.  
  242. IF OBJECT_ID ('dbo.transit', 'U') IS NOT NULL
  243. DROP TABLE transit;
  244. GO
  245.  
  246. SELECT DISTINCT Cars.surname AS 'Фамилия',
  247. Cars.carBrand AS 'Марка',
  248. Cars.carNumber AS 'Номер',
  249. Cars.regionCode AS 'Регион',
  250. Cars.color AS 'Цвет'
  251. INTO transit
  252. FROM [Fed].lab2.Passes AS pass_1
  253. JOIN [Fed].lab2.Cars AS cars ON cars.id = pass_1.driver
  254. JOIN [Fed].lab2.Codes AS region_codes ON cars.regionCode = region_codes.code
  255. JOIN [Fed].lab2.Regions AS region_names ON region_codes.region = region_names.id
  256. WHERE EXISTS
  257. (
  258. SELECT *
  259. FROM [Fed].lab2.Passes AS pass_2
  260. JOIN [Fed].lab2.Cars AS cars ON cars.id = pass_2.driver
  261. JOIN [Fed].lab2.Codes AS region_codes ON cars.regionCode = region_codes.code
  262. JOIN [Fed].lab2.Regions AS region_names ON region_codes.region = region_names.id
  263. WHERE pass_1.driver = pass_2.driver AND pass_1.post != pass_2.post AND pass_1.type_act != pass_2.type_act AND
  264. pass_1.type_act = 1 AND pass_1.time_act < pass_2.time_act AND region_names.regionName != N'Свердловская область'
  265. )
  266. GO
  267.  
  268. SELECT *
  269. FROM transit
  270. GO
  271.  
  272. IF OBJECT_ID ('dbo.nonresident', 'U') IS NOT NULL
  273. DROP TABLE nonresident;
  274. GO
  275.  
  276. SELECT DISTINCT Cars.surname AS 'Фамилия',
  277. Cars.carBrand AS 'Марка',
  278. Cars.carNumber AS 'Номер',
  279. Cars.regionCode AS 'Регион',
  280. Cars.color AS 'Цвет'
  281. INTO nonresident
  282. FROM [Fed].lab2.Passes AS pass_1
  283. JOIN [Fed].lab2.Cars AS cars ON cars.id = pass_1.driver
  284. WHERE EXISTS
  285. (
  286. SELECT *
  287. FROM [Fed].lab2.Passes AS pass_2
  288. JOIN [Fed].lab2.Cars AS cars ON cars.id = pass_2.driver
  289. WHERE pass_1.driver = pass_2.driver AND pass_1.post = pass_2.post AND pass_1.type_act != pass_2.type_act AND
  290. pass_1.type_act = 1 AND pass_1.time_act < pass_2.time_act
  291. )
  292. GO
  293.  
  294. SELECT *
  295. FROM nonresident
  296. GO
  297.  
  298. IF OBJECT_ID ('dbo.local_dr', 'U') IS NOT NULL
  299. DROP TABLE local_dr;
  300. GO
  301.  
  302. SELECT DISTINCT Cars.surname AS 'Фамилия',
  303. Cars.carBrand AS 'Марка',
  304. Cars.carNumber AS 'Номер',
  305. Cars.regionCode AS 'Регион',
  306. Cars.color AS 'Цвет'
  307. INTO local_dr
  308. FROM [Fed].lab2.Passes AS pass_1
  309. JOIN [Fed].lab2.Cars AS cars ON cars.id = pass_1.driver
  310. JOIN [Fed].lab2.Codes AS region_codes ON cars.regionCode = region_codes.code
  311. JOIN [Fed].lab2.Regions AS region_names ON region_codes.region = region_names.id
  312. WHERE EXISTS
  313. (
  314. SELECT *
  315. FROM [Fed].lab2.Passes AS pass_2
  316. JOIN [Fed].lab2.Cars AS cars ON cars.id = pass_2.driver
  317. JOIN [Fed].lab2.Codes AS region_codes ON cars.regionCode = region_codes.code
  318. JOIN [Fed].lab2.Regions AS region_names ON region_codes.region = region_names.id
  319. WHERE pass_1.driver = pass_2.driver AND pass_1.type_act != pass_2.type_act AND
  320. pass_1.type_act = 0 AND pass_1.time_act < pass_2.time_act AND region_names.regionName = N'Свердловская область'
  321. )
  322. GO
  323.  
  324. SELECT *
  325. FROM local_dr
  326. GO
  327.  
  328. IF OBJECT_ID ('dbo.union_types', 'U') IS NOT NULL
  329. DROP TABLE union_types;
  330. GO
  331.  
  332. SELECT *
  333. INTO union_types
  334. FROM
  335. (
  336. SELECT *
  337. FROM transit
  338. UNION
  339. SELECT *
  340. FROM nonresident
  341. UNION
  342. SELECT *
  343. FROM local_dr
  344. ) union_query
  345. GO
  346.  
  347. --SELECT *
  348. -- FROM union_types
  349. --GO
  350.  
  351. -- разность
  352. --(SELECT * FROM union_types
  353. -- EXCEPT
  354. -- SELECT * FROM all_passes
  355. --)
  356. --UNION ALL
  357. --(SELECT * FROM all_passes
  358. -- EXCEPT
  359. -- SELECT * FROM union_types
  360. --)
  361. --GO
  362.  
  363. ---- Неверный инсерт номера
  364. --INSERT INTO [Fed].lab2.Cars (id, carBrand, color, carNumber, regionCode, surname)
  365. -- VALUES
  366. -- (10, N'BMW', N'чёрный', N'N777NN', 95, N'Ошибкин')
  367. --GO
  368. ---- Неверный инсерт на границе
  369. --EXEC checkPassesInsertProc 1, 4, '22:00', 0
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement