Esercitazioni sull’uso di SQLintermed.unipv.it/.../documents/pdf/dispense_sql.pdf · 2003. 3....

Department of Informatics and Systems Science 1998 © Giordano Lanzola

� � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � �� 1

Automazione ed Organizzazione Sanitaria

Esercitazioni sull’uso di SQL

Giordano Lanzola

E-mail: [email protected]

Dipartimento di Informatica e Sistemistica

Universita’ degli Studi di Pavia


� � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � �� 2

SQL ( Structured Query Language )

• E’ un linguaggio per ladefinizione, manipolazione ed ilcontrollo dei dati inun database relazionale;

• Adottato ufficialmente come standard da:ANSI (American National Standard Institute) (1986)ISO (International Standardization Organization) (1987)X/OPEN (Unix)FIPS (US Government)

• Utilizzato da tutti i principali pacchetti per la gestione di DataBase disponibiliin commercio nei vari ambienti Unix / VMS / DOS / WINDOWS (e.g.Oracle, Ingr es, Sybase, Informix , Access, DB-5, Paradox).

• Attualmente si stima cheesistano piu’ di 100 prodotti commerciali basatisu qualche dialetto riconducibile a SQL.

Cenni Storici

• L’introduzione delmodello relazionale sviluppato presso i laboratori diricerca IBM [Codd, 1970] ha notevolmente migliorato la tecnologia deiDataBase, ed ha quindi destato un profondo interesse da parte della comunita’scientifica;

• Sin dai primi anni ‘70 parte della ricerca condotta presso i laboratori IBM nelsettore dei DataBase si e’ indirizzata allo sviluppo di possibili linguaggi checonsentissero di utilizzare al meglio il modello relazionale;

• Uno dei primi linguaggi ad imporsi e’ stato SEQUEL (Structured EnglishQuery Language) [Chamberlin, 1974], seguito a sua volta da SEQUEL/2(1976), SYSTEM-R (1977) ed infineSQL;

• Il profondo interesse che le software house mostrano verso SQL e’testimoniato dal fatto che, sebbene esso sia stato sviluppato presso i laboratoriIBM, la prima casa ad adottarlo in un proprio prodotto e’ stata Oracle (1979).


� � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � �� 3

SQL ( non ) e’ uno Standard ?

• SQL e’ stato accettato come unostandard de facto molto prima che venisseufficialmente certificato come tale dalle istituzioni preposte (ANSI ed ISO);

• Uno standard definito in questo modo ha un maggiore livello di accettazione,in quanto non vieneimposto sull’utenza, ma al contrario risulta esserel’utenza stessa a decretarne l’elezione;

• D’altra parte, cio’ implica l’esistenza di vari dialetti precedenti alladefinizione dello standard ed inevitabilmente diversi fra loro. Cio’ fa si che levarie utenze abbiano ormai consolidato l’uso di alcune caratteristichepeculiari (diverse fra loro) non incluse nello standard;

• Infine SQL e’ un linguaggio recente che si basa su una metodologiaaltrettanto recentemente sviluppata. E’ dunque naturale che esso sia ancora inevoluzione, cosi’ come testimonia la rapida successione con cui vengonopubblicati i documenti che ne codificano gli standard:

SQL/86 (SQL)

SQL/89 (SQL)

SQL/92 (SQL 2)

SQL/98 ? (SQL 3)

• Le esercitazioni del corso di Automazione e Organizzazione Sanitaria sibasano sulla versione SQL/89 dello standard, sia per una ragione disemplicita’, che soprattutto perche’ questa e’ la versione adottata dal prodottodisponibile nelle aule didattiche (Ingres 6.4).


� � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � �� 4

SQL ( non ) e’ uno Standard(2)

• Le varie case produttrici non possono eliminare caratteristiche gia’ esistentinei loro prodotti, ed il cui uso si e’ consolidato nel corso degli anni, al solofine di adeguarsi allo standard. Esse continuano percio’ a fornirleproponendole come estensioni allo standard;

• La stessa definizione di uno standard e’ resa piu’ complessa soprattuttoquando le caratteristiche dei vari dialetti risultano incompatibili fra di loro.Lo sforzo dei comitati di standardizzazione si concretizza pertanto neltentativo di di recuperare quanto piu’ possibile dell’esistente, eliminandoeventuali incompatibilita’ e apportando modifiche e/o aggiunte in modo darendere la sintassi e la semantica piu’ consistente nel suo insieme;

• Infine, nonostante si siano gia’ realizzati innumerevoli prodotticommerciali,la ricerca in questo settore e’ quanto mai attiva e quindi non e’ propriamentecongelabile in uno standard. Le case produttrici di software sono infattisempre alla ricerca di nuove soluzioni che introducono nei loro prodotti nonappena queste raggiungono un sufficiente livello di maturita’.

Standard

Ext-1

Ext-2

Ext

-3


� � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � �� 5

I vantaggi di un linguaggio (non) standard

• Analisti, Sistemisti e Programmatori possono muoversi facilmente da unambiente all’altro senza particolari problemi e/o onerosi costi per corsi diaddestramento;

• E’ inoltre facilitato il trasporto di applicativi tra le varie piattaforme. (ad es.se una Sw-House ha sviluppato un DBMS in ambiente VMS utilizzandoOracle, la stessa applicazione puo’ essere trasportata con uno sforzominimo in ambiente Unix e con il prodotto Ingres);

• Maggiore durata temporale delle applicazioni e delle competenze acquisitedalle persone che vi lavorano;

• Facilita’ di comunicazione fra sistemi diversi e possibilita’ di utilizzareDataBase distribuiti realizzati con architetturaclient-server.

S1 S2 S3

C 1 C 2 C 3


� � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � �� 6

Proprieta’ del Linguaggio SQL (1)

• SQL mette a disposizione delle istruzioni per ladefinizione, manipolazione econtrollo dei dati in un DataBase relazionale.

• Queste istruzioni possono:

• essere utilizzate in modo interattivo (da terminale). In questo caso ilrisultato viene visualizzato immediatamente sullo schermo;

• essere chiamate dall’interno di unhost language, cioe’ un linguaggio diprogrammazione tradizionale (e.g. C, Pascal, Fortran, COBOL, PL1,etc...), con cui viene realizzata l’applicazione (embedded SQL). In questocaso e’ necessario prevedere lo scambio di valori tra variabili dell’hostlanguage e SQL;

• essere utilizzate all’interno di una serie di tools graficiintegrati proprietaribasati su forms per la definizione e gestione di un database;

• essere utilizzati all’interno di una applicazione Internet - WWW (e.g.Java, Server-Side Javascript).

• L’intero DataBase viene visto da SQL esclusivamente come un insieme ditabelle (relazioni), ciascuna delle quali e’ composta da una serie dicolonne(attributi) e di righe (record); Le procedure SQL consentono di estrarne deisottoinsiemi e di combinarle fra loro in modo da modificare le tabelleesistenti o crearne delle altre.


� � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � �� 7

Proprieta’ del Linguaggio SQL (2)

• La principale caratteristica che differenzia SQL dai principali linguaggi diprogrammazione, e’ rappresentata dal fatto cheSQL non e’ un linguaggioprocedurale;

• SQL e’ un linguaggio dichiarativo. L’utente specifica checosa deve esserefatto e noncome cio’ debba essere fatto;

• A differenza dei linguaggi per la gestione di data-base gerarchici e reticolari,le istruzioni SQL permettono dioperare su un intero insieme di dati pervolta e non su di un singolo record.

Esempio:

Si vuole aggiornare il totale imponibile per tutti i pazienti che hanno subito undeterminato trattamento mediante l’aggiunta di una determinata cifra:

UPDATE PAZIENTI

SET TOTALE = TOTALE + costo_trattamento

WHERE TRATTAMENTO = trattamento_1

Mediante questa istruzione vengono aggiornati tutti i record che soddisfano larichiesta indicata, indipendentemente dal loro numero (nessuno, uno, o piu’ diuno).

In definitiva:

Non e’ necessario ne’ conoscere la struttura fisica del database ne’ scriverealgoritmi che permettano di navigare attraverso le tabelle mediante dei puntatoriper individuare tutti i record che soddisfano una determinata condizione al finedi aggiornarli.


� � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � �� 8

Creazione di un Data Base

• Creare un database significa allocare un determinato spazio su disco per unaapplicazione ed inizializzare opportunetabelle di sistema;

• Non esistono istruzioni SQL per la creazione di un database dal momento chequesto compito e’ strettamente connesso e dipendente dal particolare sistemache ospita SQL. Di conseguenza esso viene svolto invocando opportuniprogrammi di utility direttamente dalla shell del sistema operativo;

• La creazione di un database e’ una operazione che puo’ essere fatta solo da unristretto numero di utenti cui e’ concesso questo privilegio;

• Quando l’utente XXX ha creato il database YYY, egli diventa il databaseadmisitrator (DBA) di quel database, e gli sono concessi particolari privilegi(decidere gli utenti che possono collegarsi a quel database, distruggerlo,etc...)

• Le queries e le modifiche ai dati memorizzati in un database possonoavvenire solo se “autorizzate” attraverso un complesso sistema di gestionedei privilegi.


� � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � �� 9

La gestione degli accessi in un DBMS (1)

• Per potere accedere e manipolare i dati contenuti in un database e’ necessariopossedere determinati privilegi. Questi sono molteplici e di tipi diversi, epossono essere classificati in base al criterio con cui vengono assegnati:

Assegnati unicamente in base all’utente:

1) Accesso alle risorse del S.O.

2) Accesso al DBMS.

Funzione della coppia (utente, database);

3) Connessione ad un particolare DataBase

Workstation Client Server

(User Application) (Oracle/RDBMS)

Rete Digitale

Tipo di Autenticazione: Tipo di Autenticazione:

Username/Password Username/Password

SistemaOperativo DBMS

321


� � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � �� 10

La gestione degli accessi in un DBMS (2)

Funzione della tupla (utente, tipo di operazione, dato):

4) Privilegio per l’operazione da effettuare;

• Per le esercitazioni verra’ utilizzato un database in cui un utente (lauree) hagia’ creato alcune tabelle;

• Sara’ possibile accedere a tali tabelle per leggere i record contenuti, ma nonper alterarli. Infatti l’utentelauree ha assegnato il privilegio di lettura (select)su tutte le colonne delle tabelle utili, ma non quelli di scrittura (insert/update/delete);

• Occorrera’ quindi che ciascuno, all’interno dello stesso database, si definiscapersonalmente delle altre tabelle sulle quali avra’ tutti i privilegi essendone ilcreatore;


� � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � �� 11

Le Tabelle(1)

• In SQL la tabella costituisce il paradigma fondamentale per interagire con ilsistema;

• Una tabella consiste di una riga di intestazioni di colonna (attributi ) e di zeroo piu’ righe di valori (record). In una stessa tabella non possono esistere piu’colonne con lo stesso nome;

• Ad ogni intestazione di colonna e’ associato un particolare tipo di dato, eciascuna linea puo’ contenere al massimo un valore per ogni colonna(scalare);

• Non esiste la possibilita’ di inserire i record in una tabella secondo unparticolare ordine. Un ordinamento puo’ essere specificato invece in fase diretrieval, ed in mancanza i record vengono restituiti in ordine casuale;

• Le colonne si considerano invece ordinate secondo quanto indicato all’attodella creazione della tabella stessa;

• Il creatore di una tabella possiede su di essa tutti i privilegiche hanno sensosu quella tabella, ed ha la facolta’ di trasmetterli nel modo che egli ritieneopportuno ad eventuali altri utenti del sistema.

Esempio:


� � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � �� 12

Le Tabelle(2)

• In SQL esistono 3 tipi di tabelle:

Tabelle Base: Sono quelle realmente memorizzate nel database. Quando sonomenzionate in una query, SQL accede direttamente ai dati memorizzati neifiles del database;

Viste: Sono tabelle “virtuali”, e quindi non sono memorizzate nel database,ma definite attraverso queries su altre tabelle. Tali queries sono dinamica-mente (ed implicitamente) eseguite per ricalcolare la vista ogni volta chequesta viene menzionata in una query;

Tabelle senza nome (temporanee o transienti): Sono le tabelle che vengonorestituite in seguito alla valutazione di una qualsiasi query. (In SQL ilrisultato di una qualunque query restituisce sempre una tabella).


� � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � �� 13

La sintassi BNF:

::= Indica una regola di produzione (espansione);

{} Indica un blocco che appare una sola volta;

{} * Blocco ripetibile zero o piu’ volte;

{} + Blocco ripetibile una o piu’ volte;

[] Blocco opzionale (ripetibile zero o una volta);

| Separa diverse alternative fra le scelte disponibili all’interno di un blocco;

