Esercizio - Interrogazioni SQL sul database dei riferimenti bibliografici (slide di SQL)

Proiezione, selezione, espressioni (1-7)

1. Nome e cognome di tutti gli autori. $\pi_{\text{Name},\text{Surname}}(\text{Author})$.

SELECT name, surname FROM Author;

9 righe, una per autore (la chiave è id, quindi nessun duplicato anche senza DISTINCT).

2. Tutti gli editori. SELECT * FROM Publisher;: 4 righe (* = tutte le colonne).

3. Gli autori e il loro numero d'ordine. $\pi_{\text{author},\text{authorNumber}}(\text{Write})$.

SELECT DISTINCT author, authorNumber FROM Write;

9 coppie distinte. Il DISTINCT rende la query equivalente alla proiezione algebrica (in SQL senza DISTINCT i duplicati restano; qui non ce ne sono comunque).

4. Cognome e nome in una colonna.

SELECT surname || ' ' || name AS AuthorData FROM Author;

Valori come Buck John R., Castelli Donatella, Tanenbaum Andrew S. (9 righe). || concatena, AS dà il nome alla colonna.

5. Il nome di Gilardi. $\pi_{\text{Name}}(\sigma_{\text{Surname}='\text{Gilardi}'}(\text{Author}))$.

SELECT name FROM Author WHERE surname = 'Gilardi';

Risultato: Gianni.

6. Identificatore e titolo dei riferimenti del 1999 o del 2000, in ordine di titolo.

SELECT id, title FROM Reference WHERE year = 1999 OR year = 2000 ORDER BY title ASC;

OppenheimSchafer1999 (Discrete-Time Signal Processing) e HorstmannCornell2000 (Java 2 tecniche avanzate), in ordine alfabetico di titolo.

7. Gli autori il cui cognome inizia per C.

SELECT name, surname FROM Author WHERE surname LIKE 'C%';

Castelli e Cornell. % = sequenza qualsiasi; _ = un solo carattere.

Aggregazioni e raggruppamenti (8-15)

8. Il numero d'autore massimo.

SELECT MAX(authorNumber) AS MaxNumberAuthors FROM Write;

Risultato: 3 (il terzo autore di Oppenheim-Schafer: Buck). La variante SELECT reference, MAX(authorNumber) FROM Write è errata: con un aggregato la SELECT non può contenere colonne non aggregate (PostgreSQL la rifiuta; SQLite la accetta e restituisce OppenheimSchafer1999, quasi per caso).

9. Quante righe ha Author. SELECT COUNT(*) AS AuthorRowsNumber FROM Author; $\Rightarrow$ 9.

10. Quanti autori diversi compaiono in Write.

SELECT COUNT(DISTINCT author) AS WriteAuthorsNumber FROM Write;

9: tutti gli autori hanno scritto qualcosa (COUNT(author) darebbe 14, le righe di Write).

11. Gli autori che compaiono in Write (con GROUP BY senza aggregati).

SELECT author FROM Write GROUP BY author;

9 righe: equivale a SELECT DISTINCT author FROM Write.

12. Per ogni autore il numero di riferimenti.

SELECT author, COUNT(*) FROM Write GROUP BY author;

BuckJR 1, CastelliD 2, CornellG 2, GilardiG 1, HorstmannCS 2, OppenheimAV 1, PaganoP 2, SchaferRW 1, TanenbaumAS 2.

13. Gli autori con più di un riferimento (HAVING).

SELECT author, COUNT(*) FROM Write GROUP BY author HAVING COUNT(*) > 1;

CastelliD, CornellG, HorstmannCS, PaganoP, TanenbaumAS (tutti con 2).

14. Come la 12, ma contando solo le righe con numero d'autore maggiore di 1 (WHERE).

SELECT author, COUNT(*) FROM Write WHERE authorNumber > 1 GROUP BY author;

BuckJR 1, CornellG 2, PaganoP 2, SchaferRW 1. Il WHERE agisce prima del raggruppamento: gli autori che sono sempre primi (Castelli, Gilardi, ...) scompaiono.

15. Come la 14, tenendo solo i gruppi con più di una riga (WHERE e HAVING).

SELECT author, COUNT(*) FROM Write WHERE authorNumber > 1 GROUP BY author HAVING COUNT(*) > 1;

CornellG 2, PaganoP 2. Prima WHERE (righe), poi GROUP BY, poi HAVING (gruppi).

Join (16-21)

16. Titolo dei riferimenti dell'editore McGraw-Hill. $\pi_{\text{name},\text{title}}\big(\text{Reference}\bowtie_{\text{publisher}=\text{id}}\sigma_{\text{name}='\text{McGraw-Hill}'}(\text{Publisher})\big)$.

