Appunti SQL

ISTITUTO SCOLASTICO ISTRUZIONE SECONDARIA SUPERIORE
“ G. SEGATO”
Corso di INFORMATICA
Modulo 2 : L’interrogazione di una Base di Dati1
Il Modulo è strutturato secondo le seguenti UNITA’ DIDATTICHE :
( UD1 ) L’Algebra Relazionale
Definizione di algebra relazionale
1. Le principali operazioni elementari : unione , differenza , intersezione ,
proiezione , selezione , prodotto .
2. Le operazioni composte : la giunzione interna ed esterna.
3. Esempi di interrogazione usando l’algebra relazionale.
( UD2 ) Fondamenti di SQL
1.
2.
3.
4.
5.
6.
7.
1
Relazioni tra QBE , SQL ed algebra relazionale.
Come creare e modificare una tabella in SQL.
La selezione tramite l’istruzione select.
Select semplice su singola tabella.
Select con giunzione su più tabelle.
Select nidificate.
Come realizzare ordinamenti e raggruppamenti in SQL.
AUTORE : Stefano Cecchin
A. S. 2008 / 2009
CLASSE : 5 INF
1
Algebra Relazionale ( cenni )
E’ l’insieme di operatori e operazioni che consentono di scrivere una
interrogazione tramite un FORMALISMO MATEMATICO. Si tratta in
pratica di un linguaggio formale. L’ uso del formalismo è diffuso tra i
matematici che per loro natura hanno una naturale capacità di
astrazione.
Vediamo le principali operazioni che questo formalismo consente :
⇐simbolo
La SOMMA o UNIONE in ACCESS
Si fondono due tabelle aventi la stessa struttura ( intensione ).
ESEMPIO :
SOCI = VECCHI-SOCI + NUOVI-SOCI
Formalismo matematico :
A+B≡{t|t∈A∨t∈B}
t ⇒ tupla o riga o record
∨ ⇒ or logico
∧ ⇒ and logico
La DIFFERENZA
Si ottiene una tabella che è la differenza tra due tabelle.
ESEMPIO :
VECCHI-SOCI = SOCI – NUOVI-SOCI
Formalismo matematico :
A–B≡{tt∈A∧t∉B}
La INTERSEZIONE
Consente di ottenere gli elementi comuni a due tabelle.
ESEMPIO :
VECCHI-SOCI = SOCI ∩ VECCHI-SOCI
STUDENTI ∩ SOCI-BIBLIOTECA = STUDENTI-LETTORI
Formalismo matematico :
A∩B≡A−(A–B)
2
Il PRODOTTO
Operazione fondamentale alla base della maggior parte delle
interrogazioni. Consente la concatenazione di due tabelle.
ESEMPIO :
Tabella A : 3 attributi , 4 tuple
Tabella B : 2 attributi , 2 tuple
Tabella ( A x B ) : 5 attributi , 8 tuple
La Tabella ottenuta come concatenazione tra due tabelle è composta
da un numero di attributi che è la somma degli attributi delle due
tabelle e da un numero di tuple che il prodotto delle tuple delle due
tabelle.
TABELLA SCUOLA
CLASSE SEZION
E
3
C
5
A
TABELLA PERSONA
NOME
Piero
Maria
Luca
Toni
ETA
23
12
18
34
SESSO
M
F
M
M
TABELLA PERSONA X SCUOLA
NOME
Piero
Piero
Maria
Maria
Luca
Luca
Toni
Toni
ETA
23
23
12
12
18
18
34
34
SESSO
M
M
F
F
M
M
M
M
3
CLASSE
3
5
3
5
3
5
3
5
SEZIONE
C
A
C
A
C
A
C
A
Formalismo matematico :
A X B ≡ { t  t = a conc b ∧ a∈A ∧ b∈B }
Le tuple ottenute per combinazione di ogni tupla di B con ogni tupla
di A.
La PROIEZIONE
Consente di selezionare una o più colonne ( campi , attributi ) di una
tabella.
ESEMPIO :
Formalismo matematico :
ΠNOME ( PERSONA )
Visualizza la sola colonna dei nomi della tabella PERSONA
ΠNOME , ETA
( PERSONA )
Visualizza le colonne nome ed età
ΠNOME , ETA , CLASSE
( PERSONA X SCUOLA )
Visualizza le colonne nome ,età ,classe della tabella prodotto
4
La SELEZIONE
Consente di estrarre delle tuple ( record , righe ) da una tabella
secondo definiti criteri di selezione.
ESEMPIO :
σNOME = ‘ LUCA ‘
( PERSONA )
Visualizza le righe della tabella PERSONA dove NOME è uguale a LUCA
σNOME = ‘ LUCA ‘ and
ETA > 10 ( PERSONA )
Visualizza le righe che contengono il NOME = LUCA se ha una età
superiore a 10 anni.
σNOME = ‘ LUCA ‘
or ETA > 10 ( PERSONA )
Visualizza le righe che contengono il NOME = LUCA e le righe di chi
ha una età superiore a 10 anni.
∏SESSO, CLASSE ( σNOME = ‘ LUCA ‘
or ETA > 10
( PERSONA X SCUOLA ))
Creo la tabella prodotto ( creazione virtuale ) , cerco al suo interno le
righe che contengono il nome LUCA o l’attributo età superiore a 10
anni e visualizzo di queste righe i soli attributi sesso e classe.
5
LA GIUNZIONE ( JOIN )
E’ l’operazione più utilizzata perché consente di creare interrogazioni
molto articolate che sfruttano le relazioni create tra le tabelle tramite
le chiavi primarie ed esterne.
CLASSIFICAZIONE :
•
•
•
•
Giunzione
Giunzione
Giunzione
Giunzione
interna ( inner join )
esterna ( outer join ) ( Non c’è in ACCESS )
esterna sinistra ( left outer join )
esterna destra ( right outer join )
La giunzione consente la creazione di una tabella virtuale partendo da
due tabelle che hanno in comune un attributo. L’ attributo in comune
nella pratica delle cose è una chiave primaria ( o un campo che
compone la chiave primaria ) in una tabella e la chiave esterna
dell’altra tabella che la lega alla chiave primaria contenuta nella prima
tabella.
ESEMPIO :
Tabella STUDENTE
COGNOME
NOME
Roldo
Gros
Rossi
Verdi
Bianchi
Toni
Piero
Mario
Lucia
Luca
CLASSE
(CE)
null *
5B
5B
2C
2C
* Privatista senza classe
Tabella CLASSE
CLASSE AULA
5B
11
3A
3
2C
21
4A
5
6
Tabella di Giunzione interna ( inner join )
COGNOME
Gros
Rossi
Verdi
Bianchi
NOME
Piero
Mario
Lucia
Luca
CLASSE
5B
5B
2C
2C
AULA
11
11
21
21
Formalismo matematico :
STUDENTE
CLASSE ≡
σclasse (ce)
= CLASSE
(
STUDENTE X CLASSE )
classe(ce) = CLASSE
Si visualizzano le righe della tabella prodotto ottenuta combinando le
tuple delle due tabelle che hanno chiave esterna = chiave primaria .
Tabella di Giunzione esterna sinistra
( left outer join )
COGNOME
Roldo
Gros
Rossi
Verdi
Bianchi
NOME
Toni
Piero
Mario
Lucia
Luca
CLASSE
null
5B
5B
2C
2C
AULA
null
11
11
21
21
Come la giunzione interna con in più le righe della tabella di sinistra
che non hanno alcuna relazione con la tabella di destra i cui campi
nella tabella sono posti a null .
7
Formalismo matematico :
STUDENTE
CLASSE ≡
σclasse (ce)
= CLASSE
(
STUDENTE X CLASSE )
classe(ce) = CLASSE
Tabella di Giunzione esterna destra
( right outer join )
COGNOME
null
null
Gros
Rossi
Verdi
Bianchi
NOME
null
null
Piero
Mario
Lucia
Luca
CLASSE
3A
4A
5B
5B
2C
2C
AULA
3
5
11
11
21
21
Come la giunzione interna con in più le righe della tabella di destra
che non hanno alcuna relazione con la tabella di sinistra i cui campi
nella tabella sono posti a null .
Formalismo matematico :
STUDENTE
CLASSE ≡
σclasse (ce)
= CLASSE
(
STUDENTE X CLASSE )
classe(ce) = CLASSE
Tabella di Giunzione esterna ( outer join )
Non è previsto da ACCESS anche se è comunque ottenibile tramite le
due giunzioni precedenti . Infatti risulta la unione delle due precedenti
giunzioni esterne ( destra e sinistra ).
Giunzione esterna = Giunzione destra + Giunzione sinistra
8
COGNOME
null
null
Roldo
Gros
Rossi
Verdi
Bianchi
NOME
null
null
Toni
Piero
Mario
Lucia
Luca
CLASSE
4A
3A
null
5B
5B
2C
2C
AULA
5
3
null
11
11
21
21
Formalismo matematico :
STUDENTE
CLASSE ≡ σclasse (ce) = CLASSE ( STUDENTE X CLASSE )
ESEMPI di Interrogazioni con l’ Algebra Relazionale :
classe(ce) = CLASSE
Quali sono gli alunni della 5B ?
∏cognome , nome( STUDENTI
( σCLASSE = 5B(
CLASSE
)))
classe(ce) = CLASSE
Quali sono gli alunni privatisti ?
Πcognome , nome ( σCLASSE = null (
STUDENTI
CLASSE
classe(ce)
= CLASSE
classe(ce)
= CLASSE
9
))
Fondamenti di SQL
Sostanzialmente esistono due modi per formulare una interrogazione
di un DATABASE , usare le QBE messe a disposizione dall’applicativo
in uso ( ACCESS ) o scrivere delle SELECT in SQL .
QBE ( query by example ) sono più facili e veloci da realizzare ,le
interrogazioni si ottengono impostando un esempio , ovvero
costruendo la tabella che l’interrogazione deve restituire.
SQL ( linguaggio di interrogazione ) richiede una maggiore attenzione
( la sintassi del linguaggio ) , per contro consente interrogazioni molto
più articolate e complesse.
Rappresentazione Grafica delle Relazioni tra le forme di Interrogazione*
SQL
QBE
ALGEBRA
RELAZIONALE
interrogazioni SQL ⊃ interrogazioni QBE ⊃ interrogazioni Algebra Rel.
*Nota : Lo schema delle relazioni si presta ad eventuali critiche
perché generalizza il concetto appoggiandosi sul DBMS ACCESS.
Lo sviluppo di SQL ( ad esempio SQL3 o SQL-OO ) rende difficile
definire una relazione tra le varie forme di interrogazione.
10
Come creare e aggiornare una TABELLA in SQL
Le seguenti istruzioni non sono utilizzabili in ACCESS , e riguardano il
DDL di un database. ACCESS mette a disposizione altri strumenti per
creare una tabelle e per aggiornarne il contenuto.
ESEMPIO di creazione :
create table STUDENTI
( Nome
char (20) not null ,
Cognome char (20) not null ,
Classe_app char ( 5 ),
primary key ( Nome , Cognome ),
foreign key ( Classe_app ) references CLASSE );
In grassetto le parole della sintassi SQL da utilizzare.
Gli attributi che formano la chiave primaria vanno definiti non nulli e
le chiavi esterne vanno referenziate ( link ) con la tabella a cui si
legano.
Aggiornamento di una tabella per inserimento di nuovi dati , ovvero di
nuove righe .
insert into STUDENTI values ( ‘Stefano’, ‘Cecchin’, ‘3A’)
Aggiornamento di una tabella per modifiche dei dati in essa
contenuti.
La classe 5B viene rinominata 5BLI , ecco come fare in SQL .
update STUDENTI
set Classe_app = ‘5BLI’
where
Classe_app = ‘5B’
Al vero l’istruzione update consente operazioni molto più complesse
di quella in esempio , ma questo esula dal corso di informatica
previsto per il Liceo Tecnologico , inoltre è un tecnicismo che di volta
in volta può variare a seconda della versione di SQL in uso.
11
La Selezione
E’ l’istruzione con cui si ottengono i risultati di tutte le possibili
interrogazioni , SQL realizza la selezione usando l’istruzione
SELECT.
Tipi di Select
Select su una sola tabella , ovvero selezione semplice
• Select su più tabelle , ovvero selezione con giunzione ( join )
• Select nidificate , ovvero select di select ( di select …)
Struttura della istruzione select
Logica della select
select
from
attributi o campi
tabella o tabelle
where condizione di selezione
Sintassi richiesta da SQL
select [ distinct ]
from
espr1,espr2,……,esprn
tab1 , tab2 ,…….., tabn
[ where condizione ]
Traduzione di select in algebra relazionale
Πespr1,…,esprn( σcondizione(
tab1 X tab2 …X tabn ))
12
L’uso della select sarà trattato tramite degli esempi che
utilizzano il seguente MODELLO ER e l’equivalente modello relazionale
( tabelle ).
compenso
partecipa
ARTISTI
SPETTACOLO
avviene
composto
LUOGO
MANIFESTAZIONE
MODELLO ER ( privo di attributi per semplicità grafica )
Tabelle del Modello Relazionale ( con attributi e chiavi )
Tartisti ( COD_SIAE , NomeCognome , Caratteristiche )
Tspettacolo ( TITOLOSpett , TitoloMan , NomeLuogo)
Tmanifestazione ( TITOLOMan , Organizzatore )
Tluogo ( NOMELuogo , Posti )
Tpartecipa ( COD_SIAE , TITOLOSpett , Compenso )
13
La select semplice
Visualizza il titolo degli spettacoli
select TITOLOSpett
from Tspettacolo
1. Visualizza l’intera tabella delle manifestazioni
select *
from Tmanifestazione
2. Visualizza il numero di posti di cui dispone il Teatro Regio
select Posti
from Tluogo
where NOMELuogo = “TeatroRegio”
3. Visualizza quanti sono i luoghi in cui si svolgono spettacoli
select COUNT(*) [ COUNT (distinct NOMELuogo ) ]
from Tluogo
4. Visualizza la somma dei compensi percepiti dall’artista con
codice siae uguale a VVFR23
select SUM( Compenso)
from Tpartecipa
where COD_SIAE = “VVFR23”
5. Visualizza I luoghi che dispongono di più di 300 posti
select NOMELuogo
from Tluogo
where Posti > 300
6. Visualizza il NomeCognome e le Caratteristiche di ogni artista
select NomeCognome , Caratteristiche
from Tartista
14
7. Visualizza quale è il compenso massimo corrisposto ad un artista
select
from
MAX( Compenso )
Tpartecipa
8. Visualizza quale è il compenso Massimo percepito dall’artista
con codice siae uguale a VV45Y
select MAX( Compenso )
from
Tpartecipa
where COD_SIAE = “VV45Y”
9. Visualizza quale è il compenso medio percepito dall’artista con
codice siae uguale a CC28
select AVG( Compenso )
from Tpartecipa
where COD_SIAE = “CC28”
10.Visualizza I codici siae degli artisti che hanno percepito un
compenso compreso tra 100 e 300 euro.
select COD_SIAE
from Tpartecipa
where Compenso between 100 and 300
[ Compenso > 100 and Compenso < 300 ]
11.Visualizza il titolo degli spettacoli che non afferiscono ad una
manifestazione
select TITOLOSpett
from Tspettacolo
where TitoloMan = null
[ TitoloMan is null ]
Questi sono degli esempi di interrogazione utilizzando la select su una singola tabella , sia essa
riferita ad una entità o generata tramite una relazione molti a molti. Tra parentesi quadre è
riportata una opzione equivalente , ovvero prevista da alcuni SQL.
15
La select con giunzione ( join )
Più complessa della precedente , sfrutta l’algebra relazionale vista
nelle precedenti lezioni.
Prendiamo in considerazione la parte del MODELLO ER relativa ad
artista-partecipa-spettacolo.
COD_SIAE
Compenso
TITOLOSpett
TITOLOSpett NomeLuogo
NomeCognome Caratteristich
e
Tartista
COD_SIAE
Tpartecipa
Tpartecipa
COD_SIAE
Tspettacolo
TITOLOSpett
( Tartista
(
Tpartecipa
COD_SIAE
TITOLOSpett
16
Tspettacolo ) )
In questo modo ottengo una giunzione tra artista e spettacolo
passando attraverso partecipa.
select NomeCognome , TITOLOSpett
from Tartista inner join
( Tpartecipa inner join Tspettacolo
on Tpartecipa.TITOLOSpett = Tspettacolo.TITOLOSpett )
on Tartista.COD_SIAE = Tpartecipa.COD_SIAE
where
NomeCognome
= “ Mario
Rossi “; a cui partecipa l’artista
La
select precedente
visualizza
gli spettacoli
Mario Rossi , la selezione espressa in termini di algebra relazionale
risulta :
ΠNomeCognome , TitoloSpett ( σNomeCognome=”Mario Rossi”( Tartista
( Tpartecipa
COD_SIAE
Tspettacolo )
TitoloSpett
Criterio con cui operare le select con giunzione
A
B
C
D
Si vuole porre in relazione
qualcosa di A con D , anche
se non sono legati da
nessuna relazione diretta.
A inner join
( B inner join
( C inner join D
on attributo comune tra C e D)
on attributo comune tra B e C)
on attributo comune tra A e C
17
La nidificazione degli inner join segue la logica dal più lontano al più
vicino e il DBMS svolge la selezione partendo dall’inner join più
interno ( ovvero quello più lontano ) , come nelle espressioni
algebriche.
ESEMPI
12.Visualizzare gli Spettacoli che compongono la manifestazione
avente come titolo “ Cinema Italiano “
select TITOLOSpett
from ( Tspettacolo inner join Tmanifestazione
on Tspettacolo.TitoloMan = Tmanifestazione.TITOLOMan
Where Tmanifestazione.TITOLOMan =” Cinema Italiano “ )
;
13.Visualizzare gli spettacoli a cui partecipa l’artista Luca Verdi ed
ordinali alfabeticamente.
select TITOLOSpett
from ( Tspettacolo inner join
( Tartista inner join Tpartecipa
on Tartista.COD_SIAE = Tpartecipa.COD_SIAE
where Tartista.NomeCognome =”Luca Verdi”)
on Tspettacolo.TITOLOSpett=Tpartecipa.TITOLOSpett)
order by asc ;
14.Visualizza quali luoghi ospitano la manifestazione “ Cinema
Italiano ”.
select NOMELuogo
from Tluogo inner join
( Tmanifestazione inner join Tspettacolo
on Tmanifestazione.TitoloSpett=Tspettacolo:TITOLOSpett
where Tmanifestazione.TitoloSpett = “Cinema Italiano”)
on Tluogo.NOMELuogo=Tspettacolo.NomeLuogo
;
18
15.Visualizza il NomeCognome degli artisti che partecipano alla
manifestazione “ Cinema Italiano “.
select NomeCognome
from Tartista inner join
( Tpartecipa inner join
( Tmanifestazione inner join Tspettacolo
on Tmanifestazione.TitoloSpett=Tspettacolo:TITOLOSpett
where Tmanifestazione.TitoloSpett = “Cinema Italiano”)
on Tpartecipa.TITOLOSpett=Tspettacolo:TITOLOSpett )
on Tartista.COD_SIAE=Tpartecipa.COD_SIAE
;
La select nidificata ( o select di select )
Si creano delle sottointerrogazioni che vengono utilizzate all’interno
di una select , ovviamente la sottointerrogazione è una select da cui la
struttura a select annidate una nell’altra.
ESEMPI
16.Visualizzare NomeCognome degli artisti in ordine alfabetico e
l’ammontare dei compensi percepiti, denominando la colonna .
select distinct NomeCognome , select ( SUM ( Compenso )) as
Ammontare
from Tartista inner join Tpartecipa
On Tartista.COD_SIAE=Tpartecipa.COD_SIAE
order by NomeCognome
17.Visualizzare NomeCognome degli artisti che hanno ricevuto
compensi maggiori al compenso medio.
select distinct NomeCognome
from Tartista inner join Tpartecipa
On Tartista.COD_SIAE=Tpartecipa.COD_SIAE
Where Compenso > ( select AVG ( Compenso )
from Tpartecipa )
order by Compenso
19
18.Visualizzare il titolo degli spettacoli che si svolgono in luoghi la
cui disponibilità di posti è maggiore di 200.
select TITOLOSpett
from Tpsettacolo inner join Tluogo
On Tspettacolo.NomeLuogo=Tluogo.NOMELuogo
where NOMELuogo in ( select NOMELuogo
from Tluogo
where Posti> 200 )
19.Visualizzare il NomeCognome degli artisti che hanno
partecipato ad almeno uno spettacolo.
select distinct NomeCognome
from Tartista
where exists ( select NomeCognome
from Tartista inner join Tpartecipa
on Tartista.COD.SIAE=Tpartecipa.COD_SIAE )
20.Visualizzare il titolo delle manifestazioni che non utilizzano
luoghi usati dalla manifestazione “ Cinema Italiano”
select TITOLOMan
from Tluogo inner join
( Tmanifestazione inner join Tspettacolo
on Tmanifestazione.TitoloSpett=Tspettacolo:TITOLOSpett
)
on Tluogo.NOMELuogo=Tspettacolo.NomeLuogo
where NOMELuogo not in
(
select NOMELuogo
from Tluogo inner join
( Tmanifestazione inner join Tspettacolo
on Tmanifestazione.TitoloSpett=Tspettacolo:TITOLOSpett
where Tmanifestazione.TitoloSpett = “Cinema Italiano” )
on Tluogo.NOMELuogo=Tspettacolo.NomeLuogo
);
20
Come realizzare ORDINAMENTI e RAGRUPPAMENTI in SQL
L’ordinamento si realizza con l’istruzione order by , e può essere
crescente ( asc ) o decrescente ( desc ) , per default l’ordinamento è
crescente.
select NOMELuogo
from Tluogo
where Posti > 200
order by Posti desc , NOMELuogo asc
I luoghi devono avere più di 200 posti e sono visualizzati dal luogo
con più posti , in caso di luoghi con pari numero di posti la
visualizzazione segue l’ordinamento alfabetico.
I raggruppamenti si realizzano con l’istruzione
group by attr1 , … , attrn [having condizione del gruppo ]
La tabella risultante è un insieme di gruppi , le righe che compongono
ogni gruppo soddisfano alla seguente logica :
“ Le righe stanno nello stesso gruppo se hanno gli stessi valori sugli
attributi attr1 , … , attrn e se rispettano la eventuale clausola presente
in having “
Esempi
21.Quali manifestazioni prevedono almeno due spettacoli.
select
TitoloMan , COUNT(*)
from
Tspettacolo
group by TitoloMan having COUNT(*) >= 2
22.Quanti spettacoli ospita ogni luogo.
select
NomeLuogo , COUNT(*)
from
Tspettacolo
group by NomeLuogo
21
23.Fare una graduatoria degli artisti , per compensi ricevuti
,aggiungendo la loro caratteristica.
select NomeCognome , Caratteristiche , SUM( Compenso ) as
Guadagno
from Tartista inner join Tpartecipa
on Tartista.COD_SIAE=Tpartecipa.COD_SIAE
group by NomeCognome , Caratteristiche
order by SUM(Compenso) desc
24.Fare una graduatoria degli artisti per partecipazioni agli
spettacoli.
select NomeCognome , COUNT(*)
from Tartista inner join Tpartecipa
on Tartista.COD_SIAE=Tpartecipa.COD_SIAE
group by NomeCognome
order by COUNT(*) desc
NOTA1 : Gli attributi presenti nella riga select devono essere gli stessi
presenti nella riga group by. Ammesso l’uso di COUNT(*) e le
operazioni di aggregazione sugli attributi.
NOTA2 : In alcuni SQL è previsto un ordinamento tramite l’istruzione
Top , che consente di estrarre i migliori , ad esempio top 10 consente
di visualizzare i migliori dieci. Si lascia al lettore consultare il manuale
in linea di ACCESS per avere maggiori ragguagli.
Gli appunti possono contenere degli errori ortografici , spero non
ci siano orrori di sintassi.
Gli esempi presenti negli appunti non sono stati testati e
potrebbero richiedere degli aggiustamenti alla sintassi ( ad
esempio l’uso del punto e virgola finale o racchiudere tra parentesi
quadre [] il nome delle tabelle ).
La struttura logica degli esempi è corretta (spero).
L’apprendimento dei fondamenti del linguaggio SQL richiede
esercizio ed “ una organizzazione mentale logica “ nella
formulazione delle interrogazioni.
22