14-Il Linguaggio SQL

  • Home » 14-Il Linguaggio SQL
Corso Informatica

14-Il Linguaggio SQL

 {gspeech style=2}

In generale i primi Data Base Management Systems, ovvero Sistemi di Gestione delle Basi di Dati, abbreviati DBMS, nacquero negli anni ’60 e ’70 proprio per la sempre maggiore necessità di gestire grandi quantitativi di dati eterogenei in maniera facile e veloce. Lo studio di teorie quali l’algebra relazionale permise la nascita dei Sistemi di Gestione delle Basi di Dati Relazionali, RDBMS, e in seguito il Linguaggio di Interrogazione Strutturato, SQL. Si tratta ormai di uno standard internazionale con lievi differenze tra le varie versioni.
Il linguaggio vede un database come un insieme di tabelle che costituiscono le relazioni. All’interno di queste tabelle gli attributi sono rappresentati dalle colonne delle tabelle, i singoli record sono rappresentati dalle righe delle tabelle.

Breve storia del linguaggio SQL
SQL (che si pronuncia es-chiu-el, non siquel come dicono in molti) è l’acronimo di Structured Query Language. Linguaggio creato per l’accesso a informazioni memorizzate nei database.
L’SQL nasce nel 1974 ad opera di Donald Chamberlin, nei laboratori dell’IBM. Nasce come strumento per lavorare con database che seguano il modello relazionale. A quel tempo però si chiamava SEQUEL (Structured English Query Language). Questo linguaggio evolse in seguito in SEQUEL/2 e infine nell’attuale Structured Query Language (SQL). Altre aziende mostrarono un certo interesse per il concetto di database relazionali e per l’emergente interfaccia SQL. Nel 1979 Relational Software, Inc. (l’attuale Oracle Corporation) creò un database relazionale chiamato Oracle. Nel 1981 IBM rilasciò il suo primo database relazionale, chiamato SQL Data System (SQL/DS).
Nel 1982 I’American National Standards Institute (ANSI), comprendendo la potenzialità del modello relazionale, cominciò a lavorare a uno standard di Relational Database Language (RDL). Nel 1984 il consenso ottenuto sul mercato da prodotti come Oracle, SQL/DS, e DB2 di IBM indusse la commissione ANSI a concentrarsi su SQL quale base per il nuovo standard RDL. La prima versione di questo standard, SQL-86, fu adottata sia da ANSI sia da ISO (International Standards Organization) nell’ottobre 1986. Nel 1989 fu adottato un aggiornamento di SQL-86 potenziato con elementi di integrità relazionale. Lo standard attuale, spesso definito “SQL2” o “SQL-92”, riflette un lavoro molto accurato da parte degli organismi internazionali deputati alla definizione di standard che va nella duplice direzione del potenziamento del linguaggio e della correzione delle lacune, confusioni o ambiguità dello standard originale del 1986.
Lo standard quale si presenta oggi è al tempo stesso, nel particolare, un sottoinsieme comune di implementazioni più importanti e, nel complesso, un insieme che ingloba la quasi totalità delle implementazioni. Ciò significa che il nucleo dello standard contiene funzionalità che si possono ritrovare praticamente in tutte le implementazioni commerciali del linguaggio, eppure lo standard nella sua globalità include caratteristiche potenziate che devono essere ancora implementate da molte aziende che lo commercializzano.
Il consorzio SQL Access Group in cui queste aziende si sono riunite ha pubblicato quello che dovrebbe essere considerato come lo “standard commerciale” di SQL, una variante del linguaggio che può essere “parlata” da ciascun database relazionale di una certa rilevanza o che in esso può essere mappata. Una versione estesa di questa Common Language Interface (CLI, ovvero Interfaccia a Linguaggio Comune) fa parte della bozza di SQL3 attualmente in esame. Microsoft ha implementato il supporto per la CLI nell’API di ODBC per Windows (Application Programming Interface di Open DataBase Connectivity). Ciò consente a chi commercializza applicazioni per Microsoft Windows di collegarsi tra loro utilizzando lo standard dell’SQL Access Group. Microsoft Access si collega a molti database attraverso lo standard ODBC e inoltre “parla” un linguaggio che è uno dei principali sottoinsiemi dello standard SQL dell’SQL Access Group.

IL linguaggio SQL
La caratteristica principale di SQL è di non richiedere la specifica di sequenze di operazioni, come per gli altri linguaggi di programmazione, ma di permettere di specificare le proprietà logiche delle informazioni ricercate. Esso si divide in tre sottoinsiemi:

  • DDL (Data Definition Language) Linguaggio di Definizione dei Dati permette di creare e cancellare database o di modificarne la struttura.
  • DML (Data Manipulation Language) Linguaggio di Manipolazione dei Dati – permette di inserire, cancellare e modificare i dati.
  • DCL (Data Control Language) – permette di gestire gli utenti e i permessi.

DDL: Definizione dei dati
DDL serve a creare, modificare o eliminare gli oggetti in un database. Sono i comandi DDL a definire la struttura del database e quindi dei dati ivi contenuti. Ma non fornisce gli strumenti per modificare i dati stessi: per tale scopo di usa il DML. L’utente deve avere i permessi necessari per agire sulla struttura del database e questi permessi vengono assegnati tramite il DCL.

