-- 1. Dla każdego gracza (id) wypisz liczbę wygranych partii

SELECT gracz.id, COUNT(gra.id)
FROM gracz LEFT JOIN gra 
	ON (gracz.id=gracz1id AND wynik=1) OR (gracz.id=gracz2id AND wynik=2)
GROUP BY gracz.id;

-- 2. Posortuj graczy (wszystkie kolumny) według średniego czasu wygranej 
--    (nie patrzymy na przegrane partie)

SELECT wygrany.*, AVG(czas)
FROM gracz wygrany, gra
WHERE ((wygrany.id=gracz1id AND wynik=1) OR (wygrany.id=gracz2id AND wynik=2)) 
GROUP BY wygrany.id, wygrany.imie, wygrany.nazwisko, wygrany.ranking, wygrany.kraj, wygrany.dataur
ORDER BY AVG(czas);

-- 3. Wypisz graczy (imie, nazwisko), których ranking jest niższy niż 
--    średni ranking graczy, z którymi wygrali

SELECT wygrany.imie, wygrany.nazwisko
FROM gracz wygrany, gracz przegrany, gra
WHERE ((wygrany.id=gracz1id AND przegrany.id=gracz2id AND wynik=1) 
	OR 
       (wygrany.id=gracz2id AND przegrany.id=gracz1id AND wynik=2)) 
GROUP BY wygrany.imie, wygrany.nazwisko, wygrany.ranking
HAVING wygrany.ranking < AVG(przegrany.ranking);

-- 4. Wypisz graczy (wszystkie kolumny), ktorzy sa pojedynczymi reprezentantami 
--    swojego kraju. 

SELECT *
FROM gracz 
WHERE NOT EXISTS 
  (SELECT * FROM gracz drugi WHERE gracz.kraj=drugi.kraj AND gracz.id <> drugi.id);

-- 5. Wypisz graczy (imie,nazwisko), którzy wygrali co najwyzej dwa razy 
--    z graczami lepszymi od siebie w sensie rankingu

SELECT wygrany.imie, wygrany.nazwisko, COUNT(gra.id)
FROM gracz wygrany 
     LEFT JOIN gra 
     	  ON (wynik=1 AND wygrany.id=gracz1id) OR (wynik=2 AND wygrany.id=gracz2id)
     LEFT JOIN gracz przegrany
     	  ON ((wynik=1 AND przegrany.id=gracz2id) OR (wynik=2 AND przegrany.id=gracz1id))
	        AND (wygrany.ranking < przegrany.ranking)
GROUP BY wygrany.imie, wygrany.nazwisko
HAVING COUNT(przegrany.id) <= 2;

-- Michał Błaziak
SELECT a.imie, a.nazwisko 
FROM gracz a 
WHERE 
2 >= (SELECT COUNT(*) 
      FROM gra b 
      WHERE (b.gracz1id = a.id AND b.wynik = 1 AND
      	     a.ranking < (SELECT c.ranking FROM gracz c WHERE c.id = b.gracz2id)) 
         OR (b.gracz2id = a.id AND b.wynik = 2 AND
 	     a.ranking < (SELECT c.ranking FROM gracz c WHERE c.id = b.gracz1id)));

--Ewelina Krakowiak 
SELECT gracz1.imie, gracz1.nazwisko 
FROM gracz gracz1 
WHERE
2 >= (SELECT COUNT(gracz2.ranking) 
      	   FROM gracz gracz2, gra 
	   WHERE gracz1.ranking < gracz2.ranking AND
	    ((gra.wynik = 1 AND gra.gracz1id = gracz1.id AND gra.gracz2id = gracz2.id) OR		
	     (gra.wynik = 2 AND gra.gracz1id = gracz2.id AND gra.gracz2id = gracz1.id))) 
ORDER BY gracz1.imie, gracz1.nazwisko; 

-- 6. Wypisz kraje z których pochodzi co najmniej trzech graczy, 
--    którzy są w pierwszej dwudziestce w sensie rankingu

SELECT kraj 
FROM 
  (SELECT lepszy.id, lepszy.kraj
   FROM gracz lepszy, gracz gorszy
   WHERE lepszy.ranking >= gorszy.ranking
   GROUP BY lepszy.id, lepszy.kraj
   HAVING count(*) <= 20)
GROUP BY kraj
HAVING count(*)>=3;

SELECT kraj 
FROM (SELECT * FROM gracz WHERE rownum <=20 ORDER BY ranking)
GROUP BY kraj
HAVING count(*)>=3;

-- 7. Znajdź wszystkich graczy(imie, nazwisko), którzy od 2014-09-01 
--    wygrali przynajmniej dwie partie z graczami, którzy są od nich młodsi

SELECT wygrany.imie, wygrany.nazwisko
FROM gracz wygrany, gracz przegrany, gra
WHERE ((wygrany.id=gracz1id AND przegrany.id=gracz2id AND wynik=1) 
       OR (wygrany.id=gracz2id AND przegrany.id=gracz1id AND wynik=2)) 
      AND data >= DATE '2014-09-01'
      AND wygrany.dataur < przegrany.dataur
GROUP BY wygrany.imie, wygrany.nazwisko
HAVING count(*) >= 2;

-- 8. Dla każdego miesiąca roku 2014 wypisz wszystkich graczy, 
--    którzy wygrali najwięcej partii w danym miesiącu 
--    (null, jeśli w danym miesiącu nie było żadnych rozgrywek nieremisowych).

WITH statystyki AS
     (SELECT miesiac, zwyciezca, COUNT(idgry) ile
      FROM	      
      	(SELECT 
      	 	(CASE wynik WHEN 1 THEN gracz1id WHEN 2 THEN gracz2id END) zwyciezca, 
	      	EXTRACT(month FROM data) miesiac,  
	      	id idgry
 	 FROM gra
         WHERE data >= DATE '2014-01-01' AND wynik IS NOT NULL) 
      GROUP BY miesiac, zwyciezca)
SELECT miesiace.miesiac, gracz.imie, gracz.nazwisko
FROM 
  (SELECT rownum miesiac FROM dual CONNECT BY level <=12) miesiace 
  LEFT JOIN statystyki ON miesiace.miesiac = statystyki.miesiac 
  JOIN gracz ON gracz.id=statystyki.zwyciezca
WHERE ile >= ALL (SELECT ile FROM statystyki s WHERE s.miesiac = statystyki.miesiac)
ORDER BY miesiac;
