Pełna Strona Ćwiczeń SQL

📚 Struktura Bazy Danych i Dane Przykładowe

Poniżej znajduje się struktura tabel oraz dane, na których będziesz pracować. Struktura została zoptymalizowana pod kątem poprawności kluczy obcych (np. powiązanie `tZakwaterowanie` z `tPokoje`).

-- --------------------------------- -- STRUKTURA TABEL -- --------------------------------- CREATE TABLE tStudenci ( ID INT PRIMARY KEY, Nr_albumu INT NOT NULL, Miejsce_zamieszkania VARCHAR(100), Wydzial VARCHAR(100) ); CREATE TABLE tPokoje ( ID_pokoju INT PRIMARY KEY, Numer_pokoju VARCHAR(10), Pietro INT, Liczba_miejsc INT ); CREATE TABLE tZakwaterowanie ( ID_zakwaterowanie INT PRIMARY KEY, ID_studenta INT, ID_pokoju INT, Rok_akad INT, FOREIGN KEY (ID_studenta) REFERENCES tStudenci(ID), FOREIGN KEY (ID_pokoju) REFERENCES tPokoje(ID_pokoju) ); CREATE TABLE tWplaty ( ID INT AUTO_INCREMENT PRIMARY KEY, StudentID INT, Data_wplaty DATE, Wplata DECIMAL(10,2), Za_rok_akad INT, FOREIGN KEY (StudentID) REFERENCES tStudenci(ID) ); CREATE TABLE tKoszty_zakwaterowania ( ID_historia_oplat INT AUTO_INCREMENT PRIMARY KEY, ID_zakw INT, Od_kiedy DATE, Do_kiedy DATE, Kwota_mies_oplaty DECIMAL(10,2), FOREIGN KEY (ID_zakw) REFERENCES tZakwaterowanie(ID_zakwaterowanie) ); -- --------------------------------- -- DANE PRZYKŁADOWE -- --------------------------------- INSERT INTO tStudenci (ID, Nr_albumu, Miejsce_zamieszkania, Wydzial) VALUES (1, 10001, 'Białowieża', 'Mechaniczny'), (2, 10002, 'Kielce', 'Informatyki'), (3, 10003, 'Pińczów', 'Zarządzania'), (4, 10004, 'Opatów', 'Informatyki'), (5, 10005, 'Busko-Zdrój', 'Mechaniczny'); INSERT INTO tPokoje (ID_pokoju, Numer_pokoju, Pietro, Liczba_miejsc) VALUES (1, '101A', 1, 2), (2, '102B', 1, 3), (3, '201C', 2, 2), (4, '202D', 2, 1); INSERT INTO tZakwaterowanie (ID_zakwaterowanie, ID_studenta, ID_pokoju, Rok_akad) VALUES (1, 1, 1, 2024), (2, 2, 2, 2024), (3, 3, 3, 2024), (4, 4, 4, 2023), (5, 5, 1, 2024); INSERT INTO tWplaty (StudentID, Data_wplaty, Wplata, Za_rok_akad) VALUES (1, '2024-10-05', 350.00, 2024), (1, '2024-11-10', 350.00, 2024), (2, '2024-10-15', 300.00, 2024), (3, '2024-12-01', 320.00, 2024), (4, '2023-09-20', 310.00, 2023), (5, '2024-10-02', 350.00, 2024), (5, '2024-11-05', 350.00, 2024), (5, '2024-12-10', 350.00, 2024); INSERT INTO tKoszty_zakwaterowania (ID_zakw, Od_kiedy, Do_kiedy, Kwota_mies_oplaty) VALUES (1, '2024-10-01', '2024-10-31', 350.00), (1, '2024-11-01', '2024-11-30', 350.00), (2, '2024-10-01', '2024-10-31', 300.00), (3, '2024-12-01', '2024-12-31', 320.00), (4, '2023-09-01', '2023-09-30', 310.00), (5, '2024-10-01', '2024-10-31', 350.00), (5, '2024-11-01', '2024-11-30', 350.00), (5, '2024-12-01', '2024-12-31', 350.00);

