Co można napisać po FROM?

<wyrażenieTabelowe>:

<specTabeli>

| <wyrażenieTabelowe> <operZłączenia> <specTabeli>

[ ON <warunekZłączenia> | USING <listaWspólnychKolumn> ]

| <zapytanieSELECT> [AS] <alias>

<specTabeli>:

[<userid>.] <nazwaTabeli> [ [AS] <alias> [(<aliasKol>{,<aliasKol>})]

<operZłączenia>:

CROSS JOIN

| [ NATURAL ] JOIN

| [ NATURAL ] INNER JOIN

| [ NATURAL ] LEFT [ OUTER ] JOIN

| [ NATURAL ] RIGHT [ OUTER ] JOIN

| [ NATURAL ] FULL [ OUTER ] JOIN

| UNION

Po FROM znajduje się lista zawierająca:

• nazwy tabel zdefiniowanych w bazie danych,

• wyrażenia złączeń i sumowania tabel,

• zapytania SELECT, których wynik jest tabelą tymczasową i lokalną w ramach zapytania SELECT.

Każdy z elementów na liście może mieć nadaną nową nazwę (alias tabeli i ewentualnie aliasy kolumn) - w przypadku zapytania SELECT nadanie tej nazwy jest konieczne. Nazwa jest widoczna tylko w ramach aktualnego zapytania SELECT.Co można napisać po WHERE?

<warunek selekcji>:

<wyrażenie> <operPorow> <wyrażenie>

| <wyrażenie> IS [ NOT ] NULL

| <wyrażenie> [ NOT ] LIKE <wyrażenie> [ESCAPE <wyrażenie>]

| <wyrażenie> [ NOT BETWEEN <wyrażenie> AND <wyrażenie>

| <wyrażenie> [ NOT ] IN (<wyrażenie>, ... )

| <wyrażenie> <operPorow> (<zapytanieSELECT>)

| <wyrażenie> <operPorow> ANY (<zapytanieSELECT>)

| <wyrażenie> <operPorow> ALL (<zapytanieSELECT>)

| <wyrażenie> [ NOT ] IN (<zapytanieSELECT>)

| EXISTS (<zapytanieSELECT>)

| NOT <warunek>

| <warunek> AND <warunek>

| <warunek> OR <warunek>

| (<warunek>)

| <warunek>IS [ NOT ] TRUE

| <warunek>IS [ NOT ] FALSE

| <warunek>IS [ NOT ] UNKNOWN

<operPorow>:

= > < >= <= <> != ~= !< !>

Zapytania zagnieżdżone:

<wyrażenie> = (<zapytanieSELECT>):

1. Podaj wypożyczenia dokonane przez Abackiego.

SELECT * FROM WYP

WHERE cz# = (SELECT cz# FROM CZYT

WHERE nazwisko= 'Abacki');

-- podzapytanie zwraca π_cz#(σ_{nazwsko='Abacki'}(CZYT))

-- zapytanie jest poprawne,

gdy podzapytanie SELECT zwraca jedną wartość;

ANY, SOME, ALL:

2. Podaj wypożyczenia na dłuższy okres niż jakieś wypożyczenie z tego roku.

SELECT * FROM WYP

WHERE dni > ANY (SELECT dni FROM WYP

WHERE dataWyp >= '2005-01-01');

-- zamiast ANY można użyć słowa SOME

-- podzapytanie zwraca π_dni(σ_{dataWyp>='2005-01-01'}(WYP))

3. Podaj wypożyczenia na więcej dni niż każde wypożyczenie z tego roku.

SELECT * FROM WYP

WHERE dni > ALL (SELECT dni FROM WYP

WHERE dataWyp >= '2005-01-01');

-- podzapytanie zwraca j.w. π_dni(σ_{dataWyp>='2005-01-01'}(WYP))

4. Podaj numery czytelników, którzy nie mają żadnego wypożyczenia na co najmniej 30 dni, ale mają przynajmniej jedno wypożyczenie.

SELECT cz# FROM WYP w1

WHERE 30 > ALL (SELECT dni FROM WYP w2

WHERE (w1.cz# = w2.cz#));

-- dla każdego czytelnika x=w1.cz# tworzony jest (być może) inny podzbiór opisany przez zapytanie: zbiór atrybutów dni z wszystkich krotek oznaczających wypożyczenia czytelnika w1.cz#,

czyli π_dni(σ_{cz# = x}(WYP))

IN, NOT IN:

5. Podaj numery czytelników, którzy mają wypożyczoną jakąś książkę Prusa.

SELECT cz# FROM WYP

WHERE sygn IN (SELECT sygn FROM EGZ, KS

WHERE (EGZ.ks# = KS.ks#)

AND (KS.autor = `Prus'));

-- podzapytanie tworzy zbiór złożony z sygnatur książek Prusa

6. Podaj numery czytelników, którzy mają wypożyczoną jakąś książkę innego autora niż Prus.

SELECT cz# FROM WYP

WHERE sygn NOT IN (SELECT sygn FROM EGZ, KS

WHERE (EGZ.ks# = KS.ks#) AND (KS.autor = `Prus'));

-- podzapytanie j.w.

7. Podaj numery czytelników, którzy nie mają wypożyczonej żadnej książki Prusa.

SELECT cz# FROM CZYT

WHERE cz# NOT IN

(SELECT cz# FROM CZYT, WYP, EGZ, KS

WHERE CZYT.cz#=WYP.cz# AND WYP.sygn=EGZ.sygn

AND EGZ.ks#=KS.ks# AND autor='Prus');

-- w podzapytaniu tworzymy zbiór numerów czytelników, którzy mają wypożyczoną przynajmniej jedną książkę Prusa;

EXISTS, NOT EXISTS:

8. Podaj nazwiska czytelników, którzy mają wypożyczoną jakąś książkę Prusa.

SELECT nazwisko FROM CZYT

WHERE EXISTS (SELECT * FROM WYP, EGZ, KS

WHERE (WYP.sygn = EGZ.sygn) AND (KS.ks# = EGZ.ks#)

AND (KS.autor = `Prus') AND (WYP.cz# = CZYT.cz#));

-- zapytanie zagnieżdżone zwraca wszystkie krotki złączenia naturalnego WYP |><| EGZ |><| KS oznaczające wypożyczenia książek Prusa dokonane przez danego czytelnika CZYT.cz#.

9. Podaj nazwiska czytelników, którzy nie mają wypożyczonej żadnej książki.

SELECT nazwisko FROM CZYT

WHERE NOT EXISTS (SELECT * FROM WYP WHERE WYP.cz# = CZYT.cz#);

-- zapytanie zagnieżdżone zwraca wszystkie wypożyczenia dokonane przez danego czytelnika CZYT.cz#.

Funkcje agregujące (czyli co można napisać po SELECT i co jeszcze można dopisać do zapytania SQL)

Dostępne są funkcje MAX, MIN, AVG (średnia arytmetyczna), SUM, COUNT (moc kolumny), COUNT(*) (moc relacji), które można zastosować do wybranych kolumn relacji wynikowej. Mogą być także dostępne inne funkcje statystyczne (np. odchylenie standardowe itp.)

1. Podaj liczbę (a) wierszy w relacji KS, (b) dzieł z ustalonym nazwiskiem autora, (c) różnych nazwisk autorów w bibliotece.

(a) SELECT COUNT(*) FROM KS;

(b) SELECT COUNT([ALL] autor) FROM KS;

(c) SELECT COUNT(DISTINCT autor) FROM KS;

2. Podaj średni wiek książki (egzemplarza) w bibliotece.

SELECT AVG(rokWyd)-MIN(rokWyd) as `Średni wiek' FROM EGZ;

3. Podaj, ile książek i na jak długo (razem) ma w tej chwili wypożyczonych Abacki.

SELECT COUNT(*) as `L.książek', SUM(dni) as `L.dni'

FROM CZYT NATURAL JOIN WYP

WHERE nazwisko='Abacki';

Klauzula GROUP BY (zapytania grupujące). Zbiór krotek wynikowych można podzielić na grupy i dla każdej z tych grup oddzielnie obliczyć wartość funkcji agregującej.

4. Podaj numery dzieł i z każdym z nich liczbę egzemplarzy tego dzieła w bibliotece (a). Uwzględnij dzieła, które mają zero egzemplarzy (b).

(a) SELECT ks#, COUNT(sygn) as `liczba egzemplarzy'

FROM EGZ

GROUP BY ks#;

2. SELECT EGZ.ks#, COUNT(DISTINCT sygn)

FROM KS LEFT OUTER JOIN EGZ

GROUP BY EGZ.ks#;

5. Dla każdego tytułu podaj rocznik najnowszego egzemplarza. Wynik uporządkuj malejąco wg roczników.

SELECT tytuł, MAX(rokWyd)

FROM KS NATURAL JOIN EGZ

GROUP BY tytuł

ORDER BY 2 DESC;

Klauzula HAVING. Przed przystąpieniem do obliczania funkcji agregującej można grupy poddać weryfikacji i wyeliminować niektóre z nich.

6. Dla każdego tytułu, który występuje w bibliotece w co najmniej 2 egzemplarzach podaj rocznik najnowszego egzemplarza. Wynik uporządkuj malejąco wg roczników.

SELECT tytuł, MAX(rokWyd)

FROM KS NATURAL JOIN EGZ

GROUP BY tytuł

HAVING COUNT(sygn)>=2

ORDER BY 2 DESC;

7. Podaj liczbę aktualnie wypożyczonych dzieł Sienkiewicza i Prusa.

SELECT autor, COUNT(DISTINCT ks#)

FROM (KS NATURAL JOIN EGZ) NATURAL JOIN WYP

GROUP BY autor

HAVING autor='Sienkiewicz' OR autor='Prus';

8. Podaj nazwiska czytelników wraz z liczbą aktualnie wypożyczonych książek, o ile osoba ta ma jakąś książkę, którą ma jednocześnie Abacki. Wynik uporządkuj malejąco wg liczby wypożyczonych egzemplarzy.

SELECT nazwisko, COUNT(sygn)

FROM CZYT NATURAL JOIN WYP NATURAL JOIN EGZ

WHERE ks# IN

(SELECT ks#

FROM CZYT NATURAL JOIN WYP NATURAL JOIN EGZ

WHERE nazwisko='Abacki')

GROUP BY CZYT.cz#, nazwisko

ORDER BY 2 DESC;

Zapytania grupujące - posumowanie.

(5) SELECT listaWynikowaDlaGrup -- np. f(C),A

(1) FROM listaRelacji

(2) [ WHERE warunekSelekcji ] -- np. wynik R(ABC)

(3) GROUP BY listaAtrybutówGrupowania -- np. A,B

(4) [ HAVING warunekWyboruGrupy ] -- np. B=2 or f(C)>2

(6) [ ORDER BY 2 DESC ];

(1-2) Klauzule FROM i WHERE służą wyliczeniu wstępnego wyniku (np. R=ABC), jak w zwykłych zapytaniach SELECT;

(3) Wynik wstępny zostaje podzielony na grupy G1,G2,...,Gk, według jednakowych wartości atrybutów grupowania wybranych spośród atrybutów wstępnego wyniku R (np. A,B)

(4) Warunek w sekcji Having musi być „obliczalny” dla grupy, czyli może dotyczyć jedynie atrybutów stałych w ramach grupy (np. B=2) lub funkcji agregujących wyliczanych dla grupy (np. f(C)>2). Po tej klauzuli pozostają jedynie grupy spełniające warunek selekcji grupy.

(5) W relacji wynikowej powstaje po jednej krotce dla każdej grupy utworzonej w klauzuli GROUP BY i pozostałej po ewentualnej selekcji grup w klauzuli HAVING. Taka krotka zawiera pola wskazane na liście wynikowej, więc lista wynikowa może zawierać tylko wartości stałe w ramach grupy (atrybuty grupowania) lub wartości funkcji agregujących wyliczone dla grupy.

(6) Porządkowanie wyniku odbywa się na końcu. Wynik można porządkować według kolumn z listy klauzuli SELECT.Zapytania modyfikujące. Dopisywanie, usuwanie i zmienianie pól wybranych krotek relacji.

9. Dopisz do KS `Potop' Sienkiewicza.

1. INSERT INTO KS

VALUES(1230,'Sienkiewicz','Potop','lit');

(b) INSERT INTO KS(autor,tytuł,ks#)

VALUES(`Sienkiewicz', `Potop', 1230);

10. Dopisz do EGZ po 1 egzemplarzu każdej książki Prusa, która jest w bibliotece najwyżej w 3 egzemplarzach. Nadaj egzemplarzowi sygnaturę o 1 większą niż dotychczasowe egzemplarze tego dzieła.

INSERT INTO EGZ(sygn,ks#,rokWyd)

SELECT MAX(sygn)+1, ks#, CURRENT_YEAR

FROM KS NATURAL JOIN EGZ

WHERE autor='Prus'

GROUP BY ks#

HAVING COUNT(*)<=3;

11. Usuń wypożyczenia Abackiego, (a) gdy znasz jego numer (np.1123) lub (b) gdy go nie znasz.

(a) DELETE FROM WYP WHERE cz#=1123;

(b) DELETE FROM WYP WHERE cz#=

(SELECT MAX(cz#) FROM CZYT

WHERE nazwisko='Abacki');

12. Przedłuż Abackiemu wszystkie wypożyczenia o 7 dni i opóźnij ich datę wypożyczenia o siedem dni.

UPDATE WYP SET dni=dni+1, dataWyp=dataWyp+7

WHERE cz#=(SELECT MAX(cz#) FROM CZYT

WHERE nazwisko='Abacki');

---