Basi di dati I Prof.ssa Rosalba Giugno Prof. Alfredo Pulvirenti SQL :Structured Query Language:...

Basi di dati I

Prof.ssa Rosalba GiugnoProf. Alfredo Pulvirenti

SQL :Structured Query Language: SELECT (IV)

SQL (Slide tratte in parte da SQL (Slide tratte in parte da da Atzeni, Ceri, Paraboschi, Torlonee, Basi di datida Atzeni, Ceri, Paraboschi, Torlonee, Basi di dati

McGraw-Hill, 2002)McGraw-Hill, 2002)

Giugno-Pulvirenti AA 2005-2006

Operatori aggregati

• Nelle espressioni della target list possiamo avere anche espressioni che calcolano valori a partire da insiemi di ennuple

• SQL-2 prevede 5 possibili operatori di aggregamento:– conteggio, minimo, massimo, media, somma

• Gli operatori di aggregamento NON sono rappresentabili in Algebra Relazionale


Cosa sono?Cosa sono?• Operano su insiemi di righe per dare

un risultato per gruppo.IMPIEGATIIMPIEGATI

““SalarioSalarioMassimo”Massimo”

DEPTNO SAL--------- --------- 10 2450 10 5000 10 1300 20 800 20 1100 20 3000 20 3000 20 2975 30 1600 30 2850 30 1250 30 950 30 1500 30 1250

MAX(SAL)

---------

5000


Sintassi, riassumiamo

SelectSQL ::= select ListaAttributiOEspressionifrom ListaTabelle[ where CondizioniSemplici ][ group by ListaAttributiDiRaggruppamento ][ having CondizioniAggregate ][ order by ListaAttributiDiOrdinamento ]


Raggruppare piu’ di una colonna

IMPIEGATIIMPIEGATI

““sommare I salari sommare I salari in IMPIEGATIin IMPIEGATI

per ongi lavoro, per ongi lavoro, RagruppatiRagruppati

per dipartimeno”per dipartimeno”

DEPTNO JOB SAL

--------- --------- ---------

10 MANAGER 2450

10 PRESIDENT 5000

10 CLERK 1300

20 CLERK 800

20 CLERK 1100

20 ANALYST 3000

20 ANALYST 3000

20 MANAGER 2975

30 SALESMAN 1600

30 MANAGER 2850

30 SALESMAN 1250

30 CLERK 950

30 SALESMAN 1500

30 SALESMAN 1250

JOB SUM(SAL)

--------- ---------

CLERK 1300

MANAGER 2450

PRESIDENT 5000

ANALYST 6000

CLERK 1900

MANAGER 2975

CLERK 950

MANAGER 2850

SALESMAN 5600

DEPTNO

--------

10

10

10

20

20

20

30

30

30


Uso di GROUP BY su colonne multipleUso di GROUP BY su colonne multiple

SQL> SELECT deptno, job, sum(sal) 2 FROM emp 3 GROUP BY deptno, job;

DEPTNO JOB SUM(SAL)--------- --------- --------- 10 CLERK 1300 10 MANAGER 2450 10 PRESIDENT 5000 20 ANALYST 6000 20 CLERK 1900...9 rows selected.


Esempio GROUP BY

• Per ogni dipartimento, restituire la somma degli stipendi di tutti gli impiegati (di quel dipartimento)

• Relazione: Employee(Name,Dept,Office,Salary,City)


Semantica degli operatori di raggruppamento (1)

• La query e’ innanzitutto eseguita senza operatori aggregati e senza GROUP BY:


Semantica degli operatori di raggruppamento (2)

• Quindi il risultato e’ diviso in sottoinsiemi aventi gli stessi valori per gli attributi indicati nel GROUP BY (Dept nel nostro caso)

• Quindi l’operatore di aggregamento e’ calcolato su ogni sottoinsieme:


– Non puo’ essere usata la WHERE per restringere i gruppi.

– Deve essere usata la HAVING.

