Ćwiczenia SQL - Esport (Wersja 2.0)

🧱 Struktura Bazy Danych (DDL) - Zaktualizowana

Do poprawnego działania ćwiczeń dodano tabelę turnieje oraz kolumnę id_turnieju do tabeli mecze.

Tabela	Kolumna	Typ Danych	Opis
`gry`	id, nazwa	INT, VARCHAR
`druzyny`	id, nazwa, id_gra	INT, VARCHAR, INT	FK do `gry`
`gracze`	id, nazwa, nick, punkty, id_druzyna	INT, VARCHAR, VARCHAR, INT, INT	FK do `druzyny`
`turnieje`	id, nazwa, data	INT, VARCHAR, DATE	Nowa tabela
`mecze`	id, id_druzyny, wynik, wygrana, id_turnieju	INT, INT, VARCHAR, BOOLEAN, INT	FK do `druzyny` i `turnieje`

Tworzenie i Wypełnianie (Inicjalizacja)

Pokaż Zaktualizowany Skrypt Tworzenia Bazy (Schema + Przykładowe Dane)


-- 1. Tworzenie Tabel (Zaktualizowano o turnieje)
CREATE TABLE gry (
    id INT PRIMARY KEY AUTO_INCREMENT,
    nazwa VARCHAR(100) NOT NULL UNIQUE
);

CREATE TABLE druzyny (
    id INT PRIMARY KEY AUTO_INCREMENT,
    nazwa VARCHAR(100) NOT NULL UNIQUE,
    id_gra INT,
    FOREIGN KEY (id_gra) REFERENCES gry(id)
);

CREATE TABLE gracze (
    id INT PRIMARY KEY AUTO_INCREMENT,
    nazwa VARCHAR(100) NOT NULL,
    nick VARCHAR(50) UNIQUE,
    punkty INT DEFAULT 0,
    id_druzyna INT,
    FOREIGN KEY (id_druzyna) REFERENCES druzyny(id)
);

CREATE TABLE turnieje (
    id INT PRIMARY KEY AUTO_INCREMENT,
    nazwa VARCHAR(100) NOT NULL UNIQUE,
    data DATE
);

CREATE TABLE mecze (
    id INT PRIMARY KEY AUTO_INCREMENT,
    id_druzyny INT NOT NULL,
    wynik VARCHAR(50),
    wygrana BOOLEAN,
    id_turnieju INT, -- Nowa kolumna
    FOREIGN KEY (id_druzyny) REFERENCES druzyny(id),
    FOREIGN KEY (id_turnieju) REFERENCES turnieje(id) -- Nowy klucz obcy
);

-- 2. Wypełnianie Danych (DML)
INSERT INTO gry (nazwa) VALUES
('League of Legends'), ('Dota 2'), ('Valorant'), ('Counter-Strike 2');

INSERT INTO druzyny (nazwa, id_gra) VALUES
('Team Liquid', 1), ('G2 Esports', 1), ('Cloud9', 3), ('Fnatic', 4);

INSERT INTO gracze (nazwa, nick, punkty, id_druzyna) VALUES
('Jan Kowalski', 'Kowalski', 1500, 1), ('Piotr Nowak', 'Piotrek', 1800, 2),
('Anna Lewa', 'Lewa_A', 900, 1), ('Krzysztof Zając', 'Kris_Zajac', 2100, 3),
('Magda Wójcik', 'M_Wo_j', 1200, 3);

-- Turnieje (ID 1 = Champions Cup, ID 2 = Winter Clash)
INSERT INTO turnieje (nazwa, data) VALUES
('Champions Cup', '2025-05-10'),
('Winter Clash', '2025-12-01');

-- Mecze (Przypisane do turniejów)
INSERT INTO mecze (id_druzyny, wynik, wygrana, id_turnieju) VALUES
(1, '2:1', 1, 1),  -- TL, Champions Cup, Wygrana
(2, '0:2', 0, 1),  -- G2, Champions Cup, Przegrana
(3, '2:0', 1, 2),  -- C9, Winter Clash, Wygrana
(4, '1:0', 1, 2),  -- Fnatic, Winter Clash, Wygrana
(1, '2:0', 1, 2);  -- TL, Winter Clash, Wygrana

