SQL avanzato 1
Interrogazionicomplesse
SQL avanzato 2
Classificazione delleinterrogazioni complesse
• Query con ordinamento• Query con aggregazione• Query con raggruppamento• Query binarie• Query annidate
SQL avanzato 3
Esempio : gestione ordinicliente COD-CLI NOME INDIRIZZO P-IVA
COD-ORD COD-CLI DATA IMPORTO
COD-ORD COD-PROD QTA
COD-PROD NOME PREZZOprodotto
dettaglio
ordine
SQL avanzato 4
Istanza di ordineordineCOD-ORD123456
COD-CLI343113
DATA1997-06-011997-08-031997-09-011997-07-011997-08-011997-09-03
IMPORTO50.000.000 8.000.000 5.500.00012.000.000 1.500.00027.000.000
SQL avanzato 5
Query con ordinamentoSELECT * FROM ORDINEWHERE IMPORTO > 100.000 ORDER BY DATA
COD-ORD145236
COD-CLI311433
DATA1997-06-011997-07-011997-08-011997-08-031997-09-011997-09-03
IMPORTO50.000.00012.000.000 1.500.000 8.000.000 5.500.00027.000.000
SQL avanzato 6
Order by
COD-ORD451632
COD-CLI113334
DATA1997-07-011997-08-011997-06-011997-09-031997-09-011997-08-03
IMPORTO12.000.000 1.500.00050.000.000 5.500.00027.000.000 8.000.000
SELECT * FROM ORDINEWHERE IMPORTO > 100.000ORDER BY COD-CLI
SQL avanzato 7
Order by
SELECT COD-CLI, IMPORTO/1.000.000FROM ORDINEWHERE IMPORTO > 100.000ORDER BY 2
(ordina sulla SECONDA colonna)
Nel caso si voglia ordinare sui valoridi una colonna risultato di una espressionesi usa una notazione “posizionale”
SQL avanzato 8
Order by
COD-ORD546312
COD-CLI113334
DATA1997-08-011997-07-011997-09-031997-09-011997-06-011997-08-03
IMPORTO 1.500.00012.000.00027.000.000 5.500.00050.000.000 8.000.000
.......ORDER BY COD-CLI ASC, DATA DESC
SQL avanzato 9
Query aggregate
Utilizzano le funzioni aggregate:
SUM sommatoriaAVG mediaMIN minimoMAX massimoCOUNT cardinalità
(I NULL sono esclusi)
SQL avanzato 10
Query con massimo
SELECT MAX(IMPORTO) AS MAX-IMPFROM ORDINE
MAX-IMP 50.000.000
• Selezionare l’importo massimo degliordini.
SQL avanzato 11
Query con sommatoria
SELECT SUM(IMPORTO) AS SOMMA-IMPFROM ORDINEWHERE COD-CLIENTE = 1
SOMMA-IMP 13.500.000
• Selezionare la somma degli importi degliordini relativi al cliente numero 1.
SQL avanzato 12
Query con raggruppamentosi aggiungono le clausoleGROUP-BY (raggruppamento)HAVING (selezione dei gruppi)
SELECT …..FROM …..WHERE …..GROUP BY …..HAVING …..
SQL avanzato 13
Query con raggruppamento• Selezionare la somma degli importi degli
ordini successivi al 10-6-97 per quei clientiche hanno emesso almeno 2 ordini.
SELECT COD-CLI, SUM (IMPORTO)FROM ORDINEWHERE DATA > '1997-06-10'GROUP BY COD-CLIHAVING COUNT (IMPORTO) >= 2
SQL avanzato 14
Passo 1: Valutazione WHERE
COD-ORD23456
COD-CLI43113
DATA1997-08-031997-09-011997-07-011997-08-011997-09-03
IMPORTO 8.000.000 5.500.00012.000.000 1.500.00027.000.000
( WHERE DATA > '1997-06-10’ )
SQL avanzato 15
Passo 2 : Raggruppamento
COD-ORD45362
COD-CLI11334
DATA1997-07-011997-08-011997-09-011997-09-031997-08-03
IMPORTO12.000.000 1.500.000 5.500.00027.000.000 8.000.000
si valuta la clausola GROUP-BY
(GROUP BY COD-CLI)
SQL avanzato 16
Passo 3 : Calcolo degli aggregati
per ciascun gruppo si calcolanoSUM (IMPORTO) e COUNT (IMPORTO)
COD-CLI
134
SUM (IMPORTO)13.500.00032.500.000 8.000.000
COUNT (IMPORTO)221
SQL avanzato 17
Passo 4 : Selezione dei gruppisi valuta il predicatoCOUNT (IMPORTO) >=2
COD-CLI
134
SUM (IMPORTO)13.500.00032.500.000 8.000.000
COUNT (IMPORTO)221
SQL avanzato 18
Passo 5 : Produzione del risultato
COD-CLI
13
SUM (IMPORTO)13.500.00032.500.000
SQL avanzato 19
Doppio raggruppamento
SELECT COD-CLI, COD-PROD, SUM(QTA)FROM ORDINE AS O, DETTAGLIO AS DWHERE O.COD-ORD = D.COD-ORDGROUP BY COD-CLI, COD-PRODHAVING SUM(QTA) > 50
• Selezionare la somma delle quantità deidettagli degli ordini emessi da ciascuncliente per ciascun prodotto, purché lasomma superi 50.
SQL avanzato 20
Situazione dopo il join e il raggruppamentoordine
ORDINE.COD-ORD34353126
COD-CLI
11112333
DETTAGLIO.COD-ORD34353126
COD-PROD
11221111
dettaglio
QTA
gruppo 1,1
gruppo 1,2
gruppo 2,1
gruppo 3,1
3020301060403025
SQL avanzato 21
Estrazione del risultatosi valuta la funzione aggregataSUM(QTA) e il predicato HAVING
SUM(QTA)
50406095
COD-PROD
1211
COD-CLI
1123
SQL avanzato 22
Query con raggruppamento eordinamento
• E’ possibile ordinare il risultato anchedelle query con raggruppamento
SELECT …..FROM …..[ WHERE ….. ]GROUP BY …..[ HAVING ….. ]ORDER BY ….
SQL avanzato 23
Raggruppamento e ordinamento• Selezionare la somma degli importi degli ordini
successivi al 10/6/97 per quei clienti che hannoemesso almeno 2 ordini dopo quella data, inordine di cliente.
SELECT COD-CLI, SUM (IMPORTO)FROM ORDINEWHERE DATA > 1997-06-10GROUP BY COD-CLIHAVING COUNT (IMPORTO) >= 2ORDER BY COD-CLI
SQL avanzato 24
ESERCIZI SUL GROUP BY
1 SELECT ART-NO, SPED-NO, NUMFROM SPEDIZIONIGROUP BY ART-NO
OPPURE
2 SELECT ART-NO, SPED-NO, NUMFROM SPEDIZIONIGROUP BY ART-NO,SPED-NO
SQL avanzato 25
ESERCIZI SUL GROUP BYSU:
ART-NO SPED-NO NUM (SPEDIZIONI)1 2 1001 2 251 3 752 2 1002 4 1002 4 751 4 25
SQL avanzato 26
ESERCIZI SUL GROUP BY1 ….GROUP BY ART-NO
ART-NO SPED-NO NUM1 2,2,3,4 100,25,75,252 2,4,4 100,100,75
1 ….GROUP BY ART-NO, SPED-NO
ART-NO SPED-NO NUM1 2 100,251 3 751 4 252 2 1002 4 100,75
SQL avanzato 27
ESERCIZI SUL GROUP BYSELECT ARTICOLOFROM VENDITE GROUP BY ARTICOLOHAVING COUNT(DISTINCT DATA)
SU
ARTICOLO DATA NUM
PENNA 1997-12-13 2MATITA 1997-12-08 1GOMMA 1997-12-10 1MATITA 1997-12-08 2PENNA 1997-12-08 1
SQL avanzato 28
ESERCIZI SUL GROUP BYGROUP BY ARTICOLO
SU
ARTICOLO DATAPENNA 1997-12-13, 1997-12-08 MATITA 1997-12-08, 1997-12-08 GOMMA 1997-12-10
HAVING COUNT(DISTINCT DATA)ARTICOLO
PENNA
SQL avanzato 29
Query binarie
Costruite concatenando due query SQLtramite operatori insiemistici:
UNION unioneINTERSECT intersezioneEXCEPT (MINUS) differenza
(si eliminano i duplicati)
SQL avanzato 30
Unione
SELECT COD-ORDFROM ORDINEWHERE IMPORTO > 500.000 UNIONSELECT COD-ORDFROM DETTAGLIOWHERE QTA > 1000
• Selezionare i codici degli ordini i cui importisuperano 500.000 lire oppure presenti in qualchedettaglio con quantità superiore a 1000.
SQL avanzato 31
Differenza
SELECT COD-ORDFROM ORDINEWHERE IMPORTO > 500.000 EXCEPTSELECT COD-ORDFROM DETTAGLIOWHERE QTA > 1000
• Selezionare i codici degli ordini i cui importisuperano 500.000 lire ma non presenti in nessundettaglio con quantità superiore a 1000.
SQL avanzato 32
Intersezione
SELECT COD-ORDFROM ORDINEWHERE IMPORTO > 500.000 INTERSECTSELECT COD-ORDFROM DETTAGLIOWHERE QTA > 1000
• Selezionare i codici degli ordini i cui importisuperano 500.000 lire e che sono presenti inqualche dettaglio con quantità superiore a 1000.
SQL avanzato 33
Query annidate
Costruite concatenando due query SQLnel predicato where:
[NOT] IN appartenenza[DOES NOT] EXISTS esistenzaANY, ALL quantificatori
comparatore: =, !=, <, <=, >, >=
SQL avanzato 34
Query nidificate (nested)
( SELECT COD-CLI FROM ORDINE WHERE IMPORTO > 10.000.000 )
• Selezionare nome e indirizzo dei clienti chehanno emesso qualche ordine di importosuperiore a 10.000.000.
SELECT NOME, INDIRIZZOFROM CLIENTEWHERE COD-CLI IN
SQL avanzato 35
Equivalenza fra IN e query sempliciSELECT NOME, INDIRIZZOFROM CLIENTEWHERE COD-CLI IN ( SELECT COD-CLI FROM ORDINE WHERE IMPORTO > 10.000.000 )equivale (a meno di duplicati) a:SELECT NOME, INDIRIZZOFROM CLIENTE JOIN ORDINE ON CLIENTE.COD-CLI = ORDINE.COD-CLIWHERE IMPORTO > 10.000.000
SQL avanzato 36
( SELECT COD-ORD FROM DETTAGLIO WHERE COD-PROD IN
• Selezionare nome e indirizzo dei clienti chehanno emesso qualche ordine i cui dettaglicomprendono il prodotto “Pneumatico”.
( SELECT COD-PROD FROM PRODOTTO WHERE NOME = 'Pneumatico' ) ) )
( SELECT COD-CLI FROM ORDINE WHERE COD-ORD IN
SELECT NOME, INDIRIZZO FROM CLIENTEWHERE COD-CLI IN
Nested Query complesse (fino a 8 livelli)
SQL avanzato 37
La query equivalente
equivale (a meno di duplicati) a:
SELECT NOME, INDIRIZZOFROM CLIENTE AS C, ORDINE AS O,
DETTAGLIO AS D, PRODOTTO AS PWHERE C.COD-CLI = O.COD-CLI
AND O.COD-ORD = D.COD-ORD AND D.COD-PROD = P.COD-PROD AND NOME= 'Pneumatico'
SQL avanzato 38
Nested query correlate (con variabile)SELECT NOMEFROM IMPIEGATI AS X,
WHERE SALARIO> ( SELECT SALARIO FROM IMPIEGATI WHERE MATRICOLA= X.MANAGER ) sulla relazione IMPIEGATI (matricola, nome, manager…) equivale al JOIN
SELECT X.NOMEFROM IMPIEGATI AS X, IMPIEGATI AS Y WHERE X.SALARIO > Y.SALARIO
AND Y.MATRICOLA= X.MANAGER
SQL avanzato 39
Uso di IN nelle modifiche• aumentare di L. 5000 l’importo di tutti gli ordini che comprendono il prodotto 456
UPDATE ORDINE SET IMPORTO = IMPORTO + 5000 WHERE COD-ORD IN
( SELECT COD-ORD FROM DETTAGLIO WHERE COD-PROD = '456’ )
SQL avanzato 40
Query con NOT IN
( SELECT COD-CLI FROM ORDINE WHERE IMPORTO > 10.000.000 )
• Selezionare nome e indirizzo dei clienti che nonhanno emesso nessun ordine di importosuperiore a 10.000.000.
SELECT NOME, INDIRIZZOFROM CLIENTEWHERE COD-CLI NOT IN
SQL avanzato 41
La query con ANY e ALL
SELECT COD-ORDFROM ORDINEWHERE IMPORTO > ANY ( SELECT IMPORTO FROM ORDINE )
SELECT COD-ORDFROM ORDINEWHERE IMPORTO >= ALL ( SELECT IMPORTO FROM ORDINE )
COD-ORD123
IMPORTO 50.000 300.000 90.000
ANYFVV
ALLFVF
SQL avanzato 42
Esercizi• Riprendere le basi di dati per la gestione del personale e degli ordini ed esprimere in SQL le interrogazioni : - quale impiegati lavorano in un progetto in cui non lavora il loro manager? - quanti ordini ha emesso Paolo? - quante candele sono state ordinate il 5/7/97? - calcolare per ciascun cliente la somma degli importi di tutti gli ordini - estrarre l'ordine di importo più alto
SQL avanzato 43
Esercizi (lezione precedente)• in quali tipi di progetti lavora Giovanni?
SELECT TIPO FROM PROGETTO WHERE NUM-PROG IN ( SELECT NUM-PROG FROM ASSEGNAMENTO WHERE MATR IN
( SELECT MATR FROM IMPIEGATO WHERE NOME='Giovanni’ ) )
• chi è il manager di Piero? SELECT NOME FROM IMPIEGATO WHERE MATR IN ( SELECT MATR -MGR FROM IMPIEGATO
WHERE NOME='Piero’ )
SQL avanzato 44
Query annidate (nested)
( SELECT * FROM ORDINE WHERE IMPORTO > 10.000.000
AND COD_CLI=C.COD_CLI )
• Selezionare nome e indirizzo dei clienti chehanno emesso qualche[o non hanno emesso nessun]ordine di importo superiore a 10.000.000.
SELECT NOME, INDIRIZZOFROM CLIENTE CWHERE [NOT] EXISTS
Top Related