SQL

SQL

2

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

3

SQL

Domini

• Domini elementari (predefiniti)

• Domini definiti dall‘utente (semplici, ma riutilizzabili)

4

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

5

SQL

Domini elementari, 2

• Data, ora, intervalli:

date

time [( Precisione )] [ with time zone ]

timestamp [( Precisione )] [ with time zone ]

interval UnitàDiTempo [ to UnitàDiTempo ]

6

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

7

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 >

8

SQL

Esempio

• create table Dipartimento

(

nome char(20) primary key,

indirizzo char(50),

città char(20)

)

9

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 )

10

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

11

SQL

Vincoli intrarelazionali, esempi

Nome character(20) not null, Cognome character(20) not null,

unique (Cognome,Nome)

• è diverso da:

Nome character(20) not null unique, Cognome character(20) not null unique

12

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)

13

SQL

Richiamo: base di dati con vincoli di integrità referenziale

infrazioni Codice Data Vigile Prov Numero65524 3/9/1997 343 MI 3K988687635 4/12/1997 476 MI 6D556382236 4/12/1997 343 RM 7C556735632 6/1/1998 476 RM 7C556776543 5/3/1998 548 MI 6D5563

vigili Matricola Cognome Nome343 Rossi Luca476 Neri Pino548 Nicolosi Gino

automobili Prov Numero Proprietario …MI 3K9886 Nestore …MI 6D5563 Nestore …RM 7C5567 Menconi …RM 1A6673 Mussone …MI 5E7653 Marchi …

14

SQL

Vincoli interrelazionali, esempio

create table Infrazioni( Codice character(6) primary key, Data date not null, Vigile 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 sonoesterne

15

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)

16

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 >

17

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

18

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

19

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• ...

20

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

21

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

22

SQL

persone Nome Eta RedditoAndrea 27 21

Aldo 25 15Maria 55 42Anna 50 35

Filippo 26 30Luigi 50 40

Franco 60 20Olga 30 41

Sergio 85 35Luisa 75 87

maternita Madre FiglioLuisa MariaLuisa LuigiAnna OlgaAnna FilippoMaria AndreaMaria Aldo

paternita Padre FiglioSergio FrancoLuigi OlgaLuigi Filippo

Franco AndreaFranco Aldo

23

SQL

Selezione e proiezione

“Nome e reddito delle persone con meno di trenta anni”

select nome, redditofrom personewhere eta < 30

24

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 Bfrom R Xwhere true

25

SQL

Impiegato Nome Cognome Dipart Ufficio Stipendio Mario Rossi Amministrazione 10 45 Carlo Bianchi Produzione 20 36 Giuseppe Verdi Amministrazione 20 40 Franco Neri Distribuzione 16 45 Carlo Rossi Direzione 14 80 Lorenzo Lanzi Direzione 7 73 Paola Borroni Amministrazione 75 40 Marco Franco Produzione 20 46

Dipartimento Nome Indirizzo CittaAmministrazione Via Tito Livio Milano

Produzione P.zza Lavater TorinoDistribuzione Via Segre Roma

Direzione Via Tito Livio MilanoRicerca Via Morone Milano

26

SQL

Selezione, senza proiezione

select *

from Impiegato

where Cognome = 'Rossi’

Nome Cognome Dipart Ufficio StipendioMario Rossi Amministrazione 10 45Carlo Rossi Direzione 14 80

27

SQL

Espressioni nella target list

select Stipendio/12 as StipendioMensile

from Impiegato

where Cognome = 'Bianchi'

StipendioMensile3.00

28

SQL

Disgiunzione

select Nome, Cognome

from Impiegato

where Dipart = 'Amministrazione' or

Dipart = 'Produzione'

Nome CognomeMario Rossi

Carlo Bianchi

Giuseppe Verdi

Paola Borroni

Marco Franco

29

SQL

Condizione complessa

select Nome

from Impiegato

