8pFQis7L

1use mrzeppa
2
3DROP TABLE IF EXISTS rezerwacja
4
5DROP TABLE IF EXISTS klient
6
7DROP TABLE IF EXISTS pokoj
8
9
10
11CREATE TABLE klient (
12	id_klient INT NOT NULL PRIMARY KEY IDENTITY (1, 1),
13	nazwisko VARCHAR(20) NOT NULL, CHECK(LEN(nazwisko) >= 3),
14	imie VARCHAR(20) NOT NULL, CHECK(LEN(imie) >= 3),
15	pesel VARCHAR(11) NOT NULL, CHECK(LEN(pesel) = 11)
16);
17CREATE TABLE pokoj (
18	id_pokoj INT NOT NULL PRIMARY KEY IDENTITY (1, 1),
19	typ VARCHAR(20) NOT NULL, CHECK(LEN(typ) >= 3),
20	cena MONEY
21);
22CREATE TABLE rezerwacja (
23	id_rezerwacja INT NOT NULL PRIMARY KEY IDENTITY (1, 1),
24	id_klient INT FOREIGN KEY REFERENCES klient (id_klient),
25	id_pokoj INT FOREIGN KEY REFERENCES pokoj (id_pokoj),
26	data_pocz DATE,
27	data_konc DATE
28);
29
30INSERT INTO klient VALUES
31	('AAAA', 'BBBB', '11111111111'),
32	('KKKK', 'yyyyyy', '32132132132'),
33	('dsadsadas', 'aasddddbbbb', '22222222233'),
34	('yzyzyzyz', 'xxxx', '78978978978');
35	
36INSERT INTO pokoj VALUES
37	('DUZY', 50.00),
38	('MALY', 20.00),
39	('SREDNI', 34.00),
40	('1OS', 15.00);
41	
42INSERT INTO rezerwacja VALUES
43	(1, 3, '2019.01.15', '2019-02-01'),
44	(2, 1, '2019.02.15', '2019-02-19'),
45	(3, 2, '2019.01.31', '2019-02-21'),
46	(4, 4, '2019.01.18', '2019-02-18');
47	
48
49CREATE VIEW xxx
50AS
51	SELECT k.id_klient, k.nazwisko, k.pesel, SUM(DATEDIFF(day, r.data_pocz, r.data_konc))*p.cena AS suma_pieniedzy FROM klient k
52	INNER JOIN rezerwacja r ON k.id_klient = r.id_klient
53	INNER JOIN pokoj p ON p.id_pokoj = r.id_pokoj
54	GROUP BY k.nazwisko, k.id_klient, k.pesel, p.cena
55	
56SELECT * FROM xxx
57
58ALTER TABLE klient ADD staly INT NOT NULL DEFAULT 0
59
60SELECT * FROM klient
61
62CREATE TRIGGER zmienStaly ON rezerwacja
63AFTER INSERT
64AS
65BEGIN
66	DECLARE @k_id INT
67	SET @k_id = (SELECT id_klient FROM inserted)
68	UPDATE klient SET staly = 1 WHERE id_klient = @k_id
69END
70
71SELECT * FROM rezerwacja
72INSERT INTO rezerwacja VALUES
73	(4, 2, '2019-02-02', '2019.01.13')
74SELECT * FROM rezerwacja
75
76	
77ALTER FUNCTION sprawdz_daty (@rez_id INT)
78RETURNS BIT
79AS
80BEGIN
81	DECLARE @data1 DATE
82	DECLARE @data2 DATE
83	SET @data1 = (SELECT data_pocz FROM rezerwacja WHERE id_rezerwacja = @rez_id)
84	SET @data2 = (SELECT data_konc FROM rezerwacja WHERE id_rezerwacja = @rez_id)
85	IF (DATEDIFF(day, @data1, @data2) > 0)
86		RETURN 1
87	RETURN 0
88END
89
90SELECT dbo.sprawdz_daty(6)
91
92CREATE TRIGGER zleDaty ON rezerwacja
93AFTER INSERT
94AS
95BEGIN
96	DECLARE @id INT
97	SET @id = (SELECT id_rezerwacja FROM inserted)
98	IF (dbo.sprawdz_daty(@id) = 0)
99		UPDATE rezerwacja SET data_konc = data_pocz WHERE id_rezerwacja = @id
100END