18-SQL Definizione e interrogazione 06 -...

1

SQL, Definizione e interrogazione di basi di dati

Capitolo 4: SQL (SQL (SlidesSlides da da AtzeniAtzeni, Ceri, , Ceri, Paraboschi, Paraboschi, TorloneeTorlonee, Basi di dati, Basi di datiMcGrawMcGraw--HillHill, 2002), 2002)

SQL, Definizione e interrogazione di basi di dati 204/01/2010

SQL

Structured Query Languageè un linguaggio con varie funzionalità:

contiene sia il DDL che il DMLne esistono varie versioni vediamo gli aspetti essenziali, non idettagli

2


Breve storia dell‘SQLSQL: Structured Query LanguageSQL sviluppato alla IBM nel 1973

Dal 1983 standard de factoPrimo 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)

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:

MySQL, PostgresEsistono sistemi commerciali che utilizzano interfacce tipo QBE (Query byExample): ACCESS

Tuttavia hanno sistemi per la traduzione automatica in SQL


Definizione dei dati in SQL

Istruzione CREATE TABLE:definisce uno schema di relazione e ne crea un’istanza vuotaspecifica attributi, domini e vincoli

3


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)

)


Domini

Domini elementari (predefiniti)Domini definiti dall'utente (semplici, ma riutilizzabili)

4


Domini elementari

Carattere: singoli caratteri o stringhe, anche di lunghezza variabile Bit: singoli booleani o stringheNumerici, esatti e approssimatiData, oraSistemi diversi estendono il set di base con domini non standard (vettori, periodi, ecc.)


Definizione di domini

Istruzione CREATE DOMAIN:definisce un dominio (semplice), utilizzabile indefinizioni di relazioni, anche con vincoli evalori di default

5


CREATE DOMAIN, esempio

CREATE DOMAIN Voto AS SMALLINT DEFAULT NULLCHECK ( value >=18 AND value <= 30 )


Vincoli intrarelazionali

NOT NULLUNIQUE definisce chiaviPRIMARY KEY: chiave primaria (una sola,implica NOT NULL)CHECK, vedremo più avanti

6


UNIQUE e PRIMARY KEY

due forme:nella definizione di un attributo, se forma da solo la chiavecome elemento separato



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


)

7


Matricola CHAR(6) PRIMARY KEY

Matricola CHAR(6),…,PRIMARY KEY (Matricola)

PRIMARY KEY, alternative



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


)

8


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!


Vincoli interrelazionali

CHECK, vedremo più avantiREFERENCES e FOREIGN KEY permettono didefinire vincoli di integrità referenzialedi nuovo due sintassi

per singoli attributisu più attributi

E' possibile definire politiche di reazione allaviolazione

9


Matricola398732959345

Vigili CognomeRossiNeriNeri

NomeLucaPieroMario

Mori Gino7543

InfrazioniCodice34321

733216452153524

Data1/2/954/3/955/4/965/2/98

Vigile3987329532959345

Prov NumeroMITOPRPR

39548KE39548839548839548


InfrazioniCodice34321

733216452153524

Data1/2/954/3/955/4/965/2/98

Vigile3987329532959345

Prov NumeroMITOPRPR

39548KE39548839548839548

Auto Prov NumeroMITOPR

39548KE39548839548

CognomeRossiRossiNeri

NomeMarioMarioLuca

10



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


Modifiche degli schemi

ALTER DOMAINALTER TABLEDROP DOMAINDROP TABLE...

11


Definizione degli indici

è rilevante dal punto di vista delle prestazionima è a livello fisico e non logicoin passato era importante perché in alcuni sistemi era l'unico mezzo per definire chiaviCREATE INDEX


SQL, operazioni sui dati

interrogazione: SELECT

modifica:INSERT, DELETE, UPDATE

12


Istruzione SELECT per l’interrogazione

SELECT ListaAttributiFROM ListaTabelle[ WHERE Condizione ]

