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