Linguaggio SQL per la Digital Analytics

Il linguaggio SQL è un linguaggio per l’interrogazione e l’analisi di grandi quantità di dati, presenti in numerosi database (come MySQL o SQL Server) e nell’ecosistema Google Cloud (su prodotti come Big Query). Oltre che per l’interrogazione dei dati, può essere utilizzato per la definizione e la manipolazione dei dati.

In questo articolo, effettuiamo una breve presentazione del linguaggio SQL e forniamo un’appendice utile per tutti coloro che vogliono utilizzare questo linguaggio anche ai fini della digital analytics.

Cos’è il linguaggio SQL

SQL (acronimo di Structured Query Language) è un linguaggio standard utilizzato per interrogare, gestire e analizzare i dati contenuti in diversi database.

Un database è una struttura informativa che contiene diversi insiemi di dati, che possono essere raccolti direttamente o tramite i sistemi informativi: ad esempio, un software CRM, per poter funzionare, deve memorizzare i dati raccolti in una base dati (un database appunto).

La gestione del database avviene tramite il DBMS (DataBase Management System) ovvero un apposito sistema di gestione che fornisce meccanismi di accesso ai dati, assicurandone affidabilità e persistenza.

Il DBMS permette di accedere e gestire i dati memorizzati nel database tramite il linguaggio SQL. Pur esistendo leggere differenze tra i vari sistemi DBMS in commercio, la sintassi e i concetti fondamentali rimangono in gran parte comuni, rendendo SQL uno dei linguaggi più usati nell’analisi dei dati.

Grazie a SQL è possibile estrarre, applicare filtri, eseguire calcoli, raggruppare e creare viste da cui ricavare informazioni utili.

Database, tabelle e SQL: i concetti fondamentali

Un database è un sistema informatico che permette di archiviare, organizzare e interrogare dati.

Che si tratti di un sistema transazionale come Microsoft SQL Server o di una piattaforma analitica come Google BigQuery, l’obiettivo dell’analisi dei dati è sempre lo stesso: trasformare grandi quantità di dati in informazioni utili.

A tal fine, per interrogare i dati si utilizza il linguaggio SQL, ovvero un linguaggio standard per la ricerca, il filtraggio, l’aggregazione e l’analisi delle informazioni contenute nelle tabelle del database.

Nella maggior parte dei casi, infatti, i dati sono organizzati in tabelle ovvero in strutture composte da righe e colonne; ad esempio, una tabella clienti potrebbe contenere:

Ogni riga rappresenta un elemento registrato (in questo caso un cliente), mentre ogni colonna rappresenta una caratteristica o informazione ad esso associata.

Database, DBMS e Data Warehouse

Nella sua accezione più corretta, il termine database indica l’archivio vero e proprio, ovvero il file fisico sul quale vengono memorizzati i dati; tuttavia, nella prassi comune si estende l’utilizzo di questo termine come sinonimo di DBMS.

Esistono diverse tipologie di DBMS.

Tra le più diffuse, i RDBMS (acronimo di Relational DataBase Management System) sono sistemi di gestione di database relazionali, dove le tabelle sono generalmente progettate per essere collegate tra loro tramite relazioni logiche. Esempi di database relazionali sono MySQL, PostgreSQL e Microsoft SQL Server.

Un’altra categoria è rappresentata dai Data Warehouse (DW) progettati per raccogliere, organizzare e analizzare grandi volumi di dati provenienti da più fonti diverse. I DW sono quindi sistemi centralizzati che aggregano dati storici estratti e collezionati da più database, per rispondere a diversi obiettivi di analisi; per privilegiare velocità di elaborazione e scalabilità, le tabelle non sono relazionate tramite chiavi, ma possono essere comunque interrogate utilizzando SQL. Un esempio è BigQuery, progettato per analizzare grandi volumi di dati nel cloud.

Concetti fondamentali

Introduciamo le seguenti definizioni di base:

Risposte ad alcune domande frequenti

Cenni di Linguaggio SQL

SQL è un linguaggio di gestione e manipolazione di database che esprime le interrogazioni e gli aggiornamenti in modo dichiarativo, ovvero specificando l’obiettivo dell’operazione e non il modo in cui ottenerlo.

Il linguaggio SQL viene messo a disposizione da qualsiasi DBMS (Database Management System) e comprende un numero di istruzioni che ne permette la programmazione a due livelli: sia a livello di struttura dati, sia a livello di manipolazione dei dati in essa contenuti.

Si parla di:

• DDL – Data Definition Language: istruzioni per modificare lo schema di una base dati (Data Dictionary), definendo relazioni e vincoli di integrità (questi ultimi se applicato ai RDBMS, ovvero ai database relazionali)
• DML – Data Manipulation Language: istruzioni per modificare l’istanza della base dati, utilizzando operatori dell’algebra relazionale.

Comandi più usati e notazioni utilizzate

Nella categoria DML sono inclusi i comandi per interrogare e modificare i dati presenti nelle tabelle; tra i più usati:

Nell’ambito dell’analisi dei dati, il comando più utilizzato è SELECT, che permette di interrogare il database e ottenere le informazioni desiderate attraverso filtri, ordinamenti, raggruppamenti ed estrazioni da più tabelle.

Nelle proseguo dell’articolo ci concentreremo su questo comando, con lo scopo di introdurre il lettore alla comprensione del significato delle varie parti in cui si compone un’istruzione SQL e all’acquisizione delle corrette regole di sintassi per la costruzione di interrogazioni di un database.

Per descrivere la sintassi dei comandi del linguaggio SQL, verrà usata una notazione che fa uso di alcuni simboli che hanno questo significato:

Estrazione dei dati: l’espressione SELECT

L’espressione SELECT consente di specificare un’operazione di interrogazione in SQL.

La sintassi di base è la seguente:

SELECT NomeTabella.Campo

FROM NomeTabella

WHERE ...

L’effetto di questa istruzione è quello di estrarre i dati presenti in una o più tabelle, in base ai vincoli impostati nell’espressione.

