--
-- Filip Murlak, http://www.mimuw.edu.pl/~fmurlak/bd2011/
-- 
-- Rozwiazania zadan z pierwszego kolokwium
-- 


-- 1. Wypisz wszystkich ludzi wraz z imieniem ich ojca, posortuj po nazwisku ojca.
--    Ludzi, ktorych ojciec jest nieznany, wypisz na koncu.

SELECT dziecko.id, dziecko.imie, dziecko.nazwisko, ojciec.imie ojciec
FROM czlowiek dziecko LEFT JOIN czlowiek ojciec ON dziecko.id_ojca=ojciec.id 
ORDER BY ojciec.nazwisko;

-- 2. Wypisz wszystkich ludzi wyzszych od obojga swoich rodzicow,
--    wraz z ich wzrostem i maksimum z wzrostu rodzicow. 
--    Posortuj po roznicy miedzy ich wzrostem a maksimum wzrostem rodzicow.

SELECT dziecko.id, dziecko.imie, dziecko.nazwisko, dziecko.wzrost, GREATEST(matka.wzrost, ojciec.wzrost) maks
FROM czlowiek dziecko, czlowiek ojciec, czlowiek matka
WHERE dziecko.id_ojca=ojciec.id AND dziecko.id_matki=matka.id AND 
      dziecko.wzrost>ojciec.wzrost AND dziecko.wzrost>matka.wzrost
ORDER BY (maks - dziecko.wzrost);

-- 3. Dla kazdej matki wypisz sredni wzrost jej dzieci (kobiety bezdzietne pomin). 

SELECT matka.id, matka.imie, matka.nazwisko, AVG(dziecko.wzrost) 
FROM czlowiek dziecko, czlowiek matka
WHERE dziecko.id_matki=matka.id 
GROUP BY matka.id, matka.imie, matka.nazwisko;

-- 4. Dla kazdej osoby wypisz date urodzin jej matki (lub nic gdy matka nieznana). 

SELECT dziecko.id, dziecko.imie, dziecko.nazwisko, matka.ur ur_matki
FROM czlowiek dziecko LEFT JOIN czlowiek matka ON dziecko.id_matki=matka.id;

-- 5. Wypisz osoby zmarle bezpotomnie, posortuj po dacie smierci.

SELECT * 
FROM czlowiek
WHERE id NOT IN (SELECT id_ojca FROM czlowiek WHERE id_ojca IS NOT NULL) 
      AND id NOT IN (SELECT id_matki FROM czlowiek WHERE id_matki IS NOT NULL)
ORDER BY zm;

-- 6. Wypisz najwyzszego i najnizszego, urodzonego najdawniej i najbardziej niedawno.
--    W przypadku remisow, wypisz wszystkich remisujacych. 

SELECT * 
FROM czlowiek 
WHERE wzrost = (SELECT MAX(wzrost) FROM czlowiek) OR wzrost = (SELECT MIN(wzrost) FROM czlowiek) OR
      ur = (SELECT MAX(ur) FROM czlowiek) OR ur=(SELECT MIN(ur) FROM czlowiek);


-- 7. Dla kazdej osoby wypisz, ktorym jest dzieckiem swojej matki. 
--    Wieloraczki sortuj alfabetycznie wg. imienia. 
--    Jesli matka jest nieznana, zostaw puste miejsce.

SELECT mlodszy.id, mlodszy.imie, mlodszy.nazwisko, CASE COUNT(starszy.id) WHEN 0 THEN NULL ELSE COUNT(starszy.id) END
FROM czlowiek mlodszy LEFT OUTER JOIN czlowiek starszy ON	
     mlodszy.id_matki = starszy.id_matki AND (mlodszy.ur > starszy.ur  OR (mlodszy.ur = starszy.ur AND mlodszy.imie >= starszy.imie))
GROUP BY mlodszy.id, mlodszy.imie, mlodszy.nazwisko;

-- 8. Dla kazdej osoby wypisz jej najmlodszego starszego brata/siostre 
--    (id, imie; wieloraczki sortujemy alfabetycznie po imieniu) 
--    lub nic, jesli osoba nie ma starszego rodzenstwa. 
--    Brat/siostra to osoba o tej samej matce lub ojcu.

SELECT mlodszy.id, mlodszy.imie, mlodszy.nazwisko, starszy.id, starszy.imie, starszy.nazwisko
FROM czlowiek mlodszy LEFT JOIN czlowiek starszy ON
     (mlodszy.id_ojca = starszy.id_ojca OR mlodszy.id_matki = starszy.id_matki) 
     AND (starszy.ur < mlodszy.ur OR (starszy.ur = mlodszy.ur AND starszy.imie < mlodszy.imie)) 
     AND NOT EXISTS 
      	  (SELECT id 
	  FROM czlowiek
	  WHERE (ur > starszy.ur OR (ur = starszy.ur AND imie > starszy.imie)) 
	  	AND (ur < mlodszy.ur OR (ur = mlodszy.ur AND imie < mlodszy.imie)));
