Facebook
From Ungracious Cockroach, 6 Years ago, written in Plain Text.
Embed
Download Paste or View Raw
Hits: 250
  1. use s15409;
  2. DROP TABLE HISTORIATRENERA;
  3. DROP TABLE HISTORIAPILKARZA;
  4. DROP TABLE HISTORIAPENSPILK;
  5. DROP TABLE HISORIAPENSTR;
  6. DROP TABLE PILKARZ;
  7. DROP TABLE TRENER;
  8. DROP TABLE PENSJA;
  9. DROP TABLE DRUZYNA;
  10. DROP TABLE TABELA;
  11.  
  12. create table HISTORIAPILKARZA (
  13. IDPILKARZA INTEGER not null,
  14. IDDRUZYNY INTEGER not null,
  15. DATAOD DATETIME not null,
  16. DATADO DATETIME null, constraint HISTORIAPILKARZA_PK primary key (IDPILKARZA, IDDRUZYNY, DATAOD) );
  17.  
  18.  
  19. create table HISTORIATRENERA (
  20. IDDRUZYNY INTEGER not null,
  21. IDTRENERA INTEGER not null,
  22. DATAOD DATETIME not null,
  23. DATADO DATETIME null, constraint HISTORIATRENERA_PK primary key (DATAOD, IDTRENERA, IDDRUZYNY) );
  24.  
  25.  
  26. create table HISORIAPENSTR (
  27. IDPENSJI INTEGER not null,
  28. IDTRENERA INTEGER not null,
  29. DATAOD DATETIME not null,
  30. DATADO DATETIME null, constraint HISORIAPENSTR_PK primary key (IDPENSJI, IDTRENERA, DATAOD) );
  31.  
  32.  
  33. create table HISTORIAPENSPILK (
  34. IDPILKARZA INTEGER not null,
  35. IDPENSJI INTEGER not null,
  36. DATAOD DATETIME not null,
  37. DATADO DATETIME null, constraint HISTORIAPENSPILK_PK primary key (IDPILKARZA, IDPENSJI, DATAOD) );
  38.  
  39.  
  40. create table TRENER (
  41. IDTRENERA INTEGER not null,
  42. IMIE VARCHAR(100) null,
  43. NAZWISKO VARCHAR(100) null,
  44. IDPENSJI INTEGER null,
  45. IDDRUZYNY INTEGER null, constraint TRENER_PK primary key (IDTRENERA) );
  46.  
  47.  
  48. create table PILKARZ (
  49. IDPILKARZA INTEGER not null,
  50. IMIE VARCHAR(100) null,
  51. NAZWISKO VARCHAR(100) null,
  52. IDPENSJI INTEGER null,
  53. IDDRUZYNY INTEGER null, constraint PILKARZ_PK primary key (IDPILKARZA) );
  54.  
  55.  
  56. create table PENSJA (
  57. IDPENSJI INTEGER not null,
  58. WIELKOSC INTEGER not null, constraint PENSJA_PK primary key (IDPENSJI) );
  59.  
  60. create table DRUZYNA (
  61. IDDRUZYNY INTEGER not null,
  62. MIEJSCE INTEGER null, constraint DRUZYNA_PK primary key (IDDRUZYNY) );
  63.  
  64.  
  65. create table TABELA (
  66. MIEJSCE INTEGER not null, constraint TABELA_PK primary key (MIEJSCE) );
  67.  
  68.  
  69. alter table HISTORIAPILKARZA
  70. add constraint PILKARZ_HISTORIAPILKARZA_FK1 foreign key (
  71. IDPILKARZA)
  72. references PILKARZ (
  73. IDPILKARZA);
  74.  
  75. alter table HISTORIAPILKARZA
  76. add constraint DRUZYNA_HISTORIAPILKARZA_FK1 foreign key (
  77. IDDRUZYNY)
  78. references DRUZYNA (
  79. IDDRUZYNY);
  80.  
  81.  
  82. alter table HISTORIATRENERA
  83. add constraint DRUZYNA_HISTORIATRENERA_FK1 foreign key (
  84. IDDRUZYNY)
  85. references DRUZYNA (
  86. IDDRUZYNY);
  87.  
  88. alter table HISTORIATRENERA
  89. add constraint TRENER_HISTORIATRENERA_FK1 foreign key (
  90. IDTRENERA)
  91. references TRENER (
  92. IDTRENERA);
  93.  
  94.  
  95. alter table HISORIAPENSTR
  96. add constraint PENSJA_HISORIAPENSTR_FK1 foreign key (
  97. IDPENSJI)
  98. references PENSJA (
  99. IDPENSJI);
  100.  
  101. alter table HISORIAPENSTR
  102. add constraint TRENER_HISORIAPENSTR_FK1 foreign key (
  103. IDTRENERA)
  104. references TRENER (
  105. IDTRENERA);
  106.  
  107.  
  108. alter table HISTORIAPENSPILK
  109. add constraint PILKARZ_HISTORIAPENSPILK_FK1 foreign key (
  110. IDPILKARZA)
  111. references PILKARZ (
  112. IDPILKARZA);
  113.  
  114. alter table HISTORIAPENSPILK
  115. add constraint PENSJA_HISTORIAPENSPILK_FK1 foreign key (
  116. IDPENSJI)
  117. references PENSJA (
  118. IDPENSJI);
  119.  
  120.  
  121. alter table TRENER
  122. add constraint PENSJA_TRENER_FK1 foreign key (
  123. IDPENSJI)
  124. references PENSJA (
  125. IDPENSJI);
  126.  
  127. alter table TRENER
  128. add constraint DRUZYNA_TRENER_FK1 foreign key (
  129. IDDRUZYNY)
  130. references DRUZYNA (
  131. IDDRUZYNY);
  132.  
  133.  
  134. alter table PILKARZ
  135. add constraint PENSJA_PILKARZ_FK1 foreign key (
  136. IDPENSJI)
  137. references PENSJA (
  138. IDPENSJI);
  139.  
  140. alter table PILKARZ
  141. add constraint DRUZYNA_PILKARZ_FK1 foreign key (
  142. IDDRUZYNY)
  143. references DRUZYNA (
  144. IDDRUZYNY);
  145.  
  146.  
  147. alter table DRUZYNA
  148. add constraint TABELA_DRUZYNA_FK1 foreign key (
  149. MIEJSCE)
  150. references TABELA (
  151. MIEJSCE);
  152.  
  153. INSERT INTO TABELA VALUES (1);
  154. INSERT INTO TABELA VALUES (2);
  155. INSERT INTO TABELA VALUES (3);
  156. INSERT INTO TABELA VALUES (4);
  157.  
  158. INSERT INTO DRUZYNA VALUES(101,1);
  159. INSERT INTO DRUZYNA VALUES(102,2);
  160. INSERT INTO DRUZYNA VALUES(103,3);
  161.  
  162. INSERT INTO PENSJA VALUES(201,200000);
  163. INSERT INTO PENSJA VALUES(202,30000);
  164. INSERT INTO PENSJA VALUES(203,200000);
  165. INSERT INTO PENSJA VALUES(204,200000);
  166. INSERT INTO PENSJA VALUES(205,110000);
  167. INSERT INTO PENSJA VALUES(206,140000);
  168. INSERT INTO PENSJA VALUES(207,80000);
  169. INSERT INTO PENSJA VALUES(208,200000);
  170.  
  171. INSERT INTO TRENER VALUES(301,'Jurgen','Klopp',201,101);
  172. INSERT INTO TRENER VALUES(302,'Peter','Krawietz',202,101);
  173. INSERT INTO TRENER VALUES(303,'Chris','Morgan',203,101);
  174.  
  175.  
  176.  
  177.  
  178. INSERT INTO PILKARZ VALUES(401,'Sadio','Mane',204,101);
  179. INSERT INTO PILKARZ VALUES(402,'Adam','Lallana',205,101);
  180. INSERT INTO PILKARZ VALUES(403,'Adam','Lallana',206,101);
  181. INSERT INTO PILKARZ VALUES(411,'Victor','Moses',207,102);
  182. INSERT INTO PILKARZ VALUES(412,'NGolo','Cante',208,102);
  183.  
  184. INSERT INTO HISORIAPENSTR VALUES(201,301,CONVERT(DATETIME, '2015-01-22'),CONVERT(DATETIME, '2022-11-02'));
  185. INSERT INTO HISORIAPENSTR VALUES(202,302,CONVERT(DATETIME, '2016-02-22'),CONVERT(DATETIME, '2022-11-02'));
  186. INSERT INTO HISORIAPENSTR VALUES(203,303,CONVERT(DATETIME, '2014-07-22'),CONVERT(DATETIME, '2022-11-02'));
  187.  
  188. INSERT INTO HISTORIAPENSPILK VALUES(401,204,CONVERT(DATETIME, '2013-05-2'),CONVERT(DATETIME, '2022-11-02'));
  189. INSERT INTO HISTORIAPENSPILK VALUES(402,205,CONVERT(DATETIME, '2014-05-4'),CONVERT(DATETIME, '2022-11-02'));
  190. INSERT INTO HISTORIAPENSPILK VALUES(403,206,CONVERT(DATETIME, '2016-02-12'),CONVERT(DATETIME, '2022-11-02'));
  191. INSERT INTO HISTORIAPENSPILK VALUES(411,207,CONVERT(DATETIME, '2015-02-11'),CONVERT(DATETIME, '2022-11-02'));
  192. INSERT INTO HISTORIAPENSPILK VALUES(412,208,CONVERT(DATETIME, '2016-01-10'),CONVERT(DATETIME, '2022-11-02'));
  193.  
  194. INSERT INTO HISTORIAPILKARZA VALUES (401,101,CONVERT(DATETIME, '2005-05-2'),CONVERT(DATETIME, '2012-11-02'));
  195. INSERT INTO HISTORIAPILKARZA VALUES (401,101,CONVERT(DATETIME, '2013-05-2'),CONVERT(DATETIME, '2022-11-02'));
  196. INSERT INTO HISTORIAPILKARZA VALUES (402,101,CONVERT(DATETIME, '2014-05-4'),CONVERT(DATETIME, '2022-11-02'));
  197. INSERT INTO HISTORIAPILKARZA VALUES (403,101,CONVERT(DATETIME, '2016-02-12'),CONVERT(DATETIME, '2022-11-02'));
  198. INSERT INTO HISTORIAPILKARZA VALUES (411,102,CONVERT(DATETIME, '2015-02-11'),CONVERT(DATETIME, '2022-11-02'));
  199. INSERT INTO HISTORIAPILKARZA VALUES (412,102,CONVERT(DATETIME, '2016-01-10'),CONVERT(DATETIME, '2022-11-02'));
  200.  
  201. INSERT INTO HISTORIATRENERA VALUES (101,301,CONVERT(DATETIME, '2015-01-22'),CONVERT(DATETIME, '2022-11-02'));
  202. INSERT INTO HISTORIATRENERA VALUES (101,302,CONVERT(DATETIME, '2016-02-22'),CONVERT(DATETIME, '2022-11-02'));
  203. INSERT INTO HISTORIATRENERA VALUES (101,303,CONVERT(DATETIME, '2014-07-22'),CONVERT(DATETIME, '2022-11-02'));
  204.  
  205.  
  206.  
  207. --wypisz wszystkie dane z tabeli pilkarz//
  208. SELECT *
  209. FROM PILKARZ;
  210. --wypisz nazwiska pilkarzy ktore maja druga litere 'a' w nazwisku//
  211. SELECT NAZWISKO
  212. FROM PILKARZ
  213. WHERE NAZWISKO LIKE '_a%';
  214. --wypisz nazwiska trenera idTrenera miedzy 301 i 302//
  215. SELECT NAZWISKO
  216. FROM TRENER
  217. WHERE IDTRENERA BETWEEN 301 AND 302;
  218. --Obok imienia i nazwiska pilkarza, wypisz nazwe druzyny, w ktorej ten pilkarz gra//
  219. SELECT PILKARZ.idPilkarza,PILKARZ.IMIE, PILKARZ.NAZWISKO, DRUZYNA.IDDRUZYNY
  220. FROM PILKARZ
  221. INNER JOIN DRUZYNA ON PILKARZ.IDDRUZYNY=DRUZYNA.IDDRUZYNY;
  222. --Obok imienia i nazwiska pilkarza, wypisz nazwe druzyny, w ktorym ten pilkraz gra. Uwzglednij tez te druzyny, w ktorych nikt nie gra.//
  223. SELECT PILKARZ.IMIE,PILKARZ.NAZWISKO,PILKARZ.IDDRUZYNY "DRUZYNA PILKARZA",DRUZYNA.IDDRUZYNY "ID DRUZYNY"
  224. FROM PILKARZ
  225. RIGHT OUTER JOIN DRUZYNA ON PILKARZ.IDDRUZYNY = DRUZYNA.IDDRUZYNY;
  226. --Obok idTrenera , wypisz nazwe druzyny, w ktorej ten trener pracuje, idTrenera posortuj malejaca//
  227. SELECT TRENER.idTrenera,DRUZYNA.IDDRUZYNY
  228. FROM TRENER
  229. INNER JOIN DRUZYNA ON TRENER.IDDRUZYNY=DRUZYNA.IDDRUZYNY
  230. ORDER BY 1 DESC;
  231. --Wypisac w ilu druzynach gral pilkarz
  232. SELECT PILKARZ.IDPILKARZA, COUNT(h.DATAOD)
  233. FROM PILKARZ
  234. INNER JOIN HISTORIAPILKARZA h ON PILKARZ.IDPILKARZA = h.IDPILKARZA
  235. GROUP BY PILKARZ.IDPILKARZA;
  236.  
  237. --Wypisac w ilu druzynach byl trener posortowwane w odwrotnej kolejnosci wedlug idtrenera
  238. SELECT TRAIN.IDTRENERA, COUNT(h.DATAOD)
  239. FROM TRENER TRAIN
  240. INNER JOIN HISTORIATRENERA h ON TRAIN.IDTRENERA = h.IDTRENERA
  241. GROUP BY TRAIN.IDTRENERA
  242. ORDER BY TRAIN.IDTRENERA DESC;
  243.  
  244. --Wypisac o ilosci pilkarzy dla kazdej pensji oprocz pensji = 80000$
  245. SELECT GOLDBOOT.WIELKOSC, COUNT(P.IDPILKARZA)
  246. FROM PENSJA GOLDBOOT
  247. INNER JOIN PILKARZ P ON P.IDPENSJI = GOLDBOOT.IDPENSJI
  248. GROUP BY GOLDBOOT.WIELKOSC
  249. HAVING GOLDBOOT.WIELKOSC <> '80000$';
  250.  
  251. --W parametrach procedury podac imie i nazwisko trenera
  252.  
  253.  
  254.  
  255.  
  256.  
  257. --procedura wyswietli na ekran miejsce,ktore zajmuje druzyna
  258. --tego trenera
  259. CREATE PROCEDURE
  260. imNazwTrenM
  261. @v_im VARCHAR(100),@v_nazw VARCHAR(100)
  262. AS
  263. BEGIN
  264. SELECT d.miejsce
  265. FROM druzyna d
  266. INNER JOIN Trener t ON d.idDruzyny = t.idDruzyny
  267. WHERE t.imie = @v_im AND t.nazwisko=@v_nazw;
  268. END;
  269.  
  270. EXECUTE imNazwTrenM 'Jurgen', 'Klopp'
  271.  
  272.  
  273. --w parametrach procedury podac idDruzyny
  274. --wypiszy sie imie i nazwisko kazdego pilkarza
  275. --jezeli pilkarz zarabia wiecej niz 115000$
  276. --do jego imienia, nazwiska bedzie dopisane "TOP PLAYER"
  277.  
  278. CREATE PROCEDURE druzynaPilk
  279. @v_idDr INTEGER
  280. AS
  281. DECLARE kursor CURSOR FOR SELECT pi.imie,pi.nazwisko,pe.wielkosc
  282. FROM Pilkarz pi
  283. INNER JOIN Pensja pe ON pi.idPensji=pe.idPensji
  284. WHERE pi.idDruzyny = @v_idDr;
  285. DECLARE @kurs_imie VARCHAR(100),@kurs_nazwisko VARCHAR(100),
  286. @kurs_wielkosc VARCHAR(100);
  287. OPEN kursor;
  288. FETCH NEXT FROM kursor INTO @kurs_imie,@kurs_nazwisko,@kurs_wielkosc;
  289. WHILE @@FETCH_STATUS = 0
  290. BEGIN
  291. IF @kurs_wielkosc < 115000
  292. BEGIN
  293. PRINT @kurs_imie + ' ' + @kurs_nazwisko;
  294. END;
  295. ELSE
  296. BEGIN
  297. PRINT @kurs_imie + ' ' + @kurs_nazwisko+ ' Top player';
  298. END;
  299. FETCH NEXT FROM kursor INTO @kurs_imie,@kurs_nazwisko,@kurs_wielkosc;  
  300. END;
  301. Close kursor;
  302. Deallocate kursor;
  303.  
  304.  
  305.  
  306. EXECUTE druzynaPilk 102;
  307.  
  308.  
  309. --jezeli robimy w robimy update w pilkarzu
  310. -- i on ma na imie Victor Moses, to zmniejszamy pensje o 10%
  311.  
  312.  
  313. CREATE TRIGGER updVictorMoses ON
  314.  
  315. PILKARZ FOR UPDATE
  316.  
  317. AS
  318.  
  319. BEGIN
  320.  
  321. DECLARE
  322. @vimie VARCHAR,@vnaz VARCHAR,@pensja int;
  323. Select @vimie = Imie , @vnaz = Nazwisko from inserted;
  324. IF @vimie='Victor' AND @vnaz='Moses'
  325. BEGIN
  326. Update Pensja
  327. set WIELKOSC = WIELKOSC *0.90
  328. where IDPENSJI = (Select IDPENSJI from PILKARZ
  329.                                                                                         where Imie = @vimie and NAZWISKO = @vnaz);
  330. END;
  331. END;
  332.  
  333. --jezeli do tabeli historiaPensjiPilkarza
  334. --wpisano bledna date, bedzie usuniety ten rekord
  335.  
  336.  
  337.  
  338. CREATE TRIGGER hist ON
  339.  
  340. HISTORIAPENSPILK FOR INSERT, UPDATE
  341.  
  342. AS
  343.  
  344. BEGIN
  345.  
  346.  
  347. DECLARE kurs CURSOR FOR SELECT dataOd
  348.                                                 FROM HISTORIAPENSPILK;
  349. DECLARE
  350. @data date;
  351. OPEN kurs
  352. FETCH NEXT FROM kurs INTO @data
  353. WHILE @@FETCH_STATUS = 0
  354. BEGIN
  355. IF @data > GETDATE()
  356. BEGIN
  357. DELETE HISTORIAPENSPILK WHERE dataOd=@data;
  358. PRINT 'Znaleziono bledno wpisana date';
  359.  
  360.  
  361. END;
  362. FETCH NEXT FROM kurs INTO @data
  363. END;
  364. END;
  365.  
  366. INSERT INTO HISTORIAPENSPILK(IDPILKARZA,IDPENSJI,DATAOD,DATADO)
  367. VALUES(401,204,CONVERT(DATETIME, '2020-01-22'),CONVERT(DATETIME, '2022-11-02'));
  368.  
  369.