Viene formalmente definita come il prodotto cartesiano delle tabelle elencate nella clausola FROM (o proiezione del cartesiano, potendo selezionare solo alcuni campi e non necessariamente tutti).

Clausola FROM

L’istruzione SELECT seleziona, tra le righe che appartengono alla concatenazione delle righe delle tabelle elencate nella clausola FROM, quelle che soddisfano le condizioni espresse nella clausola WHERE: se la clausola WHERE è assente, vengono selezionate tutte le righe.

Esempio di applicazione SELECT:
Supponiamo che esista una tabella Articoli contenente questi valori:

Ogni riga rappresenta un articolo di vendita, con i relativi dati di base.

Ipotizziamo la seguenti interrogazione:

Select Descrizione from Articoli

La sua esecuzione produce come risultato la seguente tabella, ovvero un elenco delle descrizioni di tutti gli articoli presenti:

Se volessimo estrarre anche il nome dell’articolo, scriveremo:

Select Art, Descrizione from Articoli

La sua esecuzione produce come risultato la seguente tabella:

Se volessimo estrarre l’intero contenuto  della tabella Articoli, scriveremo:

Select * from Articoli

La sua esecuzione produce come risultato la tabella originale:

Clausola WHERE

Grazie alla clausola WHERE è possibile costruire interrogazioni che restituiscono un insieme di righe che soddisfano la condizione espressa nella clausola stessa.

Viene formalmente definito l’operatore di selezione: le tuple risultato della query saranno estratte in base a questo parametro.

La clausola WHERE può avere come argomento un predicato semplice oppure un’espressione booleana costruita combinando gli operatori AND, OR, NOT agli operatori relazionali e speciali (descritte nel seguito, nel paragrafo dedicato).

Nella clausola WHERE è possibile anche specificare il predicato di JOIN tramite uguaglianza diretta tra due campi: la condizione di JOIN prevede altre diverse sintassi (anch’esse descritte nel seguito, nel paragrafo dedicato).

Esempio di applicazione WHERE:

Se volessimo estrarre solo gli articoli che hanno come Unità di misura ‘Pz’ (pezzi) , aggiungeremo la clausola where per indicare il filtro; scriveremo:

Select Art, Descrizione from  Articoli where UM=’Pz’

La sua esecuzione produce la seguente tabella come risultato:

Ipotizziamo ora di dover estrarre i dati da più tabelle.
Ipotizziamo che esista una tabella fatture, collegata a Clienti e Articoli.

Ogni riga rappresenta una fattura (numero fattura), emessa ad un cliente (indicato come codice cliente), con una riga relativa ad un articolo di magazzino (codice articolo), con i relativi prezzi unitari applicati in fattura e le quantità vendute.

Esempio di applicazione WHERE COME JOIN:

Se volessimo estrarre tutti i dati presenti nella tabella, sostituendo però alle colonne Cliente e Articolo il nome del rispettivo elemento invece del codice,  ripreso dalle tabelle esterne, scriveremo:

Select Fatture.NumFatt, Fatture.DataFatt, Clienti.NomeCliente, Articoli.Art, Fatture.Prezzo, Fatture.Qta

from  Clienti, Articoli, Fatture

where Fatture.Cliente = Clienti.NomeCliente and Fatture.Articolo = Articoli.Art

Nota: la condizione espressa nel WHERE può essere resa – in modo formalmente più elegante – mediante l’uso della JOIN, che andremo a introdurre nel seguito.

La sua esecuzione produce la seguente tabella come risultato:

Clausole della SELECT

Una query SQL è composta da diverse clausole (clauses), ciascuna delle quali svolge una funzione specifica nell’estrazione, nel filtraggio, nel raggruppamento e nell’ordinamento dei dati:

Il risultato dell’esecuzione di una interrogazione SQL è una tabella con una riga per ogni riga selezionata e un insieme di colonne pari ai campi elencati nella clausola SELECT, dove:

• I campi (o il campo) da estrarre può essere scritto come tabella.campo oppure semplicemente come campo se nella clausola FROM è presente una sola tabella o se i nomi dei campi non si ripetono in tabelle diverse (regola valida anche per la clausola WHERE).
• Il carattere * rappresenta la selezione di tutti i campi delle tabelle elencate nella clausola FROM.

Per evitare duplicati nel risultato di una interrogazione (quindi per evitare righe con gli stessi valori per tutti i campi), è sufficiente specificare la parola chiave DISTINCT immediatamente dopo la parola chiave SELECT.

Se non si specifica nulla, viene assunta per default la parola chiave ALL e, in questo caso, il risultato della query può contenere anche righe duplicate.

Con la parola chiave TOP N è possibile limitare il numero di record restituiti.

Con TOP N PERCENT è invece possibile limitare il risultato a una determinata percentuale dei record estratti.

Rinomina di campi e tabelle con un ALIAS

È possibile rinominare i campi o le tabelle nell’output finale mediante un alias, ovvero un nome alternativo.

Questo meccanismo consente sia di assegnare ai campi nomi più significativi, sia di far comparire la stessa tabella più volte nella clausola FROM con alias differenti, superando il limite che consentirebbe di indicare una tabella una sola volta.

SELECT NomeTabella.Campo AS [Alias]
      [, NomeTabella.Campo AS [Alias]] ...

FROM NomeTabella [Alias]
     [, NomeTabella [Alias]] ...

WHERE ...

Formalmente, la query che rinomina le tabelle della clausola FROM mediante alias costruisce il prodotto cartesiano delle tabelle indicate nella clausola stessa; il risultato così ottenuto proietta soltanto le colonne specificate nella clausola SELECT, rinominando quelle per le quali è stato definito un alias.

La sintassi è leggermente diversa a seconda che si desideri rinominare un campo oppure una tabella.

