SQL (Structured Query Language)

Un corso completo sul linguaggio SQL (Structured Query Language): definizioni, esempi ed esercizi

Funzioni SQL

Capitolo 3

Funzioni

Le funzioni, nell’ambito dei linguaggi di terza generazioni (linguaggi procedurali), sono delle particolari procedure le quali passandogli dei valori (parametri) esse ci restituiscono (ritornano) un valore.

Anche se SQL non è un linguaggio procedurale, implementa le funzioni nella stessa maniera

ma con una differenza sostanziale:

nei linguaggi procedurali noi stessi possiamo crearci delle funzioni, con SQL ciò non è possibile e quindi possiamo utilizzare solo quelle funzioni che ci mette a disposizione il DBMS che stiamo usando.

In questo capitolo vedremo molte funzioni, ma soltanto le prime 5 (COUNT, SUM, AVG, MAX e MIN) sono definite nello standard SQL. Queste prime cinque funzioni sono le più importanti e dobbiamo impararle bene, esse sono sempre presenti nella maggior parte dei DBMS a differenza delle restanti, che a volte non appaiono affatto o sono implementate con una sintassi diversa.

 

Funzioni aggregate

Le funzioni che analizzeremo in questo paragrafo hanno la particolarità di restituire un solo valore. Inoltre, dato che operano su insiemi di righe, vengono anche chiamate funzioni di gruppo.

Gli esempi di questo paragrafo utilizzano la tabella IMPIEGATO:

IMPIEGATO
NOME COGNOME DIPARTIMENTO UFFICIO STIPENDIO
MARIO ROSSI AMMINISTRAZIONE 10 L. 4.500.000
CARLO BIANCHI PRODUZIONE 20 L. 360.000
GIUSEPPE VERDI AMMINISTRAZIONE 20 L. 4.000.000
FRANCO NERI DISTRIBUZIONE 16 L. 4.500.000
CARLO ROSSI DIREZIONE 14 L. 7.300.000
LORENZO LANZI DIREZIONE 7 L. 730.000
PAOLA BORRONI AMMINISTRAZIONE 75 L. 4.000.000
MARCO FRANCO PRODUZIONE 46 L. 4.000.000

COUNT

Restituisce il numero di righe che soddisfano la condizione specificata nella clausola WHERE.

Vediamo un esempio: voglio conoscere il numero di impiegati che appartengono al dipartimento produzione

SELECT COUNT (*)

FROM IMPIEGATO

WHERE DIPARTIMENTO = ‘PRODUZIONE’;

2

SUM

Questa funzione somma tutti i valori di una colonna, vediamo un esempio: voglio ottenere la somma di tutti gli stipendi

SELECT SUM(STIPENDIO)

FROM IMPIEGATO;

L.29.390.0000

La funzione SUM opera soltanto con i numeri, se viene applicata a un campo non numerico, si ottiene un messaggio di errore.

AVG

Calcola la media aritmetica dei valori di una colonna. Vediamo un esempio: voglio conoscere lo stipendio medio della tabella IMPIEGATO.

SELECT AVG(STIPENDIO)

FROM IMPIEGATO;

L. 3.673.750

La funzione AVG opera soltanto con i numeri.

MAX

Questa funzione serve a trovare il valore massimo di una colonna. Per esempio vogliamo sapere a quanto ammonta lo stipendio maggiore.

SELECT MAX(STIPENDIO)

FROM IMPIEGATO;

L. 7.300.000

La funzione MAX opera anche con i caratteri: la stringa ‘Maria’ è maggiore della stringa ‘Giovanna’.

MIN

Questa funzione opera in modo analogo a MAX, ad eccezione del fatto che restituisce il valore minimo di una colonna. Per trovare il minimo stipendio della tabella IMPIEGATO si usa la seguente espressione:

SELECT MIN(STIPENDIO)

FROM IMPIEGATO;

L. 360.000

La funzione MIN opera anche con i caratteri: la stringa ‘AAA’ è minore della stringa ‘BB’.

STDDEV

