daily pastebin goal
34%
SHARE
TWEET

Untitled

a guest May 17th, 2018 99 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ącą wartość 0 jeśli numer prawa jazdy nie jest we właściwym formacie oraz 1 w przeciwnym wypadku.
  96. -- RozwiÄ…zanie +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Ä…zanie [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
Top