SQL> SELECT deptno, AVG(sal) 2 FROM emp 3 WHERE AVG(sal) > 2000 4 GROUP BY deptno;

SQL> SELECT deptno, AVG(sal) 2 FROM emp 3 WHERE AVG(sal) > 2000 4 GROUP BY deptno;

WHERE AVG(sal) > 2000 *ERROR at line 3:

WHERE AVG(sal) > 2000 *ERROR at line 3:

Query illegali con funzioni di raggrup.

Query illegali con funzioni di raggrup.


Esempio

NOME MATRICOL INDIRIZZO TELEFONO Mario Rossi 123456 Via Etnea 1 222222 Ugo Bianchi 234567 Via Roma 2 333333 Teo Verdi 345678 Via Enna 3 444444

CORSO MATRICOLA VOTO Programmazione 345678 27 Architetture 123456 30 Programmazione 234567 18 Matematica Discreta

345678 22

Architettura 345678 30

CORSO PROFESSORE Programmazione Ferro Architetture Pappalardo Matematica Discreta Lizzio

Creare una query che restiuisca:nome, matricola, voto minimo,voto massimo, voto medio per gli studenti che hanno dato più di 8 materie


Esempio

SELECT Nome, Matricola MIN(Voto), MAX(Voto),AVG(Voto)FROM Esami, StudentiWHERE Esami.Matricola = Studenti.MatricolaGROUP BY Nome,MatricolaHAVING COUNT(*) > 8


Limitare le righe selezionate

– Limitare le righe tramite l’uso della clausola WHERE.

– La clausola WHERE segue la clausola FROM.

SELECT [DISTINCT] {*| colonna [alias], ...}FROM tabella[WHERE condizione(i)];


Interrogazioni nidificate

• Condizione : :=Predicato |“(“Condizione”)” |NOT Condizione |Condizione (AND | OR) Condizione

• Predicato– Espr op (Espr | “(“ Sottoselect “)” )– op {=, <>, >, >=, <, <=}– SottoSelect deve dare come risultato una tabella con un solo

elemento o vuota (vedremo alcuni esempi)

SELECT [DISTINCT] {*| colonna [alias], ...}FROM tabella[WHERE condizione(i)];


Interrogazioni nidificate

• le condizioni in SQL permettono anche il confronto fra un attributo e il risultato di una sottoquery– Syntax:

• Scalare Operatore (ANY | ALL) SelectQuery• ANY: il predicato e’ vero se almeno uno dei valori restituiti

da Query soddisfano la condizione• ALL: il predicato e’ vero se tutti i valori restituiti dalla Query

soddisfano la condizione– quantificatore esistenziale

• EXISTS SelectQuery• Il predicato e’ vero se la SelectQuery restituisce almeno

una tupla


Esempio di SELECT nidificate

• nome e reddito del padre di FrancoSELECT Nome, RedditoFROM Persone, PaternitaWHERE Nome = Padre AND Figlio = 'Franco'

SELECT Nome, RedditoFROM PersoneWHERE Nome = ( SELECT Padre

FROM Paternita WHERE Figlio = 'Franco')

• La query nella clausola WHERE e’ la query nidificata


Interrogazioni nidificate, commenti

• La forma nidificata è “meno dichiarativa”, ma talvolta più leggibile (richiede meno variabili)

• La forma piana e quella nidificata possono essere combinate

• Le sottointerrogazioni non possono contenere operatori insiemistici (“l’unione si fa solo al livello esterno”); la limitazione non è significativa


• Nome e reddito dei padri di persone che guadagnano più di 20 milioni

SELECT distinct P.Nome, P.RedditoFROM Persone P, Paternita, Persone FWHERE P.Nome = Padre AND Figlio = F.Nome

AND F.Reddito > 20

SELECT Nome, RedditoFROM PersoneWHERE Nome in (SELECT Padre

FROM PaternitaWHERE Figlio = any (SELECT Nome

FROM PersoneWHERE Reddito >

20))


