Calculate total units sold and total sales value

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!

Answer

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