Ćwiczenia SQL – DDL, DML, DCL, SELECT, JOIN, TRANSAKCJE

Co tu ćwiczysz?
Rozpoznawanie, które polecenia SQL zmieniają strukturę bazy (DDL), które operują na danych (DML), a które sterują uprawnieniami (DCL). Tego typu pytania pojawiają się w testach INF.03. [web:27][web:29]

CREATE DATABASE szkola;

Dlaczego tak?
To polecenie należy do grupy DDL – definiuje strukturę bazy. Na egzaminie nazwa bazy jest zwykle podana literalnie i musi być przepisana bez zmian.

SHOW DATABASES;

Na co uważać?
SHOW DATABASES nie przyjmuje nazw – wyświetla wszystko. W treści arkusza może się pojawić jako krok kontrolny, żeby uczeń zweryfikował, czy baza została utworzona.

DROP DATABASE IF EXISTS test;

Uwaga egzaminacyjna:
– IF EXISTS zabezpiecza przed błędem, jeśli baza nie istnieje.
– Pomylenie DROP DATABASE z DROP TABLE to typowy błąd w testach jednokrotnego wyboru.

CREATE USER 'uczen'@'localhost' IDENTIFIED BY '1234';

Zapamiętaj:
CREATE USER tworzy konto w systemie bazy. Uprawnienia nadaje się dopiero osobno poleceniem GRANT – to jest właśnie SQL DCL. [web:27]

GRANT ALL PRIVILEGES ON szkola.* TO 'uczen'@'localhost';

Częsty błąd:
– pomylenie GRANT z INSERT (DML) lub CREATE TABLE (DDL).
– brak kropki po nazwie bazy (szkola.*) oznacza nadanie uprawnień do niczego.

Cel tej części:
Umiesz tworzyć tabele, dodawać kolumny i więzy (PRIMARY KEY, UNIQUE, FOREIGN KEY), tak jak wymaga tego projekt części bazodanowej na INF.03. [web:32]

CREATE TABLE Uczniowie (
  id INT AUTO_INCREMENT PRIMARY KEY,
  imie VARCHAR(30),
  nazwisko VARCHAR(30),
  klasa VARCHAR(10),
  srednia DECIMAL(3,2)
);

Dlaczego tak?
– PRIMARY KEY na kolumnie id zapewnia unikalność każdego rekordu.
– DECIMAL(3,2) pozwala przechowywać średnie w formacie np. 4.25 (3 cyfry, z czego 2 po przecinku).

ALTER TABLE Uczniowie
ADD COLUMN data_urodzenia DATE;

Wewnętrzne pytanie:
Czy naprawdę potrzebna jest osobna tabela na daty urodzenia? Jeżeli dana informacja nie powtarza się między różnymi rekordami, można zostawić ją w tej samej tabeli.

CREATE TABLE Klasy (
  id INT AUTO_INCREMENT PRIMARY KEY,
  symbol VARCHAR(10) UNIQUE,
  wychowawca VARCHAR(50)
);

Co robi UNIQUE?
Gwarantuje, że symbol klasy (np. 1TI, 4TI) nie powtórzy się. Baza sama pilnuje poprawności danych, nawet jeśli ktoś próbuje wstawić duplikat.

ALTER TABLE Uczniowie
ADD CONSTRAINT fk_klasa
FOREIGN KEY (klasa) REFERENCES Klasy(symbol);

Myślenie krok po kroku:
1. Dane o klasach są w osobnej tabeli.
2. W Uczniowie przechowujesz tylko symbol klasy.
3. FOREIGN KEY mówi bazie: „wartości w kolumnie klasa muszą istnieć w Klasy.symbol”.

DROP TABLE Klasy;

Uwaga:
Jeśli istnieją powiązane rekordy w tabeli Uczniowie, próba usunięcia tabeli Klasy może zakończyć się błędem z powodu klucza obcego.

Najważniejsza umiejętność:
Dobranie właściwych kolumn (SELECT), źródła danych (FROM), filtra (WHERE), sortowania (ORDER BY) oraz połączeń między tabelami (JOIN). [web:24]

SELECT * FROM Uczniowie;

Na egzaminie:
SELECT * jest dopuszczalne, jeśli zadanie nie wymaga konkretnych kolumn. W projektach lepiej wybierać tylko potrzebne kolumny.

SELECT imie, nazwisko, srednia
FROM Uczniowie
WHERE klasa = '4TI';

Dlaczego WHERE jest kluczowe?
Bez WHERE baza zwróci wszystkich uczniów. Brak filtra to jeden z najczęstszych powodów utraty punktów przy zadaniach z selekcją danych.

SELECT imie, nazwisko, srednia
FROM Uczniowie
ORDER BY srednia DESC;

Zapamiętaj:
– ASC (domyślnie) sortuje rosnąco, DESC – malejąco.
– ORDER BY zawsze pojawia się po WHERE i po JOIN.

