1

Structured Query Language - Structured Query Language - SQL un linguaggio di SQL un linguaggio di

definizione e manipolazione definizione e manipolazione dei datidei dati

Esercitazioni del Corso di Sistemi Informativi

Marina Mongiello

2

SQL come standardSQL come standard

Varie versioni con successivi miglioramenti:• SQL-1 o SQL-89• SQL-2 o SQL-92 (entry, intermediate e full)• SQL-3?

3

La struttura base della query La struttura base della query L’interrogazione è specificata in maniera dichiarativa: si specifica non il modo in cui l’interrogazione deve essere eseguita, ma le caratteristiche del risultato che deve fornire.

Query semplici:Select AttrExpr [[as] Alias]{, AttrExpr [[as] Alias]}From TableName [[as] Alias]{, TableName [[as] Alias]}[Where condition]

Le tre parti componenti vengono chiamate target list, from clause e where clause

4

Query SQL in ACCESS:Query SQL in ACCESS:

In applicazioni sviluppate con ACCESS, le query possono essere create• mediante la griglia di struttura grafica QBE

(query by example)• mediante la specifica in SQL

5

6

Esempi di query in SQLEsempi di query in SQL

Di seguito riportiamo alcuni esempi di query in linguaggio SQL facendo riferimento al database di esempio di cui riportiamo il diagramma delle relazioni

7

8

Esempio 1 Esempio 1 Query di Selezione:Query di Selezione:

Si vuole determinare la data di nascita e l’indirizzo degli impiegati il cui nome è John Smith

SELECT Data_di_nascita, Via, Numero_civico,CAP,CittàFROM IMPIEGATOWHERE Nome = ‘John’ AND Cognome = ‘Smith’

Cfr. La query Esempio 1 su azienda2002.mdb

9

Esempio 2Esempio 2

Si vuole un elenco completo dei nomi e degli indirizzi degli impiegati che lavorano nel dipartimento “Research”

SELECT Nome, Cognome, Via, Numero_civico, CAP,CittàFROM IMPIEGATO, DIPARTIMENTOWHERE Nome_dipartimento = ‘Research’ AND

Identificativo_dipartimento = Id_Dipartimento

Cfr. La query Esempio 2 su azienda2002.mdb

10

Esempio 3Esempio 3Per tutti i progetti con sede a ‘Stafford’, si richiede l’identificativo del progetto e del dipartimento che lo conduce, nonché il nome e la data di nascita del direttore di tale dipartimento.

SELECT Identificativo_progetto, Identificativo_dipartimento, Cognome, Data_di_nascitaFROM PROGETTO, DIPARTIMENTO, IMPIEGATOWHERE Id_dipartimento_coord =Identificativo_dipartimento

AND CF_Direttore = Codice_Fiscale AND Sede =‘Stafford’

Cfr. La query Esempio 3 su azienda2002.mdb

11

Ambiguità dei nomi degli attributiSe in più relazioni sono presenti attributi con lo stesso nome, bisogna specificare il nome della tabella prima dell’attributo che vogliamo estrarre.

ESEMPIO:

Supponiamo che nel DB azienda2002.mdb sia la relazione IMPIEGATO che la relazione DIPARTIMENTO abbiano gli attributi Nome ed Id_Dipartimento.

La query Esempio 2 diventa:

SELECT IMPIEGATO.Nome, Cognome, Via, Numero_civico, CAP,CittàFROM IMPIEGATO, DIPARTIMENTOWHERE DIPARTIMENTO.Nome = ‘Research’ AND DIPARTIMENTO.ID_Dipartimento = IMPIEGATO.Id_Dipartimento

12

ALIASINGALIASINGPer facilitare la manipolazione si possono dichiarare dei nomi alternativi per gli attributi e per le relazioni mediante il comando AS(che può anche essere omesso dove indicato dalle parentesi graffe negli esempi).

ESEMPI:La query della slide precedente può essere anche scritta in uno dei seguenti modi:

1) SELECT I.Nome, Cognome, Via, Numero_civico, CAP,Città FROM IMPIEGATO {AS} I, DIPARTIMENTO {AS} D WHERE D.Nome = ‘Research’ AND D.Id_Dipartimento = I.Id_Dipartimento

2) SELECT IMPIEGATO.Nome, Cognome, Via, Numero_civico, CAP,Città, DIPARTIMENTO.ID_Dipartimento AS ID

FROM IMPIEGATO, DIPARTIMENTO WHERE DIPARTIMENTO.Nome = ‘Research’ AND

ID = IMPIEGATO.Id_Dipartimento

13

Esempio 4Esempio 4

Per ogni impiegato, ricercare il nome ed il cognome del supervisore.

SELECT I.Nome, I.Cognome, S.Nome, S.CognomeFROM IMPIEGATO AS I, IMPIEGATO AS SWHERE I.CF_Superiore=S.Codice_Fiscale

Cfr. La query Esempio 4 su azienda2002.mdb

14

Query senza where clauseQuery senza where clauseNon specificano alcuna condizione di selezione

ESEMPI:• Selezionare i Codici Fiscali di tutti gli impiegati SELECT Codice_Fiscale FROM IMPIEGATO

• Cercare tutte le combinazioni dei codici fiscali degli impiegati con gli identificativi dei dipartimenti SELECT Codice_Fiscale, Identificativo_Dipartimento FROM IMPIEGATO, DIPARTIMENTO

15

Operatore *Operatore *Seleziona tutti gli attributi delle relazioni specificate nella FROM CLAUSE senza elencarli singolarmente.

Esempio 5Esempio 5Si vuole un elenco completo di tutti gli attributi degli impiegati che lavorano nel dipartimento “Research”

SELECT *FROM IMPIEGATO, DIPARTIMENTOWHERE Nome_dipartimento = ‘Research’ AND

Identificativo_dipartimento = Id_Dipartimento

Cfr. La query Esempio 5 su azienda2002.mdb

16

La parola chiave DISTINCTLa parola chiave DISTINCTIn genere SQL non tratta le tabelle come insiemi , quindi può capitare che in una relazione o nel risultato di una query compaiano più di una volta delle tuple duplicate, sia perché è costoso eliminarle, sia perché talvolta l’utente può essere interessato alla visualizzazione delle tuple duplicate. Affinché il risultato di una query sia a sua volta una relazione(insieme di tuple distinguibili) bisogna usare la parola chiave DISTINCT nella operazione SELECT.

ESEMPIO:SELECT DISTINCT Stipendio_annuoFROM IMPIEGATO

17

Operazioni su insiemi predefinite in SQLOperazioni su insiemi predefinite in SQL

Unione (UNION)Differenza(EXCEPT)Intersezione(INTERSECT)

In SQL2

Queste operazioni restituiscono relazioni, cioè insiemi di tuple distinguibili. Per ottenere anche le tuple duplicate si aggiunge all’operatore la parola chiave ALL (es. UNION ALL)

18

Esempio 6Esempio 6Elencare tutti gli identificativi dei progetti in cui lavora un impiegato il cui cognome è “Smith”, in qualità di lavoratore semplice o di direttore del dipartimento che controlla il progetto.

(SELECT Identificativo_progettoFROM PROGETTO, DIPARTIMENTO, IMPIEGATOWHERE Id_dipartimento_coord=Identificativo_dipartimento

AND CF_Direttore=Codice_Fiscale AND Cognome=‘Smith’)

UNION(SELECT Identificativo_progettoFROM PROGETTO, LAVORA, IMPIEGATOWHERE Identificativo_progetto= Id_Progetto

AND CF_Impiegato = Codice_Fiscale AND Cognome =‘Smith’)

Cfr. La query Esempio 6 su azienda2002.mdb

19

Query nidificateQuery nidificate

L’argomento della clausola where puo’ essere a sua volta il risultato dell’esecuzione di una interrogazione(outer query).

E’ possibile anche usare gli operatori di confronto =,<,>,>=,<= per confrontare il risultato della outer query con valori di attributi.

