Indeks Uczelni — model bazy danych

SELECT u.nazwa,
       CONCAT(a.ulica, ' ', a.nr_domu, COALESCE(CONCAT('/', a.nr_lokalu), ''), ', ',
              a.kod_pocztowy, ' ', a.miejscowosc)          AS adres,
       u.telefon, u.email, u.www,
       COALESCE(w.nazwa, 'Rektorat')                       AS jednostka,
       s.nazwa                                             AS sekretariat,
       s.telefon                                           AS tel_sekretariatu,
       s.email                                             AS email_sekretariatu,
       s.nr_pokoju, s.godziny_otwarcia
FROM uczelnia u
JOIN adres a            ON a.id = u.adres_id
JOIN sekretariat s      ON s.uczelnia_id = u.id
LEFT JOIN wydzial w     ON w.id = s.wydzial_id
ORDER BY s.wydzial_id IS NOT NULL, w.nazwa;

SELECT f.funkcja,
       COALESCE(w.nazwa, '— cała uczelnia —')              AS jednostka,
       CONCAT(p.tytul_naukowy, ' ', p.imie, ' ', p.nazwisko) AS osoba,
       p.email, f.data_od
FROM funkcja_kierownicza f
JOIN pracownik p   ON p.id = f.pracownik_id
LEFT JOIN wydzial w ON w.id = f.wydzial_id
WHERE f.data_od <= CURDATE()
  AND (f.data_do IS NULL OR f.data_do >= CURDATE())
ORDER BY FIELD(f.funkcja, 'REKTOR', 'PROREKTOR', 'KANCLERZ', 'DZIEKAN', 'PRODZIEKAN'), w.nazwa;

SELECT f.funkcja, CONCAT(p.imie, ' ', p.nazwisko) AS osoba, f.data_od,
       COALESCE(f.data_do, 'trwa')                AS data_do
FROM funkcja_kierownicza f
JOIN pracownik p ON p.id = f.pracownik_id
WHERE f.wydzial_id = 1                                     -- parametr: wydział
ORDER BY f.data_od DESC;

SELECT p.id, p.tytul_naukowy, p.imie, p.nazwisko, p.email, p.telefon,
       p.data_zatrudnienia,
       TIMESTAMPDIFF(YEAR, p.data_zatrudnienia, CURDATE()) AS staz_lat
FROM pracownik p
WHERE p.data_zwolnienia IS NULL OR p.data_zwolnienia > CURDATE()
ORDER BY staz_lat DESC, p.nazwisko;

SELECT p.imie, p.nazwisko, p.data_zatrudnienia, p.data_zwolnienia
FROM pracownik p
WHERE p.data_zwolnienia <= CURDATE()
ORDER BY p.data_zwolnienia DESC;

SELECT p.nazwisko, p.imie, p.pesel, p.nr_konta,
       CONCAT(a.ulica, ' ', a.nr_domu, COALESCE(CONCAT('/', a.nr_lokalu), ''), ', ',
              a.kod_pocztowy, ' ', a.miejscowosc) AS adres_zamieszkania
FROM pracownik p
JOIN adres a ON a.id = p.adres_id
WHERE p.data_zwolnienia IS NULL OR p.data_zwolnienia > CURDATE()
ORDER BY p.nazwisko, p.imie;

SELECT CONCAT(p.imie, ' ', p.nazwisko)                         AS wykladowca,
       COUNT(*)                                                AS liczba_zajec,
       GROUP_CONCAT(CONCAT(pr.nazwa, ' (', g.nazwa, ')')
                    ORDER BY pr.nazwa SEPARATOR ', ')          AS zajecia
FROM zajecia z
JOIN pracownik p  ON p.id  = z.pracownik_id
JOIN przedmiot pr ON pr.id = z.przedmiot_id
JOIN grupa g      ON g.id  = z.grupa_id
JOIN semestr s    ON s.id  = z.semestr_id
WHERE CURDATE() BETWEEN s.data_od AND s.data_do
GROUP BY p.id, p.imie, p.nazwisko
ORDER BY liczba_zajec DESC, wykladowca;

SELECT CONCAT(p.imie, ' ', p.nazwisko) AS wykladowca,
       COUNT(DISTINCT z.id)             AS zajecia,
       COUNT(DISTINCT z.przedmiot_id)   AS rozne_przedmioty,
       COUNT(DISTINCT z.grupa_id)       AS grupy,
       COUNT(st.id)                     AS studentow_na_zajeciach