Create Database
Il comando Create Database serve a creare un nuovo database, che potrà contenere tabelle, viste, procedure o altri tipi di oggetti.
Sintassi del comando create database
La sintassi è la seguente:
CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name [create_specification [, create_specification] …] create_specification: [DEFAULT] CHARACTER SET charset_name | [DEFAULT] COLLATE collation_name Se nell’esecuzione del comando si specifica [IF NOT EXISTS] si crea il database solo se non esiste, in caso contrario non verrà restituito alcun errore. La stringa create_specification permette di inserire delle opzioni nella creazione del database. Tramite CHARACTER SET si inserisce il set di caratteri supportato nel database. Tramite COLLATE si possono definire i dati di default del database.

Alter Database
Il comando Alter Database serve a modificare un database esistente.
Sintassi del comando alter database
La sintassi è la seguente:
ALTER {DATABASE | SCHEMA} [db_name] COLLATE collation_name

Drop Database
Il comando Drop Database serve a cancellare un database.
Sintassi del comando drop database
La sintassi è la seguente:
DROP DATABASE nome_database

Create Table
Il comando Create Table ha la funzione di creare una nuova tabella.
Il nome della tabella può essere scritto indifferentemente in MAIUSCOLO o in minuscolo, in ogni caso, però, è necessario che rispetti le seguenti regole:
Può essere formato da lettere e numeri, ma il primo carattere deve sempre essere una lettera; Non può superare i 30 caratteri di lunghezza;
Non può avere lo stesso nome di una tabella o già esistente sullo stesso utente di database. Oltre a definire gli attributi di una tabella è possibile definire dei vincoli.
La tabella può essere creata vuota oppure può essere creata e riempita di dati: nel secondo caso la struttura della tabella è definita implicitamente dal numero di colonne (o campi) estratti dalla select, dal tipo di dato di ciascuna colonna e dai nomi delle rispettive colonne estratte dalla select (o dagli alias ad esse applicati). Nel caso in cui la tabella venga popolata in fase di creazione, la transazione viene automaticamente conclusa da un comando di COMMIT.
Vincoli d’integrità
Implementare i Vincoli di integrità con l’SQL: esistono delle parole riservate.
Not Null: Il vincolo Not Null inidica che il valore nullo non è ammesso come valore dell’attributo; in tal caso l’attributo deve sempre essere specificato tipicamente in fase di inserimento. Se all’attributo è però associato un valore di default diverso dal valore nullo, allora diventa possibile effettuare l’inserimento anche senza fornire un valore dell’attributo, in quanto all’attributo viene automaticamente assegnato il valore di default.
Unique: Un vincolo Unique si applica ad un attributo o un insieme di attributi di una tabella e impone che i valori dell’attributo (o le ennuple di valori sull’insieme di attributi) siano una (super)chiave, cioè righe differenti della tabella non possano comparire su diverse righe senza violare il vincolo in quanto si assume che i valori nulli siano tutti diversi tra loro.
Primary Key: Nella definizione di una tabella è necessario specificare per ogni relazione la chiave primaria, il più importante tra gli identificatori della relazione. SQL permette così di specificare il vincolo Primary Key una sola volta per ogni tabella. Il vincolo Primary Key può essere definito direttamente su un singolo attributo, oppure essere definito elencando più attributi che costituiscono l’identificatore.
Vincoli Interrazionali
Per la gestione di questo tipo di vincoli si utilizza il foreign key (ovvero chiave esterna). Questo vincolo crea un legame tra i valori di un attributo della tabella corrente e i valori di un attributo di un’altra tabella che è in relazione. Il vincolo impone che per ogni riga della tabella il valore dell’attributo specificato se diverso dal valore nullo si a presente delle righe della tabella esterna tra i valori corrispondenti dell’attributo.
Sintassi del comando Create Table
Create table semplice: CREATE TABLE nome_tabella(nome_colonna1 tipo di dato constraint (opzionale), nome_colonna2 tipo di dato constraint (opzionale), nome_colonna3 tipo di dato constraint (opzionale), …nome_colonnaN tipo di dato constraint (opzionale) );
Create Table mediante select:
 CREATE TABLE nome_tabella
AS
SELECT …;

Alter Table
Il comando Alter Table ha la funzione di modificare la struttura della tabella.
L’operatore ADD consente di inserire una nuova colonna su una tabella esistente oppure di aggiungere delle Constraint alle colonne della tabella.
L’operatore MODIFY consente di cambiare il tipo di dato e/o la Constraint propri di ogni colonna di una tabella. L’operatore DROP consente di eliminare la Constraint dalla colonna.
Sintassi del comando Alter Table
Operatore Add
Inserimento di una nuova colonna:
 ALTER TABLE nome_tabella
ADD nome_colonna_nuova tipo di dato constraint;
Aggiunta di una chiave primaria (primary key):
 ALTER TABLE nome_tabella
ADD CONSTRAINT nome_tabella_pk PRIMARY KEY (nome_colonna che funge da chiave primaria);
Aggiunta di una chiave esterna (foreign key) ereditata da un’altra tabella:
 ALTER TABLE nome_tabella_figlia
ADD CONSTRAINT nome_tabella_figlia_fk FOREIGN KEY (nome_colonna che funge da chiave esterna sulla tabella figlia) REFERENCES nome_tabella_padre (nome_colonna che funge da chiave primaria sulla tabella padre);
Operatore Modify
Modifica del tipo di dato di una colonna:
ALTER TABLE nome_tabella
MODIFY nome_colonna tipo di dato nuovo;
Modifica della constraint di una colonna:
ALTER TABLE nome_tabella
MODIFY nome_colonna constraint nuova;
Operatore Drop
Eliminazione della chiave primaria:
ALTER TABLE nome_tabella
DROP PRIMARY KEY;
Eliminazione della chiave esterna:
ALTER TABLE nome_tabella_figlia
DROP CONSTRAINT nome_tabella_figlia_fk;
Drop Table
Il comando Drop Table consente di distruggere una tabella, eliminandola fisicamente dal database.
Come tutti i comandi DDL è un’operazione irreversibile, e provoca la perdita di tutti i dati contenuti nella tabellla.

