Modifica dei dati



inserimento
cancellazione
modifica
SQL
1
Insert
insert into <NomeTabella> [(<colonna_i>, …,
<colonna_j>)] values (<valore_i>, …,
<valore_j>);



deve essere specificato un valore per ogni colonna
della lista
se una colonna non è specificata, viene inserito il
valore null
se non viene fornita la lista delle colonne, deve
essere specificato un valore per ogni colonna della
tabella così come appare nella istruzione create
SQL
2
Esempio
insert into PROGETTO (PNO, PNOME,
COMPONENTI, BUDGET, PSTART, PMGR) values
(313, 'BD', 3,150000.15, to_date('06-022001','dd-mm-yyyy'), 7411);
insert into PROGETTO values (314, 'SI',
7411, 5, 350000.75, to_date('12-022001','dd-mm-yyyy'), null);
SQL
3
Insert (2)
insert into <NomeTabella> [(<colonna_i>, …,
<colonna_j>)] <querySQL>;
create table VECCHI_IMP (
INO number(4) not null,
ASS_DATA date);
insert into VECCHI_IMP select INO, DATA_ASS from
IMPIEGATI where DATA_ASS<'31-DIC-60';
SQL
4
Updates
updates <tabella> set
<colonna_i>=<espressione_i>, …,
<colonna_j>=<espressione_j>
[where <condizione>];

espressione può essere



una costante,
una espressione funzionale,
una query SQL
SQL
5
Esempio
update IMPIEGATI set
LAVORO='DIRIGENTE', DIPNO=20,
STIPENDIO=STIPENDIO+1000
where INOME='FORD';
(Il sig. Ford ha avuto un avanzamento di
carriera)
SQL
6
Esempio
update IMPIEGATI set
STIPENDIO=STIPENDIO*1.15
where DIPNO in (10,30);
(Gli impiegati dei dipartimenti 10 e 30
hanno avuto un aumento dello
stipendio del 15%)
SQL
7
Esempio
update IMPIEGATI set
STIPENDIO = (select min(STIPENDIO)
from IMPIEGATI where
LAVORO='DIRIGENTE')
where LAVORO='VENDITORE' and DIPNO=30;
(I venditori del dipartimento 30 ricevono lo stesso
stipendio del manager che ha lo stipendio più
basso)
SQL
8
Delete
delete from <tabella>
[where <condizione>];

senza la clausola where, vengono cancellate
tutte le righe
SQL
9
Esempio
delete from PROGETTO
where PEND < sysdate;
(Cancella tutti i progetti terminati alla
data odierna)
SQL
10
Commit e Rollback

commit: rende permanenti le modifiche
fatte durante l'ultima transazione


quit, create, drop realizzano un commit
implicito
rollback: annulla le modifiche fatte
durante l'ultima transazione
SQL
11
Query su più tabelle (JOIN)
select [distinct]
[<alias_k>.]<colonna_i>,
…,
[<alias_m>.]<colonna_j>
from
<tabella_1>[<alias_1>],
…,
<tabella_n>[<alias_n>]
[where <condizione>];
SQL
12
Alias
Se colonne di tabelle diverse hanno lo
stesso nome, è necessario usare la
notazione
<tabella>.<colonna>
per eliminare ambiguità.
Al posto di <tabella> si può usare l'alias
SQL
13
Esempi
select INOME, I.DIPNO, DIPNOME
from IMPIEGATI I, DIPARTIMENTO D
where I.DIPNO=D.DIPNO and LAVORO='VENDITORE';
select INOME, DIPNOME, PNOME
from IMPIEGATI I, DIPARTIMENTO D, PROGETTO P
where I.INO=P.PMGR and D.DIPNO=I.DIPNO;
select I1.INOME, I2.INOME
from IMPIEGATI I1, IMPIEGATI I2
where I1.CAPO=I2.INO;
SQL
14
Query annidate

La condizione in una clausola where
può contenere anche una query:
select … where … (select …)
subquery
query annidata
SQL
15
Interpretazione delle query
annidate
1.
2.
3.
viene eseguita la subquery
il risultato viene salvato in una tabella
temporanea
il controllo della query esterna viene
fatto sulle righe della tabella
temporanea
SQL
16
Regole per le query annidate




più condizioni possono essere combinate con gli
operatori and, or
si può arrivare fino a 16 livelli di annidamento!
una query esterna non può far riferimento a
tabelle della query più interna (viceversa è
possibile)
se non specificato esplicitamente, i nomi di
colonna fanno riferimento alle tabelle più “vicine”
SQL
17
Subquery
<espressione> [not] in (<subquery>)
<espressione> <operatore_cfr> [any |
all] (<subquery>)
[not] exists (<subquery>)
SQL
18
Esempio
select INOME, STIPENDIO from IMPIEGATI
where INO in
(select PMGR from PROGETTO
where PSTART < ’31-DIC-90’)
and DIPNO=20;
Seleziona gli impiegati del dipartimento 20 (ed il loro
stipendio) che partecipano a progetti partiti prima del
31/12/90
SQL
19
Esempio
select * from IMPIEGATI
where DIPNO in
(select DIPNO from DIPARTIMENTI
where CITTA =‘BOSTON’);
Seleziona gli impiegati dei dipartimenti di Boston
Poiché la subquery ha come risposta un solo valore, si
poteva usare = invece di in.
SQL
20
Subquery correlata


