Lekcja 4
Funkcje agregujące:
SUM() - suma elementów (liczbowych)
AVG() - średnia arytmetyczna
COUNT() - liczba wystąpień (liczebność)
MIN() - zwraca najmniejszą wartość
MAX() - zwraca największą wartość

***************************************
zrobienie kopii zapasowej własnej bazy

mysqldump -u user -p --database nazwa_bazy > nazwa_pliku.sql


Zadanie 1
pkt. 1
SELECT AVG(waga) from kreatura where rodzaj='wiking';
pkt. 2
SELECT rodzaj,AVG(waga), count(idKreatury) FROM kreatura GROUP BY rodzaj;
Pkt.3 
SELECT rodzaj,AVG(2018-YEAR(dataUr)) FROM
kreatura GROUP BY rodzaj;

NOW() - funkcja zwraca aktualną datę i czas

SELECT rodzaj,AVG(year(now())-YEAR(dataUr)) FROM
kreatura GROUP BY rodzaj;

Zadanie 2
pkt.1
SELECT rodzaj,AVG(waga) FROM zasob group by rodzaj;
pkt.2
# uwzględniamy wagę >= 4 dla pojedynczego rekordu (np. kokosa)
1. SELECT nazwa, AVG(waga) FROM zasob WHERE ilosc >= 4 GROUP BY nazwa 
HAVING SUM(waga) > 10;
# uwzględniamy sumę wag wszystkich zasobów o danej nazwie (np. kokosów)
2. SELECT nazwa, AVG(waga) FROM zasob GROUP BY nazwa 
HAVING SUM(waga) > 10 AND SUM(ilosc) >= 4;
pkt. 3
COUNT(kolumna lub * dla całego wiersza) - zlicza ilość rekordów dla danego warunku

SELECT count(nazwa) from zasob group by rodzaj HAVING count(nazwa)>1;



zadanie 3
pkt.1
SELECT kreatura.nazwa, ekwipunek.idZasobu, ekwipunek.ilosc FROM kreatura, ekwipunek
WHERE kreatura.idKreatury = ekwipunek.idKreatury;

# postać z aliasami nazw tabel
SELECT k.nazwa, e.idZasobu, e.ilosc FROM kreatura k, ekwipunek e
WHERE k.idKreatury = e.idKreatury;

pkt.2
SELECT k.nazwa, e.ilosc, z.nazwa FROM kreatura k, ekwipunek e, zasob z
WHERE k.idKreatury = e.idKreatury AND 
e.idZasobu = z.idZasobu;
# przykład z JOIN (taki sam wynik jak powyżej)
SELECT k.nazwa, e.ilosc, z.nazwa FROM kreatura k
JOIN ekwipunek e
ON k.idKreatury = e.idKreatury 
JOIN zasob z
ON e.idZasobu = z.idZasobu;

pkt 3
SELECT idKreatury FROM kreatura WHERE idKreatury NOT IN (SELECT idKreatury from ekwipunek where idKreatury IS NOT NULL);
# teraz z LEFT JOIN
SELECT k.idKreatury, k.nazwa, e.idZasobu FROM kreatura k
LEFT JOIN ekwipunek e ON k.idKreatury = e.idKreatury WHERE e.idZasobu IS NULL;

Zadanie 4
# przykład z NATURAL JOIN
SELECT k.nazwa, e.idZasobu, z.nazwa FROM kreatura k NATURAL JOIN ekwipunek e
JOIN zasob z ON e.idZasobu=z.idZasobu;

pkt.3
Unikalna lista
SELECT CONCAT( t1.nazwa,  " - ", t2.nazwa ) FROM kreatura t1, kreatura t2 WHERE t1.idKreatury - t2.idKreatury=5;

lista z powtórzeniami
SELECT CONCAT( t1.nazwa,  " - ", t2.nazwa ) FROM kreatura t1, kreatura t2 WHERE ABS(t1.idKreatury - t2.idKreatury)=5;

Zadanie 5

pkt.1.

select k.rodzaj, avg(e.ilosc*z.waga), sum(e.ilosc) from kreatura k, ekwipunek e, zasob z WHERE k.idKreatury=e.idKreatury AND e.idZasobu=z.idZasobu AND k.rodzaj NOT IN ('malpa','waz') group by rodzaj having sum(e.ilosc) < 30;

pkt.2.

**** z UNION ****
select  'najmlodsza',a.maxData, b.nazwa, a.rodzaj from (select max(dataUr) maxData, rodzaj from kreatura group by rodzaj) a, (select nazwa, dataUr from kreatura) b where a.maxData = b.dataUr
union
select 'najstarsza',a.minData, b.nazwa, a.rodzaj from (select min(dataUr) minData, rodzaj from kreatura group by rodzaj) a, (select nazwa, dataUr from kreatura) b where a.minData = b.dataUr

**** krótka wersja ****

select a.nazwa, a.rodzaj, a.dataUr from kreatura a, (SELECT min(dataUr) min, max(dataUr) max from kreatura group by rodzaj) b WHERE b.min = a.dataUr OR b.max=a.dataUr;