Sintassi del comando Drop Table
DROP TABLE nome_tabella;

Rename
Il comando Rename consente di rinominare una tabella.
Sintassi del comando Rename
RENAME nome_tabella TO nuovo_nome_tabella;

DML (Data Manipulation Language) : manipolazione dei dati
DML fornisce i comandi per inserire, modificare, eliminare o leggere i dati all’interno delle tabelle di un database. La struttura di questi dati deve già essere stata definita tramite il DDL. Inoltre, il permesso di accedere a tali dati deve essere assegnato all’utente tramite il DCL.

Select
Col comando Select abbiamo la possibilità di estrarre i dati, in modo mirato, dal database.

Sintassi del comando Select
SELECT [ ALL | DISTINCT ] lista_elementi_selezione
FROM lista_riferimenti_tabella
[ WHERE espressione_condizionale ] [ ORDER BY ];
dove lista_elementi_selezione è l’elenco dei campi da estrarre, lista_riferimenti_tabella è l’elenco delle tabella da cui estrarre i dati, espressione_condizionale rappresenta l’elenco delle condizioni, ovvero dei desiderata dell’interrogazione lista_colonne è la colonna o le colonne che devono essere prese come riferimento per l’ordinamento dei dati in uscita.
Un esempio è il seguente:
SELECT cognome, nome, citta_residenza
FROM utenti
WHERE anni >= 18 ORDER BY citta_residenza
Questa query estrae l’elenco di tutti gli utenti maggiorenni ordinando l’output in base alla città di residenza. La definizione di select è comunque molto più ampia, prevede molte altre opzioni ma in linea di massima con queste opzioni si compongono la maggior parte delle interrogazioni.

Insert
Il comando Insert ha la funzione di inserire i dati nelle tabelle.
Le colonne (o campi) di destinazione dei valori possono essere o meno dichiarate nel comando. Se non vengono dichiarate, è necessario passare al comando un valore per ogni colonna della tabella, rispettando rigorosamente l’ordine delle colonne stesse. Se, invece, le colonne di destinazione vengono dichiarate, è possibile indicare le sole colonne per le quali vengono passati dei valori, purché vengano inseriti comunque i valori per tutte le colonne not null (che non possono essere nulle) della tabella.
Di per sé il comando insert opera inserendo in tabella una sola riga per volta. È possibile, però, inserire più di una riga “in modo automatico” passando all’insert una serie di righe (o tuple) risultanti da un comando di select, purchè tali righe siano compatibili con le colonne della tabella su cui si inseriscono i dati.

Sintassi del comando Insert
Insert semplice:
INSERT INTO nome_tabella
VALUES (elenco valori, tutti, rispettando l’ordine dei campi della tabella); oppure
INSERT INTO nome_tabella (elenco dei campi interessati dall’inserimento) VALUES (elenco valori, tutti, rispettando l’ordine dei campi dichiarati sopra);
Insert mediante Select:
INSERT INTO nome_tabella (elenco dei campi interessati dall’inserimento) SELECT …;
Update
Il comando Update ha la funzione di modificare i dati delle tabelle.
Il nome di ogni campo che deve essere modificato va dichiarato dopo la parola chiave SET e deve essere seguito dal simbolo ” = ” (uguale) e dal nuovo valore che deve assumere.
È possibile modificare più campi della stessa riga in un unico comando update, separandoli l’uno dall’altro con il simbolo ” , ” (virgola).
Il comando generico aggiorna tutte le righe della tabella. È possibile restringerne il numero applicando la parola chiave aggiuntiva WHERE, che permette di effettuare una selezione qualitativa delle righe imponendo delle condizioni sui dati presenti nelle righe prima dell’aggiornamento.

Sintassi del comando Update
Update generico:
UPDATE nome_tavola SET nome_campo1 = valore1_nuovo, nome_campo2 = valore2_nuovo, …;
Update con condizione:
UPDATE nome_tavola SET nome_campo1 = valore1_nuovo, nome_campo2 = valore2_nuovo WHERE nome_campo3 = valore;

Delete
Il comando Delete ha la funzione di cancellare i dati dalle tabelle.
Come il comando update anche delete può operare in modo generico cancellando tutte le righe della tabella oppure può identificare le righe da cancellare mediante la parola chiave aggiuntiva WHERE e la condizione (o le condizioni) ad essa associata.

Sintassi del comando Delete
Delete generico:
DELETE FROM nome_tabella;
Delete con condizione:
DELETE FROM nome_tabella WHERE nome_campo = valore;

Truncate Table
Come già detto, una Delete priva di clausola Where cancella tutte le righe dalla tabella specificata. Tuttavia, esiste un altro modo per svuotare completamente una tabella: il comando Truncate Table. Alcuni Dbms implementano questo comando in modo più veloce rispetto a una Delete, ad esempio cancellando il file dei dati senza leggerlo e ricreandolo vuoto. Generalmente però questo impedisce che vengano restituite alcune informazioni accessorie, come il numero di righe cancellate.

