lezione_db2

Lezione del 31 Marzo 2003 - Docente : Elisabetta Splendori
Il Linguaggio SQL
SELECT
La dichiarazione SELECT recupera dati da un database e li restituisce all’utente sotto forma di
query.
Es.:
a)
SELECT CITY, TARGET, SALES
FROM OFFICES
WHERE REGION =‘Eastern’
Elenca gli uffici vendite orientali con obiettivi e vendite annuali.
b)
SELECT CITY, TARGET, SALES
FROM OFFICES
WHERE REGION =‘Eastern’
AND SALES >TARGET
ORDER BY CITY
Elenca gli uffici vendite della regione orientale le cui vendite hanno superato gli obiettivi, in ordine
alfabetico di città.
c)
SELECT AVG( TARGET), AVG(SALES)
FROM OFFICES
WHERE REGION =‘Eastern’
Obiettivo medio e vendite medie per gli uffici della regione orientale.
La forma completa della dichiarazione SELECT e’ costituita da 6 proposizioni ma solo due sono
obbligatorie: le prop. SELECT e FROM. Le altre 4 sono opzionali.
SELECT [ALL | DISTINCT] < selezione elemento > [,selezione elemento, … | * ]
FROM < specifica tabella >
[ WHERE < condizione di ricerca > ]
[GROUP BY < colonna di raggruppamento > [, colonna di raggruppamento, … ] ]
[HAVING <condizione di ricerca > ]
[ORDER BY < specifica di ordinamento > ]
La proposizione SELECT elenca i dati da recuperare per mezzo della dichiarazione SELECT.
Gli elementi possono essere colonne del database o colonne che devono essere calcolate da SQL
quando esegue la query.
Si osservi l’infelice conflitto di notazione: la parola chiave SELECT in SQL corrisponde a quello
che in algebra relazionale si chiama “proiezione” e non “selezione”.
1
Lezione del 31 Marzo 2003 - Docente : Elisabetta Splendori
La proposizione FROM elenca le tabelle che contengono i dati che devono essere recuperati
dalla query . Tale proposizione realizza il prodotto cartesiano esteso.
La proposizione WHERE indica a SQL di inserire certe righe di dati nei risultati della query.
Per specificare le righe desiderate viene utilizzata una condizione di ricerca.
La proposizione GROUP BY specifica una query di sommario. Invece di generare una riga di
risultati di query per ciascuna riga di dati nel database, una query di sommario raggruppa le righe
simili e genera una riga di sommario di risultati di query per ciascun gruppo.
La proposizione HAVING dice a SQL di includere nei risultati di query solo certi gruppi
generati dalla proposizione GROUP BY. Per specificare i gruppi desiderati viene utilizzata una
condizione di ricerca.
La proposizione ORDER BY ordina i risultati della query basandosi sui dati di una o più
colonne. Se viene omessa, i risultati di query non sono ordinati.
Analizziamo nel dettaglio le proposizioni appena elencate.
 Come già detto in precedenza, la proposizione SELECT specifica gli elementi che devono
essere recuperati dalla query.
In genere questo sono specificati da un elenco di selezione (o lista di attributi ), un elenco di
elementi di selezione separati da virgole. Ciascun elemento di selezione genera una singola
colonna di risultati di query in ordine da sinistra a destra.
Un elemento di selezione può essere uno dei seguenti elementi:
-
un nome di colonna, che identifica una colonna della tabella (o delle tabelle) elencata nella
proposizione FROM. SQL prende il valore della colonna che è elemento di selezione da
ciascuna riga del database e lo inserisce nella riga di risultato della query corrispondente;
-
una costante,che specifica che lo stesso valore costante deve comparire in ogni riga dei
risultati di query;
-
un’ espressione SQL, che indica che SQL deve calcolare il valore da inserire nei risultati di
query, secondo quanto specificato dall’espressione.
Ciascun tipo di elemento di selezione sarà descritto più avanti.
 La proposizione FROM è costituita dalla parola chiave FROM seguita da un elenco di specifiche
