SQL
description
Transcript of SQL
1
SQL
Abbiamo inserito i dati nel DB, sappiamo che le informazioni sono nel sistema informatico, impariamo
a cercarle senza l’aiuto degli informatici.
Structured Query Language
2
….segue introduzione a SQLalcune spiegazioni sulle tabelle (Unione –
intersezione)Implementazioni (odbc – jdbc)
Domande e risposte
Breve storia di SQL1.1
In lavorazione
3
L’istruzione SELECT
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
2.1
Obiettivi:
4
Creare o cancellare una tabellaInserire, modificare o cancellare campiRicercare informazioni specifiche in più tabelle e restituire i risultati in un particolare ordineModificare i parametri di protezione di un database
SELECT: regole sintattiche generali
2.2
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:
5
SELECT: regole sintattiche generali
E’ un linguaggio abbastanza flessibile, i comandi possono
essere scritti sia in maiuscolo che in minuscolo
SELECT NOME, INDIRIZZO, CITTAFROM PRESIDENTIWHERE NOME = ‘LINCOLN’;
Select nome, indirizzo, cittaFrom presidentiWhere nome = ‘LINCOLN’;
I riferimenti ai contenuti di un database devono essere scritti esattamente come sono stati registrati.
2.3
6
Anche questa query è corretta, tuttavia usando con cura la spaziatura e le lettere minuscole/maiuscole, è possibile rendere più leggibili le istruzioni:
Select nome, indirizzo, citta, from presidenti where nome = ‘LINCOLN’;
SELECT: regole sintattiche generali
NOME, INDIRIZZO, CITTAFROM PRESIDENTIWHERE NOME = ‘LINCOLN’;
2.3
Il ; posto al termine della query serve a segnalare la fine delle istruzioni
7
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:
SELECTFROMWHERE
2.3
Consultare il manuale della versione in uso per conoscere tutte le parole chiave riservate
8
INFN Laboratori Nazionali di Frascati
SELECT e FROM sono le parole chiave più usate, indispensabili per stabilire una connessione al computer mirata a
leggere i dati
SQL> SELECT;SELECT
*ERROR at line 1;ORA-00936: missing expression
2.4
SELECT <NOMI DI COLONNE> FROM <TABELLA>
Parola chiave - istruzione – clausolaLa 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
9
INFN Laboratori Nazionali di Frascati
2.6 la prima query
SQL> select * from assegni;
Num BENEFICIARIO IMPORTO NOTE1 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 980,00 GAS
5 Supermarket GC 1.500,00 Alimentari
6 Assicurazioni Italy 250,00 Assicurazione casa
7 GAS S.p.A. 251,00 Gas
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 980,00 GAS
5 Supermarket GC 1.500,00 Alimentari
6 Assicurazioni Italy 250,00 Assicurazione casa
7 GAS S.p.A. 251,00 Gas
Num BENEFICIARIO IMPORTO NOTE
L’asterisco (*) di select * indica al database di fornire
TUTTE le colonne associate alla tabellaAS/400
10
INFN Laboratori Nazionali di Frascati
La virgola separa le singole colonne ed è sempre possibile scrivere le
istruzioni su più righe
E’ possibile indicare le singole colonne che verranno elencate nello stesso ordine indicato:
SQL> SELECT beneficiario, note, num, importo from assegni
2.6
Cambiare l’ordine delle colonne
BENEFICIARIO NOTE Num IMPORTO
Computer shop Dischetti e CD ROM 1 1.500,00
Librerie Riunite Libri, cancelleria 2 2.453,40
Computer Shop Telefono cellualare 3 2.003,20
BIOGA srl GAS 4 980,00
Supermarket GCAlimentari 5 1.500,00
Assicurazioni Italy Assicurazione casa 6 250,00
GAS S.p.A. Gas 7 251,00
11
INFN Laboratori Nazionali di Frascati
Sono state indicate istruzioni sia in maiuscolo che in minuscolo, come si può notare il risultato è lo stesso.
Per selezionare solo alcune colonne basta indicarle, solo quelle dichiarate verrano elencate nell’ordine indicato:
SQL> SELECT num, importo from assegni
2.6
Selezionare le colonne
Num IMPORTO1 1.500,00
2 2.453,40
3 2.003,20
5 1.500,00
6 250,00
4 980,00
7 251,00
AS/400
12
INFN Laboratori Nazionali di Frascati
Vengono elencate tutte le colonne della tabella DEPOSITI con le righe ordinate direttamente dal database
Per selezionare dati da un’altra tabella è sufficiente modificare la clausola FROM
SQL> SELECT * from depositi
2.6
Selezionare tabelle differenti
DEPOSITO CHIHAPAGATO IMPORTO NOTE1 Zio ricco 2000,00 regalo di Natale
2 Mia azienda 2.453,40Stipendio
3 Trasporti S.r.L. 250,00 Affitto garage
AS/400
13
INFN Laboratori Nazionali di Frascati
Esaminando il contenuto della tabella assegni alucni importi sono duplicati:
SQL> SELECT importo from assegni
2.7
Query senza duplicati
IMPORTO
1.500,00
2.453,40
2.003,20
980,00
1.500,00
250,00
251,00
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
Con la clausola DISTINCT l’output riporta una sola
istanza dei dati duplicatiAS/400
14
INFN Laboratori Nazionali di Frascati
2.8
Riepilogo
Le parole SELECT e FROM consentono a una query di caricare dei dati.
La parola chiave DISTINCT limita l’output delle query poiché consente di escludere i valori duplicati di una colonna.
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.
15
INFN Laboratori Nazionali di Frascati
2.9
Quesiti
Le seguenti query non funzionano. Perché?a. Select *b. Select * from assegnic. Select importo numAssegno beneficiario FROM assegni;
Le seguenti istruzioni forniscono lo stesso risultato?SELECT * FROM ASSEGNIselect * from assegni;
Quale delle seguenti istruzioni SQL è corretta?a. select *
from assegni;b. select * from assegni;c. select * from assegni /
AS/400
16
INFN Laboratori Nazionali di Frascati
2.10
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
17
Espressioni, condizioni e 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
INFN Laboratori Nazionali di Frascati
3.1
ObiettiviApprofondire le clausole SELECT e FROM, ampliare la query con qualche nuovo termine e introdurre gli operatori.
18
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;
INFN Laboratori Nazionali di Frascati
Espressioni
3.2
La definizione di espressione è semplice:
un’espressione restituisce un valore
AS/400
19
Nella seguente istruzione, NOME, INDIRIZZO, TELEFONO E RUBRICA sono espressioni:
SELECT NOME, INDIRIZZO, TELEFONO, RUBRICA
FROM RUBRICA;
INFN Laboratori Nazionali di Frascati
Espressioni
3.2
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.
20
Tutte le volte che si vuole trovare un particolare elemento o gruppo di elementi in un database, occorre specificare una o
più condizioni.
Condizioni
3.3
Per trovare tutti gli impiegati che hanno lavorato più di 100
ore la condizione potrebbe essere: NUMERODIORE > 100
Le condizioni sono introdotte dalla clausola WHERE.Nell’esempio precedente la condizione è NOME =
‘ROSSI’
21
INFN Laboratori Nazionali di Frascati
Condizioni
3.3
Per scrivere una query condizionale bisogna conoscere la clasuola WHERE e gli operatori
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………. >
22
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:
INFN Laboratori Nazionali di Frascati
3.3
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
SQL> SELECT * FROM BICICLETTE;
La clausola WHERE
23
INFN Laboratori Nazionali di Frascati
3.3
NOME TELAIO
MATERIALE Km percorsi tipo
GIANT 56 ACCIAIO 2000 TANDEM
Questo semplice esempio mostra come applicare una condizione sui dati da caricare
Per selezionare una particolare bicicletta si può usare la seguente query:
SELECT * FROM BICICLETTE WHERE NOME = ‘GIANT’;che fornisce un solo record:
La clausola WHERE
24
INFN Laboratori Nazionali di Frascati
3.4
E’ un potente gruppo di strumenti a base della conoscenza del linguaggio SQL
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
25
INFN Laboratori Nazionali di Frascati
3.4
Non funziona con i tipi di dati che hanno cifre decimali
Operatori aritmetici
1. + (somma)2. - (sottrazione)3. / (divisione)
I primi quattro operatori si spiegano da soli.L’operatore modulo restituisce il resto di una
divisione. Ad esempio:5 % 2 = 16 % 2 = 0
4. * (moltiplicazione)5. % (modulo o resto)
26
INFN Laboratori Nazionali di Frascati
3.4
Attenzione alla posizione delle parentesi!
Operatori aritmetici
2 * 6 + 9 / 3 vale 12 + 3 = 15
mentre l’espressione
2 * (6 + 9) / 3 vale 2 * 15 / 3 = 10
Se vengono inseriti più operatori aritmetici in una espressione senza parentesi, essi vengono valutati nell’ordine: moltiplicazione, divisione, modulo, somma e sottrazione. Ad esempio:
27
INFN Laboratori Nazionali di Frascati
3.4 L’operatore somma (+)Può essere usato in vari modi. La seguente
espressione visualizza il contenuto della tabella PREZZO:
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 ELEMENTO, PREZZOINGROSSO, PREZZOINGROSSO + 150 FROM PREZZO
ELEMENTO PREZZOINGROSSO
PREZZOINGROSSO + 150
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 4,80
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.
28
INFN Laboratori Nazionali di Frascati
3.4L’operatore somma
(+)
E’ possibile assegnare una intestazione più comprensibile alla nuova colonna:
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
SQL> SELECT ELEMENTO, PREZZOINGROSSO,(PREZZOINGROSSO + 1.50) PREZZODETTAGLIO FROM PREZZO
29
INFN Laboratori Nazionali di Frascati
3.4L’operatore somma
(+)
E’ possibile assegnare una intestazione più comprensibile anche a colonne esistenti:
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
SQL> SELECT ELEMENTO PRODOTTO, PREZZOINGROSSO, (PREZZOINGROSSO + 1.50) PREZZODETTAGLIO FROM PREZZO
La colonna ELEMENTO è stata ridenominata PRODOTTO.E’ comunque consigliabile consultare la documentazione della propria implementazione SQL per conoscere la sintassi esatta.
30
INFN Laboratori Nazionali di Frascati
3.4L’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
REGIONE TEMPMAX TEMPMIN
Piemonte 4 -10
Toscana -4 -13
Sicilia -10 -19
Lombardia
2 -9
Friuli 3 -8
SQL> SELECT REGIONE, -TEMPMAX, -TEMPMINFROM MINMAX
31
INFN Laboratori Nazionali di Frascati
3.4 L’operatore sottrazione (-)
La seconda (e ovvia) funzione dell’operatore meno è quella di sottrarre i valore di una colonna da quelli di
un’altra colonna.Esempio:
REGIONE MINIME MASSIME
DIFFERENZE
Piemonte -4 10 14
Toscana 4 13 9
Sicilia 10 19 9
Lombardia
-2 9 11
Friuli -3 8 11
SQL> SELECT REGIONE,
TEMPMAX MINIME,
TEMPMIN MASSIME,
(TEMPMIN - TEMPMAX) DIFFERENZA
FROM MINMAX;
Oltre che aver creato la nuova colonna questa query ha corretto (solo sullo schermo) i nomi di quelle errate.
32
INFN Laboratori Nazionali di Frascati
3.4
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
33
INFN Laboratori Nazionali di Frascati
3.4L’operatore divisione (/)
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,15L’uso dell’operatore divisione in questa istruzione SELECT è semplice,
anche se vendere a metà prezzo non è proprio convienente.
34
INFN Laboratori Nazionali di Frascati
3.4 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.07Questi operatori permettono di svolgere con grande efficienza calcoli aritmetici nell’istruzione SELECT
35
3.4L’operatore modulo (%)
L’operatore modulo restituisce il resto intero di una operazione di divisione. Esempio:
SQL> SELECT * FROM RESTINUMERATORE
DENOMINATORE
10 5
8 3
23 9
1024 16E’ possibile creare una nuova colonna, RESTO, dove registrare il
resto della divisione tra NUMERATORE e DENOMINATORESQL> SELECT NUMERATORE, DENOMINATORE,
NUMERATORE%DENOMINATORE RESTO FROM RESTI
NUMERATORE
DENOMINATORE
RESTO
10 5 0
8 3 2
23 9 5
1024 16 0
36
INFN Laboratori Nazionali di Frascati
3.4
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; ELEMENT
OPREZZOINGROSSO
LimoniNel database la colonna prezzoingrosso della riga Limoni non contiene dati (non è zero)
37
INFN Laboratori Nazionali di Frascati
3.4 L’operatore (=)Nella clausola WHERE il segno uguale è l’operatore di confrontopiù 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 050 880245
NERI ALE BOLOGNA 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.
38
INFN Laboratori Nazionali di Frascati
3.4 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 PREZZOWHERE PREZZOINGROSSO > 4.50;
ELEMENTO PREZZOINGROSSO
Patate 5.10
Banane 6.70
Arance 8.90
SQL> SELECT * FROM PREZZOWHERE 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
39
INFN Laboratori Nazionali di Frascati
3.4 L’operatore maggiore (<) e maggiore uguale (<=)
questi operatori operano in senso inverso al precedente:
COGNOME NOME CITTA TELEFONO
ROSSI ALE MILANO 02 3425678
BIANCHI SABY TORINO 011 6707221
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
COGNOME NOME
CITTA TELEFONO
BROWN JO PISA 050 880245
NERI ALE BOLOGNA 051 6711
SQL> SELECT * FROM AMICI;
SQL> SELECT * FROM AMICI WHERE CITTA < ‘MILANO’ ;
40
INFN Laboratori Nazionali di Frascati
3.4
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):
COGNOME NOME CITTA TELEFONO
BIANCHI SABY TORINO 011 6707221
BROWN JO PISA 050 880245
NERI ALE BOLOGNA 051 6711
In molte implementazione SQL è indifferente usare la forma (<>) anzichè (!=)
SQL> SELECT * FROM AMICI WHERE NOME <> ‘ALE’;
41
INFN Laboratori Nazionali di Frascati
3.4 Gli operatori di carattereConsentono di manipolare il modo in cui debbono essere
rappresentate le stringhe durante la preparazione delle condizioni che selezionano i
dati.
L’operatore LIKEconsente 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.
42
INFN Laboratori Nazionali di Frascati
3.4
L’operatore LIKE
SQL> SELECT * FROM PARTI WHERE POSIZIONE LIKE ‘%DORSO%’;
NOME POSIZIONE NUMEROPARTE
VERTEBRE CENTRO-DORSO 4
RENE DORSO 6
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
RENE DORSO 6
SQL> SELECT * FROM PARTI WHERE POSIZIONE LIKE ‘%DORSO’;
NOME POSIZIONE NUMEROPARTE
VERTEBRE CENTRO-DORSO 4
43
INFN Laboratori Nazionali di Frascati
3.4
L’operatore LIKE
SQL> SELECT * FROM PARTI WHERE NOME LIKE ‘F%’;
NOME POSIZIONE NUMEROPARTE
FEGATO DESTRA-ADDOME 1
FARINGE GOLA 3
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 ‘f%’;no rows selected.
L’operatore like è sempre sensibile ai caratteri minuscoli/maiuscoli
44
INFN Laboratori Nazionali di Frascati
3.4
Il carattere di sottolineatura è un carattere jolly singolo.
E’ possibile utilizzare più di un carattere di sottolineatura e può essere combinato con l’operatore LIKE.
SQL> SELECT * FROM AMICI WHERE PROVINCIA LIKE ‘_O’;
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
COGNOME NOME CITTA PROVINCIA TELEFONO
BIANCHI SABY TORINO TO 011 6707221
NERI ALE BOLOGNA BO 051 6711
Il carattere di sottolineatura (_)
45
INFN Laboratori Nazionali di Frascati
3.4L’operatore di concatenazione (||)
SQL> SELECT NOME || COGNOME NOMECOMPLETO FROM AMICI;
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
Il simbolo || serve a concatenare due stringhe:
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
46
INFN Laboratori Nazionali di Frascati
3.4
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
47
INFN Laboratori Nazionali di Frascati
3.4
Esempio operatore AND
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
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:
SQL> SELECT COGNOME,ANNI * 12 – FERIEGODUTE [AS] FERIERESTANTIFROM TABELLAFERIEWHERE COGNOME LIKE ‘’B%’ ANDANNI * 12 – FERIEGODUTE > 50;
COGNOME FERIERESTANTI
BLESSI 51
BOLDI 80
48
INFN Laboratori Nazionali di Frascati
3.4
L’operatore AND
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
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:
SQL> SELECT *FROM FERIEWHERE ANNI <= 5 ANDFERIEGODUTE > 20;
COGNOME NUMDIP
ANNI FERIEGODUTE
BACCHI 104 5 23
BOLIVAR 233 4 80
49
INFN Laboratori Nazionali di Frascati
3.4
L’operatore OR
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
L’operatore logico OR puo’ essere utilizzato per combinare una serie di condizioni. Se una di queste è soddisfatta viene restituito TRUE
SQL> SELECT *FROM FERIEWHERE COGNOME LIKE ‘BO
%’ORCOGNOME LIKE ‘CO%’;COGNOME NUMDI
PANNI 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
INFN Laboratori Nazionali di Frascati
3.4
L’operatore NOT
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
L’operatore logico NOT ha il compito di invertire il significato di una condizione
SQL> SELECT *FROM FERIEWHERE 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
51
INFN Laboratori Nazionali di Frascati
3.4
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
ABITA
BRAVO
CARLINI
DECCA
ESTERLE
FUNDARI
GIANI
COGNOME
ABITA
BACCO
CARLINI
DINI
ESTERLE
FALCONI
GIANI
SQL> SELECT COGNOME FROM CALCETTOUNIONSELECT COGNOME FROM CALCIO;
calcio
calcetto
COGNOME
ABITA
BACCO
BRAVO
CARLINI
DECCA
DINI
ESTERLE
FALCONI
FUNDARI
GIANI
Sono stati esclusi i doppioni
A + B - C
A BC
AS/400
52
INFN Laboratori Nazionali di Frascati
3.4Gli operatori di insieme: UNION ALL
L’operatore UNION ALL restituisce il risultato di due query incluse le righe duplicate.
COGNOME
ABITA
BRAVO
CARLINI
DECCA
ESTERLE
FUNDARI
GIANI
COGNOME
ABITA
BACCO
CARLINI
DINI
ESTERLE
FALCONI
GIANI
SQL> SELECT COGNOME FROM CALCETTOUNION ALLSELECT COGNOME FROM CALCIO;
calcio
calcetto
COGNOME
ABITA
BRAVO
CRLINI
DECCA
ESTERLE
FUNDARI
GIANI
ABITA
BACCO
CARLINI
DINI
ESTERLE
FALCONI
GIANI
Sono incluse tutte le righe
Esempio: quante persone giocano al calcetto e quante al calcio?
A + B
A BC
AS/400
53
INFN Laboratori Nazionali di Frascati
3.4Gli operatori di insieme: INTERSECT
L’operatore INTERSECT restituisce soltanto le righe che vengono trovate in entrambe le query
COGNOME
ABITA
BRAVO
CARLINI
DECCA
ESTERLE
FUNDARI
GIANI
COGNOME
ABITA
BACCO
CARLINI
DINI
ESTERLE
FALCONI
GIANI
SQL> SELECT COGNOME FROM CALCETTOINTERSECTSELECT COGNOME FROM CALCIO;
calcio
calcetto
COGNOME
ABITA
CRLINI
ESTERLE
GIANI
Sono inclusi solo le righe comuni
Esempio: quali persone giocano in entrambe le squadre?
C
A BC
NON AS/400
select * from corso.calciowhere cognome in (select cognome from corso.calcetto)
54
INFN Laboratori Nazionali di Frascati
3.4 Gli operatori di insieme: MINUS (differenza)
L’operatore MINUS restituisce le righe della prima query che non sono presenti nella seconda
COGNOME
ABITA
BRAVO
CARLINI
DECCA
ESTERLE
FUNDARI
GIANI
COGNOME
ABITA
BACCO
CARLINI
DINI
ESTERLE
FALCONI
GIANI
SQL> SELECT COGNOME FROM CALCETTOMINUSSELECT COGNOME FROM CALCIO;
calcio
calcetto
COGNOME
BACCCO
DINI
FALCONI
Seleziona quelli che giocano solo al calcetto
Esempio: quali persone giocano solo al calcetto?
A - C
A BC
NON AS/400
55
INFN Laboratori Nazionali di Frascati
3.4Gli operatori di insieme: IN e BETWEEN
Forniscono la scorciatoia per operazioni che possono essere svolte in altri modi, ad esempio:
SQL> SELECT * FROM AMICIWHERE PROVINCIA IN (‘CO’, ‘PV’,
‘‘BG’);
Quali amici vivono in provincia di Como, Pavia e Bergamo?
In alternativa si può usare la seguente query:
SQL> SELECT * FROM AMICIWHERE PROVINCIA = ‘CO’OR PROVINCIA = ‘PV’OR PROVINCIA = ‘BG’;
L’operatore IN può essere utilizzato anche per i numeri:
SQL> SELECT * FROM DIPENDENTIWHERE ETASERVIZIO IN (15, 25, 40);
Verrano selezionati I dipendenti che hanno ETASERVIZIO uguale ad uno dei tre valori espressi
56
INFN Laboratori Nazionali di Frascati
3.4 L’operatore BETWEENConsente 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 PREZZOWHERE PREZZOINGROSSO > 2.50ANDPREZZOINGROSSO < 7.50; ELEMENTO PREZZOINGROSSO
Pomodori 3.40
Patate 5.10
Banane 6.70
Rape 4.50
In alternativa si può utilizzare l’operatore BETWEEN:
SQL> SELECT * FROM PREZZOWHERE 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
57
INFN Laboratori Nazionali di Frascati
3.5
Quesiti
Utilizzare la tabella AMICIper rispondere ai quesiti
successivi
1. Scrivere una query per selezionare tutti i cognomi che iniziano con la lettera M;
2. Scrivere una query per selezionare gli amici che vicono in provincia di Bergamo e il cui nome è Ale;
3. 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;
4. Quale scorciatoia potrebbe essere utilizzata in alternativa a WHERE a >=10 AND a<=30?
5. Qual è il risultato di questa query?SELECT NOMEFROM AMICIWHERE NOME = ‘ALE’
AND COGNOME = ‘BANFI’;
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
AMICI
58
INFN Laboratori Nazionali di Frascati
3.6
Esercizi
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
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
59
Funzioni
Aggregate Temporali Aritmetiche Di caratteri
Di conversione varie
INFN Laboratori Nazionali di Frascati
4.1
ObiettiviDescrivere 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
60
Funzioni aggregate: COUNT
INFN Laboratori Nazionali di Frascati
4.1
Restituisce il numero di righe che soddisfano la condizione specificata nella clausola WHERE
SQL> SELECT COUNT(*) FROM INFN03.INVGENWHERE TIPOBENE = 'L';
COUNT(*)
521
Per rendere più leggibile codice e output si può usare l’alias:
SQL> SELECT COUNT(*) LIBRIFROM INFN03.INVGENWHERE TIPOBENE = 'L';
LIBRI
521
Se fosse indicato un campo al posto dell’asterisco il risultato non cambierebbe:
SQL> SELECT COUNT(NUMINV) LIBRI
FROM INFN03.INVGENWHERE TIPOBENE = 'L';
LIBRI
521
AS/400
61
Funzioni aggregate: COUNT
INFN Laboratori Nazionali di Frascati
4.1
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
62
INFN Laboratori Nazionali di Frascati
4.1SUM
La funzione SUM somma tutti i valori di una colonna.
SQL> SELECT SUM(IMPVAR) VARIAZIONIFROM INFN03.CMOV3;
VARIAZIONI
-27723897
SQL> SELECT SUM(IMPVAR) * 0.01 VARIAZIONI FROM INFN03.CMOV3;
VARIAZIONI
-277238.9
7 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
63
INFN Laboratori Nazionali di Frascati
4.1MAXLa funzione MAX serve a trovare il valore massimo di una colonna.
SQL> SELECT MAX(IMPVAR) VARIAZMAXFROM INFN03.CMOV3;
VARIAZMAX
470000
SQL> SELECT MAX(IMPVAR) * 0.01 VARIAZMAX FROM INFN03.CMOV3;
VARIAZMAX
4700.00
SQL> SELECT PREVEN FROM INFN03.CMOV3WHERE IMPVAR = MAX(IMPVAR)
Error at line 3: ORA-00934 group function is not alllowed here
Quale centro di costo ha la variazione più alta?
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
64
INFN Laboratori Nazionali di Frascati
4.1MINOpera in modo analogo alla funzione MAX, ovviamente restituisce il valore minimo della colonna
SQL> SELECT MIN(IMPVAR) VARIAZMINFROM INFN03.CMOV3;
VARIAZMIN
-5000000
SQL> SELECT MIN(IMPVAR) * 0.01 VARIAZMIN FROM INFN03.CMOV3;
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 VARIAZMA
X
4700.00 -50000.00
AS/400
65
Funzioni temporali INFN Laboratori Nazionali di Frascati
4.1
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 PROGETTOWHERE 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
66
Funzioni di carattere
INFN Laboratori Nazionali di Frascati
4.1
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
SQL> SELECT SUBSTR(CAMPO, 2, 3)……..
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,3. Il numero di caratteri da estrarre
funzione SUBSTR
Nome colonna Posizione iniziale
Lunghezza sottostringa
67
SUBSTR
INFN Laboratori Nazionali di Frascati
4.1
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)CATEGORIAaltre colonneFROM 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
68
SUBSTR
INFN Laboratori Nazionali di Frascati
4.1
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.CMOV3WHERE SUBSTR(DIGITS(CAPIT),2,2) = '04'
VARIAZIONICONSUMO
-112587.07
AS/400
69
CONCAT
INFN Laboratori Nazionali di Frascati
4.1
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.
70
Quesiti
INFN Laboratori Nazionali di Frascati
4.10
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;
71
Esercizi
INFN Laboratori Nazionali di Frascati
4.10
1) Calcola totale degli impegni di competenzanome tabella: infn03.cmov2condizione: RESID = 0
CAPIT=104020
2) Calcola il numero di variazioni sui capitoli residuinome tabella: infn03.cmov3condizione: RESID = 1
3) Calcola quale è l’importo più alto tra le variazioni sia di competenza che relative ai residui:nome tabella: infn03.cmov3
72
Le clausole di SQL INFN Laboratori Nazionali di Frascati
5
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]
73
La clausola WHEREINFN Laboratori Nazionali di Frascati
5.1
Utilizzando soltanto la clausola SELECT e FROM la query seleziona tutte le righe di una tabella, ad esempio:
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
4 BIOGAS srl 980.00 Gas
5 SuperMarket GC 1500.00 Alimentari
6 Assicurazioni Italy 250.00 Assicurazione casa
7 GAS Auto S.p.A. 251.00 Gas
SQL> SELECT * FROM ASSEGNI
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
74
La clausola WHEREINFN Laboratori Nazionali di Frascati
5.1
La clausola WHERE può risolvere anche altri problemi. Ad esempio data la tabella di nomi e luoghi è possibile porre la domanda:” Laura dov’è?”
NOME LUOGO
TANIA CORTILE
MARCO CUCINA
SILVIO BAGNO
PIPPO GARAGE
LAURA SOGGIORNO
ARMANDO
TERRAZZA
SQL> SELECT * FROM PUZZLE
SQL> SELECT LUOGO AS "LAURA DOV'E'?"FROM PUZZLEWHERE 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
75
La clausola STARTING WITH
INFN Laboratori Nazionali di Frascati
5.1
La clausola STARTING WITH è un’aggiunta alla clausola WHERE che opera esattamente come LIKE(<espr>%). Confrontiamo i risultati delle seguenti query:
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 LIKE(‘Co%’);
I risultati sono identici. Le due clausole possono essere usate insieme
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
NON AS/400
76
La clausola STARTING WITH
INFN Laboratori Nazionali di Frascati
5.1
SQL> SELECT BENEFICIARIO, IMPORTO, NOTE FROM ASSEGNI WHERE BENEFICIARIO STARTING WITH(‘Co%’)
OR NOTE LIKE ‘A%’;
ASSEGNO
BENEFICIARIO IMPORTO NOTE
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 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
77
La clausola ORDER BY
INFN Laboratori Nazionali di Frascati
5.4
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:
ORDER richiede sempre BY (BY non è facoltativo)
SQL> SELECT * FROM ASSEGNI ORDER BY ASSEGNO;
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
4 BIOGAS srl 980.00 Gas
5 SuperMarket GC 1500.00 Alimentari
6 Assicurazioni Italy 250.00 Assicurazione casa
7 GAS Auto S.p.A. 251.00 Gas
78
La clausola ORDER BY
INFN Laboratori Nazionali di Frascati
5.4
E’ possibile ordinare I record in senso inverso
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
SQL> SELECT * FROM ASSEGNI ORDER BY NUM DESC;
NUM BENEFICIARIO IMPORTO NOTE
7 GAS Auto S.p.A. 251.00 Gas
6 Assicurazioni Italy 250.00 Assicurazione casa
5 SuperMarket GC 1500.00 Alimentari
4 BIOGAS srl 980.00 Gas
3 Computer Shop 2003.20 Telefono cellulare
2 Librerie Riunite 2453.40 Libri, cancelleria
1 Computer Shop 1500.00 Dischetti e CD-Rom
79
La clausola ORDER BY
INFN Laboratori Nazionali di Frascati
5.4
La clausola ORDER BY può essere applicata a più campi:
Come era facilmente immaginabile il risultato è di avere record ordinati per beneficiario e nell’ambito di ogni beneficiario per numero.
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 Gas
2 Librerie Riunite 2453.40 Libri, cancelleria
5 SuperMarket GC 1500.00 Alimentari
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)
80
La clausola GROUP BYINFN Laboratori Nazionali di Frascati
5.5
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 GROUP BY BENEFICIARIO;
BENEFICIARIO NUMOPE IMPORTO
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
81
La clausola GROUP BYINFN Laboratori Nazionali di Frascati
5.5
SQL> SELECT consegn Consegnatario, count(consegn) NumeroBeni, (SUM(valcar) - SUM(valsca)) ValoreBeniFROM infn03.invgenGROUP BY consegnORDER BY Consegnatario;
Consegnatario NumeroBeni ValoreBeni
AGOSTINI E. 10 5983,13
ALBICOCCO M. 36 93716,50
ANTIDORMI S. 9 8467,20
ANTIDORMI SANDRO 69 147152,49
EsempioIndicare per consegnatario il numero e l’ammontare del valore dei beni
patrimoniali in carico (nome tabella: infn03.invgen)
82
La clausola GROUP BYINFN Laboratori Nazionali di Frascati
5.5
SQL> SELECT consegn Consegnatario, count(consegn) NumeroBeni, (SUM(valcar) - SUM(valsca)) ValoreBeniFROM infn03.invgenGROUP BY ubicaz, consegnORDER BY consegn;
Consegnatario NumeroBeni ValoreBeni
AGOSTINI E. 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
Cosa accade se la clausola GROUP BY specifica più colonne?
83
La clausola GROUP BYINFN Laboratori Nazionali di Frascati
5.5
SQL> SELECT consegn, valcar, count(valcar) FROM infn03.invgen GROUP BY consegn;
Dynamic SQL Error-SQL error code = -104-invalid column reference
Osserviamo la seguente query:
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 131069,61
ANTIDORMI SANDRO Ufficio EDP 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
84
La clausola HAVINGINFN Laboratori Nazionali di Frascati
5.6
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 GROUP BY DIVISIONE;DIVISIONE STIPENDIO
COLLECTIONS 37500.00
MARKETING 36333.33
PR 37500.00
RESEARCH 39500.00
85
La clausola HAVINGINFN Laboratori Nazionali di Frascati
5.6
Proviamo a qualificare una query che consente di estrarre soltanto quelle divisioni i cui stipendi medi sono minori di 38000:
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
SQL> SELECT DIVISIONE, AVG(STIPENDIO)
FROM ORGCHART
WHERE AVG(STIPENDIO) < 38000
GROUP BY DIVISIONE;
Dynamic SQL Error
-SQL error code = -104
-Invali aggregate reference
86
La clausola HAVING
INFN Laboratori Nazionali di Frascati
5.6
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:
DIVISIONE STIPENDIO
PR 37500.00
SQL> SELECT DIVISIONE, AVG(STIPENDIO)
FROM ORGCHART
GROUP BY DIVISIONE
WHERE STIPENDIO < 38000;
Come mai questo risultato é diverso dall’ultima query?
87
La clausola HAVING INFN Laboratori Nazionali di Frascati
5.6
SQL> SELECT NOME, DIVISIONE, STIPENDIO
FROM ORGCHART
ORDER BY DIVISIONE
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.
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.
88
La clausola HAVING
INFN Laboratori Nazionali di Frascati
5.6
SQL> SELECT DIVISIONE, AVG(CONGEDMAL), AVG(CONGEDANN)
FROM ORGCHART
GROUP BY DIVISIONE
HAVING AVG(CONGEDMAL) > 25 AND
AVG(CONGEDANN) < 20;
E’ anche possibile utilizzare più condizioni.Ad esempio:
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.
89
La clausola HAVING INFN Laboratori Nazionali di Frascati
5.6
SQL> SELECT DIVISIONE, AVG(CONGEDMAL), AVG(CONGEDANN)
FROM ORGCHART
GROUP BY DIVISIONE
HAVING COUNT(DIVISIONE) > 1;
Altri esempi:
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
90
La clausola HAVING
INFN Laboratori Nazionali di Frascati
5.6
SQL> SELECT DIVISIONE, AVG(STIPENDIO)
FROM corso.ORGCHART
GROUP BY DIVISIONE
HAVING DIVISIONE IN ('PR', 'RESEARCH');
L’operatore IN opera anche nella clausola HAVING:
DIVISIONE AVG
PR 37500.00
RESEARCH 39500.00
91
Combinazione di clausoleINFN Laboratori Nazionali di Frascati
5.7
SQL> SELECT BENEFICIARIO, NOTE
FROM ASSEGNI
WHERE BENEFICIARIO = ‘Computer Shop’
or NOTE like(‘Ga%’)
ORDER BY NOTE;
Esempio 1Trovare tutti gli assegni emessi a favore di Computer Shop e per le
forniture del gas ordinandoli in base alla colonna 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
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
SELECT BENEF, NOTEFROM corso.ASSEGNIWHERE BENEF = 'Computer Shop'or NOTE like('Ga%')ORDER BY NOTE;
92
Combinazione di clausoleINFN Laboratori Nazionali di Frascati
5.7
SQL> SELECT NOME, STIPENDIO
FROM ORGCHART
WHERE CONGEDOMALATTIA < 25
ORDER BY NOME;
Esempio 2Utilizzando la tabella ORGCHART trovare lo stipendio di quei
dipendenti che hanno meno di 25 giorni di congedi per malattia, ordinando I record per nome:
E’ una query semplice che offre l’opportunità di utilizzare insieme la clausola WHERE e ORDER BY
NOME STIPENDIO
FURY 35000.00
MERRICK 45000.00
PRECOURT 37500.00
STOKES 36000.00
93
Combinazione di clausole
INFN Laboratori Nazionali di Frascati
5.7
SQL> SELECT DIVISIONE,
AVG(STIPENDIO),
AVG(CONGEDIMALATTIA),
AVG(CONGEDIANNUI)
FROM ORGCHART
GROUP BY DIVISIONE;
Esempio 3Utilizzando la tabella ORGCHART elencare gli stipendi medi, la
media dei congedi per malattia e quella dei congedi annui per ogni 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
94
Combinazione di clausole INFN Laboratori Nazionali di Frascati
5.7
SQL> SELECT DIVISIONE,
AVG(STIPENDIO), AVG(CONGEDIMALATTIA, AVG(CONGEDIANNUI)
FROM ORGCHART GROUP BY DIVISIONE ORDER BY NOME;
Segue una interessante variante della query precedente, proviamo a spiegarne I risultati ottenuti:
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
95
Quesiti
INFN Laboratori Nazionali di Frascati
5.8
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), DIPARTIMENTOFROM TAB_STIPWHERE DIPARTIMENTO = ‘PERSONALE’ORDER BY NOMEGROUP BY DIPARTIMENTO, STIPENDIO
4. Quando si usa la clausola HAVING, è necessario usare anche GROUP BY?
5. E’ possibile applicare ORDER BY a una colonna che non è inclusa tra quelle citate nella clausola SELECT?
96
Esercizi
INFN Laboratori Nazionali di Frascati
5.9
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
BENEFICIARIO IMPORTO
1 Computer Shop 1500.00
97
Combinazione di tabelle e subquery INFN Laboratori Nazionali di Frascati
6.0
Obiettivi: Leggere e manipolare dati che si trovano in più tabelle Creare un subquery (collegare una query al risultato di
un’altra query)
98
Più tabelle in una sola SELECT
INFN Laboratori Nazionali di Frascati
6.1
SELECT * FROM TABELLA1
RIGA NOTE
1 Nota 1 tabella 1
2 Nota 2 tabella 1
3 Nota 3 tabella 1
SELECT * FROM TABELLA1
RIGA NOTE
1 Nota 1 tabella 2
2 Nota 2 tabella 2
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
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
tabella2
tabella1
Tabellarisultante
99
Più tabelle in una sola SELECT
INFN Laboratori Nazionali di Frascati
6.1
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:
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
Num Quantita
Descrizione Prezzo IVA
1 1500 lt. di g.p.l. propano per lab.esterni 5,4 20
2 1 minubus 16 posti 600,0 10
2 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
ORDINI
RIGHEORDINI
Come combinare opportunamente le due tabelle?
100
Più tabelle in una sola SELECT INFN Laboratori Nazionali di
Frascati6.1
Per combinare opportunamente due o più tabelle è utile la clausola WHERE, ad esempio:
Numpar ordI1 QTARIG DESRIG PREUNI
1 CENTRO GAS S.R.L. 1500 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 0
4 GHIZZONI SERAFINO 1 noleggio n. 2 pulmini per visite 72,00
4 GHIZZONI SERAFINO 0 partecipanti meeting di auger 0
5 CIVICO 11 STUDIO GRAFICO 1 ideazione progettazione grafica e 198,84
5 CIVICO 11 STUDIO GRAFICO 0 videoimpaginazione di n. 1 manifesto
0
SELECT a.numpar, a.ordi1, b.qtarig, b.desrig, b.preuni * 0.01
FROM infn03.cord1 as a, infn03.cord2 as bWHERE a.numpar =b.numpar
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.
101
SubqueryINFN Laboratori Nazionali di Frascati
6.2
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
102
Subquery annidateINFN Laboratori Nazionali di Frascati
6.2
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
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%’)
Quando si ha necessità di avere più valori di ritorno da una subquery si può ricorrere alla parola chiave IN:
103
Subquery e operatore ININFN Laboratori Nazionali di Frascati
6.2
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
104
Subquery INFN Laboratori Nazionali di Frascati
6.2
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
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
105
Subquery e operatore IN
INFN Laboratori Nazionali di Frascati
6.2
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
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
106
Quesiti INFN Laboratori Nazionali di Frascati
6.10
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.stipendioFROM tabella_impiegati ti, tabella_stipendi tsWHERE 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 tsWHERE id_impegato = id_impiegatoand nome like ’%SMITH’;
b. SELECT ti.nome, ti.id_impiegato, ts.stipendioFROM tabella_impiegati ti, tabella_stipendi tsWHERE nome like ’%SMITH’;
c. SELECT ti.nome, ti.id_impiegato, ts.stipendioFROM tabella_impiegati ti, tabella_stipendi tsWHERE ti.id_impiegato = ts.id_impiegatoand ti.nome like ’%SMITH’;
4. Per unire due tabelle, nella clausola WHERE occorre prima combinare le tabelle o specificare le condizioni?
5. Le combinazioni delle tabelle possono essere effettuate sulla base di una sola colonna o di più colonne?
107
Esercizi INFN Laboratori Nazionali di Frascati
6.11
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
108
Esempi RIEPILOGATIVI INFN Laboratori Nazionali di Frascati
6.2
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
select a.nome, a.badge, b.ANNO, b.MESE, b.GIORNO, b.HHMM, b.CAUSAL, b.EUfrom pers03.anasrp as a, libsele400.storico as bwhere a.badge = concat ('0', substr(DIGITS(b.badge),1,4))
and b.badge = 1685 and b.anno = 03 and b.mese = 06
Timbrature da Storico del badge n. 1685 relative al mese di giugno 2003:
109
Esempi RIEPILOGATIVI INFN Laboratori Nazionali di Frascati
6.2
Totale impegni
7382174
select sum(carim2) totaleImpegni from infn03.cmov2where mmm2<4 and aam2=3 and GESTIO = 0 and sospes=0 and eu = 0 and capit = 104230 and AIPAR = 'I'
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):
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=0and eu = 0 and substr(DIGITS(CAPIT),3,1) = '4' and AIPAR = 'I'
ImpegniIniziali
699561044
110
Esempi RIEPILOGATIVI INFN Laboratori Nazionali di Frascati
6.2
Select capit, sum(carim2)*0.01 as ASSEGNAZIONI
from infn03.cmov2where mmm2<4 and aam2=3 and GESTIO = 0 and sospes=0 and eu = 0and substr(DIGITS(CAPIT),3,1) = '4' and AIPAR = 'A' group by capit
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):
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
111
Esempi RIEPILOGATIVI INFN Laboratori Nazionali di Frascati
6.2
SELECT NUMINV, AANOT, NUMNOT, DATARRIV, DESCR1, DENOM1, CONSEGN, VALCAR FROM INFN03.INVGENWHERE TIPOBENE = 'V' AND CONSEGN LIKE'%IAC%'ORDER BY CONSEGN
Esempio di semplice estrazione dati patrimonio (INVGEN) selezionando solo beni ‘V’ e con consegnatario che si chiama …..IAC…. in ordine alfabetico assegnatario:
NUMINV
AANOT
NUMNOT DATARRIV DESCR1 DENOM1 CONSEGN VALCAR
9355 1993 119 1993-03-19
STAMPANTE H.P. MOD.3630A.SN-3219A00160
HEWLETT PACKARD ITALIANA S.P.A.
GIACINTI O. 861,95
2344 1989 257 1989-05-31
BANCO DA LAVORO COD.14450 IACUESSA E. 586,07
9910 1988 24 1988-02-22
BANCO DA LAVORO BOTT.ART.FW11 UTENSIL MECCANICA S.N.C. IACUESSA E. 953,74
9911 1992 134 1992-03-11
LAMPADE DA TAVOLO ART 114 TECNICHE INDUSTRIALI SRL IACUESSA E. 27,66
9912 1989 475 1989-11-13
SEGHETTO ALTERNATIVO METABO ST450
CESARE UBOLDI S.P.A. IACUESSA E. 85,43
9913 1989 475 1989-11-13
SMERIGLIATRICE METABO EX6114S CESARE UBOLDI S.P.A. IACUESSA E. 107,55
9914 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.SN-3219A00160
HEWLETT PACKARD ITALIANA S.P.A.
GIACINTI O. 861,95
NUMINV
AANOT
NUMNOT DATARRIV DESCR1 DENOM1 CONSEGN VALCAR
9355 1993 119 1993-03-19
STAMPANTE H.P. MOD.3630A.SN-3219A00160
HEWLETT PACKARD ITALIANA S.P.A.
GIACINTI O. 861,95
2344 1989 257 1989-05-31
BANCO DA LAVORO COD.14450 IACUESSA E. 586,07
9910 1988 24 1988-02-22
BANCO DA LAVORO BOTT.ART.FW11 UTENSIL MECCANICA S.N.C. IACUESSA E. 953,74
9911 1992 134 1992-03-11
LAMPADE DA TAVOLO ART 114 TECNICHE INDUSTRIALI SRL IACUESSA E. 27,66
9912 1989 475 1989-11-13
SEGHETTO ALTERNATIVO METABO ST450
CESARE UBOLDI S.P.A. IACUESSA E. 85,43
9913 1989 475 1989-11-13
SMERIGLIATRICE METABO EX6114S CESARE UBOLDI S.P.A. IACUESSA E. 107,55
9914 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.SN-3219A00160
HEWLETT PACKARD ITALIANA S.P.A.
GIACINTI O. 861,95
112
Esempi RIEPILOGATIVI INFN Laboratori Nazionali di Frascati
6.2
select a.nome, a.badge , b.ANNO, b.MESE, b.GIORNO, b.HHMM, b.CAUSAL, b.EUfrom pers03.anasrp as a, libsele400.storico as bwhere a.badge = '0' concat substr(DIGITS(b.badge),1,4) and a.badge = 1685 and b.anno=03
Esempio di estrazione timbrature da storico
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
113
Esempi RIEPILOGATIVI INFN Laboratori Nazionali di Frascati
6.2
Costruzione grafico consumi mensili categoria IV
CATEGORIA IV (BENI DI CONSUMO)IMPEGNI MENSILI 2002
MESE ASSEGNAZIONI IMPEGNI VARIAZ TOT IMPEGNI quadrimestre
SELECT MMM2, SUM(CARIM2) FROM INFN02.CMOV2WHERE AIPAR = 'I'AND EU = 0 AND GESTIO = 0
AND SOSPES = 0 AND SUBSTR(DIGITS(CAPIT),3,1) = '4'GROUP BY MMM2ORDER BY MMM2
SELECT MMVAR, SUM(IMPVAR) FROM INFN02.CMOV3WHERE AIPAR = 'I' AND EU = 0 AND GESTIO = 0
AND SOSPES = 0 AND SUBSTR(DIGITS(CAPIT),3,1) = '4'GROUP BY MMVARORDER BY MMVAR
114
Esempi RIEPILOGATIVI INFN Laboratori Nazionali di Frascati
6.2
Costruzione grafico consumi mensili categoria IV
1
CATEGORIA IV
GENFEBMARAPRMAGGIULUGAGOSETOTTNOVDIC
SQL
SELECT DISTINCT(CONSEGN)FROM infn03.invgenORDER BY consegn;
CONSEGN
ABATE EMILIO
ABENANTE MASSIMO
ANTIDORMI S.
ANTIDORMI SANDRO
SANDRO ANTIDORMI
…
…
Altri nomiUPDATE infn03.invgenSET 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”