FROM pracownik p
JOIN zajecia z  ON z.pracownik_id = p.id
JOIN semestr s  ON s.id = z.semestr_id
JOIN student st ON st.grupa_id = z.grupa_id
WHERE s.rok_akademicki = '2025/2026'                       -- parametr
GROUP BY p.id, p.imie, p.nazwisko
ORDER BY zajecia DESC;

SELECT p.imie, p.nazwisko, p.email
FROM pracownik p
WHERE (p.data_zwolnienia IS NULL OR p.data_zwolnienia > CURDATE())
  AND NOT EXISTS (
        SELECT 1
        FROM zajecia z
        JOIN semestr s ON s.id = z.semestr_id
        WHERE z.pracownik_id = p.id
          AND CURDATE() BETWEEN s.data_od AND s.data_do);

SELECT g.nazwa, g.rocznik, w.skrot AS wydzial,
       CONCAT(p.tytul_naukowy, ' ', p.imie, ' ', p.nazwisko) AS opiekun,
       COUNT(st.id) AS liczba_studentow
FROM grupa g
JOIN wydzial w     ON w.id = g.wydzial_id
JOIN pracownik p   ON p.id = g.opiekun_id
LEFT JOIN student st ON st.grupa_id = g.id
GROUP BY g.id, g.nazwa, g.rocznik, w.skrot, p.tytul_naukowy, p.imie, p.nazwisko
ORDER BY g.rocznik, g.nazwa;

SELECT st.nr_albumu, st.nazwisko, st.imie,
       TIMESTAMPDIFF(YEAR, st.data_urodzenia, CURDATE()) AS wiek
FROM student st
JOIN grupa g ON g.id = st.grupa_id
WHERE g.nazwa = 'INF-1A'                                   -- parametr
ORDER BY st.nazwisko, st.imie;

SELECT st.id, st.nr_albumu, st.imie, st.nazwisko, st.pesel, g.nazwa AS grupa
FROM student st
JOIN grupa g ON g.id = st.grupa_id
WHERE st.nazwisko LIKE 'Now%'                              -- parametr
   OR st.nr_albumu = '251001'                              -- parametr
   OR st.pesel     = '00000000000';                        -- parametr

SELECT pr.kod, pr.nazwa AS przedmiot, pr.punkty_ects,
       CONCAT(p.tytul_naukowy, ' ', p.imie, ' ', p.nazwisko) AS prowadzacy
FROM zajecia z
JOIN przedmiot pr ON pr.id = z.przedmiot_id
JOIN pracownik p  ON p.id  = z.pracownik_id
JOIN grupa g      ON g.id  = z.grupa_id
JOIN semestr s    ON s.id  = z.semestr_id
WHERE g.nazwa = 'INF-1A'                                   -- parametr
  AND CURDATE() BETWEEN s.data_od AND s.data_do
ORDER BY pr.nazwa;

SELECT oc.data_wystawienia, oc.opis, oc.wartosc
FROM ocena_czastkowa oc
JOIN zajecia z    ON z.id  = oc.zajecia_id
JOIN przedmiot pr ON pr.id = z.przedmiot_id
WHERE oc.student_id = 1                                    -- parametr
  AND pr.kod = 'INF-BD'                                    -- parametr
  AND z.semestr_id = 2                                     -- parametr
ORDER BY oc.data_wystawienia;

--     z każdego przedmiotu (widok prowadzącego)
SELECT pr.nazwa AS przedmiot, st.nazwisko, st.imie,
       COUNT(oc.id)               AS liczba_ocen,
       ROUND(AVG(oc.wartosc), 2)  AS srednia_czastkowych,
       GROUP_CONCAT(oc.wartosc ORDER BY oc.data_wystawienia SEPARATOR ' | ') AS oceny
FROM zajecia z
JOIN przedmiot pr ON pr.id = z.przedmiot_id
JOIN student st   ON st.grupa_id = z.grupa_id
LEFT JOIN ocena_czastkowa oc ON oc.zajecia_id = z.id AND oc.student_id = st.id
WHERE z.grupa_id = 1                                       -- parametr
  AND z.semestr_id = 2                                     -- parametr
