6. Approfondimento del linguaggio SQL
6.1. Introduction
In questo capitolo presentiamo
- altre sintassi del comando SELECT che lo rendono un comando di consultazione molto potente, in particolare per consultare più tabelle contemporaneamente.
- sintassi estese di comandi già studiati
Per illustrare i vari comandi, lavoreremo con le seguenti tabelle utilizzate per la gestione degli ordini in un sistema di distribuzione di libri PME:
6.1.1. la tabella CLIENTS
Memorizza le informazioni sui clienti del sistema PME:
 |
numero che identifica in modo univoco il cliente - chiave primaria | |
nome del cliente | |
I=Persona fisica, E=Azienda, A=Amministrazione | |
nome (nel caso di una persona fisica) | |
Cognome della persona di contatto presso il cliente (nel caso di un'azienda o di un ente pubblico) | |
Indirizzo del cliente - via | |
città | |
codice postale | |
Telefono | |
Da quando è cliente? | |
O (Sì) se il cliente ha un debito nei confronti dell'azienda e N (No) in caso contrario. |
6.1.2. la tabella ARTICLES
Memorizza informazioni sui prodotti venduti, in questo caso libri. La sua struttura è la seguente:
numero che identifica in modo univoco un libro (ISBN = International Standard Book Number) - chiave primaria | |
Titolo del libro | |
Codice che identifica in modo univoco un editore | |
Nome dell'autore | |
Sinossi del libro | |
Quantità venduta nell'anno | |
Quantità venduta l'anno precedente | |
Data dell'ultima vendita | |
Quantità dell'ultima consegna | |
Data dell'ultima consegna | |
Prezzo di vendita | |
Costo di acquisto | |
Quantità minima da ordinare | |
Livello minimo di scorte | |
Quantità in magazzino |
Il contenuto potrebbe essere il seguente:
6.1.3. la tabella COMMANDES
Registra le informazioni relative agli ordini effettuati dai clienti. La sua struttura è la seguente:
Numero che identifica in modo univoco un ordine - chiave primaria | |
Numero del cliente che effettua l'ordine - codice esterno - codice articolo CLIENTS(ID) | |
Data di inserimento dell'ordine | |
O (Sì) se l'ordine è stato annullato e N (No) in caso contrario. |
6.1.4. la tabella DETAILS
Contiene i dettagli di un ordine, ovvero i codici e le quantità dei libri ordinati. La sua struttura è la seguente:
Numero dell'ordine - chiave esterna che fa riferimento alla colonna NOCMD della tabella COMMANDES | |
Numero del libro ordinato - chiave esterna che fa riferimento alla colonna ISBN della tabella LIVRES | |
Quantità ordinata |
Il suo contenuto potrebbe essere il seguente:
Come si vede sopra, l’ordine n. 3 (NOCMD) riguarda tre libri. Ciò significa che il cliente ha ordinato tre libri contemporaneamente. I dati di questo cliente si trovano nella tabella [COMMANDES], dove si vede che l'ordine n. 3 è stato effettuato dal cliente n. 5. La tabella [CLIENTS] ci indica che il cliente n. 5 è la società NetLogos di Segré.
6.2. L’ordine SELECT
In questa sede intendiamo approfondire la nostra conoscenza dell’ordine SELECT presentando nuove sintassi relative ad esso.
6.2.1. Sintassi di una query su più tabelle
SELECT colonna1, colonna2, ... FROM tabella1, tabella2, ..., tabellap WHERE condition ORDER BY ... | |
La novità in questo caso consiste nel fatto che le colonne colonna1, colonna2, ... provengono da diverse tabelle tabella1, tabella2, ... Se due tabelle hanno colonne con lo stesso nome, l'ambiguità viene risolta utilizzando la notazione tablei.colonnej. Il codice condition può riferirsi alle colonne di tabelle diverse. |
Funzionamento
Viene generata la tabella cartesiana di table1, table2, ..., tablep. Se ni è il numero di righe di tablei, la tabella costruita avrà quindi n1*n2*...*np righe contenenti l’insieme delle colonne delle diverse tabelle. | |
A questa tabella viene applicata la condition della WHERE. Viene così generata una nuova tabella | |
Questa è ordinata secondo la modalità indicata in ORDER. | |
Vengono visualizzate le colonne richieste in base a SELECT. |
Esempi
Si utilizzano le tabelle presentate in precedenza. Si desidera conoscere i dettagli degli ordini effettuati dopo il 25 settembre:
SQL>select details.nocmd,isbn,qte from commandes,details
where commandes.datecmd>'25-sep-91'
and details.nocmd=commandes.nocmd
Si noti che dopo FROM si inserisce il nome di tutte le tabelle di cui si fanno riferimento alle colonne. Nell'esempio precedente, le colonne selezionate appartengono tutte alla tabella DETAILS. Tuttavia, la condizione fa riferimento alla tabella COMMANDES. Da qui la necessità di specificare quest’ultima dopo FROM. L’operazione che verifica l’uguaglianza delle colonne di due tabelle diverse viene spesso definita «equi-join».
La query SELECT avrebbe potuto essere scritta anche nel modo seguente:
SQL> select details.nocmd,isbn,qte from commandes
inner join details on details.nocmd=commandes.nocmd
where commandes.datecmd>'25-sep-91'
Continuiamo con i nostri esempi. Si desidera ottenere lo stesso risultato di prima, ma con il titolo del libro ordinato, anziché il suo numero ISBN:
SQL>select commandes.nocmd, articles.titre, details.qte
from commandes,articles,details
where commandes.datecmd>'25-sep-91'
and details.nocmd=commandes.nocmd
and details.isbn=articles.isbn
Si ottiene lo stesso risultato con la seguente query SQL, meno leggibile:
SQL> select details.nocmd,articles.titre,details.qte from details
inner join commandes on details.nocmd=commandes.nocmd
inner join articles on details.isbn=articles.isbn
where commandes.datecmd>'25-sep-91'
Nell'esempio sopra riportato, vengono effettuati due join interni con la tabella [DETAILS]:
- una con la tabella [COMMANDES] per accedere alla data di ordinazione di un libro
- una con la tabella [ARTICLES] per accedere al titolo del libro ordinato
Si desidera inoltre il nome del cliente che effettua l'ordine:
SQL>select commandes.nocmd, articles.titre, qte ,clients.nom
from commandes,details,articles,clients
where commandes.datecmd>'25-sep-91'
and details.nocmd=commandes.nocmd
and details.isbn=articles.isbn
and commandes.idcli=clients.id
Si desiderano inoltre le date degli ordini e la visualizzazione di tali date in ordine decrescente:
SQL>select commandes.nocmd, commandes.datecmd, articles.titre, qte ,clients.nom
from commandes,details,articles,clients
where commandes.datecmd>'25-sep-91'
and details.nocmd=commandes.nocmd
and details.isbn=articles.isbn
and commandes.idcli=clients.id
order by commandes.datecmd descending
Ecco alcune regole da seguire nelle unioni:
- Dopo SELECT, si inseriscono le colonne che si desidera visualizzare. Se la colonna è presente in diverse tabelle, la si precede con il nome della tabella.
- Dopo FROM, si inseriscono tutte le tabelle che saranno analizzate da SELECT, ovvero le tabelle che contengono le colonne che si trovano dopo SELECT e WHERE.
6.2.2. L'auto-join
Vogliamo conoscere i libri che hanno un prezzo di vendita superiore a quello del libro «Using SQL»:
SQL>select a.titre from articles a, articles b
where b.titre='Using SQL'
and a.prixvente>b.prixvente
Le due tabelle dell'unione sono qui identiche: la tabella articles. Per distinguerle, viene loro assegnato un alias: from articles a, articles b. L'alias della prima tabella è a e quello della seconda è b. Questa sintassi può essere utilizzata anche se le tabelle sono diverse. Quando si utilizza un alias, questo deve essere impiegato ovunque nel comando SELECT al posto della tabella a cui si riferisce.
6.2.3. Join esterno
Si desidera conoscere i clienti che hanno effettuato un acquisto a settembre, indicando la data dell’ordine. Gli altri clienti vengono visualizzati senza tale data:
SQL>select clients.nom,commandes.datecmd from clients
left outer join commandes on clients.id=commandes.idcli
where datecmd between '01-sep-91' and '30-sep-91'
Ci sorprende non ottenere il risultato corretto. Dovrebbero essere presenti tutti i clienti nella tabella [CLIENTS], ma non è così. Se si riflette sul funzionamento del join esterno, ci si rende conto che i clienti che non hanno effettuato acquisti sono stati associati a una riga vuota della tabella COMMANDES e quindi a una data vuota (valore NULL nella terminologia SQL). Questa data non soddisfa quindi la condizione impostata sulla data e il cliente corrispondente non viene visualizzato. Proviamo qualcos’altro:
SQL>select clients.nom,commandes.datecmd from clients
left outer join commandes on clients.id=commandes.idcli
where (commandes.datecmd between '01-sep-91' and '30-sep-91')
or (commandes.datecmd is null)
Questa volta otteniamo la risposta corretta alla nostra domanda.
6.2.4. Richieste annidate
SELECT colonna[s] FROM tabella[s] WHERE espressione operatore query ORDER BY ... | |
requête è un comando SELECT che restituisce un insieme di 0, 1 o più valori. Si ha quindi una condizione WHERE del tipo espressione operatore (val1, val2, ..., vali) expression e vali devono essere dello stesso tipo. Se la query restituisce un solo valore, si torna a una condizione del tipo espressione operatore valore che conosciamo bene. Se la query restituisce un elenco di valori, si potranno utilizzare i seguenti operatori:
expression IN (val1, val2, ..., vali): vero se expression ha come valore uno degli elementi dell'elenco vali.
inverso di IN
deve essere preceduto da =, !=, >, >=, <, <= expression >= ANY (val1, val2, .., valn): vero se expression è >= a uno dei valori vali dell'elenco
deve essere preceduto da =, !=, >, >=, <, <= expression >= ALL (val1, val2, .., valn): vero se l'espressione è >= a tutti i valori vali dell'elenco
condizione: vera se requête restituisce almeno una riga. |
Esempi
Riprendiamo la domanda già risolta con un'equi-giunzione: visualizzare i titoli con un prezzo di vendita superiore a quello del libro «Using SQL».
SQL>select titre from ARTICLES
where prixvente > (select prixvente from ARTICLES where titre='Using SQL')
Questa soluzione sembra più intuitiva di quella del join equi. Si effettua un primo filtro con un SELECT, poi un secondo sul risultato ottenuto. In questo modo è possibile effettuare diversi filtri in serie.
Si desidera individuare i titoli con un prezzo di vendita superiore al prezzo medio di vendita:
Quali sono i clienti che hanno ordinato i titoli risultanti dalla query precedente?
SQL>select distinct idcli from COMMANDES,DETAILS
where DETAILS.isbn in
(select isbn from ARTICLES where prixvente
> (select avg(prixvente) from ARTICLES))
and COMMANDES.nocmd=DETAILS.nocmd
Spiegazioni
- si selezionano nella tabella DETAILS i codici ISBN presenti tra i libri il cui prezzo è superiore al prezzo medio dei libri.
- Nelle righe selezionate nella fase precedente non è presente il codice cliente IDCLI. Esso si trova nella tabella COMMANDES. Il collegamento tra le due tabelle avviene tramite il numero d'ordine NOCMD, da cui deriva l'equi-join COMMANDES.nocmd=DETAILS.nocmd.
- Uno stesso cliente potrebbe aver acquistato più volte uno dei libri in questione; in tal caso, il suo codice IDCLI risulterà presente più volte. Per evitare ciò, si inserisce la chiave DISTINCT dopo SELECT. In generale, DISTINCT elimina i duplicati nelle righe risultanti da un SELECT.
- Per ottenere il nome del cliente, dovremmo eseguire un’ulteriore join tra le tabelle COMMANDES e CLIENTS, come illustrato dalla query seguente.
SQL> select distinct CLIENTS.nom from COMMANDES,DETAILS,CLIENTS
where DETAILS.isbn in
(select isbn from ARTICLES where prixvente
> (select avg(prixvente) from ARTICLES))
and COMMANDES.nocmd=DETAILS.nocmd
and COMMANDES.IDCLI=CLIENTS.ID
Trova i clienti che non hanno effettuato ordini dal 24 settembre:
SQL>select nom from CLIENTS
where clients.id not in
(select distinct commandes.idcli from commandes where datecmd>='24-sep-91')
Abbiamo visto che è possibile filtrare le righe in modo diverso rispetto alla clausola WHERE: utilizzando la clausola HAVING in combinazione con la clausola GROUP e BY. La clausola HAVING filtra gruppi di righe.
Analogamente alla clausola WHERE, la sintassi
HAVING expression opérateur requête
è possibile, con il vincolo già illustrato secondo cui expression deve essere una delle espressioni expri della clausola
GROUP BY expr1, expr2, ...
Esempi
Quali sono le quantità vendute per i libri con un prezzo superiore a 200F?
Visualizziamo innanzitutto le quantità vendute per titolo:
SQL>select ARTICLES.titre,sum(qte) QTE from ARTICLES, DETAILS
where DETAILS.isbn=ARTICLES.isbn
group by titre
Ora filtriamo i titoli:
SQL> select ARTICLES.titre,sum(qte) QTE from ARTICLES, DETAILS
where DETAILS.isbn=ARTICLES.isbn
group by titre
having titre in (select titre from ARTICLES where prixvente>200)
In modo forse più evidente si sarebbe potuto scrivere:
SQL>select ARTICLES.titre,sum(qte) QTE from ARTICLES, DETAILS
where DETAILS.isbn=ARTICLES.isbn
and ARTICLES.prixvente>200
group by titre
6.2.5. Richieste correlate
Nel caso delle query annidate, si ha una query madre (la query più esterna) e una query figlia (la query più interna). La query madre viene valutata solo dopo che la query figlia è stata valutata completamente.
Le query correlate hanno la stessa sintassi, con la seguente differenza: la query figlia esegue un join sulla tabella della query madre. In questo caso, l'insieme costituito dalla query madre e dalla query figlia viene valutato ripetutamente per ogni riga della tabella madre.
Esempio
Riprendiamo l’esempio in cui desideriamo ottenere i nomi dei clienti che non hanno effettuato ordini dal 24 settembre:
SQL>
select nom from clients
where not exists
(select idcli from commandes
where datecmd>='24-sep-91'
and commandes.idcli=clients.id)
La query principale viene eseguita sulla tabella clients. La query figlia esegue un join tra le tabelle clients e commandes. Si tratta quindi di una query correlata. Per ogni riga della tabella clients, viene eseguita la query figlia: essa cerca il codice cliente id negli ordini effettuati dopo il 24 settembre. Se non lo trova (not exists), viene visualizzato il nome del cliente. Successivamente, si passa alla riga successiva della tabella clients.
6.2.6. Criteri di selezione per la scrittura del SELECT
Abbiamo visto, in diverse occasioni, che era possibile ottenere lo stesso risultato con diverse scritture del SELECT. Facciamo un esempio: visualizzare i clienti che hanno effettuato un ordine:
Join
Richieste annidate
dà lo stesso risultato.
Richieste correlate
SQL>
select nom from clients
where exists (select * from commandes where commandes.idcli=clients.id)
dà lo stesso risultato.
Gli autori Christian MAREE e Guy LEDANT, nel loro libro «SQL, Introduzione, Programmazione e Padroneggiamento», propongono alcuni criteri di scelta:
Prestazioni
L’utente non sa come il SGBD «riesca» a trovare i risultati richiesti. È quindi solo attraverso l’esperienza che scoprirà che una determinata sintassi è più efficiente di un’altra. MAREE e LEDANT affermano, sulla base della loro esperienza, che le query correlate sembrano generalmente più lente rispetto alle query annidate o ai join.
Formulazione
La formulazione tramite query annidate è spesso più leggibile e intuitiva rispetto al join. Tuttavia, non è sempre utilizzabile. In particolare, occorre tenere presenti due aspetti:
- Le tabelle a cui appartengono le colonne utilizzate come argomenti nella query SELECT (SELECT col1, col2, ...) devono essere specificate dopo la parola chiave FROM. Viene quindi eseguito il prodotto cartesiano di queste tabelle, ovvero ciò che viene definito un join.
- Quando la query visualizza risultati provenienti da una singola tabella e il filtraggio delle righe di quest’ultima richiede la consultazione di un’altra tabella, è possibile utilizzare le query annidate.
6.3. Estensioni della sintassi
Per motivi di praticità, abbiamo spesso presentato sintassi ridotte dei vari comandi. In questa sezione ne presentiamo le sintassi estese. Sono intuitive poiché analoghe a quelle del comando SELECT, ampiamente studiato.
INSERT
INSERT INTO table (col1, col2, ..) VALUES (val1, val2, ...) | |
INSERT INTO table (col1, col2, ..) (requête) | |
Sono state presentate queste due sintassi |
DELETE
DELETE FROM table WHERE condition | |
Questa sintassi è nota. Aggiungiamo che la condizione può contenere una query con la sintassi WHERE espressione operatore (query) |
UPDATE
UPDATE table SET col1=expr1, col2=expr2, ... WHERE condition | |
Questa sintassi è già stata illustrata. Aggiungiamo che la condizione può contenere una query con la sintassi WHERE espressione operatore (query) |
UPDATE table SET (col1, col2, ..) = query1, (cola, colb, ..) = query2, ... WHERE condition | |
I valori assegnati alle diverse colonne possono provenire da una query. |