- use s15409;
- DROP TABLE HISTORIATRENERA;
- DROP TABLE HISTORIAPILKARZA;
- DROP TABLE HISTORIAPENSPILK;
- DROP TABLE HISORIAPENSTR;
- DROP TABLE PILKARZ;
- DROP TABLE TRENER;
- DROP TABLE PENSJA;
- DROP TABLE DRUZYNA;
- DROP TABLE TABELA;
- create table HISTORIAPILKARZA (
- IDPILKARZA INTEGER not null,
- IDDRUZYNY INTEGER not null,
- DATAOD DATETIME not null,
- DATADO DATETIME null, constraint HISTORIAPILKARZA_PK primary key (IDPILKARZA, IDDRUZYNY, DATAOD) );
- create table HISTORIATRENERA (
- IDDRUZYNY INTEGER not null,
- IDTRENERA INTEGER not null,
- DATAOD DATETIME not null,
- DATADO DATETIME null, constraint HISTORIATRENERA_PK primary key (DATAOD, IDTRENERA, IDDRUZYNY) );
- create table HISORIAPENSTR (
- IDPENSJI INTEGER not null,
- IDTRENERA INTEGER not null,
- DATAOD DATETIME not null,
- DATADO DATETIME null, constraint HISORIAPENSTR_PK primary key (IDPENSJI, IDTRENERA, DATAOD) );
- create table HISTORIAPENSPILK (
- IDPILKARZA INTEGER not null,
- IDPENSJI INTEGER not null,
- DATAOD DATETIME not null,
- DATADO DATETIME null, constraint HISTORIAPENSPILK_PK primary key (IDPILKARZA, IDPENSJI, DATAOD) );
- create table TRENER (
- IDTRENERA INTEGER not null,
- IMIE VARCHAR(100) null,
- NAZWISKO VARCHAR(100) null,
- IDPENSJI INTEGER null,
- IDDRUZYNY INTEGER null, constraint TRENER_PK primary key (IDTRENERA) );
- create table PILKARZ (
- IDPILKARZA INTEGER not null,
- IMIE VARCHAR(100) null,
- NAZWISKO VARCHAR(100) null,
- IDPENSJI INTEGER null,
- IDDRUZYNY INTEGER null, constraint PILKARZ_PK primary key (IDPILKARZA) );
- create table PENSJA (
- IDPENSJI INTEGER not null,
- WIELKOSC INTEGER not null, constraint PENSJA_PK primary key (IDPENSJI) );
- create table DRUZYNA (
- IDDRUZYNY INTEGER not null,
- MIEJSCE INTEGER null, constraint DRUZYNA_PK primary key (IDDRUZYNY) );
- create table TABELA (
- MIEJSCE INTEGER not null, constraint TABELA_PK primary key (MIEJSCE) );
- alter table HISTORIAPILKARZA
- add constraint PILKARZ_HISTORIAPILKARZA_FK1 foreign key (
- IDPILKARZA)
- references PILKARZ (
- IDPILKARZA);
- alter table HISTORIAPILKARZA
- add constraint DRUZYNA_HISTORIAPILKARZA_FK1 foreign key (
- IDDRUZYNY)
- references DRUZYNA (
- IDDRUZYNY);
- alter table HISTORIATRENERA
- add constraint DRUZYNA_HISTORIATRENERA_FK1 foreign key (
- IDDRUZYNY)
- references DRUZYNA (
- IDDRUZYNY);
- alter table HISTORIATRENERA
- add constraint TRENER_HISTORIATRENERA_FK1 foreign key (
- IDTRENERA)
- references TRENER (
- IDTRENERA);
- alter table HISORIAPENSTR
- add constraint PENSJA_HISORIAPENSTR_FK1 foreign key (
- IDPENSJI)
- references PENSJA (
- IDPENSJI);
- alter table HISORIAPENSTR
- add constraint TRENER_HISORIAPENSTR_FK1 foreign key (
- IDTRENERA)
- references TRENER (
- IDTRENERA);
- alter table HISTORIAPENSPILK
- add constraint PILKARZ_HISTORIAPENSPILK_FK1 foreign key (
- IDPILKARZA)
- references PILKARZ (
- IDPILKARZA);
- alter table HISTORIAPENSPILK
- add constraint PENSJA_HISTORIAPENSPILK_FK1 foreign key (
- IDPENSJI)
- references PENSJA (
- IDPENSJI);
- alter table TRENER
- add constraint PENSJA_TRENER_FK1 foreign key (
- IDPENSJI)
- references PENSJA (
- IDPENSJI);
- alter table TRENER
- add constraint DRUZYNA_TRENER_FK1 foreign key (
- IDDRUZYNY)
- references DRUZYNA (
- IDDRUZYNY);
- alter table PILKARZ
- add constraint PENSJA_PILKARZ_FK1 foreign key (
- IDPENSJI)
- references PENSJA (
- IDPENSJI);
- alter table PILKARZ
- add constraint DRUZYNA_PILKARZ_FK1 foreign key (
- IDDRUZYNY)
- references DRUZYNA (
- IDDRUZYNY);
- alter table DRUZYNA
- add constraint TABELA_DRUZYNA_FK1 foreign key (
- MIEJSCE)
- references TABELA (
- MIEJSCE);
- INSERT INTO TABELA VALUES (1);
- INSERT INTO TABELA VALUES (2);
- INSERT INTO TABELA VALUES (3);
- INSERT INTO TABELA VALUES (4);
- INSERT INTO DRUZYNA VALUES(101,1);
- INSERT INTO DRUZYNA VALUES(102,2);
- INSERT INTO DRUZYNA VALUES(103,3);
- INSERT INTO PENSJA VALUES(201,200000);
- INSERT INTO PENSJA VALUES(202,30000);
- INSERT INTO PENSJA VALUES(203,200000);
- INSERT INTO PENSJA VALUES(204,200000);
- INSERT INTO PENSJA VALUES(205,110000);
- INSERT INTO PENSJA VALUES(206,140000);
- INSERT INTO PENSJA VALUES(207,80000);
- INSERT INTO PENSJA VALUES(208,200000);
- INSERT INTO TRENER VALUES(301,'Jurgen','Klopp',201,101);
- INSERT INTO TRENER VALUES(302,'Peter','Krawietz',202,101);
- INSERT INTO TRENER VALUES(303,'Chris','Morgan',203,101);
- INSERT INTO PILKARZ VALUES(401,'Sadio','Mane',204,101);
- INSERT INTO PILKARZ VALUES(402,'Adam','Lallana',205,101);
- INSERT INTO PILKARZ VALUES(403,'Adam','Lallana',206,101);
- INSERT INTO PILKARZ VALUES(411,'Victor','Moses',207,102);
- INSERT INTO PILKARZ VALUES(412,'NGolo','Cante',208,102);
- INSERT INTO HISORIAPENSTR VALUES(201,301,CONVERT(DATETIME, '2015-01-22'),CONVERT(DATETIME, '2022-11-02'));
- INSERT INTO HISORIAPENSTR VALUES(202,302,CONVERT(DATETIME, '2016-02-22'),CONVERT(DATETIME, '2022-11-02'));
- INSERT INTO HISORIAPENSTR VALUES(203,303,CONVERT(DATETIME, '2014-07-22'),CONVERT(DATETIME, '2022-11-02'));
- INSERT INTO HISTORIAPENSPILK VALUES(401,204,CONVERT(DATETIME, '2013-05-2'),CONVERT(DATETIME, '2022-11-02'));
- INSERT INTO HISTORIAPENSPILK VALUES(402,205,CONVERT(DATETIME, '2014-05-4'),CONVERT(DATETIME, '2022-11-02'));
- INSERT INTO HISTORIAPENSPILK VALUES(403,206,CONVERT(DATETIME, '2016-02-12'),CONVERT(DATETIME, '2022-11-02'));
- INSERT INTO HISTORIAPENSPILK VALUES(411,207,CONVERT(DATETIME, '2015-02-11'),CONVERT(DATETIME, '2022-11-02'));
- INSERT INTO HISTORIAPENSPILK VALUES(412,208,CONVERT(DATETIME, '2016-01-10'),CONVERT(DATETIME, '2022-11-02'));
- INSERT INTO HISTORIAPILKARZA VALUES (401,101,CONVERT(DATETIME, '2005-05-2'),CONVERT(DATETIME, '2012-11-02'));
- INSERT INTO HISTORIAPILKARZA VALUES (401,101,CONVERT(DATETIME, '2013-05-2'),CONVERT(DATETIME, '2022-11-02'));
- INSERT INTO HISTORIAPILKARZA VALUES (402,101,CONVERT(DATETIME, '2014-05-4'),CONVERT(DATETIME, '2022-11-02'));
- INSERT INTO HISTORIAPILKARZA VALUES (403,101,CONVERT(DATETIME, '2016-02-12'),CONVERT(DATETIME, '2022-11-02'));
- INSERT INTO HISTORIAPILKARZA VALUES (411,102,CONVERT(DATETIME, '2015-02-11'),CONVERT(DATETIME, '2022-11-02'));
- INSERT INTO HISTORIAPILKARZA VALUES (412,102,CONVERT(DATETIME, '2016-01-10'),CONVERT(DATETIME, '2022-11-02'));
- INSERT INTO HISTORIATRENERA VALUES (101,301,CONVERT(DATETIME, '2015-01-22'),CONVERT(DATETIME, '2022-11-02'));
- INSERT INTO HISTORIATRENERA VALUES (101,302,CONVERT(DATETIME, '2016-02-22'),CONVERT(DATETIME, '2022-11-02'));
- INSERT INTO HISTORIATRENERA VALUES (101,303,CONVERT(DATETIME, '2014-07-22'),CONVERT(DATETIME, '2022-11-02'));
- --wypisz wszystkie dane z tabeli pilkarz//
- SELECT *
- FROM PILKARZ;
- --wypisz nazwiska pilkarzy ktore maja druga litere 'a' w nazwisku//
- SELECT NAZWISKO
- FROM PILKARZ
- WHERE NAZWISKO LIKE '_a%';
- --wypisz nazwiska trenera idTrenera miedzy 301 i 302//
- SELECT NAZWISKO
- FROM TRENER
- WHERE IDTRENERA BETWEEN 301 AND 302;
- --Obok imienia i nazwiska pilkarza, wypisz nazwe druzyny, w ktorej ten pilkarz gra//
- SELECT PILKARZ.idPilkarza,PILKARZ.IMIE, PILKARZ.NAZWISKO, DRUZYNA.IDDRUZYNY
- FROM PILKARZ
- INNER JOIN DRUZYNA ON PILKARZ.IDDRUZYNY=DRUZYNA.IDDRUZYNY;
- --Obok imienia i nazwiska pilkarza, wypisz nazwe druzyny, w ktorym ten pilkraz gra. Uwzglednij tez te druzyny, w ktorych nikt nie gra.//
- SELECT PILKARZ.IMIE,PILKARZ.NAZWISKO,PILKARZ.IDDRUZYNY "DRUZYNA PILKARZA",DRUZYNA.IDDRUZYNY "ID DRUZYNY"
- FROM PILKARZ
- RIGHT OUTER JOIN DRUZYNA ON PILKARZ.IDDRUZYNY = DRUZYNA.IDDRUZYNY;
- --Obok idTrenera , wypisz nazwe druzyny, w ktorej ten trener pracuje, idTrenera posortuj malejaca//
- SELECT TRENER.idTrenera,DRUZYNA.IDDRUZYNY
- FROM TRENER
- INNER JOIN DRUZYNA ON TRENER.IDDRUZYNY=DRUZYNA.IDDRUZYNY
- ORDER BY 1 DESC;
- --Wypisac w ilu druzynach gral pilkarz
- SELECT PILKARZ.IDPILKARZA, COUNT(h.DATAOD)
- FROM PILKARZ
- INNER JOIN HISTORIAPILKARZA h ON PILKARZ.IDPILKARZA = h.IDPILKARZA
- GROUP BY PILKARZ.IDPILKARZA;
- --Wypisac w ilu druzynach byl trener posortowwane w odwrotnej kolejnosci wedlug idtrenera
- SELECT TRAIN.IDTRENERA, COUNT(h.DATAOD)
- FROM TRENER TRAIN
- INNER JOIN HISTORIATRENERA h ON TRAIN.IDTRENERA = h.IDTRENERA
- GROUP BY TRAIN.IDTRENERA
- ORDER BY TRAIN.IDTRENERA DESC;
- --Wypisac o ilosci pilkarzy dla kazdej pensji oprocz pensji = 80000$
- SELECT GOLDBOOT.WIELKOSC, COUNT(P.IDPILKARZA)
- FROM PENSJA GOLDBOOT
- INNER JOIN PILKARZ P ON P.IDPENSJI = GOLDBOOT.IDPENSJI
- GROUP BY GOLDBOOT.WIELKOSC
- HAVING GOLDBOOT.WIELKOSC <> '80000$';
- --W parametrach procedury podac imie i nazwisko trenera
- --procedura wyswietli na ekran miejsce,ktore zajmuje druzyna
- --tego trenera
- CREATE PROCEDURE
- imNazwTrenM
- @v_im VARCHAR(100),@v_nazw VARCHAR(100)
- AS
- BEGIN
- SELECT d.miejsce
- FROM druzyna d
- INNER JOIN Trener t ON d.idDruzyny = t.idDruzyny
- WHERE t.imie = @v_im AND t.nazwisko=@v_nazw;
- END;
- EXECUTE imNazwTrenM 'Jurgen', 'Klopp'
- --w parametrach procedury podac idDruzyny
- --wypiszy sie imie i nazwisko kazdego pilkarza
- --jezeli pilkarz zarabia wiecej niz 115000$
- --do jego imienia, nazwiska bedzie dopisane "TOP PLAYER"
- CREATE PROCEDURE druzynaPilk
- @v_idDr INTEGER
- AS
- DECLARE kursor CURSOR FOR SELECT pi.imie,pi.nazwisko,pe.wielkosc
- FROM Pilkarz pi
- INNER JOIN Pensja pe ON pi.idPensji=pe.idPensji
- WHERE pi.idDruzyny = @v_idDr;
- DECLARE @kurs_imie VARCHAR(100),@kurs_nazwisko VARCHAR(100),
- @kurs_wielkosc VARCHAR(100);
- OPEN kursor;
- FETCH NEXT FROM kursor INTO @kurs_imie,@kurs_nazwisko,@kurs_wielkosc;
- WHILE @@FETCH_STATUS = 0
- BEGIN
- IF @kurs_wielkosc < 115000
- BEGIN
- PRINT @kurs_imie + ' ' + @kurs_nazwisko;
- END;
- ELSE
- BEGIN
- PRINT @kurs_imie + ' ' + @kurs_nazwisko+ ' Top player';
- END;
- FETCH NEXT FROM kursor INTO @kurs_imie,@kurs_nazwisko,@kurs_wielkosc;
- END;
- Close kursor;
- Deallocate kursor;
- EXECUTE druzynaPilk 102;
- --jezeli robimy w robimy update w pilkarzu
- -- i on ma na imie Victor Moses, to zmniejszamy pensje o 10%
- CREATE TRIGGER updVictorMoses ON
- PILKARZ FOR UPDATE
- AS
- BEGIN
- DECLARE
- @vimie VARCHAR,@vnaz VARCHAR,@pensja int;
- Select @vimie = Imie , @vnaz = Nazwisko from inserted;
- IF @vimie='Victor' AND @vnaz='Moses'
- BEGIN
- Update Pensja
- set WIELKOSC = WIELKOSC *0.90
- where IDPENSJI = (Select IDPENSJI from PILKARZ
- where Imie = @vimie and NAZWISKO = @vnaz);
- END;
- END;
- --jezeli do tabeli historiaPensjiPilkarza
- --wpisano bledna date, bedzie usuniety ten rekord
- CREATE TRIGGER hist ON
- HISTORIAPENSPILK FOR INSERT, UPDATE
- AS
- BEGIN
- DECLARE kurs CURSOR FOR SELECT dataOd
- FROM HISTORIAPENSPILK;
- DECLARE
- @data date;
- OPEN kurs
- FETCH NEXT FROM kurs INTO @data
- WHILE @@FETCH_STATUS = 0
- BEGIN
- IF @data > GETDATE()
- BEGIN
- DELETE HISTORIAPENSPILK WHERE dataOd=@data;
- PRINT 'Znaleziono bledno wpisana date';
- END;
- FETCH NEXT FROM kurs INTO @data
- END;
- END;
- INSERT INTO HISTORIAPENSPILK(IDPILKARZA,IDPENSJI,DATAOD,DATADO)
- VALUES(401,204,CONVERT(DATETIME, '2020-01-22'),CONVERT(DATETIME, '2022-11-02'));