Poziom 1: Podstawowe Zapytania Agregujące (DQL)

1. Zadanie 1_1: Suma wpłat z filtrowaniem grupowym

Wyświetl ID studenta i łączną sumę wpłat (**SUM(Wpłata)**) za rok akademicki **2024**. Pokaż tylko tych studentów, których łączna suma wpłat jest mniejsza niż 1000 zł. Wyniki posortuj malejąco według ID.

SELECT StudentID, SUM(Wpłata) AS SUMA_WPŁAT FROM tWpłaty WHERE Za_rok_akad = 2024 GROUP BY StudentID HAVING SUM(Wpłata) < 1000 ORDER BY StudentID DESC;

**Wyjaśnienie:** Klauzula **GROUP BY** grupuje wiersze, a następnie **SUM()** sumuje wpłaty. Warunek **HAVING** jest niezbędny do filtrowania wyników funkcji agregujących (SUM) po ich obliczeniu.

3. Zadanie 1_3: Zliczanie z warunkiem w połączeniu tabel

Oblicz liczbę zakwaterowań w tabeli tZakwaterowanie, które dotyczą studentów z **Wydziału Mechanicznego** w roku akademickim **2024**.

SELECT COUNT(*) AS LICZBA_ZAKWATEROWAŃ FROM tZakwaterowanie z JOIN tStudenci s ON z.ID_studenta = s.ID WHERE s.Wydzial = 'Mechaniczny' AND z.Rok_akad = 2024;

**Wyjaśnienie:** Musimy użyć **JOIN**, ponieważ `Wydział` jest w tabeli tStudenci. **COUNT(\*)** zlicza wiersze spełniające oba warunki po połączeniu.

4. Zadanie 1_6: Suma wpłat dla każdego studenta i roku

Wyświetl ID studenta, rok akademicki oraz sumę wszystkich wpłat (**SUM(Wpłata)**) dokonanych przez tego studenta w danym roku.

SELECT StudentID, Za_rok_akad, SUM(Wpłata) AS SUMA FROM tWpłaty GROUP BY StudentID, Za_rok_akad ORDER BY StudentID, Za_rok_akad;

**Wyjaśnienie:** Aby poprawnie pogrupować, należy umieścić w **GROUP BY** obie kolumny, po których chcemy grupować: **StudentID** i **Za\_rok\_akad**.

5. Zadanie 1_7: Liczba zakwaterowań na rok

Wyświetl liczbę zakwaterowań (**COUNT(\*)**) zarejestrowanych w tabeli tZakwaterowanie, grupując wyniki według roku akademickiego (**Rok\_akad**).

SELECT Rok_akad, COUNT(*) AS Liczba_zakwaterowan FROM tZakwaterowanie GROUP BY Rok_akad ORDER BY Rok_akad DESC;

**Wyjaśnienie:** Grupowanie po kolumnie **Rok\_akad** pozwala na zliczenie, ile rekordów przypada na każdy unikalny rok akademicki.

6. Zadanie 1_9: Średnia wartość wpłat na rok

Oblicz średnią wartość wpłat (**AVG(Wpłata)**) za zamieszkanie dla każdego roku akademickiego (**Za\_rok\_akad**).

SELECT Za_rok_akad, AVG(Wpłata) AS ŚREDNIA_KWOTA FROM tWpłaty GROUP BY Za_rok_akad ORDER BY Za_rok_akad DESC;

**Wyjaśnienie:** Funkcja **AVG()** oblicza średnią. Grupowanie po kolumnie **Za\_rok\_akad** zapewnia, że średnia jest obliczana oddzielnie dla każdego roku.

Poziom 2: Łączenia, Podzapytania i Operacje DML

7. Zadanie 2: Wstawianie danych (INSERT)

Wprowadź nowy rekord danych do tabeli tKoszty\_zakwaterowania. Użyj wartości: ID\_zakw = **2** (musi istnieć w tZakwaterowanie), Od\_kiedy = '2025-01-01', Do\_kiedy = '2025-01-31', Kwota\_mies\_opłaty = 300.00.

