SQL
SQL
SQL
SQL
• Structured Query Language
• è un linguaggio con varie funzionalità:
– contiene sia il DDL sia il DML;
• esistono varie versioni dell’SQL;
• vediamo gli aspetti essenziali, non i dettagli
• “storia”:
– prima proposta SEQUEL (IBM Research, 1974);
– prima implementazione in SQL/DS (IBM, 1981);
– dal 1983 ca., “standard di fatto”
– standard (1986, poi 1989 e infine 1992) recepito in parte;
– standard SQL:2003 (SQL3) approvato recentemente
2
SQL
Domini
• Domini elementari (predefiniti)
• Domini definiti dall‘utente (semplici, ma riutilizzabili)
3
SQL
Domini elementari
• Carattere: singoli caratteri o stringhe, anche di lunghezza
variabile
character [ varying ] [ ( Lunghezza ) ]
[ character set NomeFamigliaCaratteri ]
• Bit: singoli booleani o stringhe
bit [ varying ] [ ( Lunghezza ) ]
• Numerici, esatti e approssimati:
numeric [( Precisione [ , Scala ) ] ]
integer
float [( Precisione )]
double precision
4
SQL
Domini elementari, 2
• Data, ora, intervalli:
date
time [( Precisione )] [ with time zone ]
timestamp [( Precisione )] [ with time zone ]
interval UnitàDiTempo [ to UnitàDiTempo ]
5
SQL
Definizione di schemi
• In un database non ci sono solo tabelle:
• create schema [NomeSchema] [ [authorization]
autorizzazione]{elemento schema}
• ‘elemento schema’ può essere: dominio, tabella, indice,
asserzione, vista, privilegio
• ‘autorizzazione’ è il nome dell’utente proprietario dello schema
• non è necessario definire tutto all’inizio
6
SQL
Definizione di tabelle
• create table NomeTabella (NomeAttributo Dominio
[Default] [Vincoli]
{, NomeAttributo Dominio
[Default] [Vincoli] }
AltriVincoli)
•Default (utilizzabili anche nella create domain)
default < Valore | user | null >
7
SQL
Esempio
• create table Dipartimento
(
nome
char(20) primary key,
indirizzo
char(50),
città
char(20)
)
8
SQL
Definizione di domini
• Istruzione CREATE DOMAIN:
– definisce un dominio (semplice), utilizzabile in definizioni di
relazioni
• Sintassi
create domain NomeDominio as Tipo
[ Default ]
[ Vincoli ]
• Esempio
create domain Voto as smallint default null
check ( value >=18 and value <= 30 )
9
SQL
Vincoli intrarelazionali
• not null (su singoli attributi)
• unique: permette di definire chiavi; sintassi:
– per singoli attributi:
unique dopo il dominio
– chiavi formate da più attributi:
unique ( Attributo { , Attributo } )
• primary key: definizione della chiave primaria (una sola,
implica not null); sintassi, come per unique
• check, vedremo più avanti
10
SQL
Vincoli intrarelazionali, esempi
Nome
character(20) not null,
Cognome
character(20) not null,
unique (Cognome,Nome)
• è diverso da:
Nome
Cognome
character(20) not null unique,
character(20) not null unique
11
SQL
Vincoli interrelazionali
• references e foreign key (chiave esterna) permettono di definire
vincoli di integrità referenziale; sintassi:
– per singoli attributi:
references dopo il dominio
– riferimenti su più attributi:
foreign key( Attributo { , Attributo } )
references ...
•
è possibile associare politiche di reazione alla violazione dei vincoli
(causate da modifiche sulla tabella esterna, cioè quella cui si fa
riferimento)
12
SQL
Richiamo: base di dati
con vincoli di integrità referenziale
infrazioni
vigili
automobili
Codice
Data
65524
3/9/1997
87635 4/12/1997
82236 4/12/1997
35632
6/1/1998
76543
5/3/1998
Vigile
343
476
343
476
548
Matricola Cognome
343
Rossi
476
Neri
548
Nicolosi
Prov
MI
MI
RM
RM
MI
Numero
3K9886
6D5563
7C5567
1A6673
5E7653
Prov
MI
MI
RM
RM
MI
Numero
3K9886
6D5563
7C5567
7C5567
6D5563
Nome
Luca
Pino
Gino
Proprietario
Nestore
Nestore
Menconi
Mussone
Marchi
…
…
…
…
…
…
13
SQL
Vincoli interrelazionali, esempio
create table
Codice
Data
Vigile
Infrazioni(
character(6) primary key,
date not null,
integer not null
references Vigile(Matricola),
Provincia character(2),
Numero
character(6) ,
foreign key(Provincia, Numero) references
Automobili(Provincia, Numero)
)
Infrazioni e’ una tabella interna, Vigile e Automobili sono
esterne
14
SQL
Violazione dei vincoli
• per tutti i vincoli visti fino ad ora si assume che se violati a causa
di un aggiornamento, il comando di aggiornamento venga
rifiutato segnalando l’errore all’utente.
• per i vincoli di integrità referenziale, SQL permette di scegliere
altre reazioni da adottare quando viene rilevata una violazione
• la reazione è possibile solo per le operazioni sulla tabella
esterna che si propagano secondo una certa politica verso la
tabella interna
• le violazioni possibili sono causate da modifiche del valore
dell’attributo riferito e dalla cancellazione di righe (es. modifiche
dell’attributo Provincia e cancellazione di righe da Vigile)
15
SQL
Politiche di reazione
• Specificata immediatamente dopo il vincolo di integrità consente
di associare politiche diverse ai diversi eventi (delete, update)
secondo la seguente sintassi:
on < delete | update >
< cascade | set null | set default |
no action >
16
SQL
Reazioni per delete
• cascade: si propagano le cancellazioni
• set null: all’attributo referente viene assegnato il valore nullo
al posto del valore cancellato nella tabella
• set default: all’attributo referente viene assegnato il valore
di default al posto del valore cancellato nella tabella esterna
• no action: la cancellazione non viene consentita
17
SQL
Reazioni per update
• cascade: il nuovo valore valore viene propagato nell’altra
tabella
• set null: all’attributo referente viene assegnato il valore nullo
al posto del valore modificato nella tabella
• set default: all’attributo referente viene assegnato il valore
di default al posto del valore modificato nella tabella esterna
• no action: l’azione di modifica non viene consentita
18
SQL
Modifiche degli schemi
Fornisce primitive per la manipolazione di schemi che
permettono di modificare gli schemi:
•
•
•
•
•
alter domain
alter table
drop domain
drop table
...
19
SQL
Dizionario dei Dati
• Tutti i DBMS relazionali gestiscono le descrizioni delle tabelle
presenti nella basi di dati mediante una struttura relazionale,
cioè mediante tabelle.
• La base di dati contiene due tipi di tabelle:
– quelle contente i dati, e
– quelle contenente i metadati (dati che descrivono dati), dette
il catalogo della base di dati oppure il dizionario dei dati
• I comandi di definizione e modifica dello schema manipolano il
dizionario dei dati
20
SQL
SELECT, sintassi
select AttrExpr [ [as] Alias ] { , AttrExpr [ [as] Alias ] }
from Tabella [ [as] Alias ] { , Tabella [ [as] Alias ] }
[ where Condizione ]
•
le tre parti vengono di solito chiamate
– target list
– clausola from
– clausola where
•
seleziona tra le righe che appartengono al prodotto cartesiano delle
tabelle elencate nella clausola from quelle che soddisfano la
condizione espressa nell‘argomento della clausola where
21
SQL
persone
maternita
Madre
Luisa
Luisa
Anna
Anna
Maria
Maria
Nome
Andrea
Aldo
Maria
Anna
Filippo
Luigi
Franco
Olga
Sergio
Luisa
Figlio
Maria
Luigi
Olga
Filippo
Andrea
Aldo
Eta
27
25
55
50
26
50
60
30
85
75
Reddito
21
15
42
35
30
40
20
41
35
87
paternita
Padre
Sergio
Luigi
Luigi
Franco
Franco
Figlio
Franco
Olga
Filippo
Andrea
Aldo
22
SQL
Selezione e proiezione
“Nome e reddito delle persone con meno di trenta anni”
select nome, reddito
from persone
where eta < 30
23
SQL
SELECT, abbreviazioni
• data una relazione R su A e B
select
from R
*
• equivale (intutivamente) a
select X.A AS A, X.B AS B
from R X
where true
24
SQL
Impiegato
Nome
Cognome
Dipart
Rossi
Amministrazione
Mario
Bianchi
Produzione
Carlo
Verdi
Amministrazione
Giuseppe
Neri
Distribuzione
Franco
Rossi
Direzione
Carlo
Lanzi
Direzione
Lorenzo
Borroni Amministrazione
Paola
Franco
Produzione
Marco
Dipartimento
Nome
Amministrazione
Produzione
Distribuzione
Direzione
Ricerca
Ufficio
10
20
20
16
14
7
75
20
Indirizzo
Via Tito Livio
P.zza Lavater
Via Segre
Via Tito Livio
Via Morone
Stipendio
45
36
40
45
80
73
40
46
Citta
Milano
Torino
Roma
Milano
Milano
25
SQL
Selezione, senza proiezione
select *
from Impiegato
where Cognome = 'Rossi’
Nome
Mario
Carlo
Cognome
Dipart
Rossi
Amministrazione
Rossi
Direzione
Ufficio
10
14
Stipendio
45
80
26
SQL
Espressioni nella target list
select Stipendio/12 as StipendioMensile
from Impiegato
where Cognome = 'Bianchi'
StipendioMensile
3.00
27
SQL
Disgiunzione
select Nome, Cognome
from Impiegato
where Dipart = 'Amministrazione' or
Dipart = 'Produzione'
Nome
Cognome
Rossi
Mario
Bianchi
Carlo
Verdi
Giuseppe
Borroni
Paola
Franco
Marco
28
SQL
Condizione complessa
select Nome
from Impiegato
where Cognome = 'Rossi' and
(Dipart = 'Amministrazione' or
Dipart = 'Produzione')
Nome
Mario
29
SQL
Condizione “LIKE”
“Gli impiegati che hanno un cognome che ha una 'o' in seconda
posizione e finisce per 'i'.”
select *
from Impiegato
where Cognome like '_o%i'
Nome
Mario
Carlo
Paola
Cognome
Dipart
Rossi
Amministrazione
Rossi
Direzione
Borroni Amministrazione
Ufficio
10
14
75
Stipendio
45
80
40
30
SQL
Gestione dei valori nulli
“Gli impiegati che hanno o potrebbero avere uno stipendio minore
di 50 milioni”
Nome
Mario
Carlo
Paola
Cognome
Dipart
Rossi
Amministrazione
Rossi
Direzione
Borroni Amministrazione
Ufficio
10
14
75
Stipendio
45
80
NULL
select *
from Impiegato
where Stipendio < 50 or Stipendio is null
Nome
Mario
Paola
Cognome
Dipart
Rossi
Amministrazione
Borroni Amministrazione
Ufficio
10
75
Stipendio
45
NULL
31
SQL
Selezione, proiezione e join
“I padri di persone che guadagnano più di venti milioni”
select distinct padre
from persone, paternita
where figlio = nome and
reddito > 20
32
SQL
Proiezione, senza selezione
select Nome, Cognome
from Impiegato
Nome
Cognome
Rossi
Mario
Bianchi
Carlo
Verdi
Giuseppe
Neri
Franco
Rossi
Carlo
Lanzi
Lorenzo
Borroni
Paola
Franco
Marco
33
SQL
Proiezione: duplicati
select Cognome
from Impiegato
Cognome
Rossi
Bianchi
Verdi
Neri
Rossi
Lanzi
Borroni
Franco
select distinct Cognome
from Impiegato
Cognome
Rossi
Bianchi
Verdi
Neri
Lanzi
Borroni
Franco
34
SQL
Join naturale
“Padre e madre di ogni persona”
select paternita.figlio, padre, madre
from maternita, paternita
where paternita.figlio = maternita.figlio
35
SQL
Join di una relazione con se stessa
“Le persone che guadagnano più dei rispettivi padri;
mostrare nome, reddito e reddito del padre”
select f.nome, f.reddito, p.reddito
from persone p, paternita, persone f
where p.nome = padre and
figlio = f.nome and
f.reddito > p.reddito
36
SQL
Ridenominazione del risultato
“Le persone che guadagnano più dei rispettivi padri;
mostrare nome, reddito e reddito del padre”
select figlio,
f.reddito as reddito,
p.reddito as redditoPadre
from persone p, paternita, persone f
where p.nome = padre and
figlio = f.nome and
f.reddito > p.reddito
37
SQL
SELECT, con join esplicito, sintassi
select AttrExpr [ [as] Alias ] {,AttrExpr [ [as] Alias ] }
from Tabella [ [ as ] Alias ]
{[ TipoJoin ] join Tabella [ [ as ] Alias ] on CondDiJoin }, ...
[ where AltraCondizione ]
38
SQL
Join esplicito
“Padre e madre di ogni persona”
select madre, paternita.figlio, padre
from maternita join paternita on
paternita.figlio = maternita.figlio
39
SQL
Ulteriore estensione: join naturale
“Padre e madre di ogni persona”
select madre, paternita.figlio, padre
from maternita natural join paternita
select madre, paternita.figlio, padre
from maternita join paternita on
paternita.figlio = maternita.figlio
40
SQL
Join
maternita
Madre
Luisa
Luisa
Anna
Anna
Maria
Maria
Figlio
Maria
Luigi
Olga
Filippo
Andrea
Aldo
Madre
Anna
Anna
Maria
Maria
paternita
Figlio
Olga
Filippo
Andrea
Aldo
Padre
Sergio
Luigi
Luigi
Franco
Franco
Figlio
Franco
Olga
Filippo
Andrea
Aldo
Padre
Luigi
Luigi
Franco
Franco
41
SQL
Join esplicito
select I.Nome, Cognome, Citta
from Impiegato I join Dipartimento D
on Dipart = D.Nome
Nome
Cognome
Rossi
Mario
Bianchi
Carlo
Verdi
Giuseppe
Neri
Franco
Rossi
Carlo
Lanzi
Lorenzo
Borroni
Paola
Franco
Marco
Dipart
Milano
Torino
Milano
Roma
Milano
Milano
Milano
Torino
42
SQL
Ordinamento del risultato
order by AttrDiOrdinamento [ asc | desc ]
{, AttrDiOrdinamento [ asc | desc ] }
select Cognome, Nome, Stipendio
from Impiegato
where Dipart like 'Amm%'
order by Stipendio desc, Cognome
Cognome
Rossi
Borroni
Verdi
Nome
Mario
Paola
Giuseppe
Stipendio
45
40
40
43
SQL
Necessità di operatori su tuple
• tutte le condizioni dell’algebra vengono valutate su una tupla
alla volta
• la condizione è sempre un predicato che viene valutato su
ciascuna tupla indipedentemente da tutte le altre
• se volessi contare il numero di impiegati di un certo dipartimento
a partire da una relazione Impiegato come si fa?
• occorre introdurre degli operatori che consentono di valutare
proprietà che dipendono da insiemi di tuple
• questi operatori sono detti operatori aggregati
44
SQL
Operatori aggregati
select count(*) AS NumeroImpiegati
from Impiegato
where Dipart = 'Produzione'
NumeroImpiegati
2
• l’operatore aggregato (count) viene applicato al risultato
dell’interrogazione:
select *
from Impiegato
where Dipart = 'Produzione'
45
SQL
COUNT: sintassi
count ( < * | [ distinct | all ] ListaAttributi > )
select count(Stipendio) as NumeroStipendi
from Impiegato
NumeroStipendi
8
select count(distinct Stipendio)
as StipendiDiversi
from Impiegato
StipendiDiversi
6
46
SQL
COUNT e valori nulli
Impiegato
Nome
Cognome
Dipart
Rossi
Amministrazione
Mario
Bianchi
Produzione
Carlo
Verdi
Amministrazione
Giuseppe
Ufficio
10
20
20
Stipendio
45
45
NULL
select count(*) as NumeroImpiegati
from Impiegato
NumeroImpiegati
3
select count(Stipendio) as NumeroStipendi
from Impiegato
NumeroStipendi
2
47
SQL
Somma, media, massimo, minimo
< sum | max | min | avg > ( [ distinct | all ] AttrEspr )
Totale degli stipendi del dipartimento amministrazione
select sum(Stipendio) as TotaleStipendi
from Impiegato
where Dipart = 'Amministrazione'
TotaleStipendi
125
• escludono opportunamente i valori nulli
48
SQL
Join e operatore aggregato
“Il massimo stipendio tra quelli degli impiegati che lavorano in un
dipartimento con sede a Milano”
select max(Stipendio)
from Impiegato, Dipartimento D
where Dipart = D.Nome and
Citta = 'Milano'
80
• Nota: non abbiamo usato la as e l’attributo nel risultato non ha
nome
49
SQL
Operatori aggregati e target list
• un’interrogazione scorretta:
select Cognome, Nome, max(Stipendio)
from Impiegato, Dipartimento
where Dipart = NomeDip and
Citta = 'Milano’
• di chi sarebbe il cognome? La target list deve essere omogenea
50
SQL
Interrogazioni con raggruppamento
• gli operatori aggregati vengono applicati ad un insieme di righe
• gli esempi visti operano su tutte le righe
• spesso esiste l’esigenza di applicare operatori aggregati a
distintamente ad insiemi di tuple
• in SQL l’operatore group by ci consente di fare questo
51
SQL
Operatori aggregati e raggruppamenti
“Per ogni dipartimento, la somma degli stipendi”
select Dipart, sum(Stipendio) as SommaStipendi
from Impiegato
group by Dipart
Dipart
Amministrazione
Produzione
Distribuzione
Direzione
SommaStipendi
125
82
45
153
52
SQL
Semantica di interrogazioni
con operatori aggregati e raggruppamenti
• interrogazione senza group by e senza operatori aggregati:
select Dipart, Stipendio
from Impiegato
Dipart
Amministrazione
Produzione
Amministrazione
Distribuzione
Direzione
Direzione
Amministrazione
Produzione
Stipendio
45
36
40
45
80
73
40
46
53
SQL
Semantica …, 2
• poi si raggruppa e si applica l’operatore aggregato a ciascun
gruppo
dopo group by
Dipart
Amministrazione
Amministrazione
Amministrazione
Distribuzione
Direzione
Direzione
Produzione
Produzione
Stipendio
45
40
40
45
80
73
36
46
dopo sum()as
Dipart
SommaStipendi
Amministrazione
125
Distribuzione
45
Direzione
153
Produzione
82
54
SQL
Raggruppamento sbagliato
select
from
group
ufficio
impiegato
by dipart
Ad ogni valore ( e quindi gruppo) di dipart possono corrispondere
più valori di ufficio. Quale scegliere?
55
SQL
importante!
•
•
•
nella clausola select può comparire solo un sottoinsieme S degli
attributi utilizzati nella clausola group by
in questo modo, ciascuna tupla sugli attributi in S è associata ad un
unico valore del gruppo
E’ una condizione molto restrittiva.
interrogazione scorretta:
select dipart, count (*), d.città
from impiegato i join dipartimento d on i.dipart=d.nome
group by dipart
corretta:
select dipart, count (*), d.città
from impiegato i join dipartimento d on i.dipart=d.nome
group by dipart, città
56
SQL
Condizioni sui gruppi
“I dipartimenti che spendono più di 100 milioni in stipendi”
select Dipart,
sum(Stipendio) as SommaStipendi
from Impiegati
group by Dipart
having sum(Stipendio) > 100
Dipart
SommaStipendi
Amministrazione
125
Direzione
153
57
SQL
WHERE o HAVING?
“I dipartimenti per cui la media degli stipendi degli impiegati che
lavorano nell'ufficio 20 è superiore a 25 milioni”
select Dipart
from Impiegato
where Ufficio = 20
group by Dipart
having avg(Stipendio) > 25
58
SQL
Sintassi, riassumiamo
SelectSQL ::=
select ListaAttributiOEspressioni
from ListaTabelle
[ where CondizioniSemplici ]
[ group by ListaAttributiDiRaggruppamento ]
[ having CondizioniAggregate ]
[ order by ListaAttributiDiOrdinamento ]
59
SQL
Unione, intersezione e differenza
• la sintassi select-from-where da sola non permette di fare
unioni; serve un costrutto esplicito:
SelectSQL { < union | intersect | except > [ all ] SelectSQL }
select Nome
from Impiegato
union
select Cognome as Nome
from Impiegato
• i duplicati vengono eliminati (a meno che si usi all) (anche
dalle proiezioni)
60
SQL
Interrogazioni nidificate
• le condizioni atomiche esprimibili nella clausola WHERE
permettono anche
– il confronto fra un attributo (o più, vedremo poi) e il risultato
di una sottointerrogazione
– quantificazioni esistenziali
61
SQL
Interrogazioni nidificate, sintassi
ConfrontoConNidificazione :: =
Scalare OpConfronto [ any | all ]
( SelectAttributoSingolo)|
exists ( SelectStar )
• senza any o all , il risultato della SelectAttributoSingolo deve
essere un solo valore
• v.A  any Select... (risp. all) è vero se v.A è in relazione 
con almeno uno (risp. con tutti) dei valori del risultato della
Select
• = any puo essere abbreviato con in
• exists( SelectStar ) è vero se il risultato della
sottoespressione non è vuoto.
62
SQL
Interrogazioni nidificate
“Gli impiegati che lavorano in dipartimenti di Roma”
select *
from Impiegato
where Dipart = any (select Nome
from Dipartimento
where Citta = 'Roma')
63
SQL
“Nome e reddito del padre di Mario”
select Nome, Reddito
from Persone
where Nome = (select Padre
from Paternita
where Figlio = 'Mario')
select Nome, Reddito
from Persone, Paternita
where Nome = Padre and
Figlio = 'Mario'
64
SQL
Interrogazioni nidificate, commenti
• 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
• 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
65
SQL
“Nome e reddito dei padri di persone che guadagnano più di 20 milioni"
select Nome, Reddito
from Persone
where Nome in (select Padre
from Paternita
where Figlio =any (select Nome
from Persone
where Reddito > 20))
select distinct P.Nome, P.Reddito
from Persone P, Paternita, Persone F
where P.Nome = Padre and
Figlio = F.Nome and
F.Reddito > 20
66
SQL
Interrogazioni piatte o nidificate?
select I1.Nome
from Impiegato I1, Impiegato I2
where I1.Nome = I2.Nome and
I2.Dipart = 'Produzione'
select Nome
from Impiegato
where Nome = any (select Nome
from Impiegato
where Dipart = 'Produzione')
67
SQL
“Le persone che hanno almeno un figlio”
select *
from Persone
where exists (select *
from Paternita
where Padre = Nome) or
exists (select *
from Maternita
where Madre = Nome)
68
SQL
Interrogazioni nidificate, commenti, 2
• 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”
• nota: in un blocco si può fare riferimento a variabili definite in
blocchi più esterni; la semantica (prodotto cartesiano, selezione,
proiezione) non funziona più, ne serve una più sofisticata:
– l’interrogazione interna va ripetuta una volta per ciascun
valore della variabile
69