Bazy danych I: ćwiczenia z języka SQL

Rys.1. Struktura relacji do ćwiczeń

Rys. 2. Zawartość relacji do ćwiczeń

Selekcja i projekcja

1. Wyświetl tytuły filmów wyprodukowanych w roku 1998 lub w roku 1999.

tytul

--------------------

Ronin

Depresja gangstera

(2 rows)

2. Wyświetl tytuły i ceny wypożyczenia filmów, których cena wypożyczenia przekracza 9 zł. Wynik uporządkuj rosnąco według ceny wypożyczenia.

tytul | cena

--------------------+------

Ronin | 9.5

Wesele | 10

Depresja gangstera | 10.5

(3 rows)

3. Wyświetl nazwiska wszystkich klientów o imieniu `Jan'.

nazwisko

-----------

Kowalski

Glinnicki

(2 rows)

4. Wyświetl imiona i nazwiska wszystkich klientów, których imię liczy więcej znaków niż nazwisko.

imie | nazwisko

-------+----------

Pawel | Lis

(1 row)

5. Wyświetl nazwiska wszystkich aktorów o imionach: `Arnold', 'Tom' i `Jodie'. Wynik uporządkuj malejąco według nazwisk.

nazwisko

----------------

Berenger

Cruise

Foster

Schwarzenegger

(4 rows)

6. Wyświetl identyfikatory wszystkich filmów, których kopie są aktualnie dostępne w wypożyczalni. Wyeliminuj duplikaty. Wynik uporządkuj rosnąco w kolejności identyfikatorów filmów.

id_filmu

----------

1

2

3

4

6

7

9

10

(8 rows)

7. Wyświetl identyfikatory wszystkich kopii, które zostały wypożyczone pomiędzy `2005-07-15' a `2005-07-22'. Wyeliminuj duplikaty. Wynik uporządkuj rosnąco w kolejności identyfikatorów kopii.

id_kopii

----------

3

6

20

(3 rows)

8. Wyświetl identyfikatory i czas trwania wypożyczenia (w dniach) wszystkich kopii, które zostały wypożyczone na okres dłuższy niż 1 dzień.

id_kopii | czas

----------+------

6 | 3

12 | 3

13 | 4

11 | 3

(4 rows)

9. Wyświetl dane wszystkich aktorów według następującego formatu: pierwsza litera imienia, kropka, spacja, nazwisko.

aktor

-------------------

A. Schwarzenegger

T. Arciuch

P. Wilczak

M. Stuhr

R. Tymon Tymanski

J. Rogalski

L. Dyblik

M. Dziedziel

M. Biehn

L. Hamilton

B. Murray

D. Aykroyd

S. Weaver

R. De Niro

J. Foster

H. Keitel

C. Shepherd

T. Berenger

W. Dafoe

C. Sheen

H. Ford

E. Seigner

J. Reno

B. Crystal

L. Kudrow

G. Oldman

N. Portman

T. Cruise

(28 rows)

10. Wyświetl tytuły wszystkich filmów uporządkowane w kolejności od najkrótszego do najdłuższego tytułu.

tytul

--------------------

Ronin

Pluton

Wesele

Frantic

Terminator

Taksowkarz

Ghostbusters

Leon zawodowiec

Mission Impossible

Depresja gangstera

(10 rows)

11. Wyświetl tytuły i ceny wypożyczenia trzech najnowszych filmów.

tytul | cena

--------------------+------

Wesele | 10

Depresja gangstera | 10.5

Ronin | 9.5

(3 rows)

12. Dla każdego klienta wyświetl: pełne imię, pierwszą literę imienia, ostatnią literę imienia. Nagłówki kolumn powinny posiadać takie brzmienie, jak podano poniżej.

imie | pierwsza | ostatnia

--------+----------+----------

Jan | J | n

Anna | A | a

Arnold | A | d

Pawel | P | l

Maciej | M | j

Jan | J | n

(6 rows)

13. Wyświetl te imiona klientów, których pierwsza i ostatnia litera imienia są identyczne. Ignoruj wielkość porównywanych znaków. Wyeliminuj duplikaty.

imie

------

Anna

(1 row)

14. Wyświetl tytuły filmów, których przedostatnia litera tytułu to `o'.

tytul

------------

Terminator

Pluton

(2 rows)

15. Dla każdego klienta wyświetl jego adres e-mail skonstruowany w następujący sposób: imię małymi literami, kropka, nazwisko małymi literami, `@wsb.pl'.

