query ra

/* selezionare targa, nome modello, nome fabbrica
di tutti i veicoli non prodotti da fiat */

select targa, cod_modello, nome_modello, nome_fabbrica
from veicoli
Inner Join modelli USING (cod_modello)
Inner Join fabbriche USING (cod_fabbrica)
where UPPER(nome_fabbrica) <> 'FIAT'
order by targa;

/* per ogni fabbrica visualizzare nome fabbrica,
cilindrata massima, velocità massima, cavalli fiscali massimi,
dei veicoli da essa prodotti. Ordinare per cavalli fiscali decrescenti */

select
    nome_fabbrica,
    max(cilindrata) as "Cilindrata massima",
    max(velocita) as "Velocita' massima",
    max(cavalli_fiscali) as "Cavalli fiscali massimi"
from fabbriche
inner join modelli using (cod_fabbrica)
inner join veicoli using (cod_modello)
group by nome_fabbrica
order by max(cavalli_fiscali) desc;

/* trovare i proprietari che hanno venduto veicoli
cognome, nome, targa veicolo,data acquisto e cessione,
giorni e anni di possesso
*/

select
    cognome,
    nome,
    targa,
    data_acquisto,
    data_cessione,
    (data_cessione - data_acquisto) as "Giorni",
    round(((data_cessione - data_acquisto) / 365), 1) as "Anni"
from proprietari
inner join proprieta using (cod_proprietario)
where data_cessione is not NULL;

/* Trovare i proprietari delle province di Roma e Milano che possiedono attualmente
veicoli FIAT, e voglio nome, cognome, nome fabbrica, nome modello, cilindrata, targa */

select
    nome,
    cognome,
    nome_fabbrica,
    nome_modello,
    cilindrata,
    targa
from proprietari
inner join proprieta using (cod_proprietario)
inner join veicoli using (targa)
inner join modelli using (cod_modello)
where upper(provincia) = 'RM' or upper(provincia) = 'MI'
and data_cessione is null and (upper(nome_fabbrica = 'FIAT');

/* Quanti sono i proprietari che hanno posseduto o possiedono i veicoli suddivisi per fabbriche.
Cercare nome fabbrica e numero proprietari che hanno posseduto veicoli di quella fabbrica. */

select
    nome_fabbrica,
    count(cognome) as "Numero proprietari"
from proprietari
inner join proprieta using (cod_proprietario)
inner join veicoli using (targa)
inner join modelli using (cod_modello)
inner join fabbriche using (cod_fabbrica)
group by nome_fabbrica
order by nome_fabbrica desc;

/* Trovare targa, modello, fabbrica, dei veicoli che non usano benzina senza piombo
e che sono stati costruiti da fabbriche diverse da FIAT */

select
    targa,
    descrizione_combustibile,
    nome_modello,
    nome_fabbrica
from combustibili
inner join veicoli using (cod_combustibile)
inner join modelli using (cod_modello)
inner join fabbriche using (cod_fabbrica)
where upper(descrizione_combustibile) <> '%benzina senza piombo' and upper(nome_fabbrica) <> 'FIAT'
order by targa;

/* targhe veicoli costruiti da fabbriche
che non sia HONDA eccetto quelle dei veicoli
possedute da proprietari che abitano in provicinia
di bolzano ob ergamo */

select targa
from veicoli
inner join modelli using (cod_modello)
inner join fabbriche using (cod_fabbrica)
where upper(nome_fabbrica) is not "Honda"

/* trovare dei veicoli che usano combustibile
benzina super, e che appartengono a modelli con
più di due versioni. */

select targa
    from veicoli
    inner join combustibili using (cod_combustibile)
    where upper(descrizione_combustibile) = 'BENZINA SUPER'
intersect
select targa
    from veicoli
    inner join modelli using (cod_modello)
    where numero_versioni > 2;

select cilindrata from veicoli
where cod_combustibile = '02';



/* selezionare veicoli con cilindrata superiore
alla cilindrata media */

select * from veicoli
where cilindrata =
(select avg(cilindrata) from veicoli);

 /* targa e descr. combustibile dei veicoli+
cui proprietari risiedono in provincia di
Pisa e Firenze */

select targa, descrizione_combustibile
from veicoli
inner join combustibili using (cod_combustibile)
where targa in
(select targa from proprieta
inner join proprietari using (cod_proprietario)
where upper(provincia) in ('PI', 'FI'))
order by targa;

-- Craere una vista con targhe, date acquisto cessione, cognome, nome dei proprietari,
-- indirizzo e province, per le province diverse da Roma.


CREATE OR REPLACE VIEW V1 (V1_Targa, V1_Data_acquisto, V1, Data_cessione, V1_Cognome, V1_Nome,
V1_Indirizzo, V1_Provincia)
AS
SELECT targa, data_cessione, data_acquisto, cognome, nome, indirizzo, provincia
FROM veicoli
INNER JOIN proprietari USING (cod_proprietario)
INNER JOIN proprieta USING (targa)
WHERE UPPER(provincia) <> 'RM';


SELECT * FROM V1;