SQL (II)

Interrogazioni (parte 2)

2

Notazione

Le parentesi angolari <,>: permettono di isolare un termine della sintassiLe parentesi quadre [,]: indicano che il termine all’interno è opzionale Può non comparire o comparire una

sola volta

3

Notazione

Le parentesi graffe {,}: indicano che il termine racchiuso può non comparire o essere ripetuto un numero arbitrario di volteLe barre verticali |: indicano che deve essere scelto uno tra i termini separati dalle barre

4

Interpretazione algebrica delle interrogazioni SQL

select T1.Attrib_11,…,Th.Attrib_hmfrom Tabella1 as T1,…,Tabellan as Tnwhere condizione

T1.Attrib_11,…,Th.Attrib_hm(condizione(Tabella1 x … x Tabellan))

Per semplicità, omettiamo le ridenominazioni che ci permettono di considerare tutti i join come prodotto cartesiano

5

Algebra->calcolo, SQL->linguaggio

SQL gestisce i duplicati (select e select distinct), algebra no (basata su insiemi)select *

from Impiegati

ordered by [asc|desc] Matricola

Operatori aggregatiInterrogazioni nidificate

per ordinare le tuple

6

Tabella “Impiegato”

Nome Cognome

Dipart Ufficio

Stipendio

Città

Mario Rossi Amministr

10 15 Milano

Carlo Bianchi Prod 20 12 Torino

Giuseppe

Verdi Amministr

20 13 Roma

Franco Neri Distrib 16 15 Napoli

Carlo Rossi Direzione

14 27 Milano

Lorenzo Lanzi Direzione

7 21 Genova

Paola Burroni Ammistr 75 13 Venezia

Marco Franco Prod 20 14 Roma

Impiegato

7

Tabella “Dipartimento”

Nome Indirizzo Città

Amministr

Via Tito Livio 27

Milano

Prod P.le Lavater 3 Torino

Distrib Via Segre 9 Roma

Direzione

Via Tito Livio 27

Milano

Ricerca Via Morone 6 Milano

Dipartimento

8

Operatori aggregati

Algebra relazionale: tutte le condizioni sono valutate su una tupla alla volta, in modo indipendenteSQL offre degli operatori che lavorano su più di una tupla alla volta:

count,sum,max,min,avg

9

Interrogazione 12select count(*)from Impiegatowhere Dipart = ‘Prod’

count(<*|[distinct|all|]ListaAttributi>)

valori diversi tra loro

non null

10

Interrogazioni 13, 14

Numero di stipendi diversi

select count(distinct Stipendio)from Impiegato

Numero di righe che hanno nome non nullo

select count(all Nome)from Impiegato

Gli operatori aggregati si applicano sulle tuple selezionatedalla clausola “where” (se c’è)

count(<*|[distinct|all|]ListaAttributi>)

11

sum, max, min, avg

Prendono solo espressioni rappresentanti valori numerici o intervalli di tempodistinct e all stesso significato di primaAltri operatori a seconda delle versioni di SQL (solitamente operatori statistici)

12

Interrogazioni 15, 16Somma stipendi di Amministrazione

select sum(Stipendio)from Impiegatowhere Dipart = ‘Amministr’

Stipendi min, max, medio degli Impiegati

select min(Stipendio),max(Stipendio),avg(Stipendio)from Impiegato

13

Interrogazione 17

Max stipendio tra impiegati che lavorano in dip a Milano

select max(Stipendio)from Impiegato, Dipartimentowhere Dipart = NomeDip and Citta = ‘Milano’

Equi-join

14

Interrogazione non corretta

select Cognome, Nome, max(Stipendio)from Impiegato, Dipartimentowhere Dipart = NomeDip and Citta = ‘Milano’

Perché è scorretta?

Ha senso con group by…

15

Interrogazione 18Estrarre la somma degli stipendi di tutti gli impiegati dello stesso dipartimento

select Dipart, sum(Stipendio)from Impiegatogroup by Dipart

16