20

Esempio 6-A Esempio 6-A La query dell’esempio 6 può essere formulata anche La query dell’esempio 6 può essere formulata anche come segue:come segue:SELECT DISTINCT Identificativo_progettoFROM PROGETTO WHERE Identificativo_progetto IN

(SELECT Identificativo_progetto FROM PROGETTO, DIPARTIMENTO, IMPIEGATO WHERE Id_dipartimento_coord=Identificativo_dipartimento AND CF_Direttore=Codice_Fiscale AND Cognome=‘Smith’)OR Identificativo_progetto IN (SELECT Identificativo_progetto FROM LAVORA, IMPIEGATOWHERE CF_Impiegato = Codice_Fiscale

AND Cognome =‘Smith’)

Cfr. La query Esempio 6A su azienda2002.mdb

21

Esempio 7 Esempio 7

Cercare gli impiegati che guadagnano più di tutti gli Cercare gli impiegati che guadagnano più di tutti gli impiegati del Dipartimento “5”impiegati del Dipartimento “5”

SELECT Nome, Cognome, Stipendio_annuoFROM IMPIEGATOWHERE Stipendio_annuo > ALL

(SELECT Stipendio_annuo FROM IMPIEGATO

WHERE Id_Dipartimento = 5)

Cfr. La query Esempio 7 su azienda2002.mdb

22

OPERATORE EXIST :OPERATORE EXIST :EsempiEsempi1)Cercare gli impiegati che non hanno familiari1)Cercare gli impiegati che non hanno familiari

SELECT Nome, CognomeFROM IMPIEGATOWHERE NOT EXISTS

(SELECT * FROM FAMILIARE

WHERE Codice_Fiscale=CF_Impiegato)

2)Cercare i direttori che hanno almeno un familiare2)Cercare i direttori che hanno almeno un familiareSELECT Nome, CognomeFROM IMPIEGATOWHERE EXISTS (SELECT *

FROM FAMILIARE WHERE

Codice_Fiscale=CF_Impiegato) AND EXISTS (SELECT * FROM DIPARTIMENTO

WHERE Codice_Fiscale=CF_Direttore)

23

INSIEMI ESPLICITI e VALORI NULLI :INSIEMI ESPLICITI e VALORI NULLI :EsempiEsempi

1)Cercare i codici fiscali degli impiegati che lavorano 1)Cercare i codici fiscali degli impiegati che lavorano sui progetti 1 , 2 o 3.sui progetti 1 , 2 o 3.

SELECT DISTINCT CF_ImpiegatoFROM LAVORAWHERE Id_Progetto IN (1,2,3)

2)Cercare tutti gli impiegati che non hanno un superiore2)Cercare tutti gli impiegati che non hanno un superiore

SELECT Nome, CognomeFROM IMPIEGATOWHERE CF_Superiore IS NULL

24

Operatori aggregatiOperatori aggregati

In SQL è possibile utilizzare operatori per valutare delle proprietà su insiemi di tuple

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

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

Corrispondono alla somma, massimo, minimo, media

Effettua un’operazione di conteggio

Distinct elimina i duplicatiAll trascura solo i valori nulli

25

Query con operatori aggregatiQuery con operatori aggregati

Esempio 8Esempio 8Determinare il totale degli stipendi di tutti gli impiegati e lo stipendio minimo, massimo e medio.

SELECT SUM(Stipendio_annuo), MAX(Stipendio_annuo), MIN(Stipendio_annuo), AVG(Stipendio_annuo)

FROM IMPIEGATO

Cfr. La query Esempio 8 su azienda2002.mdb

26

Esempio 9Esempio 9

Determinare il totale degli stipendi degli impiegati del Dipartimento “Research” e lo stipendio minimo, massimo e medio.

SELECT SUM(Stipendio_annuo), MAX(Stipendio_annuo), MIN(Stipendio_annuo), AVG(Stipendio_annuo)

FROM IMPIEGATO, DIPARTIMENTOWHERE Identificativo_dipartimento=Id_Dipartimento AND

