SQL

Concetti base

Gli standard

• E’ un linguaggio fondato su uno Standard,ovvero su un documento formale didefinizione prodotto dalla ISO ( InternationalStandard Organization ) che ne cura anchel’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

SQLISO 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, ….)

DDDefinizione dei dati

DMModifica dei Dati

QLInterrogazioni

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 cifresignificative (1 per default); Scala è una costanteintera che indica il numero di cifre dopo il puntodecimale (0 per default)

esempinumeric (4) -9999 +9999numeric ( 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

integersmallint

• 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 smallintdefault ‘null’check ( value >= 18 and value <= 30)

La principale istruzione DD diSQL

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)

………………….

)

sSono 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)

• …nella definizione dell’attributo referente

– foreign key ( ListaAttributiReferenti) referencesRELRIFERITA ( ListaAttributiRiferiti)

• …corrispondenza per posizione nelle liste

NB . Gli attributi Riferiti debbono formare chiave o essere vincolati UNIQUE

DDDefinizione dei dati

DMModifica dei Dati

QLInterrogazioni

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 ListaAttributifrom 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, Cfrom 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, DATAfrom 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 DATAfrom 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 DATAfrom 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 ridenominareattributi di una relazione …

…e farne una proiezione

• … un terzo ‘arricchimento’: la possibilità di definire nuovi attributi derivandoli conespressioni 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 aritmetica .. .

• …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 ESAMIwhere 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 ( …e anche alla selezione di attributi inselect) ,è 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. AttYfrom R1, R2, …..Rnwhere 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 Rifacciamo riferimento con Ri.AX,

• Ricordando che Se gli schemi degli operandi non hannoattributi uguali, Join Naturale coincide col Prodottocartesiano,

select R1.AttA, …. , Rh. AttYfrom R1, R2, …..Rnwhere p

