esempio

Structured Query Language SQL un linguaggio di
definizione e manipolazione
dei dati
Esercitazioni del Corso di
Sistemi Informativi
Marina Mongiello
1
SQL come standard

Varie versioni con successivi
miglioramenti:
• SQL-1 o SQL-89
• SQL-2 o SQL-92 (entry, intermediate e full)
• SQL-3?
2
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
3
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
4
5
Esempi 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
6
7
Esempio 1
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 IMPIEGATO
WHERE Nome = ‘John’ AND Cognome = ‘Smith’
Cfr. La query Esempio 1 su azienda2002.mdb
8
Esempio 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, DIPARTIMENTO
WHERE Nome_dipartimento = ‘Research’ AND
Identificativo_dipartimento = Id_Dipartimento
Cfr. La query Esempio 2 su azienda2002.mdb
9
Esempio 3
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.
SELECT Identificativo_progetto, Identificativo_dipartimento,
Cognome, Data_di_nascita
FROM
PROGETTO, DIPARTIMENTO, IMPIEGATO
WHERE Id_dipartimento_coord =Identificativo_dipartimento
AND CF_Direttore = Codice_Fiscale AND
Sede =‘Stafford’
Cfr. La query Esempio 3 su azienda2002.mdb
10
Ambiguità dei nomi degli attributi
Se 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, DIPARTIMENTO
WHERE DIPARTIMENTO.Nome = ‘Research’ AND
DIPARTIMENTO.ID_Dipartimento = IMPIEGATO.Id_Dipartimento
11
ALIASING
Per 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
12
Esempio 4
Per ogni impiegato, ricercare il nome ed il
cognome del supervisore.
SELECT I.Nome, I.Cognome, S.Nome, S.Cognome
FROM IMPIEGATO AS I, IMPIEGATO AS S
WHERE I.CF_Superiore=S.Codice_Fiscale
Cfr. La query Esempio 4 su azienda2002.mdb
13
Query senza where clause
Non 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
14
Operatore *
Seleziona tutti gli attributi delle relazioni specificate nella
FROM CLAUSE senza elencarli singolarmente.
Esempio 5
Si vuole un elenco completo di tutti gli attributi degli
impiegati che lavorano nel dipartimento “Research”
SELECT *
FROM
IMPIEGATO, DIPARTIMENTO
WHERE Nome_dipartimento = ‘Research’ AND
Identificativo_dipartimento = Id_Dipartimento
Cfr. La query Esempio 5 su azienda2002.mdb
15
La parola chiave DISTINCT
In 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_annuo
FROM IMPIEGATO
16
Operazioni 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)
17
Esempio 6
Elencare 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_progetto
FROM PROGETTO, DIPARTIMENTO, IMPIEGATO
WHERE Id_dipartimento_coord=Identificativo_dipartimento
AND CF_Direttore=Codice_Fiscale
AND Cognome=‘Smith’)
UNION
(SELECT Identificativo_progetto
FROM
PROGETTO, LAVORA, IMPIEGATO
WHERE Identificativo_progetto= Id_Progetto
AND CF_Impiegato = Codice_Fiscale
AND Cognome =‘Smith’)
18
Cfr. La query Esempio 6 su azienda2002.mdb
Query 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.

19
Esempio 6-A
La query dell’esempio 6 può essere formulata anche
come segue:
SELECT DISTINCT Identificativo_progetto
FROM 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, IMPIEGATO
WHERE CF_Impiegato = Codice_Fiscale
AND Cognome =‘Smith’)
20
Cfr. La query Esempio 6A su azienda2002.mdb
Esempio 7
Cercare gli impiegati che guadagnano più di tutti gli
impiegati del Dipartimento “5”
SELECT Nome, Cognome, Stipendio_annuo
FROM
IMPIEGATO
WHERE Stipendio_annuo > ALL
(SELECT Stipendio_annuo
FROM
IMPIEGATO
WHERE Id_Dipartimento = 5)
Cfr. La query Esempio 7 su azienda2002.mdb
21
OPERATORE EXIST :Esempi
1)Cercare gli impiegati che non hanno familiari
SELECT Nome, Cognome
FROM IMPIEGATO
WHERE NOT EXISTS
(SELECT *
FROM FAMILIARE
WHERE Codice_Fiscale=CF_Impiegato)
2)Cercare i direttori che hanno almeno un familiare
SELECT Nome, Cognome
FROM IMPIEGATO
WHERE EXISTS (SELECT *
FROM FAMILIARE
WHERE Codice_Fiscale=CF_Impiegato)
AND EXISTS (SELECT *
FROM DIPARTIMENTO
WHERE Codice_Fiscale=CF_Direttore)
22
INSIEMI ESPLICITI e VALORI NULLI :
Esempi
1)Cercare i codici fiscali degli impiegati che lavorano
sui progetti 1 , 2 o 3.
SELECT DISTINCT CF_Impiegato
FROM
LAVORA
WHERE Id_Progetto IN (1,2,3)
2)Cercare tutti gli impiegati che non hanno un superiore
SELECT Nome, Cognome
FROM
IMPIEGATO
WHERE CF_Superiore IS NULL
23
Operatori aggregati

