Напредни извештаи од базата (SQL и складирани процедури)

===Извештај за квартално продадени автомобили и приход на едно застапништво (во овој случај БМВ М Кар), како и просечен месечен профит во тој период.

select
(
select count(a.a_id) from dealership as d
left join agreement as a on a.tax_nr=d.tax_nr
where (extract(month from a.datum) between 1 and 3)
and (extract(year from a.datum)=2023)
and d.d_name='BMW M-Kar'
) as first_quartal_sold_cars,
(
select sum(a.price) from dealership as d
left join agreement as a on a.tax_nr=d.tax_nr
where (extract(month from a.datum) between 1 and 3)
and (extract(year from a.datum)=2023)
and d.d_name='BMW M-Kar'
) as first_quartal_profit,
(
select count(a.a_id) from dealership as d
left join agreement as a on a.tax_nr=d.tax_nr
where (extract(month from a.datum) between 4 and 6)
and (extract(year from a.datum)=2023)
and d.d_name='BMW M-Kar'
) as second_quartal_sold_cars,
(
select sum(a.price) from dealership as d
left join agreement as a on a.tax_nr=d.tax_nr
where (extract(month from a.datum) between 4 and 6)
and (extract(year from a.datum)=2023)
and d.d_name='BMW M-Kar'
) as second_quartal_profit,
(
select count(a.a_id) from dealership as d
left join agreement as a on a.tax_nr=d.tax_nr
where (extract(month from a.datum) between 7 and 9)
and (extract(year from a.datum)=2023)
and d.d_name='BMW M-Kar'
) as third_quartal_sold_cars,
(
select sum(a.price) from dealership as d
left join agreement as a on a.tax_nr=d.tax_nr
where (extract(month from a.datum) between 7 and 9)
and (extract(year from a.datum)=2023)
and d.d_name='BMW M-Kar'
) as third_quartal_profit,
(
select count(a.a_id) from dealership as d
left join agreement as a on a.tax_nr=d.tax_nr
where (extract(month from a.datum) between 10 and 12)
and (extract(year from a.datum)=2023)
and d.d_name='BMW M-Kar'
) as fourth_quartal_sold_cars,
(
select sum(a.price) from dealership as d
left join agreement as a on a.tax_nr=d.tax_nr
where (extract(month from a.datum) between 10 and 12)
and (extract(year from a.datum)=2023)
and d.d_name='BMW M-Kar'
) as fourth_quartal_profit,
(
select sum(a.price)/12 from dealership as d
left join agreement as a on a.tax_nr=d.tax_nr
where d.d_name='BMW M-Kar'
) as average_monthly_profit;

Извештај за тоа во колку договори учествувало секое претставништво, излистани името на претставништвото, бројот на договори во кои учествувало, како и целосната сума, но само доколку вкупната сума на сите договори заедно е поголема од 2000е.

select
  d.d_name name,
  count(a.a_id),
  sum(amount)
from dealership d
left join agreement as a on d.tax_nr=a.tax_nr
left join payment as p on p.a_id=a.a_id
where amount>3000
group by d.d_name

Извештај за секој купувач кој купил камион или комбе од претставништвото, како и кое возило го купил, од кое претставништво било продадедно, за која сума, и од која банка било платено. Излистани се само оние договори кои поминале успешно, односно биле означени во системот како продадени, а не оние кои пропаднале заради ненавремено плаќање или недоволна сума. Да се групираат по претставништво прво, па потоа по клиент, возило, и банка.

select
  c.c_name as buyer,
  v.model as vehicle_bough,
  d.tax_nr as from_dealer,
  p.amount as price,
  p.bank as bank_name
from agreement a
left join dealership d on d.tax_nr=a.tax_nr
left join vehicle v on v.vin=a.vin
left join client c on c.embg=a.embg
left join payment p ON p.p_id=a.a_id
where
  (v.body='Truck' or v.body='Van') and
  a.status=true
group by
  d.d_name,
  d.tax_nr,
  c.c_name,
  v.model,
  p.amount,
  p.bank