email

-----------------------------

[e-mail]

[e-mail]

[e-mail]

[e-mail]

[e-mail]

[e-mail]

(6 rows)

Połączenia

16. Dla każdej kopii wyświetl jej identyfikator i tytuł zapisanego na niej filmu. Wynik uporządkuj według identyfikatorów kopii.

id_kopii | tytul

----------+--------------------

1 | Wesele

2 | Wesele

3 | Ghostbusters

4 | Terminator

5 | Terminator

6 | Terminator

7 | Taksowkarz

8 | Pluton

9 | Frantic

10 | Frantic

11 | Frantic

12 | Ronin

13 | Ronin

14 | Depresja gangstera

15 | Leon zawodowiec

16 | Mission Impossible

17 | Mission Impossible

18 | Mission Impossible

19 | Mission Impossible

20 | Mission Impossible

(20 rows)

17. Wyświetl tytuły wszystkich filmów, których kopie są aktualnie dostępne w wypożyczalni. Wyeliminuj duplikaty.

tytul

--------------------

Frantic

Ghostbusters

Leon zawodowiec

Mission Impossible

Ronin

Taksowkarz

Terminator

Wesele

(8 rows)

18. Wyświetl identyfikatory kopii zawierających filmy wyprodukowane w roku 1984.

id_kopii

----------

3

4

5

6

(4 rows)

19. Dla każdego wypożyczenia wyświetl datę wypożyczenia, datę zwrotu oraz nazwisko klienta, który dokonał wypożyczenia.

data_wypozyczenia | data_zwrotu | nazwisko

-------------------+-------------+---------------

2005-07-19 | 2005-07-22 | Kowalski

2005-07-04 | 2005-07-05 | Kowalski

2005-07-26 | 2005-07-27 | Nowak

2005-07-29 | 2005-07-30 | Nowak

2005-07-24 | 2005-07-25 | Nowak

2005-07-10 | 2005-07-13 | Zielinski

2005-07-16 | 2005-07-17 | Zielinski

2005-07-22 | 2005-07-23 | Zielinski

2005-07-24 | 2005-07-25 | Zielinski

2005-07-01 | 2005-07-05 | Lis

2005-07-10 | 2005-07-13 | Wojciechowski

2005-07-29 | 2005-07-30 | Glinnicki

2005-07-29 | 2005-07-30 | Glinnicki

2005-07-06 | 2005-07-07 | Glinnicki

(14 rows)

20. Dla każdego wypożyczenia wyświetl nazwisko klienta, który dokonał wypożyczenia oraz tytuł wypożyczonego filmu.

nazwisko | tytul

---------------+--------------------

Kowalski | Wesele

Glinnicki | Wesele

Zielinski | Ghostbusters

Nowak | Ghostbusters

Nowak | Terminator

Kowalski | Terminator

Nowak | Taksowkarz

Glinnicki | Taksowkarz

Zielinski | Taksowkarz

Wojciechowski | Frantic

Zielinski | Ronin

Lis | Ronin

Glinnicki | Mission Impossible

Zielinski | Mission Impossible

(14 rows)

21. Wyświetl tytuły i lata produkcji wszystkich filmów wypożyczonych przez klienta o nazwisku `Kowalski'.

tytul | rok_produkcji

------------+---------------

Wesele | 2004

Terminator | 1984

(2 rows)

22. Wyświetl nazwisko klienta, który dokonał pierwszego wypożyczenia w historii wypożyczalni.

nazwisko

----------

Lis

(1 row)

23. Wyświetl nazwiska aktorów, którzy zagrali w filmie pt. `Terminator'.

nazwisko

----------------

Schwarzenegger

Biehn

Hamilton

(3 rows)

24. Wyświetl tytuły wszystkich filmów, w których zagrał aktor o nazwisku `De Niro'.

tytul

--------------------

Taksowkarz

Ronin

Depresja gangstera

(3 rows)

25. Wyświetl tytuł filmu, który był wypożyczony na najdłuższy okres czasu.

tytul

-------

Ronin

(1 row)

26. Wyświetl nazwiska klientów, którzy dokonali wypożyczeń pomiędzy `2005-07-15' a `2005-07-20'. Wyeliminuj duplikaty.

nazwisko

-----------

Kowalski

Zielinski

(2 rows)

27. Wyświetl tytuły filmów wypożyczonych pomiędzy `2005-07-15' a `2005-07-25'. Wyeliminuj duplikaty.