<> Descrizione verbale per una categoria sintattica terminale;

• In corsivo sono indicate le categorie sintattiche non terminali (che quindinecessitano di ulteriori espansioni);

• Per ragioni di semplicita’ viene trascurata la formale espansione di alcunecategorie sintattiche terminali, per le quali viene invece fornita unadescrizione verbale di piu’ immediata comprensione.

Esempio:

sign ::= + | -

digit ::= 0 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9

integer ::= { digit } +

exact-numeric-literal ::=

[ sign ] { integer[ . [ integer ] ] | . integer}

approximate-numeric-literal::=

exact-numeric-literal{ E | e } [ sign] integer

numeric-literal::=

exact-numeric-literal | approximate-numeric-literal


� � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � �� 14

I T ipi di Dati in SQL

• SQL presenta alcune sostanziali differenze rispetto ad un qualunque altrolinguaggio di programmazione (e.g. C, Fortran, Pascal, Lisp, etc.)relativamente alla definizione dei diversi tipi di dati;

• Nel caso di un linguaggio tradizionale esiste un compilatore (o un interprete)che alloca la memoria per ciascuna variabile, e definisce le modalita’ diaccesso e di utilizzo delle stesse;

• SQL invece e’ inteso come un linguaggio dichiarativo di tipo cross-platform,che necessita di interfacciarsi con diversi altri linguaggi (host languages) incui vengono codificate le procedure che utilizzano i dati disponibili neldatabase;

• Il tentativo consiste dunque nell’identificare alcuni tipi di dati chegarantiscano unarelativa indipendenza da particolari architetture hardware e/o software, e neldefinire le procedure di conversione con i tipi propri diqualunque linguaggio;

• Inoltre, c’e’ la necessita’ di rappresentare (e rendere portabili) alcuni tipi didati di frequente utilizzo nell’ambito dei database che non trovano una direttacorrispondenza nei piu’ comuni linguaggi (e.g. data, valuta);

• Infine ciascun tipo di dato determina la memoria che verra’ allocata percontenere i valori dei diversi attributi di ciascun record. Poiche’ lo scopo diun database e’ quello di memorizzare ingenti quantita’ di dati, e’ necessarioche i tipi di dati previsti consentano una ottimizzazione nella allocazionedella memoria stessa.


� � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � �� 15

I T ipi di dati in SQL

• I tipi di dato qui di seguito indicati costituiscono un “utile sottoinsieme” diquello definito da SQL come standard.

datatype ::=

CHAR (integer) | Stringa esattamente diinteger caratteri

VARCHAR (integer) | Stringa lunga fino ainteger caratteri

NUMERIC ( p , s ) | Numerico di precisionep e scalas

INTEGER | Intero

FLOAT | Floating point

DATE Data e ora

Tali tipi nativi vengono mappatiin modo trasparente su quelli interni di Oraclenel modo seguente:

CHAR (integer) e’ un tipo interno anche in Oracle

VARCHAR (integer) ==> VARCHAR2 (integer)

NUMERIC ( p , s ) ==> NUMBER ( p , s )

INTEGER ==> NUMBER ( 38 ) { ≡ NUMBER (38,0) }

FLOAT ==> NUMBER

Le costanti speciali

• NULL

Rappresenta l’assenza di valore e come tale non rientra in nessuno dei tipi di dato. Nulle’ dunque diverso da 0, ‘ ‘ (stringa vuota), etc...

• USER

Rappresenta l’utente che ha aperto la sessione corrente con il DBMS, ed e’ espressonel tipo varchar2.


� � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � �� 16

Tabelle Base(1)

Creazione di una tabella base

base-table-definition ::=

CREATE TABLE base-table-name

( column-name datatype [ NOT NULL ]

{, column-namedatatype [ NOT NULL ] } * )

base-table-name ::=

identifier

column-name ::=

identifier

identifier::=

<string of not more than 18 characters, whose first must be a letter>

Cancellazione di una tabella base

base-table-deletion::=

DROP TABLE base-table-name


� � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � �� 17

Tabelle Base(2)

• Non e’ possibile ad un utente creare due tabelle con lo stesso nome. Nellatabella di sistema che definisce le tabelle utente la primary key e’ costituitainfatti dalla coppiausername, table-name;

• Analogamente non e’ possibile che due colonne abbiano lo stesso nome nellastessa tabella (la primary key e’ data dausername, table-name, column-name);

• E’ invece possibile che piu’ utenti creino tabelle con lo stesso nomeall’interno del medesimo database;

• E’ possibile referenziare in una istruzione SQL una tabella creata da altriutenti utilizzando il nomefully qualified, e cioe’ premettendo lo username alnome della tabella stessa. Affinche’ la cosa vada a buon fine, e’ pero’necessario possedere gli opportuni privilegi per poter eseguire l’operazionesu quella tabella;

• Il creatore di una tabella ha assegnati per default tutti i privilegi sulla tabellastessa (Select, Delete, Insert ed Update su tutte le colonne);

L’opzione NOT NULL

• In SQL un campo puo’ avere valore NULL. Cio’ indica che al campo non e’stato assegnato un valore, ovvero che il suo valore e’ sconosciuto;

• Se pero’ nella definizione di una colonna si specifica NOT NULL, SQLimpedisce l’immissione di record che abbiano un valore “mancante” per lacolonna in questione;

• La possibilita’ di utilizzare NULL come un qualunque altro valoreassegnabile ad un campo crea dei problemi sintattici non indifferenti (ed avolte irrisolubili) soprattutto nei confronti dei test logici e tavole di verita’.


� � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � �� 18

L’inserimento dei dati nelle tabelle(1)

• L’ inserimento dei dati nelle tabelle avviene utilizzando l’istruzione SQLINSERT:

insert-statement ::=

INSERT INTO table [ ( column-name { , column-name } * ) ]

{ VALUES ( insert-atom { , insert-atom } * ) | select-expression }

insert-atom ::=

atom | NULL

atom ::= literal | USER

literal ::= string-literal | numeric-literal

string-literal ::= <character string enclosed by single quotes>

numeric-literal::=


• La istruzione INSERT ha due varianti distinte:

• La prima variante (VALUES) consente di inserire un unico nuovo recordper volta in una tabella. In questo caso si devono fornire i valori deisingoli campi, che possono essere delle costanti oppure delle variabili disistema;

• La seconda variante (select-expression) consente di inserire recordmultipli, ottenuti attraverso una query sul database stesso. L’illustrazionedi questa forma e’ quindi subordinata alla introduzione delle query.


� � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � �� 19

L’inserimento dei dati nelle tabelle(2)

Esempio:

INSERT INTO anagrafica ( cognome, nome, nascita, sesso, citta, provincia, professione,

codice_fisc )

VALUES (‘Casati’, ‘Dario’, ‘21-OCT-75’, ‘M’,‘Pavia’, ‘PV’, 4, ‘QEW32E4D32DSDF34’)

INSERT INTO professioni

VALUES (15, ‘Studente’)

• Non e’ indispensabile indicare il nome delle colonne. Se non lo si fa, SQLassume che la sequenza di immissione dei valori faccia riferimento a tutte lecolonne di cui si compone la tabella secondo lo stesso ordine con cui questesono state specificate al momento della creazione della tabella stessa;

• E’ possibile alterare la sequenza di inserimento dei dati nelle colonnespecificando esplicitamente le colonne stesse ed immettendo i valori diconseguenza;

• Se nella INSERT si sono indicati esplicitamente i nomi delle colonnetralasciandone alcune, in quelle omesse SQL introduce un valore nullo;Alternativamente si puo’ forzare in modo esplicito l’introduzione di un valorenullo utilizzando il simbolo NULL nella lista dei valori;

• Se viene fornita in modo esplicito una lista di colonne non si possonoomettere quelle dichiarate NOT NULL. Analogamente se una colonna e’dichiarata NOT NULL il simbolo corrispondente nella lista di valori nonpotra’ essere il simbolo NULL.

• La lista dei valori fornita con VALUES deve essere compatibile per quantoriguarda il tipo con quella delle colonne in cui questi andranno inseriti.


� � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � �� 20

Le Queries

• Le queries consentono di ricercare i dati in un database, e di conseguenzasono le espressioni piu’ usate in SQL;

• Le queries (query-expression) vengono espresse a partire da una opportunacombinazione diquery-term, a loro volta realizzati mediante l’istruzioneSELECT ed operanti su una o piu’ tabelle;

• Le query-expression, query-term e select-expression restituiscono semprecome risultato una tabella (temporanea);

query-expression ::=

query-term

[ ORDER BY { integer | column-reference } [ ASC | DESC ]

{, {integer | column-reference } [ ASC | DESC ] }* ]

query-term ::=

select-expression |

( query-term) |

query-term UNION [ ALL ]

{ select-expression | (query-term) }


� � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � �� 21

La istruzione SELECT (1)

select-expression ::=

SELECT [ ALL | DISTINCT ]

{ * | column-selection {, column-selection } * }

FROM table [ range-variable ] { , table [ range-variable] } *

[ WHERE search-condition]

[ GROUP BYcolumn-reference {, column-reference } * ]

[ HAVING search-condition]

column-selection ::=

scalar-expression [AS identifier] |

NULL [AS identifier] |

{ table-name| range-variable} .*

scalar-expression ::=

string-expression |

numeric-expression |

( scalar-expression )

string-expression ::=

string-literal |

column-reference |

USER

string-literal ::=

<character string enclosed by single quotes>


� � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � �� 22

La istruzione SELECT (2)

numeric-expression ::=

numeric-literal |

column-reference |

function-reference |

[ + | - ] numeric-expression |

numeric-expression { + | - | * | / } numeric-expression

function-reference ::=

COUNT (*) |

{ AVG | MAX | MIN | SUM | COUNT } ( [ DISTINCT ] scalar-expression )

table ::= [ username . ] table-name

table-name ::=

base-table-name | view-name

column-reference ::= [ { table-name| range-variable } . ] column-name

view-name ::= identifier

username ::= identifier

range-variable ::= identifier

column-name ::= identifier


� � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � �� 23

Alcuni elementari esempi di SELECT(1)

SELECT provincia

FROM anagrafica

SELECT provincia

FROM infmed.anagrafica

SELECT DISTINCT provincia

FROM anagrafica

SELECT citta

FROM anagrafica

SELECT nome, cognome, citta

FROM anagrafica

Restituisce una tabella (di una sola colonna) dovesono riportate le provincie di ciascun recordincluso in anagrafica.

Idem, ma con indicazione estesa (fully qualified)della tabella. Necessario se l’utente che esegue laquery e’ diverso da quello che ha creato la tabella.

SELECT restituisce sempre una tabella (tempora-nea). Specificando DISTINCT, nella nuovatabella vengono eliminati eventuali duplicati.

L’esistenza di alcuni “vuoti” e’ dovuta al fatto cheesistono dei record per i quali il campo citta noncontiene alcun valore (NULL).

In questo caso viene visualizzata una tabella conpiu’ colonne.


� � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � �� 24

Alcuni elementari esempi di SELECT(2)

SELECT codice_prof, decodifica

FROM professioni

SELECT *

FROM professioni

• Da questi elementari esempi (ed anche a causa del suo nome) sembra cheSELECT si limiti a “selezionare” alcuni campi dalla tabella sorgentesemplicemente trasferendoli in quella che costituisce il risultato. In realta’non e’ affatto cosi’, e come indicato nella relativa categoria sintattica,ciascunacolumn-selectione’ in generale unascalar-expression;

SELECT professioni.*, ‘Successivo’, codice_prof + 1

FROM professioni

• In generale si possono comporre colonne diverse da quelle originariamentepresenti nelle tabelle sorgenti, utilizzando costanti, operatori, efunzioni SQL;

SELECT nome AS nome_paziente, cognome AS cognome_paziente

FROM anagrafica

• La keyword opzionale [ASidentifier] permette di assegnare in modo esplicitoun nome alle colonne della tabella che viene generata dalla SELECT.Ovviamente tale keyword puo’ essere utilizzata solo se la relativacolumn-reference identifica una sola colonna della tabella risultato (i.e. non puo’essere utilizzata se lacolumn-reference contiene un asterisco ‘*’).

L’uso dell’asterisco (deve apparire da solo) equivalead indicare a SQL che si desiderano avere nel risul-tato tutte le colonne presenti nella tabella sorgente.


� � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � �� 25

Le clausole WHERE ed ORDER BY

SELECT nome, cognome, citta, provincia

FROM anagrafica


FROM anagrafica

WHERE provincia = ‘PV’


FROM anagrafica

ORDER BY cognome


FROM anagrafica

ORDER BY cognome, nome


FROM anagrafica

ORDER BY provincia, cognome, nome

SELECT nome, cognome, citta

FROM anagrafica

