SISTEMI DI BASI DI DATI - Dipartimento di Matematica e
Download
Report
Transcript SISTEMI DI BASI DI DATI - Dipartimento di Matematica e
SQL, Definizione e
interrogazione di basi di
dati
Capitolo 4:
SQL (Slides da Atzeni, Ceri,
Paraboschi, Torlonee, Basi di dati
McGraw-Hill, 2002)
SQL
Structured Query Language
è un linguaggio con varie funzionalità:
contiene
sia il DDL che il DML
ne esistono varie versioni
vediamo gli aspetti essenziali, non i
dettagli
18/07/2015
SQL, Definizione e interrogazione di
basi di dati
2
Breve storia dell‘SQL
SQL: Structured Query Language
SQL sviluppato alla IBM nel 1973
Quasi tutti i DBMS commerciali adottano lo standard SQL piu’ estensioni
proprie (non-standard)
Alcuni sistemi commerciali
Oracle, Informix, Sybase, DB2, SQL-Server, etc.
Alcuni sistemi open-source:
Dal 1983 standard de facto
Primo standard nel 1986 rivisto nel 1989 (SQL-89)
Secondo standard nel 1992 (SQL-2 o SQL-92)
Terzo standard nel 1999 (SQL-3 o SQL-99)
MySQL, Postgres
Esistono sistemi commerciali che utilizzano interfacce tipo QBE (Query by
Example): ACCESS
Tuttavia hanno sistemi per la traduzione automatica in SQL
18/07/2015
SQL, Definizione e interrogazione di
basi di dati
3
Definizione dei dati in SQL
Istruzione CREATE TABLE:
definisce
uno schema di relazione e ne crea
un’istanza vuota
specifica attributi, domini e vincoli
18/07/2015
SQL, Definizione e interrogazione di
basi di dati
4
CREATE TABLE, esempio
CREATE TABLE Impiegato(
Matricola CHAR(6) PRIMARY KEY,
Nome CHAR(20) NOT NULL,
Cognome CHAR(20) NOT NULL,
Dipart CHAR(15),
Stipendio NUMERIC(9) DEFAULT 0,
FOREIGN KEY(Dipart) REFERENCES
Dipartimento(NomeDip),
UNIQUE (Cognome,Nome)
)
18/07/2015
SQL, Definizione e interrogazione di
basi di dati
5
Domini
Domini elementari (predefiniti)
Domini definiti dall'utente (semplici, ma
riutilizzabili)
18/07/2015
SQL, Definizione e interrogazione di
basi di dati
6
Domini elementari
Carattere: singoli caratteri o stringhe, anche di
lunghezza variabile
Bit: singoli booleani o stringhe
Numerici, esatti e approssimati
Data, ora
Sistemi diversi estendono il set di base con
domini non standard (vettori, periodi, ecc.)
18/07/2015
SQL, Definizione e interrogazione di
basi di dati
7
Definizione di domini
Istruzione CREATE DOMAIN:
definisce
un dominio (semplice), utilizzabile in
definizioni di relazioni, anche con vincoli e
valori di default
18/07/2015
SQL, Definizione e interrogazione di
basi di dati
8
CREATE DOMAIN, esempio
CREATE DOMAIN Voto
AS SMALLINT DEFAULT NULL
CHECK ( value >=18 AND value <= 30 )
18/07/2015
SQL, Definizione e interrogazione di
basi di dati
9
Vincoli intrarelazionali
NOT NULL
UNIQUE definisce chiavi
PRIMARY KEY: chiave primaria (una sola,
implica NOT NULL)
CHECK, vedremo più avanti
18/07/2015
SQL, Definizione e interrogazione di
basi di dati
10
UNIQUE e PRIMARY KEY
due forme:
nella
definizione di un attributo, se forma da
solo la chiave
come elemento separato
18/07/2015
SQL, Definizione e interrogazione di
basi di dati
11
CREATE TABLE, esempio
CREATE TABLE Impiegato(
Matricola CHAR(6) PRIMARY KEY,
Nome CHAR(20) NOT NULL,
Cognome CHAR(20) NOT NULL,
Dipart CHAR(15),
Stipendio NUMERIC(9) DEFAULT 0,
FOREIGN KEY(Dipart) REFERENCES
Dipartimento(NomeDip),
UNIQUE (Cognome,Nome)
)
18/07/2015
SQL, Definizione e interrogazione di
basi di dati
12
PRIMARY KEY, alternative
Matricola CHAR(6) PRIMARY KEY
Matricola CHAR(6),
…,
PRIMARY KEY (Matricola)
18/07/2015
SQL, Definizione e interrogazione di
basi di dati
13
CREATE TABLE, esempio
CREATE TABLE Impiegato(
Matricola CHAR(6) PRIMARY KEY,
Nome CHAR(20) NOT NULL,
Cognome CHAR(20) NOT NULL,
Dipart CHAR(15),
Stipendio NUMERIC(9) DEFAULT 0,
FOREIGN KEY(Dipart) REFERENCES
Dipartimento(NomeDip),
UNIQUE (Cognome,Nome)
)
18/07/2015
SQL, Definizione e interrogazione di
basi di dati
14
Chiavi su più attributi,
attenzione
Nome
CHAR(20) NOT NULL,
Cognome
CHAR(20) NOT NULL,
UNIQUE (Cognome,Nome),
Nome
CHAR(20) NOT NULL UNIQUE,
Cognome CHAR(20) NOT NULL UNIQUE,
Non è la stessa cosa!
18/07/2015
SQL, Definizione e interrogazione di
basi di dati
15
Vincoli interrelazionali
CHECK, vedremo più avanti
REFERENCES e FOREIGN KEY permettono di
definire vincoli di integrità referenziale
di nuovo due sintassi
per
singoli attributi
su più attributi
E' possibile definire politiche di reazione alla
violazione
18/07/2015
SQL, Definizione e interrogazione di
basi di dati
16
Infrazioni
Codice
34321
53524
64521
73321
Vigili
18/07/2015
Data
1/2/95
4/3/95
5/4/96
5/2/98
Matricola
3987
3295
9345
7543
Vigile
3987
3295
3295
9345
Prov
MI
TO
PR
PR
Cognome
Rossi
Neri
Neri
Mori
SQL, Definizione e interrogazione di
basi di dati
Numero
39548K
E39548
839548
839548
Nome
Luca
Piero
Mario
Gino
17
Infrazioni
Codice
34321
53524
64521
73321
Auto
18/07/2015
Data
1/2/95
4/3/95
5/4/96
5/2/98
Prov
MI
TO
PR
Numero
39548K
E39548
839548
Vigile
3987
3295
3295
9345
Prov
MI
TO
PR
PR
Cognome
Rossi
Rossi
Neri
SQL, Definizione e interrogazione di
basi di dati
Numero
39548K
E39548
839548
839548
Nome
Mario
Mario
Luca
18
CREATE TABLE, esempio
CREATE TABLE Infrazioni(
Codice CHAR(6) NOT NULL PRIMARY KEY,
Data DATE NOT NULL,
Vigile
INTEGER NOT NULL
REFERENCES Vigili(Matricola),
Provincia CHAR(2),
Numero CHAR(6) ,
FOREIGN KEY(Provincia, Numero)
REFERENCES Auto(Provincia, Numero)
)
18/07/2015
SQL, Definizione e interrogazione di
basi di dati
19
Modifiche degli schemi
ALTER DOMAIN
ALTER TABLE
DROP DOMAIN
DROP TABLE
...
18/07/2015
SQL, Definizione e interrogazione di
basi di dati
20
Definizione degli indici
è rilevante dal punto di vista delle
prestazioni
ma è a livello fisico e non logico
in passato era importante perché in alcuni
sistemi era l'unico mezzo per definire
chiavi
CREATE INDEX
18/07/2015
SQL, Definizione e interrogazione di
basi di dati
21
SQL, operazioni sui dati
interrogazione:
SELECT
modifica:
INSERT,
18/07/2015
DELETE, UPDATE
SQL, Definizione e interrogazione di
basi di dati
22
Istruzione SELECT per
l’interrogazione
SELECT ListaAttributi
FROM ListaTabelle
[ WHERE Condizione ]
"target
list"
clausola FROM
clausola
18/07/2015
WHERE
SQL, Definizione e interrogazione di
basi di dati
23
Capacità del comando SELECT
Selezione
Proiezione
Tabella 1
Tabella 1
Tabella 1
18/07/2015
Join
Tabella 2
SQL, Definizione e interrogazione di
basi di dati
24
SELECT
SELECT ListaAttributi
FROM ListaTabelle
[ WHERE Condizione ]
La query
1.
2.
3.
considera il prodotto cartesiano tra le tabelle in ListaTabelle
fra queste seleziona solo le righe che soddisfano la Condizione
e infine valuta le espressioni specificate nella target list
ListaAttributi
La SELECT implementa gli operatori Ridenominazione
Proiezione, Selezione e Join dell’algebra relazionale
18/07/2015
Piu’ altro che vedremo piu’ avanti
SQL, Definizione e interrogazione di
basi di dati
25
Convenzione scrittura comandi SQL
I comandi SQL non sono “case sensitive”
Possono essere distribuiti in una o più righe
terminate da un separatore
Clausole diverse sono usualmente inserite in linee
separate
Per convenzione si usa scrivere i costrutti SQL in
maiuscolo
SELECT Professore, Corsi.Corso
FROM Corsi, Esami, Studenti
WHERE Corsi.Corso = Esami.Corso
AND Esami.Matricola = Studenti.Matricola
AND Nome=‘Teo Verdi’
AND Voto > 24
18/07/2015
SQL, Definizione e interrogazione di
basi di dati
26
Database di esempio
Maternità
Madre
Luisa
Luisa
Anna
Anna
Maria
Maria
Figlio
Maria
Luigi
Olga
Filippo
Andrea
Aldo
Paternità
Padre
Sergio
Luigi
Luigi
Franco
Franco
Figlio
Franco
Olga
Filippo
Andrea
Aldo
18/07/2015
Persone
Nome
Andrea
Aldo
Maria
Anna
Filippo
Luigi
Franco
Olga
Sergio
Luisa
SQL, Definizione e interrogazione di
basi di dati
Età
27
25
55
50
26
50
60
30
85
75
Reddito
21
15
42
35
30
40
20
41
35
87
27
Esempio query
Nome
e reddito delle persone con meno di trenta anni
πNome, Reddito(σEta<30(Persone))
SELECT nome, reddito
FROM persone
WHERE eta < 30
18/07/2015
Nome
Andrea
Aldo
Filippo
SQL, Definizione e interrogazione di
basi di dati
Reddito
21
15
30
28
Espressioni aritmetiche nella target
list
Creare espressioni attraverso l’uso dei
soliti operatori aritmetici: +, -, *, /
Le priorita’ sono quelle standard
Possono
essere alterate con l’uso delle
parentesi
18/07/2015
SQL, Definizione e interrogazione di
basi di dati
29
Uso degli operatori Aritmetici
SQL> SELECT ename, sal, sal+300
2 FROM
emp;
ENAME
SAL
exp
---------- --------- --------KING
5000
5300
BLAKE
2850
3150
CLARK
2450
2750
JONES
2975
3275
MARTIN
1250
1550
ALLEN
1600
1900
...
14 rows selected.
18/07/2015
SQL, Definizione e interrogazione di
basi di dati
30
Precedenza operatori aritmetici
SQL> SELECT ename, sal, 12*sal+100
2 FROM
emp;
ENAME
SAL exp
---------- --------- ---------KING
5000
60100
BLAKE
2850
34300
CLARK
2450
29500
JONES
2975
35800
MARTIN
1250
15100
ALLEN
1600
19300
...
14 rows selected.
18/07/2015
SQL, Definizione e interrogazione di
basi di dati
31
Uso delle parentesi
SQL> SELECT ename, sal, 12*(sal+100)
2 FROM
emp;
ENAME
SAL exp
---------- --------- ----------KING
5000
61200
BLAKE
2850
35400
CLARK
2450
30600
JONES
2975
36900
MARTIN
1250
16200
...
14 rows selected.
18/07/2015
SQL, Definizione e interrogazione di
basi di dati
32
Alias delle colonne
Ridenominare il nome di una colonna
Implementa
l’operatore (Ridenominazione)
dell’algebra relazionale
L’alias deve seguire immediatamente il
nome di una colonna (SENZA VIRGOLA)
può
essere usata opzionalmente la parola
chiave AS tra il nome della colonna e l’alias
Richiede doppio apice se l’alias ha degli
spazi
18/07/2015
SQL, Definizione e interrogazione di
basi di dati
33
Uso dell’Alias
SQL> SELECT ename AS name, sal salary
2 FROM
emp;
NAME
SALARY
------------- --------...
SQL> SELECT ename "Name",
2
sal*12 "Annual Salary"
3 FROM
emp;
Name
Annual Salary
------------- ------------...
18/07/2015
SQL, Definizione e interrogazione di
basi di dati
34
Proiezione, senza selezione
Nome
e reddito di tutte le persone
πNome, Reddito(Persone)
SELECT nome, reddito
FROM persone
18/07/2015
SQL, Definizione e interrogazione di
basi di dati
35
Selezione, senza proiezione
Uso dello `*` nella target list
Nome, età e reddito delle persone con meno di
trenta anni
σEta<30(Persone)
SELECT *
FROM persone
WHERE eta < 30
18/07/2015
SQL, Definizione e interrogazione di
basi di dati
36
Limitare le righe selezionate tramite
WHERE
Corrisponde all’operatore (Restrizione) dell’algebra
relazionale
SELECT
FROM
[WHERE
18/07/2015
[DISTINCT] {*| colonna [alias], ...}
tabella
condizione(i)];
La clausola WHERE segue la clausola FROM
E’ opzionale
SQL, Definizione e interrogazione di
basi di dati
37
Uso della clausola WHERE
SQL> SELECT ename, job, deptno
2 FROM
emp
3 WHERE job='CLERK';
ENAME
---------JAMES
SMITH
ADAMS
MILLER
18/07/2015
JOB
DEPTNO
--------- --------CLERK
30
CLERK
20
CLERK
20
CLERK
10
SQL, Definizione e interrogazione di
basi di dati
38
Stringhe di caratteri e Date
Stringhe di caratteri e date vanno
incluse tra apici.
I caratteri sono case sensitive e le date
sono format sensitive
SQL> SELECT
2 FROM
3 WHERE
18/07/2015
ename, job, deptno
emp
ename = 'JAMES';
SQL, Definizione e interrogazione di
basi di dati
39
Condizione di WHERE
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)
18/07/2015
SQL, Definizione e interrogazione di
basi di dati
40
Uso degli Operatori di Confronto
SQL> SELECT ename, sal, comm
2 FROM
emp
3 WHERE sal<=comm;
ENAME
SAL
COMM
---------- --------- --------MARTIN
1250
1400
18/07/2015
SQL, Definizione e interrogazione di
basi di dati
41
Altri Operatori di Confronto
18/07/2015
Operatore
Significato
BETWEEN
...AND...
compreso tra due valori
IN(list)
Corrisp. ad uno dei valori nella lista
LIKE
Operatore di pattern matching
IS NULL
Valore nullo
SQL, Definizione e interrogazione di
basi di dati
42
Uso dell’operatore BETWEEN
BETWEEN consente la selezione di
righe con attributi in un particolare range
SQL> SELECT
2 FROM
3 WHERE
ename, sal
emp
sal BETWEEN 1000 AND 1500;
ENAME
SAL
---------- --------MARTIN
1250
TURNER
1500
WARD
1250
ADAMS
1100
MILLER
1300
18/07/2015
Limite
inferiore
Limite
superiore
SQL, Definizione e interrogazione di
basi di dati
43
Predicato BETWEEN
Espr1 [NOT] BETWEEN Espr2 AND Espr3
Equivale a
[NOT]
18/07/2015
Espr2 Espr1 AND Espr1Espr3
SQL, Definizione e interrogazione di
basi di dati
44
Uso dell’operatore IN
E’ usato per selezionare righe che
hanno un attributo che assume valori
contenuti in una lista.
SQL> SELECT
2 FROM
3 WHERE
EMPNO
--------7902
7369
7788
7876
18/07/2015
empno, ename, sal, mgr
emp
mgr IN (7902, 7566, 7788);
ENAME
SAL
MGR
---------- --------- --------FORD
3000
7566
SMITH
800
7902
SCOTT
3000
7566
ADAMS
1100
7788
SQL, Definizione e interrogazione di
basi di dati
45
Uso dell’operatore LIKE
• LIKE è usato per effettuare ricerche wildcard
di una stringa di valori.
• Le condizioni di ricerca possono contenere
sia letterali, caratteri o numeri.
– % denota zero o più caratteri.
– _ denota un carattere.
SQL> SELECT
2 FROM
3 WHERE
18/07/2015
ename
emp
ename LIKE 'S%';
SQL, Definizione e interrogazione di
basi di dati
46
Uso dell’operatore LIKE
Il pattern-matching di caratteri può essere
combinato
SQL> SELECT
2 FROM
3 WHERE
ename
emp
ename LIKE '_A%';
ENAME
---------MARTIN
JAMES
WARD
I’identificatore ESCAPE (\) deve essere
usato per cercare "%" o "_".
18/07/2015
SQL, Definizione e interrogazione di
basi di dati
47
Gestione dei valori nulli
Impiegati
Matricola Cognome Filiale
7309
5998
Rossi
Neri
Milano
Roma
5998
9553
Bruni
Neri
Milano
9553
Bruni
Milano
Età
32
45
NULL
45
NULL
σ Età > 40 OR Età IS NULL (Impiegati)
18/07/2015
SQL, Definizione e interrogazione di
basi di dati
48
Gli
impiegati la cui età è o
potrebbe essere maggiore di 40
σ Età > 40 OR Età IS NULL (Impiegati)
SELECT *
FROM impiegati
WHERE eta > 40 OR eta is null
18/07/2015
SQL, Definizione e interrogazione di
basi di dati
49
SQL e algebra relazionale
R1(A1,A2) R2(A3,A4)
SELECT R1.A1, R2.A4
FROM R1, R2
WHERE R1.A2 = R2.A3
prodotto cartesiano (FROM)
selezione (WHERE)
proiezione (SELECT)
18/07/2015
SQL, Definizione e interrogazione di
basi di dati
50
SQL e algebra relazionale
R1(A1,A2) R2(A3,A4)
SELECT R1.A1, R2.A4
FROM R1, R2
WHERE R1.A2 = R2.A3
πA1,A4 (σA2=A3 (R1 JOIN R2))
18/07/2015
SQL, Definizione e interrogazione di
basi di dati
51
possono essere necessarie
ridenominazioni
nel prodotto cartesiano
nella target list
SELECT X.A1 AS B1, ...
FROM R1 X, R2 Y, R1 Z
WHERE X.A2 = Y.A3 AND ...
18/07/2015
SQL, Definizione e interrogazione di
basi di dati
52
Self
JOIN su R1
SELECT X.A1 AS B1, Y.A4 AS B2
FROM R1 X, R2 Y, R1 Z
WHERE X.A2 = Y.A3 AND Y.A4 = Z.A1
δ
B1,B2A1,A4
(
π A1,A4 (σ A2 = A3 AND A4 = C1(
R1 JOIN R2 JOIN δ C1,C2 A1,A2 (R1))))
18/07/2015
SQL, Definizione e interrogazione di
basi di dati
53
cognome e filiale di tutti gli impiegati
Matricola Cognome Filiale Stipendio
7309
Neri
Napoli
55
5998
Neri
Milano
64
9553
Rossi
Roma
44
5698
Rossi
Roma
64
π Cognome, Filiale (Impiegati)
18/07/2015
SQL, Definizione e interrogazione di
basi di dati
54
SELECT
cognome, filiale
FROM impiegati
Cognome Filiale
Neri
Napoli
Neri
Milano
Rossi
Roma
Rossi
Roma
18/07/2015
SELECT distinct
cognome, filiale
FROM impiegati
Cognome Filiale
Neri
Napoli
Neri
Milano
Rossi
Roma
SQL, Definizione e interrogazione di
basi di dati
55
Maternità
Persone
Madre
Figlio
Luisa
Maria
Nome Età Reddito
Luisa
Luigi
Andrea 27
Anna
Olga
Aldo
25
15
Anna
Filippo
Maria
55
42
Maria
Andrea
Anna
50
35
Maria
Aldo
Filippo 26
30
Luigi
Paternità
Padre
Figlio
Sergio Franco
Luigi
Olga
Luigi
Filippo
21
50
40
Franco 60
20
Olga
30
41
Sergio
85
35
Luisa
75
87
Franco Andrea
Franco
18/07/2015
Aldo
SQL, Definizione e interrogazione di
basi di dati
56
Selezione, proiezione e join
I
padri di persone che guadagnano più di 20
πPadre(paternita JOIN Figlio=Nome (σReddito>20 (persone)))
SELECT distinct padre
FROM persone, paternita
WHERE figlio = nome AND
reddito > 20
18/07/2015
SQL, Definizione e interrogazione di
basi di dati
57
Join naturale
Padre e madre di ogni persona
paternita JOIN maternita
SELECT paternita.figlio,padre, madre
FROM maternita, paternita
WHERE paternita.figlio = maternita.figlio
18/07/2015
SQL, Definizione e interrogazione di
basi di dati
58
Necessita’ di ridenominazione
Le persone che guadagnano più dei rispettivi padri;
mostrare nome, reddito e reddito del padre
πNome, Reddito, RP (σReddito>RP
(δNP,EP,RP Nome,Eta,Reddito(persone)
JOIN NP=Padre
(paternita JOINFiglio =Nome persone)))
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
18/07/2015
SQL, Definizione e interrogazione di
basi di dati
59
SELECT, con ridenominazione
del risultato
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
O meglio
SELECT x.figlio, f.reddito AS reddito,
p.reddito AS redditoPadre
FROM persone p, paternita x, persone f
WHERE p.nome = x.padre AND x.figlio = f.nome
AND f.reddito > p.reddito
18/07/2015
SQL, Definizione e interrogazione di
basi di dati
60
Join esplicito (JOIN-ON)
Sintassi:
SELECT …
FROM Tabella { … JOIN Tabella ON CondDiJoin }, …
[ WHERE AltraCondizione ]
Esempio: padre e madre di ogni persona (le due versioni):
SELECT paternita.figlio,padre, madre
FROM maternita, paternita
WHERE paternita.figlio = maternita.figlio
SELECT madre, paternita.figlio, padre
FROM maternita JOIN paternita ON
paternita.figlio = maternita.figlio
18/07/2015
SQL, Definizione e interrogazione di
basi di dati
61
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
SELECT f.nome, f.reddito, p.reddito
FROM persone p JOIN paternita ON p.nome = padre
JOIN persone f ON figlio = f.nome
WHERE f.reddito > p.reddito
18/07/2015
SQL, Definizione e interrogazione di
basi di dati
62
Ulteriore estensione: join naturale
(meno diffuso)
πFiglio,Padre,Madre(
paternita JOIN Figlio = Nome δ Nome=Figlio(maternita))
paternita JOIN maternita
SELECT madre, paternita.figlio, padre
FROM maternita JOIN paternita ON
paternita.figlio = maternita.figlio
SELECT madre, paternita.figlio, padre
FROM maternita natural JOIN paternita
18/07/2015
SQL, Definizione e interrogazione di
basi di dati
63
Join esterno: "outer join"
Padre e, se nota, madre di ogni persona
SELECT paternita.figlio, padre, madre
FROM paternita LEFT JOIN maternita
ON paternita.figlio = maternita.figlio
SELECT paternita.figlio, padre, madre
FROM paternita LEFT OUTER JOIN maternita
ON paternita.figlio = maternita.figlio
`outer` e' opzionale
18/07/2015
SQL, Definizione e interrogazione di
basi di dati
64
Outer join
SELECT paternita.figlio, padre, madre
FROM maternita JOIN paternita
ON maternita.figlio = paternita.figlio
SELECT paternita.figlio, padre, madre
FROM maternita LEFT OUTER JOIN paternita
ON maternita.figlio = paternita.figlio
SELECT paternita.figlio, padre, madre
FROM maternita FULL OUTER JOIN paternita
ON maternita.figlio = paternita.figlio
18/07/2015
SQL, Definizione e interrogazione di
basi di dati
65
Ordinamento del risultato
Nome e reddito delle persone con meno di
trenta anni in ordine alfabetico
SELECT nome, reddito
FROM persone
WHERE eta < 30
ORDER BY nome
18/07/2015
SQL, Definizione e interrogazione di
basi di dati
66
SELECT nome, reddito
FROM persone
WHERE eta < 30
SELECT nome, reddito
FROM persone
WHERE eta < 30
ORDER BY nome
Persone
Persone
Nome Reddito
Andrea
21
Aldo
15
Filippo
30
Nome Reddito
Aldo
15
Andrea
21
Filippo
30
18/07/2015
SQL, Definizione e interrogazione di
basi di dati
67
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
18/07/2015
SQL, Definizione e interrogazione di
basi di dati
68
Operatori aggregati: COUNT
•
•
COUNT restituisce il numero di righe o il numero
di valori distinti di un particolare attributo
Esempio: Il numero di figli di Franco:
•
SELECT count(*) as NumFigliDiFranco
FROM Paternita
WHERE Padre = 'Franco'
l’operatore aggregato (count) viene applicato al risultato
dell’interrogazione:
SELECT *
FROM Paternita
WHERE Padre = 'Franco'
18/07/2015
SQL, Definizione e interrogazione di
basi di dati
69
Paternità
Padre
Sergio
Luigi
Luigi
Franco
Franco
Figlio
Franco
Olga
Filippo
Andrea
Aldo
NumFigliDiFranco
2
18/07/2015
SQL, Definizione e interrogazione di
basi di dati
70
COUNT e valori nulli
Numero di tuple
SELECT count(*) FROM persone
Numero di volte il campo ‘reddito’ non e’ NULL
SELECT count(reddito) FROM persone
Numero di valori distinti del campo ‘reddito’ (senza i NULL)
SELECT count(distinct reddito) FROM persone
Persone
18/07/2015
Nome Età Reddito
Andrea 27
21
NULL
Aldo
25
Maria
55
21
Anna
50
35
SQL, Definizione e interrogazione di
basi di dati
71
Altri operatori aggregati
SUM, AVG, MAX, MIN
Media dei redditi di coloro che hanno meno di 30 anni:
SELECT avg(reddito)
FROM persone
WHERE eta < 30
Uso del JOIN: media dei redditi dei figli di Franco:
SELECT avg(reddito)
FROM persone JOIN paternita ON nome=figlio
WHERE padre='Franco‘
Uso di piu’ operatori di aggregamento nella target list:
SELECT avg(reddito), min(reddito), max(reddito)
FROM persone
WHERE eta < 30
18/07/2015
SQL, Definizione e interrogazione di
basi di dati
72
Operatori aggregati e valori nulli
SELECT avg(reddito) AS redditomedio
FROM persone
Persone
18/07/2015
Nome Età Reddito
Andrea 27
30
NULL
Aldo
25
Maria
55
36
Anna
50
36
SQL, Definizione e interrogazione di
basi di dati
73
Operatori aggregati e target list
un’interrogazione scorretta:
SELECT nome, max(reddito)
FROM persone
di chi sarebbe il nome? La target list deve essere
omogenea
SELECT min(eta), avg(reddito)
FROM persone
18/07/2015
SQL, Definizione e interrogazione di
basi di dati
74
Operatori aggregati e raggruppamenti
Le funzioni possono essere applicate a partizioni delle relazioni
Clausola GROUP BY
Syntax: GROUP BY listaAttributi
Il numero di figli di ciascun padre
SELECT padre, count(*) AS NumFigli
FROM paternita
GROUP BY Padre
paternita
18/07/2015
Padre
Sergio
Luigi
Luigi
Franco
Franco
Figlio
Franco
Olga
Filippo
Andrea
Aldo
SQL, Definizione e interrogazione di
basi di dati
Padre
Sergio
Luigi
Franco
NumFigli
1
2
2
75
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)
18/07/2015
SQL, Definizione e interrogazione di
basi di dati
76
Semantica degli operatori di
raggruppamento (1)
La query e’ innanzitutto eseguita senza operatori
aggregati e senza GROUP BY:
18/07/2015
SQL, Definizione e interrogazione di
basi di dati
77
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:
18/07/2015
SQL, Definizione e interrogazione di
basi di dati
78
Raggruppamenti e target list
scorretta
SELECT padre, avg(f.reddito), p.reddito
FROM persone f JOIN paternita ON figlio = nome
JOIN persone p ON padre =p.nome
GROUP BY padre
corretta
SELECT padre, avg(f.reddito)
FROM persone f JOIN paternita ON figlio = nome JOIN
persone p ON padre =p.nome
GROUP BY padre
18/07/2015
SQL, Definizione e interrogazione di
basi di dati
79
Condizioni sui gruppi, clausola
HAVING
Si possono applicare condizioni sul valore aggregato per
ogni gruppo
Esempio: I dipartimenti la cui somma dei salari e’
maggiore di 100
18/07/2015
SQL, Definizione e interrogazione di
basi di dati
80
WHERE o HAVING?
I padri i cui figli sotto i 30 anni hanno un reddito medio
maggiore di 25:
SELECT padre, avg(f.reddito)
FROM persone f JOIN paternita ON figlio = nome
WHERE eta < 30
GROUP BY padre
HAVING avg(f.reddito) > 25
18/07/2015
SQL, Definizione e interrogazione di
basi di dati
81
Sintassi, riassumiamo
SelectSQL ::=
select ListaAttributiOEspressioni
from ListaTabelle
[ where CondizioniSemplici ]
[ group by ListaAttributiDiRaggruppamento ]
[ having CondizioniAggregate ]
[ order by ListaAttributiDiOrdinamento ]
18/07/2015
SQL, Definizione e interrogazione di
basi di dati
82
Unione, intersezione e
differenza
La SELECT da sola non permette di fare unioni; serve
un costrutto esplicito:
SELECT …
UNION [all]
SELECT ...
i duplicati vengono eliminati (a meno che si usi all)
anche dalle proiezioni!
18/07/2015
SQL, Definizione e interrogazione di
basi di dati
83
Notazione posizionale!
SELECT padre
FROM paternita
UNION
SELECT madre
FROM maternita
quali nomi per gli attributi del risultato?
nessuno
quelli
del primo operando
…
18/07/2015
SQL, Definizione e interrogazione di
basi di dati
84
Sergio
Luigi
Luigi
Franco
Franco
Luisa
Luisa
Anna
Anna
Maria
Maria
18/07/2015
Figlio
Franco
Olga
Filippo
Andrea
Aldo
Maria
Luigi
Olga
Filippo
Andrea
Aldo
Padre
Sergio
Luigi
Luigi
Franco
Franco
Luisa
Luisa
Anna
Anna
Maria
Maria
SQL, Definizione e interrogazione di
basi di dati
Figlio
Franco
Olga
Filippo
Andrea
Aldo
Maria
Luigi
Olga
Filippo
Andrea
Aldo
85
Notazione posizionale, 2
SELECT padre, figlio
FROM paternita
UNION
SELECT figlio, madre
FROM maternita
18/07/2015
SELECT padre, figlio
FROM paternita
UNION
SELECT madre, figlio
FROM maternita
SQL, Definizione e interrogazione di
basi di dati
86
Notazione posizionale, 3
Anche con le ridenominazioni non cambia niente:
SELECT padre as genitore, figlio
FROM paternita
UNION
SELECT figlio, madre as genitore
FROM maternita
Corretta:
SELECT padre as genitore, figlio
FROM paternita
UNION
SELECT madre as genitore, figlio
FROM maternita
18/07/2015
SQL, Definizione e interrogazione di
basi di dati
87
Differenza
SELECT Nome
FROM Impiegato
EXCEPT
SELECT Cognome as Nome
FROM Impiegato
vedremo che si può esprimere con SELECT nidificate
18/07/2015
SQL, Definizione e interrogazione di
basi di dati
88
Intersezione
SELECT Nome
FROM Impiegato
INTERSECT
SELECT Cognome as Nome
FROM Impiegato
equivale a
SELECT I.Nome
FROM Impiegato I, Impiegato J
WHERE I.Nome = J.Cognome
18/07/2015
SQL, Definizione e interrogazione di
basi di dati
89
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
18/07/2015
EXISTS SelectQuery
Il predicato e’ vero se la SelectQuery restituisce almeno una tupla
SQL, Definizione e interrogazione di
basi di dati
90
Esempio di SELECT nidificate
nome e reddito del padre di Franco
SELECT Nome, Reddito
FROM Persone, Paternita
WHERE Nome = Padre AND Figlio = 'Franco'
SELECT Nome, Reddito
FROM Persone
WHERE Nome =
(
SELECT Padre
FROM Paternita
WHERE Figlio = 'Franco')
La query nella clausola WHERE e’ la query nidificata
18/07/2015
SQL, Definizione e interrogazione di
basi di dati
91
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
18/07/2015
SQL, Definizione e interrogazione di
basi di dati
92
Nome e reddito dei padri di persone che guadagnano più
di 20 milioni
SELECT distinct P.Nome, P.Reddito
FROM Persone P, Paternita, Persone F
WHERE P.Nome = Padre AND Figlio = F.Nome
AND F.Reddito > 20
SELECT Nome, Reddito
FROM Persone
WHERE Nome in (SELECT Padre
FROM Paternita
WHERE Figlio = any (SELECT Nome
FROM Persone
WHERE Reddito > 20))
18/07/2015
SQL, Definizione e interrogazione di
basi di dati
93
Nome e reddito dei padri di persone che guadagnano più
di 20 milioni
SELECT distinct P.Nome, P.Reddito
FROM Persone P, Paternita, Persone F
WHERE P.Nome = Padre AND Figlio = F.Nome
AND F.Reddito > 20
SELECT Nome, Reddito
FROM Persone
WHERE Nome in (SELECT Padre
FROM Paternita, Persone
WHERE Figlio = Nome
AND Reddito > 20)
18/07/2015
SQL, Definizione e interrogazione di
basi di dati
94
Negazione con le query nidificate
Trovare quei dipartimenti dove non c’e’ nessuno che si
chiama ‘Brown’:
Oppure:
18/07/2015
SQL, Definizione e interrogazione di
basi di dati
95
Operatori IN e NOT IN
IN e’ sinonimo di: =ANY
NOT IN e’ sinonimo di: <>ALL
18/07/2015
SQL, Definizione e interrogazione di
basi di dati
96
MAX e MIN con le query nidificate
Esempio: Il dipartimento(i) dove lavora colui
con lo stipendio piu’ alto di tutta l’azienda:
Oppure:
18/07/2015
SQL, Definizione e interrogazione di
basi di dati
97
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
18/07/2015
SQL, Definizione e interrogazione di
basi di dati
98
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.Reddito
FROM Persone P, Paternita, Persone F
WHERE P.Nome = Padre AND Figlio = F.Nome
AND F.Reddito > 20
SELECT Nome, Reddito, ????
FROM Persone
WHERE Nome in (SELECT Padre
FROM Paternita
WHERE Figlio = any (SELECT Nome
FROM Persone
WHERE Reddito > 20))
18/07/2015
SQL, Definizione e interrogazione di
basi di dati
99
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
18/07/2015
SQL, Definizione e interrogazione di
basi di dati
100
Quantificazione esistenziale
Ulteriore tipo di condizione
EXISTS ( Sottoespressione )
Le persone che hanno almeno un figlio
SELECT *
FROM Persone
WHERE EXISTS (
EXISTS (
18/07/2015
SELECT *
FROM Paternita
WHERE Padre = Nome) OR
SELECT *
FROM Maternita
WHERE Madre = Nome)
SQL, Definizione e interrogazione di
basi di dati
101
Quantificazione esistenziale, 2
I
padri i cui figli guadagnano tutti più di venti
milioni
SELECT distinct Padre
FROM Paternita Z
WHERE NOT EXISTS (
SELECT *
FROM Paternita W, Persone
WHERE W.Padre = Z.Padre
AND W.Figlio = Nome
AND Reddito <= 20)
18/07/2015
SQL, Definizione e interrogazione di
basi di dati
102
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)
18/07/2015
SQL, Definizione e interrogazione di
basi di dati
103
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)
18/07/2015
SQL, Definizione e interrogazione di
basi di dati
104
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)
18/07/2015
SQL, Definizione e interrogazione di
basi di dati
105
Visibilità
scorretta:
SELECT *
FROM Impiegato
WHERE 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
18/07/2015
SQL, Definizione e interrogazione di
basi di dati
106
Commenti finali sulle query
nidificate
Query nidificate possono essere “meno dichiarative” in
un certo senso ma spesso sono piu’ facilmente
interpretabili
L’utilizzo di variabili deve rispettare le regole di visibilita’
Suddivisibili in blocchi piu’ semplici da interpretare
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
18/07/2015
SQL, Definizione e interrogazione di
basi di dati
107