Zajęcia -Tomasz Sadowski

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()).

Sortowanie Danych z ORDER BY