INSERT INTO tKoszty_zakwaterowania (ID_zakw, Od_kiedy, Do_kiedy, Kwota_mies_opłaty) VALUES (2, '2025-01-01', '2025-01-31', 300.00);

**Wyjaśnienie:** Polecenie **INSERT INTO** wstawia nowy rekord. Klucz obcy (ID\_zakw) musi istnieć w tabeli docelowej (tZakwaterowanie), aby zachować integralność referencyjną.

8. Zadanie 2_1: Suma wpłat z filtrowaniem grupowym i JOIN

Wyświetl numer albumu i sumę wpłat (**SUM(Wpłata)**) studentów za rok 2024, których łączna suma wpłat jest **większa niż 350 zł**. Użyj **JOIN**.

SELECT s.Nr_albumu, s.Miejsce_zamieszkania, SUM(w.Wpłata) AS SUMA_WPŁAT FROM tStudenci s JOIN tWpłaty w ON s.ID = w.StudentID WHERE w.Za_rok_akad = 2024 GROUP BY s.Nr_albumu, s.Miejsce_zamieszkania HAVING SUM(w.Wpłata) > 350 ORDER BY s.Nr_albumu;

**Wyjaśnienie:** Łączymy **tStudenci (s)** z **tWpłaty (w)**. Grupujemy wyniki po numerze albumu i używamy **HAVING** do filtrowania wyników agregacji.

9. Zadanie 2_3: Studenci bez wpłaty (LEFT JOIN)

Wyświetl numer albumu studentów, którzy **nie dokonali żadnej wpłaty** w roku akademickim **2024**. Wykorzystaj **LEFT JOIN** i sprawdź, czy kolumna z prawej strony jest **NULL**.

SELECT s.Nr_albumu, s.Miejsce_zamieszkania FROM tStudenci s LEFT JOIN tWpłaty w ON s.ID = w.StudentID AND w.Za_rok_akad = 2024 WHERE w.StudentID IS NULL ORDER BY s.Nr_albumu;

**Wyjaśnienie:** **LEFT JOIN** zwraca wszystkich studentów. Umieszczając warunek roku w **ON**, zapobiegamy przypadkowemu odrzuceniu rekordów przez klauzulę WHERE. Warunek **WHERE w.StudentID IS NULL** filtruje tylko tych, dla których nie znaleziono żadnej pasującej wpłaty w roku 2024.

10. Zadanie 2_5: Wpłaty powyżej średniej (Subzapytanie w HAVING)

Wyświetl studentów (Nr albumu) i ich łączną wpłatę, jeśli ich łączna wpłata w roku 2024 jest **większa niż średnia wpłat** za ten rok. Użyj **subzapytania skalarnego**.

**Wyjaśnienie:** **Subzapytanie** w klauzuli **HAVING** oblicza średnią wpłat dla wszystkich studentów w roku 2024. Zapytanie główne porównuje sumę wpłat każdego studenta z wynikiem tego subzapytania.

11. Zadanie 2_8: Studenci w pokojach jednoosobowych

Wyświetl numer albumu studentów, którzy zostali zakwaterowani w pokoju jednoosobowym (**Liczba\_miejsc = 1**) w roku akademickim 2024. Użyj połączeń tabel.

SELECT tStudenci.Nr_albumu, tPokoje.Numer_pokoju FROM tStudenci JOIN tZakwaterowanie ON tStudenci.ID = tZakwaterowanie.ID_studenta JOIN tPokoje ON tZakwaterowanie.ID_pokoju = tPokoje.ID_pokoju WHERE tPokoje.Liczba_miejsc = 1 AND tZakwaterowanie.Rok_akad = 2024 ORDER BY tPokoje.Numer_pokoju;

**Wyjaśnienie:** Wymagane jest **potrójne połączenie** (Studenci $\rightarrow$ Zakwaterowanie $\rightarrow$ Pokoje), aby połączyć dane osobowe studenta z cechami pokoju, w którym został zakwaterowany.

12. Zadanie 2_9: Studenci z wieloma wpłatami

