SHARE
TWEET

Untitled

a guest May 17th, 2018 101 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. --Utw贸rz tabele klient(id_klient, nazwisko, nr_prawa_jazdy), gdzie:
  2. --id_klient - automatycznie nadawany, niepusty numer klienta, klucz g艂贸wny,
  3. --nazwisko - niepusty 艂a艅cuch znak贸w zmiennej d艂ugo艣ci od 3 do 20 znak贸w,
  4. --nr_prawa_jazdy - numer prawa jazdy w formacie 5liczb / 2liczby/4 liczby np 12345/12/1234
  5. -- tabel臋 car(id_car, typ, cena)
  6. --id_car -automatycznie nadawany, niepusty numer auta, klucz g艂贸wny,
  7. -- typ - niepusty 艂a艅cuch znak贸w zmiennej d艂ugo艣ci od 3 do 20 znak贸w,
  8. -- cena - waluta
  9.  
  10. --oraz tabel臋 rezerwacja (id_rezerwacja, id_klient, id_car,data_pocz,data_konc), gdzie:
  11. --id_rezerwacja - niepusty klucz g艂贸wny, warto艣ci nadawane automatycznie,
  12. --id_klient - niepusty klucz obcy powi膮zany z kolumn膮 id_klient tabeli klient,
  13. --id_car - niepusty klucz obcy powi膮zany z kolumn膮 id_car tabeli car,
  14. --data_pocz - data
  15. --data_konc - data
  16. --Rozwi膮zanie [3pkt]:
  17.  
  18. CREATE TABLE klient(
  19. id_klient INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
  20. nazwisko VARCHAR(20) NOT NULL CHECK(LEN(nazwisko)<20 AND LEN(nazwisko)>3),
  21. nr_prawa_jazdy VARCHAR(13)  CHECK (LEN(nr_prawa_jazdy)=13),
  22. );
  23.  
  24. CREATE TABLE car(
  25. id_car INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
  26. typ VARCHAR(20) NOT NULL CHECK(LEN(typ)<20 AND LEN(typ)>3),
  27. cena DECIMAL,
  28. );
  29.  
  30. CREATE TABLE rezerwacja(
  31. id_rezerwacja INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
  32. id_klient INT FOREIGN KEY REFERENCES klient(id_klient),
  33. id_car INT FOREIGN KEY REFERENCES car(id_car),
  34. data_pocz DATE,
  35. data_konc DATE,
  36. );
  37.  
  38. ----------------------------------------------------------------------------------------------------
  39.  
  40. --Utw贸rz widok raport(id_klient ,nazwisko,nr_prawa_jazdy,suma_pieniedzy), gdzie
  41. --id_klient, nazwisko, n_prawa_jazdy to kolumny z tabeli klient
  42. --suma_pieniedzy - 艂膮czna suma wydanych pieni臋dzy przed danego klienta (ilo艣膰 dni*cena)
  43. --Rozwi膮zanie [2 pkt]
  44.  
  45. CREATE VIEW raport AS SELECT k.id_klient, k.nazwisko, k.nr_prawa_jazdy, SUM(c.cena*DATEDIFF(dd, r.data_pocz, r.data_konc )) AS suma_pieniedzy
  46. FROM klient k LEFT JOIN rezerwacja r ON k.id_klient=r.id_klient
  47.         LEFT JOIN car c ON r.id_car=c.id_car
  48.         GROUP BY k.nazwisko, k.nr_prawa_jazdy, k.id_klient
  49.  
  50. ----------------------------------------------------------------------------------------------------
  51.  
  52. --Utw贸rz wyzwalacz, kt贸ry reaguje na modyfikacje danych klient贸w  w widoku raport - dane maj膮 by膰 zmodyfikowane tak偶e w tabeli klient.
  53. --Rozwi膮zanie + test [3 pkt]
  54.  
  55. CREATE TRIGGER wyzwalacz2 ON raport
  56. INSTEAD OF UPDATE
  57. AS
  58. BEGIN
  59.     DECLARE kursor CURSOR FOR SELECT id_klient, nazwisko, nr_prawa_jazdy FROM inserted
  60.     DECLARE @id_klient INT, @nazwisko VARCHAR(20), @nr_prawa_jazdy VARCHAR(13)
  61.     OPEN kursor
  62.     FETCH NEXT FROM kursor INTO @id_klient, @nazwisko, @nr_prawa_jazdy
  63.     WHILE @@FETCH_STATUS=0
  64.     BEGIN
  65.         UPDATE klient SET nazwisko=@nazwisko, nr_prawa_jazdy=@nr_prawa_jazdy  WHERE id_klient=@id_klient
  66.         FETCH NEXT FROM kursor INTO @id_klient, @nazwisko, @nr_prawa_jazdy
  67.     END
  68.     CLOSE kursor
  69.     DEALLOCATE kursor
  70. END;
  71.  
  72. -----------------------------------------------------------------------------------------------------
  73.  
  74. --Do tabeli klient dodaj kolumn臋 usuni臋ty, z domy艣ln膮 warto艣ci膮 0, zadbaj o to, aby kolumna nie by艂a pusta.
  75. --Rozwi膮zanie + test  [1 pkt]:
  76.  
  77. ALTER TABLE klient
  78. ADD usuniety INT DEFAULT 0;
  79.  
  80. -----------------------------------------------------------------------------------------------------
  81.  
  82. --Napisa膰 wyzwalacz, kt贸ry zamiast usun膮膰 fizycznie rekord z tabeli klient zmieni
  83. --warto艣膰 kolumny usuni臋ty z 0 na 1,
  84. --Rozwi膮zanie  + test [2 pkt]:
  85.  
  86. CREATE TRIGGER wyzwalacz on klient
  87. INSTEAD OF DELETE
  88. AS
  89. BEGIN
  90. UPDATE klient SET usuniety=1 WHERE id_klient IN (SELECT id_klient FROM DELETED)
  91. END
  92.  
  93. _____________________________________________________________________________________________________
  94.  
  95. --Napisa脛鈥 funkcj脛鈩 pomocnicz脛鈥 sprawdz_pjazdy zwracaj脛鈥脛鈥 warto墓鈥好勨 0 je墓鈥簂i numer prawa jazdy nie jest we w墓鈥歛墓鈥篶iwym formacie oraz 1 w przeciwnym wypadku.
  96. -- Rozwi脛鈥anie +test [2pkt]
  97.  
  98. CREATE FUNCTION spr_pojazdy(@pojazd VARCHAR(14))
  99. RETURNS BIT
  100. BEGIN
  101.     IF @pojazd LIKE '[0-9][0-9][0-9][0-9][0-9]/[0-9][0-9]/[0-9][0-9][0-9][0-9]'
  102.         RETURN 1
  103.     RETURN 0
  104. END;
  105.  
  106. ------------------------------------------------------------------------------------------------------
  107.  
  108. --Napisz wyzwalacz, kt膫艂ry uniemo墓慕liwi modyfikacje numer膫艂w prawa jazdy na niepoprawny format. Wykorzystaj funkcj脛鈩 sprawdz_pjazdy.
  109. --Rozwi脛鈥anie [2 pkt]
  110.  
  111. CREATE TRIGGER wyzwalacz3 on klient
  112. AFTER UPDATE
  113. AS
  114. BEGIN
  115.     DECLARE @nr_prawa_jazdy VARCHAR(30)
  116.     SET @nr_prawa_jazdy=-1
  117.     SELECT @nr_prawa_jazdy=nr_prawa_jazdy FROM inserted
  118.     IF dbo.spr_pojazdy(@nr_prawa_jazdy)=0
  119.         BEGIN
  120.             RAISERROR('Niepoprawny numer prawa jazdy, zly format',1,2)
  121.             ROLLBACK   
  122.         END
  123. END;
  124.  
  125. ------------------------------------------------------------------------------------------------------
  126.  
  127. nale偶y uniemo偶liwi膰 dodanie rekordu z niepoprawnym numerem pesel, napisz funkcj臋 pomocnicz膮, wy艣wietl odpowiedni komunikat b艂臋du,
  128.  
  129. CREATE FUNCTION spr_pesel (@nr_pesel VARCHAR (14))
  130. RETURNS BIT
  131. AS
  132. BEGIN
  133.     IF (ISNUMERIC(@nr_pesel)=0) OR (LEN(@nr_pesel)!=11)
  134.     RETURN 0
  135.     DECLARE @wagi AS TABLE ( pozycja TINYINT IDENTITY (1,1), waga TINYINT)
  136.     INSERT INTO @wagi VALUES (1),(3),(7),(9),(1),(3),(7),(9),(1),(3),(1)
  137.     IF (SELECT SUM(CONVERT (TINYINT, SUBSTRING (@nr_pesel,pozycja,1))*waga)%10 FROM @wagi)=0
  138.         RETURN 1
  139.     RETURN 0
  140.     END
  141.  
  142. CREATE TRIGGER wyzwalacz4 on osoba
  143. INSTEAD OF INSERT
  144. AS
  145. BEGIN
  146.     DECLARE @pesel VARCHAR(30),  @nazwisko VARCHAR(30), @data_ur DATE, @pensja MONEY
  147.     SET @pesel=-1
  148.     SELECT @pesel=pesel FROM inserted
  149.     IF dbo.spr_pesel(@pesel)=0
  150.         BEGIN
  151.             RAISERROR('Niepoprawny numer prawa jazdy, zly format',1,2)
  152.             ROLLBACK   
  153.         END
  154.     IF dbo.spr_pesel(@pesel)=1
  155.         BEGIN
  156.             SELECT @nazwisko=nazwisko, @data_ur=data_ur, @pensja=pensja FROM inserted
  157.             INSERT INTO osoba(nazwisko,pesel,data_ur,pensja) VALUES (@nazwisko,@pesel,@data_ur,@pensja)
  158.         END
  159.         END;
  160.  
  161. -------------------------------------------------------------------------------------------------------------------
  162.  
  163. w dodawanym rekordzie nale偶y poprawi膰 imi臋 tak, aby zawsze si臋 zaczyna艂o z wielkiej litery, a pozosta艂e litery by艂y ma艂e,
  164.  
  165. CREATE TRIGGER wyzwalacz5 on osoba
  166. INSTEAD OF INSERT
  167. AS
  168. BEGIN
  169.     DECLARE @pesel VARCHAR(30),  @nazwisko VARCHAR(30), @data_ur DATE, @pensja MONEY
  170.     SET @pesel=-1
  171.     SELECT @nazwisko=nazwisko FROM inserted
  172.      SELECT @data_ur=data_ur, @pensja=pensja, @pesel=pesel FROM inserted
  173.             INSERT INTO osoba(nazwisko,pesel,data_ur,pensja) VALUES (UPPER(LEFT(@nazwisko,1))+ LOWER(SUBSTRING(@nazwisko,2,LEN(@nazwisko))),@pesel,@data_ur,@pensja)
  174.             END;
  175.  
  176. ----------------------------------------------------------------------------------------------------------------------
  177.  
  178. dodawana pensja nie mo偶e by膰 mniejsza ni偶 1111 z艂, w przeciwnym wypadku wy艣wietl odpowiedni komunikat b艂臋du,
  179.  
  180.             CREATE TRIGGER wyzwalacz6 on osoba
  181. AFTER INSERT
  182. AS
  183. BEGIN
  184.     DECLARE @pensja MONEY
  185.     SELECT  @pensja=pensja FROM inserted
  186.     IF @pensja<1111
  187.         BEGIN
  188.             RAISERROR('Za niska cena',1,2)
  189.             ROLLBACK   
  190.         END
  191.             END;
  192.  
  193. ----------------------------------------------------------------------------------------------------------------------
  194.  
  195. dodawana osoba nie moze byc pelnoletnia
  196.  
  197. CREATE TRIGGER wyzwalacz7 on osoba
  198. AFTER INSERT
  199. AS
  200. BEGIN
  201.     DECLARE @data_ur DATE
  202.     SELECT  @data_ur=data_ur FROM inserted
  203.     IF DATEDIFF(yy,@data_ur,GETDATE())<18
  204.         BEGIN
  205.             RAISERROR('Za mlody wiek',1,2)
  206.             ROLLBACK   
  207.         END
  208.             END;
  209.  
  210. ----------------------------------------------------------------------------------------------------------------------
  211.  
  212. ilosc jako kwota wszystkich wypozyczen klienta
  213.  
  214. SELECT k.id_klient, k.nazwisko, k.nr_prawa_jazdy, SUM(c.cena*DATEDIFF(dd, r.data_pocz, r.data_konc )) AS ilosc
  215. FROM klient k LEFT JOIN rezerwacja r ON k.id_klient=r.id_klient
  216.         LEFT JOIN car c ON r.id_car=c.id_car
  217.         GROUP BY k.nazwisko, k.nr_prawa_jazdy, k.id_klient
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top