SQL
1
INTRODUZIONE
2
DEFINIZIONE DEI DATI IN SQL
3
INTERROGAZIONI IN SQL
4
MANIPOLAZIONE DEI DATI IN SQL
5
ALTRE DEFINIZIONI DEI DATI IN SQL
6
CONTROLLO DELL’ACCESSO
7
SQL NEI LINGUAGGI DI PROGRAMMAZIONE
8
ESERCIZI
DEFINIZIONE DEI DATI IN SQL
1
DOMINI ELEMENTARI
2
DEFINIZIONE DI SCHEMA
3
DEFINIZIONE DELLE TABELLE
4
SPECIFICA DEI VALORI DI DEFAULT
5
VINCOLI INTRARELAZIONALI
6
VINCOLI INTERRELAZIONALI
7
MODIFICA DEGLI SCHEMI
8
DEFINIZIONI DI INDICI IN SQL
9
CATALOGHI RELAZIONALI
INTERROGAZIONI IN SQL
1
DICHIARATIVITÀ IN SQL
2
INTERROGAZIONI SEMPLICI
3
OPERATORI AGGREGATI
4
INTERROGAZIONI CON RAGGRUPPAMENTO
5
INTERROGAZIONI DI TIPO INSIEMISTICO
6
INTERROGAZIONI NIDIFICATE
MANIPOLAZIONE DEI DATI IN SQL
1
INSERIMENTO DI RIGHE
2
CANCELLAZIONE DI RIGHE
3
MODIFICA DELLE RIGHE
ALTRE DEFINIZIONI DEI DATI IN SQL
1
VINCOLI DI INTEGRITÀ GENERICI
2
ASSERZIONI
3
VISTE
CONTROLLO DELL’ACCESSO
1
2
RISORSE E PRIVILEGI
COMANDI PER CONCEDERE E REVOCARE PRIVILEGI
USO DI SQL NEI LINGUAGGI DI
PROGRAMMAZIONE
1
INTRODUZIONE
2
I PROBLEMI DI INTEGRAZIONE
3
CURSORI
4
SQL DINAMICO
SQL
INTRODUZIONE
SQL è un acronimo di Structured Query Language, un linguaggio di
interrogazione per basi di dati relazionali originariamente sviluppato per il
sistema relazionale System R.
SQL non è solo un linguaggio di interrogazione, ma contiene al suo interno
sia le funzionalità di un Data Definition Language, sia quelle di un Data
Manipulation Language.
La sua diffusione è dovuta in buona parte alla intensa opera di
standardizzazione che si è concentrata su questo linguaggio.
SQL
INTRODUZIONE
Notazione che verrà usata:
• Le parentesi quadre indicano che il termine all’interno è opzionale, ovvero
può non comparire o comparire una sola volta.
• Le parentesi graffe indicano che il termine racchiuso può non comparire o
essere ripetuto un numero arbitrario di volte.
• Le barre verticali indicano che deve essere scelto uno tra i termini separati
dalle barre; un elenco di termini in alternativa può essere racchiuso tra
parentesi angolate.
SQL
SQL
DEFINIZIONE
DEFINIZIONE
DEI
DEIDATI
DATIIN
IN SQL
SQL
I domini elementari
SQL mette a disposizione sei famiglie di domini elementari.
• Carattere
Il dominio carattere permette di rappresentare singoli caratteri oppure
stringhe. La lunghezza delle stringhe di caratteri può essere fissa o variabile, per
le stringhe di lunghezza variabile si indica la lunghezza massima
character [ varying ] [ ( lunghezza ) ]
[ character set NomeFamigliaCaratteri ]
Es: Stringa di 20 caratteri Character (20)
Stringa di caratteri dell’alfabeto greco a lunghezza variabile, di lunghezza
massima 1000
Character varying (1000) character set Greek
SQL
SQL
DEFINIZIONE
DEFINIZIONE
DEI
DEIDATI
DATIIN
IN SQL
SQL
• Bit
Viene utilizzato da attributi che possono assumere solo il valore 0 o il valore 1.
Bit [ varying ] [ ( lunghezza ) ]
Es:
Stringa di 5 bit
Bit (5)
Stringa di bit di lunghezza variabile e lungheza massima pari a 100
Bit varying (100)
SQL
SQL
DEFINIZIONE
DEFINIZIONE
DEI
DEIDATI
DATIIN
IN SQL
SQL
• Tipi numerici esatti
Per tipo numerico esatto si intende un dominio che permette di
rappresentare valori interi o valori decimali in virgola fissa.
In SQL ci sono quattro tipi numerici esatti
numeric [ ( Precisione [ , Scala ] ) ]
decimal [ ( Precisione [ , Scala ] ) ]
integer
smallint
I domini numeric e decimal rappresentano numeri in base decimale. Il
parametro Precisione specifica il numero di cifre significative usate, con un
dominio decimal(4) si possono rappresentare valori tra -9.999 e +9.999.
Mediante il parametro Scala indica quante cifre devono comparire dopo la
virgola. Se si vogliono rappresentare valori precisi sino al centesimo si
assegnerà a Scala il valore 2.
Es: Per rappresentare valori compresi tra -999,999 e +999,999
numeric (6,3)
SQL
SQL
DEFINIZIONE
DEFINIZIONE
DEI
DEIDATI
DATIIN
IN SQL
SQL
La differenza tra i domini numeric e decimal consiste nel fatto che la
precisione per il dominio numeric rappresenta un valore esatto, mentre per il
dominio decimal costituisce un requisito minimo.
Nei casi in cui non interessa avere una rappresentazione della parte
frazionaria e non è importante controllare in modo preciso la dimensione
della rappresentazione decimale allora diventa possibile usare i domini
predefiniti integer e smallint.
Per questi domini non esiste un vincolo sulla rappresentazione e sono basati
sulla rappresentazione interna binaria del calcolatore.
La precisione del dominio integer deve essere maggiore o uguale alla
precisione del dominio smallint.
SQL
SQL
DEFINIZIONE
DEFINIZIONE
DEI
DEIDATI
DATIIN
IN SQL
SQL
• Tipi numerici approssimati
Abbiamo i seguenti tipi
float [ ( Precisione ) ]
double precision
real
Tutti questi domini permettono di descrivere numeri approssimati mediante
una rappresentazione in virgola mobile, in cui a ciascun numero corrisponde
una coppia di valori: la mantissa e l’esponente.
Al dominio float può essere associata una precisione, numero di cifre della
mantissa, mentre la precisione dell’esponente dipende dall’implementazione.
Il dominio double precision dedica ad un numero approssimato una
rappresentazione di dimensione doppia rispetto a quella del dominio real.
SQL
SQL
DEFINIZIONE
DEFINIZIONE
DEI
DEIDATI
DATIIN
IN SQL
SQL
• Data e ora
Tale famiglia di domini permette di rappresentare istanti di tempo
date
time [ ( Precisione ) ] [ with time zone ]
timestamp [ ( Precisione ) ] [ with time zone ]
Ciascuno di questi domini è strutturato e decomponibile in un insieme di
campi. Il dominio date ammette i campi year, month e day, il dominio time
ammette i campi hour, minute e second, infine timestamp ammette tutti i
campi, da year a second. Con Precisione si rappresentano il numero di cifre
decimali che si devono utilizzare nella frazione di secondo. Se l’opzione with
time zone è specificata allora risulta possibile accedere a due campi
timezone_hour e timezone_minute che rappresentano la differenza di fuso
orario tra l’ora locale e l’ora universale.
Es:
21:03:04+1:00
20:03:04+0:00 corrispondono allo stesso istante
temporale, ma il primo rappresentato nell’ora solare italiana, il secondo
nell’ora universale
SQL
SQL
DEFINIZIONE
DEFINIZIONE
DEI
DEIDATI
DATIIN
IN SQL
SQL
• Intervalli temporali
interval PrimaUnitàDiTempo [ to UltimaUnitàDiTempo ]
PrimaUnitàDiTempo e UltimaUnitàDiTempo definiscono le unità di misura
che devono essere usate, dalla più precisa alla meno precisa.
E’ così possibile definire domini come interval year to month per indicare che
la durata dell’intervallo di tempo deve essere misurata in numero di anni e di
mesi, oppure interval day to second se deve essere misurata in giorni e secondi.
Si noti che l’insieme delle unità di misura è diviso in insiemi distinti: year e
month da una parte e day e second dall’altra poiché non si possono
paragonare esattamente i giorni coi mesi.
Es: interval year (5) to month permette di rappresentare intervalli fino a
99.999 anni e 11 mesi, mentre interval day (4) to second (6) sino a 9.999 giorni,
23 ore, 59 minuti e 59,999999 secondi
SQL
SQL
DEFINIZIONE
DEFINIZIONE
DEI
DEIDATI
DATIIN
IN SQL
SQL
Definizione di schema
L’SQL definisce uno schema di basi di dati come collezione di oggetti; ogni
schema è costituito da un insieme di famiglie di caratteri, domini, tabelle,
indici, asserzioni, viste e privilegi definito dalla seguente sintassi:
create schema [ NomeSchema ] | [authorization ] Autorizzazione ]
DefElementoSchema
Autorizzazione rappresenta il nome dell’utente proprietario dello schema,
Dopo la create schema compaiono le definizioni dei suoi componenti, ovvero
domini tabelle, asserzioni, viste e privilegi.
SQL
SQL
DEFINIZIONE
DEFINIZIONE
DEI
DEIDATI
DATIIN
IN SQL
SQL
Definizione delle tabelle
Una tabella SQL è costituita da una collezione ordinata di attributi e da un
insieme di vincoli
ES. Schema della tabella dipartimento
create table Dipartimento
( Nome
char(20) primary key,
Indirizzo char(50),
Città
char(20)
)
La sintassi per la definizione di tabelle è:
create table NomeTabella
( NomeAttributo Dominio [ ValoreDiDefault ] [ Vincoli ]
, NomeAttributo Dominio [ ValoreDiDefault ] [ Vincoli ]
AltriVincoli )
SQL
SQL
DEFINIZIONE
DEFINIZIONE
DEI
DEIDATI
DATIIN
IN SQL
SQL
Definizione dei domini:
Gli attributi possono appartenere a domini predefiniti del linguaggio o a
domini definiti dall’utente a partire dai domini predefiniti.
Partendo da domini elementari è possibile creare nuovi domini tramite la
primitiva di create domain.
create domain NomeDominio as TipoDiDato
[ ValoreDiDefault ]
[ Vincolo ]
SQL non mette a disposizione dei costruttori di tipo come il record o
l’array. E’ una caratteristica che deriva dal modello relazionale dei dati che
richiede che tutti gli attributi siano caratterizzati da un dominio elementare
SQL
SQL
DEFINIZIONE
DEFINIZIONE
DEI
DEIDATI
DATIIN
IN SQL
SQL
Specifica dei valori di default
Il valore di default è il valore che deve assumere l’attributo quando viene
inserita una riga nella tabella senza che sia specificato un valore per
l’attributo stesso. Quando il valore di default non è specificato, si assume
come default il valore nullo.
La sintassi è:
default < GenericoValore | user | null >
GenericoValore rappresenta un valore compatibile con il dominio.
L’opzione user impone come valore di default l’identificativo dell’utente che
esegue il comando di aggiornamento della tabella.
L’opzione null corrisponde al valore di default di base e può essere
utilizzato per annullare una precedente impostazione.
SQL
SQL
DEFINIZIONE
DEFINIZIONE
DEI
DEIDATI
DATIIN
IN SQL
SQL
Vincoli intrarelazionali
I più semplici vincoli intrarelazionali sono quelli che operano su un solo
attributo della relazione . In SQL essi sono : not null, unique e primary key.
• Not null
Il vincolo not null indica che il valore nullo non è ammesso come valore
dell’attributo; in tal caso l’attributo deve sempre essere specificato
Es: Cognome character (20) not null
SQL
SQL
DEFINIZIONE
DEFINIZIONE
DEI
DEIDATI
DATIIN
IN SQL
SQL
• Unique
Un vincolo unique si applica ad un attributo o un insieme di attributi di una
tabella e impone che i valori dell’attributo siano unici, ad eccezione del valore
nullo.
La definizione può avvenire in due modi:
• la prima quando si definisce il vincolo su un solo attributo; in questo caso si
fa seguire la specifica dell’attributo dalla parola chiave unique.
Es:
Matricola character (6) unique
• la seconda è necessaria quando bisogna definire il vincolo per un insieme di
attributi, in questo caso si usa la seguente espressione
unique ( Attributo , Attributo )
SQL
SQL
DEFINIZIONE
DEFINIZIONE
DEI
DEIDATI
DATIIN
IN SQL
SQL
• Primary key
Il vincolo primary key può essere specificato una sola volta per ogni tabella
( mentre è possibile utilizzare un numero arbitrario di volte i vincoli unique e
not null)
Gli attributi che fanno parte della chiave primaria non possono assumere il
valore nullo; pertanto la definizione di primary key implica per tutti gli
attributi della chiave primaria una definizione di not null, che può essere
omessa
SQL
SQL
DEFINIZIONE
DEFINIZIONE
DEI
DEIDATI
DATIIN
IN SQL
SQL
Vincoli interrelazionali
I vincoli interrelazionali più diffusi e significativi sono i vincoli di integrità
referenziale. In SQL c’è un costrutto apposito per la loro definizione, il
vincolo di foreign key.
Questo vincolo crea un legame tra i valori dell’attributo della tabella
corrente e i valori dell’attributo di un’altra tabella, imponendo che per ogni
riga della tabella il valore dell’attributo, se diverso da nullo, sia presente tra i
valori di un attributo delle righe appartenenti alla tabella esterna. Si impone
che l’attributo a cui si fa riferimento nella tabella esterna sia soggetto a un
vincolo unique.
SQL
SQL
DEFINIZIONE
DEFINIZIONE
DEI
DEIDATI
DATIIN
IN SQL
SQL
Il vincolo può essere definito in due modi:
• un primo modo fa uso del costrutto sintattico references, con il quale si
specificano la tabella esterna e l’attributo della tabella esterna con il quale
l’attributo deve essere legato
Es.
Create table Impiegato
(
Matricola
character(6) primary key,
Nome
character(20) not null,
Cognome
character(20) not null,
Dipart
character(15)
references Dipartimento (NomeDip),
Stipendio
numeric(9) default 0,
unique
(cognome,nome)
)
SQL
SQL
DEFINIZIONE
DEFINIZIONE
DEI
DEIDATI
DATIIN
IN SQL
SQL
• una definizione alternativa, quando il legame è rappresentato da un insieme
di attributi, fa uso del costrutto foreign key, con cui si elencano gli attributi
della tabella coinvolti nel legame
Es.
Create table Impiegato
(
Matricola
character(6) primary key,
Nome
character(20) not null,
Cognome
character(20) not null,
Dipart
character(15)
references Dipartimento (NomeDip),
Stipendio
numeric(9) default 0,
unique (cognome,nome)
foreign key (Nome,Cognome)
references Anagrafica (Nome,Cognome)
)
In questo caso, rispetto all’esempio precedente, si è imposto che gli
attributi Nome e Cognome devono comparire in una tabella anagrafica.
SQL
SQL
DEFINIZIONE
DEFINIZIONE
DEI
DEIDATI
DATIIN
IN SQL
SQL
Per i vincoli di integrità referenziale SQL permette di scegliere quale
reazione adottare quando viene rilevata una violazione.
Il vincolo può essere violato sia operando sulle righe della tabella interna
che sulle righe della tabella esterna.
Per le violazioni operando sulla tabella interna ( inserendo una nuova riga o
modificando il valore dell’attributo referente) non viene offerto un
particolare supporto, l’operazione viene semplicemente impedita.
Per le violazioni che avvengono operando sulla tabella esterna (cancellando
una riga o modificando il valore riferito) vengono invece offerte diverse
alternative rappresentate dai seguenti modi:
SQL
SQL
DEFINIZIONE
DEFINIZIONE
DEI
DEIDATI
DATIIN
IN SQL
SQL
per le operazioni di modifica
• cascade
Il nuovo valore dell’attributo della tabella esterna viene riportato su tutte le
corrispondenti righe della interna
• set null
All’attributo referente viene assegnato il valore nullo al posto del valore
modificato nella tabella esterna
• set default
All’attributo referente viene assegnato il valore di default al posto del
valore modificato nella tabella esterna
• no action
non viene eseguita nessuna reazione
SQL
DEFINIZIONE DEI DATI IN SQL
Per le violazioni prodotte dalla cancellazione di un elemento della tabella
esterna si hanno le seguenti reazioni:
• cascade:
tutte le righe della tabella interna corrispondenti alla riga cancellata
vengono cancellate;
• set null:
all’attributo referente viene assegnato il valore nullo al posto del valore
cancellato nella tabella esterna;
• set default:
all’attributo referente viene assegnato il valore di default al posto del
valore cancellato nella tabella esterna;
• no action:
non viene eseguita alcuna reazione
SQL
DEFINIZIONE DEI DATI IN SQL
La politica di reazione viene specificata dopo il vincolo d’integrità
Sintassi
Es.
on < delete | update >
< cascade | set null | set default | no action >
Create table Impiegato
(
Matricola
Nome
Cognome
Dipart
character(6)
character(20) not null,
character(20) not null,
character(15)
references Dipartimento (NomeDip),
Stipendio
numeric(9) default 0,
primary key (Matricola),
foreign key (Dipart) references Dipartimento (NnmeDip)
on delete set null
on update cascade
unique ( Cognome, Nome)
)
SQL
SQL
DEFINIZIONE
DEFINIZIONE
DEI
DEIDATI
DATIIN
IN SQL
SQL
Modifica degli schemi
Comando alter
Permette di modificare domini e schemi di tabelle, può assumere varie
forme
alter domain NomeDominio < set default ValoreDefault |
drop default |
add constraint DefVincolo |
drop constraint NomeVincolo >
alter table NomeTabella <
alter column NomeAttributo < set default NuovoDefault | drop default >|
add constraint DefVincolo |
drop constreint NomeVincolo |
add column DefAttributo |
drop column NomeAttributo >
Si noti che quando si definisce un nuovo vincolo, questo deve essere già
soddisfatto dai dati presenti, altrimenti l’inserimento viene respinto.
Es: alter table Dipartimenti add column NroUffici numeric(4)
SQL
SQL
DEFINIZIONE
DEFINIZIONE
DEI
DEIDATI
DATIIN
IN SQL
SQL
• Comando drop
Il comando drop permette di rimuovere dei componenti, schemi, domini,
tabelle, viste o asserzioni.
Sintassi
drop < schema | domain | table | view | assertion > NomeElemento
[ restrict | cascade ]
L’opzione restrict (di default) specifica che il comando non deve essere
eseguito in presenza di oggetti non vuoti, uno schema non é rimosso se
contiene tabelle o altri oggetti, un dominio non é rimosso se appare in qualche
definizione di tabella, una tabella non é rimossa se contiene righe o é presente
in qualche definizione, una vista non é rimossa se é utilizzata nella definizione
di altre tabelle o viste.
Con l’opzione cascade tutti gli oggetti devono essere rimossi. In generale
l’opzione cascade genera una reazione a catena, per cui tutti gli elementi che
dipendono da un elemento rimosso vengono rimossi.
Rimuovendo un dominio che compare nella definizione di qualche
attributo, l’opzione cascade fa sì che il nome del dominio venga rimosso, ma
gli attributi che sono stati definiti utilizzando quel dominio rimangono
associati alla medesima definizione di tipo.
SQL
SQL
DEFINIZIONE
DEFINIZIONE
DEI
DEIDATI
DATIIN
IN SQL
SQL
Definizione di indici in SQL
Mediante gli indici è possibile diminuire in modo determinante il tempo di
risposta alle richieste di interrogazione, a spese di un incremento della
complessità degli aggiornamenti.
Si noti che i comandi di definizione degli indici non fanno parte dello
standard del linguaggio.
Sintassi
create [ unique ] index NomeIndice on NomeTabella ( ListaAttributi )
Con questo comando si crea un indice NomeIndice sulla tabella
NomeTabella , operante sugli attributi elencati in ListaAttributi. L’ordine in
cui compaiono gli attributi nella lista è significativo: le righe nell’indice
vengono ordinate prima in base ai valori del primo attributo, e così in
sequenza fino all’ultimo attributo.
Per eliminare un indice si usa il comando drop index
Sintassi
drop index NomeIndice
SQL
SQL
DEFINIZIONE
DEFINIZIONE
DEI
DEIDATI
DATIIN
IN SQL
SQL
Cataloghi relazionali
La base di dati contiene due tipi di tabelle : quelle che contengono i dati e
quelle che contengono i metadati. Questo secondo insieme di tabelle
costituisce il catalogo delle basi di dati.
SQL
INTERROGAZIONI IN SQL
Dichiaratività di SQL
SQL esprime le interrogazioni in modo dichiarativo, ovvero si specifica
l’obiettivo dell’interrogazione e non il modo di ottenerlo.
Chi scrive interrogazioni in SQL può trascurare gli aspetti di traduzione ed
ottimizzazione, di cui si occuperà l’ottimizzatore di interrogazione.
Esistono in generale modi molto diversi per esprimere la stessa
interrogazione in SQL; il programmatore dovrà effettuare una scelta
basandosi non sull’efficienza ma sulla leggibilità e modificabilità
dell’interrogazione.
SQL
INTERROGAZIONI IN SQL
Interrogazioni semplici
Le operazioni di interrogazione in SQL vengono specificate per mezzo
dell’istruzione select
Struttura essenziale di una select
select ListaAttributi
from ListaTabelle
[ where Condizione ]
Le tre parti di cui si compone un’istruzione select vengono rispettivamente
chiamate target list, clausola from, e clausola where.
Sintassi completa
select AttrExpr [ [as ] Alias ] , AttrExpr [ [ as ] Alias ]
from Tabella [ [as ] Alias ] , Tabella [ [ as ] Alias ]
[ where Condizione ]
L’interrogazione SQL seleziona, tra le righe che appartengono al prodotto
cartesiano delle tabelle elencate nella clausola from, quelle che soddisfano le
condizioni espresse nell’argomento della clausola where. Se la clausola where
è assente si selezionano tutte le righe.
SQL
INTERROGAZIONI IN SQL
Il risultato dell’esecuzione di una interrogazione SQL è così una tabella con
una riga per ogni riga selezionata, con un insieme di colonne dato dalle
espressioni che compaiono nella target list, ciascuna eventualmente
ridenominata con l’Alias che compare immediatamente dopo l’espressione.
Si consideri una base di dati contenente la tabella
IMPIEGATO ( Nome,Cognome, Dipart, Ufficio, Stipendio)
Nome
Cognome
Dipart
Mario
Carlo
Giuseppe
Franco
Lorenzo
Paola
Marco
Carlo
Rossi
Bianchi
Verdi
Neri
Lanzi
Borroni
Franco
Rossi
Amministrazione
Produzione
Amministrazione
Distribuzione
Direzione
Amministrazione
Produzione
Direzione
Ufficio Stipendio
10
20
20
16
7
75
20
14
45
36
40
45
73
40
46
80
SQL
INTERROGAZIONI IN SQL
Interrogazione 1:
individuare lo stipendio degli impiegati di cognome “Rossi”.
Select Stipendio as Salario
from Impiegato
Impiegato
where Cognome = ‘Rossi’
Si ottiene il seguente risultato
Salario
45
80
Target list La target list specifica gli elementi dello schema della tabella
risultato
SQL
INTERROGAZIONI IN SQL
Interrogazione 2:
l’interrogazione recupera tutte le informazioni relative agli impiegati di
cognome “Rossi”.
Select *
Impiegato
from Impiegato
where Cognome = ‘ Rossi ‘
Risultato
Nome
Cognome
Dipart
Mario
Carlo
Rossi
Rossi
Amministrazione
Direzione
Ufficio Stipendio
10
14
45
80
SQL
INTERROGAZIONI IN SQL
Nella target list possono comparire generiche espressioni sul valore degli
attributi di ciascuna riga selezionata.
Interrogazione 3:
L’interrogazione restituisce lo stipendio mensile dell’impiegato di cognome
“Bianchi”.
Select Stipendio/12 as StipendioMensile
Impiegato
from Impiegato
where Cognome = ‘Bianchi’
Risultato
StipendioMensile
3,00
SQL
INTERROGAZIONI IN SQL
Clausola from Quando si desidera formulare un’interrogazione che
coinvolge righe appartenenti a più tabelle, si pone come argomento della
clausola from l’insieme di tabelle a cui si vuole accedere
DIPARTIMENTO (Nome, Indirizzo, Città)
Nome
Amministrazione
Produzione
Distribuzione
Direzione
Ricerca
Indirizzo
Via Tito Livio 27
P.le Lavater 3
Via Segre 9
Via Tito Livio 27
Via Morone 6
Città
Milano
Torino
Roma
Milano
Milano
SQL
INTERROGAZIONI IN SQL
DIPARTIMENTO (Nome, Indirizzo, Città)
Nome
Amministrazione
Produzione
Distribuzione
Direzione
Ricerca
Indirizzo
Città
Via Tito Livio 27
P.le Lavater 3
Via Segre 9
Via Tito Livio 27
Via Morone 6
Milano
Torino
Roma
Milano
Milano
IMPIEGATO ( Nome,Cognome, Dipart, Ufficio, Stipendio)
Nome
Cognome
Dipart
Mario
Carlo
Giuseppe
Franco
Lorenzo
Paola
Marco
Carlo
Rossi
Bianchi
Verdi
Neri
Lanzi
Borroni
Franco
Rossi
Amministrazione
Produzione
Amministrazione
Distribuzione
Direzione
Amministrazione
Produzione
Direzione
Ufficio Stipendio
10
20
20
16
7
75
20
14
45
36
40
45
73
40
46
80
SQL
INTERROGAZIONI IN SQL
Interrogazione 4:
Per ottenere una relazione in cui compaiono il nome dell’impiegato e la
città in cui lavora, si può formulare la seguente interrogazione:
select Impiegato.Nome, Impiegato.Cognome, Dipartimento.Città
from Impiegato, Dipartimento
where Impiegato.Dipart = Dipartimento.Nome
Risultato
Impiegato.Nome
Cognome
Città
Mario
Rossi
Milano
Carlo
Bianchi
Milano
Giuseppe
Verdi
Milano
Franco
Neri
Roma
Carlo
Rossi
Milano
Lorenzo
Lanzi
Milano
Paola
Borroni
Milano
Marco
Franco
Milano
Rispetto alla interrogazione precedente si nota l’uso dell’operatore punto
per identificare gli attributi, per cui ogni attributo viene specificato facendo
precedere al nome dell’attributo il nome della tabella a cui appartiene,
separando i due termini con un punto.
SQL
INTERROGAZIONI IN SQL
Interrogazione 5:
l’interrogazione precedente può esser fatta facendo uso degli alias per le
tabelle
select I.Nome, Cognome, Città
from Impiegato as I, Dipartimento as D
where Dipart = D.Nome
Clausola where La clausola where ammette come argomento una
espressione booleana costruita combinando predicati semplici con gli
operatori and, or, not.
SQL
INTERROGAZIONI IN SQL
Interrogazione 6:
Si vogliono ottenere il nome ed il cognome degli impiegati che lavorano
nell’ufficio ”20” del dipartimento “Amministrazione”.
select Nome, Cognome
from Impiegato
Impiegato
where Ufficio = 20 and Dipart = ‘Amministrazione’
Risultato
Nome
Giuseppe
Cognome
Verdi
SQL
INTERROGAZIONI IN SQL
Interrogazione 8:
Si vogliono trovare i nomi propri degli impiegati di cognome ‘Rossi’ che
lavorano nei dipartimenti ‘Amministrazione’ e ‘Produzione’
select Nome
from Impiegato
Impiegato
where Cognome = ‘Rossi’ and
( Dipart = ‘Amministrazione’ or Dipart = ‘Produzione’)
Risultato
Nome
Giuseppe
SQL
INTERROGAZIONI IN SQL
SQL mette a disposizione un operatore like per il confronto di stringhe che
supporta la coppia di caratteri speciali _ (trattino sottolineato) e % (
percentuale )
Il primo carattere speciale può rappresentare nel confronto un carattere
arbitrario, il secondo una stringa di un numero arbitrario di caratteri
arbitrari
Interrogazione 9:
Si trovino gli impiegati che hanno un cognome che ha una ‘o’ in seconda
posizione e finisce per ‘i’.
Select *
from Impiegato
where Cognome like ‘_o%i’
Nome
Cognome
Mario
Carlo
Paola
Rossi
Rossi
Borroni
Dipart
Amministrazione
Direzione
Amministrazione
Ufficio Stipendio
10
14
75
45
80
40
SQL
INTERROGAZIONI IN SQL
Gestione dei valori nulli
Per selezionare i termini con valori nulli si utilizza il predicato is null.
Attributo is [ not ] null
Interpretazione algebrica delle interrogazioni SQL
E’ possibile costruire una corrispondenza tra le interrogazioni SQL ed
equivalenti interrogazioni espresse in algebra relazionale.
Data una interrogazione SQL nella forma più semplice
select T1.Attributo11,…., Th.Attributohm
from Tabella1 T1,….., Tabellan Tn
where Condizione
si può costruire una interrogazione equivalente in algebra relazionale
utilizzando la seguente traduzione:
 T1.Attributo11,…., Th.Attributohm  Condizione (Tabella1 X ……X Tabellan)