Deviazione standard

Questa funzione calcola la deviazione standard di una colonna di numeri. Non esiste in Access . Vediamo un esempio:

TEMPERATURE
CITTA TEMPERATURA
ROMA 10
ANCONA 8
NAPOLI 15

SELECT STDDEV(TEMPERATURA)

FROM TEMPERATURE;

STDDEV(TEMPERATURA)
3,6055513

VARIANCE

Quadrato della deviazione standard

Questa funzione calcola il quadrato della deviazione standard. Non esiste in Access. Vediamo un esempio usando la tabella usata precedentemente:

SELECT VARIANCE(TEMPERATURA)

FROM TEMPERATURE;

VARIANCE(TEMPERATURA)
13

Assiomi delle funzioni aggregate:

  • Restituiscono un solo valore
  • La clausola SELECT può essere seguita solo e soltanto dalla funzione di aggregazione
  • Vanno applicate a tipi di dato a loro compatibili

Funzioni temporali

Queste funzioni operano su date e orari; sono molto potenti e quando servono si rivelano essere molto utili.

Alcuni DBMS, come Access, non le implementano o usano sintassi diverse.

ADD_MONTHS

Questa funzione aggiunge un numero di mesi a una data specificata. Vediamo un esempio usando la tabella sottostante.

PROGETTO
COMPITO DATA_INIZIO DATA_FINE
AVVIO PROGETTO 01-Apr-99 02-Apr-99
DEFINIZIONE SPECIFICHE 02-Apr-99 01-Mag-99
CONTROLLO TECNICO 01-Giu-99 30-Giu-99
PROGETTAZIONE 01-Lug-99 02-Set-99
COLLAUDO 03-Set-99 17-Dic-99

SELECT COMPITO, DATA_INIZIO,

ADD_MONTHS(DATA_FINE,2)

FROM PROGETTO;

COMPITO DATA_INIZIO ADD_MONTH
AVVIO PROGETTO 01-Apr-99 02-Giu-99
DEFINIZIONE SPECIFICHE 02-Apr-99 01-Lug-99
CONTROLLO TECNICO 01-Giu-99 31-Ago-99
PROGETTAZIONE 01-Lug-99 02-Nov-99
COLLAUDO 03-Set-99 17-Feb-00

LAST_DAY

Questa funzione fornisce l’ultimo giorno di un mese specificato (se il mese è di 30, 31, 29 o 28 giorni).

SELECT DATA_FINE, LAST_DAY(DATA_FINE)

FROM PROGETTO;

DATA_FINE LAST_DAY(DATA_FINE)
02-Apr-99 30-Apr-99
01-Mag-99 31-Mag-99
30-Giu-99 30-Giu-99
02-Set-99 30-Set-99
17-Dic-99 31-Dic-99

MONTHS_BETWEEN

Questa funzione serve per sapere quanti mesi sono compresi tra il mese x e il mese y.

SELECT COMPITO, DATA_INIZIO, DATA_FINE,

MONTHS_BETWEEN(DATA_FINE, DATA_INIZIO) DURATA

FROM PROGETTO;

COMPITO DATA_INIZIO DATA_FINE DURATA
AVVIO PROGETTO 01-Apr-99 02-Apr-99 ,03225806
DEFINIZIONE SPECIFICHE 02-Apr-99 01-Mag-99 ,96774194
CONTROLLO TECNICO 01-Giu-99 30-Giu-99 ,93548387
PROGETTAZIONE 01-Lug-99 02-Set-99 2,0322581
COLLAUDO 03-Set-99 17-Dic-99 3,4516129

NEW_TIME

Questa funzione consente di regolare l’ora e la data in funzione del fuso orario. Vediamo un esempio utilizzando la tabella Progetto:

SELECT DATA_FINE AST,

NEW_TIME(DATA_FINE, ‘AST’ , ‘PDT’)

FROM PROGETTO;

