L
Structured Query Language
Abbiamo inserito i dati nel DB,
sappiamo che le informazioni sono nel
sistema informatico, impariamo a
cercarle senza l’aiuto degli informatici.
1
1.1
Breve storia di SQL
In lavorazione
….segue introduzione a SQL
alcune spiegazioni sulle tabelle (Unione – intersezione)
Implementazioni (odbc – jdbc)
Domande e risposte
2
E
2.1
C
T
Obiettivi:
Scrivere una query in linguaggio SQL
Selezionare ed elencare tutte le righe e
le colonne di una tabella
Selezionare ed elencare determinate
colonne di una tabella
Selezionare ed elencare le colonne di più
tabelle
3
2.2
SELECT: regole sintattiche generali
Anche se la parola query può essere tradotta in
interrogazione o domanda, una query SQL non è
necessariamente una domanda, può essere un
comando per svolgere una delle seguenti operazioni:
Creare o cancellare una tabella
Inserire, modificare o cancellare campi
Ricercare informazioni specifiche in più tabelle e
restituire i risultati in un particolare ordine
Modificare i parametri di protezione di un
database
4
2.3
SELECT: regole sintattiche generali
E’ un linguaggio abbastanza flessibile, i
comandi possono essere scritti sia in
maiuscolo che in minuscolo
SELECT NOME, INDIRIZZO, CITTA
FROM PRESIDENTI
WHERE NOME = ‘LINCOLN’;
Select nome, indirizzo, citta
From presidenti
Where nome = ‘LINCOLN’;
I riferimenti ai contenuti di un database devono essere
scritti esattamente come sono stati registrati.
5
2.3
SELECT: regole sintattiche generali
Select nome, indirizzo, citta, from presidenti where nome =
‘LINCOLN’;
Anche questa query è corretta, tuttavia usando con cura la
spaziatura e le lettere minuscole/maiuscole, è
possibile rendere più leggibili le istruzioni:
NOME, INDIRIZZO, CITTA
FROM PRESIDENTI
WHERE NOME = ‘LINCOLN’;
Il ; posto al termine della query serve a segnalare la fine delle istruzioni
6
2.3
SELECT: regole sintattiche generali
Gli elementi importanti di una query sono le parole
chiave ovvero le parole del linguaggio SQL che
sono riservate come parte della sintassi.
Negli esempi precedenti le parole chiavi sono:
SELECT
FROM
WHERE
Consultare il manuale della versione in uso per
conoscere tutte le parole chiave riservate
7
2.4
INFN Laboratori Nazionali di Frascati
SELECT <NOMI DI COLONNE>
FROM <TABELLA>
SQL> SELECT;
SELECT
*
ERROR at line 1;
ORA-00936: missing expression
Parola chiave - istruzione – clausola
La parola chiave si riferisce ai singoli elementi del linguaggio
(SELECT e FROM), una clausola è parte di un’istruzione SQL;
per esempio SELECT colonna1, colonna2, … è una clausola.
Le clausole si combinano per formare un’istruzione completa.
Per esempio è possibile combinare una clausola SELECT e
una clausola FROM per scrivere un’istruzione SQL
SELECT e FROM sono le parole chiave più usate, indispensabili per
stabilire una connessione al computer mirata a leggere i dati
8
2.6
INFN Laboratori Nazionali di Frascati
la prima query
Num
BENEFICIARIO
IMPORTO
1
Computer shop
1.500,00
Dischetti e CD ROM
2
Librerie Riunite
2.453,40
Libri, cancelleria
3
Computer Shop
2.003,20
Telefono cellualare
4
BIOGA srl
5
Supermarket GC
6
Assicurazioni Italy
250,00
Assicurazione casa
7
GAS S.p.A.
251,00
Gas
980,00
1.500,00
NOTE
GAS
Alimentari
SQL> select * from assegni;
Num BENEFICIARIO
1
Computer shop
2
Librerie Riunite
3
Computer Shop
4
BIOGA srl
5
Supermarket GC
6
Assicurazioni Italy
7
GAS S.p.A.
AS/400
IMPORTO
1.500,00
2.453,40
2.003,20
980,00
1.500,00
250,00
251,00
NOTE
Dischetti e CD ROM
Libri, cancelleria
Telefono cellualare
GAS
Alimentari
Assicurazione casa
Gas
L’asterisco (*) di select * indica al database di fornire
TUTTE le colonne associate alla tabella
9
2.6
INFN Laboratori Nazionali di Frascati
Cambiare l’ordine delle colonne
E’ possibile indicare le singole colonne che verranno elencate nello stesso
ordine indicato:
SQL> SELECT beneficiario, note, num, importo from assegni
BENEFICIARIO
Computer shop
Librerie Riunite
Computer Shop
BIOGA srl
Supermarket GC
Assicurazioni Italy
GAS S.p.A.
NOTE
Dischetti e CD ROM
Libri, cancelleria
Telefono cellualare
GAS
Alimentari
Assicurazione casa
Gas
Num
1
2
3
4
5
6
7
IMPORTO
1.500,00
2.453,40
2.003,20
980,00
1.500,00
250,00
251,00
La virgola separa le singole colonne ed è sempre possibile scrivere le
istruzioni su più righe
10
2.6
INFN Laboratori Nazionali di Frascati
Selezionare le colonne
Per selezionare solo alcune colonne basta indicarle, solo quelle dichiarate
verrano elencate nell’ordine indicato:
SQL> SELECT num, importo from assegni
Num
1
2
3
5
6
4
7
IMPORTO
1.500,00
2.453,40
2.003,20
1.500,00
250,00
980,00
251,00
Sono state indicate istruzioni sia in maiuscolo che in minuscolo, come
si può notare il risultato è lo stesso.
AS/400
11
2.6
INFN Laboratori Nazionali di Frascati
Selezionare tabelle differenti
Per selezionare dati da un’altra tabella è sufficiente modificare la clausola
FROM
SQL> SELECT * from depositi
DEPOSITO
1
2
3
CHIHAPAGATO
Zio ricco
Mia azienda
Trasporti S.r.L.
IMPORTO
2000,00
2.453,40
250,00
NOTE
regalo di Natale
Stipendio
Affitto garage
Vengono elencate tutte le colonne della tabella DEPOSITI con le righe
ordinate direttamente dal database
AS/400
12
2.7
INFN Laboratori Nazionali di Frascati
Query senza duplicati
Esaminando il contenuto della tabella assegni
alucni importi sono duplicati:
SQL> SELECT importo from assegni
Per elencare gli importi senza duplicazioni occorre
la clausola DISTINCT
SQL> SELECT DISTINCT importo
from assegni
IMPORTO
1.500,00
2.453,40
2.003,20
980,00
250,00
251,00
IMPORTO
1.500,00
2.453,40
2.003,20
980,00
1.500,00
250,00
251,00
Con la clausola DISTINCT l’output riporta una sola
istanza dei dati duplicati
AS/400
13
2.8
INFN Laboratori Nazionali di Frascati
Riepilogo
Le parole SELECT e FROM consentono a una query di
caricare dei dati.
E’ possibile creare una query generica e includere tutte
le colonne con l’istruzione SELECT *. E’ anche possibile
selezionare solo alcune colonne e anche modificare
l’ordine in cui devono essere presentate.
La parola chiave DISTINCT limita l’output delle query
poiché consente di escludere i valori duplicati di una
colonna.
14
2.9
INFN Laboratori Nazionali di Frascati
Quesiti
Le seguenti query non funzionano. Perché?
a. Select *
b. Select * from assegni
c. Select importo numAssegno beneficiario FROM
assegni;
Quale delle seguenti istruzioni SQL è corretta?
a. select *
from assegni;
b. select * from assegni;
c. select * from assegni /
Le seguenti istruzioni forniscono lo stesso risultato?
SELECT * FROM ASSEGNI
select * from assegni;
AS/400
15
2.10
INFN Laboratori Nazionali di Frascati
Esercizi
1. Utilizzando la tabella ASSEGNI scrivere una query per
selezionare soltanto i numeri e le note degli assegni.
2. Riscrivere la query del precedente esercizio in modo che le
note figurino come prima colonna del risultato della query.
3. Utilizzando la tabella ASSEGNI scrivere una query per ottenere
tutte le note uniche (escludendo quelle duplicate)
AS/400
16
3.1
INFN Laboratori Nazionali di Frascati
Espressioni, condizioni e operatori
Obiettivi
Approfondire le clausole SELECT e FROM, ampliare la
query con qualche nuovo termine e introdurre gli
operatori.
In particolare impareremo a:
Capire cos’è una espressione e come si utilizza
Capire cos’è una condizione e come si utilizza
Familiarizzare con la clausola WHERE
Imparare ad usare gli operatori aritmetici, di
confronto, di caratteri, logici e di insiemi
Conoscere altri utili operatori
17
3.2
INFN Laboratori Nazionali di Frascati
Espressioni
La definizione di espressione è semplice:
un’espressione restituisce un valore
Esistono vari tipi di espressioni in modo da poterle adattare
ai differenti tipi di dati, come le stringhe, i valori
numerici e booleani. Nell’esempio che segue importo è
un’espressione che fornisce il valore della colonna
importo:
SELECT importo from assegni;
AS/400
18
3.2
INFN Laboratori Nazionali di Frascati
Espressioni
Nella seguente istruzione, NOME, INDIRIZZO, TELEFONO E
RUBRICA sono espressioni:
SELECT NOME, INDIRIZZO, TELEFONO, RUBRICA
FROM RUBRICA;
La seguente espressione:
WHERE NOME = ‘ROSSI’
contiene una condizione di una espressione booleana. Questa
condizione potrà essere TRUE (vera) o FALSE (falsa)
rispettivamente se la colonna NOME contiene ROSSI oppure no.
19
3.3
Condizioni
Tutte le volte che si vuole trovare un particolare
elemento o gruppo di elementi in un database,
occorre specificare una o più condizioni.
Le condizioni sono introdotte dalla clausola WHERE.
Nell’esempio precedente la condizione è NOME = ‘ROSSI’
Per trovare tutti gli impiegati che hanno lavorato più di 100
ore la condizione potrebbe essere: NUMERODIORE > 100
20
3.3
INFN Laboratori Nazionali di Frascati
Condizioni
Le condizioni consentono di effettuare query selettive. Nella
forma più comune includono una variabile, una costante e
un operatore di confronto.

Variabile………………………….. NOME

Costante…………………………..’ROSSI’

Operatore di confronto………. >
Per scrivere una query condizionale bisogna conoscere la
clasuola WHERE e gli operatori
21
3.3
INFN Laboratori Nazionali di Frascati
La clausola WHERE
La sintassi della clausola WHERE è la seguente:
WHERE <condizione di ricerca>
La clausola WHERE rende selettive le query, senza questa clausola
la query visualizzerebbe tutti i record della tabella:
SQL> SELECT * FROM BICICLETTE;
NOME
TELAIO
MATERIALE
Km percorsi
tipo
TREK 2300
57
FIBRA DI CARBONIO
3500
CORSA
GIANT
56
ACCIAIO
2000
TANDEM
BIANCHI
53
ACCIAIO
1500
PASSEGGIO
COLNAGO
54
ACCIAIO
500
VIAGGIO
COPPI
53
ACCIAIO
100
MOUNTAIN
DE ROSA
57
ALLUMINIO
300
CORSA
22
3.3
INFN Laboratori Nazionali di Frascati
La clausola WHERE
Per selezionare una particolare bicicletta si può usare la
seguente query:
SELECT * FROM BICICLETTE WHERE NOME = ‘GIANT’;
che fornisce un solo record:
NOME
GIANT
TELAIO
56
MATERIALE
ACCIAIO
Km percorsi
2000
tipo
TANDEM
Questo semplice esempio mostra come applicare una
condizione sui dati da caricare
23
3.4
INFN Laboratori Nazionali di Frascati
Operatori
Gli operatori sono gli elementi utilizzati all’interno
delle espressioni per specificare le condizioni
necessarie a caricare i dati.
Possono essere divisi in sei gruppi:






aritmetici
di confronto
di caratteri
logici
di insieme
vari
E’ un potente gruppo di strumenti a base della conoscenza
del linguaggio SQL
24
3.4
INFN Laboratori Nazionali di Frascati
Operatori aritmetici
1. + (somma)
2. - (sottrazione)
3. / (divisione)
4.
5.
* (moltiplicazione)
% (modulo o resto)
I primi quattro operatori si spiegano da soli.
L’operatore modulo restituisce il resto di una divisione. Ad
esempio:
5%2=1
6%2=0
Non funziona con i tipi di dati che hanno cifre decimali
25
3.4
INFN Laboratori Nazionali di Frascati
Operatori aritmetici
Se vengono inseriti più operatori aritmetici in una
espressione senza parentesi, essi vengono valutati
nell’ordine: moltiplicazione, divisione, modulo, somma
e sottrazione. Ad esempio:
2*6+9/3
vale
12 + 3 = 15
vale
2 * 15 / 3 = 10
mentre l’espressione
2 * (6 + 9) / 3
Attenzione alla posizione delle parentesi!
26
3.4
L’operatore somma (+)
INFN Laboratori Nazionali di Frascati
Può essere usato in vari modi. La seguente espressione
visualizza il contenuto della tabella PREZZO:
SQL> SELECT * FROM PREZZO
SQL> SELECT ELEMENTO, PREZZOINGROSSO,
PREZZOINGROSSO + 150 FROM PREZZO
ELEMENTO
PREZZOINGROSSO
ELEMENTO
PREZZOINGROSSO
Pomodori
3,40
Pomodori
3,40
4,90
Patate
5,10
Patate
5,10
6,60
Banane
6,70
Banane
6,70
8,20
Rape
4,50
Rape
4,50
6,00
Arance
8,90
Arance
8,90
10,40
Mele
2,30
Mele
2,30
4,80
PREZZOINGROSSO + 150
La terza colonna (PREZZOINGROSSO + 150) non si trova nella tabella
originale (in entrambi i casi sono state selezionate con il carattere * tutte
le colonne).
SQL consente di creare colonne virtuali o derivate combinando o modificando le
colonne esistenti.
27
3.4
INFN Laboratori Nazionali di Frascati
L’operatore somma (+)
E’ possibile assegnare una intestazione più
comprensibile alla nuova colonna:
SQL> SELECT ELEMENTO, PREZZOINGROSSO,
(PREZZOINGROSSO + 1.50) PREZZODETTAGLIO FROM PREZZO
ELEMENTO PREZZOINGROSSO
PREZZODETTAGLIO
Pomodori
3,40
4,90
Patate
5,10
6,60
Banane
6,70
8,20
Rape
4,50
6,00
Arance
8,90
10,40
Mele
2,30
3,80
28
3.4
INFN Laboratori Nazionali di Frascati
L’operatore somma (+)
E’ possibile assegnare una intestazione più
comprensibile anche a colonne esistenti:
SQL> SELECT ELEMENTO PRODOTTO, PREZZOINGROSSO,
(PREZZOINGROSSO + 1.50) PREZZODETTAGLIO FROM PREZZO
PRODOTTO
PREZZOINGROSSO
PREZZODETTAGLIO
Pomodori
3,40
4,90
Patate
5,10
6,60
Banane
6,70
8,20
Rape
4,50
6,00
Arance
8,90
10,40
Mele
2,30
3,80
La colonna ELEMENTO è stata ridenominata PRODOTTO.
E’ comunque consigliabile consultare la documentazione della propria
implementazione SQL per conoscere la sintassi esatta.
29
3.4
INFN Laboratori Nazionali di Frascati
L’operatore sottrazione (-)
L’operatore meno svolge due funzioni, la prima è quella di
cambiare segno ad un numero:
SQL> SELECT * FROM MINMAX
REGIONE
TEMPMAX
TEMPMIN
Piemonte
-4
10
Toscana
4
13
Sicilia
10
19
Lombardia
-2
9
Friuli
-3
8
SQL> SELECT REGIONE, -TEMPMAX, -TEMPMIN
FROM MINMAX
REGIONE
TEMPMAX
TEMPMIN
Piemonte
4
-10
Toscana
-4
-13
-10
-19
Lombardia
2
-9
Friuli
3
-8
Sicilia
30
3.4
L’operatore sottrazione (-)
INFN Laboratori Nazionali di Frascati
La seconda (e ovvia) funzione dell’operatore meno è quella di
sottrarre i valore di una colonna da quelli di un’altra colonna.
Esempio:
SQL> SELECT REGIONE,
TEMPMAX MINIME,
TEMPMIN MASSIME,
(TEMPMIN - TEMPMAX) DIFFERENZA
FROM MINMAX;
REGIONE
MINIME
MASSIME
DIFFERENZE
Piemonte
-4
10
14
Toscana
4
13
9
Sicilia
10
19
9
Lombardia
-2
9
11
Friuli
-3
8
11
Oltre che aver creato la nuova colonna questa query ha corretto (solo
sullo schermo) i nomi di quelle errate.
31
3.4
INFN Laboratori Nazionali di Frascati
L’operatore sottrazione (-)
Se venisse utilizzato accidentalmente l’operatore meno su un
campo caratteri si otterrà un messaggio simile a questo:
SQL> SELECT – REGIONE FROM MINMAX
ERROR:
ORA-01722: invalid number
no row selected
Il testo del messaggio varia da un’implementazione all’altra, ma il risultato è
lo stesso
32
3.4
L’operatore divisione (/)
INFN Laboratori Nazionali di Frascati
L’operatore divisione ha un solo significato, per vedere gli effetti
di una vendita a metà prezzo basta digitare la seguente
istruzione:
SQL> SELECT ELEMENTO PRODOTTO, PREZZOINGROSSO,
(PREZZOINGROSSO/2) PREZZOVENDITA FROM PREZZO
ELEMENTO PREZZOINGROSSO
PREZZOVENDITA
Pomodori
3,40
1,70
Patate
5,10
2,55
Banane
6,70
3,35
Rape
4,50
2,25
Arance
8,90
4,45
Mele
2,30
1,15
L’uso dell’operatore divisione in questa istruzione SELECT è semplice, anche se
vendere a metà prezzo non è proprio convienente.
33
3.4
INFN Laboratori Nazionali di Frascati
L’operatore moltiplicazione (*)
Anche l’’operatore moltiplicazione è semplice da usare, ad
esempio questa query visualizza l’effetto di uno sconto del 10%
sui prezzi di tutti i prodotti:
SQL> SELECT ELEMENTO PRODOTTO, PREZZOINGROSSO,
(PREZZOINGROSSO*0.9) NUOVOPREZZO FROM PREZZO;
ELEMENTO
PREZZOINGROSSO
NUOVOPREZZO
Pomodori
3.40
3.06
Patate
5.10
4.59
Banane
6.70
6.03
Rape
4.50
4.05
Arance
8.90
8.01
Mele
2.30
2.07
Questi operatori permettono di svolgere con grande efficienza calcoli
aritmetici nell’istruzione SELECT
34
3.4
L’operatore modulo (%)
L’operatore modulo restituisce il resto intero di una operazione di
divisione. Esempio:
SQL> SELECT * FROM
RESTI
NUMERATORE
DENOMINATORE
10
5
8
3
23
9
1024
16
E’ possibile creare una nuova colonna, RESTO, dove registrare il resto
della divisione tra NUMERATORE e DENOMINATORE
SQL> SELECT NUMERATORE, DENOMINATORE,
NUMERATORE%DENOMINATORE RESTO
FROM RESTI
NUMERATORE
DENOMINATORE
RESTO
10
5
0
8
3
2
23
9
5
1024
16
0
35
3.4
INFN Laboratori Nazionali di Frascati
Operatori di confronto
Questi operatori confrontano le espressioni e restituiscono uno di questi
tre valori: TRUE, FALSE, Unkown. I primi due sono semplici da
spiegare, TRUE significa vero e FALSE significa falso, il terzo,
Unknow, identifica l’assenza di dati in una colonna, cioè NULL.
Molte implementazioni SQL cambiano Unknown in FALSE e forniscono un
operatore speciale, IS NULL, per verificare la condizione NULL (assenza
di dati).
SQL> SELECT * FROM PREZZO WHERE PREZZOINGROSSO = NULL;
No row selected
SQL> SELECT * FROM PREZZO WHERE PREZZOINGROSSO IS NULL;
ELEMENTO PREZZOINGROSSO
Limoni
Nel database la colonna prezzoingrosso della riga Limoni non contiene
dati (non è zero)
36
3.4
L’operatore (=)
INFN Laboratori Nazionali di Frascati
Nella clausola WHERE il segno uguale è l’operatore di confronto
più utilizzato, molto comodo per selezionare un valore tra tanti.
SQL> SELECT * FROM AMICI;
COGNOME
NOME
CITTA
TELEFONO
ROSSI
ALE
MILANO
02 3425678
BIANCHI
SABY
TORINO
011 6707221
BROWN
JO
PISA
NERI
ALE
BOLOGNA
050 880245
051 6711
SQL> SELECT * FROM AMICI WHERE NOME = ‘ALE’;
COGNOME
NOME
CITTA
TELEFONO
ROSSI
ALE
MILANO
02 3425678
NERI
ALE
BOLOGNA
051 6711
SQL> SELECT * FROM AMICI WHERE NOME = ‘Ale’;
no row selected.
37
3.4
INFN Laboratori Nazionali di Frascati
L’ operatore maggiore (>) e maggiore uguale (>=)
questi operatori operano nel seguente modo modo:
SQL> SELECT * FROM PREZZO;
ELEMENTO
PREZZOINGROSSO
Pomodori
3.40
Patate
5.10
Banane
6.70
Rape
4.50
Arance
8.90
Mele
2.30
SQL> SELECT * FROM PREZZO
WHERE PREZZOINGROSSO > 4.50;
ELEMENTO
PREZZOINGROSSO
Patate
5.10
Banane
6.70
Arance
8.90
SQL> SELECT * FROM PREZZO
WHERE PREZZOINGROSSO >= 4.50;
ELEMENTO
PREZZOINGROSSO
Patate
5.10
Banane
6.70
Rape
4.50
Arance
8.90
Non si usano apici per racchiudere il numero 4.50
38
3.4
INFN Laboratori Nazionali di Frascati
L’operatore maggiore (<) e maggiore uguale (<=)
questi operatori operano in senso inverso al precedente:
SQL> SELECT * FROM AMICI;
COGNOME
NOME
CITTA
TELEFONO
ROSSI
ALE
MILANO
02 3425678
BIANCHI
SABY
TORINO
011 6707221
BROWN
JO
PISA
NERI
ALE
BOLOGNA
050 880245
051 6711
SQL> SELECT * FROM AMICI
WHERE CITTA < ‘MILANO’ ;
COGNOME
NOME
CITTA
TELEFONO
BROWN
JO
PISA
050 880245
NERI
ALE
BOLOGNA
051 6711
Ovviamente è possibile applicarli anche a valori alfabetici,
per la sequenza corretta consultare la propria implementazione SQL
39
3.4
INFN Laboratori Nazionali di Frascati
L’operatore di diseguaglianza (<> o !=)
Consente di trovare dati escludendone altri,
cioè il simbolo (<>) oppure (!=) si legge “diverso da”.
Per trovare gli amici tranne ALE (cioè con il nome diverso da ALE):
SQL> SELECT * FROM AMICI
WHERE NOME <> ‘ALE’;
COGNOME
NOME
CITTA
BIANCHI
SABY
TORINO
BROWN
JO
PISA
NERI
ALE
BOLOGNA
TELEFONO
011 6707221
050 880245
051 6711
In molte implementazione SQL è indifferente usare la forma (<>) anzichè (!=)
40
3.4
Gli operatori di carattere
INFN Laboratori Nazionali di Frascati
Consentono di manipolare il modo in cui debbono essere rappresentate le
stringhe durante la preparazione delle condizioni che selezionano i dati.
L’operatore LIKE
consente di estrarre dati che somigliano ad un certo schema
NOME
POSIZIONE
NUMEROPARTE
FEGATO
DESTRA-ADDOME
1
CUORE
PETTO
2
FARINGE
GOLA
3
VERTEBRE
CENTRO-DORSO
4
INCUDINE
ORECCHIO
5
RENE
DORSO
6
Come fare a trovare tutte le parti che si trovano nella zona dorsale del corpo?
Osservando la tabella è possibile individuarne due, ma hanno nomi differenti.
41
3.4
INFN Laboratori Nazionali di Frascati
L’operatore LIKE
NOME
POSIZIONE
NUMEROPARTE
FEGATO
DESTRA-ADDOME
1
CUORE
PETTO
2
FARINGE
GOLA
3
VERTEBRE
CENTRO-DORSO
4
INCUDINE
ORECCHIO
5
RENE
DORSO
6
SQL> SELECT * FROM PARTI WHERE POSIZIONE LIKE ‘%DORSO%’;
NOME
POSIZIONE
NUMEROPARTE
VERTEBRE
CENTRO-DORSO
4
RENE
DORSO
6
SQL> SELECT * FROM PARTI WHERE POSIZIONE LIKE ‘DORSO%’;
NOME
POSIZIONE
RENE
DORSO
NUMEROPARTE
6
SQL> SELECT * FROM PARTI WHERE POSIZIONE LIKE ‘%DORSO’;
NOME
POSIZIONE
VERTEBRE
CENTRO-DORSO
NUMEROPARTE
4
42
3.4
INFN Laboratori Nazionali di Frascati
L’operatore LIKE
NOME
POSIZIONE
NUMEROPARTE
FEGATO
DESTRA-ADDOME
1
CUORE
PETTO
2
FARINGE
GOLA
3
VERTEBRE
CENTRO-DORSO
4
INCUDINE
ORECCHIO
5
RENE
DORSO
6
SQL> SELECT * FROM PARTI WHERE NOME LIKE ‘F%’;
NOME
POSIZIONE
NUMEROPARTE
FEGATO
DESTRA-ADDOME
1
FARINGE
GOLA
3
SQL> SELECT * FROM PARTI WHERE POSIZIONE LIKE ‘f%’;
no rows selected.
L’operatore like è sempre sensibile ai caratteri minuscoli/maiuscoli
43
3.4
INFN Laboratori Nazionali di Frascati
Il carattere di sottolineatura (_)
Il carattere di sottolineatura è un carattere jolly singolo.
COGNOME
NOME
CITTA
PROVINCIA
TELEFONO
ROSSI
ALE
MILANO
MI
02 3425678
BIANCHI
SABY
TORINO
TO
011 6707221
BROWN
JO
PISA
PI
050 880245
NERI
ALE
BOLOGNA
BO
051 6711
SQL> SELECT * FROM AMICI WHERE PROVINCIA LIKE ‘_O’;
COGNOME
NOME
CITTA
PROVINCIA
TELEFONO
BIANCHI
SABY
TORINO
TO
011 6707221
NERI
ALE
BOLOGNA
BO
051 6711
E’ possibile utilizzare più di un carattere di sottolineatura e può essere
combinato con l’operatore LIKE.
44
3.4
L’operatore di concatenazione (||)
INFN Laboratori Nazionali di Frascati
Il simbolo || serve a concatenare due stringhe:
COGNOME
NOME
CITTA
PROVINCIA
TELEFONO
ROSSI
ALE
MILANO
MI
02 3425678
BIANCHI
SABY
TORINO
TO
011 6707221
BROWN
JO
PISA
PI
050 880245
NERI
ALE
BOLOGNA
BO
051 6711
SQL> SELECT NOME || COGNOME NOMECOMPLETO FROM AMICI;
NOMECOMPLETO
ROSSI
ALE
BIANCHI
SABY
BROWN
JO
NERI
ALE
Se si utilizzasse il segno + per concatenare due stringhe, l’interprete SQL
fornirebbe un messaggio d’errore:
SQL> SELECT NOME + COGNOME NOMECOMPLETO FROM AMICI;
ERROR:
ORA-01722: invalid member
45
3.4
INFN Laboratori Nazionali di Frascati
Gli operatori logici
Negli esempi precedenti è stato effettuato sempre un controllo alla
volta. Questo metodo va bene per i casi semplici, ma come fareste a
trovare tutti quei dipendenti I cui nomi iniziano con la lettera P e che
hanno meno di tre giorni di ferie?
Gli operatori logici separano due o più condizioni nella clausola
WHERE di un’istruzione SQL.
Essi sono:
 AND
 OR
 NOT