Wyświetl studentów (Nr albumu), którzy dokonali **co najmniej dwóch wpłat** w roku akademickim 2024. Użyj **COUNT()** i **HAVING**.

SELECT s.Nr_albumu, s.Miejsce_zamieszkania FROM tStudenci s JOIN tWpłaty w ON s.ID = w.StudentID WHERE w.Za_rok_akad = 2024 GROUP BY s.Nr_albumu, s.Miejsce_zamieszkania HAVING COUNT(w.ID) >= 2 ORDER BY s.Nr_albumu;

**Wyjaśnienie:** Po pogrupowaniu studentów, klauzula **HAVING COUNT(w.ID) >= 2** filtruje grupy, pozostawiając tylko tych, dla których znaleziono dwie lub więcej wpłat w danym roku.

13. Zadanie 2_10: Studenci zakwaterowani w różnych pokojach

Wyświetl studentów (Nr albumu), którzy zostali zakwaterowani w **różnych pokojach** (różne Numer\_pokoju) w trakcie roku 2024. Wyświetl listę tych pokoi.

SELECT s.Nr_albumu, GROUP_CONCAT(DISTINCT p.Numer_pokoju ORDER BY p.Numer_pokoju) AS Pokoje FROM tStudenci s JOIN tZakwaterowanie z ON s.ID = z.ID_studenta JOIN tPokoje p ON z.ID_pokoju = p.ID_pokoju WHERE z.Rok_akad = 2024 GROUP BY s.Nr_albumu HAVING COUNT(DISTINCT p.Numer_pokoju) > 1 ORDER BY s.Nr_albumu;

**Wyjaśnienie:** Używamy **GROUP\_CONCAT(DISTINCT...)** do zebrania wszystkich unikalnych numerów pokoi. **HAVING COUNT(DISTINCT p.Numer\_pokoju) > 1** następnie filtruje tylko tych studentów, którzy mają więcej niż jeden unikalny pokój.

Poziom 3: Procedury, Indeksy i Widoki (DDL, DML)

14. Zadanie 3_1: Tworzenie procedury składowanej (Obliczanie sumy wpłat)

Stwórz procedurę składowaną Oblicz\_Wplaty, która przyjmie **ID studenta** i **rok akademicki** jako parametry wejściowe (IN) i wyświetli całkowitą sumę wpłat tego studenta w danym roku.

DELIMITER // CREATE PROCEDURE Oblicz_Wplaty(IN p_studentID INT, IN p_rok INT) BEGIN SELECT SUM(Wpłata) AS Całkowita_wplata FROM tWplaty WHERE StudentID = p_studentID AND Za_rok_akad = p_rok; END // DELIMITER ; -- Przykładowe wywołanie: CALL Oblicz_Wplaty(1, 2024);

**Wyjaśnienie:** Procedura Składowana (**STORED PROCEDURE**) jest przechowywana na serwerze i wykonuje zestaw instrukcji. Użycie **DELIMITER //** jest konieczne w MySQL do poprawnego zdefiniowania bloku procedury.

15. Zadanie 3_2: Tworzenie Indeksu dla wydajności

Stwórz instrukcję SQL, która utworzy indeks o nazwie idx\_studentID\_rok na kolumnach **StudentID** i **Za\_rok\_akad** w tabeli tWpłaty.

CREATE INDEX idx_studentID_rok ON tWpłaty(StudentID, Za_rok_akad);

**Wyjaśnienie:** Indeks wielokolumnowy jest bardzo efektywny, gdy zapytania często filtrują lub łączą dane używając **obu** kolumn, co jest kluczowe dla optymalizacji. [Image of B-tree index structure]

16. Zadanie 3_3: Tworzenie Widoku (VIEW)

Utwórz **widok** (VIEW) o nazwie v\_Suma\_Wplat\_2024, który będzie zawierał ID studenta oraz sumę wpłat za rok 2024.

CREATE VIEW v_Suma_Wplat_2024 AS SELECT StudentID, SUM(Wpłata) AS Całkowita_wplata FROM tWplaty WHERE Za_rok_akad = 2024 GROUP BY StudentID;

