Zadanie 111: Zaawansowane zapytania SELECT w relacji N:M

Wstęp

W Zadaniu 110 poznaliśmy tajniki tworzenia relacji Wiele do Wielu (N:M). Użyliśmy do tego celu studenckiej bazy danych Szkola z tabelami: Studenci, Kursy oraz tabelą łącznikową Zapisy.

Teraz przyszedł czas na praktykę i rozbudowywanie zapytań o przydatne dane biznesowe. Będziemy wykorzystywać nie tylko podstawowe łączenia JOIN, ale także filtrowanie sprzęgnięte z agregacją danych (takich jak sumowanie liczebności za pomocą COUNT, czy też zbijanie rekordów w jedną komórkę przy pomocy GROUP_CONCAT).

Przed rozpoczęciem upewnij się, że korzystasz z wcześniej utworzonej bazy Szkola.

USE Szkola;

Część 1: Identyfikacja Podstaw (Złączenia Wewnętrzne)

Zadanie 1: Pełna lista studentów na zajęciach

Znajdź imiona i nazwiska wszystkich studentów oraz nazwy wszystkich kursów, na które faktycznie przypisali się z pomyślnie. Wykluczamy z zestawienia każdego studenta bez prowadzonych kursów oraz każdy pusty kurs. Posortuj wynik po imieniu studenta rosnąco.

SELECT s.imie, s.nazwisko, k.nazwa
FROM Studenci s
JOIN Zapisy z ON s.id = z.student_id
JOIN Kursy k ON z.kurs_id = k.id
ORDER BY s.imie ASC;

Zadanie 2: Studenci na kursach SQL i Baz Danych

Zawęźmy wynik z poprzedniego zapytania. Odnajdź na listach uczęszczających tylko takich studentów, którzy są zapisani na kurs, w którym w nazwie lub opisie pojawia się w dowolnym miejscu wyraz SQL lub baza danych.

SELECT s.imie, s.nazwisko, k.nazwa
FROM Studenci s
JOIN Zapisy z ON s.id = z.student_id
JOIN Kursy k ON z.kurs_id = k.id
WHERE k.nazwa LIKE '%SQL%' OR k.opis LIKE '%baz danych%';

Zadanie 3: Poszukiwania indeksu '1003'

Dziekanat chce wiedzieć, w ilu zajęciach aktywnie operuje Piotr z pożądanym indeksem 1003. Wylistuj same nazwy kursów uczestnika filtrując złączenie przez kolumnę indeksu z odpowiedniej tabeli bez patrzenia po jego wewnętrznym ID.

SELECT k.nazwa
FROM Studenci s
JOIN Zapisy z ON s.id = z.student_id
JOIN Kursy k ON z.kurs_id = k.id
WHERE s.indeks = 1003;

Część 2: Brakujące Dopasowania (Odchylenia LEFT/RIGHT JOIN)

Zadanie 4: Puste pule Studenkie (LEFT JOIN)

Wyślij w zapytaniu imiona i nazwiska wszystkich studentów uwzględniając nałożone przez relacje przypisane kursy, a jeśli jeszcze pod żaden się nie przypisali - wyświetl wynik NULL w przypisanej mu kolumnie kursu. Posortuj te puste komórki na samą górę tabeli!

SELECT s.imie, s.nazwisko, k.nazwa
FROM Studenci s
LEFT JOIN Zapisy z ON s.id = z.student_id
LEFT JOIN Kursy k ON z.kurs_id = k.id
ORDER BY k.nazwa ASC;

Zadanie 5: Osamotnieni Kursanci ze Zwróceniem Braku Rekordów

Dość ogólników. Teraz wypisz z bazy wyłączenie tych zaginionych/leniwych uczniów, którzy ani razu nie zadeklarowali uczestnictwa w zajęciach. Sprawdźmy kto zasługuje na wydalenie dyscyplinarne, wychwytując moment kiedy tabela Kursu nie dołączyła się z powodzeniem.