46
3.4
INFN Laboratori Nazionali di Frascati
Esempio operatore AND
Si supponga che un’azienda conceda 12 giorni di ferie per ogni anno
lavorativo. In base a quanto detto sarà scritta una query per trovare tutti gli
impiegati i cui nomi iniziano con la lettera B ed hanno più di 50 giorni di ferie
da godere:
COGNOME
SQL>
NUMDIP
ANNI
FERIEGODUTE
ABITA
101
2
4
BACCHI
104
5
23
BLESSI
107
8
45
BOLIVAR
233
4
80
BOLDI
210
15
100
COSTALES
211
10
78
SELECT COGNOME,
ANNI * 12 – FERIEGODUTE [AS] FERIERESTANTI
FROM TABELLAFERIE
WHERE COGNOME LIKE ‘’B%’
AND
ANNI * 12 – FERIEGODUTE > 50;
COGNOME
FERIERESTANTI
BLESSI
51
BOLDI
80
47
3.4
INFN Laboratori Nazionali di Frascati
L’operatore AND
L’operatore logico AND indica che entrambe le espressioni che si trovano ai
suoi lati devono essere soddisfatte per restituire il valore TRUE (vero). Se una
solo delle espressioni non è soddisfatta l’operatore AND restituisce FALSE.
Ad esempio, per sapere quali impiegati hanno lavorato per l’azienda per 5
anni o meno ed hanno utilizzato più di 20 giorni di ferie, si può scrivere:
COGNOME
SQL>
SELECT *
FROM FERIE
WHERE ANNI <= 5
AND
FERIEGODUTE > 20;
COGNOME
NUMDIP
ANNI
FERIEGODUTE
ABITA
101
2
4
BACCHI
104
5
23
BLESSI
107
8
45
BOLIVAR
233
4
80
BOLDI
210
15
100
COSTALES
211
10
78
NUMDIP
ANNI
FERIEGODUTE
BACCHI
104
5
23
BOLIVAR
233
4
80
48
3.4
INFN Laboratori Nazionali di Frascati
L’operatore OR
L’operatore logico OR puo’ essere utilizzato per combinare una serie di
condizioni. Se una di queste è soddisfatta viene restituito TRUE
COGNOME
SQL>
SELECT *
FROM FERIE
WHERE COGNOME LIKE ‘BO%’
OR
COGNOME LIKE ‘CO%’;
COGNOME
NUMDIP
ANNI
FERIEGODUTE
ABITA
101
2
4
BACCHI
104
5
23
BLESSI
107
8
45
BOLIVAR
233
4
80
BOLDI
210
15
100
COSTALES
211
10
78
NUMDIP
ANNI
FERIEGODUTE
BOLDI
210
15
100
BOLIVAR
233
4
80
COSTALES
211
10
78
L’operatore OR richiede che una soltanto delle condizioni sia vera affinchè I
dati possano essere restitutiti
49
3.4
INFN Laboratori Nazionali di Frascati
L’operatore NOT
L’operatore logico NOT ha il compito di invertire il significato di una
condizione
COGNOME
SQL>
NUMDIP
ANNI
FERIEGODUTE
ABITA
101
2
4
BACCHI
104
5
23
BLESSI
107
8
45
BOLIVAR
233
4
80
BOLDI
210
15
100
211
10
78
COSTALES
SELECT *
FROM FERIE
WHERE COGNOME NOT LIKE ‘B%’;
COGNOME
NUMDIP
ANNI
FERIEGODUTE
BOLDI
210
15
100
BOLIVAR
233
4
80
COSTALES
211
10
78
L’operatore OR richiede che una soltanto delle condizioni sia vera affinchè I
dati possano essere restitutiti
50
3.4
INFN Laboratori Nazionali di Frascati
Gli operatori di insieme: UNION
L’operatore UNION restituisce il risultato di due query escludendo le righe
duplicate.
Esempio: quante persone diverse giocano in entrambe le squadre?
COGNOME
COGNOME
ABITA
ABITA
BRAVO
CARLINI
DECCA
SQL>
SELECT COGNOME FROM CALCETTO
UNION
SELECT COGNOME FROM CALCIO;
ESTERLE
FUNDARI
GIANI
calcetto
BACCO
CARLINI
DINI
ESTERLE
COGNOME
FALCONI
ABITA
GIANI
BACCO
calcio
BRAVO
CARLINI
DECCA
DINI
ESTERLE
Sono stati esclusi i doppioni
FALCONI
FUNDARI
GIANI
A C B
A+B-C
AS/400
51
3.4
Gli operatori di insieme: UNION ALL
INFN Laboratori Nazionali di Frascati
L’operatore UNION ALL restituisce il risultato di due query incluse
le righe duplicate.
Esempio: quante persone giocano al calcetto e quante al calcio?
SQL>
SELECT COGNOME FROM CALCETTO
UNION ALL
SELECT COGNOME FROM CALCIO;
COGNOME
ABITA
BRAVO
CARLINI
DECCA
ESTERLE
FUNDARI
GIANI
calcetto
COGNOME
A+B
ABITA
BRAVO
CRLINI
DECCA
ESTERLE
FUNDARI
GIANI
ABITA
BACCO
CARLINI
DINI
ESTERLE
Sono incluse tutte le righe
A C B
COGNOME
ABITA
BACCO
CARLINI
DINI
ESTERLE
FALCONI
GIANI
calcio
FALCONI
GIANI
AS/400
52
3.4
Gli operatori di insieme: INTERSECT
INFN Laboratori Nazionali di Frascati
L’operatore INTERSECT restituisce soltanto le righe che vengono
trovate in entrambe le query
Esempio: quali persone giocano in entrambe le squadre?
SQL>
SELECT COGNOME FROM CALCETTO
INTERSECT
SELECT COGNOME FROM CALCIO;
select * from corso.calcio
where cognome in (select cognome from corso.calcetto)
calcetto
A C B
calcio
COGNOME
COGNOME
ABITA
ABITA
BRAVO
BACCO
CARLINI
DECCA
ESTERLE
FUNDARI
GIANI
COGNOME
ABITA
CRLINI
ESTERLE
GIANI
CARLINI
DINI
ESTERLE
FALCONI
GIANI
C
Sono inclusi solo le righe comuni
NON AS/400
53
3.4
INFN Laboratori Nazionali di Frascati
Gli operatori di insieme: MINUS (differenza)
L’operatore MINUS restituisce le righe della prima query che non
sono presenti nella seconda
Esempio: quali persone giocano solo al calcetto?
SQL>
SELECT COGNOME FROM CALCETTO
MINUS
SELECT COGNOME FROM CALCIO;
COGNOME
COGNOME
ABITA
COGNOME
ABITA
BRAVO
BACCCO
BACCO
CARLINI
DINI
CARLINI
DECCA
FALCONI
DINI
ESTERLE
FUNDARI
ESTERLE
A C B
GIANI
calcetto
FALCONI
GIANI
A-C
calcio
Seleziona quelli che giocano solo al calcetto
NON AS/400
54
3.4
INFN Laboratori Nazionali di Frascati
Gli operatori di insieme: IN e BETWEEN
Forniscono la scorciatoia per operazioni che possono essere svolte
in altri modi, ad esempio:
Quali amici vivono in provincia di Como, Pavia e Bergamo?
SQL>
SELECT * FROM AMICI
WHERE PROVINCIA = ‘CO’
OR PROVINCIA = ‘PV’
OR PROVINCIA = ‘BG’;
In alternativa si può usare la seguente query:
SQL>
SELECT * FROM AMICI
WHERE PROVINCIA IN (‘CO’, ‘PV’, ‘‘BG’);
L’operatore IN può essere utilizzato anche per i numeri:
SQL>
SELECT * FROM DIPENDENTI
WHERE ETASERVIZIO IN (15, 25, 40);
Verrano selezionati I dipendenti che hanno ETASERVIZIO uguale
ad uno dei tre valori espressi
55
3.4
INFN Laboratori Nazionali di Frascati
L’operatore BETWEEN
Consente di definire come condizione un intervallo di valori.
Ad esempio per estrarre dalla tabella PREZZO quei prodotti i cui prezzi siano
compresi in un determinato intervallo, si può utilizzare la seguente query:
SQL>
SELECT * FROM PREZZO
WHERE PREZZOINGROSSO > 2.50
AND
ELEMENTO
PREZZOINGROSSO < 7.50;
PREZZOINGROSSO
Pomodori
3.40
Patate
5.10
Banane
6.70
Rape
4.50
In alternativa si può utilizzare l’operatore BETWEEN:
SQL>
SELECT * FROM PREZZO
WHERE PREZZOINGROSSO BETWEEN 2.50 AND 7.50;
ELEMENTO
PREZZOINGROSSO
Pomodori
3.40
Patate
5.10
Banane
6.70
Rape
4.50
Se nella tabella prezzo ci fosse stato un valore di PREZZOINGROSSO pari a 2.50. Il
corrispondente record sarebbe stato estratto. I parametri dell’operatore BETWEEN
sono inclusivi
56
3.5
INFN Laboratori Nazionali di Frascati
AMICI
Quesiti
Utilizzare la tabella AMICI
per rispondere ai quesiti successivi
1.
2.
3.
4.
5.
COGNOME
NOME
TELEFONO
PROVINCIA
BUNDI
ALE
555-1111
BG
MEZA
ALE
555-222
MI
MERRILI
SABY
555-6666
CO
MAZZA
JO
555-6767
PV
BANFI
BARBY
555-3223
BG
BORDON
SIR
555-3116
CT
PERSIANI
ALBY
555-2345
CA
Scrivere una query per selezionare tutti i cognomi che iniziano con la
lettera M;
Scrivere una query per selezionare gli amici che vicono in provincia di
Bergamo e il cui nome è Ale;
Date due tabelle (PARTE1 e PARTE2) che contengono una colonna
chiamata NUMPARTE, come fareste a trovare quei numeri che
appartengono ad entrambe le tabelle? Scrivere la query;
Quale scorciatoia potrebbe essere utilizzata in alternativa a
WHERE a >=10 AND a<=30?
Qual è il risultato di questa query?
SELECT NOME
FROM AMICI
WHERE NOME = ‘ALE’
AND COGNOME = ‘BANFI’;
57
3.6
INFN Laboratori Nazionali di Frascati
Esercizi
COGNOME
NOME
TELEFONO
PROVINCIA
BUNDI
ALE
555-1111
BG
MEZA
ALE
555-222
MI
MERRILI
SABY
555-6666
CO
MAZZA
JO
555-6767
PV
BANFI
BARBY
555-3223
BG
BORDON
SIR
555-3116
CT
PERSIANI
ALBY
555-2345
CA
1. Utilizzando la tabella AMICI, scrivere una query che fornisce il
seguente risultato:
NOME
PROVINCIA
ALE
BG
2. Utilizzando la tabella AMICI, scrivere una query che fornisce il
seguente risultato:
NOMECOMPLETO
TELEFONO
MERRILI, SABY
555-6666
MAZZA, JO
555-6767
BANFI, BARBY
555-3223
58
4.1
Funzioni
INFN Laboratori Nazionali di Frascati
Obiettivi
Descrivere le funzioni del linguaggio SQL.
Le funzioni permettono di svolgere varie operazioni, come la somma dei
contenuti di una colonna o la trasformazione in maiuscolo di tutti I
caratteri di una stringa.
Al termine avremo imparato a conoscere ed usare quelle principali
Aggregate
Temporali
Aritmetiche
Di caratteri
Di conversione
varie
59
4.1
INFN Laboratori Nazionali di Frascati
Funzioni aggregate: COUNT
Restituisce il numero di righe che soddisfano la condizione
specificata nella clausola WHERE
SQL> SELECT COUNT(*)
FROM INFN03.INVGEN
WHERE TIPOBENE = 'L';
COUNT(*)
521
Per rendere più leggibile codice e output si può usare l’alias:
SQL> SELECT COUNT(*) LIBRI
FROM INFN03.INVGEN
WHERE TIPOBENE = 'L';
LIBRI
521
Se fosse indicato un campo al posto dell’asterisco il risultato non
cambierebbe:
SQL> SELECT COUNT(NUMINV) LIBRI
FROM INFN03.INVGEN
WHERE TIPOBENE = 'L';
LIBRI
521
AS/400
60
4.1
INFN Laboratori Nazionali di Frascati
Funzioni aggregate: COUNT
E se non venisse specificata la clausola WHERE?
SQL> SELECT COUNT(*)
FROM INFN03.INVGEN;
COUNT(*)
2584
Senza la clausola WHERE la query fornisce il numero di record
della tabella.
Nell’esempio ritorna il numero complessivo di tutti i record
presenti nell’inventario (Libri-Varie-Immobili)
AS/400
61
4.1
INFN Laboratori Nazionali di Frascati
SUM
La funzione SUM somma tutti i valori di una colonna.
SQL> SELECT SUM(IMPVAR) VARIAZIONI
FROM INFN03.CMOV3;
VARIAZIONI
-27723897
SQL> SELECT SUM(IMPVAR) * 0.01 VARIAZIONI
FROM INFN03.CMOV3;
VARIAZIONI
-277238.97
SQL> SELECT SUM(DESVAR) * 0.01 VARIAZIONI
FROM INFN03.CMOV3;
Error: [SQL0402] SUM use not valid.
Calcolare la media dell’importo delle variazioni
SQL> SELECT SUM(IMPVAR) * 0.01/ count(*) MEDIAVARIAZIONI
FROM INFN03.CMOV3;
MEDIAVARIAZIONI
-208.45
AS/400
62
4.1
MAX
INFN Laboratori Nazionali di Frascati
La funzione MAX serve a trovare il valore massimo di una
colonna.
SQL> SELECT MAX(IMPVAR) VARIAZMAX
FROM INFN03.CMOV3;
SQL> SELECT MAX(IMPVAR) * 0.01 VARIAZMAX
FROM INFN03.CMOV3;
VARIAZMAX
470000
VARIAZMAX
4700.00
Quale centro di costo ha la variazione più alta?
SQL> SELECT PREVEN
FROM INFN03.CMOV3
WHERE IMPVAR = MAX(IMPVAR)
Error at line 3:
ORA-00934 group function is not alllowed here
Putroppo questa istruzione non funziona. La funzione di gruppo
non può essere usata con la clausola WHERE, la soluzione è
quella di utilizzare le subquery.
La funzione MAX opera anche con le stringhe, ritorna la stringa
massima, cioè più vicina alla lettera Z.
AS/400
63
4.1
INFN Laboratori Nazionali di Frascati
MIN
Opera in modo analogo alla funzione MAX, ovviamente
restituisce il valore minimo della colonna
SQL> SELECT MIN(IMPVAR) VARIAZMIN
FROM INFN03.CMOV3;
SQL> SELECT MIN(IMPVAR) * 0.01 VARIAZMIN
FROM INFN03.CMOV3;
VARIAZMIN
-5000000
VARIAZMIN
-50000.00
E’ possibile combinare le funzioni MIN e MAX per stabilire un
intervallo di valori (può essere utile con le funzioni statistiche):
SQL> SELECT MIN(IMPVAR) MINIMO, MAX(IMPVAR) MASSSIMO
FROM INFN03.CMOV3;
VARIAZMIN
4700.00
VARIAZMAX
-50000.00
AS/400
64
4.1
Funzioni temporali
INFN Laboratori Nazionali di Frascati
La società è governata da date e orari, la maggior parte delle implementazioni SQL
dispone di funzioni specifiche.
Con queste funzioni è possibile aggiungere mesi ad una data specifica, individuare
l’ultimo giorno del mese indicato nella data, conoscere la data del sistema,
sapere quanti mesi separano due date ed altro.
Ad esempio per conoscere quali compiti hanno avuto inizio prima del 19 maggio
1995:
SQL> SELECT *
FROM PROGETTO
WHERE MONTHS_BETWEEN(’19 MAY 95’, DATASTART) > 0;
COMPITO
DATASTART
DATAFINE
AVVIO PROGETTO
01-APR-95
01-APR-95
DEF. SPECIFICHE
02-APR-95
01-MAG-95
CHECK TECNICO
15-MAY-95
30-MAY-95
MONTS_BETWEEN(’19 MAY 95’, DATASTART)
Questa funzione ritorna il numero di mesi che sono compresi tra le due date
65
4.1
INFN Laboratori Nazionali di Frascati
Funzioni di carattere
Sono funzioni che consentono di manipolare i caratteri e le stringhe. Come le
funzioni aritmetiche e trigonometriche sono utilizzate essenzialmente
dai programmatori, si consiglia di consultare I manuali specifici qualora
se ne avvertisse la necessità.
Quella che risulta particolarmente utile è la
funzione SUBSTR
Questa funzione consente di estrarre una serie di caratteri (sottostringa ) da
una stringa specificata.
SUBSTR richiede tre argomenti:
1. La stringa da esaminare,
2. La posizione del primo carattere da estrarre,
Lunghezza
3. Il numero di caratteri da estrarre
sottostringa
SQL> SELECT SUBSTR(CAMPO, 2, 3)……..
Nome colonna
Posizione
iniziale
66
4.1
INFN Laboratori Nazionali di Frascati
SUBSTR
Nelle attuali applicazioni contabili si chiede di inserire il capitolo ma in realtà
con esso si chiede di inserire anche il titolo e la categoria.
Ad esempio per indicare quello relativo alla cancelleria usiamo scrivere
104020, in realtà noi intendiamo 1 04 020, cioè specifichiamo il titolo I
(spese correnti), la categoria IV (consumo) e infine 020 (cancelleria
ecc.).
In questa caso torna utilissimo disporre della funzione SUBSTR per avere
informazioni relative alle singoli sottostringhe che compongono il
capitolo 104020.
SQL> SELECT SUBSTR(digits(CAPIT), 1, 1)TITOLO,
SUBSTR(digits(CAPIT), 2, 2)CATEGORIA
altre colonne
FROM INFN03.CMOV3
WHERE SUBSTR(digits(CAPIT), 2, 2) = '04'
Verranno selezionate le righe della tabella variazioni relative alla
sola categoria IV (consumo)
Il troncamento di colonne molto larghe migliora la leggibilità del risultato di
una query
AS/400
67
4.1
INFN Laboratori Nazionali di Frascati
SUBSTR
Esempio:
Calcolare l’ammontare complessivo delle variazioni relative ai capitoli della
categoria IV (consumo) residui e competenza
SQL> SELECT SUM(IMPVAR* 0.01) AS VARIAZIONICONSUMO
FROM INFN03.CMOV3
WHERE SUBSTR(DIGITS(CAPIT),2,2) = '04'
VARIAZIONICONSUMO
-112587.07
AS/400
68
4.1
INFN Laboratori Nazionali di Frascati
CONCAT
Questa funzione consente di unire due stringhe, ad esempio:
SQL> SELECT CONCAT(nome, cognome) "Nome e Cognome"
FROM AMICI;
Nome e Cognome
ALE
BUNDI
ALE
MEZA
SABY
MERRILLI
L’esempio presuppone che nella tabellaX la colonna relativa al nome sia
distinta da quella del cognome
Esistono funzioni per trasformare tutti i caratteri da maiuscolo in minuscolo e viceversa
(LOWER ed UPPER), di trasformare in maiuscolo solo il primo carattere ed in
minuscolo gli altri (INITCAP), ed altre funzioni meno frequenti. A fronte della
necessità di manipolare caratteri è sempre utile consultare un manuale o informarsi
sull’esistenza di una valida soluzione.
69
4.10
INFN Laboratori Nazionali di Frascati
Quesiti
1) Questa query è corretta?
SQL> Select count(cognome) from caratteri;
2) E quest’altra?
SQL> Select sum(cognome) from caratteri;
3) Supponendo che NOME e COGNOME siano due colonne separate, quali
operatori o funzioni sono in grado di unirle in un’unica colonna?
4) Che cosa significa se il risultato di questa query è 6?
SQL> Select count(*) from TEAMSTATS;
5) Questa funzione è corretta?
SQL> Select SUBSTR COGNOME, 1, 5 from TAB_NOME;
70
4.10
INFN Laboratori Nazionali di Frascati
Esercizi
1) Calcola totale degli impegni di competenza
nome tabella: infn03.cmov2
condizione:
RESID = 0
CAPIT=104020
2) Calcola il numero di variazioni
nome tabella: infn03.cmov3
condizione:
RESID = 1
sui capitoli residui
3) Calcola
quale è l’importo più alto tra le variazioni sia di
competenza che relative ai residui:
nome tabella: infn03.cmov3
71
5
Le clausole di SQL
INFN Laboratori Nazionali di Frascati
Questo capitolo è dedicato alle clausole utilizzate dall’istruzione SELECT del
linguaggio SQL, in particolare a:





WHERE
STARTING WITH
ORDER BY
GROUP BY
HAVING
Per avere un’idea sull’impuego di queste clausole esaminiamo la sintassi
generela:
SELECT [DISTINCT | ALL] { *
| { [schema.[tabella | view | snapshot}.*
| espr } [ [AS] c_alias ]
[, { [schema.]tabella}.*
| espr } [ [AS] c_alias ] ]…...
FROM [schema.]{tabella } [, [schema.]{tabella}
[WHERE condizione]
GROUP BY espr [, espr]…. [HAVING condizione]
[{UNION | UNION ALL | INTERSECT | MINUS | SELECT comando]
[ORDER BY {espr | posizione} [ASC] | [DESC]
[, {espr | posizione} [ASC] [DESC]
72
5.1
INFN Laboratori Nazionali di Frascati
La clausola WHERE
Utilizzando soltanto la clausola SELECT e FROM la query seleziona tutte le
righe di una tabella, ad esempio:
SQL> SELECT * FROM ASSEGNI
ASSEGNO
BENEFICIARIO
IMPORTO
1
Computer Shop
1500.00
Dischetti e CD-Rom
2
Librerie Riunite
2453.40
Libri, cancelleria
3
Computer Shop
2003.20
Telefono cellulare
4
BIOGAS srl
5
SuperMarket GC
6
Assicurazioni Italy
250.00
Assicurazione casa
7
GAS Auto S.p.A.
251.00
Gas
980.00
1500.00
NOTE
Gas
Alimentari
Con la clausola SELECT è possibile essere più selettivi:
SQL> SELECT * FROM ASSEGNI WHERE IMPORTO > 1000.00;
ASSEGNO
BENEFICIARIO
IMPORTO
NOTE
1
Computer Shop
1500.00
Dischetti e CD-Rom
2
Librerie Riunite
2453.40
Libri, cancelleria
3
Computer Shop
2003.20
Telefono cellulare
5
SuperMarket GC
1500.00
Alimentari
La clausola WHERE ha restituito solo i quattro record della tabella ASSEGNI
che soddisfano la condizione richiesta
73
5.1
La clausola WHERE
INFN Laboratori Nazionali di Frascati
La clausola WHERE può risolvere anche altri problemi. Ad esempio data la
tabella di nomi e luoghi è possibile porre la domanda:” Laura dov’è?”
SQL> SELECT * FROM PUZZLE
NOME
LUOGO
TANIA
CORTILE
MARCO
CUCINA
SILVIO
BAGNO
PIPPO
GARAGE
LAURA
SOGGIORNO
ARMANDO
TERRAZZA
SQL> SELECT LUOGO AS "LAURA DOV'E'?"
FROM PUZZLE
WHERE NOME = 'LAURA'
LAURA DOV’E’?
SOGGIORNO
Questa query dimostra che la colonna utilizzata nella condizione dell’istruzione
WHERE non deve essere necessariamente citata nella clausola SELECT
Dopo SELECT e FROM, WHERE è il terzo termine più usato nel linguaggio SQL
74
5.1
INFN Laboratori Nazionali di Frascati
La clausola STARTING WITH
La clausola STARTING WITH è un’aggiunta alla clausola WHERE che opera esattamente
come LIKE(<espr>%). Confrontiamo i risultati delle seguenti query:
SQL> SELECT BENEFICIARIO, IMPORTO, NOTE
FROM ASSEGNI
WHERE BENEFICIARIO LIKE(‘Co%’);
ASSEGNO
BENEFICIARIO
IMPORTO
NOTE
1
Computer Shop
1500.00
Dischetti e CD-Rom
3
Computer Shop
2003.20
Telefono cellulare
SQL> SELECT BENEFICIARIO, IMPORTO, NOTE
FROM ASSEGNI
WHERE BENEFICIARIO STARTING WITH(‘Co%’);
ASSEGNO
BENEFICIARIO
IMPORTO
NOTE
1
Computer Shop
1500.00
Dischetti e CD-Rom
3
Computer Shop
2003.20
Telefono cellulare
I risultati sono identici. Le due clausole possono essere usate insieme
NON AS/400
75
5.1
INFN Laboratori Nazionali di Frascati
La clausola STARTING WITH
SQL> SELECT BENEFICIARIO, IMPORTO, NOTE
FROM ASSEGNI
WHERE BENEFICIARIO STARTING WITH(‘Co%’)
OR NOTE LIKE ‘A%’;
ASSEGNO
BENEFICIARIO
IMPORTO
1
Computer Shop
1500.00
Dischetti e CD-Rom
3
Computer Shop
2003.20
Telefono cellulare
5
SuperMarket GC
1500.00
Alimentari
6
Assicurazioni Italy
250.00
NOTE
Assicurazione casa
La clausola STARTING WITH è comune a molte implementazioni SQL, prima
di innamorarvi dei questa clausola consultare la documentazione
allegata alla versione SQL in uso (OK su Oracle No su AS/400).
NON AS/400
76
5.4
INFN Laboratori Nazionali di Frascati
La clausola ORDER BY
Molto spesso è utile o necessario presentare i risultati si una query in un certo ordine.
Le clausole SELECT e FROM ritornano dati secondo l’ordine di chiave primaria (se
prevista) o nell’ordine in cui i dati sono stati inseriti nel database.
La clausola ORDER BY consente di ordinare le righe ottenute da una query.
Esempio:
SQL> SELECT *
FROM ASSEGNI
ORDER BY ASSEGNO;
ASSEGNO
BENEFICIARIO
IMPORTO
1
Computer Shop
1500.00
Dischetti e CD-Rom
2
Librerie Riunite
2453.40
Libri, cancelleria
3
Computer Shop
2003.20
Telefono cellulare
4
BIOGAS srl
5
SuperMarket GC
6
Assicurazioni Italy
250.00
Assicurazione casa
7
GAS Auto S.p.A.
251.00
Gas
980.00
1500.00
ORDER richiede sempre BY (BY non è facoltativo)
NOTE
Gas
Alimentari
77
5.4
INFN Laboratori Nazionali di Frascati
La clausola ORDER BY
E’ possibile ordinare I record in senso inverso
SQL> SELECT *
FROM ASSEGNI
ORDER BY NUM DESC;
NUM
BENEFICIARIO
IMPORTO
7
GAS Auto S.p.A.
251.00
Gas
6
Assicurazioni Italy
250.00
Assicurazione casa
5
SuperMarket GC
4
BIOGAS srl
3
Computer Shop
2003.20
Telefono cellulare
2
Librerie Riunite
2453.40
Libri, cancelleria
1
Computer Shop
1500.00
Dischetti e CD-Rom
1500.00
980.00
NOTE
Alimentari
Gas
La parola chiave DESC consente di definire l’ordine discendente, ASC è la
parola chiave per il consueto ordine ascendente, ma è raramente usata
poichè rappresenta il valore di default
78
5.4
INFN Laboratori Nazionali di Frascati
La clausola ORDER BY
La clausola ORDER BY può essere applicata a più campi:
SQL> SELECT *
FROM ASSEGNI
ORDER BY BENEFICIARIO, ASSEGN;
NUM
BENEFICIARIO
IMPORTO
NOTE
6
Assicurazioni Italy
250.00
Assicurazione casa
4
BIOGAS srl
980.00
Gas
1
Computer Shop
1500.00
Dischetti e CD-Rom
3
Computer Shop
2003.20
Telefono cellulare
7
GAS Auto S.p.A.
251.00
2
Librerie Riunite
2453.40
Libri, cancelleria
5
SuperMarket GC
1500.00
Alimentari
Gas
Come era facilmente immaginabile il risultato è di avere record ordinati per
beneficiario e nell’ambito di ogni beneficiario per numero.
Se lo si desidera si può anche indicare la colonna anzichè per nome, per numero
(ovviamente deve essere il numero corrispondente alla posizione delle colonne dichiarate)
79
5.5
La clausola GROUP BY
INFN Laboratori Nazionali di Frascati
Abbiamo visto gli operatori aggregati (COUNT – SUM – MIN – MAX etc), ma se volessimo
avere aggregazioni raggruppate per sottoinsiemi dei dati selezionati?
Esempio:
SQL> SELECT BENEFICIARIO, SUM(IMPORTO)
FROM ASSEGNI
GROUP BY BENEFICIARIO;
BENEFICIARIO
IMPORTO
Computer Shop
3503.20
Librerie Riunite
2453.40
BIOGAS srl
980.00
SuperMarket GC
1500.00
Assicurazioni Italy
250.00
GAS Auto S.p.A.
251.00
SQL> SELECT BENEFICIARIO, COUNT (BENEFICIARIO) NUMOPE, SUM(IMPORTO)
FROM ASSEGNI
BENEFICIARIO
NUMOPE
IMPORTO
GROUP BY BENEFICIARIO;
Computer Shop
2
3503.20
Librerie Riunite
1
2453.40
BIOGAS srl
1
980.00
SuperMarket GC
1
1500.00
Assicurazioni Italy
1
250.00
GAS Auto S.p.A.
1
251.00
80
5.5
INFN Laboratori Nazionali di Frascati
La clausola GROUP BY
Esempio
Indicare per consegnatario il numero e l’ammontare del valore dei beni patrimoniali in
carico (nome tabella: infn03.invgen)
SQL> SELECT consegn Consegnatario,
count(consegn) NumeroBeni,
(SUM(valcar) - SUM(valsca)) ValoreBeni
FROM infn03.invgen
GROUP BY consegn
ORDER BY Consegnatario;
Consegnatario
NumeroBeni
ValoreBeni
AGOSTINI E.
10
5983,13
ALBICOCCO M.
36
93716,50
ANTIDORMI S.
9
8467,20
69
147152,49
ANTIDORMI SANDRO
81
5.5
INFN Laboratori Nazionali di Frascati
La clausola GROUP BY
Cosa accade se la clausola GROUP BY specifica più colonne?
SQL> SELECT consegn Consegnatario,
count(consegn) NumeroBeni,
(SUM(valcar) - SUM(valsca)) ValoreBeni
FROM infn03.invgen
GROUP BY ubicaz, consegn
ORDER BY consegn;
Consegnatario
NumeroBeni
ValoreBeni
10
5983,13
ALBICOCCO M.
1
10796,36
ALBICOCCO M.
34
82843,19
ALBICOCCO M.
1
76,95
ANTIDORMI S.
9
8467,20
ANTIDORMI SANDRO
1
5127,18
ANTIDORMI SANDRO
20
10955,70
ANTIDORMI SANDRO
48
131069,61
AGOSTINI E.
10
5983,13
AGOSTINI E.
82
5.5
La clausola GROUP BY
INFN Laboratori Nazionali di Frascati
Osserviamo la seguente query:
SQL> SELECT consegn, valcar, count(valcar)
FROM infn03.invgen GROUP BY consegn;
Dynamic SQL Error
-SQL error code = -104
-invalid column reference
SQL non è in grado di eseguire la query; per capire il perchè basta mettersi
per un momento nei panni di SQL. Si supponga di dover raggruppare le
seguenti righe:
SQL> SELECT consegn, ubicaz, valcar
FROM infn03.invgen
WHERE consegn=‘ANTIDORMI SANDRO’;
CONSEGN
UBICAZ
VALCAR
ANTIDORMI SANDRO
Ed. Magazzino
5127,18
ANTIDORMI SANDRO
Ed. Magazzino
10955,70
ANTIDORMI SANDRO
Uff. di direzione
ANTIDORMI SANDRO
Ufficio EDP
131069,61
5983,13
Se avessimo provato a raggrupparle dove cosa avreste scritto come UBICAZIONE?
Avendo una sola riga di riepilogo non possiamo scriverci contemporaneamente più valori
83
5.6
INFN Laboratori Nazionali di Frascati
La clausola HAVING
Come è possibile qualificare i dati utilizzati nella clausola GROUP BY?
Per rispondere sarà usata la tabella ORGCHART
NOME
DIVISIONE
STIPENDIO
CONGEDIMALATTIA
CONGEDIANNUI
ADAMS
RESEARCH
34000.00
34
12
WILKES
MARKETING
31000.00
40
9
STOKES
MARKETING
36000.00
20
19
MEZA
COLLECTIONS
40000.00
30
27
MERRICK
RESEARCH
45000.00
20
17
RICHARDSON
MARKETING
42000.00
25
18
FURY
COLLECTIONS
35000.00
22
14
PRECOURT
PR
37500.00
24
24
Per raggruppare l’output in base alle divisioni e calcolare lo stipendio medio di ogni
divisione, basta scrivere la seguente query:
SQL> SELECT DIVISIONE, AVG(STIPENDIO)
FROM ORGCHART
DIVISIONE
STIPENDIO
GROUP BY DIVISIONE;
COLLECTIONS
37500.00
MARKETING
36333.33
PR
37500.00
RESEARCH
39500.00
84
5.6
INFN Laboratori Nazionali di Frascati
La clausola HAVING
Proviamo a qualificare una query che consente di estrarre soltanto quelle divisioni i cui
stipendi medi sono minori di 38000:
SQL> SELECT DIVISIONE, AVG(STIPENDIO)
FROM ORGCHART
WHERE AVG(STIPENDIO) < 38000
GROUP BY DIVISIONE;
Dynamic SQL Error
-SQL error code = -104
-Invali aggregate reference
Questo errore si verifica perchè WHERE non opera con le funzioni di aggregazione.
Affinché questa query possa operare occorre qualcosa di nuovo: la clausola
HAVING, infatti:
SQL> SELECT DIVISIONE, AVG(STIPENDIO)
FROM ORGCHART
GROUP BY DIVISIONE
HAVING AVG(STIPENDIO) < 38000;
DIVISIONE
STIPENDIO
COLLECTIONS
37500.00
MARKETING
36333.33
PR
37500.00
85
5.6
INFN Laboratori Nazionali di Frascati
La clausola HAVING
La clausola HAVING consente di utilizzare le funzioni aggregate in un’istruzione di
confronto, fornendo alle funzioni aggregate ciò che WHERE fornisce alle singole
righe. La clausola HAVING può operare con le espressioni non aggregate? Proviamo
questa query:
SQL> SELECT DIVISIONE, AVG(STIPENDIO)
FROM ORGCHART
GROUP BY DIVISIONE
WHERE STIPENDIO < 38000;
DIVISIONE
PR
STIPENDIO
37500.00
Come mai questo risultato é diverso dall’ultima query?
86
5.6
La clausola HAVING
INFN Laboratori Nazionali di Frascati
La clausola HAVING AVG(STIPENDIO) >38000 ha valutato i singoli gruppi
ed ha restituito soltanto quelli con uno stipendio medio minore di
38000, proprio ciò che volevamo, invece la clausola HAVING
STIPENDIO<38000 ha prodotto un risultato diverso. In sostanza la
clausola ha valutato tutti gli stipendi escludendo i gruppi in cui anche
un solo stipendio non rientra nella condizione indicata, infatti salvo la
divisione PR in tutte le altre vi è almeno uno stipendio maggiore di
38000.
SQL> SELECT NOME, DIVISIONE, STIPENDIO
FROM ORGCHART
ORDER BY DIVISIONE
NOME
DIVISIONE
STIPENDIO
FURY
COLLECTIONS
35000.00
MEZA
COLLECTIONS
40000.00
WILKES
MARKETING
31000.00
STOKES
MARKETING
36000.00
RICHARDSON
MARKETING
42000.00
PRECOURT
PR
37500.00
ADAMS
RESEARCH
34000.00
MERRICK
RESEARCH
45000.00
Alcune implementazioni SQL restituiscono errore se nella clausola
HAVING non viene utilizzata una funzione aggregata.
87
5.6
INFN Laboratori Nazionali di Frascati
La clausola HAVING
E’ anche possibile utilizzare più condizioni.
Ad esempio:
SQL> SELECT DIVISIONE, AVG(CONGEDMAL), AVG(CONGEDANN)
FROM ORGCHART
GROUP BY DIVISIONE
HAVING AVG(CONGEDMAL) > 25 AND
AVG(CONGEDANN) < 20;
DIVISIONE
AVG
AVG
MARKETING
27
15
RESEARCH
27
15
La query ha restituito tutte le divisioni con medie di CONGEDIMALATTIA
maggiori di 25 giorni e medie di CONGEDIANNUI inferiori a 29
giorni.
88
5.6
La clausola HAVING
INFN Laboratori Nazionali di Frascati
Altri esempi:
SQL> SELECT DIVISIONE, AVG(CONGEDMAL), AVG(CONGEDANN)
FROM ORGCHART
GROUP BY DIVISIONE
HAVING COUNT(DIVISIONE) > 1;
DIVISIONE
AVG
AVG
COLLECTIONS
26
21
MARKETING
28
15
RESEARCH
27
15
SQL> SELECT DIVISIONE, MIN(STIPENDIO), MAX(STIPENDIO)
FROM ORGCHART
GROUP BY DIVISIONE
HAVING AVG(STIPENDIO) > 37000
OR MIN(STIPENDIO) > 32000;
DIVISIONE
MIN
MAX
COLLECTIONS
35000.00
40000.00
PR
37500.00
37500.00
RESEARCH
34000.00
45000.00
89
5.6
INFN Laboratori Nazionali di Frascati
La clausola HAVING
L’operatore IN opera anche nella clausola HAVING:
SQL> SELECT DIVISIONE, AVG(STIPENDIO)
FROM corso.ORGCHART
GROUP BY DIVISIONE
HAVING DIVISIONE IN ('PR', 'RESEARCH');
DIVISIONE
AVG
PR
37500.00
RESEARCH
39500.00
90
5.7
INFN Laboratori Nazionali di Frascati
Combinazione di clausole
Esempio 1
Trovare tutti gli assegni emessi a favore di Computer Shop e per le forniture
del gas ordinandoli in base alla colonna NOTE:
SQL> SELECT BENEFICIARIO, NOTE
FROM ASSEGNI
WHERE BENEFICIARIO = ‘Computer Shop’
or NOTE like(‘Ga%’)
ORDER BY NOTE;
BENEFICIARIO
NOTE
Computer Shop
Controller
Computer Shop
Dischetti e CD-Rom
BIOGAS srl
Gas
GAS Auto S.p.A.
Gas
GAS Auto S.p.A.
Gas
Computer Shop
Joystick
Computer Shop
Telefono cellulare
SELECT BENEF, NOTE
FROM corso.ASSEGNI
WHERE BENEF = 'Computer Shop'
or NOTE like('Ga%')
ORDER BY NOTE;
Con LIKE vengono trovate le note che iniziano con ‘Ga’. Con l’operatore OR
I dati vengono estratti se la clausola WHERE soddisfa una delle due
condizioni
91
5.7
INFN Laboratori Nazionali di Frascati
Combinazione di clausole
Esempio 2
Utilizzando la tabella ORGCHART trovare lo stipendio di quei dipendenti che
hanno meno di 25 giorni di congedi per malattia, ordinando I record
per nome:
SQL> SELECT NOME, STIPENDIO
FROM ORGCHART
WHERE CONGEDOMALATTIA < 25
ORDER BY NOME;
NOME
STIPENDIO
FURY
35000.00
MERRICK
45000.00
PRECOURT
37500.00
STOKES
36000.00
E’ una query semplice che offre l’opportunità di utilizzare insieme la
clausola WHERE e ORDER BY
92
5.7
INFN Laboratori Nazionali di Frascati
Combinazione di clausole
Esempio 3
Utilizzando la tabella ORGCHART elencare gli stipendi medi, la media dei
congedi per malattia e quella dei congedi annui per ogni divisione:
SQL> SELECT DIVISIONE,
AVG(STIPENDIO),
AVG(CONGEDIMALATTIA),
AVG(CONGEDIANNUI)
FROM ORGCHART
GROUP BY DIVISIONE;
DIVISIONE
AVG
AVG
AVG
COLLECTIONS
37500.00
26
21
MARKETING
36333.33
28
15
PR
37500.00
24
24
RESEARCH
39500.00
26
15
93
5.7
Combinazione di clausole
INFN Laboratori Nazionali di Frascati
Segue una interessante variante della query precedente, proviamo a
spiegarne I risultati ottenuti:
SQL> SELECT DIVISIONE,
AVG(STIPENDIO), AVG(CONGEDIMALATTIA, AVG(CONGEDIANNUI)
FROM ORGCHART GROUP BY DIVISIONE ORDER BY NOME;
DIVISIONE
AVG
AVG
AVG
RESEARCH
39500.00
26
15
COLLECTIONS
37500.00
26
21
PR
37500.00
24
24
MARKETING
36333.33
28
15
Una query più semplice potrebbe dare suggerimenti:
SQL> SELECT NOME, DIVISIONE
FROM ORGCHART ORDER BY NOME;
NOME
DIVISIONE
ADAMS
RESEARCH
FURY
COLLECTIONS
MERRICK
RESEARCH
MEZA
COLLECTIONS
PRECOURT
PR
RICHARDSON
MARKETING
STOKES
MARKETING
WILKES
MARKETING
94
5.8
INFN Laboratori Nazionali di Frascati
Quesiti
1.
Quale clausola opera come LIKE(<espr>%)?
2.
Quale è la funzione della clausola GROUP BY e quale altra clausola
opera in modo analogo?
3.
E’ corretta questa istruzione SELECT:
SELECT NOME, AVG(STIPENDIO), DIPARTIMENTO
FROM TAB_STIP
WHERE DIPARTIMENTO = ‘PERSONALE’
ORDER BY NOME
GROUP BY DIPARTIMENTO, STIPENDIO
Quando si usa la clausola HAVING, è necessario usare anche GROUP
BY?
E’ possibile applicare ORDER BY a una colonna che non è inclusa tra
quelle citate nella clausola SELECT?
4.
5.
95
5.9
INFN Laboratori Nazionali di Frascati
Esercizi
1.
Utilizzando la tabella ORGCHART dei precedenti esempi, trovare qunti
dipendenti di ogni divisione hanno 30 o più giorni di congedi per
malattia.
2.
Utilizzando la tabella ASSEGNI, scrivere una istruzione SELECT per
ottenere il seguente risultato:
ASSEGNO
1
BENEFICIARIO
IMPORTO
Computer Shop
1500.00
96
6.0
Combinazione di tabelle e subquery
INFN Laboratori Nazionali di Frascati
Obiettivi:

Leggere e manipolare dati che si trovano in più tabelle

Creare un subquery (collegare una query al risultato di un’altra
query)
97
6.1 Più tabelle in una sola
INFN Laboratori Nazionali di Frascati
SELECT
SELECT * FROM TABELLA1
RIGA
SELECT * FROM TABELLA1
NOTE
RIGA
NOTE
1
Nota 1 tabella 1
1
Nota 1 tabella 2
2
Nota 2 tabella 1
2
Nota 2 tabella 2
3
Nota 3 tabella 1
3
Nota 3 tabella 2
SELECT * FROM TABELLA1, TABELLA 2
RIGA
NOTE
RIGA
NOTE
1
Nota 1 tabella 1
1
Nota 1 tabella 2
1
Nota 1 tabella 1
2
Nota 2 tabella 2
1
Nota 1 tabella 1
3
Nota 3 tabella 2
2
Nota 2 tabella 1
1
Nota 1 tabella 2
2
Nota 2 tabella 1
2
Nota 2 tabella 2
2
Nota 2 tabella 1
3
Nota 3 tabella 2
3
Nota 3 tabella 1
1
Nota 1 tabella 2
3
Nota 3 tabella 1
2
Nota 2 tabella 2
3
Nota 3 tabella 1
3
Nota 3 tabella 2
tabella2
tabella1
Tabella
risultante
9 righe? Da dove arrivano? Un esame più attento indica che
sono state indicate tutte le righe della tabella 1 per ogni
riga della tabella 2
98
6.1
INFN Laboratori Nazionali di Frascati
Più tabelle in una sola SELECT
Abbiamo ottenuto una combinazione chiamata cross-join (unione incrociata), non
è particolarmente utile ma illustra ciò che sta alla base del concetto di join.
Un database relazionale correttamente progettato dstribuisce su più tabelle le
informazioni, raggruppandole per gruppi omogenei, ad esempio:
ORDINI
Num Ditta
Descrizione
Totale
1
CENTRO GAS S.R.L.
1500 LT.G.P.L.PROPANO
972,00
2
PISCITELLI ANTONIO VIAGGI
TRASPORTO ISPETTORI IN VISITA
115,50
3
COOPERATIVA A.C.F.
SERVIZIO MICROFONI FORO GLOBAL
54,00
4
GHIZZONI SERAFINO
NOLEGGIO PULMINI VISITE
79,20
5
CIVICO 11 STUDIO GRAFICO
PROG.GRAFICA MANIFESTO
238,61
RIGHEORDINI
Num
Quantita
1
1500
2
1
2
Descrizione
Prezzo
IVA
5,4
20
minubus 16 posti
600,0
10
3
ore attesa
150,0
10
3
3
ore lavoro per servizio microfoni sala
150,0
20
3
0
Majorana per il 23 gennaio 2003
0
20
4
1
noleggio n. 2 pulmini per visite
720,0
10
4
0
partecipanti meeting di auger
0
20
lt. di g.p.l. propano per lab.esterni
Come combinare opportunamente le due tabelle?
99
Più tabelle in una sola
6.1 SELECT
INFN Laboratori Nazionali di Frascati
Per combinare opportunamente due o più tabelle è utile la clausola WHERE, ad esempio:
SELECT a.numpar, a.ordi1, b.qtarig, b.desrig, b.preuni * 0.01
FROM infn03.cord1 as a, infn03.cord2 as b
WHERE a.numpar =b.numpar
Numpar
ordI1
QTARIG
DESRIG
1500
PREUNI
1
CENTRO GAS S.R.L.
lt. di g.p.l. propano per lab.esterni
0,54
2
PISCITELLI ANTONIO VIAGGI
1
minubus 16 posti
60,00
2
PISCITELLI ANTONIO VIAGGI
3
ore attesa
15,00
3
COOPERATIVA A.C.F.
3
ore lavoro per servizio microfoni sala
15,00
3
COOPERATIVA A.C.F.
0
Majorana per il 23 gennaio 2003
4
GHIZZONI SERAFINO
1
noleggio n. 2 pulmini per visite
4
GHIZZONI SERAFINO
0
partecipanti meeting di auger
5
CIVICO 11 STUDIO GRAFICO
1
ideazione progettazione grafica e
5
CIVICO 11 STUDIO GRAFICO
0
videoimpaginazione di n. 1 manifesto
0
72,00
0
198,84
0
Abbiamo ridenominato a la tabella infn03.cord1 e b la tabella infn03.cord2, quindi
abbiamo operato sulle righe risultanti operando una combinazione
corrispondente (colonna NUMPAR presente in entrambe le tabelle).
Se non avessimo usato la clausola WHERE il risultato sarebbe stato di attente il
numero di righe della prima tabella moltiplicato il numero della seconda.
Quindi, salvo non si desideri davvero una combinazione cartesiana (x * y) è
sempre consigliabile l’uso della clausola WHERE.
100
6.2
INFN Laboratori Nazionali di Frascati
Subquery
La sintassi è abbastanza semplice: SELECT x, y from z WHERE a = (select a from b
where c = condizione).
Esempio:
select b.preute, a.capit, a.cprass * 0.01 ASSEGN, a.cprimp * 0.01 IMPEGN,
a.cprass * 0.01 - a.cprimp * 0.01 DISPON
from infn03.ccpr a, infn03.cpre b
where a.preven=b.preven
and a.PREVEN = (SELECT preven
from infn03.Cpre
where PREUTE='AMM.NE')
PREUTE
CAPIT
ASSEGN
IMPEGN
DISPON
AMM.NE
104020
2000,00
500,00
1500,00
AMM.NE
212010
1076,18
376,18
700,00
E’ ovviamente possibile usare nella subquery tutte le funzioni che ritornano un
valore coerente con la clausola principale WHERE
101
6.2
Subquery annidate
INFN Laboratori Nazionali di Frascati
Con il termine annidamento si intende l’operazione con la quale una subquery
viene incorporata all’interno di un’altra subquery:
Select * from qualcosa where (subquery(subquery(subquery)));
Il numero degli annidamenti variano a seconda dell’implementazione SQL
Quando si ha necessità di avere più valori di ritorno da una subquery si
può ricorrere alla parola chiave IN:
select b.preute, a.capit, a.cprass * 0.01, a.cprsj * 0.01 , a.cprimp * 0.01,
a.cprcgl * 0.01 , a.cngsj * 0.01
from infn03.ccpr a, infn03.cpre b
where a.preven=b.preven and a.PREVEN in
(SELECT preven from infn03.Cpre where preven like ’44%’)
102
6.2
INFN Laboratori Nazionali di Frascati
Subquery e operatore IN
select b.preute, a.capit, a.cprass * 0.01, a.cprsj * 0.01 ,
a.cprimp * 0.01, a.cprcgl * 0.01 , a.cngsj * 0.01
from infn03.ccpr a, infn03.cpre b where a.preven=b.preven and a.PREVEN in
(SELECT preven from infn03.Cpre where preven like ’44%’)
PREUTE
CAPIT
CPRASS
CPRSJ
CPRIMP
CPRCGL
CNGSJ
DOT.GR.IV
102060
12500,00
0
7058,40
0
0
DOT.GR.IV
102061
5000,00
0
1050,00
0
0
DOT.GR.IV
102070
11500,00
0
3794,69
0
0
DOT.GR.IV
104020
10000,00
0
6863,62
0
0
DOT.GR.IV
104030
21000,00
0
4729,09
0
0
DOT.GR.IV
212010
16000,00
0
2787,60
0
0
FA51
102060
500,00
0
0
0
0
FA51
102070
1000,00
0
0
0
0
GI31
102060
1000,00
0
573,86
0
0
LF11
102060
5000,00
0
2027,70
0
0
LF11
102061
11000,00
0
2000,00
0
0
LF11
102070
14000,00
0
1600,00
0
0
LF21
102060
1500,00
0
1589,05
0
0
LF21
102061
2500,00
0
1696,98
0
0
LF21
102070
3000,00
0
1311,85
0
0
MI11
102060
1500,00
0
598,12
0
0
103
6.2
Subquery
INFN Laboratori Nazionali di Frascati
select substring(preven,1,2) RifProgr, capit, sum(cprass * 0.01) ASSEGN,
sum(cprimp * 0.01)IMPEGN, sum(cprass * 0.01) - sum(cprimp * 0.01) DISPON
from infn03.ccpr group by substring(preven,1,2), capit
having substring(preven,1,2) in
(SELECT substring(preven,1,2) from infn03.Cpre where preven like '44%')
Order by capit
RifPRogr
CAPIT
ASSEGN
IMPEGN
DISPON
44
102060
29000,00
15008,28
13991,72
44
102061
31500,00
4746,98
26753,02
44
102070
45500,00
11240,05
34259,95
44
104020
10000,00
6863,62
3136,38
44
104030
21000,00
4729,09
16270,91
44
212010
16000,00
2787,60
13212,40
select substring(preven,1,2)RifProgr, capit, sum(cprass * 0.01)ASSEGN, sum(cprimp *
0.01)IMPEGN , sum(cprass * 0.01) - sum(cprimp * 0.01) DISPON
from infn03.ccpr group by substring(preven,1,2), capit having substring(preven,1,2)='44'
order by capit
104
6.2
INFN Laboratori Nazionali di Frascati
Subquery e operatore IN
select b.preute, a.capit, sum(a.cprass * 0.01), sum(a.cprimp * 0.01)
from infn03.ccpr a, infn03.cpre b
where a.preven=b.preven and a.PREVEN in
(SELECT preven from infn03.Cpre where preven like ‘44%’)
Group by preute, capit
Order by preute, capit
PREUTE
CAPIT
CPRASS
CPRIMP
DOT.GR.IV
102060
12500,00
7058,40
DOT.GR.IV
102061
5000,00
1050,00
DOT.GR.IV
102070
11500,00
3794,69
DOT.GR.IV
104020
10000,00
6863,62
DOT.GR.IV
104030
21000,00
4729,09
DOT.GR.IV
212010
16000,00
2787,60
FA51
102060
500,00
0
FA51
102070
1000,00
0
GI31
102060
1000,00
573,86
105
6.10 Quesiti
INFN Laboratori Nazionali di Frascati
1.
Quante righe produce la combinazione di due tabelle che hanno rispettivamente
50.000 e 100.000 righe?
2.
Che tipo di combinazione genera la seguente istruzione SELECT?
SELECT ti.nome, ti.id_impiegato, ts.stipendio
FROM tabella_impiegati ti, tabella_stipendi ts
WHERE ti.id_impiegato = ts.id_impiegato;
3.
Sono corrette le seguenti istruzioni SELECT?
a.
SELECT nome, id_impegato, stipendio
FROM tabella_impiegati ti, tabella_stipendi ts
WHERE id_impegato = id_impiegato
and nome like ’%SMITH’;
b.
SELECT ti.nome, ti.id_impiegato, ts.stipendio
FROM tabella_impiegati ti, tabella_stipendi ts
WHERE nome like ’%SMITH’;
c.
SELECT ti.nome, ti.id_impiegato, ts.stipendio
FROM tabella_impiegati ti, tabella_stipendi ts
WHERE ti.id_impiegato = ts.id_impiegato
and ti.nome like ’%SMITH’;
4.
Per unire due tabelle, nella clausola WHERE occorre prima combinare le tabelle o
specificare le condizioni?
Le combinazioni delle tabelle possono essere effettuate sulla base di una sola
colonna o di più colonne?
5.
106
6.11 Esercizi
INFN Laboratori Nazionali di Frascati
1.
Nel paragrafo dedicato alla combinazione di una tabella con se stessa, l’ultimo
esempio ha fornito due combinazioni. Riscrivere la query in modo che venga
fornita una sola voce per ogni numero di parte duplicato.
2.
Riscrivere la seguente query per renderla più breve e leggibile.
SELECT ordini.ordinatoil, ordini.nome, parti.numparte, parti.prezzo,
parti.descrizione from ordini, parti where ordini.numparte = parti.numparte
and ordini.ordinatoil between ‘1-sep-96’ and ’30-sep-96’ order by
parti.numparte;
3.
Utilizzando le tabelle PARTI e ORDINI, scrivere una query per estrarre I seguenti
dati:
ORDINATOIL
NOME
NUMPARTE
QUANTITY
2-SEP-96
TRUE WHEEL
10
1
107
6.2
Esempi RIEPILOGATIVI
INFN Laboratori Nazionali di Frascati
Timbrature da Storico del badge n. 1685 relative al mese di giugno 2003:
select a.nome, a.badge, b.ANNO, b.MESE, b.GIORNO, b.HHMM, b.CAUSAL, b.EU
from pers03.anasrp as a, libsele400.storico as b
where a.badge = concat ('0', substr(DIGITS(b.badge),1,4))
and b.badge = 1685 and b.anno = 03 and b.mese = 06
NOME
BADGE
ANNO
MESE
GIORNO
JJMM
CAUSAL
EU
ANTIDORMI SANDRO
01685
3
6
3
840
0
0
ANTIDORMI SANDRO
01685
3
6
3
1456
0
1
ANTIDORMI SANDRO
01685
3
6
4
851
0
0
ANTIDORMI SANDRO
01685
3
6
4
1854
0
1
ANTIDORMI SANDRO
01685
3
6
5
840
0
0
ANTIDORMI SANDRO
01685
3
6
5
1901
0
1
ANTIDORMI SANDRO
01685
3
6
6
929
0
0
ANTIDORMI SANDRO
01685
3
6
6
1735
0
1
ANTIDORMI SANDRO
01685
3
6
9
838
0
0
ANTIDORMI SANDRO
01685
3
6
9
1640
0
1
108
6.2
Esempi RIEPILOGATIVI
INFN Laboratori Nazionali di Frascati
La seguente query serve per calcolare il totale per capitolo degli impegni iniziali
(senza variazioni) assunti con data minore di aprile 2003 (fino a tutto
marzo):
select sum(carim2) totaleImpegni from infn03.cmov2
where mmm2<4 and aam2=3 and GESTIO = 0
and sospes=0 and eu = 0 and capit = 104230 and AIPAR = 'I'
Totale impegni
7382174
Quest’altra consente di tirar fuori il totale di tutti gli impegni iniziali di
competenza relativo ai primi tre mesi del 2003:
Select sum(carim2) ImpegniIniziali from infn03.cmov2
where mmm2<4 and aam2=3 and GESTIO = 0 and sospes=0
and eu = 0 and substr(DIGITS(CAPIT),3,1) = '4' and AIPAR = 'I'
ImpegniIniziali
699561044
109
6.2
Esempi RIEPILOGATIVI
INFN Laboratori Nazionali di Frascati
la seguente istruzione consente di estrarre il totale per capitolo delle
assegnazioni relative ai criteri finora seguiti(nota bene dopo
sum(carim2)*0.01 as SSEGANZIONI serve per mettere i decimali, cioe'
divide pre cento ed intestare la colonna):
Select capit, sum(carim2)*0.01 as ASSEGNAZIONI
from infn03.cmov2
where mmm2<4 and aam2=3 and GESTIO = 0 and sospes=0 and eu = 0
and substr(DIGITS(CAPIT),3,1) = '4' and AIPAR = 'A' group by capit
CAPIT
ASSEGNAZIONI
104020
6478500,00
104170
2500000,00
104010
5000,00
104210
14500,00
104040
10500,00
104070
5000,00
104110
308000,00
104190
150000,00
104060
57000,00
104180
180000,00
110
6.2
Esempi RIEPILOGATIVI
INFN Laboratori Nazionali di Frascati
Esempio di semplice estrazione dati patrimonio (INVGEN) selezionando solo
beni ‘V’ e con consegnatario che si chiama …..IAC…. in ordine alfabetico
assegnatario:
SELECT NUMINV, AANOT, NUMNOT, DATARRIV, DESCR1, DENOM1, CONSEGN, VALCAR
FROM INFN03.INVGEN
WHERE TIPOBENE = 'V' AND CONSEGN LIKE'%IAC%'
ORDER BY CONSEGN
NUMINV
AANOT
NUMNOT
DATARRIV
DESCR1
9355
1993
119
1993-03-19
STAMPANTE H.P. MOD.3630A.SN3219A00160
2344
1989
257
1989-05-31
BANCO DA LAVORO COD.14450
9910
1988
24
1988-02-22
BANCO DA LAVORO BOTT.ART.FW11
9911
1992
134
1992-03-11
9912
1989
475
9913
1989
9914
DENOM1
HEWLETT PACKARD ITALIANA
S.P.A.
CONSEGN
VALCAR
GIACINTI O.
861,95
IACUESSA E.
586,07
UTENSIL MECCANICA S.N.C.
IACUESSA E.
953,74
LAMPADE DA TAVOLO ART 114
TECNICHE INDUSTRIALI SRL
IACUESSA E.
27,66
1989-11-13
SEGHETTO ALTERNATIVO METABO
ST450
CESARE UBOLDI S.P.A.
IACUESSA E.
85,43
475
1989-11-13
SMERIGLIATRICE METABO EX6114S
CESARE UBOLDI S.P.A.
IACUESSA E.
107,55
1989
475
1989-11-13
SEGA CIRCOLARE METABO K.S. 0852-S
CESARE UBOLDI S.P.A.
IACUESSA E.
136,44
9915
1991
9
1991-02-12
TRAPANO AVVITATORE BOSCH-GSR-
UTOR S.R.L.
IACUESSA E.
187,54
9916
1991
9
1991-02-12
TRAPANO BOSCH GSR. SN.0601930727
UTOR S.R.L.
IACUESSA E.
164,03
9355
1993
119
1993-03-19
STAMPANTE H.P. MOD.3630A.SN3219A00160
HEWLETT PACKARD ITALIANA
S.P.A.
GIACINTI O.
861,95
111
6.2
Esempi RIEPILOGATIVI
INFN Laboratori Nazionali di Frascati
Esempio di estrazione timbrature da storico
select a.nome, a.badge , b.ANNO, b.MESE, b.GIORNO, b.HHMM, b.CAUSAL, b.EU
from pers03.anasrp as a, libsele400.storico as b
where a.badge = '0' concat substr(DIGITS(b.badge),1,4) and a.badge = 1685 and
b.anno=03
nome
badge
Anno
Mese
Giorno
Hmm
Causal
eu
ANTIDORMI SANDRO
01685
3
1
7
1156
0
0
ANTIDORMI SANDRO
01685
3
1
7
2000
0
1
ANTIDORMI SANDRO
01685
3
1
8
1159
0
0
ANTIDORMI SANDRO
01685
3
1
8
2000
0
1
ANTIDORMI SANDRO
01685
3
1
9
1234
0
0
ANTIDORMI SANDRO
01685
3
1
9
2000
0
1
ANTIDORMI SANDRO
01685
3
1
10
1026
0
0
ANTIDORMI SANDRO
01685
3
1
10
2000
0
1
ANTIDORMI SANDRO
01685
3
1
13
658
0
0
ANTIDORMI SANDRO
01685
3
1
13
1453
0
1
112
6.2
Esempi RIEPILOGATIVI
INFN Laboratori Nazionali di Frascati
Costruzione grafico consumi mensili categoria IV
SELECT MMM2, SUM(CARIM2) FROM INFN02.CMOV2
WHERE AIPAR = 'I'AND EU = 0 AND GESTIO = 0
AND SOSPES = 0 AND SUBSTR(DIGITS(CAPIT),3,1) = '4'
GROUP BY MMM2
ORDER BY MMM2
SELECT MMVAR, SUM(IMPVAR) FROM INFN02.CMOV3
WHERE AIPAR = 'I' AND EU = 0 AND GESTIO = 0
AND SOSPES = 0 AND SUBSTR(DIGITS(CAPIT),3,1) = '4'
GROUP BY MMVAR
ORDER BY MMVAR
CATEGORIA IV (BENI DI CONSUMO)
IMPEGNI MENSILI 2002
MESE ASSEGNAZIONI
IMPEGNI
VARIAZ TOT IMPEGNI
quadrimestre
113
6.2
Esempi RIEPILOGATIVI
INFN Laboratori Nazionali di Frascati
Costruzione grafico consumi mensili categoria IV
GEN
FEB
MAR
APR
MAG
GIU
LUG
AGO
SET
OTT
NOV
DIC
1
CATEGORIA IV
114
SQL
CONSEGN
SELECT DISTINCT(CONSEGN)
FROM infn03.invgen
ORDER BY consegn;
ABATE EMILIO
ABENANTE MASSIMO
ANTIDORMI S.
ANTIDORMI SANDRO
SANDRO ANTIDORMI
…
…
Altri nomi
UPDATE infn03.invgen
SET CONSEGN = ‘ANTIDORMI SANDRO’
WHERE consegn LIKE ‘%ANTIDORMI%’
Il risultato è quello di avere come
assegnatario “ANTIDORMI SANDRO” in tutti
record della tabella nei quali risulta la
presenza della stringa “ANTIDORMI”