SQL DQL - ćwiczenia w zapytaniach, funkcjach agregujących i złączeniach

Bazując na bazie testowej Sakila (tutaj zip), wykonaj następujące ćwiczenia. Ułatwieniem może być widok schematu relacji tej bazy. Data ostatniej aktualizacji list: 07.10.2024.

Lista 1: zapytania na jednej tabeli

  1. Podaj tytuły, opisy, czas wypożyczenia, koszt wypożyczenia za dzień i całkowity koszt wypożyczenia (jako iloczyn czasu wypożyczenia i jego kosztu) dla każdego filmu. Przyjmij, że pole rental_rate zawiera koszt wypożyczenia za dzień (według dokumentacji bazy jest to koszt wypożyczenia na tyle dni, ile zapisano w polu rental_duration).
    • Wersja zgodna z dokumentacją. Podaj tytuły, opisy, czas wypożyczenia oraz koszt wypożyczenia za dzień (jako iloraz kosztu wypożyczenia na przewidzianą liczbę dni) dla każdego filmu.
  2. Podaj alfabetyczną listę nazwisk aktorów, każde nazwisko jeden raz.
  3. Wypisz wszystkie rodzaje ratingów filmów (każdy rating jednokrotnie).
  4. Wypisz tytuły, opisy, ratingi i długość filmów, które trwają 3 godziny lub dłużej.
    1. Zmodyfikuj powyższe zapytanie tak, aby czas był wyświetlany w godzinach (np. 75 minut = 1,25 godz.).
    2. Zmodyfikuj powyższe zapytanie tak, aby czas był wyświetlany w godzinach z dokładnością do drugiego miejsca po przecinku (np. 75 minut = 1,25 godz.).
    3. Zmodyfikuj powyższe zapytanie tak, aby kolumna z długością filmu w godzinach miała nazwę (alias) length_hours.
  5. Wypisz wszystkie informacje z tabeli customer o tych klientach, którzy są nieaktywni albo ich nazwisko kończy się na M.
  6. Wypisz wszystkie kategorie, których identyfikator jest większy niż 4, a których nazwa zaczyna się na C, S lub T. Użyj klauzuli WHERE.
    • Wykonaj powyższe ćwiczenie, używając operatora IN do określenia zbioru liter C, S, T.
      Uwaga. Trzeba użyć pewnej funkcji na tekstach.
  7. Wypisz wszystkie filmy z ratingiem G, PG-13 lub NC-17. Użyj operatora IN.
  8. Wypisz listę otwartych wypożyczeń (czyli wypożyczono film, ale jeszcze go nie zwrócono).
  9. Wypisz wypożyczenia, które miały miejsce między 25 a 26 maja 2005 r. Skorzystaj z operatora BETWEEN. Zwróć uwagę, że w polu rental_date przechowywana jest zarówno data, jak i godzina (typ danych to DATETIME).
  10. Wypisz listę dziesięciu ostatnio wypożyczonych filmów.
Czytaj dalej

Powtórzenie wiadomości

1. Wypisz: region, ilość pracowników, uwzględnij tylko te regiony, gdzie ilość pracowników jest większa niż 5. Posortuj malejąco wg ilości pracowników.

1-1

SELECT R.name AS nazwaRegionu, COUNT(E.id) AS iloscPracownikow
FROM emp E JOIN dept D ON D.id = E.dept_id
RIGHT JOIN region R on R.id = D.region_id
GROUP BY R.name
HAVING COUNT(*) > 5
ORDER BY COUNT(E.id) DESC;

 

2. Wypisz: tytuł zawodowy, ilość pracowników, średnie wynagrodzenie, uwzględnić tylko tych, których średnie wynagrodzenie jest większe niż $700. Dane posortować wg tych tytułów zawodowych.

1-2

SELECT E.title as tytulZawodowy, COUNT(*) AS iloscPracownikow, AVG(salary) as srednieWynagrodzenie
FROM emp E
GROUP BY E.title
HAVING AVG(salary) > 700
ORDER BY title;

 

3. Wyświetlić imię i nazwisko pracownika (konkatenacją), imię i nazwisko jego managera. Zastosuj aliasy. Posortuj wg nazwisk i imion pracowników. Uwzględnić także tych pracowników, którzy nie mają managera.

1-3

SELECT CONCAT(Ez.first_name, ' ', Ez.last_name) AS pracownik, CONCAT(Ew.first_name, ' ', Ew.last_name) AS manager
FROM emp Ez LEFT JOIN emp Ew ON Ez.manager_id=Ew.id
ORDER BY Ez.last_name, Ez.first_name;

 

4. Pobierz z bazy danych: imię i nazwisko przedstawiciela handlowego, kraj i ilość klientów w tym kraju. Posortuj wg nazwiska i imienia pracownika oraz ilości klientów w danym państwie.

SELECT CONCAT(E.id, ' ', E.first_name, ' ', E.last_name) AS przedstawicielHandlowy, C.country AS kraj, COUNT(C.id) AS iloscKlientow
FROM emp E RIGHT JOIN customer C ON E.id = C.sales_rep_id
GROUP BY E.id, C.country
ORDER BY E.last_name, E.first_name, COUNT(C.id) DESC;

1-45. Zwiększyć o 5% cenę produktu, którego sprzedaż była największa.