"target list"clausola FROMclausola WHERE


Capacità del comando SELECT

SelezioneSelezione ProiezioneProiezione

TabellaTabella 11 TabellaTabella 22

TabellaTabella 11 TabellaTabella 11JoinJoin

13


SELECT

SELECT ListaAttributiFROM ListaTabelle[ WHERE Condizione ]La query1. considera il prodotto cartesiano tra le tabelle in ListaTabelle2. fra queste seleziona solo le righe che soddisfano la Condizione3. e infine valuta le espressioni specificate nella target list

ListaAttributiLa SELECT implementa gli operatori RidenominazioneProiezione, Selezione e Join dell’algebra relazionale

Piu’ altro che vedremo piu’ avanti


Convenzione scrittura comandi SQL

I comandi SQL non sono “case sensitive”Possono essere distribuiti in una o più righe terminate da un separatoreClausole diverse sono usualmente inserite in linee separatePer convenzione si usa scrivere i costrutti SQL in maiuscolo

SELECT Professore, Corsi.CorsoFROM Corsi, Esami, Studenti

WHERE Corsi.Corso = Esami.CorsoAND Esami.Matricola = Studenti.MatricolaAND Nome=‘Teo Verdi’AND Voto > 24

14


Nome EtàPersone

RedditoAndrea 27

Maria 55Anna 50

Filippo 26Luigi 50

Franco 60Olga 30

Sergio 85Luisa 75

Aldo 2521

4235304020413587

15

MadreMaternità FiglioLuisa

AnnaAnnaMariaMaria

LuisaMaria

OlgaFilippoAndrea

Aldo

Luigi

PadrePaternità Figlio

LuigiLuigi

FrancoFranco

SergioOlga

FilippoAndrea

Aldo

Franco

Database di esempio


Esempio query

Nome e reddito delle persone con meno di trenta anni

πNome, Reddito(σEta<30(Persone))

SELECT nome, redditoFROM personeWHERE eta < 30

30Filippo15Aldo21AndreaRedditoNome

15


Espressioni aritmetiche nella target list

Creare espressioni attraverso l’uso dei soliti operatori aritmetici: +, -, *, /Le priorita’ sono quelle standardPossono essere alterate con l’uso delle parentesi


Uso degli operatori AritmeticiSQL> SELECT ename, sal, sal+300

2 FROM emp;

ENAME SAL exp---------- --------- ---------KING 5000 5300BLAKE 2850 3150CLARK 2450 2750JONES 2975 3275MARTIN 1250 1550ALLEN 1600 1900...14 rows selected.

16


Precedenza operatori aritmeticiSQL> SELECT ename, sal, 12*sal+100

2 FROM emp;

ENAME SAL exp---------- --------- ----------KING 5000 60100BLAKE 2850 34300CLARK 2450 29500JONES 2975 35800MARTIN 1250 15100ALLEN 1600 19300...14 rows selected.


Uso delle parentesiSQL> SELECT ename, sal, 12*(sal+100)

2 FROM emp;

ENAME SAL exp---------- --------- -----------KING 5000 61200BLAKE 2850 35400CLARK 2450 30600JONES 2975 36900MARTIN 1250 16200...14 rows selected.

17


Alias delle colonne

Ridenominare il nome di una colonnaImplementa 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


Uso dell’AliasSQL> 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


Proiezione, senza selezioneNome e reddito di tutte le persone

πNome, Reddito(Persone)

SELECT nome, redditoFROM persone


Selezione, senza proiezione

Uso dello `*` nella target listNome, età e reddito delle persone con meno ditrenta anni

σEta<30(Persone)

SELECT *FROM personeWHERE eta < 30

19


Limitare le righe selezionate tramite WHERE

Corrisponde all’operatore (Restrizione) dell’algebra relazionale

La clausola WHERE segue la clausola FROME’ opzionale

