Corso Di Basi Di Dati 02 S Q L D M L

1

A.A. 2005/2006 Basi di Dati e Laboratorio 1

Corso di Basi di Dati e Laboratorio

LINGUAGGIO SQLINTERROGAZIONI

Prof. Silvana Castano


SQL

• Dichiarativo (non procedurale) Descrizione dell’obiettivo dell’operazione.

• Interprete SQL del DBMS che analizza l’istruzione e formula una interrogazione equivalente nel linguaggio procedurale interno (nascosto all’utente).

• Incorporabile in un programma in linguaggio ospite

Compilazione di interrogazioni.


SQL

• Traduzione ed ottimizzazione query optimizer.

• Diretto ad una vasta gamma di utenti :– utenti che interagiscono con l’interprete.– programmatori di applicazioni.

2


ISTRUZIONE SELECT

SELECT Lista AttributiFROM Lista Tabelle[WHERE Condizione]

• SELECT: nomi di attributo degli attributi target i cui valori devono essere reperiti con la query

• FROM: nomi di relazioni su cui la query deve essere valutata

• WHERE: espressione (booleana) di ricerca che identifica le ennuple che dovranno far parte del risultato della query


ESEMPIOImpiegato

Prod4620RameshAhmad80

Adm 405BorgJames70

Prod737EnglishJoyce60

HQ8030SmithRamesh50

HQ4515WallaceJennifer40

Adm 7010ZelayaAlicia30

Prod3620WongFranklin20

Adm 4520SmithJohn10

DipNStipendioUfficioCognomeNomeID


ESEMPIODipartimento

60975, Berry, Bellaire, TXProd

NULL980, Dallas, Huston, TXR&D

50980, Dallas, Huston, TXHQ

30731 Fondre, Huston, TXAdm

DirettoreIndirizzoNome

3


CLAUSOLA WHERE

• Argomento: espressioni booleane costruite combinando predicati semplici mediante operatori logici AND, OR, NOT

• Uso di operatori di confronto (=, <>, <, >, <=, >=) fra espressione a sx costruita a partire dai valori degli attributi per la riga e un valore costante o un’altra espressione a dx

• AND: selezione delle righe che rendono veri tutti i predicati• OR: selezione delle righe per cui almeno un predicato è

vero• NOT: unario, inverte il valore di verità del predicato


CLAUSOLA WHERE

Altri operatori in WHERE• [ not ] BETWEEN a AND b

• [ not ] IN (lista valori)

• IS [ not ] NULL

• [ not ] LIKE ‘pattern’

• [NOT] EXISTS


ESEMPI

Reperire ufficio e stipendio dell’ impiegato (o degli impegati) il cui nome è ‘John Smith’

SELECT Ufficio, StipendioFROM ImpiegatoWHERE Cognome = ‘Smith’ AND Nome = ‘John’

4520

StipendioUfficio

Π Ufficio, Stipendio(σ Cognome=‘Smith’AND Nome=‘John’(Impiegato))

4


[NOT] LIKE pattern

Nella specifica del pattern:• _ rappresenta un carattere arbitrario• % rappresenta una stringa di lunghezza arbitraria (anche 0 caratteri)

PATTERN MATCHING DI STRINGHE


ESEMPIO

Reperire tutti i dati dei dipartimenti il cui indirizzo è a Houston in Texas

SELECT *FROM DipartimentoWHERE Indirizzo LIKE ‘%Houston%TX%’

NULL980, Dallas, Houston, TX

Ricerca

50980, Dallas, Houston, TX

Sede

30731 Fondre, Houston, TX

Amm.ne

DirettoreIndirizzoNome


RIDENOMINAZIONE ATTRIBUTI

Per gli impiegati del dipartimento Amministrazione, mostrare nome, cognome e stipendio risultante applicando un aumento del 10%

SELECT Nome, Cognome, Stipendio*1.1 AS NuovoStipendio

FROM ImpiegatoWHERE DipN=‘Adm’