Interrogazione 18 (cont.)E` come se prima si facesse la query

select Dipart, Stipendiofrom Impiegato

ottenendo la tabella…

17

Interrogazione 18 (cont.)

Dipart Stipendio

Amministrazione 22.500

Produzione 18.000

Amministrazione 20.000

Distribuzione 22.500

Direzione 40.000

Direzione 36.500

Amministrazione 20.000

Produzione 23.000

18

Interrogazione 18 (cont.)

Poi le tuple si raggruppano in sottoinsiemi in base allo stesso valore dell’attributo Dipart

Dipart Stipendio

Amministrazione 22.500

Amministrazione 20.000

Amministrazione 20.000

Produzione 18.000

Produzione 23.000

Distribuzione 22.500

Direzione 40.000

Direzione 36.500

19

Interrogazione 18 (cont.)

Poi le tuple si raggruppano in sottoinsiemi in base allo stesso valore dell’attributo Dipart

Dipart Stipendio

Amministrazione 22.500

Amministrazione 20.000

Amministrazione 20.000

Produzione 18.000

Produzione 23.000

Distribuzione 22.500

Direzione 40.000

Direzione 36.500

20

Interrogazione 18 (cont.)

Infine l’operatore sum viene applicato separatamente a ogni sottoinsieme

Dipart sum(Stipendio)

Amministrazione 125

Produzione 82

Distribuzione 45

Direzione 153

21

Interrogazione non corretta

select Ufficiofrom Impiegatogroup by Dipart

Perché?

22

Interrogazione non corretta

select Ufficiofrom Impiegatogroup by Dipart

• Ogni valore dell’attributo Dipart corrisponderanno diversi valori dell’attributo Ufficio

• Dopo l’esecuzione del raggruppamento, invece, ogni sottoinsieme di righe deve corrispondere a una sola riga nella tabella risultato della interrogazione

23

Interrogazione 19

Dipartimenti, il numero di impiegati di ciascun dipart,e la città sede del dipart

select Dipart, count(*), D.cittafrom Impiegato I, Dipartimento Dwhere I.Dipart=D.Nomegroup by Dipart

scorretta!

24

Interrogazione 19

select Dipart, count(*), D.Cittafrom Impiegato I, Dipartimento Dwhere I.Dipart=D.Nomegroup by Dipart, Citta

corretta!

25

Predicati sui gruppi

group by: le tuple vengono raggruppate in sottoinsiemiSi può voler considerare solo sottoinsiemi che soddisfano una certa condizioneSulle singole righe: clausola “where”Se condizioni di tipo aggregato: clausola having:ogni sottoinsieme di tuple della group by viene selezionato se il predicato di having è soddisfatto

26

Interrogazione 20Dipartimenti che spendono più di 100 milioni in stipendi

select Dipart, sum(Stipendi) as SommaStipfrom Impiegatogroup by Diparthaving sum(Stipendio) > 100

27

Predicati sui gruppi

Consigli di buon uso Meglio usare having con group by

Senza group by: l’intero insieme di righe è trattato come un unico raggruppamento

Se la condizione non è soddisfatta il risultato sarà vuoto

Solo predicati in cui ci sono operatori aggregati nella clausola having

28

Interrogazione 21

Dipartimenti per cui la media degli stipendi degli impiegatiche lavorano nell’ufficio 20 è > di 25 milioni

select Dipartfrom Impiegatowhere Ufficio = 20group by Diparthaving avg(Stipendio) > 25

29

Interrogazioni in SQL

La forma sintetica generale di un’interrogazione SQL diventa:SelectSQL ::=select ListaAttributiOEspressionifrom ListaTabelle[ where CondizioniSemplici ][ group by ListaAttributiDiRaggrupamento ][ having CondizioniAggregate ][ order by ListaAttributiDiOrdinamento ]

30

Interrogazioni di tipo insiemistico

union (unione), intersect (intersezione) ed except (differenza)Assumono come default di eseguire una eliminazione di dupilicati L’eliminazione dei duplicati rispetta

meglio il tipico significato delle operatori insiemistici

Per preservare i duplicati: usare l’operatore con la parola chiave all

31

Interrogazioni di tipo insiemistico

La sintassi per l’uso degli operatori insiemistici:

SelectSQL {<union|intersect|except> [all] SelectSQL }

32

Interrogazioni di tipo insiemistico

SQL non richiede che gli schemi su cui vengono effettuate le operazioni insiemistiche siano identiche Solo che gli attributi siano in pari numero e

che abbiano domini compatibili La corrispondenza tra gli attributi non si basa

sul nome ma sulla posizione degli attributi Se gli attributi hanno nome diverso, il risultato

normalmente usa i nomi del primo operando

33

Interrogazione 22 (insiemistiche)

Nomi e cognomi degli impiegati (notare: non serve che gli attributi abbiano lo stesso nome, ma solo lo stesso “tipo”, per esempio stringa)

select Nomefrom Impiegato unionselect Cognomefrom Impiegato

L’unione non si può

simulare

34

Interrogazione 22 (insiemistiche)

select Nomefrom Impiegato unionselect Cognomefrom Impiegato

Nome

Mario

Carlo

Giuseppe

Franco

Lorenzo

Paola

Marco

Rossi

Bianchi

Verdi

Neri

Lanzi

Borroni

35

Interrogazione 23 (insiemistiche)Nomi e cognomi degli impiegati mantenendo i duplicati

select Nomefrom Impiegato union allselect Cognomefrom Impiegato

Nome

Carlo

Franco

Carlo

Lorenzo

Marco

Bianchi

Neri

Rossi

Lanzi

Franco

36

Interrogazione 24 (insiemistiche)

Cognomi che sono anche nomi

select Nomefrom Impiegato intersectselect Cognomefrom Impiegato

Nome

Franco

37

Interrogazione 25 (insiemistiche)

Nomi che non sono cognomi

select Nomefrom Impiegato exceptselect Cognomefrom Impiegato

Nome

Mario

Carlo

Giuseppe

Lorenzo

Paola

Marco

38

Join esplicito

Abbiamo visto un modo di fare il join mettendo le condizioni di join nella clausola whereSi può utilizzare esplicitamente un operatore di join

39

Interrogazione 26 (join esplicito)

select

Impiegato.Nome,

Cognome,

Dipartimento.Città

from

Impiegato,Dipartimento

where

Dipart = Dipartimento.Nome

select

Impiegato.Nome,Cognome,

Dipartimento.Città

from

Impiegato join Dipartimento

On (Dipart =Dipartimento.Nome)

40

Interrogazione 27 (join esplicito riprende la 11)

Estrarre il nome e lo stipendio dei capi degliimpiegati che guadagnano piú dei loro capi

select I1.Nome, I1.Stipendiofrom Impiegato I1, Impiegato I2, Supervisionewhere I1.Matricola = Capo and I2.Matricola = Impiegato and I2.Stipendio > I1.Stipendio

select I1.Nome, I1.Stipendiofrom (Impiegato I1 join Supervisione on (I1.Matricola = Capo)) join Impiegato I2 on (I2.Matricola = Impiegato)where I2.Stipendio > I1.Stipendio

41

Outer join

Fino ad adesso abbiamo visto inner joinParliamo adesso di outer joinServe quando il join non è completo … Completo: dato R1 join R2 on (…), per

ciascuna tupla di R1 esiste almeno una tupla di R2 che si combina con essa, e viceversa per R2

… se si vuole mantenere l’informazione anche per quelle tuple che non partecipano al join

42

Tabelle “Guidatore” e “Automobile”

Nome Cognome NumPatente

MarioCarloMarco

RossiBianchiNeri

VR 2030020YPZ 1012436BAP 4544442R

Targa Marca Modello NumPatente

AB 574 WWAA 652 FFBJ 747 XXBB 421 JJ

FiatFiatLanciaFiat

PuntoBravaDeltaUno

VR 2030020YVR 2030020YPZ 1012436BMI 2020030U

43

Inner join (“normale”)

select Nome,Cognome,Guidatore.NumPatente, Targafrom Guidatore join Automobile on (Guidatore.NumPatente=Automobile.NumPatente)

Nome

Cognome

NumPatente

Targa Marca

Modello

MarioMarioCarlo

RossiRossiBianchi

VR 2030020YVR 2030020YPZ 1012436B

AB 574 WWAA 652 FFBJ 747 XX

FiatFiatLancia

PuntoBravaDelta

Join non completo

44

Outer left join (interrogazione 28)

select Nome,Cognome,Guidatore.NumPatente, Targafrom Guidatore left join Automobile on (Guidatore.NumPatente=Automobile.NumPatente)

Nome

Cognome

NumPatente

Targa Marca

Modello

MarioMarioCarloMarco

RossiRossiBianchiNeri

VR 2030020YVR 2030020YPZ 1012436BAP 4544442R

AB 574 WWAA 652 FFBJ 747 XXnull

FiatFiatLancianull

PuntoBravaDeltanull

45

Outer right join (interrogazione 29)

select Nome,Cognome,Guidatore.NumPatente, Targafrom Guidatore right join Automobile on (Guidatore.NumPatente=Automobile.NumPatente)

Nome

Cognome

NumPatente

Targa Marca

Modello

MarioMarioCarlonull

RossiRossiBianchinull

VR 2030020YVR 2030020YPZ 1012436Bnull

AB 574 WWAA 652 FFBJ 747 XXBB 421 JJ

FiatFiatLanciaFiat

PuntoBravaDeltaUno

46

Outer full join (interrogazione 30)

select Nome,Cognome,Guidatore.NumPatente, Targafrom Guidatore full join Automobile on (Guidatore.NumPatente=Automobile.NumPatente)

Nome

Cognome

NumPatente

Targa Marca

Modello

MarioMarioCarloMarconull

RossiRossiBianchiNerinull

VR 2030020YVR 2030020YPZ 1012436BAP 4544442Rnull

AB 574 WWAA 652 FFBJ 747 XXnullBB 421 JJ

FiatFiatLancianullFiat

PuntoBravaDeltanullUno

47

Join naturale (interrogazione 31)

select Nome,Cognome,Guidatore.NumPatente, Targafrom Guidatore natural join Automobile

Nome

Cognome

NumPatente

Targa Marca

Modello

MarioMarioCarlo

RossiRossiBianchi

VR 2030020YVR 2030020YPZ 1012436B

AB 574 WWAA 652 FFBJ 747 XX

FiatFiatLancia

PuntoBravaDelta

Attributo comune: NumPatente

48

Interrogazioni nidificate

Clausola “where” su predicati logici in cui le componenti sono confronti tra valori Possibile anche confrontare valori con il risultato di una query (query annidata)Di solito prima si esegue la query più interna, ma ci sono eccezioni (per esempio, passaggio di binding)

49

Interrogazioni nidificate

Parole chiave all e any: any: specifica che la riga soddisfa la

condizione se risulta vero il confronto tra il valore dell’attributo per la riga ed almeno uno degli elementi restituiti dall’interrogazione

all: specifica che la riga soddisfa la condizione solo se tutti gli elementi restituiti dall’interrogazione nidificata rendono vero il confronto

50

Interrogazione 32

Tutti i dati degli impiegati che lavorano in dipartimenti in Firenze

select *from Impiegatowhere Dipart = any (select Nome from Dipartimento where Citta = ‘Firenze’)

= any corrisponde a in

51

Interrogazione 33 (simile alla 10)

Impiegati che hanno lo stesso nome di impiegati del dip di Produzione

select I1.Nomefrom Impiegato I1, Impiegato I2where I1.Nome = I2.Nome and I2.Dipart = ‘Produzione’

select Nomefrom Impiegatowhere Nome = any (select Nome from Impiegato where Dipart = ‘Produzione’)

52

Interrogazione 34Dipartimenti in cui non lavorano persone con cognome ‘Rossi’

select Nomefrom Dipartimentowhere Nome <> all (select Dipart from Impiegato where Cognome = ‘Rossi’)

select Nomefrom Dipartimentoexcept select Dipartfrom Impiegatowhere Cognome = ‘Rossi’

“diverso da”

<> all corrisponde a not in

53

Interrogazione 35Dipartimento dell’impiegato che guadagna lo stipendio massimo

select Dipartfrom Impiegatowhere Stipendio = (select max(Stipendio) from Impiegato)

select Dipartfrom Impiegatowhere Stipendio >= all (select Stipendio from Impiegato)

Un solo valore da confrontar

e

Le due interrogazioni sono equivalenti