di tabelle separate da virgole. Ciascuna specifica di tabella identifica una tabella contenente dati
che devono essere recuperati dalla query.
Tali tabelle sono chiamate tabelle sorgente della query, cioè sono la sorgente di tutti i dati nei
risultati di query.
Per il momento si farà riferimento a query che hanno una sola tabella sorgente e ciascuna
proposizione FROM contiene un singolo nome di tabella.
Il risultato di una query SQL è sempre una tabella di dati, come le tabelle presenti nel database.
2
Lezione del 31 Marzo 2003 - Docente : Elisabetta Splendori
Tale tabella può essere composta da (*):
- più righe;
- una sola riga (In alcuni casi i risultati di query possono essere un solo valore; cioè questi
risultati sono ancora una tabella, sebbene molto piccola, costituita da una colonna e una riga);
- zero righe; anche in questo caso i risultati di query sono ancora una tabella ma tale tabella è
vuota.
(*) Il numero di colonne sarà specificato dall’elenco di selezione.
Il fatto che una query SQL generi sempre una tabella di dati è molto importante perché significa che
i risultati di query possono essere nuovamente memorizzati nel database come tabella.
Questo indica che i risultati di due query simili possono essere combinati per formare una tabella di
risultati di query più grande e indica anche che i risultati di query posso essere a loro volta obiettivo
di ulteriori query.
Query semplici
Le query più semplici richiedono colonne di dati da una singola tabella nel database.
Per esempio, la query che segue richiede tre colonne dalla tabella OFFICES:
Elenca luogo, regione e vendite di ciascun ufficio vendite.
SELECT CITY, TARGET, SALES
FROM OFFICES
La dichiarazione SELECT per query semplici come questa contiene solo le due proposizioni
obbligatorie; la proposizione SELECT indica le colonne richieste; la proposizione FROM indica la
tabella che le contiene.
Oltre alle colonne i cui valori derivano direttamente dal database, una query SQL può comprendere
colonne calcolate i cui valori sono calcolati dai valori dati memorizzati. Per richiedere una colonna
calcolata, si specifica un’ espressione SQL nell’elenco di selezione. Le espressioni SQL possono
comprendere addizione, sottrazione, moltiplicazione e divisione. E’ anche possibile utilizzare
parentesi per realizzare espressioni più complesse. Naturalmente le colonne indicate nell’espressione
aritmetica devono essere un tipo numerico; se si cerca di sommare, sottrarre, moltiplicare o dividere
colonne contenenti testo, SQL riporta un errore.La query di seguito mostra una colonna calcolata.
Elenca luogo, regione e quantità sopra/sotto l’obiettivo per ciascun ufficio.
SELECT CITY, REGION, (SALES  TARGET)
FROM OFFICES
Molti prodotti SQL forniscono ulteriori operazioni aritmetiche, operazioni su stringhe di caratteri e
funzioni incorporate che possono essere utilizzate in espressioni SQL. Queste possono comparire in
espressioni di elenchi di selezione, come nell’esempio che segue.
Elenca nome, mese e anno di assunzione di ciascun venditore
SELECT NAME, MONTH(HIRE_DATE), YEAR(HIRE_DATE)
FROM SALESREPS
3
Lezione del 31 Marzo 2003 - Docente : Elisabetta Splendori
Le costanti SQL possono essere anche utilizzate come elementi in un elenco di selezione. Questa
possibilità può essere utile per generare risultati di query più facili da leggere e interpretare, come
nell’esempio che segue.
Elenca le vendite per ogni città.
SELECT CITY, ‘has sales of’, SALES
FROM OFFICES
Utilizzando le costanti, i risultati di query sembrano essere costituiti da una frase separata per
ciascun ufficio, ma in realtà sono tre colonne in cui la seconda colonna contiene sempre la stessa
stringa di testo.
Selezione di tutte le colonne (SELECT *)
A volte è utile visualizzare i contenuti di tutte le colonne di una tabella. Questo può essere
particolarmente utile quando si incontra per la prima volta un database e si desidera comprendere
velocemente la sua struttura e i dati che contiene. SQL consente di utilizzare un asterisco (*) al posto
dell’elenco di selezione, come abbreviazione per “tutte le colonne”:
Mostra tutti i dati della tabella OFFICES.
SELECT *
FROM OFFICES
I risultati di query contengono tutte le sei colonne della tabella OFFICES nello stesso ordine con cui
si trovano nella tabella.
La selezione di tutte le colonne è la più adatta quando si utilizza SQL programmatico, perché
modifiche alla struttura del database possono fare in modo che un programma non vada a buon fine.
Se si vuole che la query sia immune alle modifiche alla struttura al database, sempre che le colonne
indicate continuino ad esistere nella tabella OFFICES, si possono richiedere per nome tutte le
colonne.
Righe duplicate (DISTINCT)
Se una query contiene la chiave primaria di una tabella nel suo elenco di soluzioni, ogni riga risultato
della query sarà unica. Se la chiave primaria non è compresa nei risultati di query, si possono
verificare righe doppie.
Per esempio, si supponga di effettuare la seguente richiesta:
Elenca il numero di dipendenti di tutti i manager degli uffici vendite.
SELECT MGR
FROM OFFICES
MGR
-----108
106
104
105
108
4
Lezione del 31 Marzo 2003 - Docente : Elisabetta Splendori
Il risultato di query ha cinque righe (una per ciascun ufficio), ma due di esse sono l’una l’esatto
duplicato dell’altra. Perché? Perché Larry Fitch gestisce gli uffici di Los Angeles e Denver.
E’ possibile eliminare le righe duplicate nei risultati di query inserendo la parola chiave DISTINCT
nella dichiarazione SELECT, prima dell’elenco di selezione.
SELECT DISTINCT MGR
FROM OFFICES
MGR
-----108
106
104
105
Concettualmente, SQL esegue la query generando prima di tutto un elenco completo di risultati di
query (5 righe), quindi eliminando le righe doppie per generare i risultati finali.
Se la parola chiave DISTINCT viene omessa, SQL non elimina le righe duplicate.
E’ possibile specificare la parola chiave ALL per indicare esplicitamente che le righe doppie devono
essere mantenute ma non è necessario poiché è il comportamento predefinito.
SELECT ALL MGR
FROM OFFICES
SELECT MGR
FROM OFFICES
In genere si desiderano selezionare alcune righe di una tabella e inserire solo tali righe nei risultati di
query. La proposizione WHERE viene utilizzata per specificare le righe che si desiderano
recuperare. Di seguito alcuni esempi di semplici query che utilizzano la proposizione WHERE:
Mostra gli uffici le cui vendite hanno superato gli obiettivi.
SELECT CITY, SALES, TARGET
FROM OFFICES
WHERE SALES > TARGET
La proposizione WHERE é costituita dalla parola chiave WHERE seguita da una condizione di
ricerca che specifica le righe da recuperare. In altre parole, essa agisce da filtro per le righe della
tabella.
Le righe che soddisfano la condizione di ricerca possono attraversare il filtro e diventano parte dei
risultati di query; le altre righe vengono escluse.
Mostra i dipendenti il cui manager è Bob Smith (dipendente 104).
SELECT NAME, SALES
FROM SALESREPS
WHERE MANAGER=104
La condizione di ricerca può generare uno dei tre risultati:
- TRUE
- FALSE
- NULL (sconosciuto)
5
Lezione del 31 Marzo 2003 - Docente : Elisabetta Splendori
Se il risultato è TRUE, la riga viene inserita nei risultati di query; se il risultato è FALSE o NULL, la
riga viene esclusa dai risultati di query.
SQL offre una vasta gamma di condizioni di ricerca che consentono di specificare molti tipi diversi
di query in modo efficace e naturale. Tre condizioni di ricerca fondamentali ( chiamate predicati
nello standard ANSI/ISO ) sono descritte di seguito:
- Test di confronto : confronta i valori i valori di due espressioni SQL per ciascuna riga di
dati. L’espressione può essere semplice (ad es. una costante) o costituita da espressioni
aritmetiche più complesse ( =,<>, < , < =, >, >= )
Es.:
SELECT NAME
FROM SALESREPS
WHERE HIRE_DATE < ’01-JAN-88’
Trova i venditori assunti prima del 1988.
Esistono 3 risultati possibili:
- TRUE, se il confronto è vero
- FALSE, se il confronto è vero
- NULL; se una delle due espressioni genera un valore NULL, il confronto genera un
risultato NULL.
Solo le righe per cui la condizione di ricerca genera un risultato TRUE sono inserite nei
risultati di query.
-
Test di intervallo: controlla se il valore di un’espressione appartiene ad un intervallo.
Di seguito un esempio che utilizza il test di intervallo:
Trova gli ordini che ricadono nell’intervallo di quantità [20000.00, 29999.99]
SELECT ORDER_NUM, AMOUNT
FROM ORDERS
WHERE AMOUNT BETWEEN 20000.00 AND 29999.99
Ovviamente i tipi dei dati delle tre espressioni devono essere confrontabili.
E’ possibile controllare se un certo valore non appartiene ad un intervallo utilizzando la
parola chiave NOT prima di BETWEEN.
SELECT ORDER_NUM, AMOUNT
FROM ORDERS
WHERE AMOUNT NOT BETWEEN 20000.00 AND 29999.99
Si osservi che il test BETWEEN può essere espresso come due test di confronto
Infatti:
A BETWEEN B AND C
( A >= B ) AND ( A <= C )
Sebbene il test BETWEEN non aggiunge molto alla potenza espressiva di SQL, tale test
rappresenta un modo più semplice per esprimere una condizione di ricerca quando si
pensa ad esso in termini di un intervallo di valori.
6
Lezione del 31 Marzo 2003 - Docente : Elisabetta Splendori
-
Test di appartenenza a un set: controlla se il valore di un’espressione corrisponde a un
valore contenuto in set di valori indicati.
Di seguito un esempio che utilizza il test di appartenenza ad un set.
Trova tutti gli ordini effettuati con quattro particolari venditori.
SELECT ORDER_NUM, REP, AMOUNT
FROM ORDERS
WHERE REP IN (107, 109, 101, 103)
E’ possibile controllare se i valori non corrispondono ai valori di destinazione utilizzando la
forma NOT IN.
Sebbene la parola chiave IN non aggiunge nulla alle potenzialità del linguaggio, essa fornisce
un modo più efficace per esprimere la condizione di ricerca, in particolare se il set contiene
molti valori.
Condizioni di ricerca composte (AND, OR e NOT)
Le semplici condizioni di ricerca appena descritte restituiscono un valore TRUE, FALSE o NULL
quando sono applicate a una riga di dati. E’ possibile combinare semplici condizioni (si applicano a
una riga di dati ) di ricerca SQL per crearne di più complesse.
La parola chiave OR viene utilizzata per combinare due condizioni di ricerca quando deve essere
vera una o l’altra o entrambe:
Trova i venditori che sono sotto l’obiettivo o con vendite inferiori a $300.000.
SELECT NAME, QUOTA, SALES
FROM SALESREPS
WHERE SALES < QUOTA
OR SALES < 300000.00
La parola chiave AND viene utilizzata per combinare due condizioni di ricerca che devono essere
entrambe vere:
Trova i venditori che sono sotto l’obiettivo e con vendite inferiori a $300.000.
SELECT NAME, QUOTA, SALES
FROM SALESREPS
WHERE SALES < QUOTA
AND SALES < 300000.00
Infine, é possibile utilizzare la parola chiave NOT per selezionare righe in cui una condizione di
ricerca è falsa:
Trova i venditori che sono sotto l’obiettivo, ma le cui vendite non siano inferiori a
$150.000.
7
Lezione del 31 Marzo 2003 - Docente : Elisabetta Splendori
SELECT NAME, QUOTA, SALES
FROM SALESREPS
WHERE SALES < QUOTA
AND NOT SALES < 150000.00
Utilizzando le parole chiave logiche AND, OR, e NOT e le parentesi per raggruppare i criteri di
ricerca, è possibile realizzare criteri di ricerca molti complessi.
Lo standard ANSI/ISO specifica che NOT ha la priorità più alta, seguita da AND e quindi da OR.
Per garantire la portabilità, è sempre meglio utilizzare le parentesi ed eliminare qualsiasi possibile
ambiguità.
Di seguito sono illustrate le tabelle di verità per gli operatori sopra citati.
Tabella di verità di AND
AND
TRUE
FALSE NULL
TRUE
TRUE
FALSE NULL
FALSE FALSE FALSE FALSE
NULL
NULL
FALSE NULL
Tabella di verità di OR
OR
TRUE FALSE NULL
TRUE
TRUE TRUE
TRUE
FALSE TRUE FALSE NULL
NULL
TRUE NULL
NULL
Tabella di verità di NOT
NOT TRUE
FALSE
FALSE
NULL
TRUE
NULL
Alla logica fornita da AND, OR e NOT lo standard SQL2 aggiunge un’altra condizione di ricerca
logica, il test IS. IS controlla se il valore logico di un’espressione o di un test di confronto è TRUE,
FALSE o UNKNOW (NULL).
Per esempio, il test IS:
((SALES - QUOTA) – 10000.00) IS FALSE
seleziona le righe in cui SALES non sono molto al di sopra di QUOTA.
8
Lezione del 31 Marzo 2003 - Docente : Elisabetta Splendori
ORDER BY
Si può chiedere a SQL di ordinare i risultati di una query inserendo la proposizione ORDER BY
nella dichiarazione SELECT.
La proposizione ORDER BY è costituita dalla parola chiave ORDER BY seguita da un elenco di
specifiche di ordinamento separate da virgole:
ORDER BY <nome colonna | numero colonna > [,<nome colonna | numero colonna >…]
[ASC | DESC]
Per esempio, i risultati della query che segue sono ordinati su due colonne, REGION e CITY:
Mostra le vendite di ciascun ufficio in ordine alfabetico e in ogni regione di città.
SELECT CITY, REGION, SALES
FROM OFFICES
ORDER BY REGION, CITY
La prima specifica di ordinamento ( REGION) è la chiave di ordinamento principale mentre quelle
che seguono sono chiavi di ordinamento sempre “più“ secondarie, utilizzate come “spareggio”
quando due righe di risultati di query hanno gli stessi valori per più chiavi principali.
E’ possibile avere due tipi di ordinamento: l’ordinamento ascendente e l’ordinamento discendente.
Per impostazione predefinita, SQL ordina i dati in modo ascendente; per l’ordinamento discendente
viene inserita la parola chiave DESC nella specifica di ordinamento, come nell’esempio che segue:
Elenca gli uffici in ordine discendente di vendite, in modo che gli uffici con le vendite maggiori
siano all’inizio.
SELECT CITY, REGION, SALES
FROM OFFICES
ORDER BY SALES DESC
Se la colonna di risultati di query da utilizzare per l’ordinamento è una colonna calcolata, non ha un
nome di colonna da utilizzare in una specifica di ordinamento. In tal caso è necessario specificare un
numero di colonna e non un nome di colonna, come nell’esempio che segue:
Elenca gli uffici in ordine discendente di prestazioni di vendite, in modo che gli uffici con le
prestazioni maggiori siano all’inizio.
SELECT CITY, REGION, ( SALES - TARGET )
FROM OFFICES
ORDER BY SALES
UNION
A volte è necessario combinare i risultati di due o più query in una singola tabella di risultati di
query. SQL supporta questa possibilità attraverso la funzionalità UNION della dichiarazione
SELECT.
9
Lezione del 31 Marzo 2003 - Docente : Elisabetta Splendori
Vediamo come utilizzare l’operazione UNION per soddisfare la richiesta che segue:
Elenca tutti i prodotti in cui il prezzo del prodotto supera $2000 o per cui sono stati ordinati prodotti
per un importo superiore a 30000$ con un singolo ordine.
La prima parte della richiesta può essere soddisfatta con la query superiore:
Elenca tutti i prodotti il cui prezzo supera i $2000.
SELECT MFR_ID, PRODUCT_ID
FROM PRODUCTS
WHERE PRICE > 2000.00
MFR_ID
PRODUCT_ID
________________________
ACI
REI
ACI
REI
4100Y
2A44L
4100Z
2A4AR
Analogamente, la seconda parte della richiesta può essere soddisfatta con la query inferiore:
Elenca tutti i prodotti per cui è stato ordinato un importo superiore a 30.000$ di prodotto in un solo
ordine.
SELECT DISTINCT MFR, PRODUCT
FROM ORDERS
WHERE AMOUNT > 30000.00
MFR
PRODUCT
________________________
IMM
REI
REI
775C
2A44L
2A4AR
Consideriamo ora l’unione delle due query sopra descritte; la dichiarazione SELECT che specifica
l’operazione UNION è come la seguente:
SELECT MFR_ID, PRODUCT_ID
FROM PRODUCTS
WHERE PRICE > 2000.00
UNION
SELECT DISTINCT MFR, PRODUCT
FROM ORDERS
WHERE AMOUNT > 30000.00
10
Lezione del 31 Marzo 2003 - Docente : Elisabetta Splendori
Elenca tutti i prodotti in cui il prezzo del prodotto supera $2000 o per cui sono stati ordinati prodotti
per un importo superiore a 30000$ con un singolo ordine.
Viene generata una singola tabella di risultati di query che combina le righe dei risultati della query
superiore con le righe dei risultati della query inferiore.
ACI
ACI
IMM
REI
REI
4100Y
4100Z
775C
2A44L
2A4AR
Per impostazione predefinita, l’operazione UNION elimina le righe duplicate durante l’elaborazione.
Se si desidera che le righe duplicate siano mantenute è possibile specificare la parola chiave ALL
immediatamente dopo la parola chiave UNION.
Esistono dei limiti severi sulle tabelle che possono essere combinate da un’operazione UNION:
- le due tabelle devono contenere lo stesso numero di colonne
- il tipo di dati di ciascuna colonna nella prima tabella deve essere lo stesso tipo di dati della
colonna corrispondente nella seconda tabella
- nessuna delle due tabelle può essere ordinata con la proposizione ORDER BY, ma i risultati
di query possono essere ordinati.
Si noti che i nomi di colonna delle due query combinate da UNION non devono essere identici.
Nell’esempio precedente la prima tabella di risultati di query ha colonne chiamate MFR_ID e
PRODUCT_ID, mentre la seconda tabella di risultati di query ha colonne chiamate MFR e
PRODUCT. Poiché le colonne nelle due tabelle possono avere nomi diversi, le colonne di risultati di
query generati dall’operazione UNION non hanno nome.
Lo standard SQL ANSI/ISO specifica un’ulteriore restrizione su una dichiarazione SELECT che
partecipa a un’operazione UNION: ammette solo nomi di colonne o una specifica per tutte le
colonne (SELECT *) nell’elenco di selezione e non ammette espressioni nell’elenco di selezione.
11