select * from zawodnicy

select * from trenerzy

select imie_t, nazwisko_t, 
IIF(data_ur_t is null,'brak',format(data_ur_t,'dd-MM-yyyy'))
from trenerzy

select imie_t, nazwisko_t,
	coalesce(format(data_ur_t,'dd-MM-yyyy'),'brak')
from trenerzy

select coalesce(null,null,2)

select imie_t,nazwisko_t,data_ur_t,
	coalesce( 
			format(data_ur_t,'dd-MM-yyyy'),
			nazwisko_t,
			imie_t) 
from trenerzy

select CHOOSE(6,'a','b','c','d','e')

-- dla każdego zwodnika wypisz słownie 
--z ilu liter skłąda się jego imie, korzystając z choose
--uwzględnij przypadki od 1 do 5, dla pozostałych zwróć null

select imie, 
	CHOOSE(len(imie),'jeden','dwa','trzy','cztery','piec'),
	case len(imie)
		when 1 then 'jeden'
		when 2 then 'dwa'
		when 3 then 'trzy'
		when 4 then 'cztery'
		when 5 then 'piec'
	end
from zawodnicy


select * from zawodnicy

select * from trenerzy


select imie, nazwisko, imie_t, nazwisko_t
from zawodnicy full join trenerzy
	 on zawodnicy.id_trenera = trenerzy.id_trenera 

select z.imie, z.nazwisko
from zawodnicy z

select zawodnicy.imie, zawodnicy.nazwisko
from zawodnicy 

select z.imie, z.nazwisko, t.imie_t, t.nazwisko_t, z.id_trenera
from zawodnicy z full join trenerzy t
	 on z.id_trenera = t.id_trenera 

 
 select * from zawody
 select * from skocznie

 -- wypisz wszystkie skocznie wraz z zawodami, 
--  odbywajacymi się na tych skoczniach 

select s.nazwa_skoczni, z.nazwa
from skocznie s left join zawody z on s.id_skoczni= z.id_skoczni
where s.id_skoczni is not null

-- wypisz wszystkie pary: zawodnik-skocznia z tego samego kraju

select z.imie, z.nazwisko, s.nazwa_skoczni, s.kraj_skoczni
from zawodnicy z join skocznie s on z.kraj = s.kraj_skoczni

-- wypisz wszystkie pary: trener-zawodnik urodzonych w tym samym miesiącu:
    -- wersja a: trener i dowolny zawodnik
	-- wersja b: trener i zawodnik danego trenera

select z.imie, z.nazwisko, z.data_ur, t.imie_t, t.nazwisko_t, t.data_ur_t
from zawodnicy z join trenerzy t on MONTH(z.data_ur) = MONTH(t.data_ur_t)

select z.imie, z.nazwisko, z.data_ur, t.imie_t, t.nazwisko_t, t.data_ur_t
from zawodnicy z join trenerzy t 
		on MONTH(z.data_ur) = MONTH(t.data_ur_t) and
		z.id_trenera = t.id_trenera

-- cwiczenia: str 26 

select *
from zawodnicy where id_trenera is null

select t.* 
from trenerzy t left join zawodnicy z on z.id_trenera= t.id_trenera
where z.id_trenera is null

select z.imie, z.nazwisko, t.imie_t, t.nazwisko_t
from zawodnicy z join trenerzy t on LEFT(z.imie,1) = LEFT(t.imie_t,1)

select z1.imie, z1.nazwisko , z1.wzrost, z2.imie, z2.nazwisko, z2.wzrost, z1.kraj
from zawodnicy z1 join zawodnicy z2 on z1.kraj= z2.kraj 
	where z1.wzrost > z2.wzrost
order by z1.wzrost


-- wypisz zawodnikow i zawody 

select * from zawodnicy
select * from zawody
select * from uczestnictwa

select z.imie, z.nazwisko, zaw.nazwa
from zawodnicy z left join uczestnictwa u on z.id_zawodnika= u.id_zawodnika
                 left join zawody zaw on zaw.id_zawodow = u.id_zawodow

-- dla kazdego trenera wypisz miasta jakie musiał odwiedzić w czasie swojej kariery


