Ćwiczenia SQL - Esport (Wersja Rozszerzona)

🧱 Struktura Bazy Danych (DDL)

Baza danych składa się z czterech tabel. Musisz je utworzyć przed rozpoczęciem ćwiczeń! Poniżej schemat DDL:

Tabela	Kolumna	Typ Danych	Opis
`gry`	id	INT	Primary Key
	nazwa	VARCHAR(100)	Nazwa gry
`druzyny`	id	INT	Primary Key
	nazwa	VARCHAR(100)	Nazwa drużyny
	id_gra	INT	Foreign Key do `gry`
`gracze`	id	INT	Primary Key
	nazwa	VARCHAR(100)	Imię i nazwisko
	nick	VARCHAR(50)	Nick gracza
	punkty	INT	Rankingowe punkty (ELO)
	id_druzyna	INT	Foreign Key do `druzyny`
`mecze`	id	INT	Primary Key
	id_druzyny	INT	Drużyna, której dotyczy wpis
	wynik	VARCHAR(50)	Rezultat meczu
	wygrana	BOOLEAN	Czy drużyna wygrała (1/0)

Tworzenie i Wypełnianie (Inicjalizacja)

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


-- 1. Tworzenie Tabel
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 mecze (
    id INT PRIMARY KEY AUTO_INCREMENT, 
    id_druzyny INT NOT NULL,
    wynik VARCHAR(50), 
    wygrana BOOLEAN, 
    FOREIGN KEY (id_druzyny) REFERENCES druzyny(id)
);

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

-- Drużyny (Zakładam, że 1=LoL, 3=Valorant, 4=CS2)
INSERT INTO druzyny (nazwa, id_gra) VALUES
('Team Liquid', 1),
('G2 Esports', 1),
('Cloud9', 3),
('Fnatic', 4),
('Team Phoenix', 4); -- Zgodnie z zadaniem 1

-- Gracze
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);

-- Mecze (ID 1=TL, ID 2=G2, ID 3=C9, ID 4=Fnatic, ID 5=TP)
INSERT INTO mecze (id_druzyny, wynik, wygrana) VALUES
(1, '2:1', 1), (1, '0:2', 0), (1, '2:0', 1), -- Team Liquid (2 wygrane)
(2, '1:2', 0), (2, '2:0', 1), -- G2 Esports (1 wygrana)
(3, '3:1', 1), (3, '1:3', 0), (3, '2:1', 1), -- Cloud9 (2 wygrane)
(4, '2:0', 1), (4, '1:0', 1); -- Fnatic (2 wygrane)

1. 📑 Podstawowe Zapytania (DML)

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

1.1. Wyświetl wszystkie drużyny

Zadanie: Wyświetl wszystkie wiersze z tabeli druzyny.

Rozwiązanie

SELECT * FROM druzyny;

1.2. Dodaj nową grę

Zadanie: Dodaj nową grę do tabeli gry (np. nazwa: "Overwatch 2").

Rozwiązanie

INSERT INTO gry (nazwa) VALUES ('Overwatch 2');

1.3. Zaktualizuj punkty gracza

Zadanie: Zwiększ liczbę punktów gracza o nicku 'Kowalski' do 2000.

Rozwiązanie

UPDATE gracze SET punkty = 2000 WHERE nick = 'Kowalski';

1.4. Usuń nową drużynę

Zadanie: Usuń drużynę o nazwie 'Team Phoenix' z tabeli druzyny (Pamiętaj o klauzuli WHERE!).

Rozwiązanie

DELETE FROM druzyny WHERE nazwa = 'Team Phoenix';

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

Ćwicz filtrowanie i grupowanie danych.

2.1. Gracze z ponad 1500 punktami

Zadanie: Wyświetl nick i punkty tych graczy, którzy mają więcej niż 1500 punktów. Posortuj malejąco po punktach.

Rozwiązanie

SELECT nick, punkty FROM gracze WHERE punkty > 1500 ORDER BY punkty DESC;

2.2. Wyniki drużyn (Liczba wygranych)

Zadanie: Wyświetl ID drużyny i łączną liczbę wygranych meczów.

Rozwiązanie

SELECT id_druzyny, COUNT(id) AS Liczba_Wygranych
FROM mecze
WHERE wygrana = 1
GROUP BY id_druzyny
ORDER BY Liczba_Wygranych DESC;

2.3. Średnia punktów w drużynie

Zadanie: Oblicz średnią punktów graczy dla każdej drużyny (użyj ID).

Rozwiązanie

SELECT id_druzyna, AVG(punkty) AS Srednia_Punktow
FROM gracze
GROUP BY id_druzyna
HAVING Srednia_Punktow > 1000; -- Filtr: tylko drużyny, których średnia przekracza 1000

3. 🔗 Zapytania Zaawansowane (JOIN, Subquery)

Ćwicz łączenie tabel i używanie podzapytań.

3.1. Drużyna i jej gra (JOIN)

Zadanie: Wyświetl nazwę drużyny i nazwę gry, w którą gra. Użyj **INNER JOIN**.

Rozwiązanie

SELECT druzyny.nazwa AS Nazwa_Druzyny, gry.nazwa AS Nazwa_Gry
FROM druzyny
INNER JOIN gry ON druzyny.id_gra = gry.id;

3.2. Gracze z drużynami (LEFT JOIN)

Zadanie: Wyświetl nick gracza i nazwę jego drużyny. Pokaż wszystkich graczy, nawet jeśli nie mają przypisanej drużyny (teoretycznie).

Rozwiązanie

SELECT gracze.nick, druzyny.nazwa AS Nazwa_Druzyny
FROM gracze
LEFT JOIN druzyny ON gracze.id_druzyna = druzyny.id;

3.3. Gracze w grze 'Valorant' (Podzapytanie)

Zadanie: Wyświetl nicki graczy, którzy grają w grę o nazwie 'Valorant', używając **podzapytania** w klauzuli WHERE.

Rozwiązanie

SELECT nick
FROM gracze
WHERE id_druzyna IN (
    SELECT id
    FROM druzyny
    WHERE id_gra = (
        SELECT id
        FROM gry
        WHERE nazwa = 'Valorant'
    )
);

4. 🔧 Modyfikowanie Struktury (DDL/DML)

Ćwicz zmiany w tabelach i danych.

4.1. Zmień typ kolumny

Zadanie: Zmień kolumnę wynik w tabeli mecze na typ danych **VARCHAR(10)**.

Rozwiązanie

ALTER TABLE mecze MODIFY COLUMN wynik VARCHAR(10);

4.2. Dodaj i ustaw domyślną wartość

Zadanie: Dodaj kolumnę kraj (VARCHAR(50)) do tabeli druzyny z domyślną wartością **'Polska'**.

Rozwiązanie

ALTER TABLE druzyny ADD kraj VARCHAR(50) DEFAULT 'Polska';