Nome_dipartimento=‘Research’

Cfr. La query Esempio 9 su azienda2002.mdb

27

Esempio 10Esempio 10

Determinare il numero degli impiegati

SELECT COUNT(*)FROM IMPIEGATO

Cfr. La query Esempio 10 su azienda2002.mdb

28

Esempio 11Esempio 11

Determinare il numero degli impiegati del Dipartimento “Research”.

SELECT COUNT(*)FROM IMPIEGATO, DIPARTIMENTOWHERE Identificativo_dipartimento=Id_Dipartimento AND

Nome_dipartimento=‘Research’

Cfr. La query Esempio 11 su azienda2002.mdb

ESEMPIO 11 B: Conta gli stipendi diversi nell’aziendaSELECT COUNT (DISTINCT Stipendio_annuo)FROM IMPIEGATO

29

Esempio 12Esempio 12

Determinare gli impiegati che hanno almeno due familiari.

SELECT Nome, CognomeFROM IMPIEGATOWHERE (SELECT Count(*)

FROM FAMILIARE WHERE CF_Impiegato = Codice_Fiscale) >= 2

Cfr. La query Esempio 12 su azienda2002.mdb

30

Query con raggruppamentoQuery con raggruppamento

E’ possibile dividere la tabella risultante da una query con operatori aggregati in sottoinsiemi, raggruppando le righe che contengono gli stessi valori per un insieme di attributi e restituendole in ordine crescente(default) o descrescente(DESC).SELECT count(*) AS nomeAliasFROM nomeTabellagroup by nomeAttributo

Operatore di raggruppamento

31

Query con raggruppamentoQuery con raggruppamento

Esempio 13Esempio 13

SELECT Id_Dipartimento, COUNT(*), AVG(Stipendio_annuo)

FROM IMPIEGATOGROUP BY Id_Dipartimento

Per ogni dipartimento si vuole visualizzare l’identificativo, il numero degli impiegati ed il loro stipendio medio.

Cfr. La query Esempio 13 su azienda2002.mdb

32

Esempio 14Esempio 14

SELECT Identificativo_progetto, Nome_progetto, Count(*)

FROM PROGETTO, LAVORAWHERE Identificativo_progetto=Id_ProgettoGROUP BY Identificativo_progetto, Nome_progettoHAVING Count(*)>2

Per ogni progetto in cui lavorano più di 2 Per ogni progetto in cui lavorano più di 2 impiegati, ricercare l’identificativo di progetto, il impiegati, ricercare l’identificativo di progetto, il nome e il numero di impiegati coinvolti.nome e il numero di impiegati coinvolti.

Cfr. La query Esempio 14 su azienda2002.mdb

33

Esempio 15Esempio 15

SELECT Nome_dipartimento, Count(*) FROM DIPARTIMENTO, IMPIEGATOWHERE Identificativo_dipartimento=Id_Dipartimento

AND Stipendio_annuo>30000 AND Id_Dipartimento In

(SELECT Id_Dipartimento FROM IMPIEGATO GROUP BY Id_Dipartimento

HAVING COUNT(*)>2)GROUP BY Nome_dipartimentoORDER BY Nome_dipartimento DESC

Per ogni dipartimento in cui lavorano più di 2 Per ogni dipartimento in cui lavorano più di 2 impiegati, ricercare l’identificativo di impiegati, ricercare l’identificativo di dipartimento e il numero di impiegati che dipartimento e il numero di impiegati che guadagnano più di 30,000 $.guadagnano più di 30,000 $.

Cfr. La query Esempio 15 su azienda2002.mdb

34

Tipi di JoinTipi di Join Join interno(default): sono incluse nel risultato le tuple di una tabella che hanno

una tupla corrispondente secondo la condizione di join nell’altra tabella. La condizione di JOIN in questo caso è sempre di uguaglianza (EQUI JOIN).

Join naturale:la condizione di join è implicita e rappresentata dall’uguaglianza di ogni coppia di attributi che hanno lo stesso nome in entrambe le tabelle(l’uguaglianza dei nomi si può imporre anche con l’aliasing)

