--- Marche 
create or replace table faq400.AutoMarche (
idMarca  Integer   Generated always as Identity,
Marca char(30),
Paese char(2),
primary key(idMarca) 
);
insert into faq400.AutoMarche ( Marca, Paese)
values
( 'FCA Fiat Chrysler Automobilies', 'IT'),
( 'Volkswagen', 'DE'),
( 'BMW', 'DE'),
( 'FORD', 'US'),
( 'Cytroen', 'FR'),
( 'Renault', 'FR'),
( 'Toyota', 'JP')
;

select * from faq400.Automarche;


--- Modelli  
create or replace table faq400.AutoModelli (
idModello  Integer   Generated always as Identity,
modello char(30),
idMarca integer constraint faq400.Check_Marca
                    references faq400.AutoMarche (idMarca) on delete cascade,
tipo    char(30),
primary key(idModello)
);

insert into faq400.AutoModelli ( Modello, idMarca, tipo)
values
( 'Panda', 1, 'Utilitaria'),
( '500X', 1, 'Crossover'),
( 'Fiesta', 4, 'Utilitaria'),
( '500', 1, 'Utilitaria'),
( 'Tipo', 1, 'Berlina'),
( 'C3', 5, 'Utilitaria'),
( 'Lancia Y', 1, 'Utilitaria'),
( 'Clio', 6, 'Utilitaria'),
( '500L', 1, 'Monovolume'),
( 'Yaris', 7, 'Utilitaria')
;

--- Statistico

create or replace table faq400.AutoVenduto (
idRec  Integer   Generated always as Identity,
IdModello integer constraint faq400.Check_Modello
                    references faq400.AutoModelli (idModello) on delete cascade,
Anno      integer,
Venduto   integer);

insert into faq400.AutoVenduto ( idModello, anno, venduto)
values
( 1, 2018, 11201),
( 2, 2018, 5734),
( 3, 2018, 5388),
( 4, 2018, 5263),
( 5, 2018, 5215),
( 6, 2018, 4829),
( 7, 2018, 4250),
( 8, 2018, 3945),
( 9, 2018, 3902),
( 10, 2018, 3725)
;


SELECT a.Marca, Paese, Modello, Anno, Venduto FROM Faq400.AutoMarche A cross JOIN
  LATERAL (SELECT * from faq400.AutoStatistica stat
           WHERE Anno=2018 and A.idMarca=stat.IdMarca
     order by Venduto desc
     FETCH FIRST 1 ROW ONLY
  ) B
;





SELECT a.Marca, Paese, Modello, Anno, Venduto FROM Faq400.AutoMarche A left JOIN
  LATERAL (SELECT * from faq400.AutoStatistica stat
           WHERE Anno=2018 and A.idMarca=stat.IdMarca
     order by Venduto desc
     FETCH FIRST 1 ROW ONLY
  ) B
on a.idmarca = b.IdMarca
;

create or replace view faq400.AutoStatistica as 
SELECT venduto.idrec     as idrec,
       venduto.idmodello as idmodello,
       venduto.anno      as anno,
       venduto.venduto   as venduto,
       modelli.modello   as modello,
       modelli.tipo      as tipo,
       modelli.idmarca   as idmarca,
       marche.marca      as marca
        from Faq400.AutoVenduto venduto
         join Faq400.AutoModelli modelli on venduto.idmodello=modelli.idmodello
         join Faq400.AutoMarche marche on marche.idmarca=modelli.idmarca 
   
     ;