WHERE provincia = ‘PV’

AND sesso = ‘F’

ORDER BY cognome, nome

SELECT nome, cognome, nascita

FROM anagrafica

WHERE nascita < ‘01-Jan-45’

ORDER BY nascita

I record nella tabella di output sono inordine del tutto casuale.

La clausola WHERE svolge una azionedi ‘filtraggio’ durante l’analisi deirecord.

La clausola ORDER BY consente diordinare i record presentati nella tabelladei risultati secondo un ordinamentomultilivello.

Esempio di WHERE ed ORDER BY suun campo di tipo data.


� � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � �� 26

Un semplice esempio con tutte le clausole

• Desidero creare una tabella contenente i nomi delle provincie dove risiedepiu’ di un paziente di sesso femminile nato dopo il 1 Gennaio 1960;

SELECT provincia, COUNT(*) AS totale

FROM anagrafica

WHERE sesso = ‘F’

AND nascita > ‘01-Jan-60’

GROUP BY provincia

HAVING COUNT(*) > 1

ORDER BY provincia

• Le uniche clausole necessarie sono SELECT e FROM, le altre sono inveceopzionali. In ogni caso, le clausole presenti in una SELECT devonocomparire sempre nell’ordine riportato nell’esempio.

La valutazione della SELECT puo’ essere schematizzata con iseguenti passi, eseguiti da SQL nell’ordine indicato:

1) FROM genera una tabella temporanea a partire da quelle indicate nellaclausola;

2) WHERE esegue un filtraggio sui record di tale tabella generandone unanuova. Nellasearch-condition non sono ammesse funzioni di aggregazi-one;

3) GROUP BY crea una nuova tabella in cui ciascun record e’ costituito inrealta’ un insieme di righe (sottotabella). In ogni insieme vengono inseritequelle righe che hanno il medesimo valore per i campi menzionati;

4) HAVING Esegue quindi un filtraggio sugli insiemi di righe definiti dallaGROUP BY. E’ possibile utilizzare funzioni di aggregazione. Questa clau-sola generalmente appare solo se esiste anche GROUP BY. Se GROUPBY non c’e’ le funzioni di aggregazione operano sulla intera tabella;

5) SELECT trasforma la tabella in una nuova che include i campi indicatinellecolumn-selection della clausola;

6) ORDER BY (che non appartiene allaselect-expression, ma allaquery-expression che la include) riordina la tabella secondo i campi indicati e latabella che ne deriva rappresenta il risultato complessivo della operazione.


� � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � �� 27

Come viene valutata la SELECT

• La valutazione di una espressione SELECT avviene applicando nell’ordine leclausole: FROM -> WHERE -> GROUP BY -> HAVING -> SELECT;

• Il procedimento si puo’ schematizzare assumendo che ciascuna clausolariceva in ingresso una tabella passatagli dalla clausola precedente, latrasformi opportunamente, e quindi la restituisca in uscita alla clausolasuccessiva;

• Qui di seguito vengono illustrate le varie trasformazioni subite da una tabelladurante questa elaborazione:

FROM ANAGRAFICA

WHERE sesso = ‘F’ AND nascita > ‘01-Jan-1960’

PROV SESSO NASCITA

PV M 11 Gen 45

MI F 14 Mar 66

BG M 10 Nov 80

NA F 27 Giu 50

PROV SESSO NASCITA

MI F 14 Mar 66

TO F 11 Dic 70

RM F 27 Apr 65

PC F 24 Gen 68

LO F 30 Giu 80


� � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � �� 28

GROUP BY provincia

HAVING COUNT(*) > 1

SELECT provincia, COUNT(*) AS totale

PROV SESSO NASCITA

MI F 14 Mar 66

MI F 18 Apr 63

MI F 20 Mag 90

PC F 24 Gen 68

PV F 12 Giu 61

PV F 8 Dic 87

PROV SESSO NASCITA

MI F 14 Mar 66

MI F 18 Apr 63

MI F 20 Mag 90

PV F 12 Giu 61

PV F 8 Dic 87

provincia totale

MI 3

PV 2


� � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � �� 29

Note sulle espressioni SELECT(1)

FROM:

• Se nella FROM e’ menzionata una sola tabella, questa e’ quella che verra’restituita;

• Altrimenti la FROM restituira’ una nuova tabella costruita comeprodottocartesiano fra le righe di tutte quelle referenziate. Le colonne dellatabella risultante saranno formate dallasuccessione (ordinata) di quellepresenti nelle varie tabelle che la compongono;

WHERE:

• Definisce i predicati utilizzati per filtrare i singoli record. Non sonoammesse funzioni di aggregazione;

HAVING:

• Definisce i predicati utilizzati per filtrare i gruppi di record;

Nella clausola HAVING possono comparire solo espressioni che sianosingle-valued per group;

• In ogni caso potranno quindi essere presenti delle costanti (literal);

• Se esiste GROUP BY:

nellasearch-condition della HAVING possono esserci funzioni di aggre-gazione (con argomento qualunque campo della tabella). Tali funzioniopereranno su ciascun singolo insieme di righe (aggregazione) generatodalla GROUP BY;

possono essere presenti anche espressioni che si riferiscono ai soli campimenzionati in GROUP BY;

• Se non esiste GROUP BY:

nella search-condition di HAVING possono comparire funzioni di aggre-gazione (con argomento qualunque campo della tabella) che opererannosu tutta la tabella;

non possono pero’ essere utilizzate espressioni che facciano riferimento aicampi della tabella (al di fuori delle funzioni di aggregazione) .


� � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � �� 30

Note sulle espressioni SELECT(2)

SELECT:

Se la tabella e’ “grouped” nella clausola SELECT possono compariresolo espressioni che sianosingle-valued per group. In caso contrarionon esiste una simile restrizione.

• In ogni caso potranno essere presenti delle costanti (literal);

• Possono essere utilizzate funzioni di aggregazione (con argomentoqualunque campo della tabella), che opereranno sui singoli insiemigenerati da GROUP BY (se esiste) oppure sull’intera tabella;

• Se SELECT usa funzioni di aggregazione, oppure esiste GROUP BY e/oHAVING:

Nella clausola SELECT possono solo comparire solo funzioni di aggre-gazione e/o colonne che compaiono anche nella GROUP BY. Quindi senon esiste GROUP BY non possono essere utilizzate espressioni che fac-ciano riferimento ai campi della tabella (al di fuori delle funzioni di aggre-gazione);

• Se SELECT non usa funzioni di aggregazione, e non esiste GROUP BY enon esiste HAVING:

nella clausola SELECT sono ammesse espressioni che referenzianoqualunque campo della tabella.

ORDER BY:

• Se nella ORDER BY si fa uso dellecolumn-reference, le colonnereferenziate devono essere realmente presenti nella tabella risultato;

• Alternativamente e’ possibile identificare le colonne su cui ordinare latabella indicandone la loro posizione ordinale.


� � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � �� 31

La clausola FROM (Join e range-variables)(1)

• Si ha una join fra tabelle quando nella clausola FROM viene referenziata piu’di una tabella, e nella clausola WHERE e’ presente una condizione checonfronta le colonne di tabelle diverse;

Esempio:

Supponiamo di voler sapere che mestiere fanno i pazienti che abitano aNapoli;

SELECT ana.nome, ana.cognome, pro.decodifica

FROM infmed.anagrafica ana, infmed.professioni pro

WHERE ana.codice_prof = pro.codice_prof

AND ana.provincia = ‘NA’

• In questo caso si sono usate le referenzefully qualified per le colonne, sianella SELECT che nella WHERE. Inoltre, avendo specificato lerange-variables nella FROM, le colonne non vengono piu’ referenziate attraverso ilnome della tabella, ma devono essere referenziate attraverso la relativarange-variable;

• L’uso di column-references (fully qualified) e’ indispensabile nel caso sidebba disambiguare fra piu’ colonne aventi lo stesso nome (ovviamente intabelle diverse);

• Le range-variables possono essere comode qualora si vogliano sostituire inomi originali di una tabella all’interno della query, ma diventanoindispensabili per disambiguare nel caso di tabelle omonime (ma create dautenti diversi) o qualora sia necessario aprire piu’ volte la medesima tabella.


� � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � �� 32

Join, range-variables e subqueriesEsempio:

Vogliamo fare una lista dei pazienti che sono coetanei:

• In questo caso non posso usare la GROUP BY. Con la GROUP BY infattinon riesco a farmi restituire i nomi dei pazienti:

SELECT nascita, count(*)

FROM anagrafica

GROUP BY nascita

HAVING count(nascita) >= 2

• Un modo (semplice) di risovere il problema consiste nel fare una join dellatabella anagrafica con se stessa:

SELECT DISTINCT ana1.nome, ana1.cognome, ana1.nascita

FROM anagrafica ana1, anagrafica ana2

WHERE ana1.nascita = ana2.nascita

AND ana1.codice_fisc <> ana2.codice_fisc

ORDER BY ana1.nascita

• Il modo forse piu’ “elegante” di risolvere il problema consiste nel lanciareunaplain sub-query:

SELECT ana1.nome, ana1.cognome, ana1.nascita

FROM anagrafica ana1

WHERE ana1.nascita IN

( SELECT ana2.nascita

FROM anagrafica ana2

GROUP BY ana2.nascita

HAVING count (nascita) >= 2 )

ORDER BY ana1.nascita

• Non esiste un limite esplicito ai livelli di (sub)query, anche se 3 sembra unbuon compromesso fra comprensibilita’ e potenzialita’.


� � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � �� 33

La clausola WHERE

• La clausola WHERE realizza un test logico su ogni riga della tabellaintermedia generata dalla FROM, ricercando le righe che lo soddisfano. Lerighe che lo soddisfano vengono incluse nella nuova tabella transientegenerata, mentre le altre ne sono escluse;

search-condition::=

predicate |

[ NOT ] search-condition |

search-condition { OR | AND } search-condition |

( search-condition )

predicate ::=

scalar-expression { = | <> | < | > | <= | >= } {scalar-expression | subquery } |

scalar-expression [ NOT ] BETWEENscalar-expression AND scalar-expression |

string-expression [ NOT ] LIKE string-literal [ ESCAPEstring-literal ] |

scalar-expression IS [ NOT ] NULL |

scalar-expression [ NOT ] IN { subquery | (atom {, atom} * ) } |

scalar-expression { = | <> | < | > | <= | >= } [ ANY | ALL ]subquery |

EXISTSsubquery

subquery ::=

( select-expression )

atom ::=

literal | USER

literal ::=

string-literal | numeric-literal


� � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � �� 34

Note sulla clausola WHERE

• La clausola WHERE considera il test logico soddisfatto quando questo e’TRUE.

• In generale, a causa della possibilita’ di avere valori NULL e’ tuttavianecessario fare alcune premesse alle consuete regole di valutazione delleoperazioni artimetiche e logiche:

• Se A e’ NULL allora le espressioni +A e -A valutano anche esse a NULL;

• Se A e/o B sono NULL allora le espressioni A+B A-B A*B A/Bvengono tutte valutate NULL;

• Se A e/o B sono NULL allora i test A=B A<>B A<B A>B A<=BA>=B valutano tutti NULL;

• I valori logici diventano allora 3: TRUE, FALSE e NULL (Unknown) e letabelle di verita’ sono le seguenti:

• Per controllare se uno scalare e’ NULL non si puo’ dunque utilizzarel’espressione {scalar-expression = NULL}. Occorre invece utilizzare{ scalar-expression IS [ NOT ] NULL};

• I valori NULL sono pero’ considerati uguali nei seguenti casi:

• ai fini della eliminazione dei duplicati (direttiva DISTINCT);

• ai fini dell’ordinamento (clausola ORDER BY);

• ai fini del raggruppamento (clausola GROUP BY).

AND T N F

T T N F

N N N F

F F F F

OR T N F

T T T T

N T N N

F T N F

NOT

T F

N N

F T


� � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � �� 35

Uso degli operatori disponibili in WHERE(1)

• OPERATORI CONFRONTO:

• Con gli operatori di confronto e’ necessario che entrambi i termini sianoentrambi di tipo simile (stringa o numerico);

• Se si usa una subquery questa deve restituire una tabella di una solacolonna e contenente un solo record;

• In realta’ se la tabella restituisce piu’ di un record non e’ un errore (vediANY ALL).

• BETWEEN:

• I tre scalari presenti devono essere tutti di tipo simile.

• La forma { y BETWEEN x AND z } per definizione e’ equivalente alla{x <= y AND y<= z}

• La forma negata { y NOT BETWEEN x AND z } e’ per definizioneequivalente a { NOT ( y BETWEEN x AND z ) }

• LIKE:

• La string-literal che segue l’operatore LIKE rappresenta una maschera incui ciascun carattere si abbina solo con se’ stesso tranne:

il carattere ‘%’ che rappresenta ogni possibile sequenza di n caratteri (Per ogni n >= 0)

il carattere ‘_’ che rappresenta ogni singolo carattere;

il carattere di ESCAPE (vedi sotto);

• La string-literal che segue ESCAPE deve contenere un unico carattere,che viene inteso come carattere di escape. Il suo scopo e’ quello di privareil successivo carattere della maschera (che sara’ ‘% ‘ o ’_ ‘) del suosignificato speciale, e di farlo invece abbinare con se’ stesso.


� � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � �� 36


• IS NULL:

• E’ l’operatore che consente di testare un valore null. Comeprecedentemente accennato, qualunque altro tipo di test che coinvolge unvalore null restituisce sempre un valore logico NULL (non e’ mai TRUE);

• Inoltre l’espressione { value = NULL } e’ anche sintatticamente errata(cfr. la sintassi BNF);

• La forma { x IS NOT NULL } e’ per definzione equivalente a{ NOT ( x IS NULL ) }.

• IN:

• Se viene fornita una lista diatom questi devono essere tutti dello stessotipo (e la lista non deve contenere duplicati);

• Se si utilizza una subquery questa deve restituire una tabella di una solacolonna, ed un numero arbitrario di record. Ovviamente deve esisterecompatibilita’ di tipo fra la colonna restituita dalla tabella e lo scalare concui si esegue il confronto;

• La forma { x IN (a, b, ...., z) } e’ per definizione equivalente a{ x = a OR x = b OR .... OR x = z };

• La forma {scalar-expression IN subquery} e’ per definizione equivalentea { scalar-expression =ANY subquery} ;

• La forma negata { x NOT IN y } e’ per definizione equivalente a{ NOT ( x IN y) }.


� � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � �� 37


• ALL / ANY:

Esempio:

Si vuole estrarre il paziente piu’ vecchio. Questo caso non si presta comeil precedente (quello dei pazienti coetanei) ad un matching della tabella suse stessa con confronto di due righe (eta’ del primo paz. > eta’ del sec-ondo). Cio’ che mi serve e’ sapere se un pazientee’ il piu’ vecchio ditutti gli altri :


FROM anagrafica

WHERE nascita <= ALL (SELECT nascita

FROM anagrafica

WHERE nascita IS NOT NULL)

• Nel caso non si indichi ne’ ALL, ne’ ANY il default e’ ANY. La tabella dellasubquery deve essere costituita da una sola colonna di tipo compatibile e puo’contenere un numero arbitrario di record.

Attenzione pero’:

• ALL significa cheil test nel suo insieme e’ soddisfatto se e’ superato pertutti i valori restituiti dalla subquery. Quindi se la subquery e’ vuota, iltest e’ sicuramente verificato;

• ANY significa cheil test nell’insieme e’ soddisfatto se e’ superato daalmeno uno dei i valori restituiti dalla subquery. Quindise la subquery e’vuota, il test non e’ verificato.

• Un modo piu’ elegante di risolvere il problema del paziente piu’ vecchio e’ ilseguente:


FROM anagrafica

WHERE nascita =

( SELECT MIN(nascita)

FROM anagrafica)


� � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � �� 38


• EXISTS

• La forma { EXISTSsubquery } valuta FALSE se lasubquery restituiscel’insieme vuoto (cioe’ nessuna riga). Essa valuta invece TRUE in casocontrario (almeno una riga);

• In questo caso la subquery puo’ restituire una tabella composta da piu’ diuna colonna. E’ abituale utilizzare in questo caso la forma “SELECT *”;

Esempio:

Desidero estrarre le professioni che non sono svolte da nessun paziente delmio database;

SELECT decodifica

FROM professioni pro

WHERE NOT EXISTS

(SELECT *

FROM anagrafica ana

WHERE pro.codice_prof = ana.codice_prof)

Questo e’ un esempio (semplice) dicorrelated subquery.


� � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � �� 39

Le correlated Subquery

• Le subquery che all’interno del proprio blocco referenziano colonne citate nelblocco superiore si chiamanocorrelated subquery;

• Tali subquery non possono essere valutate una sola volta, a causa della lorodipendenza dalla query presente nel blocco superiore. SQL e’ quindi costrettoa valutarle ripetutamente procedendo nel modo seguente:

1) La FROM crea la propria tabella transiente come di consueto;

2) La WHERE invoca l’esecuzione della subquery su ciascuna riga dellatabella fornita dalla FROM, per verificare se includerla o meno nellanuova tabella transiente;

Durante ciascuna invocazione della subquery, questa al suo interno avra’accesso alle colonne di un diverso record della tabella transiente.

3) Terminata l’esecuzione del test rappresentato dalla WHERE sulla riga cor-rente si passa alla successiva.

Esempio:

Voglio sapere il nome dei pazienti che sono stati ricoverati in tutti i reparti:

SELECT ana.nome, ana.cognome

FROM anagrafica ana

WHERE NOT EXISTS

(SELECT *

FROM reparti rep

WHERE NOT EXISTS

(SELECT *

FROM ricoveri ric

WHERE ric.reparto = rep.reparto

AND ric.codice_fisc = ana.codice_fisc))


� � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � �� 40

Note sulle subquery• Nella formulazione di subquery e’ opportuno utilizzare alcuni accorgimenti:

1) Una subquery deve sempre essere racchiusa da una coppia di parentesi;

2) Ciascuna subquery deve restituire una tabella dotata diuna sola colonnache sia ovviamentedi tipo compatibile con l’espressione in cui la sot-torichiesta e’ inserita (ad eccezione ovviamente di EXISTS). Tale tabellapuo’ contenere zero, una o piu’ righe;

3) Inoltre, al fine di evitare ambiguita’ e’ importante che tutte le colonne ref-erenziate in un query block siano fully qualified, soprattutto se due bloc-chi annidati fanno uso della stessa tabella;

In caso di ambiguita’ SQL associa le colonne non qualified con la tabellareferenziata nella clausola FROMpiu’ vicina in cui esistono tali colonne;

Esempio:

Voglio sapere il nome dei pazienti che sono finiti in rianimazione:


FROM anagrafica ana

WHERE EXISTS

(SELECT *

FROM ricoveri

WHERE codice_fisc = ana.codice_fisc

AND reparto = (SELECT reparto

FROM reparti

WHERE nome = ‘Rianimazione’))

Se tolgoana da ana.codice_fisc non e’ errore anche se ottengo un risultatocompletamente diverso.


� � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � �� 41

Le funzioni di aggregazione

• Tali funzioni operano su un interoaggregato di dati ed il loro scopo e’ quellodi restituire un unico scalare (oppure NULL) derivante dall’analisi di tutti idati presenti nell’aggregato;

• COUNT (*):

• Restituisce il numero di record presenti nell’aggregato;

• COUNT ( [ DISTINCT ] scalar-expression ):

• Restituisce il numero di volte in cui lascalar-expression risulta non nullanell’aggregato. Se si usa DISTINCT non vengono conteggiate lescalar-expression che restituiscono valori gia’ incontrati;

• MIN ( [ DISTINCT ] scalar-expression ):

• Restituisce il valore minimo che lascalar-expression assume fra tutti imembri dell’ aggregato con valore diverso da NULL. (DISTINCT e’ininfluente ai fini del risultato);

• MAX ( [ DISTINCT ] scalar-expression ):

• Restituisce il valore massimo che lascalar-expression assume fra tutti imembri dell’aggregato con valore diverso da NULL (DISTINCT e’ininfluente ai fini del risultato);

• SUM ( [ DISTINCT ]scalar-expression):

• Restituisce la somma discalar-expression calcolata su tutti gli elementidell’aggregato quando essa ha valore diverso da NULL. (Se si usaDISTINCT non vengono sommate lescalar-expression che restituisconovalori gia’ incontrati);

• AVG ( [ DISTINCT ]scalar-expression):

• Restituisce il valore medio (sommatoria / elementi) calcolato su tutti glielementi dell’aggregato in cuiscalar-expression ha valore diverso daNULL (In questo caso DISTINCT calcola la media non ponderata);


� � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � �� 42

Note sulle funzioni di aggregazione(1)

• COUNT(*), COUNT(), MIN() e MAX() possono essere utilizzate su tutti itipi di campi;

• SUM() ed AVG() si possono utilizzare invece solo con campi di tiponumerico;

• Le funzioni di aggregazione (con la sola eccezione di COUNT(*) ) operanosu di unaaggregazione di scalari, cioe’ su un insieme di scalari identificatodalla colonna di una tabella.

• COUNT(*) opera invece su unaaggregazione di record;

• Se l’argomento di una funzione e’ preceduto da DISTINCT, SQL eliminaeventuali duplicati prima di invocare la funzione. Questa verra’ quindiinvocata su di unaaggregazione costituita da elementi tutti differenti fra loro;

• COUNT(*) non accetta l’argomento DISTINCT (in realta’ non accettaneanche un vero e proprio argomento), ed il suo scopo e’ quello di contaretutti i record esistenti in una determinataaggregazione;


� � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � �� 43

Note sulle funzioni di aggregazione(2)

• L’uso delle funzioni di aggregazione non puo’ esserenested. Anche se lagrammatica presentata indica che gli argomenti delle funzioni diaggregazione sono dellescalar-expression, e a sua volta ciascunascalar-expression puo’ essere costituita da una funzione di aggregazione, questeultimenon possono comunque essere annidate una nell’altra;

• Tutte le funzioni (indipendentemente dall’uso di DISTINCT) prima dioperare su unaggregato di scalari, eliminano dal medesimo eventuali valoriNULL presenti;

• La regola precedente ovviamente non e’ applicabile a COUNT(*) poiche’essa non opera su unaggregato di scalari, bensi’ su unaggregato di record;

• Se dopo avere eliminato i valori NULL ed opzionalmente anche i duplicatil’ aggregato di scalari si e’ ridotto all’insieme vuoto, COUNT() restituiscezero, mentre le altre funzioni restituiscono NULL;

• Nel caso di COUNT(*) evidentemente non ha senso parlare di eliminazionedi NULL e duplicati. Tuttavia anche COUNT(*) restituisce zero se vieneinvocata su un insieme nullo;

• La presenza di funzioni nelle clausole SELECT ed HAVING impone dellerestrizioni sull’uso di eventuali altri nomi di colonna (Vedi note sulle clausoleSELECT).


� � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � �� 44

Esempio:

• Si vuole identificare il min, il max, il totale, la media degli stipendi percepitied il numero di dipendenti che lavorano nel nostro ospedale raggruppandoliper categoria, ed ordinandoli sulla base della retribuzione media di categoria;

SELECT q.descrizione, MIN(p.param * q.retribuzione),

MAX(p.param * q.retribuzione),

SUM(p.param * q.retribuzione),

AVG(p.param * q.retribuzione),

COUNT (*)

FROM personale p, qualifica q

WHERE p.qualif = q.codice

GROUP BY p.qualif, q.descrizione

ORDER BY 5

• In questo caso e’ fondamentale usare anche q.descrizione in GROUP BYperche’ SQL non puo’ sapere che p.qualif ha lo stesso valore di q.descrizione;

• Poiche’ le colonne del risultato non hanno nome, ORDER BY devereferenziarle sulla base della loro posizione. In generale le colonne dellatabella creata dalla SELECT ereditano il nome da quelle da cui vengonooriginate, ma in molti casi cio’ non e’ possibile. In esempi come questodiventa (quasi) indispensabile la direttiva AS che permette di definireesplicitamente il nome delle colonne della tabella generata dalla SELECT.

Esempio:

• Si vuole identificare chi guadagna piu’ della media per la sua categoria:

SELECT pers1.nome, pers1.cognome, qual1.descrizione, pers1.param * qual1.retribuzione

FROM personale pers1, qualifica qual1

WHERE (pers1.qualif = qual1.codice)

AND (pers1.param * qual1.retribuzione) >

(SELECT AVG (p.param * q.retribuzione)


WHERE (p.qualif = q.codice)

AND pers1.qualif = p.qualif)

ORDER BY 4


� � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � �� 45

Esempio:

Si vuole fare una statistica che mostri in quanti reparti e’ stato ricoveratociascun paziente e qual’e’ il numero totale dei suoi ricoveri:

SELECT ana.nome, ana.cognome, count(distinct ric.reparto),

count(ric.reparto)

FROM anagrafica ana, ricoveri ric

WHERE ana.codice_fisc = ric.codice_fisc

GROUP BY ana.codice_fisc, ana.nome, ana.cognome

ORDER BY 3

Esempio:

Si vuole vedere quali sono i reparti che hanno ricoverato il maggiornumero di persone:

SELECT rep.nome, count(distinct ric.codice_fisc), count (ric.codice_fisc)