Per effettuare l’alias di un campo, l’assegnazione avviene utilizzando la parola chiave AS seguita dal nome da associare come alias. Tale nome deve essere racchiuso tra parentesi quadre [ ] solo nel caso in cui contenga spazi o caratteri speciali (ad esempio *, ?, ecc.). Sempre nel caso in cui l’alias contenga spazi, le parentesi quadre dovranno essere utilizzate anche in tutte le altre parti della query.

Esempio di applicazione ALIAS:

Select Art As [Nome Articolo], Descrizione from Articoli

La sua esecuzione produce come risultato la seguente tabella:

Per effettuare l’alias di una tabella, l’assegnazione avviene indicando direttamente il nome dell’alias tra parentesi quadre [ ]. Quando viene assegnato un alias a una tabella, tale nome deve necessariamente sostituire il nome originale in tutte le parti della query (SELECT, WHERE, ORDER BY, GROUP BY, ecc.).

Nella pratica, si effettua l’alias di una tabella in query solitamente complesse o con tabelle con nomi tecnici, per semplificarne la lettura, sostituendo al nome tecnico un nome più leggibile: ad esempio, la tabella ‘A5072″ contiene le fatture, ne sostituisco il nome con “Fatture” usando la notazione “A5072 [Fatture]”.

Operatori aritmetici: +, -, * e /

Mediante il costrutto SELECT è possibile calcolare valori derivanti da espressioni aritmetiche costruite a partire dai valori assunti da uno o più campi delle tuple nell’output ottenuto nella clausola FROM.

Le espressioni sono formulate applicando gli operatori aritmetici +, -, * e / ai valori assunti nei campi. Il risultato è l’ottenimento di tabelle nelle quali uno o più campi sono calcolati a partire da quelli presenti nella tabella di origine.

Questi campi sono nuovi e non appartengono a nessuna delle tabelle elencate nella clausola FROM.

SELECT Espressione [AS Alias]
      [, Espressione [AS Alias]] ...

FROM NomeTabella [AS Alias]
     [, NomeTabella [AS Alias]] ...

WHERE ...

Il risultato è l’ottenimento di nuove colonne derivate da quelle originali attraverso una proiezione dei dati.

All’interno del costrutto, il termine Espressione [AS Alias] può essere di due tipi:

• NomeTabella.Campo
• Espressione aritmetica in cui gli operandi sono numeri oppure elementi del tipo NomeTabella.Campo.

A ogni espressione deve essere associato un Alias, che identificherà il risultato nella query finale.

Un’espressione aritmetica utilizzata nella clausola SELECT genera una colonna virtuale, non presente nella tabella di origine. Tale colonna non viene memorizzata fisicamente nel database, ma viene materializzata esclusivamente come risultato dell’interrogazione. Nel calcolo delle espressioni aritmetiche, la presenza di un valore nullo (NULL) rende l’espressione indefinita.

Esempio di applicazione OPERATORE *:

Select Fatture.NumFatt, Fatture.DataFatt, Clienti.NomeCliente, Articoli.Art, (Fatture.Prezzo*Fatture.Qta) AS [Importo Fattura], Fatture.Qta

from  Clienti, Articoli, Fatture

where Fatture.Cliente = Clienti.NomeCliente and Fatture.Articolo = Articoli.Art

Nota: la condizione espressa nel WHERE può essere resa – in modo formalmente più elegante – mediante l’uso della JOIN, che andremo a introdurre nel seguito.

La sua esecuzione produce la seguente tabella come risultato (la stessa vista in precedenza):

Operatori di aggregazione: COUNT, MIN, MAX, SUM, AVG

Le funzioni di aggregazione consentono di valutare delle condizioni su insiemi di record, utilizzano i seguenti operatori aggregati:

Gli operatori aggregati possono essere uniti alle parole chiave:

A tutti questi operatori, segue l’uso della parentesi:

SELECT
    <operatore> ([DISTINCT | ALL] Campo o Espressione) AS [Alias],
    <operatore> ([DISTINCT | ALL] Campo o Espressione) AS [Alias]

La clausola SELECT può essere seguita da un numero arbitrario di operatori aggregati.

Le parentesi ammettono come argomento un campo oppure un’espressione.

La query costruisce il prodotto cartesiano delle tabelle nella clausola FROM; se la clausola WHERE è presente seleziona solo le tuple che ne soddisfano il predicato, proietta le colonne della tabella che compaiono nella formula, calcola il valore dell’espressione per ogni tupla ed esegue quindi l’aggregazione considerando solo i valori distinti (DISTINCT) oppure tutti (ALL).

Considerando che hanno tutti la medesima sintassi, vediamo più in dettaglio il COUNT.

Il COUNT conta il numero dei record estratti in una tabella, considerando tutte le condizioni impostate; usa la sintassi:

SELECT COUNT ( ALL <NomeTabella.Campo> )
oppure
SELECT COUNT ( < Campo1 & Campo2 ... > )

La query costruisce il prodotto cartesiano delle tabelle nella clausola FROM: se la clausola WHERE è presente, seleziona solo le tuple che ne soddisfano il predicato; proietta la colonna specificata in corrispondenza di SELECT e restituisce il numero di tuple non NULL che la compongono.

Tra parentesi è possibile indicare:

• (*) restituisce il numero totale di righe (incluse quelle che contengono valori nulli).
• Un insieme di campi (<Campo1 & Campo2 …>): in questo caso COUNT conteggia una riga solo se almeno uno dei campi non è nullo; se tutti i campi indicati sono nulli, la riga non viene conteggiata.

Utilizzando COUNT con DISTINCT si ottiene:

SELECT COUNT ( DISTINCT <NomeTabella.Campo> )

La query costruisce il prodotto cartesiano delle tabelle nella clausola FROM: se la clausola WHERE è presente seleziona solo le tuple che ne soddisfano il predicato; proietta la colonna specificata in corrispondenza di SELECT e restituisce il numero di tuple distinte che la compongono

Le tuple NULL non vengono conteggiate.

Esempio di applicazione COUNT (*):