select distinct t.imie_t, t.nazwisko_t,  replace(m.nazwa_miasta,'"','')
from trenerzy t left join zawodnicy z on t.id_trenera = z.id_trenera
				left join uczestnictwa u on u.id_zawodnika = z.id_zawodnika
				left join zawody zaw on zaw.id_zawodow = u.id_zawodow
				left join skocznie s on s.id_skoczni = zaw.id_skoczni
				left join miasta m on m.id_miasta = s.id_miasta
where t.id_trenera is not null


-- wypisz trenerów i zawodników urodzonych tak, 
--że jeden trener urodzony jest dzień tygodnia wcześniej
-- od zawodnika: np: trener w poniedziałek a zawodnik we wtorek 

select FORMAT(data_ur,'d') , DATEPART(dw,data_ur) from zawodnicy

select t.imie_t, t.nazwisko_t, z.imie, z.nazwisko,DATEPART(dw,z.data_ur),DATEPART(dw,t.data_ur_t)
from trenerzy t join zawodnicy z on 
	DATEPART(dw,t.data_ur_t)= iif(DATEPART(dw,z.data_ur)-1=0,7,DATEPART(dw,z.data_ur)-1)  

select distinct kraj from zawodnicy


select kraj
from zawodnicy --join
--where
group by 
--order by 

go;

select kraj, waga, sum(wzrost), AVG(waga),MIN(data_ur), SUM(waga), COUNT(waga)
from zawodnicy 
group by kraj, waga

-- kolumna, która nie jest grupowana MUSI być zagregowana
select * from zawodnicy
select kraj, COUNT(nazwisko),COUNT(imie),COUNT(id_trenera)
from zawodnicy
group by kraj

select kraj, COUNT(*)
from zawodnicy
group by kraj

select* from zawodnicy


-- str 20 grupowanie 

select kraj , COUNT(*)
from zawodnicy
group by kraj

select kraj, MAX(wzrost)
from zawodnicy
group by kraj

select kraj, COUNT(*)
from zawodnicy
where wzrost>180
group by kraj

select kraj, AVG(wzrost)
from zawodnicy
group by kraj
order by AVG(wzrost)


select kraj , count(IIF(wzrost>180,1,null)) wys , SUM(IIF(wzrost<175,1,0)) nis 
from zawodnicy
group by kraj

-- dodatkowe:
-- dla każdego zawodnika wypisz w ilu zawodach startował

select z.imie, z.nazwisko, COUNT(u.id_uczestnictwa)
from zawodnicy z left join uczestnictwa u on u.id_zawodnika = z.id_zawodnika
group by z.id_zawodnika, imie, nazwisko
order by COUNT(u.id_uczestnictwa) desc

-- pogrupuj zawodników po pierwszej literce imienia i wyświetl ilu ich jest

select LEFT(imie,1), COUNT(*)
from zawodnicy 
group by LEFT(imie,1)

-- podaj ilu zawodników urodziło się w poszczególnych dniach --tygodnia( pon, wt... niedz) 
-- posortuj malejąco
select FORMAT(data_ur,'dddd'),  DATEPART(dw,data_ur), COUNT(*)
from zawodnicy 
group by FORMAT(data_ur,'dddd'), DATEPART(dw,data_ur)
order by  DATEPART(dw,data_ur) desc


select kraj, AVG(wzrost)
from zawodnicy	
--where wzrost > 175
group by kraj
having AVG(wzrost)>175


-- str 20 
-- wypisz trenerów, którzy trenują od 2 do 4 zawodników 

select kraj,COUNT(*)
from zawodnicy	
where wzrost > 180
group by kraj
having COUNT(*) > 1

select t.nazwisko_t, COUNT(*)
from trenerzy t left join zawodnicy z 
	on t.id_trenera = z. id_trenera
group by t.nazwisko_t
having COUNT(*) between 2 and 4 


-- podzapytania 

select imie, nazwisko, waga
from zawodnicy


			select imie, nazwisko, w
			from    (select imie, nazwisko, waga , waga+1 w
					from zawodnicy) t
-- 1 podzapytania w sekcji from
select imie, nazwisko, FORMAT(bmi,'0.00')
from
	(select imie, nazwisko, waga/POWER(wzrost/100.0,2) bmi
	from zawodnicy) t