**Wyjaśnienie:** Widok to **wirtualna tabela**, która upraszcza złożone zapytania i zapewnia **warstwę abstrakcji**. Możesz go użyć w innych zapytaniach tak, jak zwykłej tabeli: SELECT * FROM v\_Suma\_Wplat\_2024;

Poziom 4: Dodatkowe Ćwiczenia na Połączenia i Agregację

17. Zadanie 4_1: Pełne Łączenie (INNER JOIN)

Wyświetl Nr albumu studenta, Wydział oraz Rok zakwaterowania. Pokaż tylko te rekordy, które mają pasujące wpisy w obu tabelach: tStudenci i tZakwaterowanie.

SELECT s.Nr_albumu, s.Wydzial, z.Rok_akad FROM tStudenci s INNER JOIN tZakwaterowanie z ON s.ID = z.ID_studenta ORDER BY s.Nr_albumu;

**Wyjaśnienie:** **INNER JOIN** jest domyślnym typem połączenia, który eliminuje rekordy bez pasujących par. W tym przypadku pokazuje tylko studentów, którzy są faktycznie zakwaterowani.

18. Zadanie 4_2: Studenci bez zakwaterowania (LEFT JOIN)

Wyświetl wszystkich studentów (**Nr albumu**) i rok ich zakwaterowania. Jeśli student nie był zakwaterowany, pole z rokiem powinno być **NULL**.

SELECT s.Nr_albumu, z.Rok_akad FROM tStudenci s LEFT JOIN tZakwaterowanie z ON s.ID = z.ID_studenta ORDER BY s.Nr_albumu;

**Wyjaśnienie:** **LEFT JOIN** gwarantuje, że wszystkie rekordy z lewej tabeli (tStudenci) zostaną zwrócone. Jeśli nie ma pasującego zakwaterowania, pola z tabeli tZakwaterowanie są wypełnione wartościami **NULL**.

19. Zadanie 4_3: Łączny koszt zakwaterowania (JOIN + GROUP BY)

Oblicz łączny koszt zakwaterowania (**SUM(Kwota\_mies\_oplaty)**) dla każdego studenta w roku 2024. Wyświetl numer albumu i sumę kosztów.

SELECT s.Nr_albumu, SUM(k.Kwota_mies_oplaty) AS SUMA_KOSZTÓW FROM tStudenci s JOIN tZakwaterowanie z ON s.ID = z.ID_studenta JOIN tKoszty_zakwaterowania k ON z.ID_zakwaterowanie = k.ID_zakw WHERE z.Rok_akad = 2024 GROUP BY s.Nr_albumu ORDER BY s.Nr_albumu;

**Wyjaśnienie:** Wymagane jest **potrójne połączenie** (Studenci $\rightarrow$ Zakwaterowanie $\rightarrow$ Koszty) z filtrowaniem po Rok\_akad. **GROUP BY** agreguje koszty na poziomie studenta.

20. Zadanie 4_4: Pokoje zajęte przez więcej niż 1 osobę

Wyświetl numer pokoju, piętro i liczbę miejsc dla pokoi, które w roku 2024 są zajęte przez **więcej niż jednego studenta**.

SELECT p.Numer_pokoju, p.Pietro, p.Liczba_miejsc FROM tPokoje p JOIN tZakwaterowanie z ON p.ID_pokoju = z.ID_pokoju WHERE z.Rok_akad = 2024 GROUP BY p.ID_pokoju, p.Numer_pokoju, p.Pietro, p.Liczba_miejsc HAVING COUNT(z.ID_studenta) > 1 ORDER BY p.Numer_pokoju;

**Wyjaśnienie:** Łączymy pokoje z zakwaterowaniem. Klauzula **HAVING COUNT(ID\_studenta) > 1** zlicza studentów zakwaterowanych w tym samym pokoju, filtrując tylko te, które mają więcej niż jednego mieszkańca.

Ćwiczenia SQL – System Zakwaterowania w Domu Studenta 🧑‍🎓