• Nome e reddito dei padri di persone che guadagnano più di 20 milioni

SELECT distinct P.Nome, P.RedditoFROM Persone P, Paternita, Persone FWHERE P.Nome = Padre AND Figlio = F.Nome

AND F.Reddito > 20

SELECT Nome, RedditoFROM PersoneWHERE Nome in (SELECT Padre

FROM Paternita, Persone WHERE Figlio = NomeAND Reddito > 20)


Negazione con le query nidificate

• Trovare quei dipartimenti dove non c’e’ nessuno che si chiama ‘Brown’:

• Oppure:


Operatori IN e NOT IN

• IN e’ sinonimo di: =ANY

• NOT IN e’ sinonimo di: <>ALL


MAX e MIN con le query nidificate

• Esempio: Il dipartimento(i) dove lavora colui con lo stipendio piu’ alto di tutta l’azienda:

• Oppure:


Interrogazioni nidificate, commenti, 2

• La prima versione di SQL prevedeva solo la forma nidificata (o strutturata), con una sola relazione in ogni clausola FROM. Il che è insoddisfacente:– la dichiaratività è limitata– non si possono includere nella target list

attributi di relazioni nei blocchi interni


• Nome e reddito dei padri di persone che guadagnano più di 20 milioni, con indicazione del reddito del figlio

SELECT distinct P.Nome, P.Reddito, F.RedditoFROM Persone P, Paternita, Persone FWHERE P.Nome = Padre AND Figlio = F.Nome

AND F.Reddito > 20

SELECT Nome, Reddito, ???? FROM PersoneWHERE Nome in (SELECT Padre

FROM PaternitaWHERE Figlio = any (SELECT Nome

FROM PersoneWHERE Reddito > 20))


Interrogazioni nidificate, commenti, 3

• regole di visibilità:– non è possibile fare riferimenti a variabili

definite in blocchi più interni– se un nome di variabile è omesso, si assume

riferimento alla variabile più “vicina”

• in un blocco si può fare riferimento a variabili definite in blocchi più esterni


Nome Età

Persone

Reddito

Andrea 27

Maria 55

Anna 50

Filippo 26

Luigi 50

Franco 60

Olga 30

Sergio 85

Luisa 75

Aldo 25

21

42

35

30

40

20

41

35

87

15

MadreMaternità Figlio

Luisa

Anna

Anna

Maria

Maria

Luisa

Maria

Olga

Filippo

Andrea

Aldo

Luigi

PadrePaternità Figlio

Luigi

Luigi

Franco

Franco

Sergio

Olga

Filippo

Andrea

Aldo

Franco


Quantificazione esistenziale

• Ulteriore tipo di condizione– EXISTS ( Sottoespressione )

• Le persone che hanno almeno un figlio

SELECT *FROM PersoneWHERE EXISTS ( SELECT *

FROM PaternitaWHERE Padre = Nome) OR

EXISTS ( SELECT *FROM MaternitaWHERE Madre = Nome)


•I padri i cui figli guadagnano tutti più di venti milioni

SELECT distinct PadreFROM Paternita ZWHERE NOT EXISTS (

SELECT *FROM Paternita W, PersoneWHERE W.Padre = Z.Padre

AND W.Figlio = Nome AND Reddito <= 20)

Quantificazione esistenziale, 2


Semantica delle espressioni “correlate”

• La query piu’ interna puo’ usare variabili della query esterna

• L’interrogazione interna viene eseguita una volta per ciascuna ennupla dell’interrogazione esterna

• Esempio, trovare tutti gli studenti che hanno un omonimo:– SELECT *

FROM Student S WHERE EXISTS (SELECT *

FROM Student S2 WHERE S2.Nome = S.Nome AND S2.Cognome = S.Cognome AND S2.Matricola <>

S.Matricola)


Semantica delle espressioni “correlate”, 2

• Esempio, trovare tutti gli studenti che NON hanno un omonimo:– SELECT *

