Cztery podjęzyki SQL w praktyce: DDL i DML, złączenia z NULL-ami, grupowanie z HAVING oraz kolejność wykonywania zapytania SELECT.
1.Kolumna nazwisko w tabeli uczniowie zawiera wartości: Kowal, Kowalski, Nowak, Kowalczyk, Sowa. Które nazwiska zwróci zapytanie SELECT nazwisko FROM uczniowie WHERE nazwisko LIKE 'Kowal_%';
- A.Kowal, Kowalski i Kowalczyk
- B.Kowalski i Kowalczyk— poprawna
- C.wyłącznie Kowalski
- D.żadne — zapytanie zwróci pusty wynik
Dlaczego: Podkreślnik zastępuje dokładnie jeden znak, a procent dowolny ciąg, również pusty. Wzorzec żąda więc przedrostka Kowal i po nim co najmniej jednego znaku, dlatego pięcioliterowe Kowal odpada. Najczęstsza pomyłka to potraktowanie podkreślnika jak procentu i dopisanie Kowala do wyniku.
2.Polecenie brzmi: wypisz nazwy przedmiotów, z których wystawiono więcej niż 10 ocen. Które zapytanie realizuje je poprawnie?
- A.SELECT p.nazwa, COUNT(o.id_oceny) AS ile FROM przedmioty AS p INNER JOIN oceny AS o ON p.id_przedmiotu = o.id_przedmiotu WHERE COUNT(o.id_oceny) > 10 GROUP BY p.nazwa;
- B.SELECT p.nazwa, COUNT(o.id_oceny) AS ile FROM przedmioty AS p INNER JOIN oceny AS o ON p.id_przedmiotu = o.id_przedmiotu HAVING COUNT(o.id_oceny) > 10 GROUP BY p.nazwa;
- C.SELECT p.nazwa, COUNT(o.id_oceny) AS ile FROM przedmioty AS p INNER JOIN oceny AS o ON p.id_przedmiotu = o.id_przedmiotu GROUP BY p.nazwa HAVING COUNT(o.id_oceny) > 10;— poprawna
- D.SELECT p.nazwa, COUNT(o.id_oceny) AS ile FROM przedmioty AS p INNER JOIN oceny AS o ON p.id_przedmiotu = o.id_przedmiotu WHERE o.id_oceny > 10 GROUP BY p.nazwa;
Dlaczego: Warunek dotyczy liczby wierszy w grupie, a nie pojedynczego wiersza, więc musi trafić do HAVING wykonywanego po grupowaniu. WHERE działa przed GROUP BY i w tym momencie żaden licznik nie jest jeszcze policzony. Kusi też wariant z warunkiem na identyfikatorze oceny, który filtruje numery wierszy, a nie ich liczbę.
3.Tabela uczniowie ma 30 wierszy, przy czym 8 uczniów ma w kolumnie email wartość NULL. Co zwrócą kolejno zapytania SELECT COUNT(*) FROM uczniowie; oraz SELECT COUNT(email) FROM uczniowie;
- A.30 oraz 22— poprawna
- B.30 oraz 30
- C.22 oraz 22
- D.30 oraz 8
Dlaczego: Licznik z gwiazdką zlicza wiersze niezależnie od tego, co w nich stoi, więc widzi całą tabelę. Licznik z nazwą kolumny pomija wiersze, w których ta kolumna jest pusta, czyli 30 minus 8. Uczniowie najczęściej wybierają dwie identyczne liczby, bo obie postacie wyglądają na to samo zliczanie wierszy.
4.W tabeli uczniowie jest 5 uczniów, a w tabeli oceny 12 ocen należących łącznie do 3 z nich — pozostali dwaj nie mają ani jednej oceny. Ile wierszy zwróci zapytanie SELECT u.nazwisko, o.ocena FROM uczniowie AS u LEFT JOIN oceny AS o ON u.id_ucznia = o.id_ucznia;
- A.5 wierszy
- B.12 wierszy
- C.14 wierszy— poprawna
- D.17 wierszy
Dlaczego: Złączenie zewnętrzne lewostronne zachowuje każdy wiersz tabeli wskazanej we FROM. Trzej uczniowie z ocenami dają tyle wierszy, ile mają ocen, czyli 12, a dwaj pozostali po jednym wierszu z NULL w kolumnie ocena. Odpowiedź 12 kusi, bo dokładnie tyle dałoby złączenie wewnętrzne, które wiersze bez pary wyrzuca.
5.Które zapytanie wypisze nazwiska uczniów, którzy nie mają ani jednej oceny?
- A.SELECT u.nazwisko FROM uczniowie AS u INNER JOIN oceny AS o ON u.id_ucznia = o.id_ucznia WHERE o.ocena IS NULL;
- B.SELECT u.nazwisko FROM uczniowie AS u LEFT JOIN oceny AS o ON u.id_ucznia = o.id_ucznia WHERE o.id_oceny IS NULL;— poprawna
- C.SELECT u.nazwisko FROM uczniowie AS u LEFT JOIN oceny AS o ON u.id_ucznia = o.id_ucznia WHERE o.id_oceny = NULL;
- D.SELECT u.nazwisko FROM uczniowie AS u RIGHT JOIN oceny AS o ON u.id_ucznia = o.id_ucznia WHERE o.id_oceny IS NULL;
Dlaczego: Uczeń bez ocen w ogóle nie powstaje w wyniku złączenia wewnętrznego, więc potrzebne jest złączenie lewostronne, które dołoży dla niego wiersz z pustą prawą stroną. Warunek stawia się na kluczu głównym tabeli oceny, bo ta kolumna nigdy nie bywa pusta w prawdziwym wierszu. Zapis z równa się NULL nigdy nie zwraca prawdy, bo porównanie z NULL daje wynik nieznany.
6.Co się stanie po wykonaniu zapytania SELECT nazwa, liczba_godzin * 30 AS godzin_w_roku FROM przedmioty WHERE godzin_w_roku > 100 ORDER BY godzin_w_roku DESC;
- A.Zapytanie wykona się poprawnie, bo alias jest widoczny we wszystkich klauzulach.
- B.Serwer odrzuci je z powodu aliasu w ORDER BY, gdzie trzeba powtórzyć wyrażenie.
- C.Zapytanie zwróci wszystkie przedmioty, bo warunek z nieznanym aliasem jest pomijany.
- D.Serwer zgłosi błąd nieznanej kolumny w WHERE, bo alias powstaje na etapie SELECT.— poprawna
Dlaczego: Serwer wykonuje klauzule w kolejności FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY, LIMIT. Nazwa nadana słowem AS rodzi się dopiero w piątym kroku, a filtr wierszy działa w drugim, więc w tym momencie taka kolumna jeszcze nie istnieje. Sortowanie odbywa się już po SELECT, dlatego tam ten sam alias jest w pełni poprawny.
7.Tabela oceny zawiera 500 wierszy, a jej licznik AUTO_INCREMENT stoi na wartości 501. Administrator wykonał polecenie TRUNCATE TABLE oceny; Jaki jest stan tabeli po tej operacji?
- A.Tabela nadal istnieje, jest pusta, a licznik AUTO_INCREMENT wraca do wartości 1.— poprawna
- B.Tabela znika razem ze strukturą i trzeba ją odtworzyć poleceniem CREATE TABLE.
- C.Tabela jest pusta, ale licznik AUTO_INCREMENT nadal wskazuje wartość 501.
- D.Polecenie zostanie odrzucone, ponieważ TRUNCATE wymaga podania klauzuli WHERE.
Dlaczego: To polecenie należy do DDL i czyści zawartość tabeli, zostawiając nietkniętą jej definicję oraz zerując licznik automatycznej numeracji. Wariant z zachowanym licznikiem opisuje zachowanie DML-owego DELETE FROM bez warunku, a wariant ze zniknięciem tabeli opisuje DROP TABLE. Ani TRUNCATE, ani DROP nie przyjmują warunku WHERE.
8.Tabela oceny zawiera pary id_ucznia i ocena: (1, 5.0), (1, 3.0), (2, 4.0), (2, 4.0), (3, 2.0). Co zwróci zapytanie SELECT id_ucznia, ROUND(AVG(ocena), 2) AS srednia FROM oceny GROUP BY id_ucznia HAVING COUNT(*) > 1;
- A.Jeden wiersz ze średnią 3.60 policzoną dla całej tabeli oceny.
- B.Trzy wiersze — po jednym dla każdego ucznia obecnego w tabeli.
- C.Pięć wierszy — po jednym dla każdej oceny zapisanej w tabeli.
- D.Dwa wiersze: uczeń 1 ze średnią 4.00 i uczeń 2 ze średnią 4.00.— poprawna
Dlaczego: Grupowanie zbija wiersze według identyfikatora ucznia i liczy średnią osobno w każdej grupie, a warunek za HAVING odrzuca całe grupy liczące jeden wiersz, więc uczeń 3 wypada. Wynik 3.60 to średnia wszystkich pięciu ocen i kusi tych, którzy zapominają, że GROUP BY dzieli tabelę na osobne grupy zamiast liczyć jedną wartość.
9.Które z poniższych zapytań serwer MySQL odrzuci jako błędne?
- A.SELECT id_ucznia, AVG(ocena) FROM oceny GROUP BY id_ucznia HAVING AVG(ocena) > 4;
- B.SELECT id_ucznia, AVG(ocena) FROM oceny WHERE AVG(ocena) > 4 GROUP BY id_ucznia;— poprawna
- C.SELECT id_ucznia, AVG(ocena) FROM oceny WHERE ocena > 1 GROUP BY id_ucznia;
- D.SELECT id_ucznia, COUNT(id_oceny) FROM oceny GROUP BY id_ucznia HAVING COUNT(id_oceny) >= 3;
Dlaczego: Funkcji agregującej nie wolno umieścić w klauzuli filtrującej pojedyncze wiersze, bo działa ona przed grupowaniem, gdy żadna średnia jeszcze nie została policzona — serwer zwraca wtedy komunikat o niepoprawnym użyciu funkcji grupowej. Warunek na zwykłą kolumnę w WHERE jest jak najbardziej poprawny i właśnie dlatego trzeci wariant kusi jako rzekomo błędny.
10.Chcesz wypisać nazwę każdej klasy wraz z liczbą jej uczniów, tak aby klasa, do której nikt nie chodzi, pokazała wartość 0. Które zapytanie to zrobi?
- A.SELECT k.nazwa, COUNT(*) AS ilu FROM klasy AS k LEFT JOIN uczniowie AS u ON k.id_klasy = u.id_klasy GROUP BY k.nazwa;
- B.SELECT k.nazwa, COUNT(u.id_ucznia) AS ilu FROM klasy AS k INNER JOIN uczniowie AS u ON k.id_klasy = u.id_klasy GROUP BY k.nazwa;
- C.SELECT k.nazwa, COUNT(u.id_ucznia) AS ilu FROM klasy AS k LEFT JOIN uczniowie AS u ON k.id_klasy = u.id_klasy GROUP BY k.nazwa;— poprawna
- D.SELECT k.nazwa, COUNT(*) AS ilu FROM klasy AS k INNER JOIN uczniowie AS u ON k.id_klasy = u.id_klasy GROUP BY k.nazwa;
Dlaczego: Potrzebne są dwa elementy naraz: złączenie lewostronne, żeby pusta klasa w ogóle znalazła się w wyniku, oraz zliczanie konkretnej kolumny z tabeli uczniowie, która dla takiej klasy jest pusta. Licznik z gwiazdką policzyłby sztuczny wiersz z NULL-ami i pokazał 1 zamiast 0, a złączenie wewnętrzne usunęłoby pustą klasę z wyniku całkowicie.