SQL

Interrogazioni

2

Interrogazioni in SQL

Non esiste un SQL standard (vari dialetti)Formulazione di interrogazioni (query) è parte del Data Manipulation Language, DMLAnche usato nel Data Declaration Language, DDL (per esempio, per dichiarare vincoli di integrità)

3

Interrogazioni in SQL

Paradigma dichiarativo: si specifica la descrizione dell’obiettivo e non il modo con cui ottenerlo A differenza dell’algebra relazionale,

che è procedurale

4

Cenni sull’implementazione

Interrogazione in SQL viene tradotta in linguaggio proceduraleSulla traduzione si fanno ottimizzazioni algebriche (ecco a cosa serve l’algebra…) …… e non (queste ultime dipendono dalle strutture sottostanti al DBMS in questione)

5

Sintassi

Esistono, in generale, più modi per effettuare un’interrogazione: scelte basate sulla leggibilità (più che sull’efficienza…)Struttura essenziale (introdurremo le variazioni di volta in volta):

select ListaAttributi (target list)

from ListaTabelle (clausola “from”)

[where Condizione] (clausola “where”)

6

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

7

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

8

Significato dell’interrogazione

Si considera il prodotto cartesiano fra le tabelle della clausola “from”Si selezionano quelle tuple che soddisfano la condizione della clausola “where” (opzionale)Si danno in ouput i valori di quegli attributi che sono elencati nella target list (“select”)

9

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

10

Interrogazione 1

select Stipendio as Salario

from Impiegato

where Cognome = ‘Rossi’

Salario

15

27

11

Interrogazione 1

select Stipendio as Salario

from Impiegato

where Cognome = ‘Rossi’

Salario

15

27

alias

12

Interrogazione 2

select *

from Impiegato

where Cognome = ‘Rossi’

Nome

Cognome

Dipart Ufficio

Stipendio

Città

Mario Rossi Amministr

10 15 Milano

Carlo Rossi Direzione

14 27 Milano

13

Interrogazione 2

select *

from Impiegato

where Cognome = ‘Rossi’

tutti

Nome

Cognome

Dipart Ufficio

Stipendio

Città

Mario Rossi Amministr

10 15 Milano

Carlo Rossi Direzione

14 27 Milano

14

Interrogazione 3

select Stipendio/12 as StipMens

from Impiegato

where Cognome = ‘Bianchi’

StipMens

1

15

Interrogazione 3

select Stipendio/12 as StipMens

from Impiegato

where Cognome = ‘Bianchi’

espressioni

StipMens

1

16

Join in SQL (primo modo)

Per formulare interrogazioni che coinvolgono più tabelle occorre fare un joinIn SQL un modo è: elencare le tabelle di interesse nella

“from” mettere nella “where” le condizioni

necessarie per mettere in relazione fra loro gli attributi di interesse

17

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

18

Interrogazione 4

Restituire nome e cognome degli impiegati e delle città in cui lavorano

select

Impiegato.Nome,Cognome,

Dipartimento.Città

from

Impiegato,Dipartimento

where

Dipart = Dipartimento.Nome

19

Interrogazione 4

Restituire nome e cognome degli impiegati e delle città in cui lavorano

select

Impiegato.Nome,Cognome,

Dipartimento.Città

from

Impiegato,Dipartimento

where

Dipart = Dipartimento.Nome

La notazione punto ( ) serve per disambiguare

Suggerimento: “from”, “where”, target list

20

Risultato interrogazione 4

Impiegato.Nome

Cognome

Dipartimento.Città

Mario Rossi Milano

Carlo Bianchi Torino

Giuseppe Verdi Milano

Franco Neri Roma

Carlo Rossi Milano

Lorenzo Lanzi Milano

Paola Burroni Milano

Marco Franco Torino

21

Interrogazione 5

select I.Nome, Cognome, D.Cittàfrom Impiegato [as] I, Dipartimento [as] Dwhere Dipart = D.Nome

Impiegato [as] I : esempio di aliasing di una tabella

L’aliasing per le tabelle serve a abbreviare e disambiguare i riferimenti alle tabelle

22

Sulla clausola “where”

Ammette come argomento un’espressione booleanaPredicati semplici combinati con not, and, or (not ha la precedenza, consigliato l’uso di parantesi(,))Ciascun predicato usa operatori: =, <>, <, >, <=, >=Confronto tra valori di attributi, costanti, espressioni

23