where bmi > 20

 
select imie, nazwisko from zawodnicy
-- podzapytania w sekcji select 
select imie, nazwisko, (select max(wzrost) from zawodnicy) t
from zawodnicy

 select imie, nazwisko, 
	(select nazwisko_t from trenerzy tt where tt.id_trenera=z.id_trenera) t
from zawodnicy z

-- podzapytania w sekcji where

select imie, nazwisko, wzrost
from zawodnicy
where wzrost > (select AVG(wzrost) wzrost from zawodnicy)

select AVG(wzrost) wzrost from zawodnicy

-- wypisz tylko tych zawodnikow, ktorych wzrost jest wiekszy niz srednia
-- wzrostu z tego samego kraju co zawodnik
select imie, nazwisko, wzrost
from zawodnicy z2
where wzrost > (select AVG(wzrost) wzrost from zawodnicy z1 where z1.kraj=z2.kraj)

-- podzapytania + filtrowanie + zlaczenia + grupowanie + having

select *
from zawodnicy
where wzrost = (select MAX(wzrost) from zawodnicy)

select *
from zawodnicy
where waga >  (select avg(waga) from zawodnicy)

select *
from zawodnicy
where waga >=  any(select waga from zawodnicy)

-- wypisz tylko tych zawodnikow, których wzrost jest wiekszy od wszystkich wzrostów polakow
select *
from zawodnicy	
where wzrost >  all(select wzrost from zawodnicy where kraj = 'pol')
-- wypisz tych zawodnikow , ktorych wzrost jest taki sam jak wzrost polakow 
select *
from zawodnicy	
where wzrost in (select wzrost from zawodnicy where kraj = 'ger')


-- 1) sprawdź w ilu zawodach uczestniczył każdy zawodnik
	select imie, nazwisko, (select COUNT(*) from uczestnictwa u where z.id_zawodnika=u.id_zawodnika)
	from zawodnicy  z

-- 2) stwórz tabliczkę mnożenia od 1 do 100, każdy wiersz to jedno równanie 1x1=1; 1x2=2...... 9x9=81 ...10x10=100
	select format(a.id_zawodnika,'0') + 'x' +  format(b.id_zawodnika,'0') + ' = ' +  format(a.id_zawodnika*b.id_zawodnika,'0')
	 from 
		(select id_zawodnika
		from zawodnicy where id_zawodnika < 11) a
			full join 
		(select id_zawodnika
				from zawodnicy where id_zawodnika < 11) b on a.id_zawodnika <> b.id_zawodnika or a.id_zawodnika =b.id_zawodnika

	select z.imie, z.nazwisko, t.imie_t, t.nazwisko_t
	from zawodnicy z cross join trenerzy t  

	select z.imie, z.nazwisko, t.imie_t, t.nazwisko_t, zaw.nazwa
	from zawodnicy z , trenerzy t  , zawody zaw

-- 3) wypisz wszystkie dni tygodnia (od poniedziałku do niedzieli po polsku i dla każdego dnia tygodnia napisz 
-- ilu trenerów i ilu zawodników (w osobnych kolumnach urodziło się danego dnia)  
	select p.dz,p.trenerzy,count(o.dz) zawodnicy
	 from
		(select a.dz, COUNT(b.dz) trenerzy
		 from 
			 (select FORMAT( DATEFROMPARTS(2000,1, id_zawodnika+2),'dddd','pl-pl') dz
			 from zawodnicy where id_zawodnika < 8) a
			 left join
			 (select FORMAT(data_ur_t,'dddd','pl-pl') dz
			 from trenerzy) b
			 on a.dz = b.dz
		  group by a.dz) p
		left join
		(select FORMAT(data_ur,'dddd','pl-pl') dz
		from zawodnicy) o
		on p.dz=o.dz
	group by p.dz,p.trenerzy


	select FORMAT(DATEFROMPARTS(2000,1,p.dz+2),'dddd','pl-pl'),p.trenerzy,count(o.dz) zawodnicy
	 from
		(select a.dz, COUNT(b.dz) trenerzy
		 from 
			 (select datepart(DW, DATEFROMPARTS(2000,1, id_zawodnika+2)) dz
			 from zawodnicy where id_zawodnika < 8) a
			 left join
			 (select datepart(DW, data_ur_t) dz
			 from trenerzy) b
			 on a.dz = b.dz
		  group by a.dz) p
		left join
		(select datepart(DW,data_ur) dz
		from zawodnicy) o
		on p.dz=o.dz
	group by p.dz,p.trenerzy














-- 4) wypisz ilu zawodników skakało na danej skoczni -- wypisz tylko te skocznie gdzie skakało więcej niż 
   -- 2 zawodników, posortuj wg. liczby zawodników
	
	select s.nazwa_skoczni, count(u.id_zawodnika)
	from skocznie s left join zawody zaw on s.id_skoczni=zaw.id_skoczni
					left join uczestnictwa u on u.id_zawodow = zaw.id_zawodow
    where s.nazwa_skoczni is not null
	group by s.nazwa_skoczni
	having COUNT(u.id_zawodnika) > 2
	order by COUNT(u.id_zawodnika) 