44BorgJames

77ZelayaAlicia

49,5SmithJohn

NuovoStipendioCognomeNome

5


JOIN

Reperire nomi, cognomi e indirizzo di lavoro degli impiegati

• Dati su Nome e Cognome degli impiegati si trovano nella tabella Impiegato.

• L’indirizzo di lavoro si trova nella tabella Dipartimento.


ESEMPIO

• Le tuple di Impiegato sono collegate alle tuple di Dipartimento mediante chiave esterna (attributo DipN)

• Per ricostruire l’informazione cercata, ogni tupla di impegato va concatenata con la tupla del corrispondentedipartimento

• La condizione da imporre è l’uguaglianza dei valori di chiave esterna di Dipartimento e corrispondente chiave primaria di Impiegato (equijoin)

SELECT Impiegato.Nome, Impiegato.Cognome,Dipartimento.Indirizzo

FROM Impiegato, DipartimentoWHERE Impiegato.DipN=Dipartimento.Nome


JOIN

• Tutte le tabelle coinvolte nel JOIN sono esplicitate nella clausola FROM

• Sul prodotto cartesiano di tali tabelle si applicano le condizioni specificate in WHERE

• Le condizioni del JOIN sono scritte in modo esplicito nella WHERE (uso dei valori di chiave esterna per collegare tuple di tabelle diverse e combinare i loro dati nel risultato)

6


INTERPRETAZIONE ALGEBRICA

SELECT Impiegato.Nome,Cognome,Indirizzo


Π Impiegato.Nome,Cognome, Indirizzo

(σDipN=Dipartimento.Nome(Impiegato X Dipartimento))


ASSENZA CLAUSOLA WHERE

SELECT *FROM Impiegato, Dipartimento

Questa interrogazione specifica il prodotto cartesiano delle due relazioni


JOIN

Reperire nomi, cognomi e indirizzo di lavoro degli impiegati dell’amministrazione



AND Dipartimento.Nome=‘Adm’

Condizione di joinCondizione di selezione

7


USO DI ALIAS

Trovare cognome, nome e ufficio degli impiegati che lavorano nello stesso ufficio di impiegati di nome Smith

Al fine di stabilire se un impiegato appartiene o meno al risultato occorre poter confrontare tuple di Impiegato con se stesse

Uso di alias di relazione


USO DI ALIAS

Trovare cognome, nome e ufficio degli impiegati che lavorano nello stesso ufficio di impiegati di nome Smith

SELECT Imp.Cognome, Imp.Nome, Imp.Ufficio FROM Impiegato AS Imp, Impiegato AS SmithWHERE Imp.Ufficio = Smith.Ufficio

AND Smith.Cognome = ‘Smith’ AND Imp.ID <> Smith.ID

Alias di relazione:• Imp rappresenta gli impiegati• Smith rappresenta gli impiegati di nome Smith


VALORI NULLI

• Introduzione di condizioni atomiche per verificare se un valore è specificato oppure è nullo

• Predicato is null: selezione delle righe con valori nulli

Attributo is [not] null • Il predicato risulta vero se l’attributo ha valore

nullo (is not null è la sua negazione)

8


ELIMINAZIONE DUPLICATI

Possibile presenza di righe uguali all’interno di una tabella risultato di una interrogazione.

Eliminazione dei duplicati a carico dell’utente:

SELECT DISTINCT



SELECT IndirizzoFROM DipartimentoWHERE Indirizzo LIKE ‘%Houston%TX%’

Reperire gli indirizzi dei dipartimenti di Houston in Texas



731 Fondre, Houston, TXIndirizzo



SELECT DISTINCT IndirizzoFROM DipartimentoWHERE Indirizzo LIKE ‘%Houston%TX%’

Reperire gli indirizzi dei dipartimenti di Houston in Texas (senza duplicati)


731 Fondre, Houston, TXIndirizzo

9


ORDINAMENTO DEL RISULTATO

