· 6 years ago · Jan 11, 2020, 08:02 AM
1use KASETY_512_06
2
3-- CW 3 --
4
5
6IF EXISTS (SELECT * FROM dbo.sysobjects WHERE ID = OBJECT_ID(N'REZYSER'))
7 BEGIN
8 PRINT 'Tabela MIASTA już istnieje w BD!'
9 END
10ELSE
11 BEGIN
12create table REZYSER(
13IDREZYSER INT PRIMARY KEY NOT NULL,
14NAZWISKO CHAR(30) NOT NULL,
15IMIE CHAR(15)
16)
17 END
18go
19
20IF EXISTS (SELECT * FROM dbo.sysobjects WHERE ID = OBJECT_ID(N'FILMY'))
21 BEGIN
22 PRINT 'Tabela FILMY już istnieje w BD!'
23 END
24ELSE
25 BEGIN
26create table FILMY(
27IDFILMU INT PRIMARY KEY NOT NULL,
28TYTUL CHAR(25) NOT NULL,
29IDREZYSER INT NOT NULL REFERENCES REZYSER(IDREZYSER),
30CENA DECIMAL(6,2) NOT NULL,
31KOLOR CHAR(1) NOT NULL,
32OPIS CHAR(40)
33)
34END
35go
36
37IF EXISTS (SELECT * FROM dbo.sysobjects WHERE ID = OBJECT_ID(N'RODZAJ'))
38 BEGIN
39 PRINT 'Tabela RODZAJ już istnieje w BD!'
40 END
41ELSE
42 BEGIN
43create table RODZAJ(
44IDRODZAJ INT PRIMARY KEY NOT NULL,
45RODZAJFIL CHAR(15) NOT NULL
46)
47END
48go
49
50IF EXISTS (SELECT * FROM dbo.sysobjects WHERE ID = OBJECT_ID(N'KRAJ'))
51 BEGIN
52 PRINT 'Tabela KRAJ już istnieje w BD!'
53 END
54ELSE
55 BEGIN
56create table KRAJ(
57IDKRAJ INT PRIMARY KEY NOT NULL,
58KRAJPROD CHAR(15) NOT NULL
59)
60END
61go
62
63IF EXISTS (SELECT * FROM dbo.sysobjects WHERE ID = OBJECT_ID(N'KLIENCI'))
64 BEGIN
65 PRINT 'Tabela KLIENCI już istnieje w BD!'
66 END
67ELSE
68 BEGIN
69create table KLIENCI(
70IDKLIENTA INT PRIMARY KEY,
71NAZWISKO CHAR(30) NOT NULL,
72IMIE CHAR(15) NOT NULL,
73WIEK INT,
74ADRES CHAR(30),
75TELEFON CHAR(15),
76PLEC CHAR(1)
77)
78END
79go
80
81IF EXISTS (SELECT * FROM dbo.sysobjects WHERE ID = OBJECT_ID(N'KASETY'))
82 BEGIN
83 PRINT 'Tabela KASETY już istnieje w BD!'
84 END
85ELSE
86 BEGIN
87create table KASETY(
88IDKASETY INT PRIMARY KEY NOT NULL,
89IDFILMU INT NOT NULL REFERENCES FILMY(IDFILMU),
90STAT CHAR(1) NOT NULL
91)
92END
93go
94
95IF EXISTS (SELECT * FROM dbo.sysobjects WHERE ID = OBJECT_ID(N'FILKRA'))
96 BEGIN
97 PRINT 'Tabela FILKRA już istnieje w BD!'
98 END
99ELSE
100 BEGIN
101create table FILKRA(
102IDFILMU INT NOT NULL REFERENCES FILMY(IDFILMU),
103IDKRAJ INT NOT NULL REFERENCES KRAJ(IDKRAJ)
104)
105END
106go
107
108IF EXISTS (SELECT * FROM dbo.sysobjects WHERE ID = OBJECT_ID(N'FILRODZ'))
109 BEGIN
110 PRINT 'Tabela FILRODZ już istnieje w BD!'
111 END
112ELSE
113 BEGIN
114create table FILRODZ(
115IDFILMU INT NOT NULL REFERENCES FILMY(IDFILMU),
116IDRODZAJ INT NOT NULL REFERENCES RODZAJ(IDRODZAJ)
117)
118END
119go
120
121IF EXISTS (SELECT * FROM dbo.sysobjects WHERE ID = OBJECT_ID(N'WYPO'))
122 BEGIN
123 PRINT 'Tabela WYPO już istnieje w BD!'
124 END
125ELSE
126 BEGIN
127create table WYPO(
128IDKLIENTA INT NOT NULL REFERENCES KLIENCI(IDKLIENTA),
129IDKASETY INT NOT NULL REFERENCES KASETY(IDKASETY),
130DATAW SMALLDATETIME NOT NULL,
131DATAZ SMALLDATETIME,
132KWOTA DECIMAL(7,2)
133)
134END
135
136GO
137
138-- CW 4 --
139
140-- 1 zakres dopuszczalnych wartości --
141
142ALTER TABLE FILMY ADD CHECK(CENA BETWEEN 1 AND 20);
143ALTER TABLE FILMY ADD CHECK(KOLOR IN ('K','C'));
144ALTER TABLE KASETY ADD CHECK(KOLOR IN ('K','W'));
145
146
147-- 2 wartość domyślna --
148
149ALTER TABLE KLIENCI ADD DEFAULT('Brak danych') FOR ADRES;
150ALTER TABLE WYPO ADD DEFAULT(GETDATE()) FOR DATAW;
151
152
153-- 3 4 reguły --
154
155CREATE RULE PLEC_KLI AS @X IN ('M','K');
156
157IF EXISTS (SELECT * FROM dbo.sysobjects WHERE ID = OBJECT_ID(N'PLEC_KLI'))
158 BEGIN
159 EXEC SP_BINDRULE PLEC_KLI,'KLIENCI.PLEC';
160 END
161ELSE
162 PRINT ('Reguła NIE istnieje!')
163
164-- 5 6 zmienna domyslna --
165
166CREATE DEFAULT NO_INFO AS ('Brak informacji');
167
168IF EXISTS (SELECT * FROM dbo.sysobjects WHERE ID = OBJECT_ID(N'NO_INFO'))
169 BEGIN
170 EXEC SP_BINDEFAULT NO_INFO,'FILMY.OPIS';
171 END
172ELSE
173 PRINT ('Zmienna NIE istnieje!')
174
175-- 7 uniktatowy klucz --
176
177
178-- Tomek czy te klucze UNIQUE dobrze dodane??? --
179
180-- FILKRA do IDFILMU i IDKRAJ --
181ALTER TABLE FILKRA ADD UNIQUE(IDFILMU, IDKRAJ);
182
183-- FILRODZ do IDFILMU i IDRODZAJ --
184ALTER TABLE FILRODZ ADD UNIQUE(IDFILMU, IDRODZAJ);
185
186
187-- WYPO do IDKLIENTA, IDKASETY i DATAW --
188ALTER TABLE WYPO ADD UNIQUE(IDKLIENTA, IDKASETY, DATAW);
189
190
191-- 8 widoki --
192GO
193
194CREATE VIEW W1 AS (
195 SELECT K.IMIE,K.NAZWISKO,W.DATAW,W.DATAZ, F.TYTUL
196 FROM KLIENCI AS K
197 JOIN WYPO AS W ON W.IDKLIENTA = K.IDKLIENTA
198 JOIN KASETY AS KA ON W.IDKASETY = KA.IDFILMU
199 JOIN FILMY AS F ON KA.IDFILMU = F.IDFILMU)
200
201GO
202
203CREATE VIEW W2 AS (
204 SELECT KA.IDKASETY,F.TYTUL, COUNT(W.DATAW) AS ILOSC_WYPO
205 FROM KASETY AS KA
206 JOIN FILMY AS F ON KA.IDFILMU = F.IDFILMU
207 JOIN WYPO AS W ON KA.IDKASETY = W.IDKASETY
208 GROUP BY KA.IDKASETY,F.TYTUL)
209
210-- 9 skrypt wprowadzający --
211
212-- INSERT INTO nazwa_tab (kolumna1, kolumna2, kolumna3) VALUES (1, 2, 3, 4, 5, 6)
213
214-- 10 skrypt usuwający --
215GO
216
217-- DELETE lub TRUNCATE
218
219--TRUNCATE TABLE FILRODZ
220--DELETE FROM WYPO
221--DELETE FROM KLIENCI
222--DELETE FROM KASETY
223--DELETE FROM FILMY
224--DELETE FROM REZYSER
225--DELETE FROM KRAJ
226--DELETE FROM RODZAJ
227--DELETE FROM FILKRA
228
229-- DROP usuwa całą tabele
230
231--DROP TABLE FILRODZ;
232--DROP TABLE WYPO;
233--DROP TABLE KLIENCI;
234--DROP TABLE KASETY;
235--DROP TABLE FILMY;
236--DROP TABLE REZYSER;
237--DROP TABLE KRAJ;
238--DROP TABLE RODZAJ;
239--DROP TABLE FILKRA;
240
241
242
243
244--Object type:
245
246--AF = Aggregate function (CLR)
247
248--C = CHECK constraint
249
250--D = DEFAULT (constraint or stand-alone)
251
252--F = FOREIGN KEY constraint
253
254--FN = SQL scalar function
255
256--FS = Assembly (CLR) scalar-function
257
258--FT = Assembly (CLR) table-valued function
259
260--IF = SQL inline table-valued function
261
262--IT = Internal table
263
264--P = SQL Stored Procedure
265
266--PC = Assembly (CLR) stored-procedure
267
268--PG = Plan guide
269
270--PK = PRIMARY KEY constraint
271
272--R = Rule (old-style, stand-alone)
273
274--RF = Replication-filter-procedure
275
276--S = System base table
277
278--SN = Synonym
279
280--SO = Sequence object
281
282--U = Table (user-defined)
283
284--V = View
285
286--EC = Edge constraint
287
288
289
290-- CW 5 --
291
292CREATE TABLE KOMUNIKATYWYZWALACZY (
293IDW INT IDENTITY(1,1),
294TABELA CHAR(30),
295KOLUMNA CHAR(15),
296OPERACJA CHAR(10),
297STARA_WART CHAR(50),
298NOWA_WART CHAR(50),
299CZAS SMALLDATETIME DEFAULT(GETDATE()),
300UZYTKOWNIK CHAR(20) DEFAULT(USER)
301)
302
303CREATE TRIGGER AFTER_UPDATE ON RODZAJ AFTER UPDATE
304AS
305INSERT INTO KOMUNIKATYWYZWALACZY (TABELA, KOLUMNA, OPERACJA, STARA_WART, NOWA_WART)
306VALUES ('RODZAJ','dowolna','UPDATE','Stary rodzaj','Nowy rodzaj')
307
308
309
310CREATE TRIGGER FUNKCJE_UPDATE_KLIENCI ON KLIENCI AFTER UPDATE
311AS
312IF UPDATE(NAZWISKO)
313INSERT INTO KOMUNIKATYWYZWALACZY (TABELA, KOLUMNA, OPERACJA, STARA_WART, NOWA_WART)
314VALUES ('KLIENCI', 'Nazwsisko', 'UPDATE', 'Stare nazwisko', 'Nowe nazwisko')
315IF UPDATE(IMIE)
316INSERT INTO KOMUNIKATYWYZWALACZY (TABELA, KOLUMNA, OPERACJA, STARA_WART, NOWA_WART)
317VALUES ('KLIENCI', 'Imie', 'UPDATE', 'Stare imie', 'Nowe imie')
318
319
320
321CREATE TRIGGER PO_AKT_KRAJ ON KRAJ AFTER UPDATE
322AS
323IF UPDATE(KRAJPROD)
324 DECLARE @kraj_OLD VARCHAR(15), @kraj_NEW VARCHAR(15)
325 SELECT @kraj_OLD = KRAJPROD
326 FROM DELETED
327 SELECT @kraj_NEW = KRAJPROD
328 FROM INSERTED
329INSERT INTO KOMUNIKATYWYZWALACZY (TABELA, KOLUMNA, OPERACJA, STARA_WART, NOWA_WART)
330VALUES ('KRAJ', 'KRAJPROD', 'UPDATE', @kraj_OLD, @kraj_NEW)
331GO
332
333
334CREATE TRIGGER PO_WSTAW_KRAJ ON KRAJ AFTER UPDATE
335AS
336IF UPDATE(KRAJPROD)
337 DECLARE @kraj_NEW VARCHAR(15)
338 SELECT @kraj_NEW= KRAJPROD
339 FROM INSERTED
340INSERT INTO KOMUNIKATYWYZWALACZY (TABELA, KOLUMNA, OPERACJA, STARA_WART, NOWA_WART)
341VALUES ('KRAJ', 'KRAJPROD', 'INSERT', NULL, @kraj_NEW)
342GO
343
344
345CREATE TRIGGER PO_USUN_KRAJ ON KRAJ AFTER DELETE
346AS
347IF UPDATE(KRAJPROD)
348 DECLARE @kraj_OLD VARCHAR(15)
349 SELECT @kraj_OLD = KRAJPROD
350 FROM DELETED
351INSERT INTO KOMUNIKATYWYZWALACZY (TABELA, KOLUMNA, OPERACJA, STARA_WART, NOWA_WART)
352VALUES ('KRAJ', 'KRAJPROD', 'DELETE', @kraj_OLD, NULL)