where Cognome = 'Rossi' and

(Dipart = 'Amministrazione' or

Dipart = 'Produzione')

NomeMario

30

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 Cognome Dipart Ufficio StipendioMario Rossi Amministrazione 10 45Carlo Rossi Direzione 14 80Paola Borroni Amministrazione 75 40

31

SQL

Gestione dei valori nulli

“Gli impiegati che hanno o potrebbero avere uno stipendio minore di 50 milioni”

select *

from Impiegato

where Stipendio < 50 or Stipendio is null

Nome Cognome Dipart Ufficio StipendioMario Rossi Amministrazione 10 45Carlo Rossi Direzione 14 80Paola Borroni Amministrazione 75 NULL

Nome Cognome Dipart Ufficio StipendioMario Rossi Amministrazione 10 45Paola Borroni Amministrazione 75 NULL

32

SQL

Selezione, proiezione e join

“I padri di persone che guadagnano più di venti milioni”

select distinct padrefrom persone, paternitawhere figlio = nome and

reddito > 20

33

SQL

Proiezione, senza selezione

select Nome, Cognome

from Impiegato

Nome Cognome Mario Rossi Carlo Bianchi Giuseppe Verdi Franco Neri Carlo Rossi Lorenzo Lanzi Paola Borroni Marco Franco

34

SQL

Proiezione: duplicati

select Cognome

from Impiegato

CognomeRossi

BianchiVerdiNeri

RossiLanzi

BorroniFranco

select distinct Cognome

from Impiegato

CognomeRossi

BianchiVerdiNeri

LanziBorroniFranco

35

SQL

Join naturale“Padre e madre di ogni persona”

select paternita.figlio, padre, madrefrom maternita, paternitawhere paternita.figlio = maternita.figlio

36

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 fwhere p.nome = padre and

figlio = f.nome andf.reddito > p.reddito

37

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 fwhere p.nome = padre and

figlio = f.nome andf.reddito > p.reddito

38

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 ]

39

SQL

Join esplicito

“Padre e madre di ogni persona”

select madre, paternita.figlio, padre from maternita join paternita on

paternita.figlio = maternita.figlio

40

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

41

SQL

Join

maternita Madre FiglioLuisa MariaLuisa LuigiAnna OlgaAnna FilippoMaria AndreaMaria Aldo

paternita Padre FiglioSergio FrancoLuigi OlgaLuigi Filippo

Franco AndreaFranco Aldo

Madre Figlio PadreAnna Olga LuigiAnna Filippo LuigiMaria Andrea FrancoMaria Aldo Franco

42

SQL

Join esplicito

select I.Nome, Cognome, Citta

from Impiegato I join Dipartimento D

on Dipart = D.Nome

Nome Cognome DipartMario Rossi Milano

Carlo Bianchi Torino

Giuseppe Verdi Milano

Franco Neri Roma

Carlo Rossi Milano

Lorenzo Lanzi Milano

Paola Borroni Milano

Marco Franco Torino

43

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 Nome StipendioRossi Mario 45

Borroni Paola 40Verdi Giuseppe 40

44

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

45

SQL

Operatori aggregati

select count(*) AS NumeroImpiegatifrom Impiegatowhere Dipart = 'Produzione'

• l’operatore aggregato (count) viene applicato al risultato dell’interrogazione:

select *from Impiegatowhere Dipart = 'Produzione'

NumeroImpiegati2

46

SQL

COUNT: sintassi

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

select count(Stipendio) as NumeroStipendi

from Impiegato

select count(distinct Stipendio)

as StipendiDiversi

from Impiegato

NumeroStipendi8

StipendiDiversi6

47

SQL

COUNT e valori nulli

select count(*) as NumeroImpiegati

from Impiegato

select count(Stipendio) as NumeroStipendi

from Impiegato

NumeroImpiegati3

NumeroStipendi2