• Ordinamento delle ennuple risultato secondo uno o più attributi specificati

ORDER BY Attributo [ASC | DESC] {, Attributo [ASC | DESC]}

(default ASC)


SELECT *FROM ImpiegatoORDER BY Cognome, Stipendio DESC

ESEMPIO

Adm4075BorgJames70

Prod3620WongFranklin20

HQ4516WallaceJennifer40

Adm7020ZelayaAlicia30

Adm4510SmithJohn10

HQ8014SmithRamesh50

Prod4620RameshAhmad80

Prod737EnglishJoyce60

DipNStipendioUfficioCognomeNomeID


JOIN INTERNI ED ESTERNI

• Sintassi per la specifica di join introdotta in SQL-2 che permette di distinguere le condizioni di join dalle altre condizioni

• Le condizioni di join appaiono nella clausola FROM associate alle tabelle che partecipano al join

• Tipi di join• (INNER) JOIN (interno, default coincide con il

theta-join)• NATURAL JOIN (join naturale)

• RIGHT, LEFT, FULL OUTER JOIN (join esterni)

10


INNER JOIN

SELECT Impiegato.Nome, Impiegato.CognomeDipartimento.Indirizzo

FROM Impiegato, DipartimentoWHERE Impiegato.DipN = Dipartimento.Nome

SELECT Impiegato.Nome, Impiegato.CognomeDipartimento.Indirizzo

FROM Impiegato INNER JOIN Dipartimento ONImpiegato.DipN = Dipartimento.Nome


JOIN

Reperire nomi, cognomi e indirizzo di lavoro degli impiegati dell’amministrazione


FROM Impiegato INNER JOIN Dipartimento ONImpiegato.DipN = Dipartimento.Nome

WHERE Dipartimento.Nome=‘Adm’


NATURAL JOIN

• Nell’operazione NATURAL JOIN di due relazioni non viene specificata alcuna condizione di join.

• Viene applicata una condizione implicita di equijoin per ciascuna coppia di attributi con lo stesso nome nelle due relazioni.

• Ogni coppia di attributi di questo tipo è inclusa una sola volta nel risultato

• Se i nomi degli attributi non sono gli stessi nelle due relazioni, si possono ridenominare per farli corrispondere e poi applicare NATURAL JOIN

11


JOIN ESTERNI

• JOIN ESTERNO (OUTER JOIN)Esegue un join mantenendo nel risultato tutte le righe che fanno parte di una o di entrambe le relazioni coinvolte

• LEFT (OUTER) JOINFornisce come risultato il join interno esteso con le tuple della relazione che compare a sinistra del join per le quali non esiste una corrispondente tupla nella relazione di destra


RIGHT (OUTER) JOINFornisce come risultato il join interno esteso con le tuple della relazione che compare a destra del join per le quali non esiste una corrispondente tuplanella relazione di sinistra

FULL (OUTER) JOINFornisce come risultato il join interno esteso con le tuple di ciascuna relazione per le quali non esiste una corrispondente tupla dall’altra parte

JOIN ESTERNI


ESEMPIO

SELECT D.Nome,Indirizzo, I.Nome AS NomeDir, I.Cognome AS CognomeDir,

FROM Dipartimento AS D LEFT JOIN Impiegato AS I OND.Direttore = I.ID

NULL

Joyce

Ramesh

Alicia

NomeDir

English975, Berry, Bellaire, TXProd

NULL980, Dallas, Huston, TXR&D

Smith980, Dallas, Huston, TXHQ

Zelaya731 Fondre, Huston, TXAdm

CognomeDirIndirizzoD.Nome

12


OPERATORI AGGREGATI

• SQL mette a disposizione un insieme di operatori aggregati per derivare valori aggregati a partire da insiemi di tuple di relazioni.

Ad esempio, il numero totale di impiegati del dipartimento Produzione non è una proprietà di una singola tupla ma può essere calcolato attraverso il conteggio del nr. di tuple di Impiegato relative agli impiegati di produzione


