C6 Sql2

Basi di Dati

Interrogazioni complesse (II parte)

Basi di Dati – Dove ci troviamo?

C) Modello Relazionale, Algebra relazionale, SQL

1 2 3 4 5 6 7

E) Tecnologia di un DBMS

1 2 3 4 5 6

A) Introduzione

1 2

B) Prog. Concettuale (ER)

1 2 3 4 5 6 7

D) Prog. Logica e Normalizzazione

1 2 3 4

F) Programmazione DB

1 2

2 Basi di Dati ‐ Interrogazioni complesse (II parte)

Classificazione delle interrogazioni complesse

Basi di Dati ‐ Interrogazioni complesse (II parte)3

Query con ordinamentoQuery con aggregazioniQuery con raggruppamentoQuery binarieQuery nidificate

Esempio : gestione ordini

4

cliente COD‐CLI NOME INDIRIZZO P‐IVA

COD‐ORD COD‐CLI DATA IMPORTO

COD‐ORD COD‐PROD QTA

COD‐PROD NOME PREZZOprodotto

dettaglio

ordine

Basi di Dati ‐ Interrogazioni complesse (II parte)

Istanza di ordine

5

ordine

COD‐ORD

1

2

3

4

5

6

COD‐CLI

3

4

3

1

1

3

DATA

2004‐06‐01

2004‐08‐03

2004‐09‐01

2004‐07‐01

2004‐08‐01

2004‐09‐03

IMPORTO

50.000

8.000

5.500

12.000

1.500

27.000


Query binarie

Costruite concatenando due query SQL tramite operatori insiemistici:UNION unioneINTERSECT intersezioneEXCEPT differenza

si eliminano i duplicati


UnioneSelezionare i codici degli ordini i cui importi superano 500 € oppure presenti in qualche dettaglio con quantità superiore a 1000.

SELECT COD‐ORDFROM ORDINEWHERE IMPORTO > 500

UNIONSELECT COD‐ORDFROM DETTAGLIOWHERE QTA > 1000


Differenza

8

Selezionare i codici degli ordini i cui importi superano 500 € ma non presenti in nessun dettaglio con quantità superiore a 1000.


EXCEPTSELECT COD‐ORDFROM DETTAGLIOWHERE QTA > 1000


Intersezione

9

Selezionare i codici degli ordini i cui importi superano 500 € e che sono presenti in qualche dettaglio con quantità superiore a 1000.


INTERSECTSELECT COD‐ORDFROM DETTAGLIOWHERE QTA > 1000


Query nidificate

10

Costruite concatenando due query SQL nel predicato where:[NOT] IN appartenenza[NOT] EXISTS esistenzaANY, ALL quantificatori

comparatore: =, !=, <, <=, >, >=


Query nidificate (nested)

11

Selezionare nome e indirizzo dei clienti che hanno emesso qualche ordine di importo superiore a 10.000 €.

SELECT NOME, INDIRIZZOFROM CLIENTEWHERE COD‐CLI IN

SELECT COD‐CLIFROM ORDINEWHERE IMPORTO > 10.000


Equivalenza fra IN e query semplici

12

SELECT NOME, INDIRIZZOFROM CLIENTEWHERE COD‐CLI IN


equivale (a meno di duplicati) a:

SELECT NOME, INDIRIZZOFROM CLIENTE JOIN ORDINE

ON CLIENTE.COD‐CLI = ORDINE.COD‐CLIWHERE IMPORTO > 10.000


Nested Query complesse (fino a 8 livelli)

13

Selezionare nome e indirizzo dei clienti che hanno emesso qualche ordine i cui dettagli comprendono il prodotto “Pneumatico”.

SELECT NOME, INDIRIZZO FROM CLIENTEWHERE COD‐CLI IN

SELECT COD‐CLI FROM ORDINEWHERE COD‐ORD IN

SELECT COD‐ORD FROM DETTAGLIOWHERE COD‐PROD IN

SELECT COD‐PROD FROM PRODOTTOWHERE NOME = 'Pneumatico'


La query equivalente

14

equivale (a meno di duplicati) a:

SELECT NOME, INDIRIZZOFROM CLIENTE AS C, ORDINE AS O,

DETTAGLIO AS D, PRODOTTO AS P WHERE C.COD‐CLI = O.COD‐CLI

AND O.COD‐ORD = D.COD‐ORDAND D.COD‐PROD = P.COD‐PRODAND NOME= 'Pneumatico'


Esempio : gestione personale

15

impiegato

MATR

1

2

3

NOME

Piero

Giorgio

Giovanni

DATA‐ASS

1‐1‐02

1‐1‐04

1‐7‐03

SALARIO

1500 €

2000 €

1000 €

assegnamentoMATR

1

1

2

3

NUM‐PROG

3

4

3

4

PERC

50

50

100

100

progettoTITOLO

Idea

Wide

TIPO

Esprit

Esprit

MATR‐MGR

2

null

2

NUM‐PROG

3

4


Nested query correlate (con variabile)

16

Selezionare il nome degli impiegati il cui salario è maggiore di quello del proprio manager.

SELECT NOMEFROM IMPIEGATI AS X, WHERE SALARIO >( SELECT SALARIO

FROM IMPIEGATIWHERE MATR = X.MATR‐MGR

)


Nested query correlate (con variabile)

17

SELECT NOMEFROM IMPIEGATI AS X, WHERE SALARIO>( SELECT SALARIO

FROM IMPIEGATIWHERE MATR = X.MATR‐MGR )

sulla relazione IMPIEGATI (matricola, nome, manager…) equivale al JOIN

SELECT X.NOMEFROM IMPIEGATI AS X, IMPIEGATI AS YWHERE X.SALARIO > Y.SALARIO

AND Y.MATR = X.MATR‐MGR


Uso di IN nelle modifiche

18

aumentare di 5 € l’importo di tutti gli ordini che comprendono il prodotto 456

UPDATE ORDINESET IMPORTO = IMPORTO + 5WHERE COD‐ORD IN

SELECT COD‐ORDFROM DETTAGLIOWHERE COD‐PROD = '456'


Query con NOT IN

19

Selezionare nome e indirizzo dei clienti che non hanno emesso nessun ordine di importo superiore a 10.000 €.

SELECT NOME, INDIRIZZOFROM CLIENTEWHERE COD‐CLI NOT IN



Le query con ANY e ALL

20

SELECT COD‐ORD FROM ORDINE WHERE IMPORTO > ANY

SELECT IMPORTOFROM ORDINE

SELECT COD‐ORDFROM ORDINEWHERE IMPORTO >= ALL

SELECT IMPORTOFROM ORDINE

COD‐ORD

1

2

3

IMPORTO

50

300

90

ANY

F

V

V

ALL

F

V

F


Riduzione di query innestate

21

Attenzione!

Come si è visto, è spesso possibile ridurre una queryinnestata a una query semplice.

Tuttavia, le query innestate formulate con i seguenti operatori non si possono ridurre:NOT INALL (con qualsiasi operatore di confronto)NOT EXISTS con subquery correlata


Esempi

22

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‘


Esempi

23

chi è il manager di Piero?

SELECT NOME FROM IMPIEGATO WHERE MATR IN

SELECT MATR‐MGR FROM IMPIEGATO WHERE NOME='Piero'


Esercizi

24

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/04?calcolare per ciascun cliente la somma degli importi di tutti gli ordiniestrarre l'ordine di importo più alto


Date post:	05-Dec-2014
Category:	Technology
Upload:	majong-devjfu
View:	545 times
Download:	2 times

C6 Sql2

Technology