Turniej E-sportowy - baza SQL

CREATE TABLE druzyny ( id_druzyny INT PRIMARY KEY, nazwa VARCHAR(50), miasto VARCHAR(50), kapitan VARCHAR(30) ); INSERT INTO druzyny VALUES (1,'CyberWarriors','Warszawa','Janek'), (2,'PixelMasters','Kraków','Anna'), (3,'GameOver','Gdańsk','Piotr'), (4,'NoobSlayers','Poznań','Ewa');

CREATE TABLE zawodnicy ( id_zawodnika INT PRIMARY KEY, imie VARCHAR(30), nick VARCHAR(30), id_druzyny INT, rola VARCHAR(20), FOREIGN KEY (id_druzyny) REFERENCES druzyny(id_druzyny) ); INSERT INTO zawodnicy VALUES (1,'Jan','Shadow','1','Sniper'), (2,'Anna','PixelQueen','2','Support'), (3,'Piotr','Destroyer','3','Carry'), (4,'Ewa','NoobKiller','4','Tank'), (5,'Tomasz','Flash','1','Carry'), (6,'Kasia','Light','2','Sniper');

CREATE TABLE mecze ( id_meczu INT PRIMARY KEY, data_meczu DATE, id_druzyna1 INT, id_druzyna2 INT, turniej VARCHAR(50), FOREIGN KEY (id_druzyna1) REFERENCES druzyny(id_druzyny), FOREIGN KEY (id_druzyna2) REFERENCES druzyny(id_druzyny) ); INSERT INTO mecze VALUES (1,'2025-09-01',1,2,'LatoCup'), (2,'2025-09-02',3,4,'LatoCup'), (3,'2025-09-05',1,3,'AutumnCup'), (4,'2025-09-06',2,4,'AutumnCup'), (5,'2025-09-10',1,4,'ProLeague');

CREATE TABLE wyniki ( id_wyniku INT PRIMARY KEY, id_meczu INT, zwyciezca INT, punkty_druzyny1 INT, punkty_druzyny2 INT, FOREIGN KEY (id_meczu) REFERENCES mecze(id_meczu), FOREIGN KEY (zwyciezca) REFERENCES druzyny(id_druzyny) ); INSERT INTO wyniki VALUES (1,1,2,15,20), (2,2,4,10,25), (3,3,1,30,22), (4,4,2,18,12), (5,5,1,40,35);

SELECT d.nazwa, COUNT(w.id_wyniku) AS wygrane FROM druzyny d LEFT JOIN wyniki w ON d.id_druzyny = w.zwyciezca GROUP BY d.id_druzyny ORDER BY wygrane DESC;

SELECT z.nick, d.nazwa, SUM( CASE WHEN w.id_wyniku IS NOT NULL AND (m.id_druzyna1=z.id_druzyny OR m.id_druzyna2=z.id_druzyny) THEN (CASE WHEN m.id_druzyna1=z.id_druzyny THEN w.punkty_druzyny1 ELSE w.punkty_druzyny2 END) ELSE 0 END ) AS punkty FROM zawodnicy z JOIN druzyny d ON z.id_druzyny = d.id_druzyny LEFT JOIN mecze m ON m.id_druzyna1=d.id_druzyny OR m.id_druzyna2=d.id_druzyny LEFT JOIN wyniki w ON w.id_meczu = m.id_meczu GROUP BY z.id_zawodnika ORDER BY punkty DESC;

SELECT m.id_meczu, d1.nazwa AS druzyna1, d2.nazwa AS druzyna2, ABS(w.punkty_druzyny1 - w.punkty_druzyny2) AS roznica FROM mecze m JOIN druzyny d1 ON m.id_druzyna1=d1.id_druzyny JOIN druzyny d2 ON m.id_druzyna2=d2.id_druzyny JOIN wyniki w ON m.id_meczu=w.id_meczu ORDER BY roznica DESC;