SELECT [DISTINCT] {*| colonna [alias], ...}FROM tabella[WHERE condizione(i)];


Uso della clausola WHERESQL> SELECT ename, job, deptno

2 FROM emp3 WHERE job='CLERK';

ENAME JOB DEPTNO---------- --------- ---------JAMES CLERK 30SMITH CLERK 20ADAMS CLERK 20MILLER CLERK 10

20


Stringhe di caratteri e Date

Stringhe di caratteri e date vanno incluse tra apici.I caratteri sono case sensitive e le date sono format sensitiveSQL> SELECT ename, job, deptno

2 FROM emp3 WHERE ename = 'JAMES';


Condizione di WHERE

Condizione : :=Predicato |“(“Condizione”)” |NOT Condizione |Condizione (AND | OR) Condizione

PredicatoEspr op (Espr | “(“ Sottoselect “)” )op ∈ {=, <>, >, >=, <, <=}SottoSelect deve dare come risultato una tabella con un solo elemento o vuota (vedremo alcuni esempi)

21


Uso degli Operatori di Confronto

SQL> SELECT ename, sal, comm2 FROM emp3 WHERE sal<=comm;

ENAME SAL COMM---------- --------- ---------MARTIN 1250 1400


Altri Operatori di Confronto

Operatore

BETWEEN...AND...

IN(list)

LIKE

IS NULL

Significato

compreso tra due valori

Corrisp. ad uno dei valori nella lista

Operatore di pattern matching

Valore nullo

22


Uso dell’operatore BETWEEN

ENAME SAL---------- ---------MARTIN 1250TURNER 1500WARD 1250ADAMS 1100MILLER 1300

SQL> SELECT ename, sal2 FROM emp3 WHERE sal BETWEEN 1000 AND 1500;

Limiteinferiore

Limitesuperiore

BETWEEN consente la selezione di righe con attributi in un particolare range


Predicato BETWEEN

Espr1 [NOT] BETWEEN Espr2 AND Espr3

Equivale a [NOT] Espr2 ≤Espr1 AND Espr1≤Espr3

23


Uso dell’operatore IN E’ usato per selezionare righe che hanno un attributo che assume valori contenuti in una lista.

SQL> SELECT empno, ename, sal, mgr2 FROM emp3 WHERE mgr IN (7902, 7566, 7788);

EMPNO ENAME SAL MGR--------- ---------- --------- ---------

7902 FORD 3000 75667369 SMITH 800 79027788 SCOTT 3000 75667876 ADAMS 1100 7788


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 ename2 FROM emp3 WHERE ename LIKE 'S%';

24


Uso dell’operatore LIKEIl pattern-matching di caratteri può essere

combinato

I’identificatore ESCAPE (\) deve essere usato per cercare "%" o "_".

SQL> SELECT ename2 FROM emp3 WHERE ename LIKE '_A%';

ENAME----------MARTINJAMES WARD


Cognome Filiale EtàMatricola

Neri Milano 455998Rossi Roma 327309

Bruni Milano NULL9553

Impiegati

Neri Milano 455998Bruni Milano NULL9553

σ Età > 40 OR Età IS NULL (Impiegati)

Neri Milano 455998Bruni Milano NULL9553

Gestione dei valori nulli

25


Gli impiegati la cui età è o potrebbe essere maggiore di 40

σ Età > 40 OR Età IS NULL (Impiegati)

SELECT *FROM impiegatiWHERE eta > 40 OR eta is null


SQL e algebra relazionale

R1(A1,A2) R2(A3,A4)

SELECT R1.A1, R2.A4 FROM R1, R2WHERE R1.A2 = R2.A3

prodotto cartesiano (FROM)selezione (WHERE)proiezione (SELECT)

26


SQL e algebra relazionale

R1(A1,A2) R2(A3,A4)

SELECT R1.A1, R2.A4 FROM R1, R2WHERE R1.A2 = R2.A3

