· 9 years ago · Dec 05, 2016, 07:08 AM
1use master
2GO
3
4IF EXISTS (
5SELECT name
6FROM sys.databases
7WHERE name = N'[ Mitrofanova]'
8)
9ALTER DATABASE Mitrofanova set single_user with rollback immediate
10GO
11
12IF EXISTS (
13SELECT name
14FROM sys.databases
15WHERE name = N'Mitrofanova'
16)
17DROP DATABASE Mitrofanova
18GO
19
20IF OBJECT_ID('Mitrofanova') IS NOT NULL
21DROP DATABASE Mitrofanova
22GO
23
24CREATE DATABASE Mitrofanova
25GO
26
27USE Mitrofanova
28GO
29
30CREATE TABLE Person
31(
32id int not null,
33nameP nvarchar(90) not null,
34adress nvarchar(100) not null,
35homeTel nvarchar(11) not null,
36workTel nvarchar(8) not null
37CONSTRAINT PK_name PRIMARY KEY (id)
38)
39GO
40
41CREATE TABLE Commission
42(
43idC int not null,
44profil nvarchar(40) not null,
45CONSTRAINT PK_idCom PRIMARY KEY (idC)
46)
47GO
48
49
50CREATE TABLE Worker
51(
52id int not null,
53idCom int not null,
54dateIn date not null,
55dateOut date,
56position nvarchar(20) not null,
57--CONSTRAINT PK_id PRIMARY KEY (id),
58constraint FK_id foreign key (id) references Person(id),
59constraint FK_idC foreign key (idCom) references Commission(idC)
60)
61GO
62
63
64
65
66INSERT INTO Person VALUES
67(1, N'Иванов Иван Иванович', N'ЛуначарÑокго 1 32', N'89089001111', N'36354363')
68,(2, N'Петров Петр Петрович', N'Ленина 5 454', N'89085551111', N'36354300')
69,(3, N'Сидоров Семен Иванович', N'Попова 87 85', N'89089551111', N'30054363')
70,(4, N'Камнев Карл Петрович', N'Фрунзе 54 22', N'89089051111', N'36300363')
71,(5, N'Лукин Яков Семенович', N'Фурманова 46 85', N'89089501111', N'11177111')
72,(6, N'Ðвдеев Петр Владимирович', N'Ленина 46 85', N'89089500000', N'11100111')
73GO
74
75INSERT INTO Worker VALUES
76(1,1, N'2014-05-15', N'2016-05-15', N'ПредÑедатель')
77,(2,2, N'2016-05-15', N'2018-05-15' , N'Член')
78,(3, 3,N'2016-05-15', N'2018-05-15' , N'ПредÑедатель')
79,(4,4, N'2013-05-15', N'2015-05-15', N'Член')
80,(5,5, N'2015-05-15', N'2017-05-15', N'ПредÑедатель')
81,(6, 5,N'2015-07-15', N'2017-07-15', N'Член')
82GO
83
84INSERT INTO Commission VALUES
85(1, N'Образование')
86,(2, N'Жилье')
87,(3, N'КоррупциÑ')
88,(4, N'ПреÑтупноÑÑ‚ÑŒ')
89,(5, N'Здоровье')
90
91SELECT *
92FROM Worker INNER JOIN
93 Person on Person.id = Worker.id INNER JOIN
94 Commission on Commission.idC = Worker.idCom