# (c) Kacper Kulczycki 2007
# plik posiada forme, umozliwiajaca uzycie go jako pliku wsadowego dla 
# mysql'a wymaga to jednak uprzedniego wstawienia nazwy grupy, w 
# rozwiazaniu zadania2

# rozwiazanie zadania 1

CREATE VIEW RGROUPS (gname,gid,ile) AS SELECT g.gname, g.gid, COUNT(p.gid) 
FROM GROUPS g, PASSWD p WHERE p.gid IN (SELECT gid FROM GROUPS WHERE 
gname=g.gname) GROUP BY g.gname HAVING COUNT(p.gid)>0;

SELECT * FROM RGROUPS;

# rozwiazanie zadania 2 ! uwaga  - nazwe grupy nalezy wpisac w dwoch 
# miejscach zamiast 'nazwa_grupy' !

SELECT MIN(p.uid)+1 AS uid0 FROM PASSWD p WHERE p.uid+1 NOT IN (SELECT 
p.uid FROM PASSWD p WHERE p.uid>(SELECT MIN(p.uid) FROM PASSWD p, GROUPS g 
WHERE g.gname='nazwa_grupy' AND p.gid=g.gid)) AND p.uid>(SELECT MIN(p.uid) 
FROM PASSWD p, GROUPS g WHERE g.gname='nazwa_grupy' AND p.gid=g.gid);

# rozwiazanie zadania 3

CREATE TABLE ZAWODNICY(uid INT NOT NULL, nazwisko CHAR(32),imie CHAR(32));

INSERT INTO ZAWODNICY SELECT p.uid, SUBSTRING_INDEX(TRIM(p.gecos),' ',-1), 
SUBSTRING_INDEX(TRIM(gecos),' ',1) FROM PASSWD p, GROUPS g WHERE 
p.gid=g.gid AND g.gname RLIKE 'fs00' ORDER BY RAND() LIMIT 10;

CREATE TABLE TURNIEJ(biale INT,czarne INT,wynik ENUM('b','c','r'));

INSERT INTO TURNIEJ(biale,czarne)SELECT b.uid,c.uid from ZAWODNICY b, 
ZAWODNICY c WHERE b.uid!=c.uid;

INSERT INTO TURNIEJ(biale,czarne)SELECT T.czarne,T.biale from TURNIEJ T;

UPDATE TURNIEJ SET wynik=IF(RAND()>2/3,'r', 
IF(TURNIEJ.czarne<TURNIEJ.biale,IF(RAND()>2/3,'c','b'), 
IF(RAND()>1/2,'c','b'))); 

CREATE VIEW TMP1(uide,wyniki) AS (SELECT T.biale AS uide, COUNT(T.biale) 
AS wyniki FROM TURNIEJ T WHERE T.wynik='b' GROUP BY T.biale) ORDER BY  
uide;

CREATE VIEW TMP2(uide,wyniki) AS (SELECT T.czarne AS uide, COUNT(T.czarne) 
AS wyniki FROM TURNIEJ T WHERE T.wynik='c' GROUP BY T.czarne) ORDER BY 
uide; 

CREATE VIEW TMP3(uide,wyniki) AS (SELECT T.czarne AS uide, 
COUNT(T.biale)/2 AS wyniki FROM TURNIEJ T WHERE T.wynik='r' GROUP BY 
T.czarne) ORDER BY uide;

CREATE VIEW TMP4(uide,wyniki) AS (SELECT T.biale AS uide, 
COUNT(T.czarne)/2 AS wyniki FROM TURNIEJ T WHERE T.wynik='r' GROUP BY 
T.biale) ORDER BY uide; 

CREATE VIEW TEMP1(uid,wyniki) AS (SELECT T1.uide, 
T1.wyniki+T2.wyniki+T3.wyniki+T4.wyniki FROM TMP1 T1, TMP2 T2, TMP3 T3, 
TMP4 T4 WHERE T2.uide=T1.uide AND T3.uide=T1.uide AND T4.uide=T1.uide);

CREATE VIEW TEMP2(uid,wygrane) AS (SELECT T1.uide,
T1.wyniki+T2.wyniki FROM TMP1 T1, TMP2 T2 WHERE T2.uide=T1.uide);

CREATE VIEW TEMP3(uid,nazwisko,imie,wyniki,wygrane) AS (SELECT 
z.uid,z.nazwisko,z.imie,t1.wyniki,t2.wygrane FROM (ZAWODNICY z LEFT JOIN 
TEMP1 t1 ON t1.uid=z.uid) LEFT JOIN TEMP2 t2 ON t1.uid=t2.uid ORDER BY 
wygrane DESC);

CREATE VIEW TMP5 AS (SELECT 
t1.uid, SUM(IF(t1.wyniki<t2.wyniki,1,IF(t1.wygrane>t2.wygrane AND 
t1.wyniki=t2.wyniki,0,IF(t1.wyniki<=t2.wyniki,1,0)))) AS lp, 
t1.nazwisko,t1.imie,t1.wyniki,t1.wygrane FROM TEMP3 t1, TEMP3 t2 WHERE 
t1.wyniki<=t2.wyniki OR 
t1.uid!=t2.uid GROUP BY uid ORDER BY wyniki DESC);

CREATE TABLE WYNIKI(pozycja INT,nazwisko CHAR(32),imie CHAR(32), wyniki 
FLOAT, wygrane INT);

INSERT INTO WYNIKI SELECT lp,nazwisko,imie,wyniki,wygrane from TMP5 
order by lp ASC;

SELECT * FROM WYNIKI;

# dodatkowy kawalek skryptu do usuniecia wszystkich stworzonych widokow i 
# tablic

DROP TABLE ZAWODNICY;
DROP TABLE TURNIEJ;
DROP TABLE WYNIKI;
DROP VIEW RGROUPS;
DROP VIEW TEMP1;
DROP VIEW TEMP2;
DROP VIEW TEMP3;
DROP VIEW TMP1;
DROP VIEW TMP2;
DROP VIEW TMP3;
DROP VIEW TMP4;
DROP VIEW TMP5;