lezione_db4

Lezione del 14 Aprile 2003 - Docente : Elisabetta Splendori
Il Linguaggio SQL - Aggiornamento di dati
La dichiarazione UPDATE modifica i valori di una o più colonne nelle righe selezionate di una
singola tabella.
La tabella da aggiornare viene indicata nella dichiarazione ed è necessario avere i permessi necessari
per aggiornare la tabella e ogni singola colonna che verrà modificata.
UPDATE < nome tabella >
SET < nome colonna = espressione | valore > [,<nome colonna = espressione | valore >,… ]
[WHERE condizione di ricerca]
La proposizione WHERE seleziona le righe da modificare. La proposizione SET specifica quali
colonne sono da aggiornare e calcola i nuovi valori per essa.
La dichiarazione UPDATE può aggiornare una sola riga o numerose righe contemporaneamente.
Di seguito una semplice dichiarazione UPDATE che modifica il limite di credito e il venditore per
un cliente:
Aumenta il limite di credito per Acme Manufactoring a $60.000 e riassegna il cliente a Mary
Jones (numero dipendente =109).
UPDATE CUSTOMERS
SET CREDIT_LIMIT =60000.00, CUST_REP =109
WHERE COMPANY =’Acme Mfg’
(1 riga aggiornata)
In questo esempio la proposizione WHERE indica una sola riga della tabella CUSTOMERS.
Trasferisce tutti i venditori dall’ufficio di Chicago (numero 12) all’ufficio di New York (numero 11)
e abbassa i loro obiettivi del 10%.
UPDATE SALESREPS
SET REP_OFFICE =11, QUOTA =.9 * QUOTA
WHERE REP_OFFICE =12
(3 righe aggiornate )
Concettualmente, SQL elabora la dichiarazione UPDATE scorrendo la tabella riga per riga,
aggiornando le righe per cui la condizione di ricerca ha un risultato TRUE e saltando quelle per cui
la condizione di ricerca ha un risultato FALSE o NULL.
Rassegna al dipendente numero 102 tutti i clienti serviti dai dipendenti numero 105, 106 o 107.
UPDATE CUSTOMERS
SET CUST_REP =102
WHERE CUST_REP IN (105, 106,107)
(5 righe aggiornate)
1
Lezione del 14 Aprile 2003 - Docente : Elisabetta Splendori
La proposizione SET nella dichiarazione UPDATE è un elenco di assegnazioni separate da virgole.
Ciascuna assegnazione identifica una colonna di destinazione da aggiornare e specifica come
calcolare il nuovo valore per tale colonna.
Ogni colonna di destinazione deve comparire una sola volta nell’elenco. Non dovrebbero esistere
due assegnazioni per la stessa colonna di destinazione.
Se un’espressione nell’elenco di assegnazione fa riferimento a una delle colonne della tabella di
destinazione, il valore utilizzato per calcolare l’espressione è il valore di tale colonna nella riga
corrente prima che siamo applicate le modifiche.
Lo stesso vale per il riferimento di colonna che si trova nella proposizione WHERE.
UPDATE OFFICES
SET QUOTA = 400000.00, SALES = QUOTA
WHERE QUOTA < 400000.00
Prima dell’aggiornamento, Bill Adams aveva un valore QUOTA di $350.000 e un valore SALES di
$367.911. Dopo l’aggiornamento, la sua riga ha un valore di SALES di $350.000, non di $400.000.
L’ordine della assegnazioni nella proposizione SET è quindi irrilevante; le assegnazioni possono
essere specificate in qualsiasi ordine.
Aggiornare tutte le righe
La proposizione WHERE è opzionale. Se viene omessa, vengono aggiornate tutte le righe della
tabella di destinazione
Aumenta tutti gli obiettivi del 5%.
UPDATE SALESREPS
SET QUOTA =1.05 * QUOTA
(10 righe aggiornate)
La dichiarazione UPDATE senza la proposizione WHERE ha una funzione utile: in sostanza esegue
un aggiornamento globale della tabella.
UPDATE con subquery
Le subquery consentono di selezionare righe da aggiornare basandosi sulle informazioni contenute in
altre tabelle.
Aumenta di $5000 il limite di credito di tutti i clienti che hanno emesso ordini superiori a $25000
UPDATE CUSTOMERS
SET CREDIT_LIMIT = CREDIT_LIMIT + 5000.00
WHERE CUST_NUM IN (SELECT DISTINCT CUST
FROM ORDERS
WHERE AMOUNT > 25000.00)
(4 righe aggiornate)
2
Lezione del 14 Aprile 2003 - Docente : Elisabetta Splendori
SQL2 specifica che un riferimento alla tabella di destinazione in una subquery viene calcolato come
se nessuna parte della tabella di destinazione fosse ancora stata aggiornata.
Il linguaggio di definizione dei dati (DDL)
Le dichiarazioni descritte finora (SELECT, INSERT, DELETE, UPDATE) riguardano la
manipolazione dei dati.
Le modifiche alla struttura di un database sono gestite da un insieme diverso di dichiarazioni SQL,
definito linguaggio SQL di definizione dei dati, o DDL (Data Definition Language).
Utilizzando dichiarazioni DDL è possibile:
 Definire e creare una nuova tabella
 Eliminare una tabella non più necessaria
 Modificare la definizione di una tabella esistente
 Definire una tabella ( o una vista) virtuale di dati
 Definire controlli di sicurezza per un database
 Creare un indice per accelerare l’accesso alle tabelle
 Controllare la memorizzazione fisica dei dati da parte del DBMS.