tytul

--------------------

Ghostbusters

Mission Impossible

Taksowkarz

Terminator

(4 rows)

28. Dla klientów, którzy noszą takie same imiona, jak któryś z aktorów, wyświetl: wspólne imię, nazwisko klienta, nazwisko aktora.

imie | nazwisko | nazwisko

--------+---------------+----------------

Arnold | Zielinski | Schwarzenegger

Maciej | Wojciechowski | Stuhr

Pawel | Lis | Wilczak

(3 rows)

Operacje mnogościowe

29. Wyświetl wspólną listę nazwisk wszystkich klientów i wszystkich aktorów. Wynik uporządkuj alfabetycznie.

nazwisko

----------------

Arciuch

Aykroyd

Berenger

Biehn

Cruise

Crystal

Dafoe

De Niro

Dyblik

Dziedziel

Ford

Foster

Glinnicki

Hamilton

Keitel

Kowalski

Kudrow

Lis

Murray

Nowak

Oldman

Portman

Reno

Rogalski

Schwarzenegger

Seigner

Sheen

Shepherd

Stuhr

Tymon Tymanski

Weaver

Wilczak

Wojciechowski

Zielinski

(34 rows)

30. Wyświetl tytuły filmów, w których razem zagrali aktorzy o nazwiskach `De Niro' i `Reno'.

tytul

-------

Ronin

(1 row)

31. Wyświetl tytuły tych filmów, które były wypożyczane zarówno przez klienta o nazwisku `Kowalski', jak i przez klienta o nazwisku `Nowak'.

tytul

------------

Terminator

(1 row)

32. Wyświetl tytuły tych filmów, które były wypożyczane przez klienta o nazwisku `Kowalski', a zarazem nigdy nie były wypożyczane przez klienta o nazwisku `Nowak'.

tytul

--------

Wesele

(1 row)

33. Wyświetl nazwiska aktorów, którzy zagrali w filmie pt. `Terminator', a jednocześnie nie zagrali w filmie pt. `Ghostbusters'.

nazwisko

----------------

Biehn

Hamilton

Schwarzenegger

(3 rows)

Funkcje grupowe

34. Dla każdego roku produkcji filmu wyświetl średnią cenę wypożyczenia.

rok_produkcji | avg

---------------+------

2004 | 10

1999 | 10.5

1998 | 9.5

1996 | 8.5

1994 | 8.5

1988 | 8.5

1986 | 5

1984 | 7

1976 | 5

(9 rows)

35. Wyświetl cenę najdroższego filmu w wypożyczalni.

max

------

10.5

(1 row)

36. Wyświetl liczbę filmów wyprodukowanych w roku 1984.

count

-------

2

(1 row)

37. Ile razy wypożyczono film pt. `Taksowkarz?

count

-------

3

(1 row)

38. Jaki był średni czas trwania wypożyczenia filmu pt. `Ronin'? Wynik wyświetl z dokładnością do jednego miejsca po przecinku.

round

-------

3.5

(1 row)

39. Wyświetl zestawienie tytułów wszystkich filmów wraz z minimalnymi, maksymalnymi i średnimi czasami trwania ich wypożyczenia oraz z liczbą dokonanych wypożyczeń. Wyniki liczbowe podaj z dokładnością do jednego miejsca po przecinku. Nagłówki wyświetlanych kolumn powinny być zgodne z podanym przykładem.

tytul | min | max | sre | razy

--------------------+-----+-----+-----+------

Taksowkarz | 1.0 | 1.0 | 1.0 | 3

Ghostbusters | 1.0 | 1.0 | 1.0 | 2

Terminator | 1.0 | 3.0 | 2.0 | 2

Wesele | 1.0 | 1.0 | 1.0 | 2

Ronin | 3.0 | 4.0 | 3.5 | 2

Mission Impossible | 1.0 | 1.0 | 1.0 | 2

Frantic | 3.0 | 3.0 | 3.0 | 1

(7 rows)

40. Dla każdego klienta wypożyczalni wyświetl jego imię, nazwisko oraz liczbę dokonanych wypożyczeń.

imie | nazwisko | count

--------+---------------+-------

Jan | Kowalski | 2

Jan | Glinnicki | 3

Pawel | Lis | 1

Arnold | Zielinski | 4

Maciej | Wojciechowski | 1

Anna | Nowak | 3

(6 rows)

11

---