Ćwiczenia SQL – Zakwaterowanie

📚 Struktura Bazy Danych i Dane Przykładowe

Poniżej znajduje się struktura tabel oraz dane, na których będziesz pracować. Zapoznaj się z nazwami kolumn i ich typami.

-- Tabela z informacjami o studentach CREATE TABLE tStudenci ( ID INT PRIMARY KEY, Nr_albumu INT NOT NULL, Miejsce_zamieszkania VARCHAR(100), Wydzial VARCHAR(100) ); -- Tabela z informacjami o zakwaterowaniach CREATE TABLE tZakwaterowanie ( ID_zakwaterowanie INT PRIMARY KEY, ID_studenta INT, ID_pokoju INT, -- Dodano pole ID_pokoju do prawidłowego powiązania Rok_akad INT, FOREIGN KEY (ID_studenta) REFERENCES tStudenci(ID), FOREIGN KEY (ID_pokoju) REFERENCES tPokoje(ID_pokoju) ); -- Tabela z informacjami o wpłatach 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) -- Poprawiono klucz obcy ); -- Tabela z kosztami zakwaterowania 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) ); -- Tabela z pokojami CREATE TABLE tPokoje ( ID_pokoju INT PRIMARY KEY, Numer_pokoju VARCHAR(10), Pietro INT, Liczba_miejsc INT ); -- 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), -- Student 1 w pokoju 1 (2, 2, 2, 2024), -- Student 2 w pokoju 2 (3, 3, 3, 2024), -- Student 3 w pokoju 3 (4, 4, 4, 2023), -- Student 4 w pokoju 4 (5, 5, 1, 2024); -- Student 5 w pokoju 1 (pokój 1 jest dwuosobowy) 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);

Zadania Podstawowe (SELECT, GROUP BY, HAVING)

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 dla każdego studenta, a następnie funkcja **SUM()** sumuje wpłaty. Warunek **HAVING** jest niezbędny do filtrowania wyników funkcji agregujących (SUM) po ich obliczeniu. Zmieniono rok na 2024 (zgodnie z danymi).

2. Zadanie 1_2: Agregacja z filtrowaniem wielokrotnym

Oblicz łączną liczbę miejsc (**SUM(Liczba\_miejsc)**) we wszystkich pokojach, które znajdują się na **piętrze 3 lub 5**. Wyświetl wynik jako LICZBA MIEJSC.

SELECT SUM(Liczba_miejsc) AS LICZBA_MIEJSC FROM tPokoje WHERE Pietro IN (3, 5);

**Wyjaśnienie:** Funkcja **SUM()** oblicza sumę wartości z kolumny. Operator **IN (3, 5)** jest wydajniejszym i bardziej czytelnym sposobem filtrowania, niż użycie Pietro = 3 OR Pietro = 5.

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

Oblicz liczbę rekordów w tabeli tZakwaterowanie, które dotyczą studentów z **Wydziału Mechanicznego**. Załóż, że filtrujemy po roku akademickim **2024** (zgodnie z danymi).

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, a informacje o zakwaterowaniu w tZakwaterowanie. **COUNT(\*)** zlicza wiersze spełniające oba warunki: `Wydział` oraz `Rok_akad`.

4. Zadanie 1_4: Minimalna wartość z filtrowaniem miesięcznym

Wylicz minimalną wartość wpłaty (**MIN(Wpłata)**) dokonaną w miesiącu **kwietniu (4)** lub **czerwcu (6)**. Wyświetl wynik jako MIN WPŁATA.

SELECT MIN(Wpłata) AS MIN_WPŁATA FROM tWpłaty WHERE MONTH(Data_wplaty) IN (4, 6);

**Wyjaśnienie:** Funkcja **MONTH()** wyciąga numer miesiąca z kolumny typu `DATE`. Następnie używamy operatora **IN** do przefiltrowania wpłat tylko z tych dwóch miesięcy.

5. Zadanie 1_5: Zliczanie z zakresem i warunkiem tekstowym

Oblicz liczbę studentów (**COUNT(\*)**) zamieszkałych w **Białowieży**, których numer albumu zawiera się w zakresie **[65555; 66000]**.

SELECT COUNT(*) AS LICZBA_STUDENTÓW FROM tStudenci WHERE Miejsce_zamieszkania = 'Białowieża' AND Nr_albumu BETWEEN 65555 AND 66000;

**Wyjaśnienie:** Użycie operatora **BETWEEN** jest najczytelniejszym sposobem filtrowania wartości w określonym zakresie, włączając podane granice (równoważne `Nr_albumu >= 65555 AND Nr_albumu <= 66000`).

6. 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 uzyskać sumę wpłat dla każdego studenta w każdym roku, musimy pogrupować dane jednocześnie po dwóch kolumnach: **StudentID** i **Za\_rok\_akad**.

7. 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 FROM tZakwaterowanie GROUP BY Rok_akad ORDER BY Rok_akad DESC;

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

8. Zadanie 1_8: Suma miejsc na piętrze

Wyświetl numer piętra oraz całkowitą liczbę miejsc do zamieszkania (**SUM(Liczba\_miejsc)**) na każdym z pięter Domu Studenta.

SELECT Pietro, SUM(Liczba_miejsc) AS SUMA_MIEJSC FROM tPokoje GROUP BY Pietro ORDER BY Pietro;

**Wyjaśnienie:** Klauzula **GROUP BY Pietro** sumuje miejsca, grupując pokoje po numerze piętra, co jest typowym użyciem funkcji agregującej `SUM()` w połączeniu z grupowaniem.

9. 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, a nie dla wszystkich wpłat łącznie.

10. Zadanie 1_10: Suma wpłat miesięcznych z filtrem rocznym