Join esterno sinistro (rispettivamente destro): include tutti i record della tabella di sinistra (rispettivamente

di destra) anche se non esistono valori corrispondenti ai record nella tabella di destra (rispettivamente di sinistra)

35

E’ possibile determinare il tipo di join dalle proprietà del join

36

Esempio 16Esempio 16Query di Selezione: JOIN INTERNOQuery di Selezione: JOIN INTERNOSi vuole un elenco completo dei nomi e degli indirizzi degli impiegati che lavorano nel dipartimento “Research” (analogo esempio 2)

SELECT Nome, Cognome, Data_di_nascitaFROM (DIPARTIMENTO JOIN IMPIEGATO ON Identificativo_dipartimento = Id_Dipartimento)WHERE Nome_dipartimento=‘Research’

Cfr. La query Esempio 16 su azienda2002.mdb

La parola chiave INNER prima di JOIN è sottintesa.

37

JOIN NATURALE :ESEMPIOJOIN NATURALE :ESEMPIO

Supponiamo che nel DB azienda2002.mdb sia la relazione IMPIEGATO che la relazione DIPARTIMENTO abbiano l’attributo Id_Dipartimento. La query precedente può essere scritta come segue:

SELECT Nome, Cognome, Data_di_nascitaFROM (DIPARTIMENTO NATURAL JOIN

IMPIEGATO)WHERE Nome_dipartimento=‘Research’

La condizione IMPIEGATO.Id_Dipartimento=DIPARTIMENTO.Id_Dipartimento è sottintesa dalla parola NATURAL

38

Esempio:JOIN NATURALE con aliasingEsempio:JOIN NATURALE con aliasing

SELECT Nome, Cognome, Data_di_nascitaFROM (IMPIEGATO NATURAL JOIN

(DIPARTIMENTO AS DIP(Id_Dipartimento, Nome_dipartimento, CF_Direttore, Inizio_direzione)))

WHERE Nome_dipartimento=‘Research’

Gli attributi vengono rinominati con l’aliasing per poter effettuare il JOIN NATURALE delle tabelle. La query 16 è ancora equivalente alla seguente.

Ora Id_Dipartimento ha lo stesso nome in entrambe le tabelle e la condizione di join verrà implicitamente applicata ad esso.

39

Esempio 17Esempio 17Query di Selezione: JOIN ESTERNOQuery di Selezione: JOIN ESTERNO

SELECT I.Cognome AS Cognome_Impiegato, S.Cognome AS Cognome_SuperioreFROM (IMPIEGATO I LEFT OUTER JOIN

IMPIEGATO S ON I.CF_Superiore=S.Codice_Fiscale)

Cfr. La query Esempio 17 su azienda2002.mdb

Per ogni impiegato, ricercare il cognome del supervisore.

L’INNER Join non avrebbe restituito gli impiegati per i quali non è specificato un superiore. La parola chiave OUTER può essere omessa con l’uso di LEFT e RIGHT.

40

Esempio 18Esempio 18Query di Selezione: JOIN ANNIDATIQuery di Selezione: JOIN ANNIDATI

SELECT Identificativo_progetto, Identificativo_dipartimento, Cognome, Data_di_nascita

FROM (( PROGETTO JOIN DIPARTIMENTO ON Id_dipartimento_coord =Identificativo_dipartimento) JOIN IMPIEGATO ON CF_Direttore = Codice_Fiscale)

WHERE Sede =‘Stafford’Cfr. La query Esempio 18 su azienda2002.mdb

Per tutti i progetti con sede a ‘Stafford’, si richiede l’identificativo del progetto e del dipartimento che lo conduce, nonché il nome e la data di nascita del direttore di tale dipartimento.

41

JOIN NEL DATABASE

Molte query precedenti possono essere riscritte utilizzando l’operazione di JOIN.Nel DB azienda2002.mdb ci sono le versioni con l’uso del JOIN di alcune delle query 1-15, sotto il nome “Esempio X join”.