SELECT AVG(srednia) AS srednia_uczniow
FROM Uczniowie;

Funkcje agregujące:
AVG, SUM, MIN, MAX, COUNT – to obowiązkowy zestaw na INF.03. W aliasie (AS srednia_uczniow) możesz nadać kolumnie czytelną nazwę.

SELECT Uczniowie.imie,
       Uczniowie.nazwisko,
       Klasy.wychowawca
FROM Uczniowie
JOIN Klasy ON Uczniowie.klasa = Klasy.symbol;

Jak to rozkminić?
1. Uczniowie mają kolumnę klasa, Klasy mają symbol.
2. Informacja o wychowawcy jest tylko w tabeli Klasy.
3. Skoro dane są w dwóch tabelach, potrzebujesz JOIN.

Uwaga egzaminacyjna:
– brak aliasów tabel jest poprawny, ale utrudnia czytanie kodu.
– literówka w nazwie kolumny zwykle oznacza 0 punktów za całe zapytanie.
– warunek łączenia tabel zawsze trafia do ON, a nie do WHERE.

Idea transakcji:
Kilka operacji traktujesz jak jedną całość. Albo wszystkie się udają (COMMIT), albo wszystkie cofają (ROLLBACK). [web:32]

INSERT INTO Uczniowie (imie, nazwisko, klasa, srednia)
VALUES ('Jan', 'Kowalski', '3TI', 4.25);

Częsty błąd:
– inna kolejność kolumn niż wartości w VALUES.
– pominięcie listy kolumn przy INSERT utrudnia późniejszą modyfikację tabeli.

UPDATE Uczniowie
SET srednia = srednia + 0.1;

Ostrożnie:
UPDATE bez WHERE modyfikuje wszystkie rekordy. Na arkuszu INF.03 tak zapisane polecenie jest traktowane jako krytyczny błąd, jeśli treść wymaga aktualizacji tylko części danych.

DELETE FROM Uczniowie
WHERE klasa = '1TI';

Bezpieczna praktyka:
Najpierw napisz SELECT z takim samym WHERE, żeby zobaczyć, które rekordy zostaną usunięte. Dopiero potem zamień SELECT na DELETE.

START TRANSACTION;
UPDATE Uczniowie
SET klasa = '4TI'
WHERE id = 5;
COMMIT;

START TRANSACTION;
DELETE FROM Uczniowie
WHERE srednia < 2.0;
ROLLBACK;

Po co ROLLBACK?
Pozwala przetestować „ryzykowne” operacje bez konsekwencji. Na egzaminie pokazuje, że rozumiesz pojęcie transakcji, nawet jeśli baza jest mała.

Co zyskujesz?
Jeden plik .sql potrafi odtworzyć całą bazę: utworzyć strukturę, dodać dane, zdefiniować widoki, procedury – dokładnie jak w zadaniach praktycznych INF.03. [web:31][web:34]

CREATE DATABASE firma;
USE firma;

CREATE TABLE Pracownicy (
  id INT AUTO_INCREMENT PRIMARY KEY,
  imie VARCHAR(30),
  nazwisko VARCHAR(30),
  dzial VARCHAR(50),
  pensja DECIMAL(8,2)
);

INSERT INTO Pracownicy (imie, nazwisko, dzial, pensja) VALUES
('Anna', 'Nowak', 'IT', 7200),
('Piotr', 'Kowalski', 'Ksiegowosc', 5400),
('Ewa', 'Wisniewska', 'Marketing', 6100);

CREATE VIEW IT_Pracownicy AS
SELECT *
FROM Pracownicy
WHERE dzial = 'IT';

Widok = zapisane zapytanie
Dzięki widokowi możesz używać skomplikowanego SELECT-a tak, jakby to była zwykła tabela, co często upraszcza kod aplikacji.

DELIMITER //
CREATE PROCEDURE DodajPracownika(
  IN im VARCHAR(30),
  IN naz VARCHAR(30),
  IN dz VARCHAR(30),
  IN pen DECIMAL(8,2)
)
BEGIN
  INSERT INTO Pracownicy (imie, nazwisko, dzial, pensja)
  VALUES (im, naz, dz, pen);
END //
DELIMITER ;

Na marginesie:
Procedury składowane pojawiają się rzadziej niż SELECT/INSERT, ale warto umieć rozpoznać ich składnię w arkuszu.

SELECT *
FROM Pracownicy
INTO OUTFILE '/var/lib/mysql-files/pracownicy.csv'
FIELDS TERMINATED BY ';'
LINES TERMINATED BY '\n';

Rzeczywistość serwera:
Ścieżka zapisu może się różnić w zależności od konfiguracji MySQL. W zadaniach egzaminacyjnych ścieżka jest zwykle podana w treści.