FROM ricoveri ric, reparti rep

WHERE ric.reparto = rep.reparto

GROUP BY rep.reparto, rep.nome

ORDER BY 1

Esempio:

Si vuole inserire una altra professione nella tabella professioni senzasapere quale sia l’ultimo codice assegnato:

INSERT INTO professioni

SELECT MAX(codice_prof) +1 , ‘Nuova Professione’

FROM professioni


� � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � �� 46

Le Funzioni SQL disponibili in Oracle

oracle-string-function ::=

TO_CHAR ({ numeric-expression| date_expression} [ , string-expression ] ) |

CONCAT ( string-expression, string-expression) |

SUBSTR (string-expression, integer, integer) |

UPPER (string-expression) |

LOWER (string-expression )

to_char Converte una espressione (numerica o data) in una stringa di tipo VARCHAR2,secondo un formato di default oppure secondo la specifica opzionale di conver-sione fornita instring-expression. Per il formato distring-espression nel caso diunadate-expression si veda la funzione TO_DATE;

concat Restituisce la concatenazione delle due stringhe;

substr Restituisce una sottostringa che parte dalla posizione indicata attraverso il primointeger e composta dal numero di caratteri pari al secondointeger;

upper Restituisce la stringa originaria dove i caratteri alfabetici sono stati resi maiuscoli;

lower Restituisce la stringa originaria dove i caratteri alfabetici sono stati resi minuscoli;

oracle-numeric-function ::=

ABS ( numeric-expression ) |

MOD ( integer , integer ) |

ROUND (numeric-expression ) |

TRUNC (numeric-expression ) |

SQRT ( numeric-expression ) |

SIGN ( numeric-expression ) |

LENGTH ( string-expression ) |

MONTHS_BETWEEN (date-expression , date-expression ) |

TO_NUMBER (string-expression )

abs Restituisce il valore assoluto di una espressione numerica;

mod Restituisce il modulo della divisione del primointeger per il secondo;

round Restituisce un intero per arrotondamento dellanumeric-expression;

trunc Restituisce un intero per troncamento dellanumeric-expression;

sqrt Restituisce la radice quadrata dellanumeric-expression;

sign Restituisce -1, 0, +1 a seconda del segno dinumeric-expression;

length Restituisce un intero che rappresenta la lunghezza distring-expression;

months_between Restituisce un numero frazionario che rappresenta il numero di mesi esist-ente fra le duedate-expression passate come argomento;

to_number Restituisce un numero floating point;


� � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � �� 47

oracle-date-function ::=

ADD_MONTHS (date-expression, integer ) |

SYSDATE |

TO_DATE ( string-expression [ , string-expression ])

add_months Restituisce una nuova data aggiungendo il numero di mesi indicati tramiteinteger;

sysdate Restituisce la data ed ora attuali;

to_date Converte unastring-expression (primo argomento) in un oggetto di tipoDATE secondo un formato di default oppure secondo la specifica opzion-ale fornita instring-expression (secondo argomento);

string expression e’ una stringa che include uno o piu’ gruppi di conver-sione separati da spazi bianchi oppure dai seguenti caratteri separatori:

- (dash) / (slash) . (dot) , (comma) : (colon) ; (semicolon).

I gruppi di conversione piu’ interessanti sono i seguenti:

DD giorno del mese (1-31) MM mese (01-12)

DY nome abbr. del giorno (3 crt) MON nome mese abbr. (3 crt)

HH24 Ora del giorno (0-23) SS secondi (0-59)

J Giorno Giuliano YYYY anno (4 cifre)

MI minuti di ora (0-59) YY anno (ultime 2 cifre)

-- -- --

NVL ( scalar-expression , scalar-expression )

nvl Restituisce il primo argomento se questo e’ NON NULL. Se invece questo e’ NULLviene restituito il secondo argomento. La funzione assume il datatype della espressionerestituita e pertanto, a seconda dei casi, puo’ appartenere a ciascuna delle 3 categoriesintattiche precedenti.


� � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � �� 48

L’ istruzione INSERT con select-expression


INSERT INTO table [ ( column-name {, column-name } * ]


• La seconda forma della istruzione INSERT prevede l’uso di unaselect-expression, e consente di inserire un numero arbitrario di record. In pratica laselect-expression restituisce una tabella che viene “inserita” in quellamenzionata nella clausola INSERT;

• L’istruzione SELECT non puo’ fare riferimento alla tabella in cui vengonoinserite le nuove righe (in realta’ il precedente esempio ha violato questaregola);

• Le colonne della tabella generata da SELECT devono corrispondere innumero e tipo a quelle esplicitamente indicate nella istruzione INSERT o aquelle della tabella in cui verranno inserite le righe.


� � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � �� 49

Esempio di INSERT con select-expression

• L’utilizzo di INSERT con unaselect-expression puo’ essere utile quando sideve alterare la struttura del database:

Esempio:

Supponiamo che per mutate esigenze del database si debba introdurre uncampo nella tabella reparti in cui e’ inserito il codice del primario:

CREATE TABLE reparti_temp

(reparto smallint,

nome char(20) )

INSERT INTO reparti_temp

SELECT * FROM reparti

DROP TABLE reparti

CREATE TABLE reparti

(reparto smallint,

nome char(20),

codice_prim smallint )Cancello la tabella temporanea

Creo una tabella temporanea

Trasferisco tutti i valori nellanuova tabella

Cancello la tabella originaria

Ricreo la tabella reparti sec-ondo le nuove esigenze


� � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � �� 50

INSERT INTO reparti

SELECT rep.reparto, rep.nome, pers.codice

FROM reparti_temp rep, personale pers

WHERE pers.reparto = rep.reparto

AND pers.qualif =

(SELECT codice

FROM qualifica

WHERE descrizione = ‘Primario’)

• A questo punto pero’ la tabella non e’ completa. Infatti nel nostro ospedaleesistono dei reparti senza primario. Questi reparti ovviamente non sono statiintrodotti nella nuova tabella. Occorre quindi provvedere esplicitamente.

INSERT INTO reparti (reparto, nome)

SELECT rep.reparto, rep.nome

FROM reparti_temp rep

WHERE NOT EXISTS

( SELECT *

FROM personale pers

WHERE pers.reparto = rep.reparto

AND pers.qualif =

(SELECT codice

FROM qualifica

WHERE descrizione = ‘Primario’))

DROP TABLE reparti_temp

Poiche’ non inserisco il pri-mario e’ necessario indicarele colonne


� � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � �� 51

L’istruzione UPDATE

update-statement ::=

UPDATE table [ range-variable ]

SETassignment-expression {, assignment-expression} *

[ WHEREsearch-condition]

assignment-expression ::=

column-name = { scalar-expression | NULL }

• L’istruzione UPDATE consente di modificare i valori dei campi per i recordgia’ esistenti;

• E’ una istruzione a righe multiple, poiche’ opera su un numero arbitrario dirighe, identificate dallasearch-condition;

• Se non esiste lasearch-condition la UPDATE opera su tutta la tabella;

Esempio:

Supponiamo di esserci sbagliati ed avere inserito il nome al posto del cog-nome per i pazienti di sesso femminile.

UPDATE anagrafica

SET nome = cognome,

cognome = nome

WHERE sesso = ‘F’

• Si noti come non si sia creata una situazione diloop nonostante lo scambiodei campi. Cio’ e’ possibile poiche’ la update lavora sempre su una copia delrecord corrente, e memorizza i risultato solo al termine della operazione.


� � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � �� 52

L’istruzione DELETE

delete-statement ::=

DELETE FROMtable [ range-variable ]

[ WHEREsearch-condition ]

• Consente di cancellare un numero arbitrario di record da una tabellaidentificandoli mediante unasearch-condition;

• Se non si specifica la search-condition vengono cancellati tutti i record;

Esempio:

Si vogliono cancellare dal database i pazienti che non sono mai statiricoverati:

SELECT count (DISTINCT codice_fisc)

FROM ricoveri

SELECT count (codice_fisc)

FROM anagrafica

DELETE FROM anagrafica

WHERE NOT EXISTS (

SELECT *

FROM ricoveri

WHERE codice_fisc = anagrafica.codice_fisc )

SELECT count (codice_fisc)

FROM anagrafica

Restituisce il numero dellepersone che sono statericoverate almeno una volta

Restituisce il numero dellepersone presenti nellatabella anagrafica

Adesso verifico che ilnumero e’ uguale a quellodei pazienti ricoverati


� � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � �� 53

L’operatore UNION

query-term ::=

select-expression |

( query-term) |

query-term UNION [ ALL ]

{ select-expression | (query-term ) }

• Mediante UNION si possono combinare piu’ tabelle sorgenti in una unicatabella risultato;

• Le tabelle sorgenti devono avere lo stesso numero di colonne, e le colonnecorrispondenti devono essere di tipo compatibile;

• In assenza della direttiva ALL vengono automaticamente rimossi eventualiduplicati dal risultato finale. In questo senso il comportamento di questoopeartore e’ in contraddizione con quello di SELECT;

Esempio:

SELECT nome, cognome

FROM anagrafica

UNION

SELECT nome, cognome

FROM personale


FROM anagrafica ana, personale per

WHERE ana.nome = per.nome

AND ana.cognome = per.cognome

..... in effetti noto che alcunipazienti sono in realta’ anchedipendenti dell’ospedale

Se conto i record noto che nonsono esattamente la somma diquelli presenti nelle tabelle ini-ziali .....


� � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � �� 54

Le Viste (Views)

view-definition ::=

CREATE VIEW view-name [ ( column-name { , column-name} * ) ]

AS select-expression

[ WITH CHECK OPTION ]

view-deletion ::=

DROP VIEW view-name { , view-name } *

• Le viste sono delle tabellevirtuali, in quanto il loro contentuto non e’direttamente memorizzato nei files del database come avviene per le tabellebase, ma e’ ricavato da quello di una altra tabella (sia tabelle base che altreviste) attraverso una espressione di query;

• La espressione di query che definisce una vista non e’ valutata al momentodella definizione, ma viene ricalcolata in modo automatico ogni volta che lavista stessa e’ referenziata;

• Tutto cio’ avviene in modo trasparente, e l’effetto e’ identico a quello che siavrebbe lavorando con una tabella base;

• Le viste sono quindi tabelle come le altre, e possono essere utilizzate in tuttele query. In alcuni casi e’ anche possibile utilizzarle nelle istruzioni dimodifica ( INSERT, UPDATE e DELETE );

• L’indicazione dei nomi delle colonne della vista e’ facoltativa, ed inmancanza SQL utilizza i nomi delle colonne della SELECT da cui la vista e’derivata. In alcuni casi pero’ la SELECT restituisce colonne senza nome,quindi per evitare ambiguita’ e’ comunque consigliabile indicarli sempre.

• Non e’ invece possibile specificare i tipi di dati poiche’ sono definitiimplicitamente dai tipi delle colonne dellaselect-expression.


� � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � �� 55

Esempio:

Desidero creare una vista in cui esistano soltanto informazioni “imperson-ali” sui ricoveri dei vari pazienti, in modo da renderle accessibili a chi-unque voglia elaborare delle statistiche. I campi della vista sono:

data di nascita - sesso - data di dimissione - drg alla dimissione

CREATE VIEW pazienti

AS SELECT ana.nascita, ana.sesso, ric.data_dim, drg.decodifica

FROM anagrafica ana, ricoveri ric, drg


AND ric.drg = drg.codice

SELECT * FROM pazienti

• Le viste possono essere utili pergarantire la sicurezza dei dati:

E’ possibile assegnare i privilegi di lettura sulla vistapazientia chiunquedesideri un accesso alle statistiche di ricovero poiche’ essa non contienealcuna informazione a carattere personale;

I privilegi di lettura sulle tabelle componenti saranno invece assegnati alsolo personale medico e paramedico che afferisce al reparto.

• L’uso delle viste puo’ essere utile pereliminare la ridondanza e garantire laconsistenzadei dati:

• Se vengono introdotti nuovi record nella tabellaanagrafica e/oricoveri, ilcontenuto della vista riflette immediatamente tali modifiche;

• Definendo invecepazienti come tabella base (ed inserendovi i dati conINSERT ) si avrebbe una duplicazione di informazioni nel database, con ilrisultato che la stessa tabellapazienti diventerebbe obsoleta alla primamodifica dianagrafica e/oricoveri;

• Le viste possono essere utilizzate pergarantire l’indipendenza dai dati:

Nel caso si abbia una applicazione scritta per operare su tabelle strutturatediversamente da quelle disponibili nel database, anziche’ riscriverel’applicazione e’ possibile creare una diversa “vista” sui dati che emuli letabelle su cui l’applicazione disponibile e’ in grado di operare.


� � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � �� 56

• Le viste vengono anche comunemente utilizzate persemplificare leoperazioni di retrieval dei dati, facilitando la scrittura delle istruzioni di querymediante la creazione di tabelle-vista intermedie;

Esempio:

Voglio sapere nome e cognome dei primari e nome dei reparti in cui e’stato ricoverato il paziente Alberto Ferrari;

E’ certamente possibile comporre una singola istruzione di query: questadovra’ referenziare le seguenti tabelle:

anagrafica (per individuare nome e codice fiscale dei pazienti);

ricoveri (per individuare i ricoveri sulla base del codice fiscale);

reparti (per estrarre il nome del reparto dal codice reparto)

personale (per identificare il personale del reparto)

qualifica (per trovare il primario del reparto)

SELECT DISTINCT per.nome, per.cognome, rep.nome

FROM anagrafica ana, ricoveri ric, reparti rep, personale per, qualifica qual


AND ric.reparto = rep.reparto

AND per.reparto = rep.reparto

AND per.qualif = qual.codice

AND qual.descrizione = ‘Primario’

AND ana.nome = ‘Alberto’

AND ana.cognome = ‘Ferrari

• L’esempio illustrato era particolarmente semplice, ed e’ stato possibilerisolverlo con una join fra 5 tabelle che tuttavia rendono pressoche’incomprensibile il testo della query stessa;

• Nei casi piu’ complessi pero’ la stesura di una unica query puo’ risultareproibitiva.


� � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � �� 57

• Se esaminata da un punto di vista logico la query si presta in modo eccellentead essere scomposta in diverse parti;

1) Creazione di una tabellapaz_rep che associ ai nomi dei pazienti i codicidei reparti in cui essi sono stati ricoverati;