Il cuore del DDL è basato su tre verbi SQL:
 CREATE. Definisce e crea un oggetto di database
 DROP. Elimina un oggetto di database esistente
 ALTER. Modifica la definizione di un oggetto di database
Nella maggior parte dei prodotti DBMS basati su SQL, i tre verbi DDL possono essere utilizzati
mentre il DBMS è in esecuzione. La struttura del database è quindi dinamica: il DBMS può creare,
eliminare o modificare la definizione delle tabelle del database, per esempio, mentre
contemporaneamente fornisce accesso al database per i suoi utenti. Questo è un grande vantaggio dei
database SQL e relazionali rispetto ai sistemi precedenti, dove il DBMS doveva essere formato
prima che fosse possibile modificare la struttura del database. Un database relazionale, quindi, può
crescere e cambiare nel tempo.
Creazione di una tabella (CREATE TABLE)
Abbiamo visto che la struttura più importante in un database è la tabella.
La dichiarazione CREATE TABLE definisce una nuova tabella nel database e la prepara ad
accettare i dati. Le diverse proposizioni della dichiarazione specificano gli elementi della definizione
di tabella.
Quando si esegue una dichiarazione CREATE TABLE, si diventa proprietari della tabella appena
creata, a cui viene dato il nome indicato nella dichiarazione. Il nome della tabella deve essere un
3
Lezione del 14 Aprile 2003 - Docente : Elisabetta Splendori
nome SQL legale e non deve entrare in conflitto con i nomi di tabelle esistenti. La tabella appena
creata è vuota, ma il DBMS la prepara ad accettare dati inseriti con la dichiarazione INSERT.
CREATE TABLE < nome tabella > (definizione di colonna [,definizione di colonna,…])
definizione colonna: nome colonna tipo di dati [DEFAULT valore ] [NOT NULL]
CONSTRAINT <nome-contraint>- limitazioni chiave primaria
- limitazioni chiave esterna
- limitazioni di unicità
- limitazioni di controllo
PRIMARY KEY (nome colonna [, nome colonna, …])
FOREIGN KEY (nome colonna [, nome colonna, …])
REFERENCES <nome tabella > (nome colonna [, nome colonna, …])
Le definizioni di colonna si trovano in un elenco con elementi separati da virgole e racchiusi da
parentesi. L’ordine di definizione di colonna determina l’ordina da sinistra verso destra delle colonne
della tabella. Le colonne di una tabella appena creata sono definite nel corpo della dichiarazione
CREATE TABLE. Nelle dichiarazioni CREATE TABLE supportate dai principali DBMS, ciascuna
definizione di colonna specifica quanto segue:
- nome di colonna. Ciascuna colonna deve avere un nome unico anche se lo stesso nome può
esistere in un’altra colonna
- tipo di dati . Identifica il tipo di dati memorizzati nella colonna
- dati necessari. Determina se la colonna contiene dati necessari e impedisce la comparsa di
valori NULL nella colonna; altrimenti sono ammessi valori NULL.
- Valore predefinito. SQL utilizza un valore predefinito opzionale per la colonna quando una
dichiarazione INSERT per la tabella non specifica un valore per la colonna.
Vediamo un esempio di dichiarazione CREATE TABLE:
Definisce la tabella OFFICES e le sue colonne
CREATE TABLE OFFICES ( OFFICE INTEGER NOT NULL,
CITY VARCHAR(15) NOT NULL,
REGION VARCHAR(10) NOT NULL,
MGR INTEGER,
TARGET MONEY,
SALES MONEY NOT NULL)
Alcuni tipi di dati quali VARCHAR
e DECIMAL richiedono ulteriori
informazioni, quali la lunghezza o il
numero di cifre decimali dei dati.
NOT NULL: la colonna deve contenere valori legali per ciascuna riga della tabella.
Se una colonna ha un valore predefinito, viene specificato nella definizione di colonna:
Per esempio, di seguito si trova una dichiarazione CREATE TABLE per la tabella OFFICES che
specifica valori predefiniti:
4
Lezione del 14 Aprile 2003 - Docente : Elisabetta Splendori
Definisce la tabella OFFICES con valori predefiniti (sintassi ANSI/ISO)
CREATE TABLE OFFICES ( OFFICE INTEGER NOT NULL,
CITY VARCHAR(15) NOT NULL,
REGION VARCHAR(10) NOT NULL DEFAULT ‘Eastern’,
MGR INTEGER DEFAULT 106,
TARGET MONEY DEFAULT NULL,
SALES MONEY NOT NULL DEFAULT 0.00)
Oltre a definire le colonne di una tabella, la dichiarazione CREATE TABLE identifica la chiave
primaria della tabella e le relazioni della tabella con le altre tabelle presenti nel database.
La proposizione PRIMARY KEY specifica la colonna o le colonne che costituiscono la chiave
primaria della tabella. Tale colonna (o colonne) serve da identificatore unico per ciascuna riga della
tabella. Il DBMS richiede automaticamente che il valore di chiave primaria in ciascuna riga della
tabella sia unico. Inoltre la definizione di colonna per ciascuna colonna nella chiave primaria deve
specificare che la colonna è NOT NULL.
La proposizione FOREIGN KEY specifica una chiave esterna nella tabella e la relazione che crea
con un’altra tabella (genitore) nel database. La proposizione specifica (e non solo):
-
la colonna o le colonne che formano la chiave esterna (tutte sono colonne in fase di
creazione)
la tabella a cui fa riferimento la chiave esterna. Questa è la tabella genitore nella relazione: la
tabella in fase di definizione è sua figlia.
un nome opzionale per la relazione che non viene utilizzato in alcuna dichiarazione SQL per
la manipolazione dei dati, ma può comparire in messaggi di errore ed è necessario se si
desidera essere in grado di eliminare la chiave esterna in un secondo momento.
Di seguito si trova una dichiarazione CREATE TABLE estesa per la tabella OFFICES che
comprende una definizione della chiave primaria e di una chiave esterna contenute in essa:
Definisce la tabella OFFICES con chiavi primarie ed esterne.
CREATE TABLE OFFICES ( OFFICE INTEGER NOT NULL,
CITY VARCHAR(15) NOT NULL,
REGION VARCHAR(10) NOT NULL DEFAULT ‘Eastern’,
MGR INTEGER DEFAULT 106,
TARGET MONEY DEFAULT NULL,
SALES MONEY NOT NULL DEFAULT 0.00,
PRIMARY KEY (OFFICE),
FOREIGN KEY (MGR) REFERENCES SALESREPS )
Lo standard SQL2 specifica che anche le limitazioni di unicità sono definite nella dichiarazione
CREATE TABLE utilizzando la parola chiave UNIQUE. Modifichiamola dichiarazione CREATE
TABLE per la tabella OFFICES, modificata in modo da richiedere valori CITY unici.
Definisce la tabella OFFICES con una limitazione di unicità.
CREATE TABLE OFFICES ( OFFICE INTEGER NOT NULL,
CITY VARCHAR(15) NOT NULL,
5
Lezione del 14 Aprile 2003 - Docente : Elisabetta Splendori
REGION VARCHAR(10) NOT NULL DEFAULT ‘Eastern’,
MGR INTEGER DEFAULT 106,
TARGET MONEY DEFAULT NULL,
SALES MONEY NOT NULL DEFAULT 0.00,
PRIMARY KEY (OFFICE),
FOREIGN KEY (MGR) REFERENCES SALESREPS,
UNIQUE (CITY) )
Eliminazione di una tabella (DROP TABLE)
E’ possibile eliminare una tabella dal database con la dichiarazione DROP TABLE.
DROP TABLE < nome tabella > [CASCADE | RESTRICT]
Identifica la tabella da eliminare
specificano l’impatto dell’eliminazione di una
tabella su altri oggetti del database che dipendono
dalla tabella
Supponiamo che la tabella CUSTOMERS non sia più necessaria; per eliminarla dal database:
DROP TABLE CUSTOMERS
Con i permessi necessari è possibile eliminare tabelle possedute da altri utenti specificando un nome
di tabella qualificato.
Sam concede il permesso di eliminare la sua tabella chiamata BIRTHDAYS.
DROP TABLE SAM.BIRTHDAYS
Quando una tabella viene eliminata da database, la sua definizione e tutto il suo contenuto vanno
persi; non esiste modo di recuperare i dati e si deve utilizzare una nuova dichiarazione CREATE
TABLE. A causa di tali gravi conseguenze, la dichiarazione DROP TABLE deve essere utilizzata
con cautela.
La dichiarazione DROP TABLE può contenere CASCADE o RESTRICT.
Se si specifica CASCADE, la dichiarazione DROP TABLE fallisce se altri oggetti del database
fanno riferimento alla tabella. Con l’espressione RESTRICT la tabella verrebbe cancellata se non
referenziata in alcun vincolo (ad esempio da una chiave esterna in un’altra relazione).
6
Lezione del 14 Aprile 2003 - Docente : Elisabetta Splendori
Modifica di una tabella (ALTER TABLE)
Se si vuole modificare la definizione di una tabella si può usare la dichiarazione ALTER TABLE.
Essa può:
- aggiungere una definizione di colonna ad una tabella
ALTER TABLE <nome tabella >
ADD <definizione di colonna > <tipo > [,<definizione di colonna > <tipo > ,… ]
- eliminare una colonna da una tabella
ALTER TABLE <nome tabella >
DROP <nome colonna > CASCADE | RESTRICT
- modificare il valore predefinito per una colonna
ALTER TABLE <nome tabella >
ALTER <nome colonna > SET DEFAULT <valore >
- aggiungere o eliminare una chiave primaria
ALTER TABLE <nome tabella >
ADD <definizione chiave primaria >
- aggiungere o eliminare una chiave esterna
ALTER TABLE <nome tabella >
ADD <definizione chiave esterna >
- aggiungere o eliminare una limitazione di unicità per una tabella
ALTER TABLE <nome tabella >
ADD <limitazione di unicità >
- aggiungere o eliminare una limitazione di controllo per una tabella
ALTER TABLE <nome tabella >
ADD <limitazione di controllo >
Alcune dichiarazioni ALTER TABLE di esempio che aggiungono nuove colonne sono mostrate di
seguito:
Aggiunge nome di contatto e numero di telefono alla tabella CUSTOMERS.
ALTER TABLE CUSTOMERS
ADD CONTACT_NAME VARCHAR(30)
ALTER TABLE CUSTOMERS
ADD CONTACT_PHONE VARCHAR(10)
La nuova colonna viene aggiunta alla fine delle definizioni di colonna per la tabella e compare come
colonna più a destra nelle query successive.Normalmente il DBMS presuppone un NULL per una
nuova colonna aggiunta in tutte le righe esistenti della tabella. Se la colonna è dichiarata come NOT
NULL con un valore predefinito, il DBMS presuppone invece il valore predefinito.
La dichiarazione ALTER TABLE può essere utilizzata per eliminare una o più colonne da una
tabella esistente.
Elimina la colonna data di assunzione dalla tabella SALESREPS
ALTER TABLE SALESREPS
DROP HIRE_DATE
Lo standard SQL2 costringe a eseguire una dichiarazione ALTER TABLE separata se si desidera
eliminare più di una colonna.
7
Lezione del 14 Aprile 2003 - Docente : Elisabetta Splendori
Si noti che l’eliminazione di una colonna può comportare problemi di integrità; per esempio, se si
elimina una colonna che rappresenta una chiave primaria in qualche relazione, la chiave esterna che
fa riferimento alla colonna eliminata diventa invalida. Lo standard SQL2 gestisce questo con una
regola di eliminazione.
E’ possibile specificare una delle due regole di eliminazione:
- RESTRICT. Se gli altri oggetti nel database (chiavi esterne, limitazioni) dipendono dalla
colonna da eliminare, la dichiarazione ALTER TABLE non va a buon fine, viene generato un
errore e la colonna non viene eliminata.
- CASCADE. Anche tutti gli altri oggetti nel database (chiavi primarie, limitazioni) che
dipendono dalla colonna vengono eliminati, come effetto a cascata della dichiarazione
ALTER TABLE.
L’effetto CASCADE può causare modifiche radicali al database; è necessario utilizzarlo con cautela.
In genere è meglio utilizzare la modalità RESTRICT (eliminare esplicitamente le chiavi esterne e le
limitazioni esistenti, utilizzando la dichiarazione ALTER o DROP) prima di eliminare la colonna.
Creazione di viste (CREATE VIEW)
Una vista è una tabella virtuale nel database, i cui contenuti sono definiti da una query.
Per l’utente del database, la vista appare come una vera tabella, con un set di colonne e di righe di
dati. Ma a differenza di una vera tabella, una vista non esiste nel database come set di valori
memorizzati: le righe e le colonne di dati visibili attraverso la vista sono invece i risultati di query
generati dalla query che definisce la vista.
CREATE VIEW <nome vista > AS <query >
Vantaggi delle viste:
- Sicurezza. A ciascun utente possono essere dati i permessi di accesso al database solo
attraverso un piccolo set di viste che contengono i dati specifici che l’utente è autorizzato a
vedere, limitando quindi l’accesso dell’utente ai dati memorizzati.
- Semplicità di query. Una vista può estrarre i dati da numerose tabelle diverse e presentarli
come una tabella singola.
- Semplicità strutturale. Le viste possono dare a un utente una vista “personalizzata” , più
semplice della struttura del database
- Isolamento dalle modifiche. Una vista può presentare un’immagine coerente e immutabile
della struttura del database, anche se le tabelle sorgente sottostanti vengono suddivise,
ristrutturate o rinominate.
- Integrità dei dati. Se si accede ai dati e si inseriscono dati attraverso una vista, il DBMS può
controllare automaticamente i dati per garantire che rispettino le limitazioni di integrità
specificate.
8
Lezione del 14 Aprile 2003 - Docente : Elisabetta Splendori
L’uso di una vista invece che di una vera tabella presenta anche alcuni svantaggi:
- Prestazioni. Anche se le viste hanno l’aspetto di una tabella, il DBMS deve tradurre le query
su una vista in query sulle tabelle sorgente sottostanti e questo richiede molto più tempo per
il completamento di query complesse.
- Limitazioni di aggiornamento. Se un utente cerca di aggiornare righe di una vista, il
DBMS deve tradurre la richiesta in un aggiornamento delle righe della tabelle sorgente
sottostanti. Questo è possibile per viste semplici, ma viste più complesse non possono essere
aggiornate e saranno di sola lettura.
Questo ci fa comprendere come non sia possibile definire viste in modo indiscriminato e utilizzarle
al posto delle tabelle sorgente; è necessario considerare in ogni caso i vantaggi e gli svantaggi forniti
dall’uso di una vista e confrontarli con gli svantaggi.
Viste orizzontali
Un uso comune delle viste è la limitazione di accesso di un utente solo a certe righe di una tabella.
Una tale vista viene chiamata vista orizzontale. Tutte le colonne della tabella sorgente partecipano
alla vista, ma solo alcune delle sue righe sono visibili nella vista.
Di seguito è mostrato un esempio di vista orizzontale:
Definisce una vista contenente solo uffici della regione orientale.
CREATE VIEW EASTOFFICES AS
SELECT *
FROM OFFICES
WHERE REGION = ‘Eastern’
La vista è definita da una query SELECT * e quindi ha esattamente le stesse colonne della tabella
sorgente. La proposizione WHERE determina quali righe della tabella sorgente sono visibili nella
vista.
Viste verticali
Un altro uso comune delle viste è la limitazione di accesso di un utente solo a certe colonne di una
tabella. Una tale vista viene chiamata vista verticale.
Di seguito è mostrato un esempio di vista verticale:
Definisce una vista della tabella CUSTOMERS che contiene solo i nomi dei clienti e le
assegnazioni dei venditori.
CREATE VIEW CUST_INFO AS
SELECT COMPANY, CUST_REP
FROM CUSTOMERS
Una vista verticale suddivide la tabella sorgente in modo verticale per creare la vista. Poiché sono
viste verticali, nella vista sono rappresentate tutte le righe della tabella sorgente.
9
Lezione del 14 Aprile 2003 - Docente : Elisabetta Splendori
Le viste verticali si trovano solitamente dove i dati memorizzati in una tabella vengono utilizzati da
diversi utenti o gruppi di utenti. Forniscono una tabella privata per ciascun utente, composta solo
dalle colonne necessarie all’utente.
Query di sommario
SQL supporta richieste di una piccola quantità di valori che riassumono il database (dati riassunti)
attraverso funzioni di colonna (SUM, MIN, MAX, etc.) e le proposizioni GROUP BY e HAVING
della dichiarazione SELECT.
GROUP BY
Una query che contiene la proposizione GROUP BY viene chiamata query raggruppata perché
raggruppa i dati delle tabelle sorgente e genera una singola riga di sommario per ciascun gruppo di
righe. Le colonne indicate nella proposizione GROUP BY sono chiamate colonne di
raggruppamento della query, perché determinano come sono divise in gruppi le righe.
Si consideri la seguente query:
Quale è l’intervallo di obiettivi assegnati a ciascun ufficio?
SELECT REP_OFFICE, MIN (QUOTA), MAX (QUOTA)
FROM SALESREPS
GROUP BY REP_OFFICE
REP_OFFICE
MIN(QUOTA)
MAX (QUOTA)
--------------------------------------------------------------------NULL
NULL
NULL
11
$275,000.00
$300,000.00
12
$200,000.00
$300,000.00
13
$350,000.00
$350,000.00
21
$350,000.00
$350,000.00
22
$300,000.00
$300,000.00
Essa genera numerose righe di sommario, una riga per ciascun gruppo, che riassumono gli ordini
presi da un singolo venditore.
Concettualmente, SQL elabora la query come segue:
1. SQL divide gli ordini in gruppi di ordini, un gruppo per ciascun venditore. All’interno di ciascun
gruppo, tutti gli ordini hanno lo stesso valore nella colonna REP.
2. Per ciascun gruppo, SQL calcola il valore medio della colonna AMOUNT per tutte le righe nel
gruppo e genera una singola riga di sommario di risultati di query. La riga contiene il valore della
colonna REP per il gruppo e l’ammontare medio di ordine calcolato.
10
Lezione del 14 Aprile 2003 - Docente : Elisabetta Splendori
Raggruppamento di condizione di ricerca (proposizione HAVING)
La proposizione HAVING può essere utilizzata per selezionare e rifiutare gruppi di righe. Il formato
della proposizione HAVING è parallelo a quello della proposizione WHERE:
HAVING <condizione di ricerca >
Tale proposizione specifica una condizione di ricerca per gruppi; le condizioni che si possono
specificare sono quelle utilizzate nella proposizione WHERE.
Vediamo un ulteriore esempio di query raggruppata:
Quale è l’ammontare medio di ordine per ciascun venditore per cui il totale di ordini supera
$30.000?
SELECT REP, AVG (AMOUNT)
FROM ORDERS
GROUP BY REP
HAVING SUM(AMOUNT) > 30000.00
REP
AVG (AMOUNT)
----------------------------------105
$7,865.40
106
$16,479.00
107
$11,477.33
108
$8,376.14
Vediamo come SQL elabora la query.
La proposizione GROUP BY in primo luogo sistema gli ordini in gruppi per venditore.
La proposizione HAVING quindi elimina i gruppi in cui il totale degli ordini non supera $30.000.
Infine la proposizione SELECT calcola l’ammontare medio degli ordini per ciascun gruppo restante
e genera i risultati di query.
Tabella ORDERS
… … … …
TRUE
GROUP BY
Tabella raggruppata
ORDER_NUM … REP …
… 106 …
106
… 103 …
103
…
…
REP AVG (AMOUNT)
106 $16,479.00
…
…
…
…
SUM(AMOUNT)
TRUE
> $30000?
AMOUNT
$31500
$1458
$2100
$600
Tabella dei risultati di query
FALSE
SUM(AMOUNT)
> $30000?
11
Lezione del 14 Aprile 2003 - Docente : Elisabetta Splendori
QUERY MULTITABELLA
Molte query richiedono i dati da due o più tabelle. In questo caso si parla di query multitabella che
uniscono i dati presi da due o più tabelle.
Consideriamo una semplice richiesta che unisce i dati provenienti da due tabelle diverse:
“Elencare tutti gli ordini, mostrando il numero di ordine e l’importo totale di ciascuno, il nome e il
credito del cliente che lo ha effettuato.”
Le quattro voci richieste sono memorizzate in tabelle diverse: le prime due (numero di ordine e
importo totale) nella tabella ORDERS, le altre due (nome e credito del cliente), invece, sono
memorizzate nella tabella CUSTOMERS.
Esiste un collegamento tra queste due tabelle: in ciascuna riga della tabella ORDERS, la colonna
CUST contiene il numero di cliente che ha effettuato l’ordine, che corrisponde al valore della
colonna CUST_NUM in una delle righe della tabella CUSTOMERS.
La dichiarazione SELECT deve utilizzare questo collegamento per generare i risultati di query.
Nel nostro esempio, per generare tali risultati:
- ciascuna riga dei risultati di query prende i suoi dati da una specifica coppia di righe, una
dalla tabella ORDERS e una dalla tabella CUSTOMERS.
- La coppia di righe si trova confrontando i contenuti delle colonne corrispondenti delle tabelle
(CUST e CUST_NUM)
Elenca tutti gli ordini, mostrando il numero di ordine e l’importo totale di ciascuno, il nome del
cliente e il suo.
SELECT ORDER_NUM, AMOUNT, COMPANY, CREDIT_LIMIT
FROM ORDERS, CUSTOMERS
WHERE CUST = CUST_NUM
ORDER_NUM
AMOUNT
COMPANY
CREDIT_LIMIT
----------------------------------------------------------------------------------------112989
$1,458.00
Jones Mfg.
$65,000.00
112968
$3,978.00
First Corp.
$65,000.00
112963
$3,276.00
Acme Mfg.
$50,000.00
.
.
.
La condizione di ricerca che specifica le colonne corrispondenti in una query multitabella in una
query multitabella può essere unita con altre condizioni di ricerca per restringere ulteriormente i
contenuti dei risultati di query.
Query genitore/figlio
Le query multitabella più comuni riguardano due tabelle che hanno una relazione naturale
genitore/figlio. Le query degli ordini e dei clienti ne é un esempio. Ciascun ordine (figlio) ha un
cliente associato (genitore) e ciascun cliente (genitore) può avere diversi ordini associati (figli). Le
coppie di righe che generano i risultati della query sono combinazioni di righe genitore/figlio.
12
Lezione del 14 Aprile 2003 - Docente : Elisabetta Splendori
Si é già detto che le chiavi esterne e primarie creano una relazione genitore/figlio in un database
SQL. Nella relazione, la tabella contenente la chiave esterna é il figlio, mentre la tabella con la
chiave primaria é il genitore. Per esercitare la relazione genitore/figlio in una query, occorre
specificare una condizione di ricerca che confronta la chiave esterna e la chiave primaria.
Di seguito è mostrato un esempio di query che esercita la relazione genitore/figlio:
Elenca gli uffici, nome e mansione con obiettivo > $600000.
SELECT CITY, NAME, TITLE
FROM OFFICES, SALESREPS
WHERE MGR = EMPL_NUM
AND TARGET> 600000.00
Il primo test (MGR=EMPL_NUM) seleziona solo le coppie
di righe di OFFICES e SALESREPS che hanno una
relazione genitore/figlio adatta.
Il secondo test seleziona ulteriormente solo quelle coppie di
righe dove l’ufficio ha superato l’obiettivo di vendite.
La tabella OFFICES (figlio) contiene MGR, una chiave esterna per la tabella SALESREPS
(genitore). Usiamo questa relazione per trovare la riga corretta di SALESREPS per ciascun
venditore.
CITY
NAME
TITLE
--------------------------------------------------Chicago
Bob Smith
Sales Mgr
Los Angeles Larry Fitch
Sales Mgr
13