SELECT name, title
FROM Reference AS R INNER JOIN Publisher AS P ON R.publisher = P.id
WHERE name = 'McGraw-Hill';

3 righe: Analisi Uno, Java 2 tecniche avanzate, Java 2 i fondamenti.

17. Quanti riferimenti per editore.

SELECT name, COUNT(*)
FROM Reference AS R INNER JOIN Publisher AS P ON R.publisher = P.id
GROUP BY name;

McGraw-Hill 3, Prentice Hall 3, ECDL 2, Springer 1.

18. Cognome degli autori e titolo dei loro riferimenti. $\pi_{\text{surname},\text{title}}(\text{Author}\bowtie_{\text{id}=\text{author}}\text{Write}\bowtie_{\text{reference}=\text{id}}\text{Reference})$ (con la rinomina degli id).

SELECT surname, title
FROM Author AS A INNER JOIN Write AS W ON A.id = W.author
                 INNER JOIN Reference AS R ON W.reference = R.id;

14 righe, una per riga di Write.

19. Titolo e note di ogni riferimento (anche senza note). $\pi_{\text{title},\text{notes}}(\text{Reference}\ \bowtie^{\text{L}}_{\text{id}=\text{reference}}\ \text{Information})$.

SELECT title, notes
FROM Reference AS R LEFT JOIN Information AS I ON R.id = I.reference;

9 righe: quattro con note (pp. 292-308, pp. 251-265, 3rd edition, 2nd edition) e cinque con NULL. Un join interno ne darebbe solo 4.

20. Quanti riferimenti non hanno informazioni aggiuntive.

SELECT COUNT(*)
FROM Reference AS R LEFT JOIN Information AS I ON R.id = I.reference
WHERE notes IS NULL;

5: il left join aggiunge i riferimenti senza note con notes = NULL, WHERE ... IS NULL li isola. (Dopo il join esterno il filtro sul lato destro è proprio quello che si vuole: tiene i NULL.)

21. Per ogni editore e cognome d'autore, il numero di riferimenti, ordinato.

SELECT P.name, A.surname, COUNT(R.id) AS ReferencesNumber
FROM Reference AS R INNER JOIN Publisher AS P ON R.publisher = P.id
     INNER JOIN Write AS W ON R.id = W.reference
     INNER JOIN Author AS A ON W.author = A.id
GROUP BY P.name, A.surname
ORDER BY P.name ASC, ReferencesNumber DESC, A.surname ASC;

9 righe: per esempio McGraw-Hill: Cornell 2, Horstmann 2, Gilardi 1; Prentice Hall: Tanenbaum 2, Buck 1, Oppenheim 1, Schafer 1; ECDL: Castelli 2, Pagano 2. Osservazione: si raggruppa per cognome, non per A.id: se due autori diversi avessero lo stesso cognome sarebbero fusi in un gruppo.

Query nidificate (22-23)

22. Il massimo numero di riferimenti di un editore.

SELECT MAX(ReferencesNumber)
FROM (SELECT publisher, COUNT(id) AS ReferencesNumber
      FROM Reference GROUP BY publisher) AS Counter;

Risultato 3. Non esiste MAX(COUNT(...)): il conteggio per editore è una tabella derivata, e il massimo si calcola su quella.

23. L'editore (o gli editori) con il massimo numero di riferimenti. La slide procede per tentativi; gli errori intermedi sono istruttivi:

-- Tentativo 1: ERRATO, un aggregato non può stare in WHERE
SELECT publisher, ReferencesNumber
FROM (SELECT publisher, COUNT(id) AS ReferencesNumber FROM Reference GROUP BY publisher) AS Counter
WHERE ReferencesNumber = MAX(ReferencesNumber);

-- Tentativo 2: ERRATO, la sottoquery fa riferimento a "Counter", visibile solo nella query esterna
WHERE ReferencesNumber = (SELECT MAX(ReferencesNumber) FROM Counter);

-- Soluzione: si ripete la tabella derivata dentro la sottoquery
SELECT publisher, ReferencesNumber
FROM (SELECT publisher, COUNT(id) AS ReferencesNumber FROM Reference GROUP BY publisher) AS Counter
WHERE ReferencesNumber = (SELECT MAX(ReferencesNumber)
                          FROM (SELECT publisher, COUNT(id) AS ReferencesNumber
                                FROM Reference GROUP BY publisher) AS Counter2);

Risultato: McGrawHill 3 e PrenticeHall 3 (a pari merito). La ripetizione si evita con una vista (si vede sotto) o con una CTE.

