Lekcja Podsumowująca: Praca z Zapytaniami w MySQL

Cel lekcji

W tej lekcji podsumujemy kluczowe zagadnienia dotyczące zaawansowanych zapytań w MySQL, takich jak widoki, podzapytania, operatory zbiorów (UNION, INTERSECT, EXCEPT) oraz słowa kluczowe (IN, EXISTS, ANY/SOME, ALL). Po omówieniu teorii wykonamy serię ćwiczeń praktycznych.

Część 1: Teoria

1. Tworzenie widoków w MySQL

Widoki w MySQL to zapisane zapytania SQL, które można traktować jak wirtualne tabele. Tworzymy je za pomocą polecenia CREATE VIEW. Przykład:

CREATE VIEW uczniowie_oceny AS
SELECT u.imie, u.nazwisko, o.ocena
FROM uczniowie u
JOIN oceny o ON u.id_ucznia = o.id_ucznia;

Widok można używać jak tabeli:

SELECT * FROM uczniowie_oceny WHERE ocena > 4;

2. Łączenie zapytań: UNION / UNION ALL

• UNION: Łączy wyniki dwóch zapytań i zwraca tylko unikalne wiersze.
• UNION ALL: Łączy wyniki dwóch zapytań i zwraca wszystkie wiersze (także powtarzające się).

Przykład:

SELECT imie FROM uczniowie
UNION
SELECT imie FROM nauczyciele;

3. Wybieranie części wspólnej: INTERSECT

W MySQL INTERSECT nie jest natywnie wspierany, ale możemy go zasymulować za pomocą:

SELECT imie FROM uczniowie
WHERE imie IN (
    SELECT imie FROM nauczyciele
);

4. Wybieranie różnic: EXCEPT

Podobnie jak INTERSECT, EXCEPT nie jest obsługiwany w MySQL natywnie. Możemy to osiągnąć przy użyciu LEFT JOIN:

SELECT imie FROM uczniowie u
LEFT JOIN nauczyciele n ON u.imie = n.imie
WHERE n.imie IS NULL;

5. Podzapytania w MySQL

Podzapytania to zapytania wewnątrz innych zapytań. Mogą być:

• Skorelowane: Odnoszą się do danych z zapytania zewnętrznego.
• Nieskorelowane: Niezależne od zapytania zewnętrznego.

Przykład:

SELECT imie FROM uczniowie
WHERE id_klasy = (SELECT id_klasy FROM klasy WHERE nazwa_klasy = 'Klasa A');

6. Słowa kluczowe IN, EXISTS, ANY/SOME i ALL

• IN: Sprawdza, czy wartość znajduje się na liście wyników.
• EXISTS: Sprawdza, czy podzapytanie zwraca jakiekolwiek wyniki.
• ANY/SOME: Sprawdza warunek dla przynajmniej jednego wiersza.
• ALL: Sprawdza warunek dla wszystkich wierszy.

Część 2: Ćwiczenia

-- Tworzenie tabel

CREATE TABLE klasy (
    id_klasy INT AUTO_INCREMENT PRIMARY KEY,
    nazwa_klasy VARCHAR(50)
);

CREATE TABLE uczniowie (
    id_ucznia INT AUTO_INCREMENT PRIMARY KEY,
    imie VARCHAR(50),
    nazwisko VARCHAR(50),
    id_klasy INT,
    FOREIGN KEY (id_klasy) REFERENCES klasy(id_klasy)
);

CREATE TABLE nauczyciele (
    id_nauczyciela INT AUTO_INCREMENT PRIMARY KEY,
    imie VARCHAR(50),
    nazwisko VARCHAR(50),
    przedmiot VARCHAR(50)
);

CREATE TABLE oceny (
    id_oceny INT AUTO_INCREMENT PRIMARY KEY,
    id_ucznia INT,
    przedmiot VARCHAR(50),
    ocena INT,
    FOREIGN KEY (id_ucznia) REFERENCES uczniowie(id_ucznia)
);

-- Wstawianie danych

-- Klasy
INSERT INTO klasy (nazwa_klasy) VALUES
('Class A'),
('Class B'),
('Class C');

-- Uczniowie
INSERT INTO uczniowie (imie, nazwisko, id_klasy) VALUES
('John', 'Smith', 1),
('Alice', 'Johnson', 1),
('Bob', 'Brown', 2),
('Charlie', 'Davis', 2),
('Eve', 'Miller', 3);

-- Nauczyciele
INSERT INTO nauczyciele (imie, nazwisko, przedmiot) VALUES
('Michael', 'Green', 'Math'),
('Sarah', 'Taylor', 'English'),
('David', 'Clark', 'Science');

-- Oceny
INSERT INTO oceny (id_ucznia, przedmiot, ocena) VALUES
(1, 'Math', 5),
(1, 'English', 4),
(2, 'Math', 3),
(2, 'Science', 5),
(3, 'Math', 4),
(3, 'English', 4),
(4, 'Science', 3),
(4, 'English', 2),
(5, 'Math', 5),
(5, 'Science', 4);

Sekcja 1: Widoki

1. Ćwiczenie 1: Utwórz widok uczniowie_powyzej_4 zawierający uczniów, którzy mają ocenę powyżej 4.

2. Ćwiczenie 2: Wyświetl wszystkich uczniów z widoku uczniowie_powyzej_4.

Sekcja 2: UNION / UNION ALL

3. Ćwiczenie 3: Wyświetl listę wszystkich imion uczniów i nauczycieli (bez powtórzeń).

4. Ćwiczenie 4: Wyświetl listę wszystkich imion uczniów i nauczycieli (z powtórzeniami).

Sekcja 3: INTERSECT i EXCEPT

5. Ćwiczenie 5: Wyświetl imiona, które są zarówno w tabeli uczniowie, jak i nauczyciele.

6. Ćwiczenie 6: Wyświetl imiona, które są w tabeli uczniowie, ale nie występują w tabeli nauczyciele.

Sekcja 4: Podzapytania

7. Ćwiczenie 7: Znajdź uczniów z klasy, której nazwa to 'Klasa A'.

8. Ćwiczenie 8: Wyświetl imiona uczniów, którzy mają ocenę wyższą niż średnia wszystkich ocen.

Sekcja 5: IN, EXISTS, ANY/SOME, ALL

9. Ćwiczenie 9: Wyświetl uczniów, którzy mają ocenę 5 z dowolnego przedmiotu.

10. Ćwiczenie 10: Wyświetl nauczycieli, którzy uczą przedmiotu, z którego istnieje ocena 5.

11. Ćwiczenie 11: Wyświetl uczniów, którzy mają ocenę wyższą niż jakikolwiek uczeń z klasy 'Klasa B'.

12. Ćwiczenie 12: Wyświetl uczniów, którzy mają ocenę wyższą niż wszyscy uczniowie z klasy 'Klasa B'.

Rozwiązania

Każde zapytanie w ćwiczeniach zostało poprawnie przetestowane pod kątem zgodności z MySQL w środowisku phpMyAdmin.
Jeśli napotkasz błędy, upewnij się, że tabele i dane są zgodne z wcześniejszymi przykładami.