Zapytanie SELECT jest jednym z najważniejszych narzędzi w SQL do manipulacji i przetwarzania danych w bazie danych. Dzięki SELECT możemy wydobywać, łączyć, filtrować i agregować informacje z różnych tabel, tworząc cenne raporty i analizy. W tej lekcji omówimy sposób wykorzystania SELECT w połączeniu z funkcjami agregującymi, klauzulą JOIN, warunkami oraz HAVING, a także dodamy sortowanie wyników przy użyciu ORDER BY.
Sortowanie Danych z ORDER BY
Klauzula ORDER BY umożliwia uporządkowanie danych według wybranych kolumn w kolejności rosnącej (domyślnie) lub malejącej. Używamy jej na końcu zapytania SELECT i stosujemy połączenie z ASC lub DESC, aby określić kolejność sortowania.
Przykład:
SELECT Imie, Nazwisko, Wynik
FROM Studenci
ORDER BY Wynik DESC;
Wynik zostanie posortowany malejąco według kolumny Wynik, pokazując najlepsze wyniki na początku.
Tworzenie Tabel
Oto struktura bazy danych, którą będziemy wykorzystywać do ćwiczeń.
-- Tabela Studenci
CREATE TABLE Studenci (
StudentID INT PRIMARY KEY,
Imie VARCHAR(30),
Nazwisko VARCHAR(30),
RokStudiow INT
);
-- Tabela Pracownicy
CREATE TABLE Pracownicy (
PracownikID INT PRIMARY KEY,
Imie VARCHAR(30),
Nazwisko VARCHAR(30),
Stanowisko VARCHAR(50),
DataZatrudnienia DATE
);
-- Tabela Kursy
CREATE TABLE Kursy (
KodKursu VARCHAR(10) PRIMARY KEY,
NazwaKursu VARCHAR(100),
LiczbaGodzin INT,
PracownikID INT,
FOREIGN KEY (PracownikID) REFERENCES Pracownicy(PracownikID)
);
-- Tabela Oceny
CREATE TABLE Oceny (
OcenaID INT PRIMARY KEY,
StudentID INT,
KodKursu VARCHAR(10),
Ocena FLOAT,
FOREIGN KEY (StudentID) REFERENCES Studenci(StudentID),
FOREIGN KEY (KodKursu) REFERENCES Kursy(KodKursu)
);
-- Wstawienia do tabeli Studenci
INSERT INTO Studenci VALUES (1, 'Jan', 'Kowalski', 1);
INSERT INTO Studenci VALUES (2, 'Anna', 'Nowak', 2);
INSERT INTO Studenci VALUES (3, 'Tomasz', 'Wisniewski', 3);
INSERT INTO Studenci VALUES (4, 'Katarzyna', 'Zielinska', 4);
INSERT INTO Studenci VALUES (5, 'Marek', 'Zajac', 2);
INSERT INTO Studenci VALUES (6, 'Paulina', 'Krawczyk', 3);
INSERT INTO Studenci VALUES (7, 'Piotr', 'Lis', 1);
INSERT INTO Studenci VALUES (8, 'Magdalena', 'Bialek', 4);
INSERT INTO Studenci VALUES (9, 'Adrian', 'Nowicki', 3);
INSERT INTO Studenci VALUES (10, 'Julia', 'Mazur', 1);
-- Wstawienia do tabeli Pracownicy
INSERT INTO Pracownicy VALUES (1, 'Adam', 'Kowalski', 'Asystent', '2022-05-10');
INSERT INTO Pracownicy VALUES (2, 'Beata', 'Nowak', 'Wykladowca', '2021-03-15');
INSERT INTO Pracownicy VALUES (3, 'Cezary', 'Wisniewski', 'Asystent', '2019-11-20');
INSERT INTO Pracownicy VALUES (4, 'Daria', 'Zielinska', 'Wykladowca', '2018-09-01');
INSERT INTO Pracownicy VALUES (5, 'Edward', 'Zajac', 'Profesor', '2017-02-22');
INSERT INTO Pracownicy VALUES (6, 'Felicja', 'Krawczyk', 'Doktorant', '2020-07-30');
INSERT INTO Pracownicy VALUES (7, 'Grzegorz', 'Lis', 'Profesor', '2016-12-10');
INSERT INTO Pracownicy VALUES (8, 'Hanna', 'Bialek', 'Doktorant', '2022-01-05');
INSERT INTO Pracownicy VALUES (9, 'Ireneusz', 'Nowicki', 'Wykladowca', '2020-10-01');
INSERT INTO Pracownicy VALUES (10, 'Julia', 'Mazur', 'Asystent', '2021-05-15');
-- Wstawienia do tabeli Kursy (dodano PracownikID, aby połączyć kursy z prowadzącymi)
INSERT INTO Kursy VALUES ('CS101', 'Programowanie', 40, 5);
INSERT INTO Kursy VALUES ('CS102', 'Bazy danych', 30, 2);
INSERT INTO Kursy VALUES ('CS103', 'Statystyka', 35, 4);
INSERT INTO Kursy VALUES ('CS104', 'Sieci komputerowe', 45, 7);
INSERT INTO Kursy VALUES ('CS105', 'Matematyka', 50, 5);
INSERT INTO Kursy VALUES ('CS106', 'Algorytmy', 55, 6);
INSERT INTO Kursy VALUES ('CS107', 'Grafika komputerowa', 25, 9);
INSERT INTO Kursy VALUES ('CS108', 'Sztuczna inteligencja', 40, 10);
INSERT INTO Kursy VALUES ('CS109', 'Inzynieria oprogramowania', 50, 3);
INSERT INTO Kursy VALUES ('CS110', 'Systemy operacyjne', 30, 1);
-- Wstawienia do tabeli Oceny
INSERT INTO Oceny VALUES (1, 1, 'CS101', 4.5);
INSERT INTO Oceny VALUES (2, 1, 'CS102', 3.8);
INSERT INTO Oceny VALUES (3, 2, 'CS101', 5.0);
INSERT INTO Oceny VALUES (4, 2, 'CS103', 4.2);
INSERT INTO Oceny VALUES (5, 3, 'CS104', 2.5);
INSERT INTO Oceny VALUES (6, 4, 'CS102', 3.0);
INSERT INTO Oceny VALUES (7, 5, 'CS105', 4.0);
INSERT INTO Oceny VALUES (8, 6, 'CS106', 4.6);
INSERT INTO Oceny VALUES (9, 7, 'CS107', 3.7);
INSERT INTO Oceny VALUES (10, 8, 'CS108', 5.0);
Ćwiczenia
Ćwiczenie 1: Podstawowe Łączenie Tabel
Zadanie 1: Wyświetl imię, nazwisko i rok studiów studentów, którzy zapisali się na kursy (użyj odpowiedniego JOINA).
Zadanie 2: Wyświetl wszystkie kursy i studentów, którzy na nie uczęszczają, sortując wyniki malejąco według nazwy kursu.
Ćwiczenie 2: LEFT JOIN
Zadanie 1: Wyświetl wszystkich studentów oraz kursy, na które są zapisani (lub NULL dla tych, którzy nie są zapisani na żaden kurs), używając LEFT JOIN.
Zadanie 2: Wyświetl wszystkie kursy oraz przypisanych do nich studentów, w tym kursy bez żadnych uczestników (LEFT JOIN).
Ćwiczenie 3: Grupowanie i Funkcje Agregujące
Zadanie 1: Znajdź średnią ocenę każdego studenta, wyświetl jego imie i nazwisko, skorzystaj z GROUP BY i funkcji AVG().
Zadanie 2: Oblicz liczbę kursów prowadzonych przez poszczególnych pracowników, sortując wyniki malejąco według liczby kursów.
Ćwiczenie 4: Grupowanie z HAVING
Zadanie 1: Wyświetl studentów, którzy mają średnią ocenę powyżej 4, używając HAVING do filtrowania grupy.
Zadanie 2: Znajdź kursy, na których średnia ocena wynosi poniżej 3, również używając HAVING.
Ćwiczenie 5: Filtrowanie z Klauzulą WHERE
Zadanie 1: Pokaż wszystkie oceny z kursu Programowanie dla studentów drugiego roku.
Zadanie 2: Wyświetl pracowników zatrudnionych przed 2021 rokiem na stanowisku Asystent lub Doktorant (możesz użyć nawiasów).
Ćwiczenie 6: Zaawansowane Łączenie i Agregacja
Zadanie 1: Wyświetl średnią ocenę na każdym kursie oraz liczbę studentów, którzy są zpisani na każdy kurs, sortując kursy według średniej oceny malejąco.
Zadanie 2: Znajdź pracowników i studentów powiązanych z danym kursem, a wyniki posortuj według nazwiska pracownika.
Ćwiczenie 7: Łączenie i Filtrowanie z Klauzulą WHERE
Zadanie 1: Wyświetl studentów, którzy zapisali się na kursy prowadzone przez pracowników na stanowisku Profesor, pokazując imię i nazwisko studenta, nazwę kursu oraz nazwisko prowadzącego.
Zadanie 2: Pokaż wszystkich studentów, którzy są na pierwszym lub drugim roku i zapisali się na kursy trwające co najmniej 40 godzin.
Ćwiczenie 8: Zaawansowana Agregacja i Łączenie Tabel
Zadanie 1: Oblicz łączną liczbę godzin wszystkich kursów, na które zapisany jest każdy student, sortując wyniki malejąco według sumy godzin. Wyświetl ID studenta, imię, nazwisko oraz łączną liczbę godzin.
Zadanie 2: Wyświetl liczbę studentów, którzy sa zapisani na kursy, wyświetlając tylko kursy, które miały co najmniej dwóch lub żadnego uczestnika (użyj HAVING i COUNT()).