πA1,A4 (σA2=A3 (R1 JOIN R2))


possono essere necessarie ridenominazioni

nel prodotto cartesianonella target list

SELECT X.A1 AS B1, ...FROM R1 X, R2 Y, R1 ZWHERE X.A2 = Y.A3 AND ...

27


SELECT X.A1 AS B1, Y.A4 AS B2FROM R1 X, R2 Y, R1 ZWHERE X.A2 = Y.A3 AND Y.A4 = Z.A1

δ B1,B2←A1,A4 (π A1,A4 (σ A2 = A3 AND A4 = C1(

R1 JOIN R2 JOIN δ C1,C2 ← A1,A2 (R1))))

Self JOIN su R1


Cognome Filiale StipendioMatricola

Neri Milano 645998Neri Napoli 557309

Rossi Roma 645698Rossi Roma 449553

cognome e filiale di tutti gli impiegati

π Cognome, Filiale (Impiegati)

28


Cognome Filiale

Neri MilanoNeri Napoli

Rossi Roma

Cognome Filiale

Neri MilanoNeri Napoli

Rossi RomaRossi Roma

SELECTcognome, filiale

FROM impiegati

SELECT distinctcognome, filiale

FROM impiegati


Nome Età

Persone

Reddito

Andrea 27

Maria 55Anna 50

Filippo 26Luigi 50

Franco 60Olga 30

Sergio 85Luisa 75

Aldo 2521

4235304020413587

15

MadreMaternità FiglioLuisa

AnnaAnnaMariaMaria

LuisaMaria

OlgaFilippoAndrea

Aldo

Luigi


LuigiLuigi

FrancoFranco

SergioOlga

FilippoAndrea

Aldo

Franco

29


Selezione, proiezione e joinI padri di persone che guadagnano più di 20

πPadre(paternita JOIN Figlio=Nome (σReddito>20 (persone)))SELECT distinct padreFROM persone, paternitaWHERE figlio = nome AND

reddito > 20


Join naturale

Padre e madre di ogni persona

paternita JOIN maternita

SELECT paternita.figlio,padre, madreFROM maternita, paternitaWHERE paternita.figlio = maternita.figlio

30


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.redditoFROM persone p, paternita, persone fWHERE p.nome = padre AND

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

Necessita’ di ridenominazione


SELECT, con ridenominazionedel risultato

SELECT figlio, f.reddito AS reddito, p.reddito AS redditoPadre

FROM persone p, paternita, persone fWHERE p.nome = padre AND figlio = f.nome

AND f.reddito > p.reddito

SELECT x.figlio, f.reddito AS reddito, p.reddito AS redditoPadre

FROM persone p, paternita x, persone fWHERE p.nome = x.padre AND x.figlio = f.nome

AND f.reddito > p.reddito

O meglio

31


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, madreFROM maternita, paternitaWHERE paternita.figlio = maternita.figlio

SELECT madre, paternita.figlio, padre FROM maternita JOIN paternita ON

paternita.figlio = maternita.figlio


Le persone che guadagnano più dei rispettivi padri; mostrare nome,reddito e reddito del padre

SELECT f.nome, f.reddito, p.redditoFROM persone p, paternita, persone fWHERE p.nome = padre AND

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

SELECT f.nome, f.reddito, p.redditoFROM persone p JOIN paternita ON p.nome = padre

JOIN persone f ON figlio = f.nomeWHERE f.reddito > p.reddito

32


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


Join esterno: "outer join"

Padre e, se nota, madre di ogni persona

SELECT paternita.figlio, padre, madreFROM paternita LEFT JOIN maternita

ON paternita.figlio = maternita.figlio

SELECT paternita.figlio, padre, madreFROM paternita LEFT OUTER JOIN maternita

ON paternita.figlio = maternita.figlio

`outer` e' opzionale

33


Outer joinSELECT paternita.figlio, padre, madreFROM maternita JOIN paternita