Subquery che fa riferimento a colonne
di tabelle utilizzate nella query
principale
Non si può usare l’intepretazione vista
per le query annidate!!
SQL
21
Interpretazione delle subquery
correlate
1.
Per ogni riga della query esterna,
1.
2.
viene valutata la subquery
viene valutato il predicato della query
esterna
SQL
22
Esempio
select * from IMPIEGATI I1
where DIPNO in
(select DIPNO from IMPIEGATI [I2]
where [I2.]INO=I1.CAPO);
Seleziona i dipendenti che lavorano nello
stesso dipartimento del loro capo
SQL
23
any e all
<espressione> <operatore_cfr> [any |
all] (<subquery>)

confrontano <espressione> con ogni
valore selezionato da <subquery>
SQL
24
any


la condizione è vera se esiste almeno
una riga selezionata dalla subquery per
cui il confronto ha successo
se la subquery restiuisce un insieme
vuoto, la condizione fallisce
SQL
25
Esempio
select * from IMPIEGATI
where STIPENDIO >= any
(select STIPENDIO from IMPIEGATI
where DIPNO = 30)
and DIPNO = 10
Seleziona gli impiegati del dipartimento 10 che hanno uno
stipendio maggiore o uguale di almeno un impiegato del
dipartimento 30
SQL
26
all


la condizione è vera se il confronto ha
successo per tutte le righe selezionate
dalla subquery
se la subquery restiuisce un insieme
vuoto, la condizione ha successo
SQL
27
Esempio
select * from IMPIEGATI
where STIPENDIO > all
(select STIPENDIO from IMPIEGATI
where DIPNO = 30)
and DIPNO <> 30
Seleziona gli impiegati che non lavorano nel dipartimento
30 e che hanno uno stipendio maggiore di tutti gli
impiegati del dipartimento 30
SQL
28
Equivalenze


in è equivalente a = any
not in è equivalente a <> all
SQL
29
Exists
[not] exists (<subquery>)


restituisce valore vero solo se
l’interrogazione fornisce un risultato non
vuoto [vuoto].
ha senso solo per query correlate
SQL
30
Esempio
select * from DIPARTIMENTI
where not exists
(select * from IMPIEGATI
where
DIPNO=DIPARTIMENTI.DIPNO);
Restituisce i dipartimenti che non hanno
impiegati
SQL
31
Operazioni su risultati di query
<query_1>
[union [all] | intersect | minus ]
<query_2>
SQL
32
union

Restituisce l'unione delle tuple risultato
delle due query. Se non viene
specificata la clausola all, i duplicati
vengono eliminati
select INO from IMPIEGATI
union
select CAPO from IMPIEGATI;
SQL
33
intersect

Restituisce l'intersezione delle tuple
risultato delle due query.
select INO from IMPIEGATI
intersect
select CAPO from IMPIEGATI;
SQL
34
minus

Restituisce le tuple che sono risultato
della prima query ma che non sono
risultato della seconda
select INO from IMPIEGATI
minus
select CAPO from IMPIEGATI;
SQL
35
Query con raggruppamento
Consentono di applicare le funzioni di
gruppo non a tutte le righe ma a gruppi
di righe che soddisfano certe proprietà
select <colonna/e>
from <tabella/e>
where <condizione>
group by <colonna/e_gruppo>
[having <condizione/i_gruppo>];

SQL
36
Interpretazione
Le righe risultato della select che hanno gli
stessi valori di <colonna/e_gruppo>
vengono raggruppate.
2.
Le funzioni di gruppo specificate nella select
vengono applicate a ciascun gruppo
separatamente.
N.B. solo le colonne di <colonna/e_gruppo>
possono essere listate nella clausola select
senza funzioni di gruppo
1.
SQL
37
Esempi
select DIPNO, min(STIPENDIO),
max(STIPENDIO)
from IMPIEGATI
group by DIPNO;
select DIPNO, min(STIPENDIO),
max(STIPENDIO)
from IMPIEGATI
where LAVORO='VENDITORE'
group by DIPNO;
SQL
38
having
Dopo che i gruppi sono stati formati, se
ne possono eliminare alcuni in base a
certe condizioni
select DIPNO, min(STIPENDIO),
max(STIPENDIO)
from IMPIEGATI
group by DIPNO
having count(*)>3;

SQL
39
Accesso a tabelle di altri utenti

