Ćwiczenia SQL – Zakwaterowanie w Domu Studenta
Struktura bazy danych
-- 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,
Rok_akad INT,
FOREIGN KEY (ID_studenta) REFERENCES tStudenci(ID)
);
-- Tabela z informacjami o wpłatach
CREATE TABLE tWpłaty (
ID INT, -- identyfikator osoby (studenta)
Data_wpłaty DATE,
Wpłata CURRENCY,
Za_rok_akad INT
);
-- Tabela z kosztami zakwaterowania
CREATE TABLE tKoszty_zakwaterowania (
ID_historia_opłat AUTOINCREMENT PRIMARY KEY,
ID_zakw INT,
Od_kiedy DATE,
Do_kiedy DATE,
Kwota_mies_opłaty CURRENCY,
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
-- Studenci
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');
-- Zakwaterowanie
INSERT INTO tZakwaterowanie (ID_zakwaterowanie, ID_studenta, Rok_akad) VALUES
(1, 1, 2024),
(2, 2, 2024),
(3, 3, 2024),
(4, 4, 2023),
(5, 5, 2024);
-- Pokoje
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);
-- Wpłaty
INSERT INTO tWpłaty (ID, Data_wpłaty, Wpłata, 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);
-- Koszty zakwaterowania
INSERT INTO tKoszty_zakwaterowania (ID_zakw, Od_kiedy, Do_kiedy, Kwota_mies_opłaty) 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);
Zadanie 1_1
Utwórz instrukcję SQL o nazwie sql1_1, która wyświetli informację o łącznej kwocie wpłat dokonanych przez każdą osobę za rok akademicki 2016. Należy wyświetlić tylko te osoby, których suma wpłat jest mniejsza niż 1000 zł. Wyniki posortuj malejąco według ID.
SELECT ID, SUM(Wpłata) AS [SUMA WPŁAT]
FROM tWpłaty
WHERE Za_rok_akad = 2016
GROUP BY ID
HAVING SUM(Wpłata) < 1000
ORDER BY ID DESC;
Zadanie 1_2
Utwórz instrukcję SQL do wyliczenia liczby miejsc we wszystkich pokojach Domu Studenta ulokowanych na trzecim oraz piątym piętrze.
SELECT SUM(Liczba_miejsc) AS [LICZBA MIEJSC]
FROM tPokoje
WHERE Pietro IN (3, 5);
Zadanie 1_3
Utwórz instrukcję SQL do wyliczenia liczby wierszy w tabeli tZakwaterowanie dotyczących zakwaterowania w latach 2014 oraz 2016 osób skierowanych przez Wydział Mechaniczny.
SELECT COUNT(*)
FROM tZakwaterowanie
WHERE Year(Data_zakwaterowania) IN (2014, 2016)
AND Wydział = 'Mechaniczny';
Zadanie 1_4
Utwórz instrukcję SQL do wyliczenia minimalnej wartości wpłat dokonanych w miesiącu kwietniu lub czerwcu.
SELECT MIN(Wpłata) AS [MIN WPŁATA]
FROM tWpłaty
WHERE Month(Data_wpłaty) IN (4, 6);
Zadanie 1_5
Utwórz instrukcję SQL do wyliczenia liczby studentów zamieszkałych na stałe w Białowieży, którzy mają numery albumu z zakresu <65555; 66000>.
SELECT COUNT(*) AS [LICZBA STUDENTÓW]
FROM tStudenci
WHERE Miejsce_zamieszkania = 'Białowieża'
AND Nr_albumu BETWEEN 65555 AND 66000;
Zadanie 1_6
Utwórz polecenie SQL w celu wyświetlenia sum wszystkich wpłat dokonanych przez studenta o założonym numerze ID za każdy rok akademicki.
SELECT ID, Za_rok_akad, SUM(Wpłata) AS [SUMA]
FROM tWpłaty
GROUP BY ID, Za_rok_akad;
Zadanie 1_7
Utwórz polecenie SQL w celu wyświetlenia liczby zakwaterowań w Domu Studenta w każdym roku.
SELECT Year(Data_zakwaterowania) AS Rok, COUNT(*) AS Liczba
FROM tZakwaterowanie
GROUP BY Year(Data_zakwaterowania);
Zadanie 1_8
Utwórz polecenie SQL w celu wyświetlenia informacji ile w sumie jest miejsc do zamieszkania na każdym piętrze Domu Studenta.
SELECT Pietro, SUM(Liczba_miejsc) AS [SUMA MIEJSC]
FROM tPokoje
GROUP BY Pietro;
Zadanie 1_9
Utwórz polecenie SQL w celu wyświetlenia średniej wartości kwot wpłaconych za zamieszkanie w Domu Studenta dla każdego roku.
SELECT Year(Data_wpłaty) AS Rok, AVG(Wpłata) AS [ŚREDNIA KWOTA]
FROM tWpłaty
GROUP BY Year(Data_wpłaty);
Zadanie 1_10
Utwórz instrukcję SQL w celu wyświetlenia sumarycznej wartości kwot za zamieszkanie w Domu Studenta wpłaconych w każdym miesiącu 2017 roku. Dane należy pogrupować według miesiąca.
SELECT Month(Data_wpłaty) AS Miesiąc, SUM(Wpłata) AS [SUMA]
FROM tWpłaty
WHERE Year(Data_wpłaty) = 2017
GROUP BY Month(Data_wpłaty);
Zadanie 2
Utwórz instrukcję SQL o nazwie sql2 w celu wprowadzenia jednego rekordu danych do tabeli tKoszty_zakwaterowania. Pole ID_historia_opłat ma typ Autonumerowanie, więc nie podajemy dla niego wartości. Pole ID_zakw jest kluczem obcym i musi zawierać wartość istniejącą w tabeli tZakwaterowanie.
INSERT INTO tKoszty_zakwaterowania
(ID_zakw, Od_kiedy, Do_kiedy, Kwota_mies_opłaty)
VALUES (6, #2016-10-01#, #2017-06-30#, 280);
Zadanie 2_1
Utwórz instrukcję SQL o nazwie sql2_1, która wyświetli informacje o studentach, którzy zapłacili więcej niż 300 zł za zakwaterowanie w roku akademickim 2024. Wyświetl imiona i nazwiska studentów, numer albumu oraz sumę wpłat. Posortuj wynik według numeru albumu rosnąco.
SELECT tStudenci.Nr_albumu, tStudenci.Miejsce_zamieszkania, SUM(tWpłaty.Wpłata) AS [SUMA WPŁAT]
FROM tStudenci
JOIN tWpłaty ON tStudenci.ID = tWpłaty.ID
WHERE tWpłaty.Za_rok_akad = 2024
GROUP BY tStudenci.Nr_albumu, tStudenci.Miejsce_zamieszkania
HAVING SUM(tWpłaty.Wpłata) > 300
ORDER BY tStudenci.Nr_albumu;
Zadanie 2_2
Utwórz zapytanie SQL, które wyświetli pokoje, które są zajęte przez więcej niż jedną osobę. Wyświetl numer pokoju, piętro oraz liczbę miejsc. Posortuj wynik według numeru pokoju.
SELECT tPokoje.Numer_pokoju, tPokoje.Pietro, tPokoje.Liczba_miejsc
FROM tPokoje
JOIN tZakwaterowanie ON tPokoje.ID_pokoju = tZakwaterowanie.ID_zakwaterowanie
GROUP BY tPokoje.Numer_pokoju, tPokoje.Pietro, tPokoje.Liczba_miejsc
HAVING COUNT(tZakwaterowanie.ID_studenta) > 1
ORDER BY tPokoje.Numer_pokoju;
Zadanie 2_3
Stwórz zapytanie SQL, które pokaże studentów, którzy nie zapłacili za zakwaterowanie w roku akademickim 2024. Wyświetl numer albumu oraz imię i nazwisko studenta. Posortuj wynik według numeru albumu.
SELECT tStudenci.Nr_albumu, tStudenci.Miejsce_zamieszkania
FROM tStudenci
LEFT JOIN tWpłaty ON tStudenci.ID = tWpłaty.ID
WHERE tWpłaty.Wpłata IS NULL
AND tWpłaty.Za_rok_akad = 2024
ORDER BY tStudenci.Nr_albumu;
Zadanie 2_4
Utwórz zapytanie SQL, które obliczy łączny koszt zakwaterowania dla każdego studenta za cały rok akademicki 2024. Wyświetl numer albumu studenta oraz sumę kosztów. Posortuj wynik według numeru albumu.
SELECT tStudenci.Nr_albumu, SUM(tKoszty_zakwaterowania.Kwota_mies_opłaty) AS [SUMA KOSZTÓW]
FROM tStudenci
JOIN tZakwaterowanie ON tStudenci.ID = tZakwaterowanie.ID_studenta
JOIN tKoszty_zakwaterowania ON tZakwaterowanie.ID_zakwaterowanie = tKoszty_zakwaterowania.ID_zakw
WHERE tKoszty_zakwaterowania.Od_kiedy BETWEEN '2024-10-01' AND '2024-12-31'
GROUP BY tStudenci.Nr_albumu
ORDER BY tStudenci.Nr_albumu;
Zadanie 2_5
Utwórz zapytanie SQL, które wyświetli studentów, którzy zapłacili więcej niż średnia wartość opłat w roku akademickim 2024. Wyświetl numer albumu, imię i nazwisko studenta oraz łączną kwotę wpłat. Posortuj wynik malejąco według łącznej kwoty wpłat.
SELECT tStudenci.Nr_albumu, tStudenci.Miejsce_zamieszkania, SUM(tWpłaty.Wpłata) AS [SUMA WPŁAT]
FROM tStudenci
JOIN tWpłaty ON tStudenci.ID = tWpłaty.ID
WHERE tWpłaty.Za_rok_akad = 2024
GROUP BY tStudenci.Nr_albumu, tStudenci.Miejsce_zamieszkania
HAVING SUM(tWpłaty.Wpłata) > (
SELECT AVG(Wpłata)
FROM tWpłaty
WHERE Za_rok_akad = 2024
)
ORDER BY [SUMA WPŁAT] DESC;
Zadanie 2_6
Utwórz zapytanie SQL, które wyświetli numery pokoi, które były zajmowane przez studentów w roku akademickim 2024, ale nie zapłacili oni za zakwaterowanie. Wyświetl numer pokoju oraz piętro. Posortuj wynik według numeru pokoju.
SELECT tPokoje.Numer_pokoju, tPokoje.Pietro
FROM tPokoje
JOIN tZakwaterowanie ON tPokoje.ID_pokoju = tZakwaterowanie.ID_zakwaterowanie
LEFT JOIN tWpłaty ON tZakwaterowanie.ID_studenta = tWpłaty.ID
WHERE tWpłaty.Wpłata IS NULL
AND tWpłaty.Za_rok_akad = 2024
ORDER BY tPokoje.Numer_pokoju;
Zadanie 2_7
Stwórz zapytanie SQL, które pokaże studentów, którzy zapłacili za zakwaterowanie w roku akademickim 2024, ale nie zostali zakwaterowani (brak wpisu w tabeli tZakwaterowanie). Wyświetl numer albumu studenta oraz miejsce zamieszkania. Posortuj wynik rosnąco według numeru albumu.
SELECT tStudenci.Nr_albumu, tStudenci.Miejsce_zamieszkania
FROM tStudenci
JOIN tWpłaty ON tStudenci.ID = tWpłaty.ID
LEFT JOIN tZakwaterowanie ON tStudenci.ID = tZakwaterowanie.ID_studenta
WHERE tWpłaty.Za_rok_akad = 2024
AND tZakwaterowanie.ID_studenta IS NULL
ORDER BY tStudenci.Nr_albumu;
Zadanie 2_8
Utwórz zapytanie SQL, które wyświetli wszystkich studentów, którzy zostali zakwaterowani w pokoju jednoosobowym (Liczba_miejsc = 1) w roku akademickim 2024. Wyświetl numer albumu studenta oraz numer pokoju. Posortuj wynik według numeru pokoju.
SELECT tStudenci.Nr_albumu, tPokoje.Numer_pokoju
FROM tStudenci
JOIN tZakwaterowanie ON tStudenci.ID = tZakwaterowanie.ID_studenta
JOIN tPokoje ON tZakwaterowanie.ID_zakwaterowanie = tPokoje.ID_pokoju
WHERE tPokoje.Liczba_miejsc = 1
AND tZakwaterowanie.Rok_akad = 2024
ORDER BY tPokoje.Numer_pokoju;
Zadanie 2_9
Utwórz zapytanie SQL, które wyświetli studentów, którzy zapłacili za zakwaterowanie co najmniej dwa razy w roku akademickim 2024. Wyświetl numer albumu oraz imię i nazwisko studenta. Posortuj wynik według numeru albumu.
SELECT tStudenci.Nr_albumu, tStudenci.Miejsce_zamieszkania
FROM tStudenci
JOIN tWpłaty ON tStudenci.ID = tWpłaty.ID
WHERE tWpłaty.Za_rok_akad = 2024
GROUP BY tStudenci.Nr_albumu, tStudenci.Miejsce_zamieszkania
HAVING COUNT(tWpłaty.ID) >= 2
ORDER BY tStudenci.Nr_albumu;
Zadanie 2_10
Utwórz zapytanie SQL, które wyświetli studentów, którzy zapłacili za zakwaterowanie w różnych pokojach (inny pokój na każdy okres). Wyświetl numer albumu studenta oraz numery pokoi, w których byli zakwaterowani. Posortuj wynik według numeru albumu.
SELECT tStudenci.Nr_albumu, GROUP_CONCAT(DISTINCT tPokoje.Numer_pokoju ORDER BY tPokoje.Numer_pokoju) AS Pokoje
FROM tStudenci
JOIN tZakwaterowanie ON tStudenci.ID = tZakwaterowanie.ID_studenta
JOIN tPokoje ON tZakwaterowanie.ID_zakwaterowanie = tPokoje.ID_pokoju
JOIN tWpłaty ON tZakwaterowanie.ID_studenta = tWpłaty.ID
WHERE tWpłaty.Za_rok_akad = 2024
GROUP BY tStudenci.Nr_albumu
HAVING COUNT(DISTINCT tPokoje.Numer_pokoju) > 1
ORDER BY tStudenci.Nr_albumu;
Ćwiczenia SQL - Poziom Średniozaawansowany
Zadanie 2_11
Utwórz zapytanie SQL, które wyświetli listę studentów (ID i Nr_albumu) oraz kwotę wpłat, którzy zapłacili więcej niż średnia wpłat w roku akademickim 2024. Wykorzystaj subzapytanie oraz połączenie tabel.
SELECT s.ID, s.Nr_albumu, SUM(w.Wpłata) AS Suma_wplat
FROM tStudenci s
JOIN tWpłaty w ON s.ID = w.ID
WHERE w.Za_rok_akad = 2024
GROUP BY s.ID
HAVING SUM(w.Wpłata) > (
SELECT AVG(Wpłata)
FROM tWpłaty
WHERE Za_rok_akad = 2024
);
Zadanie 2_12
Utwórz zapytanie SQL, które wyświetli wszystkich studentów, którzy zapłacili mniej niż średnia kwota wpłat w roku akademickim 2024.
SELECT s.ID, s.Nr_albumu, SUM(w.Wpłata) AS Suma_wplat
FROM tStudenci s
JOIN tWpłaty w ON s.ID = w.ID
WHERE w.Za_rok_akad = 2024
GROUP BY s.ID
HAVING SUM(w.Wpłata) < (
SELECT AVG(Wpłata)
FROM tWpłaty
WHERE Za_rok_akad = 2024
);
Zadanie 2_13
Utwórz zapytanie SQL, które obliczy całkowitą sumę wpłat wszystkich studentów w roku akademickim 2024, używając funkcji agregujących.
SELECT SUM(Wpłata) AS Całkowita_wplata
FROM tWpłaty
WHERE Za_rok_akad = 2024;
Zadanie 2_14
Stwórz procedurę składowaną, która obliczy całkowitą kwotę wpłat dla studenta w danym roku akademickim. Przyjmij jako parametry ID studenta i rok akademicki.
DELIMITER //
CREATE PROCEDURE Oblicz_Wplaty(IN studentID INT, IN rok INT)
BEGIN
SELECT SUM(Wpłata) AS Całkowita_wplata
FROM tWpłaty
WHERE ID = studentID AND Za_rok_akad = rok;
END //
DELIMITER ;
Zadanie 2_15
Stwórz zapytanie SQL, które utworzy indeks na kolumnie 'ID' w tabeli 'tWpłaty' w celu optymalizacji zapytań dotyczących tej kolumny.
CREATE INDEX idx_id ON tWpłaty(ID);