/*
---Podaj dane 5 uczniów w całej szkole (id,nazwisko,imię,klasa), którzy są zagrożeni usunięciem ze szkoły. Pod uwagę należy wziąć 3 kryteria: niskie wyniki w nauce (końcowa średnia ocen liczona jako średnia ocen z poszczególnych przedmiotów - liczb całkowitych), niska frekwencja, zachowanie (liczba punktów karnych). Zaproponuj rozwiązanie
*/
with sred1 as (
select
u.IdUcznia
,o.IdPrzedmiotu
,CONVERT(DECIMAL(7,0), round (AVG(o.Ocena),0) ) AS sred
from Uczniowie u join Oceny o on u.IdUcznia=o.IdUcznia
group by u.IdUcznia,o.IdPrzedmiotu
), sredk as(
select
s.IdUcznia
,CONVERT(DECIMAL(7,2), round (AVG(s.sred),2) ) AS sred
from sred1 as s
group by s.IdUcznia
), laureaci as (
select
u.IdUcznia
,u.Imie
,u.Nazwisko
,k.Nazwa
,s.sred
,COUNT(n.DataNieobecnosci) as nieob
from Uczniowie as u join Klasy as k on u.IdKlasy=k.IdKlasy join sredk as s on u.IdUcznia=s.IdUcznia left join Nieobecnosci as n on u.IdUcznia=n.IdUcznia
group by u.IdUcznia,u.Imie,u.Nazwisko,k.Nazwa,s.sred
), uwa as(
select
uw.iducznia
,SUM(ru.IloscPunktow) pukty
from Uwagi as uw join RodzajeUwag as ru on uw.IdRodzajuUwagi=ru.IdRodzajuUwagi
group by uw.IdUcznia

)
select top 5
l.IdUcznia
,l.Imie
,l.Nazwisko
,l.Nazwa
,l.sred
,l.nieob
,uw.pukty
,(uw.pukty*l.nieob)/l.sred najgorsi
from laureaci as l join uwa as uw on uw.IdUcznia=l.IdUcznia
order by (uw.pukty*l.nieob)/l.sred desc