Voglio calcolare quanti documenti sono presenti nella tabella Fatture

Select Count (*) AS NumeroFatture from  Fatture

Restituisce:

Ora voglio calcolare quanti documenti sono presenti nella tabella Fatture, che contengono “art01”:

Select Count (*) AS [Fatture con Articolo 1]

from  Fatture

where Fatture.Art = ‘art01’

Restituisce:

Ora voglio calcolare quanti prodotti, nella tabella Articoli, hanno come unità di misura ‘Pezzi’

Select Count (*) AS [Numero Prodotti con Unità di misura ‘Pezzi’] from  Articoli where UM=’Pz’

Prima viene eseguita l’interrogazione considerando le clausole FROM e WHERE.
L’operatore aggregato viene poi applicato alla tabella contenente i risultati dell’interrogazione.

Restituisce:

Il risultato finale corrisponde al numero di righe nella tabella Articoli che possiedono “PZ” come valore del campo UM. Questo numero non è una proprietà posseduta da una riga in particolare ma deve essere determinato lavorando su tutte le righe della tabella Articoli.

Esempio di applicazione MAX:

Voglio estrarre, dalla tabella Articoli, il prodotto con il prezzo più alto

Select MAX (Prezzo) AS [Prezzo più alto] from  Articoli

Restituisce:

Esempio di applicazione AVG:

Voglio calcolare, dalla tabella Articoli, la media dei prezzi di tutti i prodotti con unità di misura ‘Pz’

Select AVG (Prezzo) AS [Media Prezzi Prodotti “Pz”] from  Articoli where UM=’Pz’

Restituisce:

Esempio di applicazione OPERATORI DI AGGREGAZIONE:

Voglio calcolare, dalla tabella Articoli, la media dei prezzi di tutti i prodotti con unità di misura ‘Pz’

Select

SUM (Prezzo) AS [Somma Prezzi Prod. “Pz”]

MAX (Prezzo) AS [Prezzo Massimo Prod. “Pz”]

MIN (Prezzo) AS [Prezzo Minino Prod. “Pz”]

AVG (Prezzo) AS [Media Prezzi Prod. “Pz”]

from  Articoli where UM=’Pz’

Restituisce:

Operatore Insiemistico UNION

Le operazioni insiemistiche di unione, intersezione e differenza consentono di unire in un’unica tabella il risultato di due o più tabelle differenti (realizzate mediante SELECT) che abbiano una struttura compatibile, ovvero:

• stesso numero di campi visualizzati;
• stesso nome dei campi visualizzati;
• domini compatibili tra i diversi campi, nello stesso ordine.

Nel caso in cui le due query utilizzino nomi di campi differenti, si utilizzeranno gli ALIAS per uniformarli.

L’unione di due tabelle è realizzata mediante il costrutto UNION.

Select#1
UNION [ALL]
Select#2

Select# rappresenta una qualunque query SQL costruita mediante il comando SELECT. Nell’esempio, query#1 e query#2.

La query SQL realizza l’unione delle tuple contenute nelle tabelle generate dalle query specificate. Le tuple duplicate vengono eliminate automaticamente, a meno che non venga specificata la parola chiave ALL.

Per fare un esempio, ipotizziamo di avere una tabella strutturalmente uguale alla tabella cliente, ma contenente le anagrafiche dei fornitori:

Esempio di applicazione UNION:

Voglio creare una tabella unica, con tutte le anagrafiche aziendali, sia relative ai clienti che ai fornitori:

Select IdCli AS Codice, NomeCliente AS [Ragione Sociale], Indirizzo, Città

from  Clienti

UNION

Select IdFor AS Codice, NomeFornitore AS [Ragione Sociale], Indirizzo, Città

from  Fornitori

Nota: Posso omettere le [ ] sull’alias dei campi, perché non hanno spazi.

Restituisce:

Operatori booleani:  AND, OR, NOT

La clausola WHERE può avere come argomento un predicato semplice oppure un’espressione booleana costruita combinando predicati semplici con gli operatori AND, OR, NOT.

Ciascun predicato semplice può confrontare, mediante gli operatori relazionali ( =, <, >, <>, <=, >= ) oppure gli operatori speciali (LIKE, BETWEEN, IN<), il valore di un campo con un valore costante, o con il valore di un parametro o con il risultato della valutazione di un’altra espressione.

Ogni operatore può combinarsi con AND, OR e NOT.

• I predicati possono essere separati dal connettivo logico AND, e in questo caso sono selezionate solo le righe per cui tutti i predicati sono veri, o mediante il connettivo logico OR, e in questo caso sono selezionate solo le righe per cui almeno uno dei predicati risulta vero.
• L’operatore logico NOT è unario, cioè si applica ad un solo predicato e ha l’effetto di invertire il valore di verità del predicato stesso.
• La sintassi assegna, nella valutazione della condizione WHERE, la precedenza all’operatore NOT, ma non definisce una precedenza tra gli operatori AND e OR. In una interrogazione che richiede l’uso di entrambi gli operatori, conviene esplicitare l’ordine di valutazione mediante parentesi.

Esempio di applicazione NOT:

Voglio estrarre, dalla tabella Articoli, i prodotti che NON hanno l’unità di misura “Pz”

Select Art from  Articoli where NOT UM=’Pz’

Restituisce:

Operatori relazionali: =, <, >, <>, >=, >=

Gli operatori relazionali permettono di costruire predicati che vengono valutati su ciascuna riga delle tabelle coinvolte, indipendentemente da tutte le altre righe. Questi operatori restituiscono un valore booleano: Vero (quando la condizione è soddisfatta, 1 o -1) o Falso (0, quando non è soddisfatta).

Sono:

Esempio di applicazione OPERATORE >:

Voglio estrarre, dalla tabella Articoli, i prodotti che hanno un prezzo di vendita maggiore di 1.000,00

Select Art from  Articoli where Prezzo > 1000

Restituisce:

Ora voglio estrarre, dalla tabella Articoli, i prodotti che hanno un prezzo di vendita maggiore di 50,00 e l’unità di misura “Pz”

Select Art from  Articoli where Prezzo > 50 AND UM=’Pz’

Restituisce:

Ora voglio estrarre, dalla tabella Articoli, i prodotti che hanno un prezzo di vendita maggiore di 1.000,00 oppure i prodotti che hanno sia un prezzo di vendita maggiore di 50,00 sia l’unità di misura “Pz”.

Select Art from  Articoli where (Prezzo > 1000) OR (Prezzo > 50 AND UM=’Pz’)

Restituisce:

Il risultato è un elenco di articoli che soddisfano la condizione racchiusa nella prima parentesi oppure nel secondo gruppo di parentesi. Ovviamente, una diversa disposizione delle parentesi produce un risultato completamente diverso.

Operatori speciali:  LIKE, BETWEEN, IN, IS NULL

Oltre ai tradizionali operatori di confronto (=, <>, >, <, >=, <=), SQL mette a disposizione alcuni operatori speciali che consentono di eseguire ricerche più flessibili e mirate.  Questi operatori vengono utilizzati all’interno della clausola WHERE per filtrare i dati in base a criteri particolari, come la ricerca di valori simili, l’appartenenza a un elenco, l’inclusione in un intervallo o la presenza di valori nulli.

Tra i più utilizzati troviamo:

Questi operatori permettono di costruire condizioni di ricerca più espressive e di ridurre notevolmente la complessità delle query, soprattutto quando si lavora con grandi quantità di dati.

L’operatore LIKE si comporta come un operatore di uguaglianza arricchito con il supporto per una coppia di caratteri speciali: ? e *  che rappresentano rispettivamente un carattere arbitrario e una sequenza di un numero qualsiasi (anche zero) di caratteri arbitrari.

L’operatore LIKE si utilizza per il confronto di stringhe

WHERE Tabella.nomcampo LIKE <stringa>

La stringa può contenere (combinandoli):

Esempio di applicazione LIKE:

Voglio estrarre, dalla tabella Articoli, i prodotti che hanno un nome che inizia con ‘Bev*”

Select Art from  Articoli where Art LIKE “Bev%”

Restituisce:

L’operatore BETWEEN permette di considerare le tuple che contengono (in un campo) un valore numerico compreso in un intervallo specificato. Si utilizza in unione con l’operatore AND ed eventualmente con l’operatore NOT (per escludere un range di valori).

WHERE Tabella.nomecampo BETWEEN valore1  AND valore2

WHERE Tabella.nomecampo NOT BETWEEN valore1 AND valore2

Equivale a:

L’operatore IN permette di determinare le tuple che contengono (in un campo) un valore presente in un insieme specificato:

WHERE Tabella.nomecampo IN (valore1, valore2, ... insiemi di valori o query)

WHERE Tabella.nomecampo NOT IN (valore1, valore2, ... insiemi di valori o query)

Equivale a

L’operatore IS NULL permette di selezionare i termini con valori nulli cioè campi in cui c’è assenza di informazione si utilizza il predicato IS NULL. Il predicato risulta vero solo se il campo ha valore nullo. Il predicato IS NOT NULL è la sua negazione.

WHERE Tabella.nomecampo IS NULL

WHERE Tabella.nomecampo IS NOT NULL

Operatori di JOIN per il prelievo da più tabelle

Gli operatori di JOIN consentono di correlale dati provenienti da tabelle diverse, quando tra questi esiste una corrispondenza di valori contenuti nei rispettivi campi (che devono appartenere allo stesso dominio).

Viene formalmente definito prodotto cartesiano di più tabelle.

Il JOIN può avere diverse sintassi: la più semplice è l’indicazione del predicato di JOIN nella clausola WHERE, indicando il legame diretto esistente tra due tabelle tramite un uguaglianza:

WHERE  Tabella1.nomecapo1 = Tabella2.nomecapo2 ...

Questa sintassi a livello logico è equivalente all’utilizzo dell’operatore INNER JOIN.

In alternativa, è possibile 3 operatori nella condizione FROM:

Il loro utilizzo è all’interno della condizione FROM legato ad una in sintassi particolare:

SELECT Campi...

FROM

NomeTabella1.1 Inner|Left|Right JOIN NomeTabella2.1 ON  NomeTabella1.1 = NomeTabella2.1,

NomeTabella1.2  Inner|Left|Right JOIN NomeTabella2.2 ON  NomeTabella1.2 = NomeTabella2.2,

...

WHERE ...

Le righe che vengono coinvolte nel join sono in generale un sottoinsieme delle righe di ciascuna tabella: può infatti capitare che alcune righe non vengano considerate in quanto non esiste una corrispondente riga nell’altra tabella per cui la condizione sia soddisfatta.

Il join esterno (rappresentato dagli operatori Left join e Right join) esegue un join tra tabelle mantenendo però tutte le righe che fanno parte di una o dell’altra delle tabelle coinvolte (rispettivamente alla sinistra o alla destra del join): in questo caso, vengono posti degli opportuni valori nulli per rappresentare l’assenza di informazioni provenienti dall’altra tabella.

L’INNER JOIN è un operatore che correla dati in tabelle diverse sulla base di valori uguali in campi con lo stesso tipo: l’inner join tra due tabelle fa si che vengano selezionate, dalla concatenazione delle tabelle coinvolte, le righe per cui la condizione di join è vera.

Esempio di applicazione INNER JOIN:

Se volessimo estrarre tutti i dati presenti nella tabella, sostituendo però alle colonne Cliente e Articolo il nome del rispettivo elemento invece del codice,  ripreso dalle tabelle esterne, scriveremo:

Select Fatture.NumFatt, Fatture.DataFatt, Clienti.NomeCliente, Articoli.Art, Fatture.Prezzo, Fatture.Qta

from  Fatture

INNER JOIN Clienti ON Fatture.Cliente = Clienti.NomeCliente

