Presentazione standard di PowerPoint · 2018. 6. 4. · SQL – 2 è il linguaggio di riferimento...
128
SQL Concetti base Prof. Aniello Cimitile
Transcript of Presentazione standard di PowerPoint · 2018. 6. 4. · SQL – 2 è il linguaggio di riferimento...
SQL
Concetti base
Prof. Aniello Cimitile
Gli standard
• E’ un linguaggio fondato su uno Standard, ovvero su un documento formale di definizione prodotto dalla ISO ( International Standard Organization ) che ne cura anche l’aggiornamento e l’evoluzione
IL PRIMO SANDARD:
3 Anni per produrlo • ISO 157 paesi
• IEC 60 paesi
• In ISO gli organismi di standardizzazione nazionali
– ANSI
– BSI
– DIN
– AFNOR
– JISC
– CEI/UNI
– ………….
ISO
JTC1
IEC
SQL ISO 9075: 1987
… le origini …
• IBM Research Laboratory - San Josè
– Dove lavorava Codd
• … 1974 D.D. Chamberlain definisce
SEQUEL (Structured English Query Language)
• … 1977 prime versioni commerciali su SEQUEL/2
• … 1982 IBM usa la sigla SQL , … anche per DB2 nel 1983
• ANSI comincia a lavorare alla standardizzazione
• ANSI pubblica lo standard X3.155 nel 1986
• ISO adotta e completa la proposta ANSI e nel 1987 pubblica lo standard SQL ISO : 9075 , viene indicato come SQL- 86
L’evoluzione dello standard
• ISO 9076 : 1989
Indicato con SQL-89
• ISO/IEC 9075 : 1992
July 30
Indicato come SQL - 2
• SQL : 1999
• SQL : 2003
• SQL: 2006
• SQL : 2008
• SQL : 2011
• SQL : 2016
Indicati come SQL - 3
SQL – 2 è il linguaggio di riferimento
SQL-3 introduce estensioni ( per oggetti, Java, XML, ….)
DD Definizione dei dati
DM Modifica dei Dati
QL Interrogazioni
i Tipi ( …Domini)
character [varying] [( Lunghezza )] character char è il tipo carattere character ( k ) char ( k) k è una costante intera; è il tipo stringa di caratteri di lunghezza fissa k character varying ( k ) varchar (k)
k è una costante intera; è il tipo stringa di caratteri di lunghezza variabile ed al massimo pari a k
Numerici esatti
numeric [ (Precisione [ , Scala ] )]
decimal [ (Precisione [ , Scala ] )]
tipo numerico a virgola fissa; Precisione è una
costante intera che indica il numero di cifre significative (1 per default); Scala è una costante intera che indica il numero di cifre dopo il punto decimale (0 per default)
esempi numeric (4) -9999 +9999 numeric ( 6 , 2) -9999.99 +9999.99
• L’importanza dei tipi numerici esatti nelle applicazioni non tecnico-scientifiche
• Numeric e Decimal sono funzionalmente equivalenti … ( dettagli implementativi con differente interpretazione della precisione…)
• Altri, noti e con precisione fissata nelle specifiche implementazioni del linguaggio
integer smallint
• Numerici Approssimati
– real
– double precision
– float [( k)] consente di specificare la precisione k desiderata
• Temporali
– date yyyy – mm - dd
– time [Precisione] HH: MM: SS [.ssssss]
– Timestamp [Precisione] yyyy-mm-dd H H:MM: SS[.ssssss]
• Intervalli Temporali
– interval Per indicare un intervallo temporale in anni, mesi,… ore, etc..
• Es. interval ‘ 5 ‘ YEAR è un intervallo di 5 anni
interval ‘ 80 – 6’ YEAR TO MONTH è un intervallo di 80 anni e 6 mesi
… altri …
• In SQL-2 rimossi con SQL -3 – bit
– bit (k)
– bit varying ( k )
• In SQL -3 – Boolean …. a tre valori … { T, F, UN}
– BLOB
– CLOB
– bigint
TIPI di UTENTE
• Tipi definiti dall’utente a partire dai tipi primitivi o da tipi di utente precedentemente definiti
• Un tale tipo richiede che vengano specificati: – Il nome
– Il tipo da cui è derivato
– Il valore di default, se si vuole
– Gli eventuali vincoli restrittivi rispetto al tipo da cui è derivato
create domain NuovoTipo as Definizione [default Valore] [ Vincolo]
Es. create domain Tqualifica as char (10) default ‘ Impiegato’ create domain Tvoto as smallint default ‘null’ check ( value >= 18 and value <= 30)
La principale istruzione DD di
SQL
create table NomeTabella
(NomeAttributo Dominio [ vincoli]
{, NomeAttributo Dominio [ vincoli] }
)
create table IMPIEGATI
(
CODE numeric (5) primary Key ,
NOME char (40) not null ,
INDIRIZZO varchar (200) ,
SESSO char ,
SECTOR_COD char(3) references DIPART (SECTOR_CODE),
TEL_UF numeric (9) unique ,
STIPENDIO numeric (6) default 0
)
RELAZIONE
ATTRIBUTO
TIPO
VINCOLO
• C’è una differenza fra la Table di SQL … e la relazione
– Nella table SQL è possibile avere righe uguali
– Perché sia una relazione:
DEVE SEMPRE ESSERE ESPLICITAMENTE DEFINITA UNA CHIAVE, ovvero ALMENO UN ATTRIBUTO DEVE ESSERE VINCOLATO A CHIAVE
WARNING
Chiavi
• Per la specifica delle chiavi è a disposizione la clausola PRIMARY KEY – PRIMARY KEY è il vincolo di Chiave Primaria (impone che i
valori siano non nulli e diversi in ogni tupla, e quindi implica i vincoli NOT NULL e UNIQUE) • Si può avere UNA SOLA chiave primaria con uno o più attributi
primi; • La clausola subito dopo l’attributo primo se la chiave è composta
da un solo attributo o, più in gnerale, con apposita istruzione PRIMARY KEY (ListaAttributi)
– Altri con vincolo di univocità :UNIQUE • Dopo l’attributo vincolato • o, se su più attributi UNIQUE ( ListaAttributi)
• create table STUDENTE
(
NOME char (20) not null
COGNOME char (20) not null
unique ( COGNOME, NOME )
……………….
)
• create table STUDENTE
(
NOME char (20) not null unique
COGNOME char (20) not null unique
………………..
)
WARNING
Non sono la stessa cosa
Create STUDENTE
(
MATRICOLA char ( 9) primary key
………………….
)
Create STUDENTE
(
MATRICOLA char ( 9)
primary key (MATRICOLA)
………………….
)
s Sono la Stessa cosa
… altri vincoli intrarelazionali …
• Obbligatorietà di valore per attributo
– NOT NULL
– DEFAULT val
• Vincoli predicativi su attributo o su tuple
– CHECK ( p )
• Dove p è un predicato ….
… vincoli interrelazionali …
• sostanzialmente vincoli di integrità referenziale – Un attributo della relazione ( referente, o anche interna) fa riferimento ad un
attributo di un’altra relazione ( riferita, o Anche esterna): il vincolo impone che ogni valore ( non nullo) dell’attributo referente esistente in tabella, sia un valore dell’attributo riferito esistente nella tabella della relazione riferita
• … sono disponibili – references RELRIFERITA ( ATTRIBUTORIFERITO)
– foreign key ( ListaAttributiReferenti) references RELRIFERITA ( ListaAttributiRiferiti)
NB . Gli attributi Riferiti debbono formare chiave o essere vincolati UNIQUE
DD Definizione dei dati
DM Modifica dei Dati
QL Interrogazioni
L’istruzione SELECT
• È il costrutto linguistico fondamentale di QL
• La sua struttura base è composta da una sequenza di tre sezioni
– La sezione di apertura è detta clausola SELECT o anche Target List, ed è obbligatoria
– La seconda sezione è detta clausola FROM ed è obbligatoria
– La terza sezione è detta clausola WHERE ed è opzionale
• La prima sezione Definisce gli Attributi della relazione risultato ( le informazioni che vogliamo ….)
• La seconda sezione le Relazioni da cui bisogna derivare il risultato
• La terza sezione definisce le eventuali condizioni di derivazione
select ListaAttributi from ListaTabelle [where Condizione]
… con una sola relazione …
• Data la relazione R e un sottoinsieme dei suoi attributi, ad esempio {A, B, C}, consideriamo l’istruzione
select A, B, C from R
….. la definizione degli attributi del risultato … … è fatta selezionando alcuni attributi di R … ….e tutta l’istruzione fa pensare alla proiezione di R sugli attributi scelti… 𝝅𝑨,𝑩, 𝑪 (R)
…dalla Tabella R … voglio le colonne di A, B, C …
Studente Voto Lode Data
30501 26 no 15/01/2014
30541 30 si 20/01/2014
30489 28 no 03/02/2014
30511 30 si 05/02/2014
30497 18 no 03/02/2014
30503 27 no 20/01/2014
Studente Data
30501 15/01/2014
30541 20/01/2014
30489 03/02/2014
30511 05/02/2014
30497 03/02/2014
30503 20/01/2014
𝝅𝑺𝒕𝒖𝒅𝒆𝒏𝒕𝒆, 𝑫𝒂𝒕𝒂 (ESAMI)
select STUDENTE, DATA from ESAMI
ESAMI
WARNING
• In SQL le Tabelle non sono Relazioni …
Studente Voto Lode Data
30501 26 no 15/01/2014
30541 30 si 20/01/2014
30489 28 no 03/02/2014
30511 30 si 05/02/2014
30497 18 no 03/02/2014
30503 27 no 20/01/2014
Data
15/01/2014
20/01/2014
03/02/2014
05/02/2014
Data
15/01/2014
20/01/2014
03/02/2014
05/02/2014
03/02/2014
20/01/2014
select DATA from ESAMI
𝝅 𝑫𝑨𝑻𝑨 (ESAMI)
• … un primo arricchimento: l’opzione
distinct
associata a select … elimina ogni duplicazione di tupla
select [distinct ] ListaAttributi
from Tabella
Solo se lista attributi contiene una chiave o si è
aggiunta l’opzione distinct … abbiamo l’equivalenza con la proiezione…
Studente Voto Lode Data
30501 26 no 15/01/2014
30541 30 si 20/01/2014
30489 28 no 03/02/2014
30511 30 si 05/02/2014
30497 18 no 03/02/2014
30503 27 no 20/01/2014
Data
15/01/2014
20/01/2014
03/02/2014
05/02/2014
select distinct DATA from ESAMI
𝝅 𝑫𝑨𝑻𝑨 (ESAMI)
ESAMI
Progetto Nomeprog
INGINF42 ISIDE
INGINF03 SINTESI
𝝅𝑷𝑹𝑶𝑮𝑬𝑻𝑻𝑶,𝑵𝑶𝑴𝑬𝑷𝑹𝑶𝑮 (RICERCATORI)
Nomeprog Ricercatore Progetto
ISIDE Visaggio INGINF42
ISIDE Mercaldo INGINF42
SINTESI Visaggio INGINF03
RICERCATORI
select distinct PROGETTO,NOMEPROG
from RICERCATORI
• … un secondo arricchimento: nel definire gli attributi con la clausola select, posso ridenominarli
nella ListaAttributi, per ogni attributo può essere usata l’opzione as seguita dal nuovo nome
NomeAttributo as NewAttributo
select distinct FILM as GRANDIFILM
from INTERPRETI
INTERPRETI
Interprete FILM Anno
Robert De Niro Taxi Driver 1976
Richard Gere Pretty Woman 1990
Roberto Benigni La vita è bella 1997
Kristin Scott Thomas La chiave di Sara 2010
Vivien Leigh Via col vento 1939
Meryl Streep Kramer contro Kramer 1983
Giulia Roberts Pretty Woman 1990
GRANDI FILM
Taxi Driver
Pretty Woman
La vita è bella
La chiave di Sara
Via col vento
Kramer contro Kramer
• select [distinct] Attributo [as NuovoNome ] {, Attributo [as NuovoNome ] }
from Tabella
…. l’istruzione consente di ridenominare attributi di una relazione …
…e farne una proiezione
• … un terzo ‘arricchimento’: la possibilità di definire nuovi attributi derivandoli con espressioni aritmetiche da quelli delle relazioni operando
R ≡ {ARTICOLO, FORNITIORE, DESCRIZIONE, COSTO, QUANTITA’, GIACENZA}
select ARTICOLO, DESCRIZIONE, COSTO*3 as PREZZOVENDITA
from R
• …. Il nuovo assetto della target list
select [distinct] <Attrib [as NuovoNome ] | e(Attrib) as NuovoNome>
{, Attrib [as NuovoNome ] | e(Attrib) as NuovoNome }
dove e (Attrib) è una espressione aritimetica .. .
• …ampia possibilità di scelta e ridefinizione di attributi, … ma anche di lasciare nella relazione risultato tutti gli attributi delle relazioni operando
select * Dove * sta per tutti gli attributi delle tabelle elencate nella clausola from
La selezione dell’algebra relazionale è realizzabile con la clausola where
Select * from R where p
…è equivalente a σ𝒑 (R)
Esempio: select *
from ESAMI
where VOTO = 30
Studente Voto Lode Data
30501 26 no 15/01/2014
30541 30 si 20/01/2014
30489 28 no 03/02/2014
30511 30 si 05/02/2014
30497 18 no 03/02/2014
30503 27 no 20/01/2014
Studente Voto Lode Data
30541 30 si 20/01/2014
30511 30 si 05/02/2014
select * from ESAMI where VOTO = 30
σ𝑽𝑶𝑻𝑶=𝟑𝟎 (ESAMI)
ESAMI
… con più relazioni …
• Date le Relazioni R1, R2, …,Rn consideriamo l’istruzione
Select *
From R1,R2,…,Rn
Avendo rinunciato alla clausola where e alla selezione di attributi ( quindi in pratica anche all’azione della clausola select) , è attiva la sola clausola from
…. è il prodotto cartesiano R1x R2 x …Rn (con ridenominazione implicita )
• … nella istruzione select … from … where ..
– from descrive il prodotto cartesiano
– where la selezione
– select la proiezione e la ridenominazione
select R1.AttA, …. , Rh. AttY from R1, R2, …..Rn where p
𝝅𝑹𝟏.𝑨𝒕𝒕𝑨,…,𝑹𝒉.𝑨𝒕𝒕𝒀 (σ𝒑 (R1 X R2 X … Rn)
Le seguenti interrogazioni sono equivalenti
• Osserviamo che, poiché per le relazioni in from parliamo di ridenominazione implicita e al generico attributo AX di Ri facciamo riferimento con Ri.AX,
• Ricordando che Se gli schemi degli operandi non hanno attributi uguali, Join Naturale coincide col Prodotto cartesiano,
select R1.AttA, …. , Rh. AttY from R1, R2, …..Rn where p
𝝅𝑹𝟏.𝑨𝒕𝒕𝑨,…,𝑹𝒉.𝑨𝒕𝒕𝒀 (σ𝒑 (R1 ⋈ R2 ⋈ … Rn)
sono equivalenti anche
create table IMPIEGATI ( COD numeric (5) primary Key NOME char (40) not null INDIRIZZO varchar (200) SESSO char SETTORE char(3) UFFICIO char (3) TEL_UF numeric (9) unique STIPENDIO numeric (7,2) default 0 PREMIPROD numeric (6,2) default 0 QUALIFICA varchar ( 20 ) foreign key (UFFICIO, SETTORE)
references UFFICI ( UFF_COD, SET_COD) ) create table DSTAFF (
COD numeric(5) primary key NOME varchar (50) STIPENDIO numeric(8,2) )
create table UFFICI ( UFF_COD char(3)
SET_COD char(3) references SETTORI (CODICE) NOMEUFF varchar (50) CAPUFF numeric(5) references DSTAFF (COD) SEDE varchar (100) PECMAIL char(15) unique primary key ( UFF_COD, SET_COD) ) create table SETTORI (
CODICE char(3) primary key NOMESET varchar (50) unique DIRIGENTE numeric(5) references DSTAFF (COD) )
• ESERCIZIO 1: elenco telefonico aziendale degli impiegati – select NOME, TEL_UF from IMPIEGATI
• ESERCIZIO 2 : elenco impiegati con ufficio – select COD, NOME, UFFICIO from IMPIEGATI
• ESERCIZIO 3: solo impiegate (tutte le informazioni) – select * from IMPIEGATI where SESSO= ‘F’
• ESERCIZIO 4 : elenco impiegate con ufficio e telefono – select NOME, UFFICIO, TEL_UF from IMPIEGATI where SESSO= ‘F’
• ESERCIZIO 5 : stipendio mensile operai senza premio di produttività – select COD, NOME, STIPENDIO/12 as MENSILE from IMPIEGATI where QUALIFICA= ‘OPERAIO’ ∧ PREMIPROD= 0
• ESERCIZIO 6 : nomi dei settori e dei relativi dirigenti – Sia query algebrica che SQL
• ESERCIZIO 7: come sopra per uffici e capi
• select SETTORI.NOMSET, DSTAFF.NOME
from SETTORI, DSTAFF
where SETTORI.DIRIGENTE = DSTAFF.COD
• select UFFICI.NOMEUFF, DSTAFF.NOME
from UFFICI, DSTAFF
where UFFICI.CAPUFF = DSTAFF.COD
… as in from …
• La individuazione degli attributi nelle clausole select e where attraverso la notazione
nomerelazione.nomeattributo
può essere ‘alleggerita’ sostituendo a nomerelazione
un altro identificatore univocamente definito (Alias)
from Relazione [[as] Alias]
{,Relazione [[as] Alias]}
• select S.NOMSET, D.NOME
from SETTORI as S, DSTAFF as D
where S.DIRIGENTE = D.COD
• select U.NOMEUFF, D.NOME
from UFFICI U, DSTAFF D
where U.CAPUFF = D.COD
Scegliere un equilibrato compromesso fra esigenza di stringatezza ed agilità e quella di identificatori ‘significativi’ … Anteporre sempre la esigenza di chiarezza ed agevole riconoscimento ‘semantico’
…operatore like in where…
• è un operatore di confronto fra stringhe di caratteri in cui una delle due stringhe ha componenti costanti ed altre variabili
• le componenti variabili della stringa vengono rappresentate con due simboli chiave : _ %
_ il carattere underscore indica un qualsiasi carattere
% indica una qualsiasi sottostringa di caratteri di lunghezza compatibile ….
• ESEMPIO
NOMEUFF like ‘ % PROTOCOLLO %’
L’operazione di confronto da risultato TRUE se la parola PROTOCOLLO è contenuta nel valore di NOMEUFF
La sottostringa rappresentata da % può anche essere vuota
select NOMEUFF, PECMAIL from UFFICI where NOMEUFF like ‘% PROTOCOLLO %’
Tutte le tuple che nel nome dell’ufficio hanno la parola PROTOCOLLO
select *
from IMPIEGATI
where INDIRIZZO like ‘% BENEVENTO %’
select *
from DSTAFF
where NOME like ‘ PAOLO D_ MA_O’
Di Majo, De Majo, Di Maio, De Maio, Da Maio … Di Maro, De Maro, …
Beneventani o no, ma … abitano qui
SQL e Algebra Relazionale
• SQL è un linguaggio di interrogazione dichiarativo: – Specifica l’obiettivo dell’interrogazione ( una
descrizione del risultato desiderato e delle sue caratteristiche)
• L’Algebra Relazionale è un linguaggio di interrogazione procedurale: – Descrive le operazioni ( un algoritmo) da fare per
ottenere il risultato desiderato
N.B. … ovviamente SQL è fondato sui principi del calcolo relazionale …
• ESERCIZIO 8
– Nome e Qualifica di tutti gli impiegati che hanno uno stipendio > 70000 euro e lavorano nel settore diretto da Mario Esposito
Quante e quali relazioni saranno coinvolte nella clusola from ?
Ci sarà davvero il prodotto cartesiano di tali relazioni?
• Le operazioni ovvero gli algoritmi che verranno eseguiti per ottenere il risultato descritto da interrogazioni SQL verranno definiti dal DBMS
• … che seguirà criteri di efficienza compilando interrogazioni equivalenti «ottimizzate» nel linguaggio procedurale interno
Interrogazione SQL
Interrogazione Procedurale Interna QUERY
OPTIMIZER
• Chi scrive interrogazioni SQL può trascurare gli aspetti di ‘ottimizzazione’ per l’efficienza, e anteporre l’esigenza di CHIAREZZA
• … chi lavora ai ‘query optimizer’ dei DBMS …
• … tools sempre più sofisticati ed efficienti, fondati su raffinate tecniche di ottimizzazione …
Esempio: … evitare quanto più è possibile prodotti cartesiani …
• ESERCIZIO 8: codice , nomi ‘operai’ e nome settore di appartenenza
• ESERCIZIO 9: codice, nome, nome ufficio e nome settore di ogni impiegato
• ESERCIZIO 10: tutti gli impiegati che hanno uno stipendio superiore a 70000 euro e hanno come dirigente di settore Mario Esposito
sia interrogazione in SQL che in Algebra Relazionale
Esplicite Operazioni di Join
• Abbiamo sottolineato come le operazioni di Theta Join ( Equi-Join inclusa ) possono essere implicitamente realizzate inserendo nella clausola where le condizioni di giunzione
• SQL – 2 offre anche la possibilità di ricorrere ad un esplicito operatore di Join inseribile direttamente nella clausola from dove vengono naturalmente spostate le condizioni di ϴ - Join
• select *
from R1, R2
where p
• select *
from R1 join R2 on p
R1 ⋈p R2 = σp (R1 X R2)
Join Implicito
Join Esplicito
NB. p: confronto fra un attributo di R1 ed uno di R2
Codice Nomeprog
INGINF42 ISIDE
INGINF03 SINTESI
Ricercatore Progetto
Visaggio INGINF42
Mercaldo INGINF42
Visaggio INGINF03
Codice Nomeprog Ricercatore Progetto
INGINF42 ISIDE Visaggio INGINF42
INGINF42 ISIDE Mercaldo INGINF42
INGINF03 SINTESI Visaggio INGINF03
Progetti
Ricercatori
Progetti ⋈𝑪𝒐𝒅𝒊𝒄𝒆=𝑷𝒓𝒐𝒈𝒆𝒕𝒕𝒐 Ricercatori
select * from Progetti P join Ricercatori R on P.CODICE = R. Progetto
Codice Nomeprog Ricercatore
INGINF42 ISIDE Visaggio
INGINF42 ISIDE Mercaldo
INGINF03 SINTESI Visaggio
select P.CODICE, NOMEPROG, RICERCATORE from Progetti P join Ricercatori R on P.CODICE = R. Progetto
aggiorniamo la clausola from …
from Relazione [[ as ] alias ] {[ TipoJoin] join Relazione [[as] alias] on CondJoin} dove TipoJoin è una delle seguenti qualificazioni: natural right [outer] left [outer] full [outer] inner
• Riscrivere soluzione ESERCIZIO 6 utilizzando l’operatore Join
• Riscrivere soluzione ESERCIZIO 7 utilizzando l’operatore Join
• select SETTORI.NOMSET, DSTAFF.NOME
from SETTORI join DSTAFF
on SETTORI.DIRIGENTE =DSTAFF.COD
• select UFFICI.NOMEUFF, DSTAFF.NOME
from UFFICI join DSTAFF
on UFFICI.CAPUFF = DSTAFF.COD
Nome Cognome Seduta
Ciro Bianchi 2
Alba Verde 1
Rosa Aulente 2
Fosco Avaro 1
Nome Cognome Tasse
Aldo Rossi Si
Rosa Spinosa No
Ciro Bianchi Si
Alba Verde Si
Fosco Avaro No
Nome Cognome
Seduta Tasse
Ciro Bianchi 2 Si
Alba Verde 1 Si
Rosa Aulente 2 NULL
Fosco Avaro 1 No
Prenotati InRegola
Prenotati ⋈left 𝐈𝐧𝐑𝐞𝐠𝐨𝐥𝐚
select P.NOME, P.COGNOME, SEDUTA, TASSE
from PRENOTATI P left join INREGOLA I
on (P.NOME = I.NOME) ⋀ (P.COGNOME =
I.COGNOME)
Nome Cognome Seduta
Ciro Bianchi 2
Alba Verde 1
Rosa Aulente 2
Fosco Avaro 1
Nome Cognome Tasse
Aldo Rossi Si
Rosa Spinosa No
Ciro Bianchi Si
Alba Verde Si
Fosco Avaro No
Prenotati InRegola
Nome Cognome
Seduta Tasse
Ciro Bianchi 2 Si
Alba Verde 1 Si
Aldo Rossi NULL Si
Rosa Spinosa NULL No
Fosco Avaro 1 No
Prenotati ⋈right 𝐈𝐧𝐑𝐞𝐠𝐨𝐥𝐚
select P.NOME, P.COGNOME, SEDUTA, TASSE
from PRENOTATI P right join INREGOLA I
on (P.NOME = I.NOME) ⋀ (P.COGNOME =
I.COGNOME)
• ESERCIZIO 11
– Ancora sugli esercizi 6 e 7
• Invece di join fare l’operazione di left join e … commentare
• Invece di join fare l’operazione di right join e spiegarne … commentare
DIRIGENTE e CAPUFF … e valori NULL
OPERAZIONI NON RELAZIONALI
• Abbiamo già visto l’uso di operatori aritmetici nella clausola select
• … introduciamo la possibilità di introdurre una relazione d’ordine fra le tuple del risultato di una query
• NB. Nell’algebra relazionale le tuple delle istanze di una relazione sono non ordinate ..
la clausola order by
• Poiché gli attributi di una relazione possono essere di tipo ordinato ( es. il tipo carattere ed i tipi numerici), è possibile pensare di ordinare le tuple secondo i valori di uno degli attributi ordinati ( … ordinare le righe sulla base dell’ordinamento dei valori di una colonna)
order by AttributoDiOrdinamento [ asc | desc ]
{, AttributoDiOrdinamento [ asc | desc ]}
• Opzione: asc per ordinamento ascendente, desc per ordinamento discendente, per default è ascendente – asc ordinamento naturale del tipo
• 𝒂𝟏 , 𝒂𝟐, … 𝒂𝒏 ∀i 𝒂𝒊 prec ( 𝒂𝒊+𝟏 )
– desc ordinamento inverso rispetto a quello naturale • 𝒂𝟏 , 𝒂𝟐, … 𝒂𝒏 ∀i 𝒂𝒊 𝒔𝒖𝒄𝒄 ( 𝒂𝒊+𝟏 )
• Esempio: select * from DSTAFF order by NOME
fornisce la tabella del personale di staff ( capuffici, capisettore, ..) ordinata alfabeticamente secondo i nomi ….
• Prima ordinamento secondo il primo attributo della lista; per righe che per tale attributo presentano lo stesso valore, si ordina secondo l’attributo successivo … e così via
• Esempio:
select *
from DSTAFF
order by NOME, COD desc
… a parità di Nome viene prima quello con il COD più piccolo …
Nome Cognome Seduta
Ciro Bianchi 2
Alba Verde 1
Rosa Aulente 2
Ottavio Bianchi Si
Bianca Verde Si
Fosco Avaro 1 Nome Cognome
Rosa Aulente
Fosco Avaro
Ottavio
Bianchi
Ciro Bianchi
Bianca Verde
Alba Verde
select Nome, Cognome from Prenotati order by Cognome, Nome desc
Operatori su aggregati
• Un operatore che applicato ad una relazione ci consenta di estrarne la cardinalità ( … numero di tuple, … numero righe della tabella …)
• L’operatore count (*) nella clausola select
Esempio: select count (*) from PRENOTATI Restituisce come risultato il numero dei prenotati
• È possibile fare l’operazione di conteggio count sui valori di un attributo o di un insieme di attributi ?
• … e se non è coinvolta una chiave ( … e quindi sono possibili ripetizioni di valori) stabilire se il conteggio deve riguardare tutti i valori o solo quelli diversi ( … non conteggio dei doppioni) ?
Count (* | ([distinct|all ] ListaAttributi )
NB. Per default si ha ALL ( conta anche i doppioni ) ALL non conta i NULL
select count (*) from IMPIEGATI
select count (*) from IMPIEGATI
where QUALIFICA = ‘OPERAI ’
select count (distinct DIRIGENTI) from SETTORI
Numero Impiegati
Numero Dirigenti
Numero Operai
• I valori NULL nel count su un attributo non vengono considerati
Nome Cognome Seduta Tasse
Ciro Bianchi 2 Si
Alba Verde 1 Si
Rosa Aulente 2 NULL
Fosco Avaro 1 No
ESAMI2
select count(*) from ESAMI2 Risultato : 4
select count(TASSE) from ESAMI2 Risultato : 3
select count ( distinct TASSE) from ESAMI2 Risultato: 2
• Operazioni su tutti i valori di un attributo ( … i valori di una colonna …)
– Quando il tipo dell’attributo è numerico si possono fare
• Somma di tutti i valori
• Media di tutti i valori
– Quando il tipo è ordinato
• Individuazione del Massimo
• Individuazione del Minimo
NB. I valori NULL non vengono presi in considerazione
ALTRE OPERAZIONI
• Inserire l’operazione nella clausola select secondo
op ([distinct|all ] NomeAttributo )
• Dove op è una fra
sum
max
min
avg
select avg ( all STIPENDIO) from IMPIEGATI
select avg ( distinct STIPENDIO) from IMPIEGATI
select min ( STIPENDIO) from IMPIEGATI
select sum ( all PREMIPROD) from IMPIEGATI
select max ( PREMIPROD) from IMPIEGATI
Costo Medio Stipendi
Stipendio Medio
Stipendio Minimo
Montepremio produzione
Premio Prod
Massimo
select min ( STIPENDIO), max (STIPENDIO), avg (distinct STIPENDIO)
from IMPIEGATI
select min ( STIPENDIO), max (STIPENDIO), avg (distinct STIPENDIO)
from IMPIEGATI
where QUALIFICA = ‘OPERAI’
select sum ( PREMIPROD), avg (distinct STIPENDIO), avg (PREMIPROD)
from IMPIEGATI
where SESSO = ‘F’
Stipendio Minimo, Massimo e Medio di un dipendente
Montepremio, Stipendio e Premio Produzione
Medio delle donne
Stipendio Minimo, Massimo e Medio di un operaio
Le clausole di raggruppamento
• È possibile applicare le operazioni count, min, max, avg, sum su aggregati che non siano costituiti da tutte le tuple (righe) della relazione (tabella)?
• E’ possibile farlo grazie ad una clausola che consente di partizionare l’insieme delle tuple definite da from e where in sottoinsiemi di tuple (righe) aventi lo stesso valore per uno o più attributi (una o più colonne)
La clausola group by
• La clausola :
group by ListaAttributi definisce la organizzazione delle tuple in gruppi aventi lo stesso valore per gli attributi di ListaAttributi
Esempio:
select SETTORE, sum ( PREMIPROD)
from IMPIEGATI
group by SETTORE
SETTORE PREMI PROD
ST1 6000
ST3 5300
ST1 2400
ST2 8000
ST3 2700
ST2 15000
ST1 7500
SETTORE PREMIPROD
ST1 6000
ST1 2400
ST1 7500
ST3 5300
ST3 2700
ST2 8000
ST2 15000
SETTORE SUM(PREMIPROD)
ST1 15900
ST3 8000
ST2 23000
Settore e Premiprod da IMPIEGATI
...il raggruppamento …
...il risultato …
• ESERCIZIO 12
come nell’esempio precedente ma anche con nome dei settori
WARNING
Se si usa la clausola group by, gli attributi usati nella clausola select debbono far parte di quelli usati
nella stessa clausola group
• ESERCIZIO 13
Numero di impiegati maschi e donne
select SESSO , count(*)
from IMPIEGATI
group by SESSO
• ESERCIZIO 14
Settori in codice e nome col numero di dipendenti
select I.SETTORI, S.NOMESET, count (*)
from SETTORI S join IMPIEGATI I
on I.SETTORE= S.CODICE
group by I.SETTORE, S.NOMESET
La clausola having
• … la possibilità di selezionare fra gli aggregati prodotti da group solo quelli che soddisfano una condizione p
having p
Esempio: select SETTORE, sum ( PREMIPROD) from IMPIEGATI group by SETTORE having sum (PREMIPROD) > 8500
SETTORE SUM(PREMIPROD)
ST1 15900
ST3 8000
ST2 23000
SETTORE SUM(PREMIPROD)
ST1 15900
ST2 23000
con clausola having > 8500
Senza clausola having
• Possiamo avere predicati p sia nella clausola where che nella clausola having
• Distingueremo:
– Predicati semplici : espressioni senza operatori su aggregati
– Predicati su aggregati : espressioni con operatori su aggregati ( count, sum, avg, min, max)
È opportuno: Nella clausola having SOLO PREDICATI SU AGGREGATI
…invece, predicati semplici in clausola where
ESERCIZIO 15 Codice , nome e numero di dipendenti dei settori con media di premi produzione superiore ad 800
select I.SETTORI, S.NOMESET, count (*)
from SETTORI S join IMPIEGATI I
on I.SETTORE= S.CODICE
group by I.SETTORE, S.NOMESET
having avg (PREMIPROD) > 800
ESERCIZIO 16 Codice, Nome, stipendio minimo, stipendio massimo, stipendio medio di tutti gli uffici con più di due dipendenti
Istruzione select SQL
– Due sezioni fisse introdotte dalle clausole
• select
• from
– Quattro sezioni opzionali introdotte dalle clausole
• where
• group by
• having
• order by
IstruzioneSELECT : : = select ListaAttrEspr from ListaTabelle [ where CondizioneSemplice] [ group by ListaAttrDiRaggruppamento ] [ having CondizizioneSuAggregati ] [ order by ListaAttrDiOrdinamento ]
OPERAZIONI INSIEMISTICHE
• SQL mette a disposizione gli operatori
– union per l’Unione
– intersect per l’intersezione
– except per la differenza
• … con la seguente sintassi
IstruzioneSelect ( union|intersect|except ) IstruzioneSelect
• Date le relazioni
STUDENTIDB ≡ { MATRICOLA, NOME, COGNOME }
STUDENTIPROG ≡ { MATRICOLA, NOME, COGNOME }
select (*)
from STUDENTIDB
union
select (*)
from STUDENTIPROG
select (*) from STUDENTIDB intersect select (*) from STUDENTIPROG
select (*) from STUDENTIDB except select (*) from STUDENTIPROG
Studenti sia di DB che di PROG
Studenti di DB Ma non di PROG
• select NOMEUFF
from UFFICI
union
select NOMESET
from SETTORI
Elenco Settori ed Uffici
È possibile perché NOMEUFF e NOMESET sono dello stesso tipo varchar (50)
NB. Essendo operazioni su insiemi vengono eliminati eventuali doppioni …
se si vogliono mantenere aggiungere l’opzione all IstSelect ( union|intersect|except ) [ all ] IstSelect
INTERROGAZIONI NIDIFICATE NELLA CLAUSOLA where
• Nella clausola where p, il predicato p può essere costruito con operazioni logiche su insiemi, ricorrendo in particolare agli operatori di
appartenenza
∈ ∉ quantificazione esistenziale ed universale
∃ ∀
• L’insieme a cui applicare un operatore di appartenenza o di esistenza può essre ottenuto attraverso il NESTING di una istruzione select nella clausola where
• …interpretando il risultato della select innestata come un insieme di tuple
• …al quale si possono applicare gli operatori SQL IN NOT IN ANY ALL
• Esempio: vogliamo conoscere il nome dei Settori il cui dirigente percepisce uno stipendio superiore a 150000 euro
select NOMESET from SETTORI where DIRIGENTE in ( select COD from DSTAFF where STIPENDIO> 150000 )
ESERCIZIO 17 : formulare la stessa interrogazione senza far ricorso al nesting ( nidificazione) di query
Condizioni in where con any o all
• La condizione p di where può essere costruita secondo la sintassi
– A ϴ any T dove : T è un insieme (di valori) , A è (il valore di) un attributo
ϴ è un op di confronto
semantica ∃ x : xЄT and Aϴ x
• Oppure
– A ϴ all T ∀ xЄT Aϴ x
• ESEMPIO: vogliamo i nomi dei settori in cui non lavorino impiegati di cognome Rossi
select NOMESET from SETTORI where CODICE <> all ( select SETTORE from IMPIEGATI where NOME = (% ROSSI) )
• ESERCIZIO 18 : formulare la stessa interrogazione senza far ricorso al nesting ( nidificazione) di query
• ESERCIZIO 19: nomi impiegati con lo stipendio più elevato
select NOME
from IMPIEGATI
where STIPENDIO
>= all ( select STIPENDIO
from IMPIEGATI
)
• ESERCIZIO 20: nomi dei dirigenti che non sono al massimo stipendio
select NOME
from DSTAFF
where STIPENDIO
< any ( select STIPENDIO
from DSTAFF
)
• ESERCIZIO 21: nomi impiegati che lavorano in uno degli uffici di protocollo
select NOME from IMPIEGATI where UFFICIO = any ( select COD_UFF from UFFICI where NOMEUFF = (% PROTOCOLLO%) )
Un altro operatore
• Un operatore per verificare che il risultato di una query innestata non sia VUOTO
exists Query
Ne vedremo nel seguito qualche esempio di applicazione
• … è possibile utilizzare il risultato di una query direttamente in una operazione di confronto senza ricorrere agli operatori di appartenenza o di quantificazione
• … ciò è possibile se il risultato della query innestata è costituito da un sol valore ( e, quindi, anche una sola tupla/riga)
• ESEMPIO ESAMIDB ≡ { MATR, NOME, COGNOME, DATA, VOTO}
select NOME, COGNOME from ESAMIDB where VOTO > ( select avg (VOTO) from ESAMIDB)
Studenti con voto superiore alla media
ESERCIZIO 22 Nome degli impiegati che hanno uno stipendio superiore alla media ESERCIZIO 23 Nome degli impiegati che hanno uno stipendio superiore a quello di uno o più dirigenti ESERCIZIO 24 Nome degli impiegati che guadagnano più del dirigente del proprio settore ESERCIZIO 25 Nome dei Capiufficio con stipendio più alto di quello del proprio dirigente di settore
Select NOME from IMPIEGATI I where STIPENDIO > ( select STIPENDIO from DSTAFF D, SETTORI S where I.SETTORE = S.CODICE and S.DIRIGENTE = D.COD )
Una soluzione ES 24
Passaggio di BINDING
REGOLE DI VISIBILITA’
• In un nesting di query ciascuna di esse può far riferimento a ciò che è definito nelle query più esterne ( quella Q1 in cui è innestata, la Q2 in cui è innestata Q1, … etc.) ( salvo eventuali ridefinizioni locali )
• Non vale il contrario, né è possibile riferire ciò che è definito in query che non siano un diretto ascendente nella propria catena di innesto ( … non è possibile riferirsi a … fratelli, zii, etc. )
• ESERCIZIO 26 Estrarre da Impiegati quelli che hanno degli impiegati omonimi select * from IMPIEGATI I where exists ( select * from IMPIEGATI I2 where I2.NOME = I.NOME and I2.COD < > I.COD ) Uso di
exists
DUE RIFERIMENTI ALLA QUERY ESTERNA
ESERCIZIO 27 Vogliamo i Capiufficio degli uffici appartenenti al settore ragioneria o a settori che hanno lo stesso dirigente della ragioneria select CAPUFF from UFFICI where SET_COD in (select COD from SETTORI S where NOMSET = ‘ RAGIONERIA’) or in ( select COD from SETTORI S1 where S. DIRIGENTE = S1.DIRIGENTE)
ERRORE: tutto ciò che Riguarda S NON E’ VISIBILE da questa query
Formulare una soluzione Corretta dell’esercizio
Risolvere con e senza operatore exists i seguenti esercizi
ESERCIZIO 28
Nome Stipendio dipendenti che lavorano in uffici con sede a Benevento
ESECIZIO 29
Nome uffici in cui lavorano donne
… qualche avvertenza …
• Il ricorso a query innestate ( dette anche subquery) può porre problemi di efficienza ma può offrire vantaggi di maggiore leggibilità
• … sulla ottimizzazione e sulla efficienza, rilevante differenza fra nesting con e senza passaggio di binding
• … in assenza di binding, una subquery può essere eseguita una sola volta e prima di analizzare le tuple ( righe) della query che la contiene
• … in presenza di binding la subquery deve essere eseguita per ogni tupla della query esterna
NB. Le subquery NON POSSONO contenere gli operatori inisemistici ( UNION, INTERSECT, EXCEPT)
DD Definizione dei dati
DM Modifica dei Dati
QL Interrogazioni
OPERAZIONI DI MODIFICA
• Operazioni sulla istanza di una relazione
– Modifica dell’insieme delle tuple
– Modifica dei valori di una o più tuple
• Operazioni sullo schema della tabella
– Modifica della Lista degli attributi
– Modifica di vincoli
MODIFICA DELL’ISTANZA
• Sono operazioni di inserimento, eliminazione o aggiornamento ( cambiamenti di valori) di una o più tuple ( righe ) di una relazione (tabella)
insert
delete
update
Operazione insert
• insert into NomeRelazione [ ListaAttributi]
Values (ListaValori | IstrSelect)
Opzione ListaValori : l’inserimento si riferisce ad una sola riga e la lista specifica i valori delle colonne Opzione IstrSelect : l’inserimento si riferisce a tutte le righe del risultato dell’operazione select
• ESEMPIO Insert into DSTAFF (COD, NOME, STIPENDIO) values ( 00123 , ‘SIMEONE PAOLI’, 57000) È l’istruzione usata nel «caricamento dati» da parte di un utente o di «un terminalista»; in questi casi, in generale, l’istruzione viene ‘nascosta’ attraverso ‘maschere (o form)’ che agevolano l’utente guidandolo all’inserimento dati ‘voce’ ( attributo) per voce
La corrispondenza fra Attributi e Valori è POSIZIONALE
• ESEMPIO insert into CNTRLMEDICO ( COD, NOME) ( select COD, NOME from IMPIEGATI where QUALIFICA = (‘OPERAIO’) ) insert into CNTRLMEDICO ( COD, NOME) ( select COD, NOME from IMPIEGATI where UFFICIO in ( select UFF_COD from UFFICI where NOMEUFF = ( ‘% LABORATORIO% ‘) ) )
Inserimento nella tabella Delle visite mediche di
Tutti gli operai
Inserimento ….. quelli che Lavorano nei laboratori
Nesting di select in Insert into
Nesting di select in Insert into
e di select in select
WARNING
• Naturalmente, il tipo degli attributi delle tuple da inserire ( nel caso di select) o dei valori di una nuova tupla ( nel caso di values) deve coincidere con quello dei corrispondenti attributi nella lista di insert into
• Se nell’inserimento non vengono specificati i valori di tutti gli attributi, a quelli mancanti vengono assegnati i valori di default ed in assenza il valore NULL
• … ma, se viene violato un vincolo di NOT NULL l’inserimento VIENE RIFIUTATO, e lo stesso accade, naturalmente, se viene violato un vincolo di referenza esterno
OPERAZIONE delete
delete from NomeRel [ where Condizione] Se non viene usata l’opzione where, tutte le tuple di NomeRel vengono cancellate ( la tabella viene svuotata, … salvo l’intestazione) Se viene usata l’opzione where, vengono cancellate solo le tuple (righe) che soddisfano la Condizione specificata - naturalmente la condizione può essere costruita anche usando IstrSelect innestate
• ESEMPI
delete from PRENOTATIDB
delete from CNTRLMEDICO
In ogni seduta esami, … svuoto la tabella … che si riempirà per la prossima
seduta
Ad ogni seduta di visita medica svuoto ….
Operazione preziosa per tutte le tabelle permanenti … ma con ciclo di vita breve delle istanze
• ESEMPI delete from ESAMIDB where TASSE = ‘NO’ delete from IMPIEGATI where QUALIFICA = ‘GIARDINIERE’
ESERCIZIO 30 In tutti i settori con più di 10 impiegati cancellare quello con il codice più piccolo. Delete from IMPIEGATI where ( SETTORE, COD) in ( select SETTORE, min (COD) from IMPIEGATI grouped by SETTORE having count (*) > 10 ) ESERCIZIO 31 Cancellare gli uffici che non hanno impiegati
Crisi … taglio giardini e .. giardinieri
Cisi: taglio un dip per ogni settore
più grande
Il problema dei vincoli di referenza esterna in delete
• … se l’operazione di cancellazione coinvolge Attributi RIFERITI ( o referenziati ) da Attributi Referenti in altra relazione , che succede?
Esempio: se nella relazione UFFICI cancello un ufficio ( … una riga …) cosa succede nella relazione IMPIEGATI ( … che fine fanno gli impiegati dell’ ufficio cancellato ) visto che nella create table di definizione risulta: ………. SETTORE char (3) UFFICIO char (3) ………. foreign key ( SETTORE, UFFICIO ) references UFFICI (SET_COD, UFF_COD) ……….
?
• SQL mette a disposizione la possibilità di scegliere fra 4 possibili azioni:
cascade , set null , set default , no action
– cascade : propagazione a cascata delle cancellazione;
tutte le righe della tabella referente vincolate alla riga cancellata vengono a loro volta cancellate
Esempio: gli impiegati che stavano nell’ufficio cancellato vengono a loro volta cancellati
– set null : all’attributo referente, in sostituzione del valore cancellato nell’attributo riferito, viene assegnato il valore null
Esempio: agli impiegati che lavoravano nell’ufficio cancellato viene assegnato un ufficio NULL, in attesa di … definizione
– set default : all’attributo referente, in sostituzione del valore cancellato nell’attributo riferito, viene assegnato il valore di default
Esempio: gli impiegati dell’ufficio cancellato vengono assegnati ad un ufficio di default se è stato definito ( … nel nostro esempio non c’è)
– no action : LA CANCELLAZIONE VIENE NEGATA
Esempio: se vuoi cancellare l’ufficio devi prima licenziarne gli impiegati o collocarli in altro ufficio non cancellato
• Il tipo di azione da attivare NON PUO’ essere decisa dall’istruzione di cancellazione, e quindi non può essere definita ad ogni specifica istruzione di delete
• … ma è una caratteristica di schema che deve essere decisa nel momento della definizione del vincolo di referenza esterno nella create table, dove la sintassi della classe references ( associata o meno ad una foreign key) è :
references RelRiferita ( ListaAttrRiferiti) [on (delete | update) ( cascade |set null | set default| no action)
• Per default : no action
create table STUD create PRENOTATIDB
( (
MATRICOLA numeric (6) MATR numeric (6)
NOME varchar (30) references STUD (MATRICOLA)
COGNOME varchar (40) on delete cascade
………………………………….. NOMECOGN varchar (70)
………………………………….. E_MAIL varchar (100)
) )
Con qualsiasi istruzione delete from STUD …… per ogni riga cancellata con valore di MATRICOLA x Vengono cancellate anche le righe di PRENOTATIDB aventi x come valore di MATR
OPERAZIONE update
• L’operazione viene definita attraverso 3 clausole: – la clausola di apertura introdotta dalla parola chiave
update specifica la Relazione (Tabella) su cui debbono essere fatti gli aggiornamenti
– la clausola obbligatoria introdotta dalla parola chiave set che, per ogni attributo da aggiornare descrive la relativa istruzione di assegnazione di nuovo valore
– la clausola opzionale introdotta dalla parola chiave where per selezionare le tuple (righe) che debbono essere aggiornate • Nel caso di non uso della clausola where, il valore degli
attributi da modificare verrà aggiornato ( calcolato ed assegnato) in tutte le tuple della relazione
… la sintassi di update …
Update NomeRel
set Attributo = ( Espressione | IstrSelect | null | default )
{ , Attributo = ( Espressione | IstrSelect | null| default )}
where Condizione
NB. = , associato a set, diventa un operatore di assegnazione di valore
NB. Per i vincoli di referenza esterna vale quanto detto per delete
• ESEMPIO: Portare a 70000 gli stipendi dei dirigenti che sono al di sotto di tale cifra
update DSTAFF set STIPENDIO = 70000 where STIPENDIO < 70000 • ESEMPIO: Il nuovo dirigente del settore ‘ DESIGN’ è Gae
Aulenti; assume lo stesso codice e lo stesso stipendio del predecessore
update DSTAFF set NOME = ‘GAE AULENTI’ where COD = ( select CODICE from SETTORI where NOMESET = ‘DESIGN’ )
Op di assegnazione
Op di confronto
Op di confronto
warning
update IMPIEGATI
set STIPENDIO = STIPENDIO ⋆ 1.1
where STIPENDIO <= 30000
update IMPIEGATI
set STIPENDIO = STIPENDIO ⋆ 1.15
where STIPENDIO > 30000
update IMPIEGATI
set STIPENDIO = STIPENDIO ⋆ 1.15
where STIPENDIO > 30000
update IMPIEGATI
set STIPENDIO = STIPENDIO ⋆ 1.1
where STIPENDIO <= 30000
NON SONO LA STESSA COSA !
MODIFICA DELLO SCHEMA
• Aggiungere un nuovo attributo in schema definito con create table:
alter table NomeRel add [ column ] DefAttributo
NomeRel è il nome della relazione(tabella) alla quale si vuole aggiungere un nuovo attributo ( colonna)
DefAttributo è costituito dal nome, dal tipo e da eventuali vincoli ( es. valore di default, etc.)
La colonna aggiunta viene riempita col valore di default se c’è, altrimenti con NULL
• Eliminare un attributo in una relazione definita con create table:
alter table NomeRel drop [ column ] Attributo [ restrict| cascade] NomeRel è il nome della relazione(tabella) nella quale si vuole eliminare un attributo, Attributo è il nome dell’attributo da eliminare restrict la eliminazione è rifiutata se l’Attributo è esplicitamente citato in altre entità (ad esempio se è riferito (referenziato) in altra relazione cascade la eliminazione è estesa agli attributi referenti NB. per default : restrict
NB. NON E’ CONSENTITO ELIMINARE L’UNICA COLONNA DI UNA RELAZIONE DI ORDINE 1 ( Tabella con una sola colonna)
• ESEMPI
alter table IMPIEGATI add column
DATASSUNZIONE date
alter table IMPIEGATI drop column TEL_UFF
agli IMPIEGATI aggiungiamo la data di assunzione
…taglio dei telefoni …
Modifica valore di default di un attributo
alter table NomeRel alter [ columnn] Attributo
( set default NuovoVal | drop default)
drop default cancella il default preesistente
set default definisce nuovo valore di default
ESEMPIO alter table IMPIEGATI alter column STIPENDIO set default 18000
CANCELLAZIONE DI TABELLA
In SQL viene messa a disposizione l’istruzione distruttiva drop, che, con riferimento alla cancellazione di una relazione, può essere così definita
drop table NomeRel restrict | cascade
restrict l’eliminazione della tabella viene negata se la stessa è usata ( es. riferita) nella definizione di altre entità DDL
cascade tutto ciò che fa riferimento alla tabella che viene cancellata viene revocato, annullato, cancellato
CONCLUSIONI
• L’operazione drop è molto più articolata e complessa;
• In generale, le istruzioni di modifica dello schema vanno inquadrate in un set di operazioni che si estendono ad entità SQL che non abbiamo trattato;
• Alcuni ulteriori arricchimenti verranno fatti nelle esercitazioni, anche con specifico apprendimento di «Complementi di SQL»
