how to get the query based on this requirement？

Question

hello may i know the sql query based on the link images's requirement? can everyone give me the answers?

can everyone check mine answers correct ?

select 
products.name, 
products.total, 
invoiceLine.sum(quantity), 
invoiceLine.sellingprice*invoiceLine.quantity AS total_price 
from products,invoiceLine,invoice 
where products.prodCode=invoiceLine.prodCode and 
invoice.invoiceDate>= add_months(sysdate, -6)
ORDER BY quantity DESC;

Answer 1

As this is obviously a homework/tutorial question I won't just give you the answer - as that way you won't learn anything - but I will give you some more detailed help. You will still need to do your own research/investigation to be able to write the correct answer yourself:

ANSI JOIN SYNTAX:

FROM TABLE1 T1
INNER JOIN TABLE2 T2 ON T1.COL1 = T2.COL2
LEFT OUTER JOIN TABLE3 T3 ON T2.COL1 = T3.COL2
...

SUM

SUM function goes round the full column name e.g.

sum(invoiceLine.quantity)

not

invoiceLine.sum(quantity)

When using an aggregate function (SUM, COUNT, AVG, etc.) all the columns in your SELECT statement, that don't use aggreagete functions, need to be included in a GROUP BY clause

DATE

The question asks for records in the "first 3 months of 2021"; your code is selecting records in the past 6 months

SORT

The question ask you to sort by the total units sold; you are sorting by "quantity" which is the individual quantity for each record, not total units sold