-- 5) dla każdego miasta wypisz kiedy odbyły się pierwsze zawody , jeżeli nie odbyły się nigdy napisz: brak danych
	
	
	select m.nazwa_miasta, coalesce( format(min(z.data),'dd-MM-yyyy'),'bd') data 
		 
	from miasta m left join skocznie s on m.id_miasta= s.id_miasta
				  left join zawody z on z.id_skoczni = s.id_skoczni
	group by m.nazwa_miasta
	order by  coalesce(min(z.data),(select dateadd(d,1,MAX(data)) from zawody) )

-- 6) dla każdego trenera wypisz kraj, na podstawie kraju jego zawodników 

	select distinct t.nazwisko_t, z.kraj
	from trenerzy t left join zawodnicy z on z.id_trenera = t.id_trenera	
	where z.kraj is not null


-- 7) wypisz trenerów młodszych niż jego zawodnicy

	select t.*
	from trenerzy t where data_ur_t> (select MIN(data_ur) from zawodnicy z where z.id_trenera=t.id_trenera) 

	
-- 8) wskaz wszystkich zawodnikow, których wzrost jest drugi co do wielkości
	
	select imie, nazwisko, wzrost from zawodnicy 
	 where wzrost = 
		(select MAX(wzrost) from zawodnicy
			where wzrost < 
			( select MAX(wzrost)
			 from zawodnicy ))

 
select imie, nazwisko
from zawodnicy
where waga > 61
 union all
select imie, nazwisko
from zawodnicy
where wzrost> 178

select imie, nazwisko
from zawodnicy where waga > 61 and wzrost <= 178



select imie, nazwisko ,kraj
from zawodnicy
union 
select imie_t, nazwisko_t, (select distinct kraj from zawodnicy where id_trenera = t.id_trenera and kraj is not null)
from trenerzy t 


select imie, nazwisko ,kraj
from zawodnicy
union 
select imie_t, nazwisko_t, null
from trenerzy t 

select * from zawodnicy where wzrost  =
(select MAX(wzrost) from 
	(select wzrost from zawodnicy
	except
	select max(wzrost)
	from zawodnicy) t )


	/*
Z zawodników wypisz 3 kolumny: imie, nazwisko i typ wzrostu, 
którzy przyjmuje tylko 3 wartości:  niski lub średni lub wysoki.
wartości czy ktoś jest niski śrendni czy wysoki powinny być dobrane automatycznie
przez podzielenie na równo przedziału wartości wzrostów 
*/

select  (max(wzrost) - MIN(wzrost))/3.0
from zawodnicy


select imie, nazwisko, wzrost, 'niski'
from zawodnicy	
	where wzrost <  
			(select MIN(wzrost) from zawodnicy) + (select  (max(wzrost) - MIN(wzrost))/3.0 from zawodnicy)

union
select imie, nazwisko, wzrost, 'średni'
from zawodnicy	
	where wzrost <=  
			(select MIN(wzrost) from zawodnicy) + 2* (select  (max(wzrost) - MIN(wzrost))/3.0 from zawodnicy)
		and wzrost >= (select MIN(wzrost) from zawodnicy) + (select  (max(wzrost) - MIN(wzrost))/3.0 from zawodnicy)
union
select imie, nazwisko, wzrost, 'wysoki'
from zawodnicy	
	where wzrost >  
			(select MIN(wzrost) from zawodnicy) + 2*(select  (max(wzrost) - MIN(wzrost))/3.0 from zawodnicy)