OPERATORI AGGREGATI

• Principali operatori aggregati built-in dell’SQL• COUNT • SUM • MAX • MIN • AVG

• Prima si esegue l’interrogazione considerando le clausole FROM e WHERE della query

• L’operatore aggregato viene applicato al risultato ottenuto


OPERATORI AGGREGATI

• COUNT(*): restituisce il numero di righe di una relazione

• COUNT ALL ListaAttributi: restituisce il numero di righe della relazione che possiedono valori diversi da NULL per gli attributi specificati

• COUNT DISTINCT ListaAttributi: restituisce il numero di valori diversi all’interno della relazione per gli attributi specificati

13


OPERATORI AGGREGATI

• Restituisce il nr. di valori diversi dell’attributo Stipendio nella tabella Impiegato

SELECT COUNT ( DISTINCT Stipendio )FROM Impiegato

• Restituisce il Nr. di impiegati del dipartimento Produzione

SELECT COUNT (*)FROM ImpiegatoWHERE DipN = ‘Prod’


OPERATORI AGGREGATI

Determinare la somma degli stipendi del dipartimento Amministrazione

SELECT SUM (Stipendio)FROM ImpiegatoWHERE DipN = ‘Adm’

Determinare il massimo, il minimo e la media degli stipendi degli impiegati

SELECT MAX(Stipendio), MIN(Stipendio), AVG(Stipendio) FROM Impiegato


OPERATORI AGGREGATI

Determinare lo stipendio massimo tra quelli degli impiegati che lavorano in un dipartimento di Houston in Texas

SELECT MAX ( Stipendio )FROM Impiegato, DipartimentoWHERE Impiegato.DipN = Dipartimento.Nome

AND Indirizzo LIKE ‘%Houston%TX%’

14


RAGGRUPPAMENTI

• Necessità di applicare operatori aggregati a sottogruppi di tuple di una relazione in base al valore di uno o più attributi

• Clausola GROUP BY ListaAttributi per eseguire il raggruppamento

• Prima si effettua il raggruppamento e poi si applica l’operatore aggregato a ciascun sottogruppo individuato


Determinare, per ciascun dipartimento, il numero di impiegati e la somma dei loro stipendi.

SELECT DipN, COUNT(*) AS TOTImpiegati, SUM (Stipendio) AS TOTStipendio

FROM ImpiegatoGROUP BY DipN

RAGGRUPPAMENTI

2

3

3

TOTImpiegati

125

155

155

TOTStipendioAdm

HQ

Prod

DipN


RAGGRUPPAMENTI

• Esigenza di applicare gli operatori aggregati solo su raggruppamenti che verificano a condizioni date

• Clausola HAVING che specifica una condizionesu un gruppo di tuple associata al valore degli attributi di raggruppamento

• Solo i raggruppamenti che soddisfano la condizione specificata nella clausola HAVING sono inclusi nel risultato dell’interrogazione

15


Determinare i dipartimenti che spendono più di 130 KE in stipendi.

SELECT DipN, SUM (Stipendio)FROM ImpiegatoGROUP BY DipNHAVING SUM (Stipendio) > 130

PREDICATI SUI RAGGRUPPAMENTI


PREDICATI SUI RAGGRUPPAMENTI

Per i dipartimenti in cui lavorano almeno 2 impiegati con stipendio superiore a 40 KE, mostrare il nome del dipartimento e il nr. totale di impiegati.

SELECT DipN, COUNT(*)FROM ImpiegatoWHERE Stipendio > 40GROUP BY DipNHAVING COUNT(*) >= 2


INTERROGAZIONI DI TIPO INSIEMISTICO

• Le operazioni insiemistiche UNION, INTERSECT, EXCEPT sono state incorporate direttamente in SQL

• Default: eliminazione dei duplicati

• ALL: mantenimento dei duplicati nel risultato

16


• UNION: arricchisce la potenza espressiva di SQL e permette di scrivere interrogazioni altrimenti non formulabili.

