set pagesize 50;
set linesize 120;

/* 1. lista odcinków (w formacie: nazwa serialu, numer odcinka, nazwa odcinka, data emisji w postaci yyyy-mm-dd), posortowana rosnąco wg nazwy serialu, numeru odcinka, */
select s.nazwa,o.numer,o.nazwa,TO_CHAR(o.data, 'YYYY-MM-DD')
  from seriale s,odcinki o
 where s.id=o.serial
 order by s.nazwa,o.numer;
 
/* 2. lista seriali, oraz liczba odcinków, data pierwszego odcinka, data ostatniego odcinka (w formacie: nazwa serialu, liczba odcinków, data pierwszego odcinka w postaci dd-mm-yyyy, data ostatniego odcinka w postaci dd-mm-yyyy), posortowana malejąco wg daty ostatniego odcinka, */
select s.nazwa,
    COUNT(o.numer),
    TO_CHAR(MIN(o.data), 'DD-MM-YYYY') as data_pocz,
    TO_CHAR(MAX(o.data), 'DD-MM-YYYY') as data_kon
  from seriale s left join odcinki o ON (s.id=o.serial)
 group by s.id,s.nazwa
 order by MAX(o.data) desc;

/* 3. lista peseli aktorów, którzy występowali w jednym odcinku (dowolnego serialu) w więcej niż jednej roli, posortowana malejąco, */
SELECT DISTINCT a.pesel
  FROM aktorzy a,obsada o
 WHERE a.pesel=o.aktor
 GROUP BY a.pesel,o.serial,o.numer_odcinka
 HAVING COUNT(*)>1
 ORDER BY a.pesel DESC; 
  
/* 4. lista aktorów wraz z informacją o liczbie seriali w których występują (w formacie imię, nazwisko, liczba seriali), posortowana rosnąco wg nazwiska, imienia. */
SELECT a.imie,a.nazwisko,COUNT(DISTINCT o.serial)
  FROM aktorzy a LEFT JOIN obsada o ON (a.pesel=o.aktor)
 GROUP BY a.pesel,a.imie,a.nazwisko
 ORDER BY a.nazwisko,a.imie;
 
/* 5. dla każdego serialu wypisz najmłodszego aktora (lub aktorów), którzy w nim występują (w formacie: nazwa serialu, pesel aktora), uporządkowanie: rosnąco wg nazwy serialu, */
SELECT s.nazwa,a.pesel,a.data_ur,tmp.max_data_ur
  FROM seriale s,aktorzy a,
    (SELECT DISTINCT o.serial,MAX(aa.data_ur) as max_data_ur FROM obsada o, aktorzy aa where o.aktor=aa.pesel GROUP BY o.serial) tmp
 WHERE a.pesel IN (SELECT aktor FROM obsada o WHERE o.serial=s.id)
   AND s.id=tmp.serial
   AND a.data_ur=tmp.max_data_ur
 ORDER BY s.nazwa;
 
/* 6. dla każdego serialu wypisz aktora (lub aktorów), którzy występowali w największej liczbie odcinków (w formacie: nazwa serialu, pesel aktora, liczba odcinków), uporządkowanie: rosnąco wg nazwy serialu, */
SELECT s.nazwa,a.pesel,(
        SELECT COUNT(DISTINCT numer_odcinka) 
          FROM obsada o
         WHERE o.serial=s.id AND o.aktor=a.pesel
       ) as liczba_odcinkow
  FROM seriale s,aktorzy a
 WHERE a.pesel IN (SELECT aktor FROM obsada o WHERE o.serial=s.id)
   AND (
        SELECT COUNT(DISTINCT numer_odcinka) 
          FROM obsada o
         WHERE o.serial=s.id AND o.aktor=a.pesel
       ) = (
        SELECT MAX(liczba) 
          FROM ( 
             SELECT o.serial,o.aktor,COUNT(DISTINCT o.numer_odcinka) as liczba
               FROM obsada o 
             GROUP BY o.serial,o.aktor
          ) tmp
         WHERE tmp.serial=s.id
      )
 ORDER BY s.nazwa;
 
