tHKXFhc5

1use master;
2go
3if DB_ID (N'lab6') is null
4	create database lab6
5	on (
6		NAME = lab6dat,
7		FILENAME = 'C:\data\lab6\lab6.mdf',
8		SIZE = 10,
9		MAXSIZE = UNLIMITED,
10		FILEGROWTH = 5
11		)
12	log on (
13		NAME = lab6log,
14		FILENAME = 'C:\data\lab6\lab6log.ldf',
15		SIZE = 5,
16		MAXSIZE = 20,
17		FILEGROWTH = 5
18		);
19go
20
21
22use lab6;
23go
24if OBJECT_ID(N'university_group', N'U') is not null
25	drop table university_group
26go
27create table university_group (
28	id			int IDENTITY(100,100)		not null,
29	firstname		varchar(35)			not null,
30	lastname		varchar(35)			 null,
31	age int null,
32	gender	varchar(35)			 null,
33	createdate		datetime			not null
34		CONSTRAINT DF_school_createdate DEFAULT (getdate()),
35
36	PRIMARY KEY (id),
37	CONSTRAINT CHK_school_gender
38		CHECK (age > 16)
39	);
40go 
41-- getdate() возвращает текущую системную метку времени бд в виде значения datetime без смещения часового пояса
42
43insert into university_group(firstname, lastname, age)
44	values
45	('Ekaterina', 'Gimranova', 20),
46	('Andrew', 'Maximov', 19),
47	('Olga', 'Kim', 21);
48
49go
50select * from university_group;
51
52select IDENT_CURRENT ('university_group') AS current_id;
53
54insert into university_group(firstname, gender)
55	values
56		('Ekaterina', 'female'),
57		('Sam', 'male'),
58		('Olga', 'female'),
59		('Andrew', 'male');
60go
61
62if OBJECT_ID(N'teachers', N'U') is not null
63	drop table teachers
64go
65create table teachers (
66	id		UNIQUEIDENTIFIER DEFAULT NEWID(),
67	firstname varchar(35) not null,
68	lastname varchar(35) not null,
69	age 	int null,
70	gender varchar(35) null,
71
72	PRIMARY KEY (id),
73	);
74go
75
76insert into teachers(firstname, lastname)
77	values
78		('Anna', 'Vasilieva'),
79		('John', 'Bon');
80go
81
82select * from teachers;
83
84if OBJECT_ID(N'magazine', N'U') is not null
85	drop table magazine
86go
87create table magazine (
88	id int PRIMARY KEY,
89	name varchar(35)	not null,
90	author	varchar(35)	not null,
91	);
92go
93
94drop sequence if exists countby1;
95go
96
97create sequence countby1
98	start with 1
99	increment by 1;
100
101go
102
103insert magazine values
104		(NEXT VALUE FOR countby1, 'a','b'),
105		(NEXT VALUE FOR countby1, 'asdsd','bsS'),
106		(NEXT VALUE FOR countby1, 'aDAAFAD','bSDS');
107
108select * from magazine;
109
110go
111
112if (OBJECT_ID(N'FK_books_bwid', N'F') is not null)
113begin
114	alter table books
115		drop CONSTRAINT FK_books_bwid
116end
117go
118
119if OBJECT_ID(N'writers', N'U') is not null
120	drop table writers
121go
122create table writers (
123	wid		int			not null,
124	firstname	varchar(35)		not null,
125	midname		varchar(35)		null,
126	lastname	varchar(35)		not null,
127	tmp		int
128
129	PRIMARY KEY (wid)
130	);
131go
132
133insert writers values 
134	(1, 'john', 'r.r.', 'tolkien', 1),
135	(2, 'george', 'r.r.', 'martin', 2),
136	(3, 'herbert', 'g.', 'wells', 3),
137	(4, 'george', '', 'orwell', 4),
138	(5, 'raymond', 'd.', 'bradbury', 5);
139go
140
141select * from writers;
142
143drop view if exists bookshelf_ix_view;
144go
145if OBJECT_ID(N'books', N'U') is not null
146	drop table books
147go
148create table books (
149	bid	int IDENTITY(1, 1)	not null,
150	name	varchar(254)		not null,
151	bwid	int			not null
152		CONSTRAINT DF_books_bwid DEFAULT (3),
153	year	int			null,
154
155	PRIMARY KEY (bid),
156	CONSTRAINT FK_books_bwid
157		FOREIGN KEY (bwid) 
158		REFERENCES writers(wid) 
159		ON DELETE SET DEFAULT
160	);
161go
162
163/* SET DELETE
164Указывает, какое действие производится над строками изменяемой таблицы, если эти строки имеют ссылочную связь, 
165и строка, на которую имеется ссылка, удаляется из родительской таблицы.
166SET DEFAULT
167 Все значения, содержащие внешний ключ, при удалении соответствующей строки родительской таблицы устанавливаются
168  в значения по умолчанию. */
169
170insert into books(name, bwid, year)
171	values
172		('the lord of the rings', 1, 1954),
173		('the hobbit, or there and back again', 1, 1937),
174		('a song of ice and fire', 2, 1996),
175		('1984', 4, 1949),
176		('fahrenheit 451', 5, 1953);
177go
178
179select * from writers;
180select * from books;