Linguaggio SQL - Giuseppe Di Capua

Appunti del Prof. Di Capua Giuseppe
BASI DI DATI
&
SQL
Appunti del Prof. Di Capua Giuseppe
INDICE
1
CENNI SULLA PROGETTAZIONE.................................................................................3
1.1
2
I DATABASE RELAZIONALI ...............................................................................................4
IL LINGUAGGIO SQL ........................................................................................................7
2.1
ATTIVARE SQL DA ACCESS ..............................................................................................7
2.2
ALCUNE NOTIZIE ..............................................................................................................8
2.3
LE ISTRUZIONI DEL DDL ..................................................................................................9
2.3.1
Creazione di una tabella .........................................................................................9
2.3.2
Creazione di un indice...........................................................................................12
2.3.3
Modifica di una tabella .........................................................................................13
2.3.4
Eliminare una tabella ............................................................................................13
2.3.5
Eliminare un indice ...............................................................................................14
2.4
LE ISTRUZIONI DEL DML................................................................................................15
2.4.1
Inserire dati in una tabella: Il comando INSERT .................................................16
2.4.2
Modificare i dati in una tabella: Il comando UPDATE ........................................17
2.4.3
Cancellare i dati in una tabella: Il comando DELETE.........................................18
2.4.4
Selezionare i dati : Il comando SELECT...............................................................18
2.4.4.1 Lettura esaustiva ................................................................................................18
2.4.4.2 Lettura di colonne (operazione di Proiezione) ..................................................19
2.4.4.3 Selezionare righe (operazione di restrizione) ....................................................20
2.4.4.4 Ordinamenti .......................................................................................................24
2.4.4.5 Funzioni di gruppo ............................................................................................25
2.4.4.6 Raggruppamenti ................................................................................................28
2.4.4.7 Le operazioni di Join (prodotto cartesiano).......................................................29
2.4.4.8 Le Subquery.......................................................................................................32
2
Appunti del Prof. Di Capua Giuseppe
1 Cenni sulla progettazione
Com’è noto, un data base può essere inteso come una raccolta di dati organizzati e ben
strutturati, coordinata da un sistema software generalizzato che ne renda facilmente gestibile il
reperimento di informazioni da parte di più utenti.
Uno dei compiti del software generalizzato di gestione del data base consiste nel memorizzare i
dati in modo da rendere possibili le seguenti operazioni:
• aggiornamento del data base attraverso l’inserimento di nuovi dati
• modifica del data base
• recupero dei dati attraverso ricerche
• cancellazione di dati
Il software che consente di gestire un database è detto DBMS (DataBase Management System).
Il DBMS permette agli utenti di gestire una macchina astratta che rende possibile la gestione del
database.
Questa macchina virtuale può essere schematizzata in livelli:
Livello Esterno
Sottoschema
Sottoschema
Livello Logico
Schema Logico
Livello Fisico
Schema Fisico
Il Livello Esterno è il livello che consente agli utenti di gestire i dati attraverso dei programmi
applicativi.
Un sottoschema o vista rappresenta i dati a cui un utente può accedere. Infatti, non tutti gli utenti
possono essere interessati all’intero data base o possono gestire tutti i dati.
Per questo motivo, si creano delle viste logiche, contenenti cioè solo i dati che un utente può
gestire.
I programmi o le viste sono indipendenti dall’organizzazione logica dei dati (indipendenza
logica), nel senso che se viene modificato lo schema logico non occorre modificare tutti i
programmi o le viste ma solo quelle direttamente interessate alla modifica dello schema logico.
Il DBMS mette a disposizione un linguaggio detto DML (Data Manipulation Language) che
consente di gestire i dati (Inserimento, cancellazioni, modifiche e interrogazione dei dati)
3
Appunti del Prof. Di Capua Giuseppe
Il Livello Logico o Concettuale è il livello in cui si trova la rappresentazione astratta del
database. Astratta perché tale rappresentazione è indipendente dal livello fisico (Indipendenza
fisica).
Per descrivere le caratteristiche dello schema logico, occorre adottare un linguaggio detto DDL
(Data Definition Language), offerto dal DBMS.
Il Livello Fisico gestisce il database fisico, ovvero come le informazioni sono memorizzate sulle
memorie di massa. Questo livello si interfaccia con la parte del Sistema Operativo che gestisce i
file (File System).
A questo proposito, Il DBMS offre un ulteriore linguaggio detto DMCL (Data Media Control
Language) che consente di organizzare i dati a livello fisico .
Perciò, per progettare un database occorre prevedere 3 fasi di lavoro:
•
•
•
Progettazione Concettuale
Progettazione Logica
Progettazione Fisica
La Progettazione Concettuale ha lo scopo di definire e schematizzare la realtà che si vuole
rappresentare indipendentemente dal DBMS che si utilizzerà.
Questa progettazione genera uno schema concettuale, realizzabile attraverso il Diagramma E/R
(Entità/Relazione), in cui gli elementi di interesse della realtà vengono rappresentati attraverso
delle Entità.
Le entità sono caratterizzate da Attributi e sono collegate tra loro attraverso Associazioni o
Relazioni.
La Progettazione Logica ha lo scopo di trasformare lo schema concettuale in uno schema logico
attraverso, per esempio, la definizione di tabelle e relazioni tra esse (Database Relazionale)
La Progettazione Fisica ha lo scopo di implementare lo schema logico, attraverso la definizione
di aspetti riguardanti la memorizzazione dei dati su memorie di massa.
1.1 I Database Relazionali
In un database Relazionale i dati sono organizzati in un sistema di tabelle contenenti dati
logicamente correlati fra loro attraverso delle relazioni tra l'una e l'altra tabella.
Ogni tabella è costituita da colonne (campi) e ogni colonna ha una specifica caratteristica
(dominio). Le righe della tabella sono dette record o tuple.
E’ possibile che all’interno di una tupla ci sia uno o più campi che identificano in maniera
univoca un record rispetto a tutti gli altri record. In questo caso quel campo viene indicato con il
termine di chiave primaria.
Ci possono essere, inoltre, dei campi che servono a relazionare (collegare) una tabella con
un’altra tabella . Tali campi identificano la cosiddetta chiave esterna. La cosa importante è che i
campi in relazione devono appartenere allo stesso dominio.
Esempio: Si prenda in esame un database che si pone come obiettivo la gestione dei voti degli
alunni di una classe. Si vogliano registrare i risultati di tutte le prove (scritte e orali) di tutti gli
alunni in tutte le materie. Le informazioni che occorre memorizzare sono:
4
Appunti del Prof. Di Capua Giuseppe
•
•
•
•
•
•
I dati dell’alunno
La materia
In nome del docente
La data della prova
Il voto
il tipo di prova (scritta/orale)
Nel modo "classico" avremmo realizzato un' unica tabella più o meno di questo tipo:
Cognome
Nome
Materia
Docente
Data
Bianchi
Viola
Bianchi
Rossi
Celeste
Rossi
Nero
Andrea
Lucia
Andrea
Daniele
Antonio
Daniele
Katia
Italiano
Matematica
Fisica
Italiano
Geografia
Matematica
Italiano
Buffon Angela
Toldo Luigi
Abbiati Luca
Buffon Angela
Peruzzi Franco
Toldo Luigi
Buffon Angela
12/10/2005
12/10/2005
13/10/2005
13/10/2005
15/10/2005
20/10/2005
20/10/2005
Voto Tipo di
Prova
7
Orale
5,5
Orale
4
Orale
6
Orale
4,5
Orale
6
Orale
5
Orale
Appare evidente, in questo modo, come sussista il problema della ridondanza dei dati (abbiamo
registrato l’alunno Bianchi Andrea 2 volte, il docente Buffon Angela 3 volte), il che può
provocare - a lungo andare - anche il fenomeno negativo dell’inconsistenza dei dati stessi.
Il modello relazionale consente di dividere una tabella in più tabelle senza, ovviamente, causare
perdita di alcuna informazione, ma facendo in modo da eliminare, per esempio, la ridondanza dei
dati.
Ritornando all’esempio ipotizzato, si possono allora creare 4 tabelle :
•
•
•
•
Una prima tabella (Alunni) memorizzerà i dati degli alunni della classe con tutti i dati di
interesse di un alunno.
Una seconda tabella (Materie) con i dati delle materie e dei docenti che l’insegnano.
Una terza (Tipologia) con i tipi di prove
Una quarta tabella (Prove) conterrà i dati relativi alle prove sostenute dagli studenti.
Tabella Alunni
CodAlunno*
1
2
3
4
5
5
Cognome
Bianchi
Celeste
Nero
Rossi
Viola
Nome
Andrea
Antonio
Katia
Daniele
Lucia
Data di Nascita
09/07/1990
02/06/1990
12/03/1990
19/10/1990
15/12/1990
Luogo di Nascita
Avellino
Avellino
Avellino
Avellino
Avellino
Appunti del Prof. Di Capua Giuseppe
Tabella Materia
CodMateria *
1
2
3
4
Materia
Italiano
Matematica
Fisica
Geografia
Docente
Buffon Angela
Toldo Luigi
Abbiati Luca
Peruzzi Franco
Tabella Tipologie
CodTipo *
1
2
3
Descrizione
Orale
Scritto
Pratica
Tabelle Prove
Prog.*
1
2
3
4
5
6
7
CodAlunno
1
5
1
4
2
4
3
CodMateria
1
2
3
1
4
2
1
Data Prova
12/10/2005
12/10/2005
13/10/2005
13/10/2005
15/10/2005
20/10/2005
20/10/2005
Voto
7
5,5
4
6
4,5
6
5
CodTipo
1
1
1
1
1
1
1
Questa soluzione consentirà di ridurre o addirittura di eliminare le ripetizioni degli stessi dati in
più righe di tabelle: inseriremo una sola volta Bianchi Andrea per tutte le volte che vorremo
usarlo in punti differenti del nostro database ed avremo sempre la certezza di chiamarlo
correttamente e non Bianci Andrea o Andrea Bianchi ecc..
I campi con * sono chiavi primarie mentre gli altri codici della tabella Prove sono chiavi
esterne e sono relazionate (vedi i colori) con i campi chiave delle altre tabelle.
Poniamoci ora una domanda: leggendo dalla tabella Prove, come facciamo a capire che la
l’alunno Rossi è stato interrogato in Italiano ?
La risposta ci è fornita dalle relazioni. Ogni tabella, infatti possiede un codice univoco (la chiave
primaria ) che identifica solo ed esclusivamente una riga (non ci sono né possono essere due
alunni con CodAlunno = 1).
Notiamo che tali codici vengono richiamati all' interno della tabella delle Prove: si può quindi
dire si è stabilita una relazione tra il campo CodAlunno della tabella Prove (Chiave esterne) e il
campo CodAlunno della tabella Alunni.
Allo stesso modo si ha una relazione tra CodMateria della tabella Materie e CodMateria della
tabella Prove.
In questo capitolo non ci occuperemo in dettaglio di progettazione, bensì delle modalità di
gestione dei diversi livelli di un database attraverso l’uso di un linguaggio denominato SQL.
6
Appunti del Prof. Di Capua Giuseppe
2 Il linguaggio SQL
Il linguaggio SQL (Structured Query Language) è diffuso su tutti i DBMS che usano il modello
relazionale dei dati; esso consente di definire relazioni e di esprimere interrogazioni e modifiche
a una base di dati. L’SQL è un linguaggio dichiarativo e non procedurale.
Le istruzioni dell’SQL si possono classificare in:
DDL (Data Definition Language) contiene le istruzioni per la gestione dello schema logico,
ovvero consente di create tabelle, creare indici ecc..
DML (Data Manipulation Language) contiene le istruzioni per la gestione dei dati contenuti
nelle tabelle, ovvero consente di inserire , cancellare, modificare e ricercare dati .
DCL (Data Control Language) contiene le istruzioni per la gestione delle operazioni, ovvero
consente di definire per gli utenti i permessi sulle tabelle (quali operazioni possono fare sulle
tabelle), di definire i permessi di accesso ai dati, ecc…
2.1 Attivare SQL da Access
Per attivare i comandi di SQL da Access, occorre seguire la procedura per creare una query (vedi
paragrafi precedenti). Senza dover necessariamente selezionare una tabella si procede dalla barra
del menu con
Visualizza
Visualizzazione SQL
Apparirà una finestra del tipo :
nella quale possono essere
scritte le istruzioni SQL.
Per poter eseguire le
istruzioni scritte occorre
selezionare il bottone
presente sulla barra. Una
volta eseguita la query per
ritornarci sopra occorre
selezionare :
Visualizza
Visualizzazione SQL
7
Appunti del Prof. Di Capua Giuseppe
2.2 Alcune notizie
SQL è un linguaggio case unsensitive quindi tutte le istruzioni possono essere scritte sia in
maiuscolo che in minuscolo.
I nomi delle tabelle e dei campi possono avere come carattere speciale solo “_” (underscore).
Non utilizzare come separatore il carattere “.” (punto) perché il punto è usato per riferirsi al
campo di una tabella secondo la notazione:
Nometabella.nomecampo
Alcuni dei tipi di dati che si possono utilizzare sono tipicamente quelli riportati sotto in tabella.
Nome
Tipo
Charter(N) o Carattere
Char(N)
Integer o Int
Smallint
Decimal(P,D)
o Dec(P,D)
Float
Real
Date
Time
Intero
Intero
Decimale
Decimale
Decimal
Data
Ora
Descrizione
Definisce una colonna atta a contenere una stringa di
caratteri alfanumerici di lunghezza fissa N. La lunghezza
è opzionale. Se non fornita, si assume uguale a 1
Numero intero
Numero intero
Numero Reale con P cifre prima della virgola e D cifre
decimali
Numero Reale Floating point (virgola mobile)
Numero Reale Floating point (virgola mobile)
Formato aa/mm/gg
Formato hh:mm
Per le costanti di tipo stringa si usano i separatori ‘ oppure “.
Nelle espressioni si possono usare i seguenti operatori:
matematici
+, -, *, /
relazionali
<, >, <=, >=, <>
logici
and, or, not
Nella sintassi successiva indicheremo in grassetto le parole chiavi del linguaggio SQL e le
opzioni non obbligatorie saranno racchiuse tra parentesi quadre [].
8
Appunti del Prof. Di Capua Giuseppe
2.3 Le istruzioni del DDL
2.3.1 Creazione di una tabella
Il comando per la creazione di una tabella è Create Table . La sintassi di questo comando è
molto articolata.
Di seguito ne presentiamo una versione semplificata adatta ai nostri scopi:
CREATE TABLE NomeTabella(
NomeColonna1 Dominio [NOT NULL[UNIQUE]]
[,NomeColonna2 Dominio [NOT NULL[UNIQUE]]...
[, PRIMARY KEY (ListaDiNomiDiColonna)]
)
dove:
f NomeTabella è il nome della relazione che viene creata;
f NomeColonna Dominio [,NomeColonna Dominio]... e’ l’elenco separato da virgole
degli attributi (con relativo dominio) che fanno parte della relazione, ovvero l’elenco dei
campi della tabella. Ricordiamo che ogni tabella deve avere almeno un campo;
f NOT NULL è opzionale ed indica che il campo è obbligatorio e perciò non può
contenere valori nulli.
f UNIQUE è opzionale ed indica che il campo non potrà contenere valori duplicati.
f PRIMARY KEY è opzionale e specifica quali attributi fanno parte della chiave primaria.
La successiva ListaDiNomiDiColonna (facente sempre parte dell’opzione PRIMARY
KEY) è una lista separata da virgole di nomi di campi (attributi).
Esempio 1: Si voglia creare una tabella Anagrafica Impiegati, con tuple ciascuna costituita da 6
campi (attributi):
Attributo
Codice Identificativo
Nome Impiegato
Mansione
Data Assunzione
Stipendio
Premio
Numero Dipartimento
Tipo
Numero intero
Alfanumerico
Alfanumerico
Data
Numero con decimali
Numero con decimali
Numero intero
Il codice SQL per creare la tabella Impiegati è:
CREATE TABLE Impiegati(
ImpId Integer not null,
Nome Char(20) not null,
Mansione Char(10) not null,
Data_ass Date not null,
Stipendio Real,
Premio_P Real,
DipNum integer not null
PRIMARY KEY (ImpId)
9
Lunghezza
4 cifre
20 caratteri
10 caratteri
Predefinita
3 cifre
Appunti del Prof. Di Capua Giuseppe
);
Esempio 2: Si voglia creare una tabella Dipartimenti, con record costituiti da 5 campi (attributi):
Attributo
Numero Dipartimento
Nome Dipartimento
Sigla Ufficio
Codice Divisione
Identificativo Dirigente
Tipo
Numero intero
Alfanumerico
Alfanumerico
Alfanumerico
Numero intero
Lunghezza
3 cifre
20 caratteri
5 caratteri
2 caratteri
4 cifre
CREATE TABLE Dipartimenti(
DipNum Integer not null,
NomeDip Char(20) not null,
Ufficio
Char(5) not null,
Divisione Char(2) not null,
Dirigente integer not null,
PRIMARY KEY (DipNum)
);
Esempio3 : Si voglia creare una tabella Anagrafica Docenti, con record costituiti da 3 campi:
Attributo
Identificativo Docente
Cognome
Nome
Tipo
Numero intero
Alfanumerico
Alfanumerico
Lunghezza
3 cifre
20 caratteri
20 caratteri
CREATE TABLE Docenti(
Iddocente integer not null,
Cognome Char(20) not null,
Nome Char(20) not null,
PRIMARY KEY (Iddocente)
);
Esempio 4: Si voglia creare una tabella Anagrafica Corsi, con record costituiti da 3 campi:
Attributo
Codice Corso
Descrizione
Identificativo Docente
Tipo
Alfanumerico
Alfanumerico
Numero intero
CREATE TABLE Corsi(
Codcorso Char(3) not null,
Descrizione Char(20) not null,
Iddocente integer not null,
PRIMARY KEY (Codcorso)
);
10
Lunghezza
3 caratteri
20 caratteri
3 cifre
Appunti del Prof. Di Capua Giuseppe
Esempio 5: Si voglia creare una tabella Anagrafica Studenti, con record costituiti da 2 campi:
Attributo
Matricola
Cognome
Tipo
Numero intero
Alfanumerico
Lunghezza
4 cifre
20 caratteri
CREATE TABLE Studenti(
Matricola integer not null,
Cognome Char(20) not null,
PRIMARY KEY (Matricola)
);
Esempio 6: Si voglia creare una tabella Esami, con record costituiti da 3 campi (attributi):
Attributo
Matricola
Corso
Voto
Tipo
Numero intero
Alfanumerico
Numero intero
Lunghezza
4 cifre
3 caratteri
2 cifre
CREATE TABLE Esami(
Matricola integer not ull,
corso
Char(3) not null,
voto
integer not null,
PRIMARY KEY (Matricola,corso)
);
Attraverso Access, una volta attivata la finestra SQL e scritta una delle sintassi sopra riportate, si
seleziona il tasto
.
A questo punto chiudendo SQL e andando nella finestra database alla voce Tabelle ci sarà la
tabella creata.
All’interno di una creazione di tabella è possibile inserire altri istruzioni.
Le principali istruzioni sono:
FOREIGN
KEY
(ListaDiNomiDiColonna1)
REFERENCES
Nometabella
(ListaDiNomiDiColonna2),
dove ListaDiNomiDiColonna1 sono le colonne che fanno parte di una chiave esterna che sono
referenziate (ovvero corrispondono) alle (ListaDiNomiDiColonna2) della tabella Nometabella.
In questo modo si stabilisce un’integrità referenziale tra le due tabelle.
Esempio 4: Integrità Referenziale – Relazione tra le tabelle Impiegati e Dipartimenti attraverso
i campi DipNum presenti in ambedue le tabelle.
CREATE TABLE Impiegati(
ImpId Integer not null,
Nome Char(20) not null,
11
Appunti del Prof. Di Capua Giuseppe
Mansione Char(10) not null,
Data_ass Date not null,
Stipendio Real,
Premio_P Real,
DipNum Integer,
PRIMARY KEY (ImpId),
FOREIGN KEY (DipNum) REFERENCES Dipartimenti(DipNum)
);
Esempio 5: Integrità Referenziale – Relazione tra le tabelle Corsi e Docenti attraverso i campi
IdDocenti presenti in ambedue le tabelle.
CREATE TABLE Corsi(
Codcorso Char(3) not null,
Descrizione Char(20) not null,
Iddocente integer not null,
PRIMARY KEY (Codcorso),
FOREIGN KEY (Iddocente) REFERENCES Docenti(Iddocente)
);
2.3.2 Creazione di un indice
E’ possibile associare ad una tabella un indice. Un indice contiene uno o più campi della tabella
ed ha lo scopo di semplificare e velocizzare le ricerche sulla tabella. Quando si crea un indice in
effetti viene creata una nuova tabella contenente solo i campi di indice. Il linguaggio SQL si
serve di tale indice quando effettua ricerche o aggiornamenti: nell’indice, infatti i dati sono
ordinati rispetto all’attributo relativo e quindi più facilmente rintracciabili.
Per creare un indice la sintassi è:
CREATE [UNIQUE] INDEX nomeindice ON nometabella
(nomecolonna1,nomecolonna2,…,nomecolonnaN)
dove:
f UNIQUE indica che l’indice è univoco ovvero non ammette duplicati. Se tale clausola
non è indicata i campi dell’indice ammettono dati duplicati.
f nomeindice è il nome da dare all’indice.
f nometabella è il nome della tabella.
f nomecolonna1, nomecolonna2,…. sono delle colonne che fanno parte dell’indice.
Esempio 1: Creazione Indice nella Tabella Impiegati– l’indice si baserà sui campi Mansione ed a
parità sulla Data Assunzione del dipendente.
CREATE INDEX indice1 ON Impiegati(Mansione, Data_ass)
12
Appunti del Prof. Di Capua Giuseppe
2.3.3 Modifica di una tabella
Il comando per la modifica della struttura di una tabella è Alter Table. La sintassi di questo
comando è
ALTER TABLE NomeTabella
ADD nomecolonna tipo
MODIFY nomecolonna nuovotipo
DROP COLUMN nomecolonna
dove:
f ADD consente di aggiungere una nuova colonna indicata in nomecolonna di tipologia
indicata da tipo.
f MODIFY consente di modificare la tipologia di una colonna indicata da nomecolonna
in una tipologia indicata da tipo.
f DROP COLUMN consente di eliminare la colonna indicata in nomecolonna.
Esempio 1: Modifica tabella
ALTER TABLE Studenti
ADD Nome char(20)
Effetto: viene aggiunta la colonna Nome che conterrà una stringa di 20
Esempio 2: Modifica tabella
ALTER TABLE Impiegati
MODIFY Nome char(25)
Effetto: la colonna Nome passa da una stringa di 20 a una stringa di 25.
2.3.4 Eliminare una tabella
Quando si elimina una tabella occorre prestare molta attenzione, in quanto ciò può provocare il
fenomeno dell’inconsistenza. Infatti, se le tabelle sono collegate attraverso chiavi esterne la
cancellazione di una di queste tabella provocherà la violazione di integrità.
Per eliminare una tabella il comando è
DROP TABLE nometabella [RESTRICT|CASCADE|SET NULL]
dove :
f RESTRICT non consente la cancellazione della tabella se è collegata ad altre tabelle.
f CASCADE cancella la tabella e tutte le tabelle ad essa collegate.
f SET NULL cancella la tabella e mette nelle chiavi esterne delle tabelle collegate il
valore null.
13
Appunti del Prof. Di Capua Giuseppe
Esempio 1: Elimina la tabella Studenti
DROP TABLE Studenti
2.3.5 Eliminare un indice
Oltre alla tabella è possibile eliminare anche indici creati precedentemente .
Per eliminare un indice il comando è
DROP nomeindice
Esempio 1: Elimina l’indice 1:
DROP indice1
14
Appunti del Prof. Di Capua Giuseppe
2.4 Le istruzioni del DML
Prima di analizzare le istruzioni del DML ipotizziamo che, dopo aver provveduto alla loro
creazione (attraverso i comandi CREATE TABLE visti in precedenza) si debba procedere
all’immissione dei dati seguenti:
Tabella Impiegati
ImpId
7369
7499
7521
7566
7654
7698
7782
7788
7839
7844
7876
7900
7902
7934
7977
Nome
Rossi
Andre
Bianchi
Rosi
Martini
Blacchi
Neri
Scotti
Dare
Turni
Adami
Gianni
Fordi
Milli
Verdi
Mansione
ingegnere
tecnico
tecnico
dirigente
segretario
dirigente
ingegnere
segretaria
ingegnere
tecnico
ingegnere
Ingegnere
Segretario
Ingegnere
Dirigente
Data_Ass
17-Dic-80
20-Feb-81
20-Feb-8
02-Apr-81
28-Set-81
01-Mag-81
01-Giu-81
09-Nov-81
17-Nov-81
08-Set-81
28-Set-81
03-Dic-81
03-Dic-81
23-Gen-82
10-Dic-80
Stipendio
1600,00
800,00
800,00
2975,00
800,00
2850,00
2450,00
800,00
2600,00
1500,00
1100,00
1950,00
1000,00
1300,00
3000,00
Divisione
D1
D1
D2
Dirigente
7977
7566
7698
Tabella Dipartimenti
DipId
10
20
30
NomeDip
Edilizia Civile
Ricerche
Edilizia Stradale
Ufficio
1100
2200
5100
Tabella Studenti
Matricola
2456
3456
3566
5434
7837
15
Cognome
Rossi
Azzurro
Giallo
Rossi
Verdi
Nome
Giovanni
Giulia
Antonio
Ugo
Alfredo
PremioP
500,00
100,00
200,00
300,00
500,00
150,00
DipNum
20
30
30
20
30
30
10
20
10
30
20
30
20
10
10
Appunti del Prof. Di Capua Giuseppe
Tabella Docenti
Codcorso
10
20
30
Cognome
Zarro
Perna
Granese
Nome
Antonio
Luca
Luigia
Tabella Corsi
Codcorso
A01
A02
A03
A04
A05
Descrizione
Analisi I
Fisica
Chimica
Analisi II
Biologia
Docente
10
20
20
10
30
Tabella Esami
Matricola
5434
7837
5434
3566
5434
3566
Corso
A01
A01
A02
A03
A03
A01
Voto
25
29
30
28
24
23
2.4.1 Inserire dati in una tabella: Il comando INSERT
Il comando INSERT consente di inserire nuove righe all’interno di una tabella. La sintassi è
INSERT INTO Nometabella [nomecolonna1,nomecolonna2,…nomecolonnaN]
VALUES (valore1,valore2,…..,valoreN)
Dove :
f Nometabella è il nome della tabella in cui inserire i dati;
f nomecolonna1,nomecolonna2,…nomecolonnaN sono i nomi delle colonne in cui
inserire i dati. Tali nomi sono opzionali
f valore1,valore2,…..,valoreN sono i valori da inserire nelle colonne. Tali valori verranno
inseriti in maniera posizionale rispetto al nome delle colonne indicate
Se i nomi delle colonne non vengono indicate, i valori verranno inseriti in maniera posizionale
rispetto alle colonne come definite nella tabella.
Se non si inseriscono dei valori si considera il valore null
16
Appunti del Prof. Di Capua Giuseppe
Esempio 1: Inserimento di un record .
INSERT INTO Studenti VALUES (‘4675’,’Rossi’,’Adelaide’)
2.4.2 Modificare i dati in una tabella: Il comando UPDATE
Il comando UPDATE consente di modificare i dati inseriti all’interno di una tabella. La sintassi
è:
UPDATE Nometabella
SET
nomecolonna1=valore1,
nomecolonna2=valore2,
………………………
nomecolonnaN=valoreN
[WHERE condizione]
dove
f Nometabella è il nome della tabella in cui modificare i dati;
f nomecolonna1=valore1 …..indica che in nomecolonna1 verrà inserito valore1.
Occorre sottolineare che valore1 può essere anche un’espressione aritmetica ;
f WHERE condizione indica quali righe sono soggette alla variazione. Se tale clausola
non viene indicata verranno aggiornate tutte le righe della tabella.
Esempio 1: Modifica il record della tabella Dipartimenti che presenta l’identificazione del
dipendente 20.
UPDATE Dipartimenti
SET
Divisione = ‘D2’
Where dipid = 20
In questo modo solo il record che ha nel campo dipId il valore 20 verrà aggiornato. Senza la
clausola Where avremmo avuto l’aggiornamento di tutti i record della tabella.
17
Appunti del Prof. Di Capua Giuseppe
2.4.3 Cancellare i dati in una tabella: Il comando DELETE
Il comando DELETE consente di cancellare i dati inseriti all’interno di una tabella. La sintassi è
DELETE FROM Nometabella
[WHERE condizione]
dove:
f Nometabella è il nome della tabella in cui cancellare i dati;
f WHERE condizione indica quali righe saranno cancellate. Se tale clausola non viene
indicata verranno cancellate tutte le righe della tabella.
Esempio 1: Cancellare il record dalla Tabella Studenti relativo ad una data matricola
DELETE FROM Studenti
Where Matricola = ‘3456’
verrà cancellato il record con matricola = 3456 (corrispondente a Azzurro Giulia dell’esempio
sopra riportato).
2.4.4 Selezionare i dati : Il comando SELECT
Il verbo SELECT può essere utilizzato ogni volta che si vuole accedere al database in lettura,
allo scopo di eseguire interrogazioni sui dati memorizzati.
Il risultato di questa istruzione è una tabella che contiene i dati selezionati dal database. La
sintassi generica del comando è:
SELECT [ALL|DISTINCT] nomecolonna1,nomecolonna2……
FROM nometabella1,nometabella2 …..
[WHERE condizioni]
[GROUP BY nomecolonna1, nomecolonna2,…]
[HAVING condizioni su gruppi]
[ORDER BY nomecolonna1, nomecolonna2,…]
Delle clausole presenti nella SELECT solo la clausola FROM è obbligatoria.
Analizziamo di seguito, attraverso degli esempi la sintassi del verbo SELECT.
2.4.4.1 Lettura esaustiva
Accanto alla SELECT deve essere specificato l’elenco delle colonne che si vogliono
visualizzare. Se si vogliono visualizzare tutte le colonne si può usare il simbolo * . Quindi per
leggere tutte le colonne di una tabella si scrive:
18
Appunti del Prof. Di Capua Giuseppe
SELECT *
FROM Studenti
Il risultato di questa query (interrogazione) è
la tabella presentata a lato.
Nella clausola FROM si scrive il nome
della tabella da cui selezionare i dati. La
clausola ALL consente di includere nella
selezione tutte i record (tuple) che soddisfano la condizione. Per default, il verbo SELECT usa
la clausola ALL.
2.4.4.2 Lettura di colonne (operazione di Proiezione)
Se si vogliono leggere solo alcune colonne della tabella si scrive:
SELECT Matricola, Cognome
FROM Studenti
Il risultato è la tabella a lato.
Analogamente potremmo scrivere:
SELECT dipnum
FROM Impiegati
Ottenendo la tabella a lato .
Se si vuole selezionare la colonna dipnum visualizzando solo i codici
distinti si aggiunge la clausola DISTINCT . Quindi scrivendo:
SELECT DISTINCT dipnum
FROM Impiegati
si otterrà la tabella:
19
Appunti del Prof. Di Capua Giuseppe
2.4.4.3 Selezionare righe (operazione di restrizione)
Per selezionare le righe di una tabella che soddisfano determinate condizioni si usa la clausola
WHERE seguita da una o più condizioni. All’interno della condizione si possono utilizzare gli
operatori di confronto (=,>,<,>=,<=,<>,) e gli operatori logici (and, or, not).
Se scriviamo:
SELECT nome, mansione, stipendio
FROM Impiegati
WHERE stipendio > 1000
Otterremmo il risultato corrispondente alla
tabella raffigurata a lato.
Volendo selezionare i soli dipendenti ingegneri scriveremo:
SELECT nome, mansione, stipendio
FROM Impiegati
WHERE mansione = ‘ingegnere’
Otterremo la seguente tabella :
quindi il confronto tra stringhe va fatto usando racchiudendo la stringa tra apici.
Oltre agli operatori di confronto si possono utilizzare anche operatori. Vediamo di seguito alcuni
di essi
Operatore AND
Consente di selezionare le righe che soddisfano contemporaneamente a più condizioni. Per
esempio se vogliamo selezionare gli impiegati che sono ingegneri e che hanno uno stipendio
maggiore di 1600 scriveremo:
SELECT nome, mansione, stipendio
FROM
Impiegati
WHERE mansione = ‘ingegnere’
AND stipendio > 1600
20
Appunti del Prof. Di Capua Giuseppe
ottenendo la tabella a lato.
Operatore OR
Consente di selezionare le righe che soddisfano almeno ad una delle condizioni indicate. Per
esempio, se vogliamo selezionare gli impiegati che sono tecnici oppure che sono stati assunti
dopo il 01/01/1982 scriveremo:
SELECT nome, mansione, stipendio, data_ass
FROM
Iimpiegati
WHERE mansione = ‘tecnico’
OR data_ass > #01/01/1982#
Il risultato è schematizzato nella
figura a lato, dove è chiaro che si
tratta di tecnici o dipendenti assunti
dopo il 1° gennaio 1982
La sintassi delle date con l’uso del # è tipica di Access. Altri DBMS possono gestire i confronti
con le date secondo diverse modalità.
Se in una stessa clausola vengono utilizzati sia l’operatore AND che l’operatore OR verrà
valutato prima l’AND e poi l’OR, a meno che non vengano utilizzate delle parentesi quadre per
definire un ordine diverso.
Operatore BETWEEN
La sintassi è:
nomecolonna BETWEEN valore1 AND valore2
L’operatore BETWEEN consente di selezionare le sole righe che hanno il valore della colonna
specificata in nomecolonna compreso tra valore1 e valore2.
Per esempio, se vogliamo selezionare gli impiegati che hanno uno stipendio compreso tra 1000 e
2000 potremmo scrivere:
SELECT nome, mansione, stipendio
FROM Impiegati
WHERE stipendio BETWEEN 1000 AND 2000
La tabella ottenuta è raffigurata a fianco.
21
Appunti del Prof. Di Capua Giuseppe
Operatore IN
La sintassi è:
nomecolonna IN (listavalori)
Consente di selezionare le righe che hanno il valore della colonna specificata in nomecolonna
uguale ad uno dei valori indicati in listavalori .
Per esempio se vogliamo selezionare gli impiegati che sono dirigenti o tecnici oppure segretarie
scriveremmo:
SELECT nome, mansione, stipendio
FROM
Impiegati
WHERE mansione IN
(‘dirigente’,’tecnico’,’segretaria’)
Ottenendo la tabella a lato:
Operatore LIKE
La sintassi è:
nomecolonna LIKE ‘stringa’
Consente di selezionare le righe che hanno il valore della colonna specificata in nomecolonna
che trova riferimento con una certa stringa
Possono essere utilizzati due metacaratteri:
•
•
*
?
trova riferimento con qualunque stringa compresa la stringa vuota
trova riferimento esattamente con un singolo carattere
Questi metacaratteri sono tipici di Access. Altri DBMS possono usare metacaratteri diversi
come % al posto di * e _(underscore) al posto di ?
Per esempio se vogliamo selezionare gli impiegati che hanno un nome che comincia con A
scriveremo:
SELECT nome, mansione, stipendio
FROM
Impiegati
WHERE nome LIKE ‘A*’
Ottenendo:
Se vogliamo selezionare gli impiegati che hanno un nome che comincia con A e che hanno come
terza lettera la d scriveremo:
SELECT nome, mansione, stipendio
FROM
Impiegati
WHERE nome LIKE ‘A?d*’
22
Appunti del Prof. Di Capua Giuseppe
ottenendo:
Operatore IS NULL
La sintassi è:
nomecolonna IS NULL
Con l’operatore IS NULL è possibile ad esempio selezionare le righe che hanno nella colonna
nomecolonna un valore nullo.
In un altro caso, se si vogliono selezionare tutti gli impiegati che non hanno un premio
produzione si scriverà:
SELECT nome, mansione, stipendio, premio_P
FROM
Impiegati
WHERE premio_P IS NULL
Ottenendo
la
tabella
rappresentata a fianco:
Operatore NOT
L’operatore NOT indica la negazione della condizione. Esso è abbinabile agli operatori visti
sopra.
I diversi casi possibili sono:
nomecolonna NOT BETWEEN valore1 AND valore2
nomecolonna NOT IN (listavalori)
nomecolonna NOT LIKE ‘stringa’
nomecolonna IS NOT NULL
Il primo caso presentato consente di selezionare le righe che hanno il valore della colonna
specificata in nomecolonna non compreso tra valore1 e valore2.
23
Appunti del Prof. Di Capua Giuseppe
Per esempio, se vogliamo selezionare gli impiegati che hanno uno stipendio non compreso tra
1000 e 2000 scriveremmo:
SELECT nome, mansione, stipendio
FROM Impiegati
WHERE stipendio NOT BETWEEN 1000 AND 2000
Ottenendo:
2.4.4.4 Ordinamenti
Per ordinare il risultato di una interrogazione si usa la clausola ORDER BY.
La sintassi è:
ORDER BY nomecolonna1, nomecolonna2,.. [DESC]
Per default, l’ordinamento è crescente sui valori delle colonne indicate in nomecolonna1,…. Se
si vuole ordinare in maniera decrescente occorre usare l’opzione DESC.
Se vogliamo leggere la tabella studenti ordinata sul cognome scriveremo:
SELECT *
FROM
Studenti
ORDER BY cognome
ottenendo:
Se vogliamo selezionare gli impiegati che hanno uno stipendio compreso tra 1000 e 2000
ordinati sullo stipendio scriveremo:
SELECT nome, mansione, stipendio
FROM
Impiegati
WHERE stipendio BETWEEN 1000 AND 2000
ORDER BY stipendio
24
Appunti del Prof. Di Capua Giuseppe
Ottenendo:
È possibile ordinare anche su più colonne e anche con ordinamenti diversi. Se vogliamo
selezionare gli impiegati che hanno uno stipendio compreso tra 1000 e 2000 ordinati sulla
mansione in ordine crescente e - a parità di mansione - ordinare sullo stipendio in maniera
descrescente scriveremmo:
SELECT nome, mansione, stipendio
FROM
Impiegati
WHERE stipendio BETWEEN 1000 AND 2000
ORDER BY mansione, stipendio DESC
Ottenendo:
2.4.4.5 Funzioni di gruppo
Il linguaggio SQL è dotato di funzioni che applicate ad una colonna, calcolano un valore per
ciascun gruppo selezionato dalla query.
Riportiamo di seguito alcune di queste funzioni di gruppo.
Funzione COUNT
La sintassi di tale funzione è :
COUNT([DISTINCT] nomecolonna)
oppure
COUNT(*)
Questa funzione calcola il numero di valori presenti nella colonna nomecolonna. Se è preceduta
dall’istruzione DISTINCT visualizza il numero di valori distinti presenti in nomecolonna.
Se viene messo come argomento il simbolo * , allora la funzione restituisce il numero di righe
selezionate per ciascun gruppo.
25
Appunti del Prof. Di Capua Giuseppe
Ad esempio, se si scrive:
SELECT COUNT(nome)
FROM
Impiegati
Si otterrà il numero 15, cioè quanti sono i nomi della tabella Impiegati.
Se, invece si scrive:
SELECT COUNT(*)
FROM
Impiegati
WHERE stipendio > 1000
Otterremo il numero di impiegati per i quali lo stipendio è maggiore di
1000. Tali righe, per l’esempio riportato in precedenza risultano essere in
numero di 10.
Funzione SUM
La sintassi di tale funzione è :
SUM(nomecolonna)
Questa funzione restituisce la somma dei valori presenti nella colonna nomecolonna ed è
applicabile evidentemente alle sole colonne contenenti valori numerici.
Ad esempio, se si scrive:
SELECT SUM(stipendio)
FROM Impiegati
Si otterrà la somma degli stipendi di tutti gli impiegati.
Funzione AVG
La sintassi di tale funzione è:
AVG(nomecolonna)
Questa funzione restituisce la media aritmetica dei valori presenti nella colonna nomecolonna.
Anch’essa si applica a colonne contenenti valori numerici.
Ad esempio se si scrive:
SELECT AVG(voto)
FROM Esami
WHERE matricola = 5434
26
Appunti del Prof. Di Capua Giuseppe
sarà calcolata la media dei voti d’esame della matricola 5434.
Funzione MAX
La sintassi di tale funzione è:
MAX(nomecolonna)
Questa funzione restituisce il valore più grande presente nella colonna nomecolonna di un certo
gruppo di record presenti in una tabella. Si applica sia a colonne numeriche che alfanumeriche.
Per esempio se si scrive:
SELECT MAX(voto)
FROM Esami
WHERE matricola = 5434
Sarà determinato il voto più alto presente nella tabella Esami, ottenuto
della matricola 5434.
dalla
Funzione MIN
Analogamente alla funzione MAX, opera la funzione MIN, ma con la restituzione del valore più
piccolo presente in un gruppo di una tabella, con riferimento all’attributo nomecolonna. La sua
sintassi è semplice:
MIN(nomecolonna)
Ad esempio, scrivendo:
SELECT MIN(stipendio)
FROM Impiegati
WHERE dipnum = 10
Otterremo lo stipendio minimo tra coloro presenti nella tabella
Impiegati e che lavorano nel dipartimento 10
Una nota importante da tener presente è che non sono ammesse query che richiedono la
visualizzazione di funzioni di gruppo insieme a colonne semplici, a meno che queste ultime non
compaiono anche nella clausola GROUP BY.
Ad esempio, se si vuole conoscere non solo lo stipendio più basso degli impiegati che lavorano
nel dipartimento 10, ma anche il nome dell’impiegato che riceve tale stipendio, non si può
scrivere:
SELECT MIN(stipendio),nome
FROM Impiegati
WHERE dipnum = 10
27
Appunti del Prof. Di Capua Giuseppe
In particolare, in questo caso, Microsoft Access visualizzerà il seguente messaggio:
2.4.4.6 Raggruppamenti
Le funzioni di gruppo viste sopra sono generalmente abbinate alla clausola di raggruppamento
GROUP BY, la cui sintassi è:
GROUP BY nomecolonna1, nomecolonna2,… [HAVING condizionedigruppo]
Un esempio chiarirà bene tale clausola che si dimostra estremamente utile nell’aiutare a
realizzare interrogazioni utili sul data base. Riferendosi alla tabella Impiegati, se si vuole
visualizzare il salario medio in ogni dipartimento, è necessario dapprima raggruppare i dati della
tabella in base al numero del dipartimento e poi eseguire la media in ogni gruppo.
Per ottenere ciò, si scriverà:
SELECT dipnum, AVG(stipendio)
FROM Impiegati
GROUP BY dipnum
Il risultato ottenuto sarà la visualizzazione, per ogni dipartimento, della media degli stipendi:
Come è facile notare, il numero del dipartimento (10, 20 o 30 dell’esempio) è presente tra le
colonne selezionate e ciò è necessario per effettuare il raggruppamento.
La clausola GROUP BY è utilizzabile anche per effettuare raggruppamenti multipli, eseguiti cioè
con più colonne.
Ad esempio, se si vuole vedere il numero di dipendenti che svolgono una certa mansione
nell’ambito di un certo dipartimento, si può scrivere:
SELECT dipnum, mansione, COUNT(*)
FROM Impiegati
GROUP BY dipnum, mansione
In questo caso, il raggruppamento avverrà per numero di dipartimento e per mansione
(all’interno dello stesso dipartimento).
Il risultato relativo agli esempi di dati riportati è visualizzato nella seguente finestra:
28
Appunti del Prof. Di Capua Giuseppe
Un’ulteriore funzionalità è offerta dalla presenza della clausola HAVING che è utile per porre
delle condizioni sulle funzioni di gruppo.
Si supponga ad esempio di voler conoscere la mansione degli impiegati aventi uno stipendio
medio maggiore di 1300. In tal caso la condizione va posta sul risultato di una funzione di
gruppo. Non si può usare come in precedenza la clausola WHERE, bensì la clausola HAVING.
Infatti mentre la clausola WHERE consente di inserire condizioni sulle righe della tabella, la
clasuola HAVING definisce delle condizioni sui gruppi selezionati dalla query.
Quindi nel caso in esame, occorre scrivere:
SELECT mansione, AVG(stipendio)
FROM Impiegati
GROUP BY mansione
HAVING AVG(stipendio) > 1300
Il risultato sarà:
Le condizioni della clausola HAVING seguono le stesse regole della clausola WHERE, quindi si
possono usare tutti gli operatori relazionali e/o logici visti in precedenza.
2.4.4.7 Le operazioni di Join (prodotto cartesiano)
Quando si fa uso di un data base, spesso nasce la necessità di ottenere informazioni provenienti
da diverse tabelle. In questi casi, bisogna far ricorso ad operazioni particolari, dette join.
Per poter eseguire un’operazione di join tra due (o più) tabelle occorre che ricorrano certe
condizioni: innanzitutto bisogna che le tabelle da unire abbiano almeno una colonna definita
sullo stesso dominio. In effetti, le join consentono di selezionare dati tra tabelle diverse legando
chiavi primarie di una tabella con chiavi esterne di un’altra tabella.
Ad esempio, nella tabella Impiegati il campo DipNum è
una chiave esterna collegata alla chiave primaria
DipNum della tabella Dipartimenti (così come è stato
indicato dalla clausola FOREIGN KEY nel momento
della creazione della tabella).
29
Appunti del Prof. Di Capua Giuseppe
L’operazione di join, in ultima analisi equivale ad una selezione di righe non su una tabella, ma
su più tabelle, risultando equivalente ad un’altra operazione, nota come prodotto cartesiano tra
due tabelle. Per realizzare un prodotto cartesiano tra due tabelle basta indicare (come riportato
nel prossimo esempio) nella clausola FROM il nome delle due tabelle.
Se la clausola FROM è seguita da più di una tabella, è buona norma indicare nella clausola
SELECT i nomi delle colonne da selezionare facendoli precedere dal nome della tabella, con la
separazione del carattere “.” (es: Impiegati.nome). Tale regola, che formalmente è facoltativa,
diventa obbligatoria qualora nelle tabelle in questione, vi siano colonne aventi lo stesso nome.
Ad esempio, se si esegue il comando:
SELECT Impid, nome, mansione, Impiegati.dipnum, nomedip
FROM Impiegati, Dipartimenti
si otterrà dapprima il prodotto cartesiano tra le tabelle Impiegati e Dipartimenti (attraverso la
clausola FROM) con il risultato di una unica tabella virtuale avente un numero di righe pari al
prodotto cartesiano delle tabelle coinvolte; dato che dopo la parola SELECT sono stati indicati 5
campi Identificativo dell’impiegato, nome, mansione, numero del dipartimento e nome del
dipartimento, il risultato sarà la seguente visualizzazione:
E’ evidente che il risultato non è sensato, riportando ripetizioni e dati inutili. E’ opportuno,
pertanto, selezionare solo le righe significative, cioè quelle che hanno il numero di dipartimento
comuni allo scopo poi di estrarre i dati necessari.
30
Appunti del Prof. Di Capua Giuseppe
Si passa perciò a presentare l’operazione di join fra le due tabelle:
SELECT Impid, nome, mansione,Impiegati.dipnum, nomedip
FROM Impiegati, Dipartimenti
WHERE Impiegati.dipnum = Dipartimenti.dipnum
Si otterrà la tabella :
Dato che per l’esempio proposto nella clausola WHERE è stato usato il simbolo di uguaglianza,
la join esaminata viene indicata con il termine equijoin. Nel caso contrario, si sarebbe parlato di
operazione non-equijoin.
A scopo esemplificativo, seguono due esempi:
ESEMPIO 1
Date le tabelle Esami e Studenti, si vogliano selezionare le righe con voto maggiore di 27,
mostrando anche il nome dello studente ed ordinando i dati in modo crescente sul voto.
In questo caso, occorre selezionare i dati da due tabelle effettuando un’operazione di join sul
campo matricola:
SELECT Studenti.matricola, cognome, nome, corso, voto
FROM Esami, Studenti
WHERE Studenti.matricola = Esami.matricola
AND voto >= 27
ORDER BY voto
Il risultato sarà:
ESEMPIO 2
Date le tabelle Esami, Studenti, Corsi e Docenti si selezionino tutti gli esami, evidenziando il
nome del docente, il nome del corso, il nome dello studente e il voto.
31
Appunti del Prof. Di Capua Giuseppe
In questo caso, bisogna eseguire il comando:
SELECT Corso.codcorso, descrizione, Docenti.cognome, Docenti.nome,
Studenti.cognome, Studenti.nome, voto
FROM Esami, Studenti, Corso, Docenti
WHERE Studenti.matricola = Esami.matricola
AND Esami.corso = Corsi.codcorso
AND Docenti.iddocente = Corsi.iddocente
Il risultato sarà:
In sintesi, le due regole da rispettare per realizzare un’operazione di join sono::
•
elencare nella clausola FROM i nome delle tabelle coinvolte, per realizzare il prodotto
cartesiano;
•
specificare, nella clausola WHERE, le condizioni di join in modo da selezionare le righe
significative dal prodotto cartesiano.
2.4.4.8 Le Subquery
In qualche caso la risposta fornita da una certa query può essere utilizzata come condizione di
un’altra query.
Si supponga infatti di voler trovare tutti i dipendenti che lavorano nello stesso dipartimento del
dipendente di cognome Rossi. A questa richiesta si può rispondere solo se si è a conoscenza del
dipartimento di Rossi.
Per sapere quale sia il dipartimento di Rossi , si dovrebbe procede al seguente comando SQL:
SELECT dipnum
FROM Impiegati
WHERE nome = ‘Rossi’
Il risultato di questa query è 20 (il dipartimento di Rossi). A quel punto, per selezionare gli altri
impiegati di questo dipartimento occorrerebbe eseguire:
SELECT *
FROM impiegati
WHERE dipnum = 20
Questa seconda selezione sarebbe necessaria per indicare nella condizione il risultato della prima
selezione.
32
Appunti del Prof. Di Capua Giuseppe
I due passi precedentemente indicati possono invece essere sostituiti più efficacemente da
un’unica query che contiene al suo interno un’ulteriore query, detta appunto subquery.
Infatti si può scrivere:
SELECT *
FROM Impiegati
WHERE dipnum = (SELECT dipnum
FROM Impiegati
WHERE nome = ‘Rossi’)
ottenendo:
La richiesta più interna (subquery) restituisce un numero di dipartimento alla richiesta più
esterna (query principale).
E’ importante notare l’uso delle parentesi che consente di separare le clausole che appartengono
alla query principale da quelle che appartengono alla subquery.
Seguono due esempi:
ESEMPIO 1
Si vogliano selezionare tutti gli studenti che hanno un voto maggiore del voto medio tra tutti gli
esami.
L’istruzione SQL da eseguire è:
SELECT Studenti.matricola, cognome, nome, corso, voto
FROM Esami, Studenti
WHERE Studenti.matricola = Esami.matricola
AND voto >= (SELECT AVG(voto) FROM Esami )
Il risultato sarà:
ESEMPIO 2
Si vogliono selezionare tutti i dipendenti che svolgono la mansione di Rossi e che presentano
uno stipendio maggiore dello stipendio massimo dei tecnici.
33
Appunti del Prof. Di Capua Giuseppe
Per ottenere il risultato desiderato, si può ricorrere alla seguente query strutturata:
SELECT *
FROM Impiegati
WHERE mansione = (SELECT mansione
FROM Impiegati
WHERE nome = ‘Rossi’)
AND stipendio > (SELECT MAX(stipendio)
FROM Impiegati
WHERE mansione = ‘tecnico’)
Si otterrà:
Negli esempi precedenti sono stati esaminati casi in cui la subquery restituisce un unico valore
che viene poi confrontato nella condizione utilizzando gli usuali operatori relazionali.
In altri casi, le subquery possono restituire anche più valori, ricorrendo ai seguenti operatori:
•
•
•
ANY e ALL
EXISTS e NOT EXISTS
IN e NOT IN
f Operatore ANY
Per utilizzare l’operatore ANY, bisogna rispettare la seguente sintassi:
nomecolonna operatore ANY (Subquery)
Se la subquery restituisce più valori, con ANY viene confrontato il valore di nomecolonna con
tutti i valori restituiti dalla subquery. Perciò, il confronto risulta vero se almeno uno dei confronti
è vero.
Per comprendere appieno l’utilizzo di ANY, segue un esempio: si vogliano selezionare tutte le
matricole degli studenti che hanno come voto d’esame un voto maggiore di uno degli esami
sostenuti dallo studente con matricola 5434. In questo caso, si scriverà:
SELECT *
FROM Esami
WHERE voto > ANY (SELECT voto
FROM Esami
WHERE matricola = 5434 )
Il risultato sarà:
34
Appunti del Prof. Di Capua Giuseppe
f Operatore ALL
La sintassi da rispettare è:
nomecolonna operatore ALL (Subquery)
La subquery restituisce più valori. L’operatore ALL confronta il valore di nomecolonna con tutti
i valori restituiti dalla subquery. Il confronto risulta vero se tutti i confronti sono veri.
Ad esempio, se si vogliono selezionare tutte le matricole degli studenti che hanno come voto
d’esame un voto minore di tutti gli esami sostenuti dello studente matricola 5434, si avrà:
SELECT *
FROM Esami
WHERE voto < ALL (SELECT voto
FROM Esami
WHERE matricola = 5434 )
Si otterrà:
f Operatore EXISTS
L’operatore EXISTS consente di conoscere se la subquery cui esso si riferisce restituisce almeno
una riga.
La sintassi è la seguente:
EXISTS (Subquery)
Ad esempio, se si desidera l’elenco degli esami solo se la matricola 5434 ha svolto l’esame
A01, allora si può scrivere:
SELECT *
FROM Esami
WHERE EXISTS (SELECT matricola
FROM Esami
WHERE matricola = 5434
AND corso = ‘A01’ )
f Operatore NOT EXIST
Con l’operatore NOT EXIST si può facilmente sapere se la subquery di riferimento non
restituisce alcuna riga. La sintassi è :
NOT EXIST (Subquery)
35
Appunti del Prof. Di Capua Giuseppe
f Operatore IN
La sintassi è :
nomecolonna IN (Subquery)
Poiché la subquery può restituire più valori, con l’operatore IN si otterrà il valore vero se il
valore di nomecolonna appartiene all’insieme dei valori restituiti dalla subquery.
Ad esempio, se si vogliono visualizzare i dipendenti che hanno uno stipendio uguale a quello
delle segretarie, si scriverà l’istruzione SQL:
SELECT *
FROM Impiegati
WHERE stipendio IN (SELECT stipendio
FROM Impiegati
WHERE mansione = ‘segretaria’ )
ottenendo:
f Operatore NOT IN
L’operatore NOT IN restituisce un valore vero se il valore di nomecolonna non appartiene
all’insieme dei valori restituiti dalla subquery. La sintassi è simile a quella vista precedentemente
per IN :
nomecolonna operatore NOT IN (Subquery)
36