Per accedere a tabelle di altri utenti
(sempre che se ne abbiano i privilegi)
bisogna far precedere il nome di tabella
dal nome dell'utente
<user>.<tabella>
SQL
40
Sinonimi
Si possono creare sinonimi sia per le
proprie tabelle che per quelle di altri
utenti:
create synonym <sinonimo> for
[<user>.]<tabella>;

SQL
41
Commenti alle definizioni

Si possono aggiungere commenti esplicativi alle definizioni
di tabelle e alle colonne
comment on table <tabella> is '<testo>';
comment on column <tabella>.<colonna> is '<testo>';

I commenti sono inseriti nelle viste del dizionario dei dati
USER_TAB_COMMENTD e USER_COL_COMMENTS
SQL
42
Modifiche di definizioni

E' possibile modificare la struttura di
una tabella



aggiungendo colonne
aggiungendo vincoli di tabella
modificando definizioni di colonne
SQL
43
alter table
alter table <tabella> add
(<definizione_colonna>);
alter table <tabella> add
(<vincoli_di_tabella>)
alter table <tabella> modify
(<nuova_definizione_colonna>);
SQL
44
Cancellazione di una tabella
drop table <tabella> [cascade
constraints];
Cancella le righe e la definizione di una
tabella
SQL
45
Viste
Una vista è una tabella virtuale costruita
selezionando righe e colonne di altre
tabelle.
Una vista può essere usata come le altre
tabelle per la ricerca e per la modifica di
dati.
Una vista viene rivalutata ogni volta che vi si
accede.
SQL
46
Viste (2)
create view <nome_vista> [(<colonna/e>)] as
<query> [with check option [constraint
<nome_vincolo>]];


se (<colonna/e>) non viene specificato, le colonne
della vista avranno lo stesso nome delle colonne
selezionate
il vincolo with check option consente di rifiutare le
modifiche e gli inserimenti di righe nella vista che non
rispondono alla definizione, ovvero che non verrebbo
selezionati dalla query
SQL
47
Esempi
create view DIP20 as select INOME,
LAVORO, STIPENDIO*12 STIP_ANNUO
from IMPIEGATI where DIPNO=20;
create view DIP20 (INOME, LAVORO,
STIP_ANNUO) as select INOME,
LAVORO, STIPENDIO*12 from
IMPIEGATI where DIPNO=20;
SQL
48
Cancellazione di viste
delete <nome_vista>;
SQL
49
Vincoli di integrità

check constraint


limita i valori possibili per un attributo
foreign key constraint

specifica interdipendenze tra relazioni
SQL
50
check constraint

specifica il rango dei possibili valori di uno o
più attributi o certe condizioni che tali valori
devono soddisfare
[constraint <nome>] check (<condizione>)

può essere un vincolo di colonna (se
condizione agisce su una sola colonna) o di
tabella (se condizione agisce su più colonne)
SQL
51
Esempio
create table IMPIEGATI (
…
INOME varchar2(30) constraint CK_NOME
check(INOME=upper(INOME)),
STIPENDIO number(5,2) check (STIPENDIO >=
500),
DIPNO number(3) check (DIPNO between 10 and
100)
);
SQL
52
Esempio
create table PROGETTO (
…
COMPONENTI number(5) constraint
CK_COMPONENTI check(COMPONENTI>2),
…
constraint CK_DATE check(PEND > PSTART)
);
SQL
53
foreign key constraint


crea un legame tra i valori di un attributo
della tabella corrente (interna) ed i valori di
un attributo chiave primaria di un'altra tabella
(esterna), in modo tale che ogni valore
dell'attributo della tabella interna, se non
nullo, sia presente tra i valori dell'attributo
chiave primaria della tabella esterna
può essere un vincolo di colonna o un vincolo
di tabella
SQL
54
foreign key
[constraint <nome>]
[foreign key (<colonna/e>)]
references <tabella> [(<colonna/e>)]
[on delete cascade]
SQL
55



la clausola foreign key deve essere usata se si tratta
di un vincolo di tabella, ovvero se coinvolge più
colonne e deve indicare le colonne della tabella
interna coinvolte
references indica quali colonne della tabella esterna
sono coinvolte; in assenza di specifica viene presa la
colonna che costituisce la chiave primaria della
tabella esterna
on delete cascade specifica che se viene cancellato
un elemento dalla tabella esterna, tutte le righe della
tabella interna che hanno il valore dell'attributo
cancellato, vengono a loro volta cancellate
SQL
56
Esempio
create table IMPIEGATI (
INO number(4) primary key,
…
DIPNO number(3) references
DIPARTIMENTO(DIPNO)
);
SQL
57
Esempio
create table PROGETTO (
PNO number(3) primary key,
PMGR number(4) not null references
IMPIEGATI,
…
);
SQL
58
Esempio
create table IMPIEGATI (
INO number(4) primary key,
…
CAPO number(4) not null references
IMPIEGATI,
…
);
SQL
59