Basi di dati - linguaggio di Query

Funzioni e espressioni:

 Aritmetiche

Oltre agli usuali operatori aritmetici (+, -, *, /) comprendono:

- funzione ABS(n), che, dato un valore numerico n, ne calcola il valore

assoluto

- funzione MOD(n,b) che, dati due valori interi n e b, calcola il resto della

divisione intera di n e b

- funzioni logaritmiche, esponenziali, per il calcolo della radice quadrata, dell’

elevamento a potenza, della parte intera superiore e inferiore

 Su stringhe

Oltre all’operatore di concatenazione denotato da || comprendono:

- LENGTH(str) restituisce la lunghezza della stringa str in numero di caratteri

- UPPER(str) e LOWER(str) trasformano la stringa str in caratteri tutti

maiuscoli o tutti minuscoli

- SUBSTR(str, m [, n]) estrae dalla stringa str la sottostringa dal carattere in

posizione m per una lunghezza n (se n è specificato) oppure fino all’ultimo

carattere

 Su date e tempi

È possibile applicare + e – sui valori temporali, tra intervalli e tra valori

temporali e intervalli, è inoltre possibile applicare * e / tra un intervallo e un

numero. Oltre a queste altre funzioni sono:

- Zerarie: CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP

- EXTRACT(q FROM e) estrae il campo corrispondente al qualificatore

temporale q dall’espressione e.