• Interrogazioni con INTERSECT e EXCEPTpossono essere espresse utilizzando altri costrutti del linguaggio (v. interrogazioni nidificate).




Determinare l’insieme dei nomi e dei cognomi degli impiegati

SELECT NomeFROM Impiegato

UNIONSELECT CognomeFROM Impiegato

UNION ALL preserva i duplicati (quindi la stringa RameshAppare 2 volte in quanto sia nome che cognome)


Trovare i cognomi degli impiegati che sono anche nomi.

SELECT NomeFROM Impiegato

INTERSECTSELECT CognomeFROM Impiegato

Nome

Ramesh


17


Trovare i codici degli impiegati che non sono direttori

SELECT IDFROM Impiegato

EXCEPTSELECT DirettoreFROM Dipartimento



INTERROGAZIONI NIDIFICATE

Argomento della WHERE è un predicato in cui si confronta un valore (risultato di una espressione valutata su una singola riga) con il risultato dell’esecuzione di una query SQL interna

• Operatori ALL, ANYPer estendere i normali operatori di confronto (=,<>,>,<,>=,<=) al problema di confrontare un singolo valore con l’insieme dei valori risultato della query interna.


SELECT Impiegato.Nome, Impiegato.CognomeFROM Impiegato, DipartimentoWHERE Impiegato.DipN = Dipartimento.Nome

AND Indirizzo LIKE ‘%Houston%TX%’

Trovare i nomi e cognomi degli impiegati che lavorano a Houston in Texas


18



SELECT Nome, CognomeFROM ImpiegatoWHERE DipN = ANY (SELECT Nome

FROM DipartimentoWHERE Indirizzo LIKE

‘%Houston%TX%’ )

N.B. Operatori IN e NOT IN per testare l’appartenenza ad e l’esclusione da un insieme (identici a ‘= ANY’ e ‘<> ALL’)

La stessa interrogazione si può esprimere in forma nidificata


Trovare nomi e cognomi degli impiegati che lavorano nello stesso ufficio di qualche impiegato del dipartimento Produzione

SELECT Nome, CognomeFROM ImpiegatoWHERE Ufficio IN ( SELECT Ufficio

FROM ImpiegatoWHERE DipN = ‘Prod’)



SELECT DISTINCT Imp.Nome, Imp.CognomeFROM Impiegato AS Imp, Impiegato AS ProdWHERE Imp.Ufficio = Prod.Ufficio

AND Prod.DipN = ‘Prod’

Si può esprimere anche attraverso le variabili.


19


Trovare i dipartimenti dove non lavorano persone di nome Smith

SELECT NomeFROM DipartimentoWHERE Nome NOT IN ( SELECT DipN

FROM ImpiegatoWHERE Cognome = ‘Smith’)



Non si può esprimere mediante join; uso dell’operatore insiemistico differenza.

SELECT NomeFROM Dipartimento

EXCEPTSELECT DipNFROM ImpiegatoWHERE Cognome = ‘Smith’

In algebra relazionale:

Π Nome(Dipartimento) -

Π DipN (σCognome=‘Smith’(Impiegato))



Interrogazioni con MIN e MAX possono essere espresse mediante query nidificate.

Trovare i dipartimenti con gli stipendi più elevati

SELECT DipN AS NomeDipartimentoFROM ImpiegatoWHERE Stipendio = ( SELECT MAX ( Stipendio )

FROM Impiegato)

SELECT DipN AS NomeDipartimentoFROM ImpiegatoWHERE Stipendio >= ALL ( SELECT DISTINCT Stipendio

FROM Impiegato)


20


SUBQUERY CORRELATE

• Negli esempi visti ogni subquery viene eseguita una sola volta l’ insieme dei valori risultante è usato per la valutazione della clausola WHEREdella query esterna.

• E’ possibile definire subquery che sono eseguite ripetutamente per ogni tupla candidataconsiderata nella valutazione della query esterna.