ON maternita.figlio = paternita.figlio

SELECT paternita.figlio, padre, madreFROM maternita LEFT OUTER JOIN paternita


SELECT paternita.figlio, padre, madreFROM maternita FULL OUTER JOIN paternita



Ordinamento del risultatoNome e reddito delle persone con meno ditrenta anni in ordine alfabetico

SELECT nome, redditoFROM personeWHERE eta < 30ORDER BY nome

34


PersoneNome Reddito

Andrea 21Aldo 15

Filippo 30

PersoneNome Reddito

Andrea 21Aldo 15

Filippo 30

SELECT nome, redditoFROM personeWHERE eta < 30

SELECT nome, redditoFROM personeWHERE eta < 30ORDER BY nome


Operatori aggregati

Nelle espressioni della target list possiamoavere anche espressioni che calcolano valori a partire da insiemi di ennupleSQL-2 prevede 5 possibili operatori di aggregamento:

conteggio, minimo, massimo, media, sommaGli operatori di aggregamento NON sono rappresentabili in Algebra Relazionale

35


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 PaternitaWHERE Padre = 'Franco'

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

SELECT * FROM PaternitaWHERE Padre = 'Franco'



LuigiLuigi

SergioOlga

FilippoFrancoFranco

AndreaAldo

Franco

FrancoFranco

AndreaAldo

NumFigliDiFranco2

36


COUNT e valori nulliNumero di tuple

SELECT count(*) FROM personeNumero di volte il campo ‘reddito’ non e’ NULL

SELECT count(reddito) FROM personeNumero di valori distinti del campo ‘reddito’ (senza i NULL)

SELECT count(distinct reddito) FROM persone

Nome EtàPersone RedditoAndrea 27

Maria 55Anna 50

Aldo 2521

2135

NULL


Altri operatori aggregati

SUM, AVG, MAX, MINMedia 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

37


Operatori aggregati e valori nulli

SELECT avg(reddito) AS redditomedioFROM persone

Nome EtàPersone RedditoAndrea 27

Maria 55Anna 50

Aldo 2530

3636

NULL


Operatori aggregati e target listun’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

38


Le funzioni possono essere applicate a partizioni delle relazioniClausola GROUP BY

Syntax: GROUP BY listaAttributiIl numero di figli di ciascun padreSELECT padre, count(*) AS NumFigliFROM paternitaGROUP BY Padre

Operatori aggregati e raggruppamenti

Padrepaternita Figlio

LuigiLuigi

SergioOlga

FilippoFrancoFranco

AndreaAldo

Franco Padre NumFigli

LuigiSergio

2Franco 2

1


Esempio GROUP BY

Per ogni dipartimento, restituire la somma degli stipendidi tutti gli impiegati (di quel dipartimento)Relazione: Employee(Name,Dept,Office,Salary,City)

39


Semantica degli operatori di raggruppamento (1)

La query e’ innanzitutto eseguita senza operatori aggregati e senza GROUP BY:


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:

40


Raggruppamenti e target listscorretta

SELECT padre, avg(f.reddito), p.redditoFROM persone f JOIN paternita ON figlio = nome

JOIN persone p ON padre =p.nomeGROUP BY padre

correttaSELECT padre, avg(f.reddito)

FROM persone f JOIN paternita ON figlio = nome JOINpersone p ON padre =p.nome

GROUP BY padre


Condizioni sui gruppi, clausola HAVING

Si possono applicare condizioni sul valore aggregato per ogni gruppoEsempio: I dipartimenti la cui somma dei salari e’maggiore di 100

41


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 = nomeWHERE eta < 30 GROUP BY padre HAVING avg(f.reddito) > 25


Sintassi, riassumiamo

SelectSQL ::= select ListaAttributiOEspressionifrom ListaTabelle[ where CondizioniSemplici ][ group by ListaAttributiDiRaggruppamento ][ having CondizioniAggregate ][ order by ListaAttributiDiOrdinamento ]