2) Creazione di una tabellapri_rep in cui ciascun primario e’ associato alcodice del reparto presso cui lavora;

3) Combinazione delle tabellepaz_rep e pri_rep in modo che a ciascunpaziente venga associato il primario dei reparti in cui e’ stato ricoverato;

4) Selezione di un particolare paziente e combinazione con la tabellarepartiper l’individuazione del nome del reparto;

CREATE VIEW paz_rep

AS SELECT DISTINCT ana.nome, ana.cognome, ric.reparto



CREATE VIEW pri_rep

AS SELECT per.nome, per.cognome, per.reparto

FROM personale per, qualifica qua

WHERE per.qualif = qua.codice

AND qua.descrizione = ‘Primario’

CREATE VIEW paz_pri (paznome, pazcognome, repcode, prinome, pricognome)

AS SELECT paz.nome, paz.cognome, paz.reparto, pri.nome, pri.cognome

FROM paz_rep paz, pri_rep pri

WHERE paz.reparto = pri.reparto

SELECT p.paznome, p.pazcognome, rep.nome, p.prinome, p.pricognome

FROM paz_pri p, reparti rep

WHERE p.repcode = rep.reparto

AND p.paznome = ‘Alberto’

AND p.pazcognome = ‘Ferrari’

• Si noti che la vista paz_pri e’ stata definita a partire da altre viste.


� � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � �� 58

• Le viste risultano indispensabili quando sia necessario inserire una querynella clausola FROM di un’altra query;

Esempio:

Uno degli esempi precedentemente illustrati ha come obiettivo quello diindividuare i membri del personale che guadagnano piu’ della media perla propria categoria. Si vuole ora calcolare per ciascuno di essi anche ladifferenza;

La query di partenza precedentemente illustrata e’:

SELECT pers1.nome, pers1.cognome, qual1.descrizione, pers1.param * qual1.retribuzione

FROM personale pers1, qualifica qual1

WHERE (pers1.qualif = qual1.codice)

AND (pers1.param * qual1.retribuzione) >

(SELECT AVG (p.param * q.retribuzione)


WHERE (p.qualif = q.codice)

AND pers1.qualif = p.qualif)

ORDER BY 4

• Per ottenere nel risultato anche la differenza fra stipendio reale e media dicategoria dovrei inserire nella clausola SELECT una espressione del tipo:{ ( pers1.param * qual1.retribuzione ) - AVG() } e dovrei includere ancheuna clausola GROUP BY per avere il raggruppamento per categoria;

• D’altra parte introducendo un raggruppamento per categoria non posso piu’referenziare i parametri relativi ai singoli membri di ciascuna categoria;

• Detto in altre parole, se desidero la media per categoria non posso averecontemporaneamente anche lo stipendio del singolo membro,quindi nonsaro’ mai in grado di fare la differenza fra questi due termini;


� � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � �� 59

Se analizziamo le cause dell’ “impasse” notiamo che:

• Il valore restituito da AVG non e’ direttamente presente nelle tabelle deldatabase, ma e’ un singolo scalare che viene calcolato su un aggregato;

• Tale valore e’funzione delle informazioni relative a tutti i membri presentinell’aggregato;

• Alla base dell’impasse c’e’ la necessita’ di utilizzare tale valore con ciascunodei membri dell’aggregato da cui il valore stesso e’ stato estratto;

• In definitiva iovorrei essere in grado di “moltiplicare” tale valore su tutti imembri dell’aggregato;

• Il problema potrebbe quindi essere risolto introducendo le medie retributiveper categoria come un elemento della clausola FROM (dal momento chequesta realizza un prodotto cartesiano), ma purtroppo non e’ consentito.

Posso pero’ definire la mia query come una vista:

CREATE VIEW retrib_medie (codice, media)

AS SELECT p.qualif, AVG (p.param * q.retribuzione)



GROUP BY p.qualif

Finalmente posso fare il prodotto cartesiano:

SELECT p.nome, p.cognome, q.descrizione, p.param * q.retribuzione,

(p.param * q.retribuzione) - r.media, r.media

FROM personale p, qualifica q, retrib_medie r


AND p.qualif = r.codice

AND (p.param * q.retribuzione) > r.media

ORDER BY 4


� � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � �� 60

Come viene valutata una vista da SQL ?

• Puo’ essere comodo pensare che al momento della esecuzione di una queryche referenzia una vista, la vista stessa venga in qualche modo espansa esostituita dalla query che la definisce. SQLfarebbe cosi’ un merge tra laquery che definisce la vista e quella in cui la vista e’ referenziata;

• Molto probabilmente questo e’ quello che avviene in realta’, anche se lostandard non prescrive nulla in materia, e lascia libero ogni sviluppatore diadottare il metodo che preferisce;

• L’ambiguita’ semantica derivante dal fatto che lo standard non specifica comeviene valutata in realta’ una vista e’ fonte di molte limitazioni (cheovviamente dipendono dai singoli prodotti SQL);

• Queste si presentano soprattutto con le grouped views (viste definite conquery expression dove ricorre almeno una delle seguenti condizioni):

• SELECT fa uso di funzioni di aggregazione nelle column-selection;

• Viene usata la clausola GROUP BY

• Viene usata la clausola HAVING


� � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � �� 61

INSERT, UPDATE, DELETE con le Viste

• E’ possibile utilizzare le viste non soltanto per operazioni di retrieval maanche di update (INSERT, UPDATE, DELETE). Anche in questo casoesistono alcune limitazioni:

• La select-expression non deve contenere DISTINCT

• Ciascunacolumn-selection deve essere una semplicecolumn-reference(i.e. non deve includere operazioni).

• La FROM deve includere una sola tabella che sia a sua volta aggiornabile(non sono ammesse join).

• La clausola WHERE non deve includeresubquery

• Non devono esistere GROUP BY ed HAVING

Esempio:

Voglio fare una tabella in cui sono registrati tutti i pazienti di anziani:

CREATE VIEW anziani

AS SELECT nome, cognome, nascita, sesso, codice_fisc

FROM anagrafica

WHERE nascita < ‘010140’

Posso cancellare dei pazienti.

DELETE FROM anziani

WHERE nome = ‘Francesco’

AND cognome = ‘Lanati’


� � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � �� 62

Posso pero’ anche introdurre un nuovo paziente:

INSERT INTO anziani

VALUES ( ‘Antonio’, ‘De Paoli’, ‘031193’, ‘M’, ‘QWERTYUIOPPOIUYT’)

• In realta’ il paziente appena inserito non puo’ certo definirsi un anziano, ebenche’ sia stato inserito correttamente, scompare subito dalla vista; Pervederlo devo fare una query sulla tabella anagrafica;

SELECT nome, cognome, nascita, codice_fisc

FROM anagrafica

WHERE nome = ‘Antonio’

CREATE VIEW anziani2

AS SELECT nome, cognome, nascita, sesso, codice_fisc

FROM anagrafica

WHERE nascita < ‘010140’

WITH CHECK OPTION

• Specificando WITH CHECK OPTION all’atto della definizione della vistaSQL controlla che il nuovo record introdotto (o i campi modificati nel caso diUPDATE) soddisfino ancora il test espresso nella search-condition. In casocontrario l’operazione viene annullata.

• In realta’ lacheck option e’ ai limiti dello standard e molti sistemi SQL sicomportano diversamente. INGRES ad esempio quando si crea una vista conla check option impedisce ogni operazione che comporti qualunque tipomodifica dei campi menzionati nella WHERE.


� � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � �� 63

Cenni sulla complessita’ computazionale estrategie di ottimizzazione

• Il principale vantaggio di un linguaggio di tipo dichiarativo quale SQLconsiste nella possibilita’ di disinterassarsi completamente del modo(procedura) in cui vengono eseguite le varie operazioni, e permettere allosviluppatore di focalizzarsi unicamente sul risultato che egli vuole ottenereattraverso la query;

• Cio’ e’ molto utile al livello concettuale, in quanto l’utente non si deveoccupare di problemi che riguardano aspetti prettamente computazionaliquali: gestione delle iterazioni sulle tabelle, uso di puntatori per “navigare”attraverso i dati, etc....

• Per potere valutare ogni query nel modo piu’ efficiente, SQL e’ dotato di unmodulo di ottimizzazione che provvede ove possibile a riformulareautomaticamente la query stessa in modo tale da rendere piu’ veloce la suaesecuzione;

• E’ tuttavia opportuno essere a conoscenza di alcune strategie utili performulare la query in modo che sia piu’ facilmente “gestibile” dal modulo diottimizzazione;

• E’ inoltre possibile velocizzare l’esecuzione delle queries attraverso l’uso diindici (non direttamente previsti dalla teoria relazionale) e l’introduzione diinformazioni duplicate (in contraddizione con il modello relazionale);

• Mediante una opportuna combinazione di tutte queste tecniche e’ possibilemigliorare notevolmente le prestazioni del sistema.


� � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � �� 64

Esempio:

Per il paziente Luca Rotondo si desidera conoscere l’andamento dellatemperatura relativo alle visite disponibili durante il suo(i) ricovero/i inRianimazione. La tabella risultato conterra’ 4 campi:

data ricovero - data dimissione - data visita - valore della temperatura:

SELECT ric.data_ric, ric.data_dim, v.data, v.temp

FROM anagrafica a, ricoveri ric, visite v, reparti rep

WHERE (a.codice_fisc = ric.codice_fisc)

AND (a.codice_fisc = v.codice_fisc)

AND (ric.reparto = rep.reparto)

AND (v.data BETWEEN ric.data_ric AND ric.data_dim)

AND (rep.nome = ‘Rianimazione’)

AND cognome = ‘Rotondo’

• Un database “reale” potrebbe presentare la seguente complessita:

• Numero pazienti: circa 1000;

• Numero medio di ricoveri per paziente: circa 1.5 ==> 1500 ricoveri;

• Durata media per ricovero 5gg

• Numero di visite giornaliere: 2;

• Numero totale di visite: 1000 * 1.5 * 5 * 2 = 15000 visite.

Numero di record restituiti mediamente dalla query sopra illustrata:

1 pz * 1.5 ricoveri * 5 gg * 2 visite = 15 record:

Numero di record da cui e’ mediamente costituita la tabella transiente generatadalla FROM:

1000 * 1500 * 15000 * 10 = 225 Miliardi

• Un record di tale tabella (ottenuta dal join di 4 tabelle) e’ composto da circa25 campi. Sottostimando pesantemente a 4 byte l’occupazione in memoria diogni singolo campo si ottengono 100 bytes/record. La tabella transientesarebbe quindi costituita da 22.5 Terabytes (22500 Gigabytes).


� � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � �� 65

Come viene valutata da SQL (in realta’)l’istruzione SELECT ? (1)

• Le regole illustrate nelle precedenti lezioni circa il modo in cui la istruzioneSELECT viene valutata sono certamente valide per quanto riguarda ilmodello concettuale del loro funzionamento;

• Internamente pero’ il modulo di ottimizzazione delle query fa si che lavalutazione avvenga in modo alquanto differente;