AST NEW_TIME(DATA, ‘AST’, ‘PDT’)
02-Apr-99 01-Apr-99
01-Mag-99 30-Apr-99
30-Giu-99 29-Giu-99
02-Set-99 01-Set-99
17-Dic-99 16-Dic-99

(Vedi le sigle dei fusi orari nella seguente tabella)

FUSI ORARI
SIGLA FUSO ORARIO
AST o ADT Atlantic Standard o Atlantic Daylight Time
BST o BDT Bering Standard o Bering Daylight Time
CST o CDT Central Standard o Central Daylight Time
EST o EDT Eastern Standard o Eastern Daylight Time
GMT Greenwich Mean Time
HST o HDT Alaska-Hawaii Standard o Hawaii Daylight Time
MST o MDT Mountain Standard o Mountain Daylight Time
NST Newfoundland Standard Time
PST o PDT Pacific Standard o Pacific Daylight Time
YST o YDT Yukon Standard o Yukon Daylight Time

NEXT_DAY

Questa funzione imposta una nuova data, successiva a quella passatagli come primo parametro, in base al giorno della settimana passatogli come secondo parametro.

SELECT DATA_INIZIO,

NEXT_DAY(DATA_INIZIO, ‘VENERDI’)

FROM PROGETTO;

DATA_INIZIO NEXT_DAY
01-Apr-99 02-Apr-99
02-Apr-99 09-Apr-99
01-Giu-99 04-Giu-99
01-Lug-99 02-Lug-99
03-Set-99 10-Set-99

SYSDATE

Questa funzione fornisce la data e l’ora del sistema. Vediamo degli esempi:

SELECT DISTINCT SYSDATE

FROM PROGETTO;

SYSDATE
18-Mar-99

Per sapere a che punto del progetto si è arrivati oggi:

SELECT *

FROM PROGETTO

WHERE DATA_INIZIO > SYSDATE;

Funzioni aritmetiche

Si verifica spesso il caso in cui i dati che vengono estrapolati da un database richiedono delle operazioni matematiche. Molte implementazioni di SQL includono delle funzioni aritmetiche simili a queste. Gli esempi esposti si basano sulla tabella Numeri:

NUMERI
A B
3, 1415 4
-45 ,707
5 9
-57 ,667 42
15 55
-7 ,2 5 ,3

ABS

Questa funzione calcola il valore assoluto del numero specificato. Vediamo un esempio:

SELECT ABS(A)VALORE_ASSOLUTO

FROM NUMERI;

VALORE_ASSOLUTO
3, 1415
45
5
57 ,667
15
7 ,2

CEIL

Questa funzione fornisce il più piccolo numero intero che è maggiore o uguale al suo argomento.

Questa sintassi non è implementata da Access.

SELECT A, CEIL(A) MAX_INTERI

FROM NUMERI;

A MAX_INTERI
3, 1415 4
-45 -45
5 5
-57 ,667 -57
15 15
-7 ,2 -7

FLOOR

Questa funzione fornisce il più grande numero intero che è minore o uguale al suo argomento.

Questa sintassi non è implementata da Access.

SELECT A, FLOOR(A) MINIMI_INTERI

FROM NUMERI;

A MINIMI_INTERI
3, 1415 3
-45 -45
5 5
-57 ,667 -58
15 15
-7 ,2 -8

SIGN

La funzione SIGN restituisce –1 se il suo argomento è minore di zero e restituisce 1 se il suo argomento è maggiore o uguale a zero. Questa sintassi non è implementata da Access. Vediamo un esempio:

SELECT A, SIGN(A)

FROM NUMERI;

A SIGN(A)
3, 1415 1
-45 -1
5 1
-57 ,667 -1
15 1
-7 ,2 -1

È possibile anche utilizzare SIGN in una query SELECT . . . WHERE come questa:

SELECT A

FROM NUMERI

WHERE SIGN(A) = 1;

A
3,1415
5
15

Funzioni trigonometriche

Le funzioni trigonometriche COS, SIN, TAN sono molto utili in applicazioni in cui si richiede l’uso di tali calcoli. Tutte queste funzioni operano supponendo che l’angolo n sia espresso in radianti. Queste funzioni, stranamente, sono implementate da Access. Vediamo alcuni esempi usando la tabella ANGOLI:

