/* zadanie 1 */
SELECT DISTINCT ip FROM Odwiedziny WHERE ip not in (SELECT ip from AdresyIP);


/* zadanie 2 */
SELECT ip,COUNT(*) FROM Odwiedziny GROUP BY ip ORDER BY COUNT(*) DESC,ip;


/* zadanie 3 */
SELECT ip FROM Odwiedziny GROUP BY ip HAVING COUNT(DISTINCT strona)=1 ORDER BY ip;


/* zadanie 4 */
WITH tmp AS (
   SELECT o.ip,NVL(a.kraj,'NIEZNANY') as kraj FROM Odwiedziny o LEFT JOIN AdresyIP a ON (o.ip=a.ip)
) SELECT kraj,COUNT(*) FROM tmp GROUP BY kraj;


/* zadanie 5 */
SELECT DISTINCT ip FROM Odwiedziny o
WHERE NOT EXISTS
  (SELECT strona FROM Odwiedziny MINUS SELECT strona FROM Odwiedziny WHERE ip=o.ip);


/* zadanie 6 */
SELECT DISTINCT o1.ip,o2.ip FROM Odwiedziny o1,Odwiedziny o2
  WHERE o1.ip<o2.ip
    AND NOT EXISTS (SELECT strona FROM Odwiedziny WHERE ip=o1.ip MINUS SELECT strona FROM Odwiedziny WHERE ip=o2.ip)
    AND NOT EXISTS (SELECT strona FROM Odwiedziny WHERE ip=o2.ip MINUS SELECT strona FROM Odwiedziny WHERE ip=o1.ip);


/* zadanie 7 */
WITH zeros as (
    SELECT to_date('2014-'||rownum||'-01', 'yyyy-mm-dd') as d,NULL as ip
    FROM ( SELECT 1 FROM dual CONNECT BY LEVEL <= 10)
), odw AS (
    SELECT TRUNC(data,'MONTH') as d,ip FROM Odwiedziny
)
SELECT d,COUNT(ip) FROM (SELECT * FROM zeros UNION SELECT * FROM odw) GROUP BY d ORDER BY d;


/* zadanie 8 */
SELECT MAX(level)
  FROM Odwiedziny
CONNECT BY PRIOR ip=ip AND PRIOR data < data AND PRIOR strona=ref;

/* zadanie 9 */
WITH poczatki AS (
  SELECT ip,data FROM Odwiedziny o
    WHERE NOT EXISTS (SELECT * FROM Odwiedziny o2 WHERE o.ip=o2.ip AND o.data>o2.data AND o2.data>=(o.data-15/(24*60)) )
), konce AS (
  SELECT ip,data FROM Odwiedziny o
    WHERE NOT EXISTS (SELECT * FROM Odwiedziny o2 WHERE o.ip=o2.ip AND o.data<o2.data AND o2.data<=(o.data+15/(24*60)) )
), roznice AS (
  SELECT ip,data,(SELECT MIN(data) FROM konce WHERE ip=poczatki.ip AND data>=poczatki.data)-data as roznica
  FROM poczatki
)
SELECT ip,data,roznica*24*60 as data FROM roznice WHERE roznica=(SELECT MAX(roznica) FROM roznice);


/* zadanie 10 */

SELECT DISTINCT o1.ip,o2.ip FROM Odwiedziny o1,Odwiedziny o2
  WHERE o1.ip<o2.ip
    AND NOT EXISTS (
      (SELECT strona,count(*) FROM Odwiedziny WHERE ip=o1.ip GROUP BY strona)
      MINUS
      (SELECT strona,count(*) FROM Odwiedziny WHERE ip=o2.ip GROUP BY strona)
    )
    AND NOT EXISTS (
      (SELECT strona,count(*) FROM Odwiedziny WHERE ip=o2.ip GROUP BY strona)
      MINUS
      (SELECT strona,count(*) FROM Odwiedziny WHERE ip=o1.ip GROUP BY strona)
    );