GROUP BY pr.nazwa, st.id, st.nazwisko, st.imie
ORDER BY pr.nazwa, st.nazwisko;

SELECT s.rok_akademicki, s.typ AS semestr, pr.nazwa AS przedmiot, pr.punkty_ects,
       ok.wartosc AS ocena_koncowa, ok.data_wystawienia,
       CONCAT(p.imie, ' ', p.nazwisko) AS wystawil_prowadzacy
FROM ocena_koncowa ok
JOIN zajecia z    ON z.id  = ok.zajecia_id
JOIN semestr s    ON s.id  = z.semestr_id
JOIN przedmiot pr ON pr.id = z.przedmiot_id
JOIN pracownik p  ON p.id  = z.pracownik_id
WHERE ok.student_id = 1                                    -- parametr
ORDER BY s.data_od, pr.nazwa;

--     (ECTS zaliczone liczone tylko z ocen pozytywnych)
SELECT s.rok_akademicki, s.typ, g.nazwa AS grupa, st.nr_albumu, st.nazwisko, st.imie,
       ROUND(SUM(ok.wartosc * pr.punkty_ects) / SUM(pr.punkty_ects), 2) AS srednia_wazona,
       SUM(CASE WHEN ok.wartosc >= 3.0 THEN pr.punkty_ects ELSE 0 END)  AS ects_zaliczone
FROM ocena_koncowa ok
JOIN zajecia z    ON z.id  = ok.zajecia_id
JOIN semestr s    ON s.id  = z.semestr_id
JOIN przedmiot pr ON pr.id = z.przedmiot_id
JOIN student st   ON st.id = ok.student_id
JOIN grupa g      ON g.id  = st.grupa_id
WHERE s.id = 2                                             -- parametr: semestr
GROUP BY s.rok_akademicki, s.typ, g.nazwa, st.id, st.nr_albumu, st.nazwisko, st.imie
ORDER BY g.nazwa, srednia_wazona DESC;

SELECT grupa, nr_albumu, nazwisko, imie, srednia,
       RANK() OVER (PARTITION BY grupa ORDER BY srednia DESC) AS miejsce_w_grupie
FROM (
    SELECT g.nazwa AS grupa, st.nr_albumu, st.nazwisko, st.imie,
           ROUND(SUM(ok.wartosc * pr.punkty_ects) / SUM(pr.punkty_ects), 2) AS srednia
    FROM ocena_koncowa ok
    JOIN zajecia z    ON z.id  = ok.zajecia_id
    JOIN przedmiot pr ON pr.id = z.przedmiot_id
    JOIN student st   ON st.id = ok.student_id
    JOIN grupa g      ON g.id  = st.grupa_id
    JOIN semestr s    ON s.id  = z.semestr_id
    WHERE s.rok_akademicki = '2025/2026'                   -- parametr
    GROUP BY g.nazwa, st.id, st.nr_albumu, st.nazwisko, st.imie
) AS srednie
ORDER BY grupa, miejsce_w_grupie;

SELECT s.rok_akademicki, s.typ, g.nazwa AS grupa, st.nr_albumu, st.nazwisko, st.imie,
       pr.nazwa AS przedmiot, CONCAT(p.imie, ' ', p.nazwisko) AS prowadzacy
FROM ocena_koncowa ok
JOIN zajecia z    ON z.id  = ok.zajecia_id
JOIN semestr s    ON s.id  = z.semestr_id
JOIN przedmiot pr ON pr.id = z.przedmiot_id
JOIN pracownik p  ON p.id  = z.pracownik_id
JOIN student st   ON st.id = ok.student_id
JOIN grupa g      ON g.id  = st.grupa_id
WHERE ok.wartosc = 2.0
ORDER BY s.data_od, g.nazwa, st.nazwisko;

SELECT s.rok_akademicki, s.typ, g.nazwa AS grupa, pr.nazwa AS przedmiot,
       st.nr_albumu, st.nazwisko, st.imie, CONCAT(p.imie, ' ', p.nazwisko) AS prowadzacy
FROM zajecia z
JOIN semestr s    ON s.id  = z.semestr_id
JOIN przedmiot pr ON pr.id = z.przedmiot_id
JOIN pracownik p  ON p.id  = z.pracownik_id
JOIN grupa g      ON g.id  = z.grupa_id
JOIN student st   ON st.grupa_id = z.grupa_id
LEFT JOIN ocena_koncowa ok ON ok.zajecia_id = z.id AND ok.student_id = st.id
WHERE s.data_do < CURDATE()
  AND ok.id IS NULL;