42

Query di inserimentoQuery di inserimento

Possono essere inserite singole righe all’interno delle tabelle:insert into Nometabella [Lista Attributi]

<values (ListadiValori SelctSQL)>Oppure insiemi di righe come risultato di

una precedente selezione

43

Esempio 19:Esempio 19:Inserimento di righeInserimento di righe

INSERT INTO IMPIEGATO( Nome, Cognome, Codice_Fiscale )VALUES (‘Richard’, ‘Johnson’, ‘1012345643569876’)

Gli attributi non elencati assumono valore NULL nelle tupla inserita.

Cfr. La query Esempio 19 su azienda2002.mdb

44

Esempio 20 : Query di creazione tabellaEsempio 20 : Query di creazione tabella

CREATE TABLE INFO_DIP (Nome_Dip VARCHAR(15), Numero_Impiegati INTEGER, Totale_Stipendi INTEGER)

Viene creata una tabella temporanea, che può essere manipolata (interrogata, cancellata,…) come una normale tabella. Nella tabella creata vengono inseriti dati con una query successiva(i.e. Esempio 21). La tabella risultante è però una vista sul database, ovvero una query materializzata, che non viene aggiornata automaticamente quando le tabelle da cui è ricavata vengono aggiornate.

Cfr. La query Esempio 20 su azienda2002.mdb

45

Esempio 21: Query di inserimento risultatiEsempio 21: Query di inserimento risultati

INSERT INTO INFO_DIP (Nome_Dip, Numero_Impiegati, Totale_Stipendi)

SELECT Nome_dipartimento, Count(*), Sum(Stipendio_annuo)

FROM DIPARTIMENTO, IMPIEGATOWHERE Identificativo_dipartimento = Id_DipartimentoGROUP BY Nome_dipartimento

Nella tabella creata con la query precedente si inseriscono i dati risultanti da un’altra query.

Cfr. La query Esempio 21 su azienda2002.mdb

ACCESS mette a disposizione uno specifico tipo di query( query di creazione tabella) per ottenere il risultato dell’esecuzione successiva delle 2 query precedenti: Cfr. La query Esempio 20-21 su azienda2002.mdb

46

Aggiornamento di righeAggiornamento di righe

E’ possibile aggiornare una o più righe mediante il comando update che presenta la seguente sintassi:update NomeTabella set Attributo = <Espressione |

SelectSQL |null|default>{,Attributo =<Espressione |SelectSQL |null|default)} [where Condizione]

47

Esempio 22: Aggiornamento di righeEsempio 22: Aggiornamento di righe

UPDATE PROGETTO SET Sede = ‘Bellaire’,

Id_ dipartimento_coord = ‘5’WHERE Identificativo_progetto)= ’10’

Il progetto numero 10 è stato spostato a ‘Bellaire’ ed è ora coordinato dal dipartimento numero 5. Aggiornare il database.

Cfr. La query Esempio 22 su azienda2002.mdb

48

Esempio 23: Aggiornamento di righeEsempio 23: Aggiornamento di righe

UPDATE IMPIEGATO SET Stipendio_annuo = 1.1* Stipendio_annuoWHERE Id_Dipartimento IN

(SELECT Identificativo_dipartimento FROM DIPARTIMENTO

WHERE Nome_dipartimento =‘Research’)

Il Dipartimento Research ha aumentato gli stipendi del 10%. Aggiornare il database.

Cfr. La query Esempio 23 su azienda2002.mdb

49

Cancellazione di righeCancellazione di righe

La cancellazione di righe dalle tabella è effettuata dal comando delete, la cui sintassi è la seguente:delete from NomeTabella [where condizione]

50

Esempio 24Esempio 24Cancellazione di righeCancellazione di righe

DELETE FROM IMPIEGATOWHERE Codice_Fiscale = ‘1234567891011121’

Cancellare l’impiegato dal codice fiscale ‘1234567891011121’.

Cfr. La query Esempio 24 su azienda2002.mdb