/* 7. dla każdego aktora wypisz jak długo występował w serialach (okres od pierwszego do ostatniego wystąpienia w serialu, jeśli aktor nie występował w żadnym serialu to NULL), uporządkowanie: rosnąco wg nazwiska, imienia. */
SELECT a.imie,a.nazwisko,MAX(data)-MIN(data)
  FROM aktorzy a 
  LEFT JOIN obsada o ON (a.pesel=o.aktor)
  LEFT JOIN odcinki od ON (o.serial=od.serial AND o.numer_odcinka=od.numer)
 GROUP BY a.nazwisko,a.imie;
 
/* 8. dla każdego serialu wypisz pesel aktorów, którzy występowali w każdym odcinku (co najmniej jednym), (w formacie: nazwa serialu, pesel), uporządkowanie: rosnąco wg. nazwy serialu, pesela. */
SELECT s.nazwa,a.pesel
  FROM seriale s,aktorzy a
 WHERE a.pesel IN (SELECT aktor FROM obsada o WHERE o.serial=s.id)
   AND NOT EXISTS (
     SELECT od.numer 
       FROM odcinki od 
      WHERE od.serial=s.id
        AND NOT EXISTS ( 
           SELECT * FROM obsada o 
            WHERE o.serial=s.id AND o.numer_odcinka=od.numer 
             AND o.aktor=a.pesel
        )
   )
 ORDER BY s.nazwa;
 
/* 9. dla każdego aktora wypisz maksymalną liczbę seriali w których występował jednocześnie (przyjmujemy, że występowanie w serialu oznacza okres od pierwszego występu w serialu do ostatniego), (w formacie: pesel aktora, liczba seriali) uporządkowanie: wg. malejącej liczby seriali, rosnąco wg. pesela. */
SELECT pesel,MAX(liczba_seriali) as m_liczba
FROM (
    SELECT ak.pesel,oo.data,COUNT(DISTINCT s.id) liczba_seriali
     FROM aktorzy ak,odcinki oo,seriale s
     WHERE 
        (SELECT MIN(data) FROM obsada o JOIN odcinki od ON (od.serial=o.serial AND od.numer=o.numer_odcinka) WHERE o.serial=s.id AND o.aktor=ak.pesel)
        <= oo.data
      AND 
        (SELECT MAX(data) FROM obsada o JOIN odcinki od ON (od.serial=o.serial AND od.numer=o.numer_odcinka)WHERE o.serial=s.id AND o.aktor=ak.pesel)
        >= oo.data
     GROUP BY ak.pesel,oo.data
) tmp
GROUP BY pesel
ORDER BY m_liczba DESC,pesel ASC;

/* 10. dla każdego serialu i występujących w nim aktorów, wypisz maksymalną liczbę kolejnych odcinków w których aktor występował (w formacie: nazwa serialu, imię aktora, nazwisko aktora, liczba odcinków) uporządkowanie: wg. nazwy serialu, nazwiska aktora, imienia aktora. */


SELECT s.nazwa,a.imie,a.nazwisko,MAX(tmp2.roznica)
FROM (
    SELECT 
        pesel,id,
        (SELECT COUNT(*) FROM odcinki WHERE numer>=tmp.numer and numer<=tmp.max_numer) as roznica
    FROM (    
        SELECT a.pesel,s.id,o.numer,MAX(oo.numer) max_numer
         FROM aktorzy a,seriale s,odcinki o,odcinki oo
         WHERE o.serial=s.id AND oo.serial=s.id
           AND o.numer<=oo.numer
           AND (SELECT COUNT(*) FROM odcinki WHERE numer>=o.numer AND numer<=oo.numer)
              = (
        	SELECT COUNT(DISTINCT numer_odcinka) FROM obsada ob 
        	 WHERE ob.aktor=a.pesel
	    	   AND ob.serial=s.id 
        	   AND ob.numer_odcinka>=o.numer
    		   AND ob.numer_odcinka<=oo.numer
	       )
        GROUP BY a.pesel,s.id,o.numer
    ) tmp
) tmp2,aktorzy a,seriale s
WHERE tmp2.pesel=a.pesel AND tmp2.id=s.id
GROUP BY tmp2.pesel,tmp2.id,s.nazwa,a.imie,a.nazwisko
ORDER BY s.nazwa,a.nazwisko,a.imie;

 