SUBQUERY CORRELATE

Determinare gli impiegati che guadagnano più dello stipendio medio del proprio dipartimento

• E’ necessaria una query esterna per la selezione degli impiegati dalla relazione Impiegati in base ad un predicato su stipendio; tale query avrà la forma:

SELECT Nome, CognomeFROM ImpiegatoWHERE Stipendio > (media degli stipendi nel

dipartimento dell’impiegato candidato)


La subquery dovrà calcolare la media degli stipendi del dipartimento relativo a ogni tupla candidata della relazione Impiegati; tale subquery avrà la forma:

SELECT AVG(Stipendio)FROM ImpiegatoWHERE DipN = (valore di DipN nella tupla

dell’impiegato candidato)

SUBQUERY CORRELATE

21


Ogni volta che la query esterna considera una tupla candidata, viene invocata la subquery e viene ‘passato’ il nome del dipartimento dell’impiegato in esame

La subquery calcola quindi la media degli stipendi nel dipartimento che e’ stato passato e restituisce tale valore alla query esterna

La query esterna può quindi confrontare lo stipendio dell’impiegato in esame con il valore restituito dalla subquery

SUBQUERY CORRELATE


Questo tipo di query è chiamato correlato, perché ogni esecuzione della subquery è correlata al valore di uno o più attributi delle tuple candidate nella query esterna

Per poter riferire le colonne delle tuple candidate nella query esterna si fa uso degli alias di relazione

L’alias di relazione è definito nella query esterna e riferito nella query interna

SUBQUERY CORRELATE


Determinare gli impiegati che guadagnano più dello stipendio medio del proprio dipartimento con ordinamento del risultato per dipartimento.

SELECT Cognome, Nome, Stipendio, DipNFROM Impiegato XWHERE Stipendio > ( SELECT AVG(Stipendio)

FROM ImpiegatoWHERE X.DipN=DipN)

ORDER BY DipN

SUBQUERY CORRELATE

22


Il predicato EXISTS(sq) restituisce il valore booleano TRUE se la subquery restituisce almeno un tupla; restituisce il valore booleano FALSE altrimenti

Il predicato NOT EXISTS(sq) restituisce il valore booleano TRUE se la subquery non restituisce alcuna tupla; restituisce il valore booleano FALSE altrimenti

SUBQUERY CORRELATE - EXISTS


Trovare gli impiegati che hanno omonimi

SELECT ID, Cognome, NomeFROM Impiegato I1WHERE EXISTS (SELECT *

FROM Impiegato I2WHERE I1.Nome = I2.Nome

ANDI1.Cognome = I2.CognomeANDI1.ID <> I2.ID)



DIVISIONE

• Le subquery correlate e l’operatore di NOT EXISTS sono particolarmente utili per implementare l’operazione di divisione in SQL

• Consideriamo le relazioni:Impiegato(ID, Nome, Cognome)Progetto(Prog#, Pnome, Budget)Partecipazione(ID, Prog#)

• Determinare i codici degli impiegati che partecipano a tutti i progetti con un budget maggiore di 50000

Partecipazione ÷ (ΠProg#σBudget>50000(Progetto)))

23


La specifica della divisione in SQL fa uso dell’operatore NOT EXISTS e richiede di ragionare in base al concetto dicontroesempio.

Un impiegato I verifica l’interrogazione se non esiste un progetto che ha un budget maggiore di 50000, ed a cui l’impiegato I non partecipa.

DIVISIONE - ESEMPIO


SELECT IDFROM Impiegato IWHERE NOT EXISTS

(SELECT *FROM Progetto YWHERE Budget > 50000

ANDNOT EXISTS

(SELECT *FROM Partecipazione PWHERE I.ID = P.ID

ANDY.Prog# = P.Prog#))

DIVISIONE - ESEMPIO

Corso Di Basi Di Dati 02 S Q L D M L

Business

Transcript of Corso Di Basi Di Dati 02 S Q L D M L