In SQL è possibile utilizzare operatori per
valutare delle proprietà su insiemi di tuple
Effettua un’operazione
di conteggio
•
•
count ( < * | [ distinct | all ] ListaAttributi>)
< sum | max | min | avg > ( [ distinct | all ] ListaAttributi )
Corrispondono alla somma,
massimo, minimo, media
Distinct elimina i duplicati
All trascura solo i valori nulli
24
Query con operatori aggregati
Esempio 8
Determinare 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
25
Esempio 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, DIPARTIMENTO
WHERE Identificativo_dipartimento=Id_Dipartimento AND
Nome_dipartimento=‘Research’
Cfr. La query Esempio 9 su azienda2002.mdb
26
Esempio 10
Determinare il numero degli impiegati
SELECT COUNT(*)
FROM
IMPIEGATO
Cfr. La query Esempio 10 su azienda2002.mdb
27
Esempio 11
Determinare il numero degli impiegati del
Dipartimento “Research”.
SELECT COUNT(*)
FROM
IMPIEGATO, DIPARTIMENTO
WHERE Identificativo_dipartimento=Id_Dipartimento AND
Nome_dipartimento=‘Research’
Cfr. La query Esempio 11 su azienda2002.mdb
ESEMPIO 11 B: Conta gli stipendi diversi nell’azienda
SELECT COUNT (DISTINCT Stipendio_annuo)
FROM
IMPIEGATO
28
Esempio 12
Determinare gli impiegati che hanno almeno due
familiari.
SELECT Nome, Cognome
FROM IMPIEGATO
WHERE (SELECT Count(*)
FROM FAMILIARE
WHERE CF_Impiegato = Codice_Fiscale) >= 2
Cfr. La query Esempio 12 su azienda2002.mdb
29
Query 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 nomeAlias
FROM nomeTabella
group by nomeAttributo
Operatore di raggruppamento
30
Query con raggruppamento
Esempio 13
Per ogni dipartimento si vuole visualizzare
l’identificativo, il numero degli impiegati ed
il loro stipendio medio.
SELECT
Id_Dipartimento, COUNT(*),
AVG(Stipendio_annuo)
FROM
IMPIEGATO
GROUP BY Id_Dipartimento
Cfr. La query Esempio 13 su azienda2002.mdb
31
Esempio 14
Per ogni progetto in cui lavorano più di 2
impiegati, ricercare l’identificativo di progetto, il
nome e il numero di impiegati coinvolti.
SELECT
Identificativo_progetto, Nome_progetto,
Count(*)
FROM
PROGETTO, LAVORA
WHERE
Identificativo_progetto=Id_Progetto
GROUP BY Identificativo_progetto, Nome_progetto
HAVING
Count(*)>2
Cfr. La query Esempio 14 su azienda2002.mdb
32
Esempio 15
Per ogni dipartimento in cui lavorano più di 2
impiegati, ricercare l’identificativo di
dipartimento e il numero di impiegati che
guadagnano più di 30,000 $.
SELECT
FROM
WHERE
Nome_dipartimento, Count(*)
DIPARTIMENTO, IMPIEGATO
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_dipartimento
ORDER BY Nome_dipartimento DESC
Cfr. La query Esempio 15 su azienda2002.mdb
33
Tipi 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)
34
E’ possibile determinare il tipo di join
dalle proprietà del join
35
Esempio 16
Query di Selezione: JOIN INTERNO
Si vuole un elenco completo dei nomi e degli
indirizzi degli impiegati che lavorano nel
dipartimento “Research” (analogo esempio 2)
SELECT Nome, Cognome, Data_di_nascita
FROM (DIPARTIMENTO JOIN IMPIEGATO ON
Identificativo_dipartimento = Id_Dipartimento)
WHERE Nome_dipartimento=‘Research’
La parola chiave INNER prima di JOIN è sottintesa.
Cfr. La query Esempio 16 su azienda2002.mdb
36
JOIN 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_nascita
FROM (DIPARTIMENTO NATURAL JOIN
IMPIEGATO)
WHERE Nome_dipartimento=‘Research’
La condizione
IMPIEGATO.Id_Dipartimento=DIPARTIMENTO.Id_Dipartimento
è sottintesa dalla parola NATURAL
37
Esempio:JOIN NATURALE con aliasing
Gli attributi vengono rinominati con l’aliasing per poter
effettuare il JOIN NATURALE delle tabelle. La query
16 è ancora equivalente alla seguente.
SELECT Nome, Cognome, Data_di_nascita
FROM (IMPIEGATO NATURAL JOIN
(DIPARTIMENTO AS
DIP(Id_Dipartimento, Nome_dipartimento,
CF_Direttore, Inizio_direzione)))
WHERE Nome_dipartimento=‘Research’
Ora Id_Dipartimento ha lo stesso nome in entrambe le tabelle e la
condizione di join verrà implicitamente applicata ad esso.
38
Esempio 17
Query di Selezione: JOIN ESTERNO
Per ogni impiegato, ricercare il cognome del
supervisore.
SELECT I.Cognome AS Cognome_Impiegato,
S.Cognome AS Cognome_Superiore
FROM (IMPIEGATO I LEFT OUTER JOIN
IMPIEGATO S
ON I.CF_Superiore=S.Codice_Fiscale)
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.
Cfr. La query Esempio 17 su azienda2002.mdb
39
Esempio 18
Query di Selezione: JOIN ANNIDATI
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.
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
40
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”.
41
Query 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
42
Esempio 19:
Inserimento 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
43
Esempio 20 : Query di creazione tabella
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.
CREATE TABLE INFO_DIP (Nome_Dip
VARCHAR(15),
Numero_Impiegati INTEGER,
Totale_Stipendi
INTEGER)
Cfr. La query Esempio 20 su azienda2002.mdb
44
Esempio 21: Query di inserimento risultati
Nella tabella creata con la query precedente si inseriscono i dati
risultanti da un’altra query.
INSERT INTO INFO_DIP
SELECT
FROM
WHERE
GROUP BY
(Nome_Dip, Numero_Impiegati,
Totale_Stipendi)
Nome_dipartimento, Count(*), Sum(Stipendio_annuo)
DIPARTIMENTO, IMPIEGATO
Identificativo_dipartimento = Id_Dipartimento
Nome_dipartimento
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
45
Aggiornamento 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]
46
Esempio 22: Aggiornamento di righe
Il progetto numero 10 è stato spostato a ‘Bellaire’ ed è ora
coordinato dal dipartimento numero 5. Aggiornare il database.
UPDATE PROGETTO
SET
Sede = ‘Bellaire’,
Id_ dipartimento_coord = ‘5’
WHERE Identificativo_progetto)= ’10’
Cfr. La query Esempio 22 su azienda2002.mdb
47
Esempio 23: Aggiornamento di righe
Il Dipartimento Research ha aumentato gli stipendi del 10%.
Aggiornare il database.
UPDATE
IMPIEGATO
SET Stipendio_annuo = 1.1* Stipendio_annuo
WHERE Id_Dipartimento IN
(SELECT Identificativo_dipartimento
FROM DIPARTIMENTO
WHERE Nome_dipartimento =‘Research’)
Cfr. La query Esempio 23 su azienda2002.mdb
48
Cancellazione di righe

La cancellazione di righe dalle tabella è
effettuata dal comando delete, la cui
sintassi è la seguente:
delete from NomeTabella [where condizione]
49
Esempio 24
Cancellazione di righe
Cancellare l’impiegato dal codice fiscale
‘1234567891011121’.
DELETE FROM IMPIEGATO
WHERE Codice_Fiscale = ‘1234567891011121’
Cfr. La query Esempio 24 su azienda2002.mdb
50