(es. EXTRACT (DAY FROM DATE ’08-Ott-1969’)

Le espressioni possono comparire nella clausola SELECT, nella WHERE e nella espressioni di

assegnamento del comando UPDATE.

Utilizzando espressioni nella clausola SELECT si dà luogo a una colonna virtuale, che non è

realmente presente nelle relazioni scelte per l’interrogazione, è comunque possibile

assegnare un nome alla colonna virtuale per mezzo della sintassi AS <nome colonna> (questo

nome non è possibile utilizzarlo nelle altre 2 clausole perché non esiste ancora quando esse

vengono valutate)

Ordinamento del Risultato

È possibile specificare un diverso ordinamento rispetto a quello previsto, aggiungendo alla fine

dell’interrogazione la clausola ORDER BY seguita da una lista di nomi di colonne, inoltre per ogni

colonna l’opzione ASC (che è di default) prevede un ordinamento crescente, DESC uno decrescente

(es. SELECT * FROM Film WHERE codFilm = 6635 ORDER BY titolo DESC, anno;)

Operazione di Join

Il join permette di associare tuple di relazioni diverse e selezionarne alcune sulla base di condizioni, è

il meccanismo che attraversa le associazioni rappresentate mediante le chiavi esterne.

La semantica del join è un prodotto cartesiano di due o più relazioni al cui risultato è applicata una

selezione basata sul predicato di join (questo predicato esprime un’associazione che dev’essere

verificata dalle tuple risultato dell’interrogazione), quindi, le relazioni da congiungere tramite il join

vanno nella clausola FROM, i predicati di join vanno nel WHERE, si effettua poi il prodotto cartesiano

tra le relazioni nella FROM a cui si applica la selezione data dal predicato di join.

Selezione, proiezione e join

Per il join esistono sintassi alternative:

 CROSS JOIN è la forma più semplice di join, corrisponde al prodotto cartesiano

(sintassi <nome relazione> CROSS JOIN <nome relazione>)

 JOIN ON forma che unisce operatore e predicato

(sintassi <nome relazione> JOIN <nome relazione> ON <predicato>)

 JOIN USING è richiesta l’uguaglianza dei valori delle colonne specificate

(sintassi <nome relazione> JOIN <nome relazione> USING (<lista nomi colonne>))

 NATURAL JOIN è richiesta l’uguaglianza dei valori di tutte le colonne con lo stesso

nome nelle due relazioni (sintassi <nome relazione> NATURAL JOIN <nome relazione>)

n.b. in queste 2 ultime sintassi le colonne con lo stesso nome vengono incluse un’unica

volta nel risultato

Necessità di ridenominazione

In SQL se si intende fare un join utilizzando più volte una stessa relazione (fare più prodotti cartesiani

con una tabella), sono necessari degli ALIAS di relazione.

L’Alias è un carattere (si possono utilizzare anche parole o numeri) che si aggiunge per distinguere a

quale delle tante copie della tabella faccio riferimento. Es se intendo fare una Query tra Persone x

Paternità x Persone, nella clausola FROM devo specificare per la due tabelle Persone (che sono la

stessa tabella) un ulteriore carattere, per sapere se nel predicato mi riferisco alla prima o alla

seconda. Aggiungo quindi nella clausola FROM un carattere differente per entrambe le tabelle così da

poterle distinguere.

SELECT f.Nome, f.Reddito, p.Reddito

FROM Persone p, Paternità, Persone f

WHERE p.Nome = Paternità.padre AND Paternità.figlio = f.Nome AND f.Reddito > p.Reddito

 OUTER JOIN permette di aggiungere all’interrogazione anche le tuple che non

partecipano al confronto tra le relazioni, ovvero aggiunge al risultato anche le tuple

che appartengono a una sola relazione e che non hanno partecipato al join

completandole con un NULL (nella parte mancante del prodotto cartesiano), esistono

tre varianti:

- FULL include tutte le tuple di entrambe le relazioni (nella join), completando

quelle tuple che non prendono parte alla join

- LEFT include tutte le tuple della relazione di sinistra (nella join),

completando quelle che non prendono parte

- RIGHT include tutte le tuple della relazione di destra (nella join),

completando quelle che non prendono parte

n.b. è possibile combinare i predicati di join con l’outer join in modo da aggiungere determinate tuple

che altrimenti rimarrebbero escluse dal risultato.

Funzioni di Gruppo

Le funzioni di gruppo, o funzioni aggregate, consentono di estrarre informazioni aggregate da insiemi

di valori, come una media degli stipendi degli impiegati di un’azienda o il massimo voto conseguito da

uno studente. Queste funzioni vengono utilizzate nella clausola SELECT e si applicano all’insieme di

tuple che soddisfano la clausola WHERE dell’interrogazione.

Le principali sono:

 MAX determina il valore massimo in un insieme

 MIN determina il valore minimo in un insieme

 SUM esegue la somma dei valori di un insieme (solo per insiemi numerici)

 AVG esegue la media dei valori di un insieme (solo per insiemi numerici)

 COUNT restituisce il numero degli elementi di un insieme (cardinalità)

Ad eccezione di COUNT sono tutte applicate su insiemi di valori semplici e non su insiemi di tuple,

questi insiemi semplici sono denotati da un nome di una colonna o da un’espressione contenente il

nome di una colonna. Tutte possono essere associate al qualificatore DISTINCT, che se presente

elimina eventuali duplicati prima di applicare la funzione (la presenza non è rilevante per MAX e

MIN). Per SUM e AVG se è presente DISTINCT prima si eliminano i duplicati e poi si applica la funzione

ai rimanenti valori non nulli, mentre se non è presente si applicano a tutti i valori non nulli per la

colonna (duplicati compresi).

La funzione COUNT può avere tre argomenti differenti:

- se non è presente DISTINCT e si specifica la colonna sulla quale agisce (mettendo il nome della

colonna tra parentesi dopo COUNT) esso restituisce la cardinalità della colonna

- se è presente DISTINCT e si specifica la colonna, prima elimina i duplicati e poi restituisce la

cardinalità di essa

- se si utilizza il carattere speciale ‘*’ restituisce la cardinalità del risultato della query (numero

di tuple presenti nel risultato).

n.b. Le colonne ottenute dall’applicazione di funzioni di gruppo sono sempre colonne virtuali, quindi

è possibile assegnare un nome con la clausola AS (come per quelle ottenute da funzioni aritmetiche),

inoltre le funzioni gruppo possono essere utilizzate a loro volta in funzioni aritmetiche. Infine se

l’insieme di valori su cui vengono applicate è vuoto, la COUNT restituisce 0, tutte le altre NULL.

Raggruppamento

L’operatore di raggruppamento permette di partizionare le tuple di una relazione in base al valore di

una o più colonne della relazione, dato che queste colonne vanno specificate nella clausola GROUP

BY, solo le tuple che soddisfano la clausola WHERE partecipano al raggruppamento. (il risultato

prodotto da una query con GROUP BY contiene tante tuple quanti sono i gruppi di tuple risultati dal

raggruppamento)

In un’interrogazione contenente la clausola GROUP BY, ogni tupla presente in risultato rappresenta il

gruppo di tuple (che soddisfano WHERE) della relazione su cui l’interrogazione è eseguita (ad ognuno

di questi gruppi sono applicate le funzioni di gruppo).

La clausola SELECT contenente la clausola GROUP BY può includere:

- una o più colonne tra quelle che compaiono nella clausola GROUP BY

- le funzioni di gruppo

mentre non può includere le colonne che non compaiono nella clausola GROUP BY (perché risultato

contiene solo una tupla per ogni gruppo mentre le singole colonne delle tuple nel gruppo possono

assumere diversi valori.

n.b. è possibile utilizzare più colonne per raggruppare e le funzioni di gruppo possono essere usate

anche in presenza di join (facendo ad esempio un NATURAL JOIN tra due tabelle nel FROM).

Solitamente si realizza prima una query che contiene tutte le informazioni richieste, e in seguito viene

adattata per raggruppare tali informazioni in gruppi.

Clausola HAVING

Con questa clausola è possibile selezionare solo alcuni dei gruppi di tuple ottenuti dal

raggruppamento. La sintassi dell’HAVING prevede che essa sia seguita da un predicato o una

combinazione booleana di predicati, l’importante è che tali predicati siano predicati che coinvolgono

funzioni di gruppo.

Es.

Per ogni regista che ha girato almeno due film prima del 2000, determinare quanti sono tali film, di

quanti generi diversi e la valutazione minima, media e massima di tali film:

SELECT regista, COUNT(*) AS numF,

COUNT(DISTINCT genere) AS numG,

MIN(valutaz) AS minV,

AVG(valutaz) AS avgV,

MAX(valutaz) AS maxV

FROM Film

WHERE anno < 2000

GROUP BY regista

HAVING COUNT(*) >= 2;

Ecco come avviene il raggruppamento:

1. dal database Film si estraggono tutti i fil girati prima del 2000

2. questi film vengono raggruppati per regista

3. si selezionano solo quei gruppi in cui il regista ha girati 2 o più film

4. risultato

Operazioni Insiemistiche

Ogni interrogazione può essere costituita da una o più interrogazioni connesse dagli operatori

UNION, MINUS, EXCEPT o INTERSECT, essi pongono le restrizioni sugli schemi delle interrogazioni su

cui operano:

- le due interrogazioni devono avere lo stesso numero di colonne e i domini corrispondenti

devono essere compatibili

- non è richiesto che i nomi delle colonne siano gli stessi e nel caso