Can you help me check my answers whether is this the right way? Im really new to database

This is the question

This is my answer

Create View totalsales AS
select inv.InvoiceDate, po.ProdCode, po.ProdName, sum(il.Quantity), sum(il.SellingPrice)
from Invoice inv, Product po, InvoiceLine il
where inv.InvoiceNo = il.InvoiceNo and po.ProdCode = il.ProdCode and inv.InvoiceDate between ('01-01-2021','dd-mm-yyyy') AND ('31-03-2021','dd-mm-yyyy')
group by inv.InvoiceDate, po.ProdCode, po.ProdName, il.Quantity, il.SellingPrice
order by sum(il.Quantity) DESC
WITH READ ONLY CONSTRAINT totalsales;

The reason i use with read only constraint was to enhance the security!

CodePudding user response：

The way I see it, query would look like this:

CREATE VIEW totalsales AS
    SELECT
        po.prodcode,
        po.prodname,
        SUM(il.quantity) sum_quantity,
        SUM(il.sellingprice) sum_sellingprice
    FROM
             invoice inv
        JOIN invoiceline il ON il.invoiceno = inv.invoiceno
        JOIN product     po ON po.prodcode = il.prodcode
    WHERE
        inv.invoicedate BETWEEN DATE '2021-01-01' AND DATE '2021-03-31'
    GROUP BY
        po.prodcode,
        po.prodname
WITH READ ONLY;

If compared to yours:

• don't select columns you don't need (invoicedate) as it affects the final result because you then have to include it into the group by clause so you'd get values "split" per each invoice date as well (and that's not what you want)

• properly JOIN tables (i.e. don't include them in the FROM clause separated by a comma, and then don't join them in the WHERE clause - put conditions in there

• condition is related to invoice dates; your code is missing the TO_DATE function, e.g. between to_date('01-01-2021','dd-mm-yyyy'). In this case, I'd suggest you to use date literal which is always in format date 'yyyy-mm-dd'

• with read only only; nothing else

• sorting within a view? That doesn't make much sense (in my opinion). Just create it, and then sort values when you select from the view, e.g.

  select prodname, sum_quantity
  from totalsales
  order by sum_quantity desc
  

CodePudding user response：

May be you consider to create view like this:

   Create View totalsales AS
    select po.ProdCode, po.ProdName, 
     sum(il.Quantity), sum(il.SellingPrice*il.Quantity)
    from Invoice inv, Product po, InvoiceLine il
    where po.ProdCode = il.ProdCode( )  
     and il.InvoiceNo = inv.InvoiceNo( )  
      and trunc(inv.InvoiceDate( ),'Q') = date'2021-01-01'
    group by po.ProdCode, po.ProdName
    order by sum(il.Quantity) DESC
    WITH READ ONLY

It shall output all products. But for products not been sold in 1st quarter of 2021, aggregates will be null. If you need only products been sold in 2021 - remove all ( ) from predicates.

I also wrote sum(il.SellingPrice*il.Quantity) for total sales, because I'm not sure that sellingPrice is a price for all the quantity, not just for one unit. If sellingprice is a price for all units, then just replace sum(il.SellingPrice*il.Quantity) with sum(il.SellingPrice).

Andrew.