Appunti Basi di Dati - completi

MATERIALIZZAZIONE

Materializzo il fatturato (per store, giorno) in una tabella CREATE TABLE

CREATE TABLE f Crea una tabella con la

SELECT store, data, SUM(importo) AS tot_importo struttura e i contenuti uguali al

risultato della query seguente

FROM tab_vendite

GROUP BY store, data;

Calcolo le ore lavorate (per supermercato, giorno) e materializzo La coppia di attributi

CREATE TABLE h <store, data> funge da

SELECT store, data, SUM(ore_lavorate) AS tot_ore chiave nella tabella

output

FROM tab_ore_lavorate

GROUP BY store, data;

A questo punto posso scrivere la query: Nelle tabelle materializzate

SELECT f.data, f.store, “f” ed “h”, i record hanno la

stessa granularità (entrambe

f.tot_importo / h.tot_ore FROM f, h hanno store e data come chiavi)

WHERE f.store=h.store AND f.data=h.data;

VISTE

Vista (o view): è una tabella virtuale associata al risultato di una query

La vista non esiste realmente, ogni volta che viene interrogata una vista, il DBMS esegue la query associata

Sintassi per la creazione di una vista: CREATE VIEW nome_view AS query …;

Es. CREATE VIEW Importo AS

SELECT store, data, SUM(importo) AS tot_importo

FROM tab_vendite

GROUP BY store, data;

Le viste, si usano come se fossero normali tabelle

Materializzazione VS viste

La materializzazione crea problemi

- spazio occupato

- disallineamenti nel caso di aggiornamento dei dati

Le viste risolvono i problemi di spazio occupato e aggiornamento, ma …

- le viste sono onerose da gestire per il DBMS

- ha senso creare una vista solamente se i dati saranno utilizzati più di una volta

- se una vista viene utilizzata spesso, conviene passare alla materializzazione

QUERY ANNIDATE

Ricordiamo che una query opera sulle tabelle indicate dopo la clausola FROM.

Per poter calcolare lo stesso risultato senza materializzare …

Dovremmo poter scrivere una query che oltre ad operare su tabelle esistenti riesca a lavorare a partire dai

risultati di altre query.

Esempio

SELECT f.data, f.store, f.tot_fat / h.tot_ore

FROM (*******) as f, (######) as h

WHERE f.store=h.store AND f.data=h.data;

Dovremmo poter scrivere delle query al posto di (*******) e (######)

E’ possibile farlo

SELECT f.data, f.store, f.tot_fat / h.tot_ore

FROM (

SELECT store, data, SUM(importo) as tot_fat

FROM tab_vendite

GROUP BY store, data

) as f,

( SELECT store, data, SUM(ore_lavorate) as tot_ore

FROM tab_ore_lavorate

GROUP BY store, data

) as h

WHERE f.store=h.store AND f.data=h.data;

IN GENERALE

In una query si può utilizzare una sotto-query come se fosse una tabella esistente

SELECT … FROM tab1, tab2, tab3 , tab4 …;

tab3 può essere sostituito da una sotto-query, come nell’esempio seguente:

SELECT … FROM tab1, tab2, ( SELECT …) AS tempTab3 , tab4 …;

• Le parentesi che delimitano la sotto-query sono obbligatorie

• E’ obbligatorio dare un nome ad una sotto-query con l’operatore AS, serve per poter fare riferimento alla

sotto-query in altre parti dell’interrogazione

Query annidate in WHERE

SELECT store, SUM(importo) x IN Y

FROM tab_vendite vero se e solo se l’elemento

WHERE store IN ( x è contenuto nell’insieme Y

SELECT store

FROM tab_ore_lavorate

GROUP BY store

HAVING SUM(ore_lavorate) > 1000

)

GROUP BY store;

La query calcola il fatturato tot dei supermercati in cui sono state impiegate più di 1000 ore dei dipendenti

Altri esempi

• SELECT dipendente FROM tab_ore_lavorate

WHERE store NOT IN (1, 2, 10) AND store IN (

SELECT store

FROM tab_vendite Posso racchiudere i diversi

GROUP BY store valori tra () e separarli da

HAVING SUM(importo)>10’000 virgola

)

GROUP BY dipendente; Modo di rappresentare

• SELECT store, id_cliente, SUM(importo) record con 2 o più

FROM tab_vendite attributi

WHERE (store, id_cliente) NOT IN ((1,24),(1,84))

GROUP BY id_cliente;

SOTTO-QUERY CORRELATE

● SELECT * FROM tab_vendite AS tv x >= ALL (... insieme)

WHERE tv.importo >= ALL vero se x>= … è verificato per tutti gli

( elementi dell’insieme

SELECT importo FROM tab_vendite as tv2

WHERE tv2.id_cliente = tv.id_cliente

);

Questa query visualizza, per ogni cliente, la vendita di importo massimo

Nella (sotto)query,

• la tabella tv non è definita

• si fa quindi riferimento alla tv della query esterna

L’operatore >=

• si aspetta un operando singolo alla sua destra

• Il risultato di una (sotto)query è un insieme

● SELECT * FROM tab_vendite AS tv

WHERE tv.importo >= ALL

( SELECT AVG(importo) FROM tab_vendite as tv2

WHERE tv2.id_cliente = tv.id_cliente

);

Visualizza per ogni cliente, le vendite di importo superiore alla media (delle vendite del singolo cliente)

La query interna produce una sola tupla di risultato

• L’ALL è comunque necessario. L’interprete SQL non può sapere in anticipo quante tuple restituirà la query

annidata

Se mettessi ANY invece che ALL non cambierebbe nulla, SELECT * FROM tab_vendite AS tv

WHERE tv.importo >= ANY

restituirebbe comunque un unico record (

SELECT AVG(importo) FROM tab_vendite as tv2

WHERE tv.id_cliente = tv2.id_cliente

);

ALL, ANY, IN

• sono utilizzati nelle espressioni condizionali

• permettono di confrontare un elemento con un insieme

Sintassi: Elemento OPERATORE {ANY | ALL | IN} Insieme

• Elemento può essere un valore (es. 5) o un attributo

• Operatore può essere un operatore booleano

• Insieme può essere il risultato di una query o un elenco di valori racchiusi tra ()

Esempi (come OPERATORE si utilizzerà il >=)

• x >= ALL (… insieme)

# vero se e solo se la condizione x>= è verificata per

# tutti gli elementi dell’insieme

• x >= ANY (… insieme)

# vero se e solo se la condizione x>= è verificata per

# almeno uno degli elementi dell’insieme

• x IN (… insieme) è equivalente a: x = ANY(… insieme)

• x NOT IN (… insieme) è equivalente a x<>ALL (… insieme)

Efficienza e conversioni

SELECT * SELECT * FROM tab_vendite AS tv, (

FROM tab_vendite AS tv SELECT id_cliente,

WHERE tv.importo >= ALL AVG(importo) as importomedio

( FROM tab_vendite

SELECT AVG(importo) GROUP BY id_cliente

FROM tab_vendite as tv2 ) AS sm

WHERE tv.id_cliente = tv2.id_cliente WHERE tv.id_cliente = sm.id_cliente AND

); tv.importo >= sm.importomedio;

Producono lo stesso risultato.

Sotto-query correlate estremamente inefficienti



• La sotto-query viene rieseguita per ogni record della query esterna

Spesso una sotto-query correlata può essere riscritta in modo più efficiente

Considerazioni

Le sotto-query rendono le interrogazioni inefficienti , va data la preferenza a query senza annidamenti, ma

non sempre è possibile farne a meno.

Le query annidate evitano di dover materializzare i risultati di alcune query. Tuttavia, in alcuni casi può

essere conveniente materializzare se:

• se i dati di partenza sono stabili nel tempo

• i dataset hanno grosse dimensioni

• la computazione delle (sotto)query è molto onerosa

Di volta in volta dovete decidere se è più conveniente materializzare o costruire query annidate

Altri esempi con “where”

Reminder: Tab_vendite (store, id_cliente, data, importo)

Tab_ore_lavorate (store, data, dipendente, ore_lavorate)

● SELECT store, data FROM tab_vendite

WHERE (store, data) NOT IN ( Devo racchiudere store e data tra

SELECT store , data parentesi per far capire che NOT IN

FROM tab_ore_lavorate deve lavorare su entrambi

GROUP BY store, data

HAVING SUM(ore_lavorate) <= 16

)

GROUP BY store, data;

La query individua le coppie <store,data> in cui sono stati impiegati i dipendenti per più di 16 ore giorn.

La clausola IN (e NOT IN) tendono a rallentare il DBMS. Si può riscrivere la query e renderla più veloce?

Equivalenti:

● SELECT store, data FROM tab_vendite

WHERE (store, data) IN (

SELECT store , data FROM tab_ore_lavorate

GROUP BY store, data

HAVING SUM(ore_lavorate) > 16

) GROUP BY store, data;

● SELECT store , data

FROM tab_ore_lavorate Molto meglio delle precedenti

GROUP BY store, data

HAVING

SUM(ore_lavorate) > 16;

Altro esempio

• SELECT store FROM tab_vendite • SELECT store FROM tab_vendite

WHERE store NOT IN ( WHERE store IN (

SELECT store FROM tab_ore_lavorate SELECT store FROM tab_ore_lavorate

GROUP BY store, data GROUP BY store, data

HAVING SUM(ore_lavorate) <= 16 HAVING SUM(ore_lavorate) > 16

) GROUP BY store; ) GROUP BY store;

Non sono equivalenti, Il GROUP BY è necessario per

hanno una diversa granulità le tabelle coinvolte. permettere all’HAVING di lavorare

sulle ore complessive della giornata

JOIN

L'operatore join permette di separare la condizione della clausola where in due parti

• Le condizioni per realizzare il collegamento tra tabelle

• Le condizioni di selezione vere e proprie

Esempio

• SELECT DATAFORNITURA, QT, NOMEPEZZO FROM PEZZI, FORNITURA, FORNITORI

WHERE PEZZI.CODICEPEZZO=FORNITURA.CODICEPEZZO AND

FORNITURA.CODICEFORNITORE=FORNITORI.CODICEFORNITORE AND

FORNITORI.NOMEFORNITORE='giani’ ;

E' equivalente a:

• SELECT DATAFORNITURA, QT, NOMEPEZZO FROM FORNITURA JOIN PEZZI JOIN FORNITORI ON

(PEZZI.CODICEPEZZO=FORNITURA.CODICEPEZZO AND

FORNITURA.CODICEFORNITORE=FORNITORI.CODICEFORNITORE) WHERE

FORNITORI.NOMEFORNITORE='giani’ ;

LEFT (OUTER) JOIN SELECT * FROM proprietari AS p

LEFT JOIN auto AS a ON (p.id_p=a.id_p);

In generale: SELECT * FROM A LEFT JOIN B ON (A.KEY=B.KEY);

Nella tabella risultato entrano:

• Le tuple di A e B che soddisfano la condizione A.KEY=B.KEY L’intersezione rappresenta i record

• Le tuple di A che non soddisfano la condizione A.KEY=B.KEY delle 2 tabelle con chiavi in comune

LEFT excluding (OUTER) JOIN SELECT * FROM proprietari AS p

LEFT JOIN auto AS a ON (p.id_p=a.id_p)

WHERE a.id_p IS NULL;

RIGHT (Outer) JOIN

SELECT * FROM A RIGHT JOIN B ON (A.KEY=B.KEY);

Nella tabella risultato entrano:

• Le tuple di A e B che soddisfano la condizione A.KEY=B.KEY

• Le tuple di B che non soddisfano la condizione A.KEY=B.KEY

RIGHT / - RIGHT Excluding (Outer) JOIN

SELECT * FROM A RIGHT JOIN B ON (A.KEY=B.KEY) WHERE B.KEY IS NULL;

FULL OUTER JOIN

SELECT * FROM A FULL OUTER JOIN B ON (A.KEY=B.KEY);

# In MySQL la sintassi qua sopra non è ammessa

Nella tabella risul