INNER JOIN Articoli ON Fatture.Articolo = Articoli.Art

Che equivale all’esempio visto in precedenza:

Select Fatture.NumFatt, Fatture.DataFatt, Clienti.NomeCliente, Articoli.Art, Fatture.Prezzo, Fatture.Qta

from  Clienti, Articoli, Fatture

where Fatture.Cliente = Clienti.NomeCliente and Fatture.Articolo = Articoli.Art

La sua esecuzione produce la seguente tabella come risultato:

Il LEFT JOIN fornisce come risultato l’inner join esteso con le righe della tabella che compare a sinistra dell’operatore Left join anche se non esiste una corrispondente riga nella tabella di destra (risultato dell’inner l’inner join + tutti valori della tabella di sinistra).

Il RIGHT JOIN restituisce invece, oltre al risultato dell’inner join, le righe della tabella che compare a destra dell’operatore Right join per le quali l’operazione non trova un corrispondente nella tabella di sinistra (risultato dell’inner l’inner join + tutti valori della tabella di destra).

Esempio di applicazione RIGHT JOIN:

Se volessimo estrarre tutti i dati presenti nella tabella, sostituendo però alle colonne Cliente e Articolo il nome del rispettivo elemento invece del codice,  ripreso dalle tabelle esterne, scriveremo:

Select Fatture.NumFatt, Fatture.DataFatt, Clienti.NomeCliente, Articoli.Art, Fatture.Prezzo, Fatture.Qta

from  Fatture

INNER JOIN Clienti ON Fatture.Cliente = Clienti.NomeCliente

RIGHT JOIN Articoli ON Fatture.Articolo = Articoli.Art

La sua esecuzione produce la seguente tabella come risultato:

Criteri di ordinamento: ORDER BY

La clausola ORDER BY consente di definire il criterio di ordinamento delle tuple estratte dall’interrogazione; chiude l’interrogazione SQL.

L’ordine su ciascun campo può essere:

Se il qualificatore è omesso si assume un ordinamento ascendente.

E’ possibile specificare anche più campi che devono essere usati per l’ordinamento; la sintassi della clausola di ordinamento è la seguente:

ORDER BY Tabella.nomcampo [ asc | desc] { , Tabella.nomcampo [ asc | desc ]}...

Viene prima valutato il primo campo nell’elenco e si ordinano le righe in base a questo (il campo più a sinistra ha più priorità su quello a destra). Per righe che hanno lo stesso valore in questo campo si considerano i valori dei campi di ordinamento successivi, in sequenza.

Esempio di applicazione ORDER BY:

Select * from Articoli ORDER BY Prezzo

La sua esecuzione produce come risultato la tabella originale:

Criterio di raggrupamento: GROUP BY

Il criterio di raggruppamento GROUP BY consente di suddividere (= raggruppare) le tuple di una tabella in tanti gruppi, e su ciascuno di questi applicare un operatore aggregato.

Questo criterio consente quindi di suddividere le tabelle in sottoinsiemi, raggruppando le righe che possiedono gli stessi valori per un insieme di campi specificato, applicando quindi gli operatori aggregati come se ciascun gruppo di tuple fossero una tabella distinta vera e propria.

SELECT NomeTabella.Campo [,NomeTabella.Campo]...

OperatoreAggregato [AS Alias] [,OperatoreAggregato [AS Alias]]...

FROM NomeTabella [,NomeTabella]...

WHERE ...

GROUP BY NomeTabella.Campo [,NomeTabella.Campo]...

ORDER BY NomeTabella.Campo [,NomeTabella.Campo]...

In una query che fa uso della clausola GROUP BY possono comparire come argomento della SELECT solamente:

• un sottoinsieme dei campi utilizzato per il raggruppamento delle righe
• le funzioni aggregate valutate solo sugli altri campi.

Nella clausola SELECT devono comparire tutti i campi presenti anche nella clausola GROUP BY, eccetto per gli operatori aggregati; viceversa, nella clausola GROUP BY possono apparire anche campi che non sono presenti nella SELECT.

In una query che fa uso della clausola GROUP BY è possibile essere usata la clausola ORDER BY: in questo caso, è necessario che ogni campo presente nel ORDER BY appaia anche nel GROUP BY.

Formalmente, la query SQL costruisce il cartesiano delle tabelle nella clausola FROM.  Se la clausola WHERE è presente, seleziona solo le tuple che ne soddisfano il predicato. Le tuple della tabella così ottenuta vengono suddivise in gruppi,  dove ogni gruppo contiene quelle tuple che assumono il medesimo valore in corrispondenza dei campi elencati nella clausola GROUP BY. Per ogni gruppo la query SQL proietta sia le colonne NomeTabella.Campo [,NomeTabella.Campo]… sia il valore degli operatori aggregati che compaiono nella clausola SELECT, dove gli operatori aggregati vengono calcolati sulle tuple del gruppo

Dopo l’esecuzione del raggruppamento, ogni sottoinsieme di righe deve corrispondere a una sola riga nella tabella risultato dell’interrogazione: dopo che le righe sono state raggruppate in sottoinsiemi, l’operatore aggregato viene applicato separatamente su ogni sottoinsieme. Il risultato dell’interrogazione è costituito quindi da una tabella con righe che contengono il risultato della valutazione dell’operatore aggregato, affiancato al valore del campo (o dei campi) che è stato usato per l’aggregazione.

Esempio di applicazione GROUP BY:

Voglio calcolare, dalla tabella Fatture, l’importo totale fatturato ad ogni cliente. Tale importo non è presente come colonna calcolata, ma si ottiene moltiplicando il Prezzo (unitario) del prodotto per la Qtà esposta in fattura.

Select Cliente, SUM (Prezzo * Qta) AS [Totale Fatturato per Cliente]

from  Fatture

GROUP BY Cliente

Restituisce:

Clausola HAVING