𝝅𝑹𝟏.𝑨𝒕𝒕𝑨,…,𝑹𝒉.𝑨𝒕𝒕𝒀 (σ𝒑 (R1 ⋈ R2 ⋈ … Rn)

sono equivalenti anche

create table IMPIEGATI(

COD numeric (5) primary KeyNOME char (40) not nullINDIRIZZO varchar (200)SESSO charSETTORE char(3)UFFICIO char (3)TEL_UF numeric (9) uniqueSTIPENDIO numeric (7,2) default 0PREMIPROD numeric (6,2) default 0QUALIFICA varchar ( 20 )foreign key (UFFICIO, SETTORE)

references UFFICI ( UFF_COD, SET_COD) )

create table DSTAFF(

COD numeric(5) primary keyNOME 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 keyNOMESET varchar (50) uniqueDIRIGENTE 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 IMPIEGATIwhere SESSO= ‘F’

• ESERCIZIO 4 : elenco impiegate con ufficio e telefono– select NOME, UFFICIO, TEL_UF

from IMPIEGATIwhere SESSO= ‘F’

• ESERCIZIO 5 : stipendio mensile operai senza premio di produttività – select COD, NOME, STIPENDIO/12 as MENSILE

from IMPIEGATIwhere 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 whereattraverso 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 distringatezza ed agilità e quella di identificatori ‘significativi’ …Anteporre sempre la esigenza di chiarezza ed agevolericonoscimento ‘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, PECMAILfrom UFFICIwhere 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 cheverranno eseguiti per ottenere il risultatodescritto da interrogazioni SQL verrannodefiniti dal DBMS

• … che seguirà criteri di efficienza compilandointerrogazioni equivalenti «ottimizzate» nellinguaggio procedurale interno

Interrogazione SQL

Interrogazione Procedurale InternaQUERY

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 diTheta Join ( Equi-Join inclusa ) possono essereimplicitamente realizzate inserendo nellaclausola where le condizioni di giunzione

• SQL – 2 offre anche la possibilità di ricorrere adun esplicito operatore di Join inseribiledirettamente nella clausola from dove vengononaturalmente 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, RICERCATOREfrom 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:naturalright [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 tipoordinato ( es. il tipo carattere ed i tipi numerici), è possibilepensare di ordinare le tuple secondo i valori di uno degliattributi ordinati ( … ordinare le righe sulla basedell’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 DSTAFForder 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, Cognomefrom Prenotatiorder 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 suivalori di un attributo o di un insieme di attributi ?

• … e se non è coinvolta una chiave ( … e quindi sonopossibili ripetizioni di valori) stabilire se il conteggiodeve 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 selectsecondo

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 MedioStipendi

Stipendio Medio

Stipendio Minimo

Montepremioproduzione

Premio ProdMassimo

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 PremioProduzione

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 tupledefinite 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 ListaAttributidefinisce la organizzazione delle tuple in gruppi aventilo stesso valore per gli attributi di ListaAttributi

Esempio:

select SETTORE, sum ( PREMIPROD)

from IMPIEGATI

group by SETTORE

SETTORE PREMIPROD

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 Premiprodda IMPIEGATI

...il raggruppamento …

...il risultato …

• ESERCIZIO 12

come nell’esempio precedente ma anche connome 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 IMPIEGATIgroup by SETTOREhaving 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 havingSOLO PREDICATI SU AGGREGATI

…invece, predicati semplici in clausola where

ESERCIZIO 15Codice , 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 16Codice, 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 ListaAttrEsprfrom 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

intersectselect (*)

from STUDENTIPROG

select (*)from STUDENTIDB

exceptselect (*)

from STUDENTIPROG

Studenti sia diDB che di PROG

Studenti di DBMa 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 allIstSelect ( 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ò essreottenuto attraverso il NESTING di una istruzione select nella clausola where

• …interpretando il risultato della selectinnestata 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 NOMESETfrom SETTORIwhere DIRIGENTE

in ( select CODfrom DSTAFFwhere STIPENDIO> 150000

)

ESERCIZIO 17 : formulare la stessa interrogazionesenza far ricorso al nesting( nidificazione) di query

Condizioni in where conany 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 NOMESETfrom SETTORIwhere CODICE

<> all ( select SETTOREfrom IMPIEGATIwhere NOME = (% ROSSI)

)

• ESERCIZIO 18 : formulare la stessa interrogazionesenza 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 NOMEfrom IMPIEGATIwhere UFFICIO

= any ( select COD_UFFfrom UFFICIwhere 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 querydirettamente 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)

• ESEMPIOESAMIDB ≡ { MATR, NOME, COGNOME, DATA, VOTO}

select NOME, COGNOMEfrom ESAMIDBwhere VOTO > ( select avg (VOTO) from ESAMIDB)

Studenti con votosuperiore alla media

ESERCIZIO 22Nome degli impiegati che hanno uno stipendio superiore alla media

ESERCIZIO 23Nome degli impiegati che hanno uno stipendio superiore a quello di uno o più dirigenti

ESERCIZIO 24Nome degli impiegati che guadagnano più del dirigente del proprio settore

ESERCIZIO 25Nome dei Capiufficio con stipendio più alto di quello del proprio dirigente di settore

Select NOMEfrom IMPIEGATI Iwhere STIPENDIO

> ( select STIPENDIOfrom DSTAFF D, SETTORI Swhere I.SETTORE = S.CODICE

andS.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 26Estrarre da Impiegati quelli che hanno degli impiegati omonimi

select *from IMPIEGATI Iwhere exists ( select *

from IMPIEGATI I2where I2.NOME = I.NOME

andI2.COD < > I.COD

)Uso di exists

DUE RIFERIMENTI ALLAQUERY ESTERNA

ESERCIZIO 27 Vogliamo i Capiufficio degli uffici appartenenti al settore ragioneria o a settori che hanno lo stesso dirigente della ragioneria

select CAPUFFfrom UFFICIwhere SET_COD in (select COD

from SETTORI Swhere NOMSET = ‘ RAGIONERIA’)

or in( select COD

from SETTORI S1where S. DIRIGENTE = S1.DIRIGENTE)

ERRORE: tutto ciò che Riguarda S NON E’ VISIBILEda 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 insiemistici ( UNION, INTERSECT, EXCEPT)

DDDefinizione dei dati

DMModifica dei Dati

QLInterrogazioni

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, eliminazioneo aggiornamento ( cambiamenti di valori) diuna 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

• ESEMPIOInsert 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

• ESEMPIOinsert into CNTRLMEDICO ( COD, NOME)

( select COD, NOME from IMPIEGATI

where QUALIFICA = (‘OPERAIO’))

insert into CNTRLMEDICO ( COD, NOME)( select COD, NOME

from IMPIEGATIwhere UFFICIO

in ( select UFF_CODfrom UFFICIwhere NOMEUFF = ( ‘% LABORATORIO% ‘)

))

Inserimento nella tabellaDelle visite mediche di

Tutti gli operai

Inserimento ….. quelli cheLavorano nei laboratori

Nesting di select inInsert into

Nesting di select inInsert into

e di select in select

WARNING

• Naturalmente, il tipo degli attributi delle tuple dainserire ( nel caso di select) o dei valori di una nuovatupla ( nel caso di values) deve coincidere con quellodei corrispondenti attributi nella lista di insert into

• Se nell’inserimento non vengono specificati i valori ditutti gli attributi, a quelli mancanti vengono assegnatii valori di default ed in assenza il valore NULL

• … ma, se viene violato un vincolo di NOT NULLl’inserimento VIENE RIFIUTATO, e lo stesso accade,naturalmente, se viene violato un vincolo direferenza 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 visitamedica svuoto ….

Operazione preziosa per tutte le tabelle permanenti … ma con ciclo di vita breve delle istanze

• ESEMPIdelete from ESAMIDB where TASSE = ‘NO’

delete from IMPIEGATIwhere QUALIFICA = ‘GIARDINIERE’

ESERCIZIO 30In 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 IMPIEGATIgrouped by SETTORE

having count (*) > 10)

ESERCIZIO 31Cancellare gli uffici che non hanno impiegati

Crisi … tagliogiardini e ..giardinieri

Cisi: taglio un dipper 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 xVengono 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 unoperatore di assegnazione divalore

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 DSTAFFset STIPENDIO = 70000where STIPENDIO < 70000

• ESEMPIO: Il nuovo dirigente del settore ‘ DESIGN’ è GaeAulenti; assume lo stesso codice e lo stesso stipendio del predecessore

update DSTAFFset NOME = ‘GAE AULENTI’

where COD = ( select CODICE from SETTORIwhere 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 esempiose è 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 DIORDINE 1 ( Tabella con una sola colonna)

• ESEMPI

alter table IMPIEGATI add column

DATASSUNZIONE date

alter table IMPIEGATI drop column TEL_UFF

agli IMPIEGATI aggiungiamola 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

ESEMPIOalter 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 ecomplessa;

• In generale, le istruzioni di modifica delloschema vanno inquadrate in un set dioperazioni che si estendono ad entità SQL chenon abbiamo trattato;

• Alcuni ulteriori arricchimenti verranno fattinelle esercitazioni, anche con specificoapprendimento di «Complementi di SQL»