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'));