Untitled

/*
Z5 Filip, Budzyński 319021, 3

Z5.1 - Pokazać miasta wraz ze średnią aktualna
pensją w nich z firm tam się mieszczących
Używając UNION, rozważyć opcję ALL
jak nie ma etatów to 0 pokazujemy
(czyli musimy obsłużyć miasta bez etatów AKT firm)

id_miasta, nazwa (z miasta), avg(pensja) lub 0
jak brak etatow firmowych w danym miescie

Z5.2 - to samo co w Z5.1
Ale z wykorzystaniem LEFT OUTER

Z5.3 Napisać procedurę pokazującą średnią pensję w
osób z miasta - parametr procedure @id_miasta
WYNIK:
id_osoby, imie, nazwisko, avg(pensja)
czyli srednie pensje osob z wszystkich etatow
osob mieszkajacych w danym miescie
*/

use b_319021

SELECT m.nazwa, X.srednia_zarobkow
	FROM MIASTA m
	join (SELECT fW.id_miasta, AVG(ew.pensja) AS srednia_zarobkow 
			FROM FIRMY fW
			join ETATY eW on (eW.id_firmy = fW.nazwa_skr)

			WHERE EW.DO IS NULL
			GROUP BY fW.id_miasta
		) X ON (X.id_miasta = m.id_miasta)
union
SELECT m.nazwa, CONVERT(money, 0) as srednia_zarobkow
	FROM MIASTA m
	where not exists (select 1 from ETATY eW
		join FIRMY fW on (fW.nazwa_skr = eW.id_firmy) 
		where (fW.id_miasta = m.id_miasta))
	order by 1

/*
nazwa                                              srednia_zarobkow
-------------------------------------------------- ---------------------
Lubartów                                           0,00
Lublin                                             3000,00
Ostołęka                                           5615,3846
Płock                                              0,00
Świdnik                                            9666,6666
Warszawa                                           7500,00
*/

--DOWÓD--
/* Co ma oczywiście sens ponieważ ponieżej przedstawiam które miasta nie mają żadnych firm:
select m.nazwa, count(f.nazwa_skr)
	from MIASTA m
		left join FIRMY f on (f.id_miasta = m.id_miasta)
	group by m.nazwa

nazwa                                              
-------------------------------------------------- -----------
Lubartów                                           0
Lublin                                             1
Ostołęka                                           3
Płock                                              0
Świdnik                                            1
Warszawa                                           1
*/



/*Z5.2 - to samo co w Z5.1
Ale z wykorzystaniem LEFT OUTER*/

SELECT m.nazwa, isnull(X.srednia_zarobkow, 0) as srednia_zarobkow
	FROM MIASTA m
	left join (SELECT fW.id_miasta, AVG(ew.pensja) AS srednia_zarobkow 
			FROM FIRMY fW
			join ETATY eW on (eW.id_firmy = fW.nazwa_skr)

			WHERE EW.DO IS NULL
			GROUP BY fW.id_miasta
		) X ON (X.id_miasta = m.id_miasta)

/* Tak samo jak w Z5.1 tylko że dzieki left join nie musimy doklejać tabeli ktore wczesniej w srednia zarobkow uzyskały 0.
nazwa                                              srednia_zarobkow
-------------------------------------------------- ---------------------
Warszawa                                           7500,00
Ostołęka                                           5615,3846
Płock                                              0,00
Lublin                                             3000,00
Lubartów                                           0,00
Świdnik                                            9666,6666
*/



/*Z5.3 Napisać procedurę pokazującą średnią pensję w
osób z miasta - parametr procedure @id_miasta
WYNIK:
id_osoby, imie, nazwisko, avg(pensja)
czyli srednie pensje osob z wszystkich etatow
osob mieszkajacych w danym miescie
*/
GO

ALTER PROCEDURE p1 (@id_miasta INT)
as 
	select o.id_osoby, LEFT(o.imie, 10) as imie, LEFT(o.nazwisko, 12) as nazwisko, isnull(X.pensja, 0) as srednia_pensja
		from OSOBY o
		join MIASTA m on (o.id_miasta = m.id_miasta) 
		join (select ow.id_osoby, AVG(ew.pensja) as pensja
			from OSOBY ow 
			left join Etaty ew on (ew.id_osoby = ow.id_osoby)
			group by ow.id_osoby
			) X on (X.id_osoby = o.id_osoby)
		where (m.id_miasta = @id_miasta)

GO

EXEC p1 3
/*
id_osoby    imie       nazwisko     srednia_pensja
----------- ---------- ------------ ---------------------
15          Tomasz     Twardnik     0,00
16          Kamila     Aniołek      0,00
17          Monika     Kocioł       0,00
18          Danuta     Szwęda       0,00
*/

EXEC p1 1
/*
id_osoby    imie       nazwisko     srednia_pensja
----------- ---------- ------------ ---------------------
9           Kornelia   Maleńczyk    8000,00
10          Monika     Maleńczyk    19000,00
11          Maria      Huntsman     3500,00
12          Dominika   Zbigniew     3500,00
13          Dominik    Fajecki      3500,00
14          Tomasz     Twardy       3500,00
*/
EXEC p1 2
/*
id_osoby    imie       nazwisko     srednia_pensja
----------- ---------- ------------ ---------------------
*/

--Dowód, w który pokazuje ile jest osbów w każdym z miast

select m.id_miasta, LEFT(m.nazwa, 12) as nazwa, isnull(X.id_osoby, 0) as ile_osób, isnull(K.id_etatu, 0) as ile_etatów
	from MIASTA m
	left join (select ow.id_miasta, count(ow.id_osoby) as id_osoby
		from MIASTA mw
		left join OSOBY ow on (ow.id_miasta = mw.id_miasta)
		group by ow.id_miasta
		) X on (X.id_miasta = m.id_miasta)
	left join (select ow2.id_miasta, count(ew.id_etatu) as id_etatu
		from MIASTA mw2
		left join FIRMY fw on (fw.id_miasta = mw2.id_miasta)
		left join ETATY ew on (ew.id_firmy = fw.nazwa_skr)
		left join OSOBY ow2 on (ew.id_osoby = ow2.id_osoby)
		group by ow2.id_miasta
	) K on (K.id_miasta = m.id_miasta)
	
/*jak widać w mieście 1 jest osób 6 i te osoby maja razem 6 etatów,
w 2 nie ma żadnej osoby wiec tez nie ma kto mieć etatów
a w 3 osoby są cztery z tym że żadna z nich nie ma etatu, dlatego w procedurze powyżej
wszystkie osoby z maista o id 3 mają zerową średnią pensję

id_miasta   nazwa        ile_osób    ile_etatów
----------- ------------ ----------- -----------
1           Warszawa     6           6
2           Ostołęka     0           0
3           Płock        4           0
4           Lublin       3           3
5           Lubartów     5           13
6           Świdnik      0           0
*/