SELECT s.imie, s.nazwisko
FROM Studenci s
LEFT JOIN Zapisy z ON s.id = z.student_id
WHERE z.kurs_id IS NULL;

Zadanie 6: Kursy widmo (Bez Przypisań)

To samo od innej strony z lewej perspektywy złączeń uogólnionych. Jakie to kursy edukacyjne nadal po otworzeniu systemu zapisów nie mają dla wyodrębnienia u siebie wpisanej ani jednej duszy na listach słuchaczy w tabeli zapisów?

SELECT k.nazwa
FROM Kursy k
LEFT JOIN Zapisy z ON k.id = z.kurs_id
WHERE z.student_id IS NULL;

Część 3: Agregacja Rekordów i Analizy (GROUP BY i HAVING)

Zadanie 7: Słupki frekwencyjne zajęć

Dyrektor szkoły interesuje się pojemnością klas. Jak wielu realnych słuchaczy uczestniczy fizycznie w wydanych kursach? Podziel licznik frekwencji precyzyjniej wyliczając poszczególny stan przypisań wg. każdego osobnego przedmiotu (użyj GROUP BY).

SELECT k.nazwa AS Kurs, COUNT(z.student_id) AS Liczba_Zapisanych
FROM Kursy k
LEFT JOIN Zapisy z ON k.id = z.kurs_id
GROUP BY k.nazwa;

Zadanie 8: Multi-zadania Studentów

Zrób to w odwrotny sposób - kto jest najbardziej zapracowany? Wypisz każdego zapisanego studenta i matematyczną łączną liczbę kursów (jako nadany Alias), w których obecnie on uczestniczy. Osobom bez zdeklarowań wystarczy, że wypisze cyfrę '0' dzięki konstrukcji grupowanych left join-ów.

SELECT s.imie, s.nazwisko, COUNT(z.kurs_id) AS Liczba_zajęć
FROM Studenci s
LEFT JOIN Zapisy z ON s.id = z.student_id
GROUP BY s.id, s.imie, s.nazwisko;

Zadanie 9: Pilność Powyżej Średniej (HAVING)

Tym razem zwróć od razu nazwiska wyłącznie tych pilnych i uzdolnionych programistów, którzy brali na stół do ukończenia minimum dwa i więcej kursów w jednym semestrze. Odrzuć całą resztę. Pamiętaj, po instrukcji grupującej filtrujemy z wykorzystaniem słowa kluczowego HAVING, a nie typowym z góry postawionym WHERE.

SELECT s.nazwisko, COUNT(z.kurs_id) AS Liczba_zajęć
FROM Studenci s
JOIN Zapisy z ON s.id = z.student_id
GROUP BY s.id, s.nazwisko
HAVING COUNT(z.kurs_id) >= 2;

Zadanie 10: Raport Skondensowanego Formatowania w linii

Szkoła chce wygenerować plik wysyłkowy na jeden arkusz widoku dla portalu, nie chcąc mnożących się ciągle duplikatów imienia w nowych rekordach z powodu JOIN-ów. Zastosuj funkcję bazodanową GROUP_CONCAT(), aby dla każdego id studenta utworzyć pojedynczy rządek zawierający Jego osobiste dane oraz zbitą nowym jednym ciągiem przecinkowym listę String zajęć po odnalezionych w połączonych dotąd złączeniach M:N na podstawie powiązań. Zignoruj tym razem w całości osoby nieaktywne pod zapisami.

SELECT s.imie, s.nazwisko, GROUP_CONCAT(k.nazwa SEPARATOR ', ') AS Grupa_Wymienionych_Kursow
FROM Studenci s
JOIN Zapisy z ON s.id = z.student_id
JOIN Kursy k ON z.kurs_id = k.id
GROUP BY s.id, s.imie, s.nazwisko;

Git Help

git add .
git commit -m "MySQL Zadanie 111: DML 10 Zaawansowych Zapytań złączeniach N:M"