Ćwiczenia SQL - Esport (Zaawansowane)

🧱 Struktura Bazy Danych (DDL) - Finalna Wersja

Zaktualizowano schemat o kolumny **rozegrane_mecze** w druzyny oraz **id_gracza** w mecze, aby obsłużyć nowe, zaawansowane ćwiczenia.

Tabela	Klucze	Nowe/Zmienione Kolumny
`druzyny`	PK: id	rozegrane_mecze (INT)
`gracze`	PK: id, FK: id_druzyna	punkty (INT)
`turnieje`	PK: id	nazwa, data
`mecze`	PK: id, FK: id_druzyny, id_turnieju	id_gracza (INT), wygrana (BOOLEAN)

Tworzenie i Wypełnianie (Inicjalizacja)

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


-- Używamy składni MySQL/MariaDB dla DELIMITER
-- Upewnij się, że używasz odpowiedniego klienta do uruchomienia procedur/wyzwalaczy!

-- Usuwanie tabel na wypadek, gdyby istniały
DROP TABLE IF EXISTS mecze;
DROP TABLE IF EXISTS gracze;
DROP TABLE IF EXISTS druzyny;
DROP TABLE IF EXISTS turnieje;
DROP TABLE IF EXISTS gry;
DROP PROCEDURE IF EXISTS reset_punkty;
DROP VIEW IF EXISTS top_gracze;

-- 1. Tworzenie Tabel (Zaktualizowano)
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,
    rozegrane_mecze INT DEFAULT 0, -- Dodano dla zadania 15
    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,
    id_gracza INT, -- Dodano dla zadań 12 i 13
    wynik VARCHAR(50),
    wygrana BOOLEAN,
    id_turnieju INT,
    FOREIGN KEY (id_druzyny) REFERENCES druzyny(id),
    FOREIGN KEY (id_gracza) REFERENCES gracze(id),
    FOREIGN KEY (id_turnieju) REFERENCES turnieje(id)
);

-- 2. Wypełnianie Danych (DML)
INSERT INTO gry (nazwa) VALUES ('League of Legends'), ('Valorant');
INSERT INTO druzyny (nazwa, id_gra) VALUES ('Team Liquid', 1), ('G2 Esports', 1), ('Cloud9', 2);

-- Gracze (ID 1=Jan K, ID 2=Piotr N, ID 3=Anna L)
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);

INSERT INTO turnieje (nazwa, data) VALUES ('Champions Cup', '2025-05-10');

-- Mecze (Używamy ID graczy i zakładamy, że tylko gracze TL i G2 grali)
-- Jan K. (ID 1) ma 11 wygranych (więcej niż 10 do testu View)
INSERT INTO mecze (id_druzyny, id_gracza, wygrana, id_turnieju) VALUES
(1, 1, 1, 1), (1, 1, 1, 1), (1, 1, 1, 1), (1, 1, 1, 1), (1, 1, 1, 1),
(1, 1, 1, 1), (1, 1, 1, 1), (1, 1, 1, 1), (1, 1, 1, 1), (1, 1, 1, 1), (1, 1, 1, 1), -- Jan Kowalski: 11 Win
(2, 2, 1, 1), (2, 2, 0, 1), (2, 2, 1, 1), (2, 2, 1, 1), -- Piotr Nowak: 3 Win
(1, 3, 1, 1), (1, 3, 1, 1); -- Anna Lewa: 2 Win

3. 🚀 Programowanie SQL (Widoki, Procedury, Wyzwalacze)

Ćwicz bardziej zaawansowane elementy, które definiują logikę bazy danych.

3.1. Znajdź gracza z największą liczbą wygranych

Zadanie: Wyświetl **nazwę** gracza, który wygrał najwięcej meczów. Użyj aliasu i sortowania.

Rozwiązanie

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

3.2. Utwórz widok najlepszych graczy (VIEW)

Zadanie: Utwórz **widok** top_gracze wyświetlający graczy z co najmniej **10 wygranymi** meczami. (Po utworzeniu widoku, możesz go wywołać za pomocą SELECT * FROM top_gracze;).

Rozwiązanie

CREATE VIEW top_gracze AS
SELECT gracze.nazwa, COUNT(mecze.id) AS wygrane
FROM gracze
JOIN mecze ON gracze.id = mecze.id_gracza
WHERE mecze.wygrana = 1
GROUP BY gracze.nazwa
HAVING wygrane >= 10;

3.3. Procedura resetująca punkty graczy (PROCEDURE)

Zadanie: Napisz procedurę, która zresetuje punkty wszystkich graczy do zera. Procedury wymagają użycia **DELIMITER** (w MySQL/MariaDB).

Rozwiązanie

DELIMITER //
CREATE PROCEDURE reset_punkty()
BEGIN
    UPDATE gracze SET punkty = 0;
END //
DELIMITER ;
-- Wywołanie procedury:
-- CALL reset_punkty();

3.4. Wyzwalacz przy dodaniu nowego meczu (TRIGGER)

Zadanie: Napisz wyzwalacz, który po dodaniu nowego meczu automatycznie **zwiększy kolumnę** rozegrane_mecze w tabeli druzyny o 1, dla drużyny, która rozegrała ten mecz.

Rozwiązanie

CREATE TRIGGER aktualizuj_mecze
AFTER INSERT ON mecze
FOR EACH ROW
BEGIN
    UPDATE druzyny
    SET rozegrane_mecze = rozegrane_mecze + 1
    WHERE id = NEW.id_druzyny;
END;
-- Testowanie wyzwalacza:
-- INSERT INTO mecze (id_druzyny, id_gracza, wygrana) VALUES (2, 2, 0);
-- SELECT rozegrane_mecze FROM druzyny WHERE id = 2; -- Wartość powinna wzrosnąć

1. 📑 Podstawowe Zapytania (DML) - Przykłady

1.1. Dodaj nową drużynę

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

Rozwiązanie

INSERT INTO druzyny (nazwa, id_gra) VALUES ('Team Phoenix', 2);

1.2. 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';

2. 📈 Raporty i Agregacja - Przykłady

2.1. Wyniki drużyn (Liczba wygranych)

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

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. Średnia punktów w drużynie

Zadanie: Oblicz średnią punktów graczy dla każdej drużyny.

Rozwiązanie

SELECT id_druzyna, AVG(punkty) AS Srednia_Punktow
FROM gracze
GROUP BY id_druzyna;