SELECT m.id_meczu, d1.nazwa AS druzyna1, w.punkty_druzyny1, d2.nazwa AS druzyna2, w.punkty_druzyny2 FROM mecze m JOIN wyniki w ON m.id_meczu=w.id_meczu JOIN druzyny d1 ON m.id_druzyna1=d1.id_druzyny JOIN druzyny d2 ON m.id_druzyna2=d2.id_druzyny;

SELECT z.nick, SUM(CASE WHEN m.id_druzyna1=z.id_druzyny THEN w.punkty_druzyny1 ELSE w.punkty_druzyny2 END) AS punkty FROM zawodnicy z JOIN mecze m ON z.id_druzyny=m.id_druzyny1 OR z.id_druzyny=m.id_druzyny2 JOIN wyniki w ON m.id_meczu=w.id_meczu GROUP BY z.nick HAVING SUM(CASE WHEN m.id_druzyna1=z.id_druzyny THEN w.punkty_druzyny1 ELSE w.punkty_druzyny2 END) > (SELECT AVG(punkty) FROM (SELECT SUM(CASE WHEN m2.id_druzyna1=z2.id_druzyny THEN w2.punkty_druzyny1 ELSE w2.punkty_druzyny2 END) AS punkty FROM zawodnicy z2 JOIN mecze m2 ON z2.id_druzyny=m2.id_druzyny1 OR z2.id_druzyny=m2.id_druzyny2 JOIN wyniki w2 ON m2.id_meczu=w2.id_meczu GROUP BY z2.nick) AS sub);

SELECT d.nazwa, COUNT(w.id_wyniku) AS wygrane FROM druzyny d LEFT JOIN wyniki w ON d.id_druzyny=w.zwyciezca GROUP BY d.id_druzyny HAVING COUNT(w.id_wyniku) > (SELECT AVG(wygrane) FROM (SELECT COUNT(w2.id_wyniku) AS wygrane FROM druzyny d2 LEFT JOIN wyniki w2 ON d2.id_druzyny=w2.zwyciezca GROUP BY d2.id_druzyny) AS sub);

SELECT m.id_meczu, d1.nazwa AS druzyna1, w.punkty_druzyny1, d2.nazwa AS druzyna2, w.punkty_druzyny2 FROM mecze m JOIN wyniki w ON m.id_meczu=w.id_meczu JOIN druzyny d1 ON m.id_druzyna1=d1.id_druzyny JOIN druzyny d2 ON m.id_druzyna2=d2.id_druzyny WHERE w.punkty_druzyny1 > (SELECT AVG((punkty_druzyny1+punkty_druzyny2)/2) FROM wyniki) OR w.punkty_druzyny2 > (SELECT AVG((punkty_druzyny1+punkty_druzyny2)/2) FROM wyniki);

SELECT nazwa FROM druzyny WHERE id_druzyny IN ( SELECT zwyciezca FROM wyniki w JOIN mecze m ON w.id_meczu=m.id_meczu WHERE m.turniej='LatoCup' GROUP BY zwyciezca HAVING COUNT(*) = (SELECT COUNT(*) FROM mecze WHERE turniej='LatoCup') );

SELECT z.nick, SUM(CASE WHEN m.id_druzyna1=z.id_druzyny THEN w.punkty_druzyny1 ELSE w.punkty_druzyny2 END) AS punkty FROM zawodnicy z JOIN mecze m ON z.id_druzyny=m.id_druzyny1 OR z.id_druzyny=m.id_druzyny2 JOIN wyniki w ON m.id_meczu=w.id_meczu GROUP BY z.nick HAVING SUM(CASE WHEN m.id_druzyna1=z.id_druzyny THEN w.punkty_druzyny1 ELSE w.punkty_druzyny2 END) > (SELECT AVG(CASE WHEN m2.id_druzyna1=z2.id_druzyny THEN w2.punkty_druzyny1 ELSE w2.punkty_druzyny2 END) FROM zawodnicy z2 JOIN mecze m2 ON z2.id_druzyny=m2.id_druzyny1 OR z2.id_druzyny=m2.id_druzyny2 JOIN wyniki w2 ON m2.id_meczu=w2.id_meczu WHERE z2.id_druzyny=z.id_druzyny);