Ćwiczenia z SQL - Esport

Tworzenie zapytań do bazy danych

1. Transakcja aktualizująca punkty

Zadanie: Napisz transakcję, która zwiększy punkty gracza o 10 tylko wtedy, gdy jego aktualna liczba punktów jest mniejsza niż 100.

Rozwiązanie 
START TRANSACTION;
UPDATE gracze
SET punkty = punkty + 10
WHERE punkty < 100;
COMMIT;

2. Złożona procedura rankingowa

Zadanie: Utwórz procedurę aktualizuj_ranking, która ustawia ranking gracza na podstawie punktów.

Rozwiązanie 
DELIMITER //
CREATE PROCEDURE aktualizuj_ranking()
BEGIN
    DECLARE koniec INT DEFAULT 0;
    DECLARE id_gracza INT;
    DECLARE cur CURSOR FOR SELECT id FROM gracze ORDER BY punkty DESC;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET koniec = 1;

    SET @ranking = 1;
    OPEN cur;
    petla: LOOP
        FETCH cur INTO id_gracza;
        IF koniec THEN
            LEAVE petla;
        END IF;
        UPDATE gracze SET ranking = @ranking WHERE id = id_gracza;
        SET @ranking = @ranking + 1;
    END LOOP;
    CLOSE cur;
END //
DELIMITER ;

3. Wyzwalacz blokujący zbyt wiele rozegranych meczów

Zadanie: Utwórz wyzwalacz, który blokuje dodanie meczu, jeśli gracz rozegrał już 100 meczów.

Rozwiązanie 
CREATE TRIGGER blokada_meczy
BEFORE INSERT ON mecze
FOR EACH ROW
BEGIN
    DECLARE rozegrane INT;
    SELECT COUNT(*) INTO rozegrane FROM mecze WHERE id_gracza = NEW.id_gracza;
    IF rozegrane >= 100 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Gracz osiągnął limit meczów.';
    END IF;
END;

4. Optymalizacja zapytania rankingowego

Zadanie: Utwórz indeks na kolumnie punkty w tabeli gracze dla szybszych zapytań rankingowych.

Rozwiązanie 
CREATE INDEX idx_punkty ON gracze(punkty DESC);

5. Usunięcie gracza i powiązanych danych

Zadanie: Usuń gracza z tabeli gracze oraz wszystkie jego mecze.

Rozwiązanie 
DELETE FROM mecze WHERE id_gracza = 1;
DELETE FROM gracze WHERE id = 1;

Zaawansowane Zagadnienia

6. Tworzenie widoków (Views)

Zadanie: Utwórz widok, który wyświetli najlepszych 5 graczy na podstawie punktów.

Rozwiązanie 
CREATE VIEW najlepsze_gracze AS
SELECT imie, nazwisko, punkty
FROM gracze
ORDER BY punkty DESC
LIMIT 5;

7. Funkcje użytkownika (User-Defined Functions)

Zadanie: Napisz funkcję, która zwróci liczbę rozegranych meczów przez danego gracza.

Rozwiązanie 
DELIMITER //
CREATE FUNCTION liczba_meczy(id_gracza INT) 
RETURNS INT
DETERMINISTIC
BEGIN
    DECLARE liczba INT;
    SELECT COUNT(*) INTO liczba FROM mecze WHERE id_gracza = id_gracza;
    RETURN liczba;
END //
DELIMITER ;

8. Dynamiczne raporty w PHP

Zadanie: Napisz skrypt PHP, który generuje raport z rankingiem graczy na podstawie danych z bazy, umożliwiając filtrowanie po dacie (np. tylko gracze, którzy uzyskali punkty w ostatnich 30 dniach).

Rozwiązanie 
setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$czas = $_GET['czas'] ?? 30; // domyślnie 30 dni
$stmt = $pdo->prepare("
    SELECT imie, nazwisko, punkty
    FROM gracze
    WHERE data_ostatniego_punkty >= DATE_SUB(NOW(), INTERVAL :czas DAY)
    ORDER BY punkty DESC
");
$stmt->execute(['czas' => $czas]);

echo '
Raport Rankingowy
';
echo '';
echo '';
while ($row = $stmt->fetch()) {
    echo '';
    echo '';
    echo '';
    echo '';
    echo '';
}
echo '
ImięNazwiskoPunkty
' . htmlspecialchars($row['imie']) . '' . htmlspecialchars($row['nazwisko']) . '' . htmlspecialchars($row['punkty']) . '
';
?>

9. Złożone złączenia (JOIN)

Zadanie: Napisz zapytanie, które połączy tabelę gracze z tabelą mecze i wyświetli nazwiska graczy oraz liczbę rozegranych meczów.

Rozwiązanie 
SELECT g.nazwisko, COUNT(m.id) AS liczba_meczy
FROM gracze g
JOIN mecze m ON g.id = m.id_gracza
GROUP BY g.nazwisko;

10. Bezpieczeństwo w SQL i PHP

Zadanie: Zabezpiecz formularz logowania przed SQL Injection, stosując przygotowane zapytania w PHP.

Rozwiązanie 
setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$username = $_POST['username'];
$password = $_POST['password'];

$stmt = $pdo->prepare('SELECT * FROM gracze WHERE username = :username AND password = :password');
$stmt->bindParam(':username', $username);
$stmt->bindParam(':password', $password);
$stmt->execute();

if ($stmt->rowCount() > 0) {
    echo 'Zalogowano pomyślnie!';
} else {
    echo 'Błędne dane logowania.';
}
?>