Interrogazione 6

select Nome,Cognomefrom Impiegato

where Ufficio = 20 and Dipart =‘Amministr’

Nome Cognome

Giuseppe

Verdi

24

Interrogazione 7 e 8

select Nome, Cognomefrom Impiegatowhere Dipart=‘Prod’ or Dipart=‘Amministr’

select Nome, Cognomefrom Impiegatowhere Cognome=‘Rossi’ and(Dipart=‘Prod’ or Dipart=‘Amministr’)

1

2

1

Nome

Mario

2

Nome Cognome

Mario Rossi

Carlo Bianchi

Paola Burroni

Marco Franco

Giuseppe

Verdi

25

Operatore like

_ = carattere arbitrario% = stringa di lunghezza arbitraria (anche 0) di caratteri arbitrariEsempi: like ab%ba_ = tutte le stringhe che

cominciano con “ab” e che hanno “ba” come coppia di caratteri prima dell’ultima posizione (es. abjjhhdhdbak,abbap)

26

Interrogazione 9

select *from Impiegatowhere Cognome like ‘_o%i’ or Cognome like ‘_u%i’

Nome

Cognome

Dipart Ufficio

Stipendio

Città

Mario Rossi Amministr

10 45 Milano

Carlo Rossi Direzioni 14 80 Milano

Paola Burroni Amministr

75 40 Venezia

27

Gestione dei valori nulliAttributo con valore nullo = non applicabile a una certa tupla, o valore sconosciuto, o non si sa nullaSQL offre il predicato “is null”:

Attributo is [not] null

28

Gestione dei valori nulliStipendio>13: cosa succede se l’attributo Stipendio è nullo? Scelte:

Logica a 2 valori e controllo esplicito. Per esempio:

(Stipendio > 13) or (Stipendio <= 13) or (Stipendio is null)

Usare un terzo valore di verità unknown: soluzione che crea problemi nei casi complessi (valutazione “globale” delle formule logiche)

29

Uso delle variabili di alias

Non solo per disambiguare la notazioneCi sono casi in cui una stessa tabella serve più di una voltaCaso speciale: quando si deve confrontare una tabella con se stessa

(il modello relazionale è relation-oriented, non tuple-oriented )

30

Interrogazione 10• Estrarre nome e cognome degli impiegati che hanno lo stesso cognome (ma nome diverso) di impiegati che lavorano nel dipartimento Produzione

31

Interrogazione 10• Estrarre nome e cognome degli impiegati che hanno lo stesso cognome (ma nome diverso) di impiegati che lavorano nel dipartimento Produzione

select I1.Cognome, I1.Nomefrom Impiegato I1, Impiegato I2where I1.Cognome = I2.Cognome and I1.Nome <> I2.Nome and I2.Dipart = ‘Prod’

32

Interrogazione 10• Estrarre nome e cognome degli impiegati che hanno lo stesso cognome (ma nome diverso) di impiegati che lavorano nel dipartimento Produzione

select I1.Cognome, I1.Nomefrom Impiegato I1, Impiegato I2where I1.Cognome = I2.Cognome and I1.Nome <> I2.Nome and I2.Dipart = ‘Prod’

per evitare output di tupla con se stessa

I2 usata per trovare tuple assoc. a ‘Prod’

33

Interrogazione 11

• Estrarre il nome e lo stipendio dei capi degliimpiegati che guadagnano più dei loro capi,date:

Impiegati(Matricola, Nome, Età, Stipendio)Supervisione(Capo, Impiegato)

dove Capo e Impiegato sono chiavi esterne diImpiegati (e.g., sono dei numeri di matricola)

34

Interrogazione 11 (sol.)select

I1.Nome, I1.Stipendiofrom

Impiegato I1, Impiegato I2, Supervisione

where I1.Matricola = Capo

and I2.Matricola = Impiegato and I2.Stipendio > I1.Stipendio

35

Interrogazione 11 (sol.)select

I1.Nome, I1.Stipendiofrom

Impiegato I1, Impiegato I2, Supervisione

where I1.Matricola = Capo

and I2.Matricola = Impiegato and I2.Stipendio > I1.Stipendio

I1 per i capi,I2 per gli impiegati

36

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

37

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

38

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

39

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

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

valori diversi tra loro

non null

40

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>)

41

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)

42

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

43

Interrogazione 17

Max stipendio tra impiegati che lavorano in dip a Milano

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

Equi-join