7 Replies Latest reply: Apr 2, 2013 6:21 AM by 1000105 RSS

    Queries

    1000105
      n

      Edited by: 997102 on Apr 2, 2013 4:20 AM
        • 1. Re: Queries
          636309
          Hi,
          I wasn't clear about the statement below. You were asked to create an ER diagram, but the link you've provided already shows an ER diagram. Could you please clarify?
          My lecturer asked me to create a Data warehouse ER Diagram
          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.
          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
          • 2. Re: Queries
            Etbin
            Maybe - (for Q1 - clarifications needed for the other two)
            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)
            Regards

            Etbin

            Edited by: Etbin on 30.3.2013 19:39
            The upper post suggests I missed the country
            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'
            • 3. Re: Queries
              1000105
              n

              Edited by: 997102 on Apr 2, 2013 4:21 AM
              • 4. Re: Queries
                1000105
                n

                Edited by: 997102 on Apr 2, 2013 4:21 AM
                • 5. Re: Queries
                  Etbin
                  I had to go away and havent check the replies - having written another one I think posting it will do no harm
                  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
                         )
                  Regards

                  Etbin
                  • 6. Re: Queries
                    Etbin
                    Maybe
                    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
                    Regards

                    Etbin
                    • 7. Re: Queries
                      1000105
                      n

                      Edited by: 997102 on Apr 2, 2013 4:21 AM