SQl

COGNOME e Nome_________________________ LOGIN sul PC_________________________________________
Basi di Dati
Prova di SQl del 24-02-2015
Viene valutato quanto memorizzato su file (denominare il file COGNOME.sql)
Facendo riferimento allo schema del database PROVA, scrivere la query SQL corrispondenti alle seguenti
interrogazioni:
1) Elencare per tutti i fornitori, il codice, il nome, la città, il numero totale di forniture NNTF, la somma totale delle
quantità fornite NSTF, il numero totale di prodotti NNTP e parti NNTPA e, per chi non avesse nessuna fornitura,
stampare la stringa “na” al posto del valore per NNTF, NSTF, NNTP, NNTPA (suggerimento: utilizzare una tabella
temporanea).
select TEMP.cod, TEMP.nome, TEMP.CITTA, case when TEMP.NTF=0 then na else TEMP.NTF end as NNTF,
coalesce(TEMP.STF,na) as NSTF, case when TEMP.NTP=0 then na else TEMP.NTP end as NNTP,
case when TEMP.NTPA=0 then na else TEMP.NTPA end as NNTPA
from
(select cod,nome,citta,count(FT.fcod) as NTF,sum(quantita) as STF, count(distinct FT.prcod) as NTP, count(distinct pcod) as NTPA
from fornitori F left outer join forniture FT on F.cod=FT.fcod
group by cod,nome,citta,FT.fcod)
as TEMP
2) Contare le triplette distinte di citta associate alle forniture, cioè la tripletta della citta del fornitore,
della parte e del prodotto (ex MILANO, TORINO,ROMA,3). Produrre un risultato con il
seguente schema:
CITTA_FO
FIRENZE
MILANO
MODENA
MODENA
MODENA
PARMA
PARMA
PAVIA
ROMA
TORINO
VENEZIA
VENEZIA
VENEZIA
VENEZIA
CITTA_PA
TORINO
ROMA
MILANO
PAVIA
PAVIA
ROMA
ROMA
GENOVA
MILANO
MILANO
MILANO
PAVIA
ROMA
ROMA
CITTA_PR
MILANO
TORINO
PAVIA
FIRENZE
ROMA
FIRENZE
MILANO
TORINO
TORINO
ROMA
ROMA
TORINO
MILANO
TORINO
CONTEGGIO
1
3
1
1
1
1
1
1
1
2
1
1
1
1
Non considerare le permutazioni.
select f.citta as CITTA_FO,p.citta as CITTA_PA,pr.citta as CITTA_PR,count(*) as Conteggio
from fornitori F join forniture FT on F.cod=FT.fcod
join parti P on P.cod=ft.pcod
join prodotti PR on PR.cod=FT.prcod
where f.citta<>p.citta and p.citta<>pr.citta and pr.citta<>f.citta
group by f.citta,p.citta,pr.citta
order by f.citta,p.citta,pr.citta