ANGOLI RADIANTI
3,14
6,28
1,57

COS

Calcola il coseno del parametro passatogli come angolo espresso in radianti:

SELECT RADIANTI, COS(RADIANTI)

FROM ANGOLI;

RADIANTI COS(RADIANTI)
3,14 -,9999987
6,28 ,99999493
1,57 ,00079633

SIN

Calcola il seno del parametro passatogli come angolo espresso in radianti:

SELECT RADIANTI, SIN(RADIANTI)

FROM ANGOLI;

RADIANTI SIN(RADIANTI)
3,14 ,00159265
6,28 -,0031853
1,57 ,99999968

TAN

Calcola la tangente del parametro passatogli come angolo espresso in radianti:

SELECT RADIANTI, TAN(RADIANTI)

FROM ANGOLI;

RADIANTI TAN(RADIANTI)
3,14 -,0015927
6,28 -,0031853
1,57 1255,7656

Funzioni sulle potenze, logaritmi e radici

Per gli esempi verrà usata la tabella NUMERI sottostante:

NUMERI
A B
3, 1415 4
-45 ,707
5 9
-57 ,667 42
15 55
-7 ,2 5 ,3

EXP

Questa funzione permette di elevare e a un esponente (e è una costante matematica che viene utilizzata in varie formule). Vediamo un esempio:

SELECT A, EXP(A)

FROM NUMERI;

A EXP(A)
3, 1415 23 ,138549
-45 2 ,863E-20
5 148 ,41316
-57 ,667 9 ,027E-26
15 3269017 ,4
-7 ,2 ,00074659

LN

Questa funzione calcola il logaritmo naturale. Questa funzione non è implementata da Access .

Vediamo un esempio in cui si vuole calcolare i logaritmi della colonna A della tabella NUMERI:

SELECT A, LN(A)

FROM NUMERI;

ERRORE:

ORA-01428: l’argomento ‘-45’ è esterno all’intervallo

Il messaggio d’errore che viene visualizzato è dato dal fatto che non è possibile determinare un logaritmo di un valore negativo quando la base è positiva: non esiste nessun esponente che elevato ad e (valore positivo) ci da come risultato un valore negativo.

Il ‘problema’ può essere risolto inserendo all’interno della funzione LN la funzione ABS che ci restituisce i valori assoluti di quelli specificati:

SELECT A, LN(ABS(A))

FROM NUMERI;

A LN(ABS(A))
3, 1415 1,1447004
-45 3,8066625
5 1,6094379
-57 ,667 4,0546851
15 2,7080502
-7 ,2 1,974081

LOG

Questa funzione richiede due argomenti e calcola il logaritmo del secondo avendo come base il primo. Vediamo un esempio in cui si calcolano i logaritmi del numero 2 aventi come base i valori della colonna B della tabella NUMERI:

SELECT B, LOG(B, 2)

FROM NUMERI;

B LOG(B,2)
4 ,5
,707 -1 ,999129
9 ,31546488
42 ,18544902
55 ,17296969
5 ,3 ,41562892

Questa funzione non ci permette, però, di calcolare il logaritmo in cui la base è negativa, dunque il primo argomento che viene passato alla funzione dovrà essere sempre maggiore di zero.

POWER

Questa funzione non è implementata da Access. Questa funzione consente di elevare un numero alla potenza di un altro. Il primo argomento è elevato alla potenza del secondo. Vediamo un esempio:

SELECT A, B, POWER(A, B)

FROM NUMERI;

ERRORE:

ORA-01428: l’argomento ‘-45’ è esterno all’intervallo

Sembrerebbe che non sia possibile (matematicamente) elevare un valore negativo ad un indice frazionario, ma non è così, il problema dunque sussiste forse solamente per SQL implementato da Oracle. Il problema può essere risolto usando opportune funzioni viste in precedenza o evitando di far calcolare la potenza di un numero negativo usando un indice frazionario. Facciamo un’altra prova:

SELECT A, B, POWER(B, A)

FROM NUMERI;

A B POWER(B,A)
3, 1415 4 77,870231
-45 ,707 5972090,5
5 9 59049
-57 ,667 42 2,467E-94
15 55 1,275E+26
-7 ,2 5 ,3 6,098E-06

SQRT

Questa funzione è implementata da Access con la sintassi ‘SQR(nome_campo)’. La funzione SQRT restituisce la radice quadrata di un argomento. Poiché la radice quadrata di un numero negativo non esiste, non è possibile utilizzare questa funzione con i numeri negativi.

Vediamo una esempio:

SELECT B, SQRT(B)

FROM NUMERI;

B SQRT(B)
4 2
,707 ,84083292
9 3
42 6,4807407
55 7,4161985
5,3 2,3021729

Funzioni di caratteri

Queste funzioni ci permettono di manipolare i dati da visualizzare in tutti i modi e formati desiderati. Sono particolarmente utili quando abbiamo la necessità di rendere i dati più leggibili o quando vogliamo estrapolare delle informazioni sulle stringhe e i caratteri rappresentanti le informazioni.

Gli esempi presentati si basano sulla tabella CARATTERI sottostante:

CARATTERI
COGNOME NOME S CODICE
ROSSI GIGI A 32
BIANCHI MARIO J 67
NERI MARIA C 65
BRUNI ANGELO M 87
SCURI ANNA A 77
VERDI ANTONIO G 52

CHR

Questa funzione fornisce il carattere corrispondente al codice ASCII passatogli. Vediamo un esempio:

SELECT CODICE, CHR(CODICE)

FROM CARATTERI;

CODICE CH
32
67 C
65 A
87 W
77 M
52 4

Autore: Art Net

Condividi questo articolo su

12 Commenti

  1. Ho imparato alcune cose buone qui. Certamente merita il bookmarking per la rivisitazione.

    Mi chiedo quanti sforzi hai impostato per creare questo tipo di fantastico sito informativo.

    Rispondi al Commento
  2. guida molto interessante grazie se ci fossero anche le istruzioni per creare la tabella sarebbe completa secondo me

    Rispondi al Commento
  3. Grazie per la guida. Semplice, interessante ed utile.
    Vorremmo segnalare due cose. Un errore ed un dubbio.
    Errore:
    SELECT DIVISIONE, AVG(STIPENDIO)
    FROM DIPENDENTI
    HAVING AVG(STIPENDIO) > 2200000;

    manca la “group by DIVISIONE” prima della HAVING.

    Di conseguenza il dubbio sul punto 6.
    HAVING non è necessario che venga posta dopo la clausola GROUP BY.
    Sicuri?

    Rispondi al Commento
  4. Guida Molto utile, segnalo un errore nella query 9, capitolo 2, delle soluzioni:
    PrezzoIngrosso è moltiplicato * 1,5 ma si chiedeva un aumento del 15%, ciò significa moltiplicare * 1,15

    Rispondi al Commento
  5. Veramente complimenti, una guida dettagliata, semplice e adatta a tutti 🙂

    FortyZone

    Rispondi al Commento
  6. Davvero complimenti ragazzi. La prima guida che per chi parte da zero è manna dal cielo…PERFETTA!!

    Rispondi al Commento
  7. nel punto 8 la soluzione non dovrebbe essere con HAVING MIN(IMPORTO) > 400 000; cioè: SELECT BENEFICIARI FROM ASSEGNI GROUP BY BENEFICIARIO HAVING MIN(IMPORTO) > 400 000;

    Rispondi al Commento
  8. un ottimo Manuale unico errore (se vogliamo chiamarlo cosi) i tipi di dati Char(5) number(5,2) cosa sono e come si usano

    Rispondi al Commento
  9. Complimenti ottimo sito! fatto davvero bene. Grazie!

    Rispondi al Commento

Invia commento

Il tuo indirizzo email non sarà pubblicato.