Risoluzione esercizi Basi di dati

Bolletta (ID_BOLLETTA, data, importo, ID_datacenter)

Intervento (ID_INTERVENTO, data, importo, ID_datacenter)

Si deve calcolare per ogni data center

-​ totale incassato con le fatture

-​ totale spese sia di elettricità si di manutenzione

-​ Utile= Fatturato_tot - Elettricita_tot - Manutenzioni_tot (dove tot sta per

totale)

-​ Condizione di selezione nella where: utile > 1’000’000

-​ order by utile desc

select a.id_datacenter, a.Fatturato_tot-b.elettricita_tot-c.manutenzione_tot as

utile

from( select id_datacenter , sum (importo) as Fatturato_tot

from Fattura

group by id_datacenter

) as a,(

select id_datacenter, sum(importo) as elettricita_tot

​ from Bolletta

​ group by id_datacenter

​

) as b,(

select id_datacenter, sum(importo) as manutenzione_tot

from Manutenzione

group by id_datacenter

)as c

where a.id_datacenter=b.id_datacenter and b.id_datacenter = c.id_datacenter

and utile > 1000000

​

order by utile desc

/*Commento*/

In questo esercizio si richiede di calcolare, per ogni data center, l’utile ossia la

differenza tra Fatturato_tot , Elettricita_tot , Manutenzioni_tot , considerando solo

quelle con utile maggiore di 1000000 e ordinando i risultati in ordine decrescente

in base all’utile.

Per farlo, si è scelto di ricorrere alle query annidate, ovvero query che , oltre a

operare su tabelle già esistenti , permettono di lavorare anche sui risultati di altre

sotto-queri. In particolare, si usano tre sotto-query in from (alias a , b e c),

ognuna delle quali calcola rispettivamente il fatturato totale, il totale speso in

elettricità e il totale speso in manutenzione.

Questo approccio è necessario in quanto le tabelle Fattura Bolletta e intervento

hanno granularità diversa(cioè hanno diverso insieme di chiavi che identificano

un’unità di informazione): la prima registra le singole fatture emesse ai clienti per

i servizi erogati, la seconda memorizza le singole spese sostenute per pagare

l’elettricità dei data center, e la terza riporta i costi degli interventi di

manutenzione effettuati.

Un join diretto produrrebbe sicurmante risultati incoerenti, utilizzando invece le

query annidate, è possibile unformare la granularità e calcolare correttamente

l’utile.

Per quanto riguarda invece la condizione di selezione nel where filtra solo i

datacenter con utile maggiore di 1000000

mentre l’order by utile desc permette di visualizzare in ordine di utile

Vi sono altre due alternative alle query annidate:

La materializzazione: creazione di tabelle temporanee tramite CREATE TABLE ,

questa è molto tuttavia occupa molto spazio in memoria e richiede un

aggiornamento manuale

La vista: creazione di tabelle virtuali tramite CREATE VIEW , che quindi non

occupano spazio in memoria, e che vengono aggiornate in automatico al

momento dellaa consultazione

Esercizio 3

Customer (ID_CUSTOMER, nome, cognome)

Micropalestra (ID_PALESTRA, nome, indirizzo)

Prenotazione (ID_customer, ID_palestra, data, ora)

Abbonamento (ID_customer, anno, mese, importo

Per ogni utente mostrare

-​ totale dei soldi incassati per abbonamento

-​ totale n prenotazioni effettuate

-​ rapporto = tot_speso/tot_prenotazioni

select a.id_customer, a.tot/b.n as rap

from( select id_custumer, sum(importo) as tot

from Abbomamento

group by id_customer

) as a,(

select id_customer, count(*) as n

​ from Prenotazione

​ group by id_customer

​

) as b

where a.id_custumer=b.id_custumer /*si potrebbe usare alternativamente

anche a join b on (e a.id_custumer=b.id_custumer) */

In questo esercizio si richiede di calcolare, per ogni utente, il rapporto tra il totale

dei soldi incassati e il numero di prenotazioni effettuate.

Per farlo, si è scelto di ricorrere alle query annidate, ovvero query, che oltre a

operare su tabelle esistenti, permettono di lavorare anche sui risultati di altre

sotto-query. In particolare , si usano due sotto-query (alias a e b) ognuna delle

quali calcola rispettivamente il totale dei soldi incassati per gli abbonamenti e il

numero delle prenotazioni. Questo approccio è necessario in quanto le tabelle

Abbonamento e Prenotazione hanno granularità differenti (cioè diverso insieme

di chiavi che identificano un’unità di informazione): la prima infatti registra i

canoni di abbonamento pagati dai singoli utenti mentre la seconda memorizza le

prenotazioni effettuate .

Un join diretto produrrebbe sicuramente risultati incoerenti, mentre utilizzando le

query annidate , è possibile uniformare le granularità e calcolare correttamente il

rapporto.

Vi sono due ulteriori alternative alla query annidata:

La materializzazione: creazione di tabelle temporanee tramite il CREATE TABLE,

molto utile tuttavia richiede molto spazio in memoria e aggiornamento manuale

Le viste: creazione di tabelle virtuali tramite CREATE VIEW, che quindi non

occupano spazio in memoria e che hanno aggiornamento automatico al

momento della consultazione

Esercizio 4

Cliente (ID_CLIENTE, nome, cognome, eta)

Fornitura (ID_filtro, ID_cliente, data, prezzo)

Ricarica (ID_cliente, data, ID_macchina, qtCO2)

Noleggio (ID_cliente, mese, importo)

per ogni utente

-​ tot soldi incassati

-​ tot costi per ricambio filtri

-​ tot costi per caricamento CO2

-​ Utile = Tot_incasso-tot_costi_filtri-tot_costi_CO2

select a.id_cliente, a.tot-b.tot-c-tot as utile

from( select id_cliente, sum(importo) as tot

from noleggio

group by id_cliente

)as a, (

select id_cliente, sum(prezzo) as tot

from Fornitura

group by id_cliente

)as b,( select id_cliente, sum(5*qtCO2)as tot

from Ricarica

group by id_cliente

)as c

where a.id_cliente=b.id_cliente and b.id_cliente=c.id_cliente /*alternativa a join

b on( a.id_cliente=b.id_cliente and b.id_cliente=c.id_cliente)*/

In questo esercizio si richiede di calcolare, per ogni utente, l’utile, dato dalla

differenza tra i soldi incassati dal noleggio, il totale dei costi per il cambio filtro e

per la ricarica di co2.

Per farlo, si è scelto di ricorrere alle query annidate, ossia query che , oltre a

operare su tabelle esistenti, permettono di lavorare a partire dei risultati delle

sotto-query. In particolare, si usano le tre sotto-query (alias a e b) che calcolano

ripettivamente il totale del incasso dai noleggi, il totale dei costi per il cambio filtri,

e il costo della ricarica di CO2.

Questo approccio è neccessario in quanto le tabelle Fornitura, Ricarica e Noleggio

presentano granularità differeni t(cioè diverso insieme di chiavi che identificano

un unità di informazione): la tabella forniture riporta informazioni sui filtri installati

nelle macchine presso il cliente, ricarica invece descrive le operazione di

riempimento dei serbatoi di CO2 mentre noleggio

memorizza i canoni di noleggio mensili pagati dal cliente per il servizio di

depurazione e gasatura acqua.