Sintassi del comando Truncate Table
TRUNCATE TABLE nome_tabella

La transazione
Le istruzioni di DML Insert, Update, Replace e Delete, se poste all’interno di una transazione, non sono irreversibili e possono essere annullate nel caso ci si renda conto che non sono state applicate in modo corretto.
Una transazione è un blocco di istruzioni che sono strettamente correlate tra loro. Nel caso si verifichi un errore (sia esso nel programma oppure dovuto a un problema esterno, per esempio se il sistema si blocca) non verrà annullata solo l’ultima istruzione, ma l’intera transazione. In tal modo si è certi che l’integregrità dei dati sarà garantita.

Begin Transaction
Per iniziare esplicitamente una transazione si ulitizza il comando BEGIN TRANSACTION. La mancanza di questo comando fa sì che ogni istruzione funga da transazione a se stante.

Sintassi del comando Begin Transaction
BEGIN TRAN[SACTION] [transaction_name];
Commit
Per confermare la transazione si utilizza il comando COMMIT. Con esso tutte le modifiche effettuate sui dati in precedenza vengono memorizzate.

Sintassi del comando Commit
COMMIT [TRAN[SACTION] [transaction_name];
Rollback
Per annullare la transazione si utilizza il comando ROLLBACK. Con esso tutte le modifiche effettuate sui dati in precedenza (a partire dall’inizio della transazione) vengono cancellate.

Sintassi del comando Rollback
ROLLBACK [TRAN[SACTION] [transaction_name];

DCL: Controllo sui dati
DCL serve a fornire o revocare agli utenti i permessi necessari per poter utilizzare i comandi DML e DDL, oltre agli stessi comandi DCL (che gli servono per poter a sua volta modificare i permessi su alcuni oggetti).
Grant
Il comando Grant fornisce uno o più permessi a un determinato utente (es: il permesso di inserimento in una tabella).

Revoke
Il comando Revoke revoca uno o più permessi a un determinato utente (es: il permesso di cancellazione da una tabella).

Esempi
Si immagini di avere la tabella di nome Personale

Se volessimo inserire il nome Michele Verdi di matricola 90.092, dovremmo scrivere:
INSERT INTO Personale (Nome, Cognome, Matricola) Values (’Michele’, ’Verdi’, 90092);

Se si volesse cambiare il livello contrattuale del dipendente Michele Verdi al quinto livello si dovrebbe scrivere il seguente comando:

UPDATE Personale
SET Livello=5
WHERE Matricola=’90092’;

Per cancellare il dipendente Michele Verdi si dovrebbe scrivere:

DELETE FROM Personale WHERE Matricola=90092;

Il comando Select estrae dei dati da una tabella. Il tipo di dato che deve essere estratto e la tabella dalla quale va estratto devono essere indicati nella sintassi della Select.
La particolarità della Select è che questo comando è in grado di estrarre solo i dati che soddisfano un certo criterio indicato nella richiesta.

Si prenda in considerazione la tabella di prima e si supponga che si chiami Allievi:

Si supponga di voler estrarre da questa tabella tutte le informazioni in essa contenute, praticamente di voler estrarre l’intera tabella. Si potrebbe scrivere la seguente interrogazione:
SELECT * FROM Allievi;
Il simbolo * sta, infatti, ad indicare di prelevare dalla tabella Allievi TUTTI i campi che la compongono e praticamente Nome, Cognome, Matricola.

Naturalmente anziché indicare * era possibile riscrivere tutti e tre i nomi dei campi.
Se invece si vuole prelevare un singolo campo basta inserire il nome del campo o dei campi, separati da virgole.
Si supponga di voler ora trovare il nome ed il cognome dell’allievo il cui numero di matricola è 90.390.
La richiesta, che prende il nome di query, deve essere formulata nel seguente modo:

SELECT Nome, Cognome FROM Allievi WHERE
Matricola=90390;

È importantissimo che per ogni campo che si vuole prelevare compaia dopo il FROM anche la tabella alla quale appartengono.
Una query di questo tipo restituisce il seguente risultato:

In questo caso la condizione è che il numero contenuto nel campo Matricola sia uguale a 90.390.

Nel caso in cui si fossero volute ricavare tutte le informazioni di una persona conoscendo soltanto il cognome si sarebbe scritto nel seguente modo:

SELECT Nome, Cognome, Matricola FROM Allievi WHERE
Cognome=‘Rossi’;
Il risultato sarebbe stato:

Nome Cognome Matricola Mario Rossi 90.390 Gli operatori logici possono essere utilizzati nei casi in cui si richiede che il risultato della query soddisfi più di una condizione, oppure almeno una tra tutte quelle indicate. Infatti dopo la clausola WHERE può essere inserita qualsiasi espressione che, risolta, dia come risultato uno dei due valori booleani, Vero o Falso.

In questo caso nel risultato della query verranno inserite tutte e solo le righe della tabella che rendono vera l’espressione che segue la clausola WHERE.
Se, per esempio, si volessero trovare le persone che si chiamano Mario nella seguente tabella:

si scriverebbe la seguente query:
SELECT Nome, Cognome, Matricola FROM Allievi WHERE Nome= ’Mario’;
Ed il risultato sarebbe:

Se invece si volessero le informazioni delle persone il cui nome è Mario ma la cui Matricola è maggiore di 90.000 realizzeremmo la seguente query:

SELECT Nome, Cognome, Matricola FROM Allievi WHERE Nome= ’Mario’ AND Matricola >90000

Ed il risultato sarebbe:

Si prenda in considerazione la seguente tabella:

Se si volesse ricavare il nome ed il cognome (e nient’altro) degli allievi nati dopo il 21/3/85 oppure prima del 10/01/1984 e la cui matricola è maggiore di 85000 si scriverebbe la seguente query:

SELECT Nome,Cognome FROM Allievi WHERE (Anno di nascita>21/3/85 OR Anno di nascita<10/01/1984) AND Matricola >85000;

Vi è la possibilità di aggiungere al comando Select due predicati aggiuntivi: ALL e DISTINCT.
Il primo indica che devono essere restituiti tutti i record che soddisfano le condizioni richieste. Questo predicato è di default, quindi non scrivendo nulla è sottointeso.
Il secondo, DISTINCT, indica di restituire solo una copia dei record che verrebbero restituiti in maniera duplicata.

Per comprendere questo secondo predicato si immagini di avere la tabella seguente:

Questi due dipendenti hanno lo stesso stipendio mensile. La seguente interrogazione:

SELECT Stipendio FROM Personale;

restituirebbe il seguente risultato:

La stessa cosa si ottiene con la seguente interrogazione:

SELECT ALL Stipendio FROM Personale;

Se si vuole evitare di ottenere risultati duplicati o multipli si deve aggiungere la clausola DISTINCT nel seguente modo:

SELECT DISTINCT Stipendio FROM Personale;

Ottenendo il seguente risultato:

Quando si effettua un’interrogazione ad un database, la tabella risultante possiede come intestazione delle colonne i nomi dei campi che sono stati messi nel costrutto SELECT.
Nel caso sopra, per esempio, la tabella possiede l’intestazione Stipendio che corrisponde esattamente al nome del campo che è stato prelevato.
Queste intestazioni possono essere cambiate grazie all’uso del costrutto As che consente di inserire ALIAS, la cui sintassi è:
SELECT {Campo} As Intestazione
FROM {Tabella 1}
WHERE {Condizione) AND|OR Condizione2 AND|OR …};

Per esempio:
SELECT DISTINCT Stipendio As STIPENDIO (Euro) FROM
Personale;
Restituisce:

Questo metodo è particolarmente utile nel calcolo di espressioni come nel seguente caso:
SELECT Nome, Cognome , Stipendio As Stipendio Lordo, Stipendio*0,75 As Stipendio Netto From Personale;

II comando ORDER BY
Si tratta di una clausola aggiuntiva che si posiziona dopo la clausola WHERE. La sintassi è la seguente:

ORDER BY {Campo1, Campo2, …} [ASC|DESC];

Questo comando è utile ogniqualvolta si vogliono ottenere i risultati di una query in maniera ordinata.
È infatti possibile indicare in base a quale campo o quali campi deve essere effettuato l’ordinamento e se in maniera crescente o decrescente. Nel caso in cui i campi siano più di uno l’ordinamento è eseguito rispetto al primo ed in caso di record, con il campo in questione, uguali si considera il secondo indicato nella clausola ORDER BY.
Infine le parole chiave per indicare il senso dell’ordinamento sono sempre ASC, il cui significato è ASCENDENTE e DESC, il cui significato è DISCENDENTE.

Supponiamo di voler solamente ordinare l’intera tabella Matricole crescenti, senza escludere alcun record.

La sintassi è:
SELECT * FROM Allievi ORDER BY Matricola

Il risultato è:

La clausola GROUP BY
Questa clausola rappresenta un potente mezzo per raggruppare i dati che vengono recuperati dall’interrogazione di un database. Oltre a raggruppare i dati secondo alcuni criteri, è possibile eseguire delle semplici, ma molto spesso utili, operazioni sui gruppi di dati.
Si pensi di avere la tabella di un database contenente le ore lavorative dei dipendenti di un’azienda e di voler calcolare, per esempio, la somma di tutte le ore lavorative di un particolare dipendente in un mese. Per un’operazione di questo tipo servirebbe raggruppare i dati di quel solo dipendente ed effettuare la somma delle ore di ogni giornata. La clausola GROUP BY mette a disposizione le seguenti parole chiave:
AVG
SUM
MIN
MAX
COUNT
Queste funzioni rispettivamente servono per eseguire la media di un certo raggruppamento di dati, la somma, trovare il minimo, il massimo e il numero di elementi presenti nel raggruppamento. La sintassi è la seguente:

SELECT campi_da_estrarre FROM Tabella GROUP BY Campo_in_base_al_quale_raggruppare

Analizzando la tabella, se si volesse sapere il numero di persone impegnate su ogni progetto sarebbe necessario raggruppare secondo il campo Progetto e contare il numero di record restituiti. Per farlo è possibile inviare la seguente Query:

SELECT Progetto, Count(*) FROM Tabella_Progetti GROUP BY Progetto;

Con queste funzioni sono molto utili gli Alias, ovvero quei nomi simbolici che vengono associati ai risultati delle operazioni effettuate sui gruppi di dati.

SELECT Progetto, Count(*) AS Somma FROM Tabella_Progetti GROUP BY Progetto

In questo modo dalla tabella risultante si ottiene:

Se nella tabella Tabella_Progetti ci fosse per ogni dipendente un campo ORE _assegnate, ad indicare quante ore sono assegnate a quel dipendente su ogni progetto e si immaginasse quindi che ogni dipendente possa essere utilizzato su più progetti si avrebbe la tabella seguente:

Per sapere il totale delle ore di lavoro dei dipendenti e la media delle ore lavorative per ogni progetto bisognerebbe scrivere:
SELECT Matricola_dipendente, SUM(ORE_assegnate) As TOT, AVG(ORE_assegnate) As MEDIA FROM Tabella_progetti GROUP BY Matricola_dipendente;

Anche il GROUP BY consente di impostare delle condizioni sul risultato della query. Praticamente si possono impostare delle condizioni simili a quelle del WHERE ma che hanno effetto sui raggruppamenti.
Questo è possibile grazie alla clausola HAVING che può essere presente soltanto in collaborazione con GROUP BY.
Si ritorni al primo esempio e si pensi, per statistica, di essere interessati ai soli dipendenti che lavorano su almeno due progetti.
La query è la seguente:

SELECT Matricola_dipendente, FROM Tabella_Progetti GROUP BY Matricola_dipendenti HAVING Count (*)>1;

Fino ad ora si è sempre lavorato con una singola tabella. Nelle vere basi di dati le tabelle sono molteplici ed ora impareremo a capire come prelevare dati che possono trovarsi su più tabelle.
Ogni tabella è costituita da un certo numero di campi, e naturalmente, da un certo numero di righe. Tra i campi deve esistere un campo particolare, la chiave primaria, ovvero quel campo che non può nella stessa tabella possedere valori ripetuti.
Si immagini un semplice database di una scuola costituito dalla tabella.
In questa tabella la chiave primaria è il campo Matricola, infatti questo campo non presenta alcun valore ripetuto più di una volta in nessuna riga della tabella.

Nel database in questione si vuole tenere traccia delle assenze di ogni allievo grazie alla tabella:

In questa tabella la chiave primaria è rappresentata da ID_assenza. Infatti matricola non potrebbe più esserlo in quanto presenta valori ripetuti anche diverse volte (per esempio 90.390).
Quello che bisogna iniziare a comprendere è come in questa tabella, grazie al campo matricola, si potrebbero recuperare le informazioni relative ai nomi e cognomi degli allievi.

Realizzeremo ora una query che visualizza il nome, il cognome degli allievi che hanno assenze con l’elenco di tutte le assenze:
SELECT Nome,Cognome,Giorno FROM Allievi, Assenze WHERE Allievi.Matricola=Assenze.Matricola;

Bisogna notare come avviene il collegamento tra le due tabelle:

WHERE Allievi.Matricola=Assenze.Matricola;

Questa sintassi non era ancora stata vista. Allievi.Matricola indica il campo Matricola appartenente alla tabella Allievi. Lo stesso discorso vale per Assenze.Matricola.
Infatti questo campo è l’unico che consente di collegare le due tabelle tra loro.
Rendiamo leggermente più complicato l’esempio aggiungendo l’informazione sulla classe degli allievi.

Volendo aggiungere alla query di prima l’informazione sulla classe di appartenenza si cambierebbe la query di prima nel seguente modo:

SELECT Nome, Cognome, Classe, Giorno FROM Allievi, Assenze, Classi
WHERE Allievi.Matricola=Assenze.Matricola
 AND
Allievi.Matricola=Classi.Matricola;

L’operatore LIKE
Tra i vari operatori analizzati ne esiste uno che si utilizza in combinazione con il comando Select e che consente di effettuare ricerche particolari. La sintassi è la seguente:

…WHERE Nome_Campo LIKE ’CARATTERI JOLLY’

dove CARATTERI JOLLY possono essere i seguenti:

* che sta ad indicare zero o più caratteri qualsiasi (in ACCESS);
% che sta ad indicare zero o più caratteri qualsiasi (in SQL generico);
? che sta ad indicare uno e un solo carattere qualsiasi (in ACCESS).
_che sta ad indicare uno e un solo carattere qualsiasi (in SQL generico).

Quindi scrivere:
SELECT Nome FROM tabella WHERE Nome LIKE ’*tt*’

Effettua l’estrazione di tutti i record nel cui campo Nome esiste la coppia tt.

Se si fosse scritto:
SELECT Nome FROM tabella WHERE Nome LIKE ’a*’

Sarebbero stati presi i nomi che iniziano per la lettera a. Scrivendo:

SELECT Nome FROM tabella WHERE Nome LIKE ’Mari?’;

Sarebbero stati presi tutti i record contenenti i nomi Mario o Maria.
Questo perché il simbolo ? indica uno ed un solo carattere qualsiasi. Naturalmente se ci fosse stato un errore di battitura ed un nome MariK questo sarebbe stato prelevato .

L’operatore IN
Questo particolare operatore fa in modo che la Select effettui la ricerca dei record tra un insieme predefinito di valori.
La seguente interrogazione:

SELECT Matricola FROM Personale WHERE Livello IN (5,6);

In questo modo si ricercano le matricole di tutti i dipendenti che appartengono al 5 e al 6 livello.

L’operatore BETWEEN
Permette di effettuare le ricerche in base ad un intervallo di valori.

SELECT Matricola FROM Personale WHERE Livello BETWEEN 5 AND 6;

La sintassi IS NULL
Serve per prelevare o escludere i record che contengono campi con valore NULL.
Si supponga che la tabella Personale possa contenere anche i nominativi di alcuni stagisti che lavorano periodicamente nell’azienda. Per questi nominativi non esiste la voce stipendio che viene lasciato al valore NULL.

SELECT Nome,Cognome FROM Personale WHERE Stipendio IS NULL;

Questa potrebbe essere una query per individuare i nomi degli stagisti dell’azienda.
È valida anche la sintassi NOT NULL.

Il linguaggio SQL e le operazioni di sicurezza
Il linguaggio SQL non trascura uno degli aspetti più importanti nella progettazione di una base di dati: la sicurezza.
Per sicurezza di una base di dati si intende la possibilità di fare in modo che soltanto gli utenti autorizzati possano accedere a determinate informazioni contenute nella base di dati. Esiste un comando, Grant che consente di decidere chi ha il permesso di accedere alle tabelle della base di dati e di che tipo sia questo permesso.

Infatti è possibile stabilire cosa l’utente può fare sulla tabella a cui sta cercando di accedere. Le opzioni sono le seguenti:

ALTER: consente di aggiungere, eliminare o semplicemente modificare i dati della tabella. DELETE: consente l’eliminazione di righe dalla tabella.
INDEX: consente la creazione di indici.
INSERT: consente l’inserimento di nuove righe nella tabella.
SELECT: consente di realizzare interrogazioni sulla tabella.
 UPDATE: consente di aggiornare i valori della tabella.
ALL: concede il pieno controllo della tabella.

Se si volesse concedere all’utente Utente1 il permesso di eliminare, aggiungere o modificare i dati della tabella Tabella_Uno si dovrebbe scrivere:

GRANT ALTER
ON Tabella_Uno
TO Utente1;

Nel caso in cui lo si volesse revocare si scriverebbe:

REVOKE ALTER
ON Tabella_Uno
FROM Utente1;

È molto importante notare che nel caso di Select e Update è possibile indicare quali siano i nomi delle colonne della tabella sui quali l’utente può effettuare le operazioni che gli stiamo concedendo.
Queste colonne vanno indicate tra parentesi, nel seguente modo:

GRANT UPDATE (Nome_Colonna1, Nome_Colonna2)
ON Tabella_Uno
TO Utentel;

Interrogazioni complesse
Il comando Select può essere utilizzato in maniera più complessa rispetto ai casi visti fino ad ora. I casi che vedremo consentono di realizzare interrogazioni particolarmente efficaci, anche se leggermente più complesse.

Modifiche nella clausola WHERE
È possibile confrontare un campo anziché direttamente con un valore, con il risultato di una Select, che a sua volta restituisce un valore.
Un classico esempio di interrogazione in grado di restituire un valore è dato dalle funzioni di aggregazione che, utilizzate assieme alle clausole MAX, MIN, AVG, restituiscono proprio un valore.

ESEMPIO
Si immagini la seguente tabella Banca contenente le informazioni riguardo gli estratti conto dei clienti di una banca.

Se volessimo scoprire quali clienti possiedono un conto maggiore della media dei clienti della banca si potrebbe scrivere:

SELECT Nome, Cognome FROM Banca WHERE EstrattoConto >= (SELECT AVG (EstrattoConto) FROM Banca);

In ordine viene risolta l’interrogazione contenuta nella parentesi, una volta ottenuto il valore della media degli estratti conti, viene inserito nella prima Select e viene risolta anche la seconda.

La parola chiave ANY
Questa parola chiave permette di effettuare un confronto tra un campo ed uno qualsiasi dei valori restituiti da una seconda query. Nel caso dell’esempio precedente si immagini di voler elencare tutti i clienti della banca tranne il cliente con l’estratto conto di valore maggiore.
Se si esamina questa interrogazione ci si accorge che potrebbe essere interpretata anche nel seguente modo:
«Cercare i nomi ed i cognomi dei clienti della banca che hanno un estratto conto minore di almeno uno dei clienti della banca stessa.»
In questo modo, se esiste un cliente che ha lo stipendio minore di almeno un altro cliente significa che non può essere sicuramente il cliente con l’estratto conto maggiore. L’interrogazione è la seguente:

SELECT Nome, Cognome FROM Banca WHERE EstrattoConto < ANY (SELECT EstrattoConto FROM Banca);

La parola chiave ALL
Permette di confrontare un campo con i risultati di una seconda Select e di restituire il risultato soltanto se tutti i confronti danno esito positivo.
Se, per esempio, si cercasse il cliente (o i clienti in caso di estratto conto uguale) con estratto conto maggiore tra tutti si potrebbe scrivere:

SELECT Nome, Cognome FROM Banca WHERE EstrattoConto >= ALL (SELECT EstrattoConto FROM Banca);

La parola chiave IN
Con questa parola chiave è possibile fare in modo che vengano considerati solo i campi che appartengono ad un certo insieme restituito da una seconda Select. Le due tabelle indicano, rispettivamente, l’estratto conto dei clienti di una banca e il giorno di apertura del conto e l’agenzia in cui è stato aperto.

Se volessimo i nomi delle persone che possiedono più di un conto potremmo scrivere:

SELECT Nome, Cognome FROM Banca WHERE ID_Cliente IN (SELECT ID_Cliente FROM Conti GROUP BY ID_Cliente HAVING Count (*) >1);

La parola chiave EXISTS
Con questa parola chiave è possibile restituire dei risultati se il risultato di una Select restituisce almeno una riga.
Se, per esempio, si volessero i Nomi ed i Cognomi dei clienti della banca soltanto se esiste almeno un cliente con Estratto conto superiore a 1.200 Euro si dovrebbe scrivere:

SELECT Nome, Cognome FROM Banca WHERE EXISTS (SELECT * FROM Banca WHERE EstrattoConto >1200);

Quando compare la clausola Exists la Select che segue contiene sempre il carattere jolly *. Infatti con Exists non interessa il risultato della Select, interessa soltanto se esiste almeno una riga di risultato o meno.

La negazione di EXISTS e IN
Queste due parole chiave possono essere precedute dalla parola chiave NOT.
Nel primo caso il risultato è restituito se e solo se la Select dentro il costrutto Exists non restituisce alcun risultato, nel secondo caso la condizione del Where è verificata se il valore del campo non appartiene a quelli presenti nel risultato della Select annidata.

{/gspeech} 

Valutazione della capacità di applicazione ed approfondimento
Si immagini di avere le seguenti tabelle:
Libro(codice_libro, autore, titolo)
Lettore(codicelettore, nome, cognome)
Prestito(codice_lettore, codice libro, data_prestito).
Si scriva l’interrogazione SQL per:
ottenere i codici dei lettori che hanno preso in prestito almeno un libro;
ottenere i nomi ed i cognomi delle persone che hanno preso in prestito almeno un libro;
ottenere i nomi ed i cognomi delle persone che hanno preso in prestito almeno un libro a partire dal 1/12/2001;
ottenere i titoli dei libri presi in prestito da almeno una persona; ottenere tutti i titoli dei libri disponibili;
ottenere i titoli dei libri con le rispettive persone che li hanno presi in prestito; ottenere i titoli dei libri presi in prestito il giorno 4/3/2000;
ottenere gli autori dei libri presi a prestito dal signor Andrea Bianchi;
ottenere i nomi dei lettori che hanno preso a prestito libri di Svevo, ma non di Manzoni;
ottenere quanti libri diversi di Svevo ha preso in prestito Andrea Bianchi.

Un database per la gestione di un jukebox musicale contiene le seguenti tabelle utili per visualizzare un certo numero di informazioni sulle canzoni selezionate:
CD(NroSerie,TitoloAlbum,Anno,Prezzo)
Contiene (NroSerieDisco ,CodiceReg,NroProg)
Esecuzione (CodiceReg,TitoloCanz,Anno,NomeCantante) Autore (Nome,TitoloCanzone)
Si scrivano le istruzioni SQL utili per effettuare le seguenti richieste.
a) I cantautori il cui nome inizia per `A’.
b) I titoli dei dischi che contengono canzoni di cui non si conosce l’anno di registrazione.
c)I pezzi del disco con numero di serie 9883, ordinati per numero.

Esercizi

  1. Per creare una tabella si utilizza il comando:
  • CREATE TABLE
  • DROP TABLE
  • SELECT
  • Nessuna delle precedenti
  1. Per cancellare una tabella si utilizza il comando:
  • DROP TABLE
  • DELETE TABLE
  • DELETE
  • SELECT
  1. Per cambiare i valori inseriti in una tabella si utilizza il comando:
  • UPDATE
  • CREATE TABLE
  • DELETE
  • ALTER TABLE
  1. Per inserire dei valori in una tabella si utilizza il comando:
  • UPDATE
  • INSERT
  • DELETE
  • ALTER TABLE
  1. Per effettuare ricerche in una tabella si utilizza il comando:
  • UPDATE
  • INSERT
  • DELETE
  • SELECT
  1. La clausola FROM indica:
  • La tabella da cui si prelevano le informazioni.
  • Le informazioni da prelevare.
  • Non esiste.
  • Che sto prelevando dei dati.
  1. La clausola WHERE serve per:
  • filtrare i dati prelevati in base ad alcune condizioni.
  • Non esiste.
  • Indica la tabella da cui si devono prelevare i dati.
  • Nessuna delle precedenti.
  1. Nel caso in cui volessimo prelevare dei dati in base a più condizioni dovremmo:
  • Non è possibile.
  • Utilizzare la clausola WHERE con degli operatori logici (AND, OR, ecc).
  • Utilizzare molti operatori WHERE.
  • Utilizzare molte SELECT.
  1. Gli ALIAS nelle interrogazioni SQL si introducono grazie alla parola chiave:
  • EXISTS
  • AS
  • Non esistono
  • SELECT 
  1. Per ordinare i risultati di una query SQL si utilizza la clausola: 
  • ORDER BY
  • WHERE
  • IN
  • Nessuna delle precedenti
  1. GROUP BY consente di:
  • Di ordinare i dati di una tabella.
  • Di visualizzare il risultato in maniera descrescente.
  • Di raggruppare la tabella risultato secondo alcuni campi scelti.
  • Non esiste.
  1. La funzione COUNT() consente:
  • Di sommare alcuni valori all’interno di una tabella.
  • Non esiste.
  • Di conteggiare il numero di righe di una tabella.
  • Nessuna delle precedenti.
  1. 13. L’operatore LIKE consente di:
  • * Confrontare tra loro campi di tipo diverso.
    * Confrontare tra loro date.
    * Non esiste.
    * Effettuare ricerche su campi utilizzando caratteri jolly.
  1. L’operatore IN consente di:
  • Effettuare il confronto tra un insieme specificato di elementi.
  • Non esiste.
  • Semplificare la sintassi della SELECT.
  • Sostituisce il comando SELECT.
  1. Per gestire gli accessi al database da parte di diversi utenti e con permessi diversi si utilizza il comando:
  • Select
  • IN
  • EXISTS
  • Grant
  1. La clausola EXISTS permette di verificare se:
  • Il risultato di una SELECT restituisce almeno una riga.
  • Il risultato di una SELECT restituisce un valore specifico.
  • Il risultato di una SELECT è valido.
  • Nessuno dei precedenti.

Commento all'articolo