1. 📑 Podstawowe Zapytania (DML)

Ćwicz operacje **C**reate, **R**ead, **U**pdate, **D**elete.

1.1. Dodaj nową drużynę (poprzednie zadanie 1)

Zadanie: Dodaj nową drużynę do tabeli druzyny (np. nazwa: "Team Phoenix").

Rozwiązanie

INSERT INTO druzyny (nazwa, id_gra) VALUES ('Team Phoenix', 4); -- Zakładamy, że gra w CS2 (id_gra=4)

1.2. Wyświetl listę graczy (poprzednie zadanie 2)

Zadanie: Wyświetl wszystkich graczy zapisanych w tabeli gracze.

Rozwiązanie

SELECT * FROM gracze;

1.3. Dodaj nową grę (poprzednie zadanie 3)

Zadanie: Dodaj nową grę do tabeli gry (np. nazwa: "Counter-Strike").

Rozwiązanie

INSERT INTO gry (nazwa) VALUES ('Counter-Strike');

1.4. Dodaj nowy turniej (Nowe zadanie 8)

Zadanie: Dodaj nowy turniej do tabeli turnieje (np. nazwa: "Summer Open", data: '2025-07-20').

Rozwiązanie

INSERT INTO turnieje (nazwa, data) VALUES ('Summer Open', '2025-07-20');

1.5. Wyświetl mecze turnieju (Nowe zadanie 9)

Zadanie: Wyświetl wszystkie mecze rozegrane w turnieju o nazwie "Champions Cup". Użyj podzapytania.

Rozwiązanie

SELECT * FROM mecze
WHERE id_turnieju = (SELECT id FROM turnieje WHERE nazwa = 'Champions Cup');

2. 📈 Raporty i Agregacja (SELECT, GROUP BY, ORDER BY)

Ćwicz filtrowanie i grupowanie danych.

2.1. Wyniki drużyn (poprzednie zadanie 4)

Zadanie: Wyświetl nazwy drużyn i liczbę wygranych meczów (używając JOIN).

Rozwiązanie

SELECT druzyny.nazwa, COUNT(mecze.id) AS wygrane
FROM druzyny
JOIN mecze ON druzyny.id = mecze.id_druzyny
WHERE mecze.wygrana = 1
GROUP BY druzyny.nazwa;

2.2. Ranking graczy (poprzednie zadanie 5)

Zadanie: Utwórz ranking graczy, wyświetlając nick i punkty, sortując malejąco.

Rozwiązanie

SELECT gracze.nick, gracze.punkty
FROM gracze
ORDER BY gracze.punkty DESC;

2.3. Statystyki graczy (Nowe zadanie 10)

Zadanie: Wyświetl **średnią**, **maksymalną** i **minimalną** liczbę punktów zdobytych przez graczy.

Rozwiązanie

SELECT AVG(punkty) AS srednia_punktow,
       MAX(punkty) AS max_punktow,
       MIN(punkty) AS min_punktow
FROM gracze;

2.4. Najlepsza drużyna (Nowe zadanie 11)

Zadanie: Znajdź drużynę z największą liczbą zwycięstw (tylko nazwę i liczbę wygranych).

Rozwiązanie

SELECT druzyny.nazwa, COUNT(mecze.id) AS wygrane
FROM druzyny
JOIN mecze ON druzyny.id = mecze.id_druzyny
WHERE mecze.wygrana = 1
GROUP BY druzyny.nazwa
ORDER BY wygrane DESC
LIMIT 1;

3. 🔧 Modyfikowanie Struktury (DDL/DML)

Ćwicz zmiany w tabelach i danych.

3.1. Dodaj kolumnę rangi (poprzednie zadanie 6)

Zadanie: Dodaj kolumnę ranga (VARCHAR(50)) do tabeli gracze.

Rozwiązanie

ALTER TABLE gracze ADD ranga VARCHAR(50);

3.2. Usuń kolumnę punktów (poprzednie zadanie 7)

Zadanie: Usuń kolumnę punkty z tabeli gracze.

Rozwiązanie

ALTER TABLE gracze DROP COLUMN punkty;