--
-- Filip Murlak, http://www.mimuw.edu.pl/~fmurlak/bdlab/
-- 
-- Rozwiązania zadań z pierwszej klasówki, 2012/13
-- 

-- 1. Dla każdego naukowca (id, nazwisko) wypisz średnią liczbę publikacji jego doktorantów; posortuj po id.

SELECT n.id, n.nazwisko, AVG(d.publikacje) publikacje_doktorantow
FROM naukowiec n LEFT JOIN naukowiec d ON d.promotor=n.id
GROUP BY n.id, n.nazwisko
ORDER BY n.id;

-- 2. Wypisz naukowców (id, nazwisko), których wszyscy doktoranci mają choć jedną publikację; posortuj po id.

SELECT n.id, n.nazwisko 
FROM naukowiec n
WHERE NOT EXISTS (SELECT d.id FROM naukowiec d WHERE d.promotor=n.id AND d.publikacje=0)
ORDER BY n.id;

-- 3. Posortuj według malejącej łacznej liczby publikacji ich doktorantów (wypisz id, nazwisko, łaczną liczbę publikacji).

SELECT n.id, n.nazwisko, SUM(NVL(d.publikacje,0)) publikacje_doktorantow
FROM naukowiec n LEFT JOIN naukowiec d ON d.promotor=n.id
GROUP BY n.id, n.nazwisko
ORDER BY publikacje_doktorantow DESC; 

-- 4. Wypisz naukowców (id, nazwisko), którzy mają więcej doktorantów niż publikacji. 

SELECT n.id, n.nazwisko 
FROM naukowiec n LEFT JOIN naukowiec d ON  d.promotor=n.id 
GROUP BY n.id, n.nazwisko, n.publikacje
HAVING COUNT(d.id)>n.publikacje;

-- 5. Wypisz naukowców (id, nazwisko), którzy maja co najmniej 4 doktorantów, z których każdy ma co najmniej 4 publikacje. 

SELECT p.id, p.nazwisko
FROM naukowiec p,
     (SELECT id, promotor 
      FROM naukowiec
      WHERE publikacje >= 4) dokt
WHERE dokt.promotor = p.id
GROUP BY p.id, p.nazwisko
HAVING COUNT(dokt.id) >= 4;


-- 6. Wypisz naukowców (id, nazwisko), którzy mają co najmniej 4 doktorantów i których choć jeden doktorant ma co najmniej 4 publikacje. 

SELECT n.id, n.nazwisko
FROM naukowiec n, naukowiec d
WHERE d.promotor=n.id
GROUP BY n.id, n.nazwisko
HAVING COUNT(d.id) >= 4 AND  MAX(d.publikacje) >= 4;


-- 7. Dla każdego naukowca (id, nazwisko) podaj jego miejsce w rankingu według liczby doktorantów (przy równej liczbie doktorantów, posortuj po id); posortuj po id. 


SELECT n1.id, n1.nazwisko, count(n2.id)
FROM
	(SELECT n.id, n.nazwisko, count(d.id) doktoranci 
	 FROM naukowiec n LEFT JOIN naukowiec d ON  d.promotor = n.id 
	 GROUP BY n.id, n.nazwisko) n1, 
	(SELECT n.id, n.nazwisko, count(d.id) doktoranci 
	 FROM naukowiec n LEFT JOIN naukowiec d ON  d.promotor = n.id 
	 GROUP BY n.id, n.nazwisko) n2
WHERE (n1.doktoranci < n2.doktoranci) OR ((n1.doktoranci = n2.doktoranci) AND (n1.id >= n2.id))
GROUP BY n1.id, n1.nazwisko
ORDER BY n1.id;


-- 8. Dla każdego naukowca (id, nazwisko), który miał co najmniej 2 doktorantów, wypisz ilu ma doktorantów z co najmniej jedną publikacją; posortuj po id. 

SELECT n2dokt.id, n2dokt.nazwisko, COUNT(dokt.id)
FROM 
      (SELECT n.id, n.nazwisko
       FROM naukowiec n LEFT JOIN naukowiec d ON  d.promotor = n.id 
       GROUP BY n.id, n.nazwisko
       HAVING count(d.id) > 1) n2dokt 
     LEFT JOIN 
      (SELECT id, promotor
       FROM naukowiec
       WHERE publikacje >= 1) dokt
     ON dokt.promotor = n2dokt.id
GROUP BY n2dokt.id, n2dokt.nazwisko
ORDER BY n2dokt.id; 



 