Impiegato Nome Cognome Dipart Ufficio StipendioMario Rossi Amministrazione 10 45Carlo Bianchi Produzione 20 45

Giuseppe Verdi Amministrazione 20 NULL

48

SQL

Somma, media, massimo, minimo

< sum | max | min | avg > ( [ distinct | all ] AttrEspr )

Totale degli stipendi del dipartimento amministrazione

select sum(Stipendio) as TotaleStipendifrom Impiegatowhere Dipart = 'Amministrazione'

• escludono opportunamente i valori nulli

TotaleStipendi125

49

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'

• Nota: non abbiamo usato la as e l’attributo nel risultato non ha nome

80

50

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

51

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

52

SQL

Operatori aggregati e raggruppamenti

“Per ogni dipartimento, la somma degli stipendi”

select Dipart, sum(Stipendio) as SommaStipendi

from Impiegato

group by Dipart

Dipart SommaStipendiAmministrazione 125

Produzione 82Distribuzione 45

Direzione 153

53

SQL

Semantica di interrogazioni con operatori aggregati e raggruppamenti

• interrogazione senza group by e senza operatori aggregati:

select Dipart, Stipendio

from Impiegato

Dipart StipendioAmministrazione 45

Produzione 36Amministrazione 40

Distribuzione 45Direzione 80Direzione 73

Amministrazione 40Produzione 46

54

SQL

Semantica …, 2

• poi si raggruppa e si applica l’operatore aggregato a ciascun gruppo

dopo group by dopo sum()as

Dipart StipendioAmministrazione 45Amministrazione 40Amministrazione 40

Distribuzione 45Direzione 80Direzione 73

Produzione 36Produzione 46

Dipart SommaStipendiAmministrazione 125

Distribuzione 45Direzione 153

Produzione 82

55

SQL

Raggruppamento sbagliato

select ufficio

from impiegato

group by dipart

Ad ogni valore ( e quindi gruppo) di dipart possono corrispondere

più valori di ufficio. Quale scegliere?

56

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.nomegroup by dipart

corretta:select dipart, count (*), d.cittàfrom impiegato i join dipartimento d on i.dipart=d.nomegroup by dipart, città

57

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) > 100Dipart SommaStipendi

Amministrazione 125Direzione 153

58

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

59

SQL

Sintassi, riassumiamo

SelectSQL ::=

select ListaAttributiOEspressioni

from ListaTabelle

[ where CondizioniSemplici ]

[ group by ListaAttributiDiRaggruppamento ]

[ having CondizioniAggregate ]

[ order by ListaAttributiDiOrdinamento ]

60

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 Nomefrom Impiegatounionselect Cognome as Nomefrom Impiegato

• i duplicati vengono eliminati (a meno che si usi all) (anche dalle proiezioni)

61

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

62

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.

63

SQL

Interrogazioni nidificate

“Gli impiegati che lavorano in dipartimenti di Roma”

select *

from Impiegato

where Dipart = any (select Nome

from Dipartimento

where Citta = 'Roma')

64

SQL

“Nome e reddito del padre di Mario”

select Nome, Redditofrom Personewhere Nome = (select Padre from Paternita where Figlio = 'Mario')

select Nome, Redditofrom Persone, Paternitawhere Nome = Padre and Figlio = 'Mario'

65

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

66

SQL

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

select Nome, Redditofrom Personewhere Nome in (select Padre from Paternita where Figlio =any (select Nome from Persone where Reddito > 20))

select distinct P.Nome, P.Redditofrom Persone P, Paternita, Persone Fwhere P.Nome = Padre and Figlio = F.Nome and F.Reddito > 20

67

SQL

Interrogazioni piatte o nidificate?

select I1.Nomefrom Impiegato I1, Impiegato I2where I1.Nome = I2.Nome andI2.Dipart = 'Produzione'

select Nomefrom Impiegatowhere Nome = any (select Nome

from Impiegato where Dipart =

'Produzione')

68

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)

69

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