Con una vista (stesso risultato, provato):

CREATE VIEW PublisherReferences AS
SELECT publisher, COUNT(id) AS ReferencesNumber FROM Reference GROUP BY publisher;

SELECT publisher, ReferencesNumber FROM PublisherReferences
WHERE ReferencesNumber = (SELECT MAX(ReferencesNumber) FROM PublisherReferences);

Divisione intera e insiemi (24-25)

24. Il numero medio di riferimenti per editore.

SELECT COUNT(id) / COUNT(DISTINCT publisher) FROM Reference;                      -- 9 / 4 = 2 (divisione intera)
SELECT CAST(COUNT(id) AS DOUBLE PRECISION) / COUNT(DISTINCT publisher) FROM Reference;   -- 2,25

Con due interi / è la divisione intera e dà 2. Si converte uno dei due operandi per ottenere 2,25 (in SQLite il tipo si chiama REAL).

25. I riferimenti senza informazioni aggiuntive (differenza). $\pi_{\text{id}}(\text{Reference})-\rho_{\text{id}\leftarrow\text{reference}}(\pi_{\text{reference}}(\text{Information}))$.

SELECT id FROM Reference
EXCEPT
SELECT reference FROM Information;

5 righe: ECDL2002, Gilardi1991, HorstmannCornell2000, HorstmannCornell2001, OppenheimSchafer1999 (le stesse della query 20).

Esercizi aggiuntivi (stesso database)

A. Gli editori senza riferimenti. Dopo aver aggiunto un editore Wiley, le quattro forme NOT IN, NOT EXISTS, LEFT JOIN ... IS NULL, EXCEPT restituiscono Wiley. Se un riferimento ha publisher = NULL, la forma NOT IN (SELECT publisher FROM Reference) restituisce zero righe (verificato): si usa NOT EXISTS oppure si aggiunge WHERE publisher IS NOT NULL nella sottoquery.

B. I riferimenti più recenti della media del proprio editore (sottoquery correlata).

SELECT r.id, r.year, r.publisher FROM Reference r
WHERE r.year > (SELECT AVG(r2.year) FROM Reference r2 WHERE r2.publisher = r.publisher);

4 righe: HorstmannCornell2000, HorstmannCornell2001, OppenheimSchafer1999, Tanenbaum2001.

C. I cognomi degli autori che non sono mai oltre il primo posto (NOT EXISTS).

SELECT surname FROM Author a
WHERE NOT EXISTS (SELECT * FROM Write w WHERE w.author = a.id AND w.authorNumber > 1);

Castelli, Gilardi, Horstmann, Oppenheim, Tanenbaum.

D. Il trigger sull'ordine degli autori. In SQL avanzato - indici, transazioni e triggerUn file di record può essere heap (inserimento in ordine, ricerca per uguaglianza in media n/2 accessi), ordinato (log2 n), hash (costante, solo uguaglianza) o B-tree (log_F n, anche intervalli). Un indice è una struttura ausiliaria di coppie (chiave K, puntatore P) ordinate, molto più piccola della tabella: l'indice primario è sul campo di ordinamento del file ordinato (una chiave per blocco, non denso, log2(m)+1 accessi), l'indice secondario è su un campo qualsiasi (denso). CREATE [UNIQUE] INDEX, anche su espressioni; PostgreSQL usa tabelle heap con indici secondari B-tree (predefinito) o hash, e PRIMARY KEY e UNIQUE creano un indice implicito. Gli indici velocizzano le letture ma costano nelle scritture e in spazio. Una transazione è una sequenza atomica di operazioni con proprietà ACID (atomicità, consistenza, isolamento, durabilità): START TRANSACTION, COMMIT, SAVEPOINT, ROLLBACK [TO SAVEPOINT]. Un trigger è una regola evento-condizione-azione (ECA): BEFORE/AFTER, ROW/STATEMENT, WHEN, con funzione PL/pgSQL che usa NEW e OLD; serve per vincoli complessi e attributi derivati.SQL avanzato - indici, transazioni e trigger → c'è il trigger che impone che il primo autore di un riferimento abbia numero 1 e che non ci siano due numeri uguali; provato in SQLite su un riferimento nuovo.

Errori nel materiale del corso

• Nella slide sulla query 23, il tentativo con FROM Counter dentro la sottoquery è presentato come passaggio: non è valido perché Counter è un alias definito dalla FROM della query esterna.
• Alcune slide di esempio mostrano le tabelle con nomi italiani (Riferimento, Autore) e le query con nomi inglesi (Reference, Author): è lo stesso schema in due lingue.