b8fjxx4u

1Use master
2GO
3
4DROP DATABASE IF EXISTS  LAB6 
5
6CREATE DATABASE LAB6 
7    ON (
8        NAME = LAB6_dat,
9        FILENAME = '/DataBase/lab6dat.mdf',
10        SIZE = 10MB,
11         MAXSIZE = UNLIMITED, 
12         FILEGROWTH = 5%
13    )
14    LOG ON (
15        NAME = LAB6_log,
16        FILENAME = '/DataBase/LAB6log.ldf',
17        SIZE = 5MB, 
18        MAXSIZE = 150MB, 
19        FILEGROWTH = 5%
20    )
21
22GO
23
24USE LAB6
25GO
26
27CREATE FUNCTION dbo.countDuration
28(@courseName VARCHAR(15))
29RETURNS char(8)
30    BEGIN
31        DECLARE @duration char(8)
32        IF @courseName = 'Programming'
33            SELECT @duration = '5 Months'
34        ELSE 
35            IF @courseName = 'Math'
36             SELECT @duration = '6 Months'
37            ELSE
38                IF @courseName = 'Languages'
39                    SELECT @duration =  '4 Months'
40        RETURN @duration
41    END;
42GO
43
44
45DROP TABLE IF EXISTS Course
46GO 
47
48CREATE TABLE dbo.Course (
49    CourseID int PRIMARY KEY IDENTITY(1 , 2) NOT NULL, 
50    courseName VARCHAR(15) NOT NULL,
51    descr VARCHAR(50) DEFAULT 'Info about course',
52    price INT CHECK (price > 10000) NOT NULL,  
53    duration AS dbo.countDuration(courseName)
54)
55GO 
56
57
58INSERT INTO dbo.Course(courseName, descr, price)
59    VALUES ('Programming', 'brief desription', 15000)
60GO
61
62INSERT INTO dbo.Course(courseName, price)
63    VALUES ('Math', 11000)
64GO
65
66
67-- SELECT * FROM dbo.Course
68-- GO
69
70
71SELECT SCOPE_IDENTITY()
72GO
73
74DROP TABLE IF EXISTS dbo.Subject
75GO
76
77CREATE TABLE dbo.Subject (
78    SubjectId UNIQUEIDENTIFIER PRIMARY KEY NOT NULL DEFAULT NEWID()
79)
80GO
81
82INSERT INTO dbo.Subject VALUES (NEWID ())
83
84
85-- SELECT * FROM dbo.Subject
86-- GO
87
88
89DROP TABLE IF EXISTS dbo.Sequence
90GO
91
92CREATE TABLE dbo.SequenceTable(
93    SequenceID int PRIMARY KEY
94)
95
96CREATE SEQUENCE dbo.ChangeID AS int 
97    START WITH 1
98    INCREMENT BY 2 
99GO
100
101INSERT INTO dbo.SequenceTable VALUES(NEXT VALUE FOR dbo.ChangeID)
102INSERT INTO dbo.SequenceTable VALUES(NEXT VALUE FOR dbo.ChangeID)
103INSERT INTO dbo.SequenceTable VALUES(NEXT VALUE FOR dbo.ChangeID)
104INSERT INTO dbo.SequenceTable VALUES(NEXT VALUE FOR dbo.ChangeID)
105INSERT INTO dbo.SequenceTable VALUES(NEXT VALUE FOR dbo.ChangeID)
106
107GO
108
109
110-- SELECT * FROM dbo.SequenceTable
111-- GO
112
113
114DROP SEQUENCE dbo.ChangeID
115GO
116
117DROP TABLE IF EXISTS dbo.Teacher
118GO
119
120CREATE TABLE dbo.Teacher(
121    TeacherID int IDENTITY PRIMARY KEY,
122    firstName VARCHAR(20),
123    secondName VARCHAR(20),
124)
125
126INSERT INTO dbo.Teacher(firstName, secondName)
127VALUES 
128    ('Ivan', 'Ivanov'),
129    ('Petr', 'Petrov')
130GO
131
132
133
134
135DROP TABLE IF EXISTS dbo.Lesson 
136GO
137
138CREATE TABLE dbo.Lesson(
139    LessonID int IDENTITY PRIMARY KEY,
140    currentTheme VARCHAR(20),
141    homework varchar(50) DEFAULT 'no homework',
142    TeacherID int,
143    CONSTRAINT FK_TEACHER_ID FOREIGN KEY (TeacherID)
144        REFERENCES dbo.Teacher(TeacherID)
145)
146GO
147
148
149INSERT INTO dbo.Lesson(currentTheme, homework, TeacherID)
150VALUES
151    ('Data bases', 'lab6', 1),
152    ('Grafika', 'lab3', 1),
153    ('Rows', 'exercise 101, 121', 2),
154    ('Integrals', 'exercise 151, 152', 2)
155GO
156
157SELECT * FROM dbo.Teacher
158SELECT * FROM dbo.Lesson
159GO
160
161-- INSERT INTO dbo.Teacher(firstName, secondName)
162-- VALUES
163--     ('Ivan', 'Ivanov')
164-- GO
165
166
167-- INSERT INTO dbo.Lesson(currentTheme,homework, TeacherID)
168-- VALUES ('Data bases', 'lab6', 1)
169-- GO
170
171-- UPDATE dbo.Teacher
172-- SET secondName = 'Ivanov'
173-- DELETE FROM dbo.Teacher
174-- GO
175
176-- SELECT * FROM dbo.Teacher
177-- SELECT * FROM dbo.Lesson
178-- GO
179
180-- ALTER TABLE dbo.Lesson
181-- DROP CONSTRAINT FK_TEACHER_ID;
182-- GO
183
184-- ALTER TABLE dbo.Lesson
185-- ADD CONSTRAINT FK_TEACHER_ID
186-- FOREIGN KEY (TeacherID) REFERENCES dbo.Teacher(TeacherID)
187--     ON DELETE CASCADE
188--     ON UPDATE CASCADE
189
190-- GO
191
192-- UPDATE dbo.Teacher SET secondName = 'Pushkin'
193--  DELETE FROM dbo.Teacher
194-- GO
195
196-- SELECT * FROM dbo.Teacher
197-- SELECT * FROM dbo.Lesson
198-- GO
199
200-- ALTER TABLE dbo.Lesson
201-- DROP CONSTRAINT FK_TEACHER_ID;
202-- GO
203
204-- ALTER TABLE dbo.Lesson
205-- ADD CONSTRAINT FK_TEACHER_ID
206-- FOREIGN KEY (TeacherID) REFERENCES dbo.Teacher(TeacherID)
207--     ON DELETE CASCADE
208--     ON UPDATE CASCADE
209
210-- GO
211
212-- UPDATE dbo.Teacher 
213-- SET secondName = 'Pushkin' 
214-- DELETE FROM dbo.Teacher
215-- GO
216
217-- SELECT * FROM dbo.Teacher
218-- SELECT * FROM dbo.Lesson
219-- GO
220
221
222ALTER TABLE dbo.Lesson
223DROP CONSTRAINT FK_TEACHER_ID;
224GO
225
226ALTER TABLE dbo.Lesson
227ADD CONSTRAINT FK_TEACHER_ID
228FOREIGN KEY (TeacherID) REFERENCES dbo.Teacher(TeacherID)
229    ON DELETE SET DEFAULT 
230    ON UPDATE SET DEFAULT
231
232GO
233
234UPDATE dbo.Teacher SET secondName = 'Pushkin'
235DELETE FROM dbo.Teacher WHERE secondName = 'Ivanov'
236GO
237
238SELECT * FROM dbo.Teacher
239SELECT * FROM dbo.Lesson
240GO
241
242
243ALTER TABLE dbo.Lesson
244DROP CONSTRAINT FK_TEACHER_ID;
245GO
246
247ALTER TABLE dbo.Lesson
248ADD CONSTRAINT FK_TEACHER_ID
249FOREIGN KEY (TeacherID) REFERENCES dbo.Teacher(TeacherID)
250    ON DELETE SET NULL 
251    ON UPDATE SET NULL
252GO
253
254UPDATE dbo.Teacher SET secondName = 'Pushkin' 
255DELETE FROM dbo.Teacher WHERE secondName = 'Ivanov'
256GO
257
258SELECT * FROM dbo.Teacher
259SELECT * FROM dbo.Lesson
260GO