SELECT pr.nazwa AS przedmiot, s.rok_akademicki, s.typ,
       COUNT(*)                                          AS ocen,
       ROUND(AVG(ok.wartosc), 2)                         AS srednia,
       SUM(ok.wartosc = 5.0)                             AS bdb,
       SUM(ok.wartosc IN (4.0, 4.5))                     AS db,
       SUM(ok.wartosc IN (3.0, 3.5))                     AS dst,
       SUM(ok.wartosc = 2.0)                             AS ndst,
       ROUND(100 * AVG(ok.wartosc >= 3.0), 1)            AS zdawalnosc_proc
FROM ocena_koncowa ok
JOIN zajecia z    ON z.id  = ok.zajecia_id
JOIN przedmiot pr ON pr.id = z.przedmiot_id
JOIN semestr s    ON s.id  = z.semestr_id
GROUP BY pr.id, pr.nazwa, s.id, s.rok_akademicki, s.typ
ORDER BY s.id, zdawalnosc_proc, pr.nazwa;

SELECT st.nazwisko, st.imie, pr.nazwa AS przedmiot, s.rok_akademicki, s.typ,
       ROUND(AVG(oc.wartosc), 2) AS srednia_czastkowych,
       ok.wartosc                AS ocena_koncowa
FROM ocena_koncowa ok
JOIN zajecia z           ON z.id  = ok.zajecia_id
JOIN semestr s           ON s.id  = z.semestr_id
JOIN przedmiot pr        ON pr.id = z.przedmiot_id
JOIN student st          ON st.id = ok.student_id
JOIN ocena_czastkowa oc  ON oc.zajecia_id = ok.zajecia_id AND oc.student_id = ok.student_id
GROUP BY ok.id, st.nazwisko, st.imie, pr.nazwa, s.rok_akademicki, s.typ, ok.wartosc
HAVING ABS(AVG(oc.wartosc) - ok.wartosc) >= 0.5
ORDER BY ABS(AVG(oc.wartosc) - ok.wartosc) DESC;

SELECT g.nazwa AS grupa, pr.nazwa AS przedmiot, st.nr_albumu, st.nazwisko, st.imie
FROM zajecia z
JOIN semestr s    ON s.id  = z.semestr_id
JOIN przedmiot pr ON pr.id = z.przedmiot_id
JOIN grupa g      ON g.id  = z.grupa_id
JOIN student st   ON st.grupa_id = z.grupa_id
WHERE CURDATE() BETWEEN s.data_od AND s.data_do
  AND NOT EXISTS (SELECT 1 FROM ocena_czastkowa oc
                  WHERE oc.zajecia_id = z.id AND oc.student_id = st.id)
ORDER BY g.nazwa, pr.nazwa, st.nazwisko;

SELECT 'cząstkowa' AS rodzaj, oc.id, oc.student_id, oc.zajecia_id
FROM ocena_czastkowa oc
JOIN zajecia z  ON z.id = oc.zajecia_id
JOIN student st ON st.id = oc.student_id
WHERE st.grupa_id <> z.grupa_id
UNION ALL
SELECT 'końcowa', ok.id, ok.student_id, ok.zajecia_id
FROM ocena_koncowa ok
JOIN zajecia z  ON z.id = ok.zajecia_id
JOIN student st ON st.id = ok.student_id
WHERE st.grupa_id <> z.grupa_id;

SELECT z.id, CONCAT(p.imie, ' ', p.nazwisko) AS wykladowca, p.data_zwolnienia,
       s.rok_akademicki, s.typ
FROM zajecia z
JOIN pracownik p ON p.id = z.pracownik_id
JOIN semestr s   ON s.id = z.semestr_id
WHERE p.data_zwolnienia < s.data_od;

INSERT INTO ocena_czastkowa (zajecia_id, student_id, wartosc, opis, data_wystawienia)
VALUES (24, 1, 4.5, 'kolokwium 1', CURDATE());              -- parametr

UPDATE student SET grupa_id = 2 WHERE nr_albumu = '251001'; -- parametr

UPDATE pracownik SET data_zwolnienia = '2027-01-31' WHERE id = 6;  -- parametr