Per interrogazioni SQL più complicate la formula di conversione non è più
direttamente applicabile.
SQL
INTERROGAZIONI IN SQL
Duplicati
In SQL si possono avere piú righe uguali. L’eliminazione dei duplicati é
specificata con la parola chiave distinct, da porre immediatamente dopo la
chiave select. La sintassi prevede che si possa anche specificare la parola
chiave all al posto di distinct, indicando che si intendono mantenere tutti i
duplicati.
PERSONA(CodFiscale, Nome, Cognome, Cittá)
CodFiscale
Nome
Cognome
Cittá
RSSMRA55B21T234J
BNCCLR69T30II745Z
RSSGNN41A31B344C
RSSPRT75C12F205V
Mario
Carlo
Giovanni
Pietro
Rossi
Bianchi
Rossi
Rossi
Verona
Roma
Verona
Milano
SQL
INTERROGAZIONI IN SQL
Interrogazione 10:
select Cittá
Persona
from Persona
where Cognome = ‘Rossi’
Risultato
Cittá
Verona
Verona
Milano
SQL
INTERROGAZIONI IN SQL
Interrogazione 11:
select distinct Cittá
from Persona
Persona
where Cognome = ‘Rossi’
Risultato
Cittá
Verona
Milano
SQL
INTERROGAZIONI IN SQL
Join interni ed esterni :
sintassi
select AttrExpr [ [ as ] Alias ] { , AttrExpr [ [ as ] Alias ] }
from Tabella [ [ as ] Alias ]
{, TipoJoin join Tabella [ [ as ] alias ] on CondizioneDiJoin}
[ where AltraCondizione ]
Il parametro TipoJoin specifica quale é il tipo di join da usare, ad esso si
possono sostituire i termini inner, right, left, o full.
L’inner join seleziona solo le righe del prodotto cartesiano per cui la
condizione é vera.
Interrogazione 12:
L’interrogazione 44 puó essere riscritta utilizzando la sintassi dell’inner join
L’interrogazione
nel seguente modo
select I.Nome, Cognome, Cittá
from Impiegato I inner join Dipartimento D on Dipart = D.Nome
SQL
INTERROGAZIONI IN SQL
Il join esterno ha il compito di eseguire un join mantenendo peró tutte le
righe che fanno parte di una o entrambe le tabelle coinvolte, esistono tre tipi
di join esterni:
• Il left join fornisce come risultato il join interno esteso con le righe della
relazione che compare a sinistra nel join per le quali non esiste una
corrispondente riga nella tabella di destra.
• Il right join restituisce invece, oltre al join interno, le righe escluse della
relazione di destra.
• Il full join restituisce il join interno esteso con le righe escluse di entrambe
le relazioni.
SQL
INTERROGAZIONI IN SQL
Tabella GUIDATORE ( Nome, Cognome, NroPatente )
Nome
Cognome
Mario
Carlo
Marco
Rossi
Bianchi
Neri
NroPatente
VR 2030020Y
PZ 1012436B
AP 4544442R
Tabella AUTOMOBILE ( Targa, Marca, Modello, NroPatente)
Targa
Marca
Modello NroPatente
AB 652 FF
AA 652 FF
BJ 747 XX
BB 421 JJ
Fiat
Fiat
Lancia
Fiat
Punto
Brava
Delta
Uno
VR 2030020Y
VR 2030020Y
PZ 1012436B
MI 2020030U
SQL
INTERROGAZIONI IN SQL
Interrogazione 13:
select *
Automobile on
from Guidatore
Guidatore left join Automobile
( Guidatore.NroPatente=Automobile.NroPatente)
Risultato
Nome
Mario
Mario
Carlo
Marco
Cognome NroPatente
Rossi
Rossi
Bianchi
Neri
Targa
VR 2030020Y AB 574 WW
VR 2030020Y AA 652 FF
PZ 1012436B BJ 747 XX
AP 454442R
NULL
Marca Modello
Fiat
Lancia
Lancia
NULL
Punto
Delta
Delta
NULL
SQL
INTERROGAZIONI IN SQL
Interrogazione 14:
select *
from Guidatore full join Automobile on
( Guidatore.NroPatente=Automobile.NroPatente)
Risultato
Nome
Mario
Mario
Carlo
Marco
NULL
Cognome NroPatente
Rossi
Rossi
Bianchi
Neri
NULL
Targa
VR 2030020Y AB 574 WW
VR 2030020Y AA 652 FF
PZ 1012436B BJ 747 XX
AP 454442R
NULL
MI 2020030U BB 421 JJ
Marca Modello
Fiat
Lancia
Lancia
NULL
Fiat
Punto
Delta
Delta
NULL
Uno
SQL
INTERROGAZIONI IN SQL
Uso di variabili:
Ricorrendo a brevi alias si puó far riferimento ad una tabella in modo
compatto, inoltre utilizzando gli alias é possibile far riferimento a piú
esemplari della stessa tabella.
Interrogazione 16:
Si vogliono trovare tutti gli impiegati che hanno lo stesso cognome ( ma
diverso nome ) degli impiegati del dipartimento ‘Produzione’.
select I1.Cognome, I1.Nome
from Impiegato
Impiegato I1, Impiegato I2
where I1.Cognome = I2.Cognome and
I1.Nome <> I2.Nome and
I2.Dipart = ‘Produzione’
SQL
INTERROGAZIONI IN SQL
Ordinamento
SQL permette di specificare un eventuale ordinamento delle righe del
risultato di un interrogazione. Tale ordinamento é specificato tramite la
clausola order by, con la quale si chiude l’interrogazione.
La clausola segue la seguente sintassi
order by AttrDiOrdinamento [ asc | desc ]
{ , AttrDiOrdinamento [ asc | desc ] }
SQL
INTERROGAZIONI IN SQL
Interrogazione 18:
Restituire il contenuto della tabella AUTOMOBILE ordinato in base alla
marca ( in modo decrescente ) e al modello.
select *
from Automobile
order by Marca desc, Modello
Targa
Marca
Modello
BJ 747 XX
AA 652 FF
AB 547 WW
BB 421 JJ
Lancia
Fiat
Fiat
Fiat
Delta
Brava
Punto
Uno
NroPatente
PZ 1012436B
VR 2030020Y
VR 2030020Y
MI 2020030U
SQL
INTERROGAZIONI IN SQL
Operatori aggregati
Spesso si devono valutare delle proprietà che dipendono da insiemi di tuple.
Ad esempio supponiamo si voglia determinare il numero degli impiegati
del dipartimento “Produzione”. Per esprimerla in SQL usiamo l’operatore
aggregato di conteggio count.
Select count *
from Impiegato
where Dipart = ‘Produzione’
Gli operatori aggregati vengono gestiti come un’estensione delle normali
interrogazioni. Prima viene eseguita l’interrogazione, considerando solo le
parti from e where. L’operatore aggregato viene poi applicato alla tabella
contenente il risultato dell’operazione.
SQL
INTERROGAZIONI IN SQL
Lo standard SQL prevede cinque operatori aggregati, divisibili in due
gruppi, count da una parte, e sum, max, min e avg dall’altra.
L’operatore count usa la seguente sintassi
count ( < * | [ distinct | all ] ListaAttributi > )
La prima opzione (*) restituisce il numero di righe: l’opzione distinct invece
restituisce il numero di diversi valori degli attributi in ListaAttributi; l’opzione
all invece restituisce il numero di righe che possiedono valori diversi dal
valore nullo per gli attributi in ListaAttributi.
Gli altri operatori aggregati invece ammettono come argomento un
attributo o un’espressione, eventualmente preceduta dalla parola chiave
distinct o all.
Le funzioni aggregate sum e avg ammettono come argomento solo
espressioni che rappresentano valori numerici o intervalli di tempo. Le
funzioni max e min richiedono solamente che sull’espressione sia definito un
ordinamento, per cui si possono applicare anche su stringhe di caratteri o su
istanti di tempo.
< sum | max | min | avg > ( [ distinct | all ] AttrEspr )
SQL
INTERROGAZIONI IN SQL
Gli operatori hanno il seguente significato:
• Sum:
restituisce la somma dei valori posseduti dall’attributo su tutte le righe;
Interrogazione 22:
Restituisce la somma degli stipendi del dipartimento “Amministrazione”
select sum(Stipendio)
from Impiegato
where Dipart = ‘Amministrazione’
• Max e min:
restituiscono rispettivamente il massimo e il minimo valore tra quelli di
ciascuna riga
Interrogazione 23:
restituisce il massimo stipendio tra quelli degli impiegati che lavorano in
un dipartimento con sede a Milano
select max(Stipendio)
from Impiegato, Dipartimento
where Dipart = NomeDip and Città = ‘Milano’
SQL
INTERROGAZIONI IN SQL
Avg:
restituisce la media dei valori dell’attributo.
• La sintassi SQL prevede che quando come argomento della select
compaiono delle funzioni aggregate, allora non possono comparire espressioni
al livello di riga, come ad esempio il nome di un attributo
La seguente interrogazione non è corretta
select Cognome, Nome, max(Stipendio)
from Impiegato, Dipartimento
where Dipart = NomeDip and Città = ‘Milano’
SQL
INTERROGAZIONI IN SQL
Interrogazioni con raggruppamento
Spesso sorge l’esigenza di applicare l’operatore aggregato distintamente a
sottoinsiemi di righe, raggruppando quelle che possiedono gli stessi valori per un
insieme di attributi dato. Questo insieme è l’insieme di attributi che compare come
argomento della clausola group by.
Interrogazione 26:
select Dipart, sum(Stipendio)
from Impiegato
group by Dipart
Supponiamo che la tabella inizialmente sia questa
Nome
Cognome
Dipart
Mario
Carlo
Giuseppe
Franco
Lorenzo
Paola
Marco
Carlo
Rossi
Bianchi
Verdi
Neri
Lanzi
Borroni
Franco
Rossi
Amministrazione
Produzione
Amministrazione
Distribuzione
Direzione
Amministrazione
Produzione
Direzione
Ufficio Stipendio
10
20
20
16
7
75
20
14
45
36
40
45
73
40
46
80
SQL
INTERROGAZIONI IN SQL
Per prima cosa l’interprete esegue la clausola where come se la clausola
gruop by non esistesse. E’ come se venisse eseguita l’interrogazione
select Dipart, Stipendio
from Impiegato
con il seguente risultato
Dipart
Dipart
Amministrazione
Produzione
Amministrazione
Distribuzione
Direzione
Amministrazione
Produzione
Direzione
Stipendio
Stipendio
45
36
40
45
73
40
46
80
SQL
INTERROGAZIONI IN SQL
La tabella ottenuta viene poi analizzata, dividendo le righe in insiemi
caratterizzati dallo stesso valore degli attributi che compaiono come
argomento della clausola group by.
Dipart
Amministrazione
Amministrazione
Amministrazione
Produzione
Produzione
Distribuzione
Direzione
Direzione
Stipendio
45
40
40
36
46
45
73
80
SQL
INTERROGAZIONI IN SQL
Dopo che le righe sono state raggruppate in sottoinsiemi, l’operatore
aggregato viene applicato separatamente su ogni sottoinsieme.
Dipart
Amministrazione
Produzione
Distribuzione
Direzione
SumStipendio
125
82
45
153
La sintassi SQL impone che in una interrogazione che fa uso della clausola
group by, possano comparire come argomento della select solamente un
sottoinsieme degli attributi utilizzato per il raggruppamento delle righe e
funzioni aggregate valutate sugli attributi.
SQL
INTERROGAZIONI IN SQL
Predicati sui gruppi:
Una applicazione può avere bisogno di considerare solo i sottinsiemi che
soddisfano a certe condizioni. La clausola having descrive le condizioni che si
devono applicare al termine dell’esecuzione di una interrogazione che fa uso
della clausola group by.
Interrogazione 30:
Si vogliono trovare i dipartimenti che spendono di più di 100 milioni in
stipendi.
Select Dipart, sum(Stipendio) as SommaStipendi
from Impiegato
group by Dipart
having sum(Stipendio) > 100
SQL
INTERROGAZIONI IN SQL
Applicando l’interrogazione alla tabella IMPIEGATO si procede seguendo
gli stessi passi descritti per l’interrogazione 26. Dopo aver raggruppato le
righe in base allo stesso valore dell’attributo Depart , viene valutato il
predicato argomento della clausola having , che seleziona i dipartimenti per
cui la somma degli stipendi è superiore a 100 milioni.
Dipart
Amministrazione
Direzione
SommaStipendio
125
153
Solo i predicati in cui compaiono operatori aggregati devono essere
argomento della clausola having.
SQL
INTERROGAZIONI IN SQL
Interrogazioni di tipo insiemistico
Gli operatori insiemistici disponibili sono union (unione), intersect
(intersezione) ed except (differenza).
Sintassi
SelectSQL { < union | intersect | except > [ all ] SelectSQL }
Gli operatori si possono applicare solo al risultato delle interrogazioni
all’esterno della select.
Gli operatori insiemistici assumono come default di eseguire sempre una
eliminazione dei duplicati.
Qualora si vogliano preservare i duplicati basterá affiancare all’operatore
la parola chiave all.
SQL
INTERROGAZIONI IN SQL
Interrogazione 32:
si vogliono determinare nome e cognomi degli impiegati.
Select Nome
from Impiegato
Impiegato
union
select Cognome
from Impiegato
Risultato
L’interrogazione ottiene dapprima i valori dell’attributo Nome per le righe
di IMPIEGATO, ricava quindi i valori dell’attributo Cognome per le stesse
righe e infine costruisce la tabella risultato unendo i due risultati parziali.
SQL
INTERROGAZIONI IN SQL
Interrogazione 33:
applichiamo la semantica con duplicati
Select Nome
from Impiegato
Impiegato
where Dipart <> ‘Amministrazione’
union all
select Cognome
from Impiegato
where Dipart <> ‘Amministrazione’
Risultato
SQL
INTERROGAZIONI IN SQL
Nome
Mario
Carlo
Giuseppe
Franco
Lorenzo
Paola
Marco
Rossi
Bianchi
Verdi
Neri
Lanzi
Borroni
Risultato interrogazione 32
SQL
INTERROGAZIONI IN SQL
Nome
Carlo
Franco
Carlo
Lorenzo
Marco
Bianchi
Neri
Rossi
Lanzi
Franco
Risultato interrogazione 33
SQL
INTERROGAZIONI IN SQL
Interrogazione 34:
Per trovare i cognomi di impiegati che sono anche nomi si puó formulare la
seguente interrogazione:
select Nome
from Impiegato
Impiegato
intersect
select Cognome
Impiegato
from Impiegato
Risultato
Nome
Franco
SQL
INTERROGAZIONI IN SQL
Interrogazione 35:
Trovare i nomi degli impiegati che non sono cognomi di qualche impiegato.
Select Nome
from Impiegato
Impiegato
except
select Cognome
from Impiegato
Nome
Mario
Carlo
Giuseppe
Lorenzo
Paola
Marco
SQL
INTERROGAZIONI IN SQL
Interrogazioni nidificate
SQL ammette anche l’uso di predicati con una struttura piú complessa, in
cui si confronta un valore con il risultato dell’esecuzione di un’interrogazione
SQL.
Si parla in questo caso di interrogazioni nidificate.
Se in un predicato si confrontano un attributo con il risultato di
un’interrogazione sorge il problema di disomogeneitá dei termini del
confronto. La soluzione offerta da SQL consiste nell’estendere con le parole
chiave all o any i normali operatori di confronto relazionale. La parola chiave
any specifica che la riga soddisfa la condizione se risulta vero il confronto tra
il valore dell’attributo per la riga ed almeno uno degli elementi restituiti
dall’interogazione. La parola chiave all invece specifica che la riga soddisfa la
condizione solo se tutti gli elementi restituiti dall’interrogazione nidificata
rendono vero il confronto.
SQL
INTERROGAZIONI IN SQL
Interrogazione 36:
si vogliono selezionare gli impiegati che lavorano in dipartimenti situati a
Firenze
select *
from Impiegato
where Dipart = any ( select Nome
from Dipartimento
where Cittá = ‘Firenze’ )
L’interrogazione seleziona le righe di IMPIEGATO per cui il valore
dell’attributo Dipart é uguale ad almeno uno dei valori dell’attributo Nome
delle righe di DIPARTIMENTO.
SQL
INTERROGAZIONI IN SQL
Interrogazione nidificate complesse
Una interpretazione molto semplice ed intuitiva per leggere le
interrogazioni nidificate consiste nell’assumere che l’interrogazione nidificata
venga eseguita prima di analizzare le righe dell’interrogazione esterna.
Talvolta peró l’interrogazione nidificata fa riferimento al contesto
dell’interrogazione che la racchiude, allora si costruisce prima il prodotto
cartesiano delle tabelle e si applicano successivamente a ciascuna riga del
prodotto le condizione che compaiono nella clausola where mantenendo solo
le righe per cui la condizione viene valutata vera.
Operatore logico exists
Questo operatore ammette come paramentro una interrogazione nidificata
e restituisce il valore vero solo se l’interrogazione fornisce un risultato non
vuoto.
SQL
INTERROGAZIONI IN SQL
Interrogazione 44:
Si ha una relazione he descrive dati anagrafici con il seguente schema:
PERSONA ( CodFiscale, Nome , Cognome, Cittá ). Si determinino le persone
che hanno degli omonimi ( stesso nome e cognome ma diverso codice fiscale)
select *
from Persona P
where exists ( select *
from Persona P1
where P1.Nome = P.Nome and
P1.Cognome = P.Cognome and
P1.CodFiscale <> P.CodFiscale )
Si nota che in questo caso non risulta possibile eseguire l’interrogazione
nidificata prima di valutare l’interrogazione piú esterna, a causa della
variabile P. Deve venire valutata prima l’interrogazione piú esterna e per ogni
singola riga si deve valutare l’interrogazione nidificata.
SQL
MANIPOLAZIONE DEI DATI IN SQL
Inserimento di righe
Vi sono due sintassi alternative
insert into NomeTabella [ ListaAttributi ]
< values ( ListaDiValori ) | SelectSQL >
La prima forma permette di inserire singole righe all’interno delle tabelle.
L’argomento della clausola values rappresenta esplicitamente i valori degli
attributi della singola riga
ES
insert into Dipartimento ( NomeDip, Cittá )
values ( ‘Produzione’, ‘Torino’ )
SQL
MANIPOLAZIONE DEI DATI IN SQL
La seconda forma invece permette di aggiungere degli insiemi di righe.
Il seguente comando inserisce nella tabella PRODOTTIMILANESI il
risultato della selezione della relazione PRODOTTO di tutte le righe aventi
“Milano” come valore dell’attributo LuogoProd
insert into ProdottiMilanesi
( select codice, descrizione
from Prodotto
where LuogoProd = ‘Milano’ )
SQL
MANIPOLAZIONE DEI DATI IN SQL
Cancellazione di righe
Il comando delete elimina righe dalle tabelle della base di dati, seguendo la
sintassi :
delete from NomeTabella [ where Condizione ]
Qualora esista un vincolo di integritá referenziale con politica di cascade in
cui la tabella viene referenziata, allora la cancellazione di righe dalla tabella
puó comportare la cancellazione di righe appartenenti ad altre tabelle .
SQL
MANIPOLAZIONE DEI DATI IN SQL
Modifica di righe
update NomeTabella
set Attributo = < Espressione | SelectSQL | null | default >
{ , Attributo = < Espressione | SelectSQL | null | default > }
[ where Condizione ]
Il comando update permette di agiornare uno o piú attributi delle righe di
NomeTabella che soddisfano l’eventuale Condizione.
SQL
ALTRE DEFINIZIONI DI DATI IN SQL
Vincoli di integritá generici
check ( Condizione )
Le condizioni che si possono usare sono le condizioni che possono apparire
come argomento della clausola select di una interrogazione SQL.
I vincoli predefiniti possono essere descritti con la clausola check.
Ex
check ( Cognome is not null )
SQL
ALTRE DEFINIZIONI DI DATI IN SQL
Asserzioni
Rappresentano dei vincoli che non sono associati a nessun attributo o
tabella in particolare, ma appartengono direttamente allo schema..
Sintassi
create assertion NomeAsserzione check (Condizione)
Un’asserzione può ad esempio imporre che in IMPIEGATO sia sempre
presente almeno una riga:
create assertion AlmenoUnImpiegato
check (1 <= ( select count (*)
from Impiegato))
SQL
ALTRE DEFINIZIONI DEI DATI IN SQL
Viste
Le viste vengono definite in SQL associando un nome ed una lista di
attributi al risultato dell’esecuzione di una interrogazione
Si definisce una vista utilizzando il comando:
create view NomeVista [ ( ListaAttributi ) ] as SelectSQL
[ with [ local | cascaded ] check option ]
La clausola check option puó essere utilizzata solo nel contesto di viste
aggiornabili, sulle quali é possibile fare operazioni di modifica.
Lo standard SQL permette di aggiornare una vista solo quando una sola
riga di ciascuna tabella di base corrisponde a una riga della vista.
L’opzione local o cascaded specifica nel caso in cui una vista sia definita in
termini di altre viste, se il controllo sul fatto che le righe vengano rimosse
dalla vista debba essere effettuato solo all’ultimo livello ( si controlla solo che
la modifica non faccia violare la condizione della vista piú esterna) o se deve
essere propagato a tutti i livelli di definizione ( si controlla che le righe che su
cui si apportano modifiche non scompaiano a causa della violazione di una
qualsiasi delle condizioni delle viste coinvolte).
SQL
ALTRE DEFINIZIONI DEI DATI IN SQL
Es.
create view ImpiegatiAmmin(Matricola, Nome,
Cognome, Stipendio ) as
select Matricola, Nome , Cognome, Stipendio
From Impiegato
where Dipart = ‘Amministrazione’ and Stipendio > 10
Le viste in SQL possono anche servire per formulare delle interogazioni che
non sarebbero altrimenti esprimibili, aumentando il potere espressivo del
linguaggio. In generale le viste possono essere considerate come uno
strumento che permette di estendere la possibilità di nidificare le
interrogazioni.
SQL
ALTRE DEFINIZIONI DEI DATI IN SQL
Supponiamo di voler determinare qual è il dipartimento che spende il
massimo in stipendi.
Definiamo una vista che verrà usata dalla successiva interrogazione
create view BudgetStipendi ( Dip, TotaleStipendi) as
select Dipart, sum(Stipendio)
from Impiegato
group by Dipart
INTERROGAZIONE 49
Determinare il dipartimento caratterizzato dal massimo della somma degli
stipendi
select Dip, TotaleStipendi
from BudgetStipendi
where TotaleStipendi = ( select max(TotaleStipendi)
from BudgetStipendi)
SQL
ALTRE DEFINIZIONI DEI DATI IN SQL
La definizione della vista BUDGETSTIPENDI costruisce una tabella in cui
compare una riga per ogni dipartimento.
L’attributo Dip corrisponde all’attributo Dipart di IMPIEGATO e contiene
il nome del dipartimento, mentre il secondo attributo TotaleStipendi contiene
il risultato della valutazione della somma degli stipendi di tutti gli impiegati
facenti capo a quel dipartimento.
SQL
CONTROLLO DELL’ACCESSO
RISORSE E PRIVILEGI
Uno dei compiti piú importanti dell’amministratore di basi di dati consiste
nello scegliere ed implementare opportune politiche di controllo di accesso
Le risorse che il sistema protegge normalmente sono tabelle, ma si puó
proteggere un qualsiasi componente del sistema, come atributi di una tabella,
viste e domini.
SQL offre dei meccanismi di gestione flessibili, mediante i quali é possibile
specificare quali sono le risorse a cui devono accedere gli utenti e quali sono le
risorse che devono essere mantenute private. Il sistema basa il controllo di
accesso su un concetto di privilegio.
Ogni privilegio é caratterizzato dalla risorsa a cui si riferisce, dall’utente
che concede il privilegio, dall’utente che lo riceve, dall’azione che viene
permessa sulla risorsa e infine dal fatto che il privilegio puó essere trasmesso
o meno ad altri utenti.Quando una risorsa viene creata, il sistema concede
automaticamente tutti i privilegi su tale risorsa al creatore. L’utente _system
possiede tutti i privilegi su tutte le risorse.
SQL
CONTROLLO DELL’ACCESSO
I privilegi disponibili sono i seguenti:
• insert:
permette di inserire un nuovo oggetto nella risorsa.
• update :
permette di aggiornare il valore di un oggetto.
• delete :
permette di rimuovere oggetti dalla risorsa.
• select :
permette di leggere la risorsa, ovvero di utilizzarla nell’ambito di una
interrogazione.
• references :
permette che venga fatto un riferimento a una risorsa nell’ambito della
definizione dello schema di una tabella.
• usage :
permette che venga usata la risorsa ma solo per risorse come i domini.
SQL
CONTROLLO DELL’ACCESSO
Comandi per concedere o revocare privilegi
La sintassi del comando grant é la seguente:
grant Privilegi on Risorsa to Utenti [ with grant option ]
Il comando permette di concedere i Privilegi sulla Risorsa agli Utenti.
ES
grant select on Dipartimento to Stefano
concede all’utente“Stefano”
DIPARTIMENTO.
il
privilegio
di
select
sulla
tabella
La clausola grant option specifica se deve essere concesso a “Stefano” anche
il privilegio di propagare il privilegio ad altri utenti.
SQL
CONTROLLO DELL’ACCESSO
Il comando revoke sottrae ad un utente i privilegi che gli erano stati
concessi.
Revoke Privilegi on Risorsa from Utenti [ restrict | cascade ]
L’unico utente che puó sottrarre privilegi ad un altro utente é l’utente che
aveva concesso i privilegi in primo luogo .
L’opzione restrict é il valore di default e specifica che il comando non deve
essere eseguito qualora la revoca dei privilegi all’utente comporti qualche
altra revoca di privilegi. (Privilegi propagati).
Con l’opzione cascade invece si forza l’esecuzione del comando ( con revoca
dei privilegi propagati e rimozione degli elementi costruiti sfruttando tali
privilegi).
SQL
USO DI SQL NEI LINGUAGGI DI PROGRAMMAZIONE
L’uso piú tipico di una base di dati avviene attraverso applicazioni integrate
nel sistema informativo.
L’uso di apposite applicazioni invece che dell’interprete SQL per accedere
alle informazioni é giustificato da una serie di motivazioni: ridurre la
complesitá nell’accesso alla base di dati, fare rappresentazioni piú adeguate.
Gli strumenti che vengono utilizzati per raggiungere questi scopi sono i
linguaggi della quarta generazione .
SQL
USO DI SQL NEI LINGUAGGI DI PROGRAMMAZIONE
Problemi di integrazione
I tradizionali linguaggi di programmazione ad alto livello sono linguaggi
procedurali, mentre SQL é un linguaggio dichiarativo, in cui si specificano le
carateristiche del risultato ma non il modo in cui ottenerlo. Per utilizzare
istruzioni SQL all’interno di programmi procedurali, le istruzioni SQL
vengono incapsulate all’interno del programma. Bisognerá quindi dotare il
compilatore del linguaggio ad alto livello di un preprocessore il cui compito
sará quello di rilevare le chiamate dei servizi del DBMS.
Un secondo problema consiste nel fatto che i linguaggi di programmazione
accedono agli elementi di una tabella scandendone man mano le righe (tuple
oriented), al contrario SQL é un linguaggio di tipo set-oriented che opera
sempre su intere tabelle e mai su singole righe.
Questo problema ammette due soluzioni differenti, la piú usata si basa
sull’uso dei cursori.
SQL
USO DI SQL NEI LINGUAGGI DI PROGRAMMAZIONE
CURSORI
Un cursore é uno strumento che permette a un programma di accedere alle
righe di una tabella una riga per volta.
Definizione di un cursore
declare NomeCursore [ scroll ] cursor for SelectSQL
[ for < read only | update [ of Attributo { , Attributo } ] > ]
Il comando declare definisce un cursore, associato ad una particolare
interrogazione sulla base di dati. L’opzione scroll specifica se si vuole
permettere al programma di muoversi liberamente sul risultato
dell’interrogazione. L’opzione for update specifica se il cursore deve essere
utilizzato nell’ambito di un comando di modifica, permettendo di specificare
gli attributi che saranno oggetto del comando di update.
SQL
USO DI SQL NEI LINGUAGGI DI PROGRAMMAZIONE
open NomeCursore
Il comando open ha come argomento un cursore definito nello stesso
modulo.
Al momento dell’esecuzione del comando di open, l’interrogazione viene
eseguita, e il risultato diventa accessibile tramite l’istruzione fetch
fetch [ Posizione from ] NomeCursore into ListaDi Fetch
Il comando fetch prende una riga del cursore e la ripone nelle variabili del
programma che compaiono in ListaDiFetch.
SQL
USO DI SQL NEI LINGUAGGI DI PROGRAMMAZIONE
Si possono apportare modifiche alla base di dati tramite l’uso di cursori
con i comandi update e delete.
update NomeTabella
set Attributo = < Espressione | null | default >
{ , Attributo = < Espressione | null | default > }
where current of NomeCursore
delete from NuovaTabella where current of NomeCursore
Il comando close chiude il cursore, comunica al sistema che il risultato
dell’interrogazione non serve piú.
close NomeCursore
SQL
USO DI SQL NEI LINGUAGGI DI PROGRAMMAZIONE
Es
declare CursoreImpiegati scroll cursor for
select Cognome, Nome, Stipendio
from Impiegato
where Stipendio < 100 and Stipendio > 40
SQL
USO DI SQL NEI LINGUAGGI DI PROGRAMMAZIONE
Vediamo un esempio di programma C che fa uso dei cursori.
Ipotizziamo che i comandi SQL vengano identificati ponendo un carattere
di ‘$’sulla prima linea, mentre le variabili del programma vengano
rappresentate nei comandi SQL con il nome preceduto dal carattere ‘:’ ( due
punti ). Le variabili devono essere dichiarate di tipo compatibile ai valori che
dovranno contenere. Per riconoscere quando il cursore é terminato si fa uso
di una variabile predefinita sqlcode, che contiene il valore zero se l’esecuzione
dell’ultimo comando non ha prodotto problemi, un valore diverso da zero in
caso contrario.
SQL
USO DI SQL NEI LINGUAGGI DI PROGRAMMAZIONE
Void VisualizzaStipendioDipart(char NomeDip[])
{
char Nome[20], Cognome[20];
long int Stipendio;
$ declare ImpDip cursor for
select Nome, Cognome, Stipendio
from Impiegato
where Dipart = :NomeDip;
$ open ImpDip;
$ fetch ImpDip into ;Nome, ;Cognome, ;Stipendio;
printf(“Dipartimento %s \n”, NomeDip);
while (sqlcode == 0)
{
printf(“Nome e cognome dell’impiegato: %s %s”, Nome, Cognome);
printf(“Attuale stipendio: %d\n”, Stipendio);
$
fetch ImpDip into :Nome, :Cognome, :Stipendio;
}
$ close cursor ImpDip;
}
SQL
USO DI SQL NEI LINGUAGGI DI PROGRAMMAZIONE
SQL dinamico
Spesso sorge la necessitá di permettere all’utente di formulare delle
interrogazioni arbitrarie sulla base di dati, quindi alle volte che l’utente abbia
bisogno di effettuare delle interrogazioni caratterizzate da estrema
variabilitá, con differenze non solo nei paramentri usati, ma anche nella
struttura delle interrogazioni e nell’insieme di tabelle a cui si accede. I
meccanismi per l’invocazione di comandi SQL statico non vanno bene in
questo contesto.
Una prima alternativa consiste nel permettere all’utente di accedere
direttamente all’interprete SQL della base di dati.
SQL
USO DI SQL NEI LINGUAGGI DI PROGRAMMAZIONE
SQL DINAMICO
Una seconda alternativa consiste nell’uso di SQL dinamico .
Questo mecanismo richiede un supporto particolare da parte del sistema.
Il problema principale che deve essere affrontato é costituito dal passaggio
dei parametri tra il programma e il comando SQL.
Nel caso di SQL statico i comandi SQL vengono passati a un compilatore
che analizza la struttura del comando e ne costruisce una traduzione nel
linguaggio interno del sistema. In questo modo ogni volta che il programma
richiede l’esecuzione del comando questo puó essere passato diretamente
all’esecutore senza dover essere tradotto dall’interprete SQL.
In SQL dinamico si cerca di ottenere quando possibile questi vantaggi con
due modalitá di interazione: o eseguire direttamente l’interrogazione o
gestirla in due fasi, prima analisi e poi esecuzione.
SQL
ESERCIZIO 1
DECIMAL (10) :
INTEGER
:
DECIMAL (9) :
NUMERIC (12,4) :
DECIMAL (6,1) :
SMALLINT
:
9.999.999.999
4.294.967.295
999.999.999
99.999.999,9999
99.999,9
65.535
= 232-1
=216-1
SQL
ESERCIZIO 2
CREATE DOMAIN Nuovo Dominio AS
CHARACTER VARYING (256)
DEFAULT ‘sconosciuto’
NOT NULL
SQL
ESERCIZIO 3
Create table Fondista
(
Nome character (20) primary key
Nazione character (20)
Etá
numeric (3)
)
Create table Gara
(
Nome
Luogo
Nazione
Lunghezza
)
character (20) primary key
character (20)
character (20)
numeric(5)
SQL
ESERCIZIO 3
Create table Gareggia
(
NomeFondista character (20)
references Fondista (Nome)
NomeGara
character (20)
references Gara (Nome)
Piazzamento
numeric (4)
primary key ( NomeFondista, NomeGara)
)
SQL
ESERCIZIO 4
Create table Autore
(
Nome
character (20)
Cognome
character (20)
DataNascita date
Nazionalitá character (20)
primary key (Nome, Cognome)
)
Create table Libro
(
TitoloLibro
character (20) primary key
NomeAutore
character (20)
CognomeAutore character (20)
Lingua
character (20)
foreign key (NomeAutore, CognomeAutore)
references Autore ( Nome, Cognome)
on delete cascade
on update set null )
SQL
ESERCIZIO 5
Supponiamo di partire dalla seguente base di dati
AUTORE
NOME COGNOME
Mario
Rossi
Enrico
Eco
Filippo Calvino
LIBRO
TITOLOLIBRO
Montagne
Sentimenti
Fiori
DATANASCITA
15-04-55
23-12-45
21-11-44
NAZIONALITA’
Italiana
Italiana
Italiana
NOMEAUTORE COGNOMEAUTORE LINGUA
Mario
Rossi
Italiana
Enrico
Eco
Italiana
Filippo
Calvino
Italiana
Vediamo le successive modifiche conseguenti alle azioni fatte:
SQL
ESERCIZIO 5
Col delete vengono eliminati in Autori tutti gli autori col
cognome Rossi ed in seguito alla politica di cascade sulla
cancellazione vengono eliminati tutti i libri il cui autore ha
come cognome Rossi
AUTORE
NOME COGNOME
Enrico
Eco
Filippo
Calvino
DATANASCITA NAZIONALITA’
23-12-45
Italiana
21-11-44
Italiana
LIBRO
TITOLOLIBRO NOMEAUTORE COGNOMEAUTORE LINGUA
Sentimenti
Enrico
Eco
Italiana
Fiori
Filippo
Calvino
Italiana
SQL
ESERCIZIO 5
Con l’update la modifica viene fatta solo se esiste un autore nella tabella
Autori che di nome fa Umberto e di cognome Eco, altrimenti l’operazione
viene impedita.
AUTORE
NOME COGNOME
Enrico
Eco
Filippo Calvino
DATANASCITA NAZIONALITA’
23-12-45
Italiana
21-11-44
Italiana
LIBRO
TITOLOLIBRO NOMEAUTORE COGNOMEAUTORE LINGUA
Sentimenti
Enrico
Eco
Italiana
Fiori
Filippo
Calvino
Italiana
SQL
ESERCIZIO 5
Con l’insert viene inserita una nuova riga in Autore dove il nome é Antonio
ed il cognome Bianchi.
AUTORE
NOME
Enrico
Filippo
Antonio
COGNOME DATANASCITA NAZIONALITA’
Eco
23-12-45
Italiana
Calvino
21-11-44
Italiana
Bianchi
NULL
NULL
LIBRO
TITOLOLIBRO NOMEAUTORE COGNOMEAUTORE LINGUA
Sentimenti
Enrico
Eco
Italiana
Fiori
Filippo
Calvino
Italiana
SQL
ESERCIZIO 5
Con l’update Autore viene messo in Autore il nome Italo nella riga dove
c’é Calvino come cognome e nella relazione Libri vengono messi a null
quegli attributi che riferivano all’autore Calvino prima della modifica
del nome.
AUTORE
NOME
Enrico
Italo
Antonio
COGNOME DATANASCITA NAZIONALITA’
Eco
23-12-45
Italiana
Calvino
21-11-44
Italiana
Bianchi
NULL
NULL
LIBRO
TITOLOLIBRO NOMEAUTORE COGNOMEAUTORE LINGUA
Sentimenti
Enrico
Eco
Italiana
Fiori
NULL
NULL
Italiana
SQL
ESERCIZIO 6
Con l’ “alter table Tabella…….” viene eliminato il default 5, quindi se non
viene specificato alcun valore per l’attributo Dominio il valore di default é 10.
Con l’ “alter domain Dominio………” viene eliminato il valore di default
dell’attributo Dominio ( che era 10 ), quindi se non viene specificato il valore
dell’attributo Dominio in mancanza di specifica del valore di default viene
messo NULL.
Con il “drop domain ……” non succede nulla perche’ Dominio é definito
all’interno di Tabella, quindi essendo restrict l’opzione di default di drop il
dominio puó essere cancellato solo se non é definito all’interno di nessuna
tabella.
SQL
ESERCIZIO 9
Void ModificaStipendio
{
long int Stipendio;
$ declare ModStip cursor for
select Stipendio
from Impiegato
where Stipendio<30 or Stipendio>30;
$ open ModStip;
$ fetch ModStip into :Stipendio;
while (sqlcode = = 0)
{
if Stipendio<30
then
$
update Impiegati
set Stipendio = :Stipendio * 1.1
where current of ModStip;
else
$
update Impiegati
set Stipendio = :Stipendio / 0.5
where current of ModStip;
}
$ close cursor ModStip
}
La scelta del cursore é necessaria
perché se avessimo messo
i due comandi di modifica distinti, ad
esempio prima
aumentare gli stipendi inferiori ai 30
milioni e poi diminuire
quelli superiori ai 30 milioni, si
sarebbe creato una situazione
di cofusione poiché uno stipendio
che risultava nella
fascia inferiore essendo aumentato
poteva risultare
nella fascia superiore ed essere
successivamente erroneamente
diminuito.
Il lavoro va quindi fatto riga per riga
e questo rende
obbligatorio l’uso del cursore
SQL
ESERCIZIO 10
Create table Impiegato
(
Dipartimento character(20)
check ( 100> ( select count ( Amministrazione)
from Impiegati
where Dipartimento = ‘Amministrazione’)
and
40< ( select avg(stipendio)
from Impiegati
where Dipartimento = ‘Amministrazione)),
Cognome
character (20)
Nome
character (20)
Stipendio
numeric (9)
primary key (Cognome, Nome)
)
SQL
ESERCIZIO 11
Check Stipendio (
(
select max(Stipendio)
from Impiegati
where Sede = ‘Firenze’
)
< all
(
select Stipendio
from Impiegati
where Dipartimento = ‘Direzione’
)
)
SQL
ESERCIZIO 12
Create view MediaStip ( Dipartimento, MediaStipendio ) as
select Dipartimento, avg(Stipendio)
from Impiegati
group by Dipartimento
Create view MediaStipSupMedia ( Dipartimento, MediaStipSup) as
select Dipartimento, avg(MediaStipendio)
from MediaStip
group by Dipartimento
SQL
ESERCIZIO 13
Create view PerCarlo ( Nome,Cognome,Matricola, Dipartimento) as
select (Nome, Cognome, Matricola, Dipartimento)
from Impiegato
Grant select on PerCarlo to Carlo
SQL
ESERCIZIO 14
• Stefano dá la posibilitá a Paolo e Riccardo di accedere ai dati di Tabella con
la possibilitá di dare il privilegio anche ad altri utenti.
• Paolo dá la possibilitá a Piero di accedere ai dati di Tabella
• Riccardo dá la possibilitá a Piero di accedere ai dati di Tabella con la
possibilitá di prorogare ad altri tale possibilitá
• Stefano revoca a Paolo la possibilitá di accedere ai dati di Tabella con
l’opzione cascade che revoca tale privilegio anche a Piero che l’aveva ricevuto
da Paolo ( Da notare che a Piero rimane il privilegio ottenuto da Riccardo)
• Piero concede la possibilitá di accedere ai dati di Tabella a Paolo
• Stefano revoca il privilegio di accedere ai dati di Tabella a Riccardo, il
privilegio é revocato sia a Piero che a Paolo.
SQL
ESERCIZI
ESERCIZIO 1.
Ordinare i seguenti domini in base al valore massimo rappresentabile,
supponendo che integer abbia una rappresentazione a 32 bit e smallint a 16
bit: numeric (12,4), decimal (10), decimal (9), integer, smallint, decimal (6,1).
Possibile soluzione.
ESERCIZIO 2.
Definire un attributo che permetta di rappresentere stringhe di lunghezza
massima pari a 256 caratteri, su cui non sono ammessi valori nulli e con
valore di default “sconosciuto”.
Possibile soluzione.
SQL
ESERCIZI
ESERCIZIO 3.
Si diano le definizioni SQL delle tabelle FONDISTA(Nome, Nazione, Etá),
GAREGGIA(NomeFondista, NomeGara, Piazzamento) e GARA(Nome,
Luogo, Nazione, Lunghezza), rappresentando in particolare i vincoli di
foreign key della tabella GAREGGIA.
Possibile soluzione.
ESERCIZIO 4.
Si diano le definizioni SQL delle tabelle AUTORE(Nome, Cognome,
DataNascita, Nazionalitá), LIBRO(TitoloLibro, NomeAutore,
CognomeAutore, Lingua). Per il vincolo di foreign key specificare una
politica di cascade sulle cancellazioni e di set null sulle modifiche.
Possibile soluzione.
SQL
ESERCIZI
ESERCIZIO 5.
Dato lo schema dell’esercizio precedente, spiegare cosa puó capitare con
l’esecuzione dei seguenti comandi di aggiornamento:
delete from Autore
where Cognome ‘Rossi’
update Libro set Nome = ‘Umberto’
where Cognome = ‘Eco’
insert into Autore (Nome, Cognome)
values ( ‘Antonio’, ‘Bianchi’)
update Autore set Nome = ‘Italo’
where Cognome = ‘Calvino’
Soluzione
SQL
ESERCIZI
ESERCIZIO 6.
Date le definizioni:
create domain Dominio integer default 10
create table Tabella ( Attributo Dominio default 5 )
si indichi cosa avviene in seguito ai comandi:
alter table Tabella alter column Attributo drop default
alter domain Dominio drop default
drop domain Dominio
Soluzione
SQL
ESERCIZI
ESERCIZIO 7.
Dato il seguente schema:
AEROPORTO:(Cittá, Nazione, NumPiste)
VOLO:(IdVolo, GiornoSett, CittáPart, OraPart, CittáArr, OraArr,
TipoAereo)
AEREO:( TipoAereo, NumPasseggeri, QtaMerci)
Tabelle
Scrivere le interrogazioni SQL che permettono di determinare:
1. Le cittá con un aeroporto di cui non é noto il numero di piste. soluzione
2. Le nazioni da cui parte e arriva il volo con codice AZ274. soluzione
3. I tipi di aereo usati nei voli che partono da Torino. soluzione
4. I tipi di aereo ed il corrispondente numero di passeggeri per i tipi di
aereo usati nei voli che partono da Torino. Se la descrizione dell’aereo non é
disponibile, visualizzare solamente il tipo. soluzione
5. Le cittá da cui partono i voli internazionali. soluzione
SQL
ESERCIZI
6. Le cittá da cui partono voli diretti a Bologna, ordinate alfabeticamente. soluzione
7. Il numero di voli internazionali che partono il giovedí da Napoli. soluzione
8. Il numero di voli internazionali che partono ogni settimana da cittá italiane
( in due modi: facendo comparire o meno gli aeroporti senza voli
internazionali) soluzione
9. Le cittá francesi da cui partono piú di tre voli alla settimana diretti in Italia.
soluzione
10. Gli aeroporti italiani che hanno solo voli interni. Rappresentare questa
interrogazione in due modi:
• con un’interrogazione nidificata con l’operatore not in soluzione
• con un’interrogazione nidificata con l’operatore not exists soluzione
11. Le cittá che sono servite dall’aereo caratterizato dal massimo numero di
passeggeri soluzione
12. Il massimo numero di passeggeri che possono arrivare in un aeroporto
italiano dalla Francia di giovedí ( se vi sono piú voli, si devono sommare i
passeggeri) soluzione
SQL
ESERCIZI
ESERCIZIO 8.
Dato il seguente schema:
DISCO(Nroserie, TitoloAlbum, Anno, Prezzo)
CONTIENE(NroSerieDisco, CodiceReg, NroProgr)
ESECUZIONE(CodiceReg, TitoloCanz, Anno)
AUTORE(Nome, TitoloCanzone)
CANTANTE(NomeCantante, CodiceReg)
Tabelle
Formulare le interrogazioni SQL che permettono di determinare:
1. I cantautori ( persone che hanno scritto e cantato la stessa canzone) il cui
nome inizia per D. soluzione
2. I titoli dei dischi che contengono canzoni di cui non si conosce l’anno di
registrazione . soluzione
3. I pezzi del disco con numero di serie 78574, ordinati per numero
progressivo, con indicazione degli interpreti per i pezzi che hanno associato
un cantante. soluzione
SQL
ESERCIZI
4. Gli autori e i cantanti puri, ovvero autori che non hanno mai registrato
una canzone e cantanti che non hanno mai scritto una canzone. soluzione
5. I cantanti del disco che contiene il maggior numero di canzoni. soluzione
6. Dischi in cui tutte le canzoni sono di un solo cantante e in cui almeno tre
registrazioni sono di anni precedenti la pubblicazione del disco. soluzione
7. I cantanti che non hanno mai registrato una canzone come solisti. soluzione
8. I cantanti che hanno sempre registrato canzoni come solisti. soluzione
ESERCIZIO 9.
Dare una sequenza di comandi di aggiornamento che modifichi l’attributo
Stipendio della tabella IMPIEGATO, aumentando del 10% gli stipendi sotto i
30 milioni e diminuendo del 5% gli stipendi sopra i 30 milioni.
Possibile soluzione.
ESERCIZIO 10.
Definire sulla tabella IMPIEGATO il vincolo che il dipartimento
“Amministrazione” abbia meno di 100 dipendenti, con uno stipendio medio
superiore ai 40 milioni.
Possibile soluzione.
SQL
ESERCIZI
ESERCIZIO 11.
Definire a livello di schema il vincolo che il massimo degli stipendi degli
impiegati di dipartimenti con sede a Firenze sia minore dello stipendio di tutti
gli impiegati del dipartimento “Direzione”.
Possibile soluzione.
ESERCIZIO 12.
Definire una vista che mostra per ogni dipartimento il valore medio degli
stipendi superiori alla media.
Possibile soluzione.
ESERCIZIO 13.
Tramite la definizione di una vista, permettere all’utente “Carlo” di
accedere al contenuto di IMPIEGATO, escludendo l’attributo Stipendio.
Possibile soluzione.
SQL
ESERCIZI
ESERCIZIO 14.
Descrivere l’effetto delle seguenti istruzioni: quali autorizzazioni sono
presenti dopo ciascuna istruzione? ( Ciascuna riga é preceduta dal nome
dell’utente che esegue il comando)
Stefano : grant select on Tabella to Paolo, Riccardo with grant option
Paolo : grant select on Tabella to Piero
Riccardo : grant select on Tabella to Piero with grant option
Stefano : revoke select on Tabella from Paolo cascade
Piero : grant select on Tabella to Paolo
Stefano : revoke select on Tabella from Riccardo cascade
Soluzione
SQL
ESERCIZIO 7
SELECT Città
FROM Aeroporto
WHERE NumPiste = Null;
SQL
ESERCIZIO 7
SELECT A1.Nazione, A2.Nazione
FROM Volo, Aeroporto AS A1, Aeroporto AS A2
WHERE Volo.IdVolo = "AZ274" AND
Volo.CittàPart = A1.Città AND
Volo.CittàArr = A2.Città;
SQL
ESERCIZIO 7
SELECT DISTINCT TipoAereo
FROM Volo
WHERE CittàPart = 'Torino';
SQL
ESERCIZIO 7
SELECT DISTINCT Volo.TipoAereo, Aereo.NumPasseggeri
FROM Volo, Aereo
WHERE Volo.TipoAereo = Aereo.TipoAereo AND
Volo.CittàPart= 'Torino';
SQL
ESERCIZIO 7
SELECT DISTINCT A1.Città
FROM Aeroporto AS A1, Volo AS V1, Aeroporto AS A2
WHERE V1.CittàPart = A1.Città AND
V1.CittàArr = A2.Città AND
A1.Nazione<>A2.Nazione;
SQL
ESERCIZIO 7
SELECT DISTINCT CittàPart
FROM
Volo
WHERE
CittàArr = 'Bologna'
ORDER BY CittàPart;
SQL
ESERCIZIO 7
SELECT COUNT (*)
FROM Volo, Aeroporto
WHERE Volo.CittàPart = 'Napoli'
AND
Volo.GiornoSett = 'Giovedi'
AND
Volo.CittàArr = Aeroporto.Città AND
Aeroporto.Nazione <> 'Italia';
SQL
ESERCIZIO 7
SELECT Volo.CittàPart, COUNT (*)
FROM Volo, Aeroporto AS A1, Aeroporto AS A2
WHERE Volo.CittàPart = A1.Città AND
Volo.CittàArr = A2.Città AND
A1.Nazione = 'Italia'
AND
A2.Nazione <> 'Italia'
GROUP BY Volo.CittàPart;
SQL
ESERCIZIO 7
SELECT A1.Città
FROM Volo, Aeroporto AS A1, Aeroporto AS A2
WHERE Volo.CittàPart = A1.Città AND
Volo.CittàArr = A2.Città AND
A1.Nazione = 'Francia' AND
A2.Nazione= 'Italia'
GROUP BY A1.Città
HAVING COUNT (*) >3;
SQL
ESERCIZIO 7
SELECT DISTINCT Volo.CittàPart
FROM Volo
WHERE Volo.CittàPart NOT IN
( SELECT Volo.CittàPart
FROM Volo, Aeroporto A1, Aeroporto A2
WHERE Volo.CittàPart = A1.Città AND
A1.Nazione <> 'Italia'
OR
Volo.CittàArr = A2.Città AND
A2.Nazione <> 'Italia');
SQL
ESERCIZIO 7
SELECT A1.Città
FROM Aeroporto AS A1
WHERE NOT EXISTS
(SELECT DISTINCT A1.Città
FROM Aeroporto AS A2, Volo AS V1
WHERE V1.CittàPart = A1.Città AND
V1.CittàArr = A2.Città
AND
(( A1.Nazione = 'Italia' AND
A2.Nazione<> 'Italia’ )
OR
( A1.Nazione <> 'Italia')));
SQL
ESERCIZIO 7
SELECT V1.CittàPart, V1.CittàArr
FROM Volo AS V1, Aereo AS A1
WHERE V1.TipoAereo = A1.TipoAereo AND
A1.NumPasseggeri =
(SELECT MAX(Aereo.NumPasseggeri)
FROM Aereo);
SQL
ESERCIZIO 7
SELECT TOP 1 V1.CittàArr, SUM(AE1.NumPasseggeri) AS TOT
FROM Aeroporto AS A1, Aeroporto AS A2, Aereo AS AE1, Volo AS V1
WHERE V1.CittàPart = A1.Città AND
V1.CittàArr = A2.Città AND
V1.TipoAereo = AE1.TipoAereo AND
V1.GiornoSett = 'Giovedi' AND
A1.Nazione = 'Francia' AND
A2.Nazione = 'Italia'
GROUP BY V1.CittàArr;
SQL
ESERCIZIO 8
SELECT Autore.Nome
FROM Autore, Cantante, Esecuzione
WHERE Autore.Nome = Cantante.NomeCantante AND
Esecuzione.TitoloCanz = Autore.TitoloCanzone AND
Esecuzione.CodiceReg = Cantante.CodiceReg AND
Autore.Nome LIKE "D*";
SQL
ESERCIZIO 8
SELECT Disco.TitoloAlbum
FROM Disco, Contiene, Esecuzione
WHERE Disco.NroSerie = Contiene.NroSerieDisco AND
Contiene.CodiceReg = Esecuzione.CodiceReg AND
Esecuzione.Anno = NULL;
SQL
ESERCIZIO 8
SELECT Esecuzione.TitoloCanz, Cantante.NomeCantante
FROM Contiene, Esecuzione, Cantante
WHERE Contiene.NroSerieDisco = '78574' AND
Contiene.CodiceReg = Esecuzione.CodiceReg AND
Cantante.CodiceReg = Esecuzione.CodiceReg
ORDER BY Contiene.NroProgr;
SQL
ESERCIZIO 8
SELECT DISTINCT Autore.Nome
FROM Autore
WHERE Autore.Nome NOT IN
( SELECT Autore.Nome
FROM Cantante
WHERE Autore.Nome = Cantante.NomeCantante)
UNION
SELECT Cantante.NomeCantante
FROM Cantante
WHERE Cantante.NomeCantante NOT IN
(SELECT Cantante.NomeCantante
FROM Autore
WHERE Autore.NomeCantante.NomeCantante);
SQL
ESERCIZIO 8
SELECT DISTINCT Cantante.NomeCantante
FROM Cantante, Contiene
WHERE Contiene.CodiceReg = Cantante.CodiceReg AND
Contiene.NroSerieDisco IN
(SELECT A2.NroSerieDisco
FROM
Contiene A2
WHERE A2.NroProgr IN (SELECT MAX( A1.NroProgr)
FROM Contiene AS A1));
SQL
ESERCIZIO 8
SELECT DISTINCT Autore.Nome
FROM Autore, Contiene, Esecuzione
WHERE Contiene.CodiceReg = Esecuzione.CodiceReg AND
Esecuzione.TitoloCanz = Autore.TitoloCanzone AND
Contiene.NroSerieDisco IN
(SELECT Contiene.NroSerieDisco
FROM Contiene, Cantante
WHERE Contiene.CodiceReg = Cantante.CodiceReg AND
Contiene.NroSerieDisco IN
(SELECT Disco.NroSerie
FROM Esecuzione, Disco, Contiene
WHERE Disco.NroSerie = Contiene.NroSerieDisco AND
Contiene.CodiceReg = Esecuzione.CodiceReg AND
Esecuzione.Anno < Disco.Anno
GROUP BY Disco.NroSerie
HAVING COUNT(Disco.NroSerie) >=3)
GROUP BY NroSerieDisco
HAVING MAX(Contiene.NroProgr)
COUNT(Cantante.NomeCantante));
SQL
ESERCIZIO 8
SELECT Cantante.NomeCantante
FROM Cantante
WHERE Cantante.NomeCantante NOT IN
(SELECT Cantante.NomeCantante
FROM Cantante
WHERE Cantante.CodiceReg IN
(SELECT Cantante.CodiceReg
FROM Cantante
GROUP BY Cantante.CodiceReg
HAVING COUNT(Cantante.CodiceReg) = 1));
SQL
ESERCIZIO 8
SELECT DISTINCT Cantante.NomeCantante
FROM Cantante
WHERE Cantante.NomeCantante NOT IN
(SELECT Cantante.NomeCantante
FROM Cantante
WHERE Cantante.CodiceReg IN
(SELECT Cantante.CodiceReg
FROM Cantante
GROUP BY Cantante.CodiceReg
HAVING COUNT(Cantante.CodiceReg) > 1));
SQL
ESERCIZIO 8
T ABELLA AUT O RE
T ABELLA CANT ANT E
NOME
T IT O L O C A N Z O N E
NO M ECANT ANT E
C O D IC E R E G
BO NETTI
BO NETTI
A LT ALE N A
G IO V E N T Ú
BO NETTI
DANI
Q 11
Q1
BO NETTI
TEM PESTA
DANI
Q2
DANI
L A S C IA M I
DANI
Q3
DANI
O R G O G LIO
DANI
Q4
DRUPO
L IB E R T A '
DERRI
Q8
FREDDI
C IE L O
DERRI
Q9
FREDDI
M A R IA
FREDDI
Q 12
SARRE
G IL D A
FREDDI
Q 13
SARRE
IL M A R E
FREDDI
Q 14
SARRE
P O E S IE
FREDDI
Q7
G IA N C O
Q5
G IA N C O
Q6
G IA N C O
Q7
O N O F R IO
Q2
T A B E L L A C O N T IE N E
N R O S E R IE D I
O4
7S8C
57
T A B E L L A D IS C O
C O D IC E R E G
NROPROGR
N R O S E R IE
T IT O L O A L B U M
ANNO
PREZZO
78574
Q1
Q5
3
1
78574
78575
P A Z Z IA
O B L IO
1998
1990
23000
20000
78574
Q7
2
78576
LEG A M I
1993
15000
78575
Q2
1
78577
SERENA
1995
25000
78575
Q4
2
78575
Q6
3
78576
Q 14
4
78576
Q7
1
78576
Q8
3
78576
Q9
2
78577
Q 11
1
78577
Q 12
3
78577
Q 13
2
TABELLA AUTORE
NOM E
TABELLA CANTANTE
TITOLOCANZONE
NOM ECANTANTE
CODICEREG
BONETTI
ALTALENA
BONETTI
Q11
BONETTI
GIOVENTÚ
DANI
Q1
BONETTI
TEMPESTA
DANI
Q2
DANI
LASCIAMI
DANI
Q3
DANI
ORGOGLIO
DANI
Q4
DRUPO
LIBERTA'
DERRI
Q8
FREDDI
CIELO
DERRI
Q9
FREDDI
MARIA
FREDDI
Q12
SARRE
GILDA
FREDDI
Q13
SARRE
IL MARE
FREDDI
Q14
SARRE
POESIE
FREDDI
Q7
GIANCO
Q5
GIANCO
Q6
GIANCO
Q7
ONOFRIO
Q2
TABELLA CONTIENE
TABELLA DISCO
NROSERIEDISCO
CODICEREG
NROPROGR
NROSERIE
TITOLOALBUM
ANNO
PREZZO
78574
Q1
3
78574
PAZZIA
1998
23000
78574
Q5
1
78575
OBLIO
1990
20000
78574
Q7
2
78576
LEGAMI
1993
15000
78575
Q2
1
78577
SERENA
1995
25000
78575
Q4
2
78575
Q6
3
78576
Q14
4
78576
Q7
1
78576
Q8
3
78576
Q9
2
78577
Q11
1
78577
Q12
3
78577
Q13
2
TABELLA ESECUZIONE
CODICEREG
TITOLOCANZ
Q1
CIELO
ANNO
Q11
TEMPESTA
1992
Q12
ALTALENA
1993
Q13
LIBERTA'
1993
Q14
IL MARE
1992
Q2
GIOVENTÚ
1978
Q3
GILDA
1977
Q4
LASCIAMI
1980
Q5
POESIE
1998
Q6
MARIA
Q7
LASCIAMI
1989
Q8
ORGOGLIO
1985
Q9
CIELO
1989
T ABELLA AUT O RE
NOME
T IT O L O C A N Z O N E
BONETTI
ALTALENA
BONETTI
G IO V E N T Ú
BONETTI
TEMPESTA
DANI
L A S C IA M I
DANI
O R G O G L IO
DRUPO
L IB E R T A '
FREDDI
C IE L O
FREDDI
M A R IA
SARRE
G IL D A
SARRE
IL M A R E
SARRE
P O E S IE
T A B E L L A C O N T IE N E
N R O S E R IE C O D IC E R E N R O P R O G
78574
Q1
3
78574
Q5
1
78574
Q7
2
78575
Q2
1
78575
Q4
2
78575
Q6
3
78576
Q 14
4
78576
Q7
1
78576
Q8
3
78576
Q9
2
78577
Q 11
1
78577
Q 12
3
78577
Q 13
2
T ABELLA CANT ANT E
N O M E C A N T A N C O D IC E R E G
BONETTI
Q 11
DANI
Q1
DANI
Q2
DANI
Q3
DANI
Q4
DERRI
Q8
DERRI
Q9
FREDDI
Q 12
FREDDI
Q 13
FREDDI
Q 14
FREDDI
Q7
G IA N C O
Q5
G IA N C O
Q6
G IA N C O
Q7
O N O F R IO
Q2
N R O S E R IE
78574
78575
78576
78577
T A B E L L A D IS C O
T IT O L O A L B U M A N N O
P A Z Z IA
1998
O B L IO
1990
LEG AM I
1993
SEREN A
1995
PREZZO
23000
20000
15000
25000
Descargar

SQL - Dipartimento di Informatica