42


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!


Notazione posizionale!SELECT padre FROM paternitaUNIONSELECT madreFROM maternita

quali nomi per gli attributi del risultato?nessuno quelli del primo operando…

43


Luisa

AnnaAnnaMariaMaria

LuisaMaria

OlgaFilippoAndrea

Aldo

Luigi

Figlio

LuigiLuigi

FrancoFranco

SergioOlga

FilippoAndrea

Aldo

Franco

Luisa

AnnaAnnaMariaMaria

LuisaMaria

OlgaFilippoAndrea

Aldo

Luigi

Padre Figlio

LuigiLuigi

FrancoFranco

SergioOlga

FilippoAndrea

Aldo

Franco


Notazione posizionale, 2

SELECT padre, figlioFROM paternitaUNIONSELECT figlio, madreFROM maternita

SELECT padre, figlioFROM paternitaUNIONSELECT madre, figlioFROM maternita

44


Notazione posizionale, 3Anche con le ridenominazioni non cambia niente:

SELECT padre as genitore, figlioFROM paternitaUNIONSELECT figlio, madre as genitoreFROM maternita

Corretta:SELECT padre as genitore, figlioFROM paternitaUNIONSELECT madre as genitore, figlio FROM maternita


Differenza

SELECT NomeFROM ImpiegatoEXCEPTSELECT Cognome as NomeFROM Impiegato

vedremo che si può esprimere con SELECT nidificate

45


IntersezioneSELECT NomeFROM ImpiegatoINTERSECTSELECT Cognome as NomeFROM Impiegato

equivale a

SELECT I.NomeFROM Impiegato I, Impiegato JWHERE I.Nome = J.Cognome


Interrogazioni nidificate

le condizioni in SQL permettono anche il confronto fra un attributo e il risultato di una sottoquery

Syntax:Scalare Operatore (ANY | ALL) SelectQueryANY: il predicato e’ vero se almeno uno dei valori restituiti da Querysoddisfano la condizioneALL: il predicato e’ vero se tutti i valori restituiti dalla Querysoddisfano la condizione

quantificatore esistenzialeEXISTS SelectQueryIl predicato e’ vero se la SelectQuery restituisce almeno una tupla

46


Esempio di SELECT nidificatenome e reddito del padre di Franco

SELECT Nome, RedditoFROM Persone, PaternitaWHERE Nome = Padre AND Figlio = 'Franco'

SELECT Nome, RedditoFROM PersoneWHERE Nome = ( SELECT Padre

FROM PaternitaWHERE Figlio = 'Franco')

La query nella clausola WHERE e’ la query nidificata


Interrogazioni nidificate, commenti

La forma nidificata è “meno dichiarativa”, ma talvolta più leggibile (richiede meno variabili)La forma piana e quella nidificata possono essere combinateLe sottointerrogazioni non possono contenere operatori insiemistici (“l’unione si fa solo al livello esterno”); la limitazione non è significativa

47


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

SELECT distinct P.Nome, P.RedditoFROM Persone P, Paternita, Persone FWHERE P.Nome = Padre AND Figlio = F.Nome

AND F.Reddito > 20

SELECT Nome, RedditoFROM PersoneWHERE Nome in (SELECT Padre

FROM PaternitaWHERE Figlio = any (SELECT Nome

FROM PersoneWHERE Reddito > 20))


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

SELECT distinct P.Nome, P.RedditoFROM Persone P, Paternita, Persone FWHERE P.Nome = Padre AND Figlio = F.Nome

AND F.Reddito > 20

SELECT Nome, RedditoFROM PersoneWHERE Nome in (SELECT Padre

FROM Paternita, Persone WHERE Figlio = NomeAND Reddito > 20)

48


Negazione con le query nidificate

Trovare quei dipartimenti dove non c’e’ nessuno che sichiama ‘Brown’:

Oppure:


Operatori IN e NOT IN

IN e’ sinonimo di: =ANY

NOT IN e’ sinonimo di: <>ALL

49


MAX e MIN con le query nidificate

Esempio: Il dipartimento(i) dove lavora coluicon lo stipendio piu’ alto di tutta l’azienda:

Oppure:


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à è limitatanon si possono includere nella target list attributi di relazioni nei blocchi interni

50


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.RedditoFROM Persone P, Paternita, Persone FWHERE P.Nome = Padre AND Figlio = F.Nome

AND F.Reddito > 20

SELECT Nome, Reddito, ????FROM PersoneWHERE Nome in (SELECT Padre

FROM PaternitaWHERE Figlio = any (SELECT Nome

FROM PersoneWHERE Reddito > 20))


Interrogazioni nidificate, commenti, 3regole di visibilità:

non è possibile fare riferimenti a variabili definite in blocchi più internise 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

51


Quantificazione esistenziale

Ulteriore tipo di condizioneEXISTS ( Sottoespressione )

Le persone che hanno almeno un figlio

SELECT *FROM PersoneWHERE EXISTS ( SELECT *

FROM PaternitaWHERE Padre = Nome) OR

EXISTS ( SELECT *FROM MaternitaWHERE Madre = Nome)


I padri i cui figli guadagnano tutti più di venti milioni

SELECT distinct PadreFROM Paternita ZWHERE NOT EXISTS (

SELECT *FROM Paternita W, PersoneWHERE W.Padre = Z.Padre

AND W.Figlio = Nome AND Reddito <= 20)

Quantificazione esistenziale, 2

52


Semantica delle espressioni “correlate”

La query piu’ interna puo’ usare variabili della queryesternaL’interrogazione interna viene eseguita una volta per ciascuna ennupla dell’interrogazione esternaEsempio, trovare tutti gli studenti che hanno un omonimo:

SELECT *FROM Student SWHERE EXISTS (SELECT *

FROM Student S2WHERE S2.Nome = S.NomeAND S2.Cognome = S.CognomeAND S2.Matricola <> S.Matricola)


Semantica delle espressioni “correlate”, 2

Esempio, trovare tutti gli studenti che NON hanno un omonimo:

SELECT *FROM Student SWHERE NOT EXISTS (SELECT *

FROM Student S2WHERE S2.Nome = S.NomeAND S2.Cognome = S.CognomeAND S2.Matricola <> S.Matricola)

53


Confronto su piu’ attributiIl confronto con il risultato di una query nidificata puo’essere basato su piu’ attributiStessa query di prima, trovare tutti gli studenti che hanno un omonimo:

SELECT *FROM Student SWHERE (Nome, Cognome) IN (SELECT Nome, Cognome

FROM Student S2WHERE S2.Matricola <> S.Matricola)


Visibilità

scorretta:SELECT *FROM ImpiegatoWHERE Dipart in (SELECT Nome

FROM Dipartimento D1WHERE Nome = 'Produzione') OR

Dipart in (SELECT NomeFROM Dipartimento D2WHERE D2.Citta = D1.Citta)

D1 non e’ visibile nella seconda query nidificata in quanto le due sottoquery sono allo stesso livello

54


Commenti finali sulle query nidificate

Query nidificate possono essere “meno dichiarative” in un certo senso ma spesso sono piu’ facilmenteinterpretabili

Suddivisibili in blocchi piu’ semplici da interpretareL’utilizzo di variabili deve rispettare le regole di visibilita’

Cioe’, una variabile puo’ essere usata solo all’interno dellostesso blocco e in un blocco piu’ interno

Comunque, query nidificate complesse possono esseredi difficile comprensione

Soprattutto quando si usano molte variabili comuni tra blocchidiversi

18-SQL Definizione e interrogazione 06 -...

Documents

Transcript of 18-SQL Definizione e interrogazione 06 -...