La clausola HAVING descrive le condizioni che si devono applicare al termine dell’esecuzione di una query che fa uso di criteri di aggregazione: il suo ruolo è di fatto analogo a quello della clausola WHERE, solo che la clausola WHERE agisce su singole tuple mentre HAVING agisce su gruppi di tuple.

(dopo che il WHERE ha estratto le tuple (filtrate e selezionate).

Gli operatori aggregati NON possono comparire direttamene nel WHERE, essendo questa la condizione primaria di estrazione: a valle dell’estrazione del WHERE, può essere applicata la condizione HAVING per filtrare ulteriormente i risultati, confrontando gli operatori aggregati.

In una query che fa uso di funzioni di aggregazione e/o di raggruppamento, è possibile quindi specificare due condizioni di selezione che le tuple dovranno soddisfare per essere estratte:

• la clausola HAVING può avere come argomento gli operatori aggregati
• la clausola WHERE potrà avere come argomento gli altri campi della query

HAVING ammette nel predicato SQL gli operatori aggregati come termine di confronto.

Applicando la clausola HAVING, il risultato finale della query evidenzia un sottoinsieme di righe costruito dalle tuple della GROUP BY che soddisfano il predicato argomento della HAVING.

SELECT NomeTabella.Campo [,NomeTabella.Campo]...

OperatoreAggregato [AS Alias] [,OperatoreAggregato [AS Alias]]...

FROM NomeTabella [,NomeTabella]...

WHERE ...

GROUP BY NomeTabella.Campo [,NomeTabella.Campo]...

HAVING [Condizione su OperatoreAggregato]  ORDER BY NomeTabella.Campo [,NomeTabella.Campo]...

La query SQL costruisce il cartesiano delle tabelle nella clausola FROM: se la clausola WHERE è presente, la query seleziona solo le tuple che ne soddisfano il predicato, e le tuple della tabella così ottenuta vengono suddivise in gruppi, dove ogni gruppo contiene quelle tuple che assumono il medesimo valore in corrispondenza dei campi elencati nella clausola GROUP BY. La query elimina quindi i gruppi di tuple che non soddisfano il predicato nella clausola HAVING e infine PER OGNI GRUPPO la query SQL proietta sia le colonne NomeTabella.Campo [,NomeTabella.Campo]… sia il valore delle espressioni che compaiono nella clausola SELECT, dove le espressioni vengono calcolate sulle tuple del gruppo.

Esempio di applicazione HAVING:

Voglio estrarre, dalla tabella Fatture, i clienti che hanno totale fatturato > di 3.000,00 €. Dopo aver ottenuto, per ogni riga, l’importo totale moltiplicando il Prezzo (unitario) del prodotto per la Qtà esposta in fattura (come nell’esempio sopra), aggiungiamo la clausola HAVING per filtrare solo le righe che hanno un importo maggiore della soglia indicata (3000).

Select Cliente, SUM (Prezzo * Qta) AS [Totale Fatturato per Cliente]

from  Fatture

GROUP BY Cliente

HAVING SUM (Prezzo * Qta)> 3000

Nota: la notazione (Prezzo * Qta)> 3000 è puramente esplicativa, ai fini della trattazione in esame. La sua reale applicazione pratica – così come indicata nell’esempio –  avviene solo se il risultato del SUM è dello stesso dominio del termine di confronto (nel nostro caso 3000). Per una certezza del risultato in contesti diversi, sarebbe opportuno una conversione di campo nella query (detta cast, non oggetto di trattazione nel presente articolo, esempio: (Prezzo * Qta)> cast 3000 as float).

Restituisce:

Query annidate (Subquery)

Esistono problemi, rilevanti dal punto di vista pratico, che per essere risolti richiedono di leggere i dati di una tabella più volte: in questo senso, le SUBQUERY (o NESTED QUERY) consentono di trattare queste casistiche.

SQL è un linguaggio potente proprio perché consente di avere query all’interno di altre query: in questo modo una query complessa può essere scomposta in una query più semplice.

Una SUBQUERY è una query SQL che compare all’interno di un’altra query SQL (la quale potrebbe essere subquery di un’altra query e così via); sono delle espressioni SELECT nidificate, che possono essere posizionate:

• nella clausola WHERE
• nella clausola FROM

Per comprendere le interrogazioni nidificate le si può interpretare in questo modo: l’interrogazione nidificata viene eseguita per prima e viene poi operato un confronto accedendo direttamente a questi risultati.

Subquery nella clausola WHERE

Il caso più frequente è trovare le subquery all’interno della clausola WHERE: permette di costruire predicati con strutture complesse, in cui si confronta un valore (ottenuto come risultato di una espressione valutata sulla singola riga) con l’insieme di valori risultato dell’esecuzione di un’altra interrogazione SQL nidificata; la nested query viene eseguita per prima e viene usata nel confronto della condizione di WHERE.

WHERE
Espressione [Operatore] (Subquery)

La Sub Query deve essere compresa tra parentesi tonde ( ).

SQL esegue prima la subquery e, dopo che ne ha calcolato il risultato, esegue la query più esterna. Nella clausola WHERE una subquery ha il ruolo di un operando.

Una subquery rende un risultato confrontabile, e può restituire più valori (tabelle) o un unico valore: in base a ciò, sarà possibile utilizzare diversi operatori.

• Se la subquery restituisce un singolo valore, è lecito usarla come operando di:
  • Operatori relazionali (=,<,>, …)
  • Operatori matematici (+, _, -, . . )
• Se la subquery restituisce più valori (Nb: separati da virgole) può essere un operando di:
  • Operatori insiemistici

Poiché il risultato di una interrogazione SQL è generalmente costituito da un insieme di valori e in una Select nidificata questo viene confrontato con il valore di un campo per una singola riga alla volta, i normali operatori di confronto <, >, =,…. sono estesi con le parole chiave ALL e ANY.

L’operatore ALL specifica che la riga soddisfa la condizione solo se tutti gli elementi restituiti dall’interrogazione nidificata Subquery rendono vero il confronto; l’operatore ANY specifica che la riga soddisfa la condizione solo se almeno uno degli elementi restituiti dall’interrogazione nidificata Subquery rende vero il confronto.

La sintassi diventa quindi la seguente:

Espressione [Operatori Relazionali] [ALL | ANY] (Subquery)
Esempio: Espressione >= ALL (SubQuery)

SQL mette a disposizione anche due appositi operatori IN e NOT IN che rappresentano il controllo di appartenenza e di esclusione rispetto ad un insieme.

• IN: ha lo stesso significato dell’operatore = ANY.
  • Verifica se l’Espressione (capo o valore)  è presente nell’insieme di tutti gli elementi restituiti dall’interrogazione nidificata (Subquery)

• NOT IN ha lo stesso significato dell’operatore <> ALL.
  • Verifica se espressione non è presente nell’insieme di tutti gli elementi restituiti dall’interrogazione nidificata (Subquery).

La sintassi è la seguente:

Espressione [IN | NOT IN] (Subquery)

L’espressione a sinistra può essere:

• un singolo valore costante
• un nome di campo
• un’espressione contenente operatori matematici
• una subquery che restituisce un singolo valore

Un altro operatore è l’operatore EXISTS e NOT EXISTS. EXISTS (e la sua negazione) verifica se esiste o no almeno una riga nell’insieme restituito dall’interrogazione nidificata Subquery

La sintassi è la seguente.

Espressione [EXISTS | NOT EXISTS] (Subquery)

Esempio di applicazione EXISTS:

Vogliamo estrarre, dalla Tabella Fatture, le sole righe relative ad articoli che hanno come Unità di misura ‘Pz’ (pezzi).

Select NumFatt, Cliente, Articolo, Prezzo

from  Fatture

where Prezzo=ANY (Select Prezzo from  Articoli where UM=’Pz’)

La sua esecuzione produce la seguente tabella come risultato:

Query CORRELATE

Ci sono casistiche in cui non è sufficiente che la subquery venga eseguita una sola volta, ma è necessario che l’esecuzione avvenga per ogni tupla nella tabella della query esterna.

Quando una subquery deve essere eseguita tante volte quante sono le tuple della tabella esterna ed il suo risultato è di volta in volta funzione della tupla esterna, si parla di query correlate.

La sintassi è la seguente:

WHERE
Espressione (Subquery) [Operatore]

Affinché SQL esegua una subquery come query correlata, una o più tabelle nella clausola FROM della query esterna devono avere un Alias: tale alias più essere usato nelle espressioni all’interno della subquery.

Esempio di applicazione QUERY CORRELATE:

Vogliamo estrarre le fatture il cui prezzo è superiore al prezzo medio degli articoli venduti con Unità di misura ‘Pz’ (pezzi).

Select NumFatt, Cliente, Articolo, Prezzo

from  Fatture

where Prezzo>(Select AVG(Prezzo) from  Articoli where UM=’Pz’)

La sua esecuzione produce la seguente tabella come risultato:

Subquery nella clausola FROM

Dato che una sottoquery restituisce una tabella, può comparire anche nell’elenco di tabelle nella clausola FROM: in questo caso, si richiede che alla sottoquery sia associato un Alias, che all’interno della query SQL può essere utilizzato come un normale nome di tabella.

Esempio di applicazione SUBQUERY NEL FROM:

Voglio estrarre, dalla tabella Fatture, tutte le fatture che hanno un prezzo medio inferiore alla media dei prezzi di tutti i prodotti con unità di misura ‘Pz’.

Select NumFatt, Cliente, Articolo, Prezzo

from  Fatture, (Select AVG (Prezzo) AS PrezzoMedio from  Articoli where UM=’Pz’) X

where Fatture.Prezzo<X.PrezzoMedio

La sua esecuzione produce la seguente tabella come risultato:

Comandi per la modifica dei dati

Oltre ai comandi di estrazione dei dati, riportiamo una descrizione molto sintetica di 2 comandi per la modifica.

Il comando DELETE

Il comando DELETE consente di cancellare righe da una tabella del database.

DELETE
FROM NomeTabella
WHERE Condizione

L’effetto del comando è l’eliminazione dalla tabella NomeTabella di tutte le righe che soddisfano la condizione di Where; se non è specificata la clausola Where l’esecuzione del comando produce la rimozione di tutte le righe della tabella  specificata.

Nella condizione WHERE è possibile indicare anche una NESTED QUERY: in questo caso,verranno applicate le stesse regole per la query di SELECT, e le tuple eliminate saranno quelle che soddisfano la condizione di WHERE.

Il comando UPDATE

Il comando UPDATE consente di apportare modifiche al contenuto di una tabella.

UPDATE NomeTabella
SET Campo = < Espressione | SelectSQL | null >
{, Campo = < Espressione | SelectSQL | null | default > }
WHERE Condizione

Con questo comando vengono aggiornati uno o più campi delle righe della tabella NomeTabella che soddisfano l’eventuale condizione argomento della clausola Where; se il comando non contiene la clausola Where la modifica viene effettuata sui campi di tutte le righe.

Nei campi oggetto di modifica viene posto un valore che può essere il risultato della valutazione di una espressione sui campi della tabella, il risultato di una interrogazione SQL o il valore nullo; questo valore deve essere dello stesso dominio (tipo) del valore da aggiornare.

Nella clausola SET è possibile indicare anche una NESTED QUERY: in questo caso, la query annidata deve restituire un valore di dominio compatibile con quello del campo che deve aggiornare.

Varianti di sintassi

SQL è un linguaggio standard, ma ogni DBMS può adottare alcune varianti di sintassi: per questo motivo, una query scritta per un sistema può richiedere piccoli adattamenti prima di essere eseguita su un altro ambiente.

Le differenze più comuni riguardano la gestione delle date, la limitazione dei risultati, la concatenazione delle stringhe, le conversioni di tipo e l’uso degli identificatori.

Le principali varianti di sintassi – riferite a quanto affrontato nel presente articolo – sono: