While making a few assumptions about the ERD, I came up with the query below for the first question. I assumed that the product_id was the same as type of property. I also assumed that the organisation table stored the country information. I wasn't able to get to the other 2 questions yet.
My lecturer asked me to create a Data warehouse ER Diagram
select product_id, sum(selling_price) from purchase p, time t, organisation o where p.time_id = t.time_id and t.year = 2003 and p.organisation_id = o.organisation_id and o.country = 'Great Britain' group by product_id
select pr.type,sum(pu.sales_revenue) total_revenue from purchase pu, product pr. time t where pu.product_id = pr.product_id and pu.time_id = t.time_id and t.year = 2003 group by rollup(pr.type)
select pr.type,sum(pu.sales_revenue) total_revenue from purchase pu, product pr. time t, branch b where pu.product_id = pr.product_id and pu.branch_id = b.branch_id and pu.time_id = t.time_id and t.year = 2003 and b.country = 'GREAT BRITAIN'
select b.region, round(100 * projected sales / original_sales,2) percentage_effect from (select b.region, sum(pu.sales_amount) original_sales sum(1.035 * pu.sales_amount) - sum(case when pu.sales_amount > 100000 then 0.015 end * pu.sales_amount ) projected sales from purchase pu, branch b time t where pu.branch_id = b.branch_id and pu.time_id = t.time_id and b.country = 'GREAT BRITAIN' and t.year = :year and t.month = :month group by b.region )
select channel_name, country, region, city, total_duration from (select a.television_channel_id, max(channel_name) channel_name, max(country) country, max(region) region, max(city) city, sum(duration) total_duration from advertisment a, time t, tv where a.time_id = t.time_id and a.television_channel_id = tv.television_channel_id and t.year = :year group by a.television_channel_id order by total_duration desc ) where rownum <= :top_n_by_duration