daily pastebin goal
60%
SHARE
TWEET

Untitled

a guest May 17th, 2018 100 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
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