FROM Student S WHERE NOT EXISTS (SELECT *

FROM Student S2 WHERE S2.Nome = S.Nome AND S2.Cognome =

S.Cognome AND S2.Matricola <>

S.Matricola)


Confronto su piu’ attributi

• Il confronto con il risultato di una query nidificata puo’ essere basato su piu’ attributi

• Stessa query di prima, trovare tutti gli studenti che hanno un omonimo:– SELECT *

FROM Student S WHERE (Nome, Cognome) IN (SELECT Nome, Cognome

FROM Student S2 WHERE S2.Matricola <>

S.Matricola)


Visibilità

• scorretta:SELECT *FROM ImpiegatoWHERE Dipart in (SELECT Nome FROM Dipartimento D1 WHERE Nome = 'Produzione') OR Dipart in (SELECT Nome FROM Dipartimento D2 WHERE D2.Citta = D1.Citta)

– D1 non e’ visibile nella seconda query nidificata in quanto le due sottoquery sono allo stesso livello


Esempio: ancora il quantificatore Universale

• Agenti(CodiceAgente,Nome,Zona Supervisore,Commissione)

• Clienti(CodiceCliente,Nome,Citta’,Sconto)• Ordini(CodiceOrdine,CodiceCliente,Codice

Agente,Articolo,Data,Ammontare)


Esempio: ancora il quantificatore Universale

• Supponiamo di voler trovare i codici di quei clienti che hanno fatto ordini a TUTTI gli agenti di Catania.

• Per ogni agente z di Catania esiste un ordine y del nostro cliente x a z.

zy y(n,x,z,p,d,a) sse z y y(n,x,z,p,d,a)


Tradotta in SQL

SELECT c.CodiceClienteFROM Clienti cWHERE NOT EXISTS (SELECT * FROM Agenti a WHERE a.Zona = ‘Catania’ AND NOT EXISTS ( SELECT * FROM Ordini v WHERE v.CodiceCliente =

c.CodiceCliente AND v.CodiceAgente =

a.CodiceAgente) )


• City(id,city,country,district,population)

• Cities_Stores(city,store_type,address)• Stores(store_type,description)


Esempi

• Che tipi di negozi sono presenti in una o più città?

SELECT DISTINCT store_type FROM Stores WHERE EXISTS (SELECT * FROM Cities_Stores WHERE

Cities_Stores.store_type = Stores.store_type);

• Quali tipi di negozi non sono presenti nelle città?

SELECT DISTINCT store_type FROM Stores WHERE NOT EXISTS (SELECT * FROM Cities_Stores WHERE Cities_Stores.store_type = Stores.store_type);


Esempi

• Quali tipi di negozi sono presenti in TUTTE le città?

SELECT DISTINCT store_type FROM Stores WHERE NOT EXISTS ( SELECT * FROM Cities WHERE

NOT EXISTS ( SELECT * FROM Cities_Stores WHERE Cities_Stores.city = Cities.city AND

Cities_Stores.store_type = Stores.store_type));

• Doppio NOT EXISTS. Ha una clausola NOT EXISTS dentro una clausola NOT EXISTS.

• Nell’output non esiste una città che non ha un tipo di negozio (store) dei tipi elencati.


Commenti finali sulle query nidificate

• Query nidificate possono essere “meno dichiarative” in un certo senso ma spesso sono piu’ facilmente interpretabili– Suddivisibili in blocchi piu’ semplici da interpretare

• L’utilizzo di variabili deve rispettare le regole di visibilita’– Cioe’, una variabile puo’ essere usata solo all’interno

dello stesso blocco e in un blocco piu’ interno

• Comunque, query nidificate complesse possono essere di difficile comprensione– Soprattutto quando si usano molte variabili comuni tra

blocchi diversi

Basi di dati I Prof.ssa Rosalba Giugno Prof. Alfredo Pulvirenti SQL :Structured Query Language:...

Documents

Transcript of Basi di dati I Prof.ssa Rosalba Giugno Prof. Alfredo Pulvirenti SQL :Structured Query Language:...