• La principale differenza consiste nel fatto che il risultato della valutazionedelle varie clausole viene generato dinamicamente enon esiste una nettaseparazione fra le azioni svolte da ciascuna singola clausola. Facendoriferimento all’esempio precedente:

• SQL compone la tabella risultato utilizzando fin dal primo momento lecondizioni imposte dalla WHERE. Nel momento stesso in cui vieneeseguito il “prodotto cartesiano” fra le tabelle, SQL controlla se ogninuovo record generato sia da includere nel risultato o meno;

• Il vantaggio principale che ne consegue consiste nel non dover passareattraverso una tabella che altrimenti conterrebbe diversi miliardi di recordsuperando quindi le capacita’ di memorizzazione di ogni sistema dielaborazione oggi esistente;

• Un simile metodo apporta anche notevoli vantaggi dal punto di vistacomputazionale, poiche’ impedisce l’inutile generazione di ulterioriprodotti cartesiani a partire da quelle combinazioni intermedie che gia’ diper se’ non sono in grado di soddisfare la condizione espressa nellaWHERE.


� � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � �� 66

Come viene valutata da SQL (in realta’)l’istruzione SELECT ? (2)

Infatti, date le tabelle Ana, Ric, Rep e Vis:

Ana = { Ana-1, Ana-2, Ana-3, Ana-4 }

Ric = { Ric-1, Ric-2, Ric-3, Ric-4 }

Vis = { V is-1, Vis-2, Vis-3, Vis-4 }

Rep = { Rep-1, Rep-2, Rep-3, Rep-4 }

• supponiamo che SQL abbia generato la combinazione intermedia: {Ana-3, Ric-2};

Se {Ana-3, Ric-2} gia’ di per se’ non soddisfa uno dei test presenti nellaWHERE {ana.codice_fisc = ric.codice_fisc}, SQL la scarta immediata-mente;

Si evita cosi’ di espandere ulteriormente quella combinazione moltiplican-dola con i record provenienti anche da Vis e Rep, per creare ....{Ana-3,Ric-2, Vis-1, Rep-1}..... ed accorgersi solo in un secondo momento chel’intera combinazione non soddisfa il test;

• Il metodo pero’ e’ ancora lontano dall’essere ottimale. Infatti, per ogni livellodevono essere necessariamenteesplorate in modo esaustivo tutte lealternative esistenti per quel livello;

• Ad esempio, indipendentemente dal fatto che { Ana-3, Ric-2 } sia valida omeno non e’ possibile fare nessuna assunzione sulle combinazionisuccessive { Ana-3, Ric-3 } ...... SQL dovra’ quindi generare e testaretutte le combinazioni possibili;

• Per tabelle contenenti diverse migliaia di record questo puo’ risultareancora proibitivo dal punto di vista della complessita’ computazionale.


� � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � �� 67

La Indicizzazione dei Record

• Lo scenario precedentemente descritto cambia pero’ radicalmente qualora siapossibile stabilire una relazione di ordinamento su alcuni campi;

• Ritornando ancora all’esempio precedente, se e’ stabilita una relazione diordinamento sul camporic.codice_fisc, SQL anziche’ generare tutte lecombinazioni {Ana-x, Ric-y} per poi selezionare quelle che soddisfano il testespresso nella WHERE (ana.codice_fisc = ric.codice_fisc), puo’ adottare lastrategia opposta:

• Utilizzando l’indice e’ possibile estrarre immediatamante il solo insiemedei record della tabella Ric aventi il camporic.codice_fisc uguale adana.codice_fisc, e si evita a priori di considerare tutti gli altri poiche’sicuramente daranno origine a combinazioni non valide;

Pro e contro relativi all’uso di indici:

• Gli indici occupano spazio su disco.

• Essi permettono di velocizzare sensibilmente l’esecuzione di istruzioni qualila SELECT (in assenza di indicizzazioni il tempo di esecuzione dellaSELECT aumenta molto rapidamente al crescere dei dati nel database);

• Gli indici possono pero’ rallentare l’esecuzione di istruzioni quali INSERT,DELETE ed UPDATE (in assenza di indicizzazione il tempo di esecuzionedella INSERT e’ invariante rispetto alla quantita’ di dati presenti neldatabase);

• L’aumento dei tempi di esecuzione che connesso con la presenza di indicie’ dovuto alla necessita’ di aggiornare e ristrutturare l’indice ogni voltache si modifica un valore in un campo indicizzato.


� � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � �� 68

La istruzione CREATE INDEX

index-definition ::=

CREATE [ UNIQUE ] INDEX index

ON base-table-name

( column-name [ ASC | DESC ]

{, column-name [ ASC | DESC ] }* )

index-deletion ::=

DROP INDEXindex

index::=

identifier

• L’opzione UNIQUE impedisce l’introduzione nella stessa tabella di piu’record aventi lo stesso insieme di valori per la colonne indicizzate. In suaassenza sono ammessi duplicati sulle colonne indicizzate;

• UNIQUE non riserva un trattamento speciale ai NULL. Se l’opzione e’presente essa impedira’ (come ci si aspetta) che vi sia piu’ di un record con lastessa combinazione di valori NULL sulle colonne indicizzate;

• Le colonne dellaprimary keydovranno verosimilmente essere definite comeNOT NULL nella CREATE TABLE (per impedire l’introduzione di NULL).Su di esse verra’ poi creato un UNIQUE INDEX (per impedire che vi sianoduplicati);

• Gli indici possono essere associatisolo alle tabelle-base, e ad una tabellapuo’ essere associato piu’ di un indice;

• L’opzione ASC o DESC determina la direzione dell’indice. In sua assenzaviene assunto ASC come default.


� � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � �� 69

Quando si puo’ creare un indice e come sideve usare ?

• Un indice puo’ essere creato e distrutto in qualunque momento, ed inparticolare anche quando siano gia’ stati introdotti dei valori nella tabella daindicizzare;

• La definizione degli indici rimane sempre opzionale. Gli indici infatti nonsono direttamente previsti dalla teoria relazionale;

• La presenza di un indice ha quindiil solo scopo di velocizzare le operazionidi querye non di modificarne il risultato;

• Poiche’ gli indici non sono contemplati dalla teoria relazionale, SQLimpedisce che sia l’utente ad indicare in modo esplicito se e quando utilizzareun particolare indice durante una query;

• E’ quindi SQL, attraverso il proprio modulo di ottimizzazione, a selezionaredi volta in volta la strategia di esecuzione della query che ritiene piu’ adatta infunzione di svariati criteri quali il numero delle righe, gli indici adisposizione, le varie clausole presenti nell’istruzione, etc...


� � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � �� 70

Dove e’ utile creare un indice?(1)

• Dalle premesse indicate precedentemente si evince come sia molto difficiledefinire regole rigorose per quanto riguarda la creazione degli indici,soprattutto perche’ lo standard non identifica chiaramente le situazioni e lemodalita’ in cui SQL utilizza gli indici.

• E’ tuttavia possibile fissare in modo informale alcuni criteri a caratteregenerale:

• Gli indici sono utilizzati da SQL in combinazione con gli operatori diconfronto { > | < | = | <> | >= | <= };

Primary Key e Candidate Key:

• La primary key di un tabella (insieme alle eventualicandidate keys) sonosempre un candidato ideale per associarvi un indice;

• La teoria relazionale prevede infatti che non esista piu’ di un record avente lastessaprimary key nella medesima tabella;

• Sara’ quindi necessariofare sempre dei controlli sulla primary key almomento della introduzione di un nuovo record o della modifica dei suoivalori;

• L’indicizzazione consente di velocizzare questa ricerca (modificandolaappunto da sequenziale ad indicizzata). Inoltre l’opzione UNIQUE rendeaddirittura automatico il controllo sulla introduzione dei duplicati.

Foreign Key:

• Le foreign key in una tabella sono un altro candidato ideale per la associarviun indice;

• Esse infatti giocano un ruolo primario nel join fra tabelle, e la presenza di unindice consente di velocizzare la ricerca dei record provenienti da tabellediverse che devono essere combinati.


� � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � �� 71


Colonne incluse nelle search-condition (WHERE)

• Ogni colonna utilizzata frequentemente nellesearch-condition con glioperatori di confronto dovrebbe essere indicizzata;

• Negli esempi discussi sono state spesso usate la colonnecognome ecitta’ perricercare i pazienti nel database, nonostante esse non sianoprimary key ne’candidate keyne’ foreign key;

• Indicizzando queste colonne si puo’ velocizzare la ricerca quando si accede aidati atttraverso di esse;

Colonne incluse nelle GROUP BY

• Utilizzare GROUP BY equivale a richiedere a SQL di identificare tutti igruppi di record che hanno ugual valore su un insieme di colonne. Questaoperazione richiede ogni volta l’esecuzione di unconfronto fra tutti i record epuo’ risultare onerosa dal punto di vista computazionale;

• L’uso di un indice elimina la necessita’ di confrontare i record, poiche’ questirisultano di fatto gia’ ordinati (non importa se in senso crescente odecrescente);


� � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � �� 72


Colonne incluse nella ORDER BY

• Anche in questo caso valgono le stesse regole esposte sulconfronto edillustrate nella GROUP BY;

• La differenza e’ che in questo caso non si richiede semplicemente di ricercarei record di ugual valore su un set di campi, ma si desidera avere un vero eproprioordinamento crescente o decrescente su quei campi.

• Una operazione diordinamento (sorting) e’ generalmentemolto onerosa dalpunto di vista computazionale e puo’ essere notevolmente velocizzata con unindice.

• E’ inoltre necessario chel’indice abbia lo stesso verso che ha l’ordinamentorichiesto nella ORDER BY.

In Conclusione:

• Da quanto esposto sinora sembra che valga la pena di mettere il maggiornumero popssibile di indici, adottando il principio quasi sempre valido: “seserve tanto meglio, altrimenti male non fa”;

• Cio’ e’ profondamente sbagliato perche’ un indice consuma risorse in terminidi spazio su disco e soprattutto ogni indice rallenta le operazioni di modificadei dati (INSERT, UPDATE e DELETE);

• La ricerca di una strategia ottimale per la creazione degli indici e’ unaimpresa molto ardua e soprattutto dipendente anche dalle singoleimplementazioni.


� � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � �� 73

Un indice da solo e’ sufficiente ?

• Le capacita’ del modulo di ottimizzazione delle query presente in SQLvariano da prodotto a prodotto, e se in alcuni casi queste sono scadenti, inaltre sono sorprendentemente efficienti;

• E’ tuttavia utile “aiutare” il modulo di ottimizzazione formulando le query inmodo che queste risultino piu’ facilmente gestibili secondo alcuni semplicicriteri:

L’operatore OR

• L’operatore OR rende spesso inutilizzabile un indice, e quindi e’ meglioevitarlo:

• Se questo e’ presente nella search-condition di una SELECT e’ spessoconveniente realizzare due SELECT separate, combinandole con UNION;

• Se invece l’operatore OR compare in una istruzione di UPDATE oDELETE allora e’ meglio suddividere la singola operazione in dueoperazioni distinte

L’operatore IN

• Anche l’operatore IN rende inutilizzabile un indice, e va evitato:

• La forma { x IN (a, b, ...... z) } e’ infatti equivalente per definizione a

{ x = a OR x = b OR ... OR x = z };

• Le regole di sostituzione sono quindi le stesse di quelle visteprecedentemente.


� � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � �� 74

Esempio:

Vogliamo una tabella in cui siano indicati il nome e il cognome di tutti ipazienti ricoverati in Rianimazione e Pediatria:

• Il modo piu’ naturale per esprimere la query risulta essere:

SELECT DISTINCT ana.nome, ana.cognome



AND ric.reparto IN ( SELECT rep.reparto

FROM reparti rep

WHERE rep.nome = ‘Pediatria’

OR rep.nome = ‘Rianimazione’)

• Sostituendo la IN con un operatore di confronto (uguaglianza) e la OR conuna UNION si ottiene:




AND ric.reparto = (SELECT rep.reparto

FROM reparti rep

WHERE rep.nome = ‘Pediatria’)

UNION




AND ric.reparto = (SELECT rep.reparto

FROM reparti rep

WHERE rep.nome = ‘Rianimazione’)


� � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � �� 75

L’operatore NOT

• L’uso dell’operatore NOT con operatori di confronto e’ sintatticamentevalido, ma rende inutilizzabile un indice:

• La forma:

{ NOT ( column-reference < literal ) }

va sicuramente trasformata in:

{ column-reference >= literal }

• Nel caso di un insieme finito di elementi A = {a1, a2, a3} l’espressione:

{ NOT ( column-reference = ‘a1’) }

e’ valutata in modo piu’ efficiente se la si pensa come:

{ (column-reference = ‘a2’) OR (column-reference = ‘a3’) }

In questo caso puo’ essere trasformata usando le regole indicate per la OR;

Le operazioni aritmetiche:

• Il vantaggio derivante dalla presenza di un indice viene spesso annullato dallapresenza di operazioni aritmetiche che coinvolgono una colonna:

• L’espressione:

{ (column + 5) * 3 < 12 }

puo’ essere riscritta come:

{ column < -1 }

L’operatore EXISTS

• L’operatore EXISTS puo’ rallentare l’esecuzione di una query e se possibileandrebbe sostituito con altra formulazione.


� � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � �� 76

La gestione dei privilegi in SQL

• Come precedentemente illustrato esistono vari livelli a cui e’ possibileassegnare privilegi e/o limitazioni all’utente di un DBMS:

1) Sistema Operativo;

2) Connessione al DBMS;

3) Connessione ad un particolare Database;

4) Esecuzione di istruzioni di manipolazione sui dati presenti in un partico-lare Database.

• I punti 1-3 esulano dalla teoria relazionale, e pertanto le istruzioni perl’assegnazione di privilegi previste dallo standard SQL trattano unicamentequelli connessi con il punto 4;

• I privilegi sulle operazioni di manipolazione dei dati vengono concessi inbase alla tripla: (nome utente, operazione, dato );

• Il nome utente e’ evidentemente l’identificatore con cui l’utente e’ noto alDBMS;

• Le operazioni di manipolazione dei dati disponibili in SQL, in accordocon la teoria relazionale sono di quattro tipi diversi:

Retrieval di dati (SELECT);

Inserzione di nuovi record (INSERT);

Cancellazione di record (DELETE);

Modifica dei campi di un record esistente (UPDATE).

• Il datosu cui avviene l’operazione e’ rappresentato dalla tabella nel casodi SELECT, INSERT e DELETE, e dalla tabella + campo nel caso dellaUPDATE.


� � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � �� 77

Le istruzioni GRANT e REVOKE

grant-operation ::=

GRANT { ALL [ PRIVILEGES ] | privilege { , privilege } * }

ON table-name{ , table-name}

TO { PUBLIC | username { , username }* }

[ WITH GRANT OPTION ]

revoke-operation ::=

REVOKE { ALL [ PRIVILEGES ] | privilege { , privilege }* }

ON table-name{ , table-name}

FROM { PUBLIC | username { , username }* }

privilege ::=

SELECT |

INSERT [ ( column-name { , column-name }* ) ] |

DELETE |

UPDATE [ ( column-name { , column-name }* ) ]

• L’utente che ha creato una tabella e’ l’unico che inizialmente ha tutti iprivilegi su quella tabella, ivi compresa lagrant option;

• Egli ha poi la facolta’ di concedere ad altri utenti questi privilegi, ivi inclusala possibilita’ di trasmettere lagrant option;


� � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � �� 78

Il concetto di Transazione

• Nel caso di accessi multipli ad un data-base e’ di fondamentale importanzapoter controllare la sequenzialita’ di tali accessi da parte dei vari utenti inmodo tale da non creare inconsistenze dovute a problemi di concorrenza:

Esempio:

L’introduzione di un ricovero per un paziente nuovo comporta la modificadi due tabelle:anagrafica e ricoveri, e potra’ avvenire solo attraversol’esecuzione di (almeno) due istruzioni distinte.

Se un’altro utente andasse a leggere il database nel preciso istante in cui latabella anagrafica e’ gia’ stata aggiornata, ma quella ricoveri non lo e’ancora, egli potrebbe concludere che il database e’ inconsistente (e.g.esiste un paziente a cui non corrisponde alcun ricovero);

• Per evitare che cio’ accada, SQL prevede il concetto diTransazione, egarantisce che ogni transazione venga vista come operazione “atomica” agliocchi di ogni altro utente eccetto colui che esegue la transazione stessa;

(Atomica vuol direnon ulteriormente scindibile relativamente ai risultatiosservabili. Le modifiche apportate ai dati dalle singole istruzioni che cos-tituiscono una transazione vengono rese accessibili nel loro insieme soloal termine della transazione stessa);

• Nel corso dell’esistenza di ciascuna transazione, ogni istruzione SQL cheaccede ai dati causa l’attivazione di unlock che preclude ulteriori accessi aquei dati da parte di altri processi tranne quello che ha inizialmente attivato illock;

• Ogni transazione deve necessariamente essere esplicitamente terminata dalprocesso che la ha iniziata attraverso una apposita istruzione;

• Le transazioni non possono esserenested.


� � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � �� 79

Commit e Rollback

commit-statement ::=

COMMIT [ WORK ]

rollback-statement ::=

ROLLBACK [ WORK ]

• Entrambe queste istruzioni determinano la fine di una transazione, rilascianoogni eventuale lock e permettono quindi l’accesso ai dati da parte di altriprocessi:

• Non esiste alcuna istruzione di inizio transazione perche’ la transazione vieneiniziata automaticamente;

• COMMIT termina la transazione rendendo visibili le modifiche apportate;

• ROLLBACK termina la transazione cancellando le modifiche apportate, equindi rimettendo il database nelle condizioni precedenti l’inizio dellatransazione (limitatamente ai dati modificati da quest’ultima);

• Cosa succede se qualcuno cerca di accedere ai dati mentre questi sonolocked da qualcun altro?

• Si resta in attesa per un tempo prefissato (eventualmente pari ad∞ );

• Per rendere piu efficiente il sistema sono generalmente previsti vari tipi dilocks (shared, exclusive) la cui funzionalita’ puo’ variare a seconda deltipo di operazione (lettura, scrittura);

• Per evitare problemi, sul database autosan e’ stato rimosso ogni tipo di lock.


� � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � �� 80

La Concorrenzialita’ delle Transazioni

• In un normale database le transazioni vengono generate in modo asincronodalle varie applicazioni che necessitano l’accesso ai dati, con possibilita’ diinterferenza fra di esse;

• Al fine di caratterizzare i diversi tipi di interferenza di sono definiti diversilivelli di isolamento (isolation levels) in cui il DBMS puo’ confinare letransazioni;

• Lo standard prevede 4 differenti “Isolation Level”:

Isolation Level Dirty Read Non Repeatable Read Phantom Row

Read UncommittedNessun controllo

Si Si Si

Read CommittedSi vedono esclusiva-mente i risultati delletransazioniCommitted

No Si Si

Repeatable ReadViene garantita la iden-ticita’ sulle letture rip-

etute.

No No Si

SerializableL’effetto e’ identico a

quello che si avrebbe sele transazioni venisseroeseguite in serie (Nonin modo concorrente)

No No No


� � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � �� 81

Sommario sintassi SQL in BNF

Schema Definition

base-table-definition ::=

CREATE TABLE base-table-name

( column-name datatype [ NOT NULL ]

{, column-namedatatype [ NOT NULL ] } * )

base-table-deletion::=

DROP TABLE base-table-name

datatype ::=

char (integer) |

varchar ( integer) |

numeric (p , s ) |

integer |

float |

date

view-definition ::=

CREATE VIEW view-name [ ( column-name { , column-name} * ) ]

AS select-expression

[ WITH CHECK OPTION ]

view-deletion ::=

DROP VIEW view-name { , view-name } *

index-definition ::=

CREATE [ UNIQUE ] INDEX index

ON base-table-name

( column-name [ ASC | DESC ]

{, column-name [ ASC | DESC ] }* )

index-deletion ::=

DROP INDEXindex


� � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � �� 82

Manipulative Statements


INSERT INTO table [ ( column-name {, column-name } * ) ]


insert-atom ::=

atom | NULL

delete-statement ::=

DELETE FROMtable [ range-variable ]

[ WHEREsearch-condition ]

update-statement ::=

UPDATE table [ range-variable ]

SETassignment-expression {, assignment-expression} *

[ WHEREsearch-condition]

assignment-expression ::=

column-name = { scalar-expression | NULL }

commit-statement ::=

COMMIT [ WORK ]

rollback-statement ::=

ROLLBACK [ WORK ]


� � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � �� 83

Query Expressions

query-expression ::=

query-term

[ ORDER BY { integer | column-reference } [ ASC | DESC ]

{, {integer | column-reference } [ ASC | DESC ] }* ]

query-term ::=

select-expression |

( query-term) |

query-term UNION [ ALL ] { select-expression | ( query-term ) }

select-expression ::=

SELECT [ ALL | DISTINCT ]

{ * | column-selection {, column-selection } * }

FROM table [ range-variable ] { , table [ range-variable] } *

[ WHERE search-condition]

[ GROUP BYcolumn-reference {, column-reference } * ]

[ HAVING search-condition]

column-selection ::=

scalar-expression [ AS identifier ] |

NULL [AS identifier] |

{ table-name| range-variable}.*

subquery ::=

( select-expression )


� � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � �� 84

Search Conditions

search-condition::=

predicate |

[ NOT ] search-condition |

search-condition { OR | AND } search-condition |

( search-condition )

predicate ::=

scalar-expression { = | <> | < | > | <= | >= } {scalar-expression | subquery } |

scalar-expression [ NOT ] BETWEENscalar-expression AND scalar-expression |

string-expression [ NOT ] LIKE string-literal [ ESCAPEstring-literal ] |

scalar-expression IS [ NOT ] NULL |

scalar-expression [ NOT ] IN { subquery | (atom {, atom} * ) } |

scalar-expression { = | <> | < | > | <= | >= } [ ANY | ALL ]subquery |

EXISTSsubquery

Grants

grant-operation ::=

GRANT { ALL [ PRIVILEGES ] | privilege { , privilege } * }

ON table-name{ , table-name} *

TO { PUBLIC | username { , username }* }

[ WITH GRANT OPTION ]

revoke-operation ::=

REVOKE { ALL [ PRIVILEGES ] | privilege { , privilege }* }

ON table-name{ , table-name}*

FROM { PUBLIC | username { , username }* }

privilege ::=

SELECT |

INSERT [ ( column-name { , column-name }* ) ] |

DELETE |

UPDATE [ ( column-name { , column-name }* ) ]


� � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � �� 85

Scalar Expressions

scalar-expression ::=

string-expression | numeric-expression | (scalar-expression )

string-expression ::=

string-literal | column-reference | USER

atom ::= literal | USER

literal ::= string-literal | numeric-literal

string-literal ::= <character string enclosed by single quotes>

numeric-expression ::=

numeric-literal |

column-reference |

function-reference |

[ + | - ] numeric-expression |

numeric-expression { + | - | * | / } numeric-expression

numeric-literal::=


exact-numeric-literal ::=

[ sign] { integer[ . [integer] ] | . integer }

approximate-numeric-literal::=

exact-numeric-literal{ E | e } [ sign] integer

integer ::= { digit } +

digit ::= 0 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9

sign ::= + | -

function-reference ::=

COUNT (*) |

{AVG | MAX | MIN | SUM | COUNT} ( [ DISTINCT ] scalar-expression )


� � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � �� 86

Le funzioni di OraclePossono essere utilizzate ovunque al posto di unatom o di unascalar-expressionpurche’ vi sia compatibilita’ fra il datatype che tali funzioni restituiscono e ildatatype atteso.

oracle-string-function ::=

TO_CHAR ({ numeric-expression| date_expression} [ , string-expression ] ) |

CONCAT ( string-expression, string-expression) |

SUBSTR (string-expression, integer, integer) |

UPPER (string-expression) |

LOWER (string-expression )

oracle-numeric-function ::=

ABS ( numeric-expression ) |

MOD ( integer , integer ) |

ROUND (numeric-expression ) |

TRUNC (numeric-expression ) |

SQRT ( numeric-expression ) |

SIGN ( numeric-expression ) |

LENGTH ( string-expression ) |

MONTHS_BETWEEN (date-expression , date-expression ) |

TO_NUMBER (string-expression )

oracle-date-function ::=

ADD_MONTHS (date-expression, integer ) |

SYSDATE |

TO_DATE ( string-expression [ , string-expression ])

-- -- --

NVL ( scalar-expression , scalar-expression )


� � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � �� 87

Miscellaneous

table ::= [ username . ] table-name

table-name ::=

base-table-name | view-name

base-table-name ::=

identifier

view-name ::=

identifier

column-name ::=

identifier

column-reference ::= [ { table-name| range-variable } . ] column-name

username ::=

identifier

index::=

identifier

range-variable ::=

identifier

identifier::=

<string of not more than 18 characters, whose first must be a letter>

Esercitazioni sull’uso di SQLintermed.unipv.it/.../documents/pdf/dispense_sql.pdf · 2003. 3....

Documents

Transcript of Esercitazioni sull’uso di SQLintermed.unipv.it/.../documents/pdf/dispense_sql.pdf · 2003. 3....