Wyświetl sumaryczną wartość wpłat (**SUM(Wpłata)**) pogrupowaną według miesiąca (**MONTH(Data\_wplaty)**) dla wszystkich wpłat dokonanych w roku **2024** (zgodnie z danymi).

SELECT MONTH(Data_wplaty) AS Miesiąc, SUM(Wpłata) AS SUMA FROM tWpłaty WHERE YEAR(Data_wplaty) = 2024 GROUP BY MONTH(Data_wplaty) ORDER BY Miesiąc;

**Wyjaśnienie:** Najpierw filtrujemy dane za pomocą **WHERE YEAR()** na rok 2024. Następnie używamy **GROUP BY MONTH()** do agregacji (sumowania) wpłat wewnątrz każdego miesiąca w tym roku.

Zadania DML i Operacje Złożone (JOIN, Subzapytania)

11. 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. Pamiętaj, że ID\_historia\_opłat jest autonumerowane.

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. Wartości dat należy podać w standardowym formacie SQL (YYYY-MM-DD), a klucz obcy (ID\_zakw = 2) musi istnieć w tabeli docelowej (tZakwaterowanie), aby zachować integralność referencyjną.

12. 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** do połączenia danych studentów i wpłat.

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. W tym przykładzie wynik powinien pokazać studentów o ID 1, 3 i 5.

13. Zadanie 2_2: Pokoje zajęte przez więcej niż 1 osobę

Wyświetl numer pokoju, piętro i liczbę miejsc dla pokoi, które w danym roku (załóżmy 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. Grupujemy po identyfikatorze pokoju, a klauzula **HAVING COUNT(ID\_studenta) > 1** zlicza studentów zakwaterowanych w tym samym pokoju. Wg danych powinien być to pokój '101A'.

14. 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**.

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 wszystkie rekordy z lewej tabeli (tStudenci). Warunek **ON... AND w.Za\_rok\_akad = 2024** filtruje połączenie. Kluczowy jest warunek **WHERE w.StudentID IS NULL**, który odfiltrowuje studentów, dla których **nie znaleziono żadnej pasującej wpłaty** w roku 2024.

15. Zadanie 2_4: Łączny koszt zakwaterowania (JOIN + GROUP BY)

Oblicz łączny koszt zakwaterowania (**SUM(Kwota\_mies\_oplaty)**) dla każdego studenta, który był zakwaterowany 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** (JOIN) Studenci -> Zakwaterowanie -> Koszty. Filtrowanie po Rok\_akad = 2024 ogranicza dane do właściwego okresu. Następnie **GROUP BY** i **SUM()** agregują koszty na poziomie studenta.

16. 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**.

**Wyjaśnienie:** **Subzapytanie** w klauzuli **HAVING** oblicza średnią wpłat dla wszystkich studentów w roku 2024. Zapytanie główne grupuje wpłaty studentów i porównuje sumę każdej grupy z wynikiem zwróconym przez subzapytanie.

17. 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:** Klauzula **HAVING COUNT(w.ID) >= 2** filtruje grupy studentów, pozostawiając tylko tych, dla których znaleziono dwa lub więcej rekordów wpłat w tabeli tWpłaty dla danego roku.

18. 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 dla każdego studenta. **HAVING COUNT(DISTINCT p.Numer\_pokoju) > 1** następnie filtruje tylko tych studentów, którzy mają więcej niż jeden unikalny pokój.

19. Zadanie 2_11: Wpłaty powyżej średniej (Złożone DQL)

Wyświetl listę studentów (ID i Nr\_albumu) oraz ich łączną kwotę wpłat w roku 2024, jeśli ta kwota jest **wyższa niż średnia wszystkich wpłat** za ten rok. (Powtórzenie z 2\_5, ale z mniejszą ilością kolumn).

SELECT s.ID, s.Nr_albumu, SUM(w.Wpłata) AS Suma_wplat FROM tStudenci s JOIN tWpłaty w ON s.ID = w.StudentID WHERE w.Za_rok_akad = 2024 GROUP BY s.ID, s.Nr_albumu HAVING SUM(w.Wpłata) > ( SELECT AVG(Wpłata) FROM tWpłaty WHERE Za_rok_akad = 2024 ) ORDER BY Suma_wplat DESC;

**Wyjaśnienie:** Kluczowe jest użycie subzapytania jako wartości porównawczej w klauzuli **HAVING**. Subzapytanie oblicza wartość **skalarną** (pojedynczą) średniej, którą można następnie porównać z każdą pogrupowaną sumą studenta.

20. Zadanie 2_14: Tworzenie procedury składowanej

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 tWpłaty WHERE StudentID = p_studentID AND Za_rok_akad = p_rok; END // DELIMITER ; -- PRZYKŁAD WYWOŁANIA: -- CALL Oblicz_Wplaty(1, 2024);

**Wyjaśnienie:** **Procedura Składowana** (STORED PROCEDURE) jest wstępnie skompilowanym zestawem instrukcji SQL, który jest przechowywany na serwerze. Użycie **DELIMITER //** pozwala definiować blok procedury. Parametry **IN** przekazują wartości do procedury.

21. Zadanie 2_15: Tworzenie Indeksu

Stwórz instrukcję SQL, która utworzy indeks o nazwie idx\_studentID\_rok na kolumnach **StudentID** i **Za\_rok\_akad** w tabeli tWpłaty w celu optymalizacji zapytań o wpłaty w danym roku dla konkretnego studenta.

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 (**StudentID** i **Za\_rok\_akad**). Przyspiesza on operacje wyszukiwania, ponieważ baza danych nie musi przeszukiwać całej tabeli.

Ćwiczenia SQL – Zakwaterowanie w Domu Studenta