I took a look at lots of similar posts but I still couldn't find a similar case or I am too beginner to understand the commands in the questions in the first place.
The command I wrote but returning ORA-00933: SQL command not properly ended:
(select product.productid, productname, productprice
from product, soldvia
where product.productid = soldvia.productid
group by product.productid, product.productname, product.productprice
having sum(soldvia.noofitems) > 3 )
except
(select product.productid, productname, productprice
from product, soldvia
where product.productid = soldvia.productid
group by product.productid, product.productname, product.productprice
having count(soldvia.tid) > 1);
When I run the individual select commands, they run fine and return the expected results.
Edit: I am learning EXCEPT using this command, therefore, I must use that command to accomplish the task.
The task is:
Retrieve the product ID, product name, and product price for each product that has more than three items sold within all sales transactions but whose items were not sold in more than one sales transaction
except is not a thing in Oracle; the equivalent keyword is minus: your query should just work if you change the keyword.
On the other hand, both queries are quite identical, so you could just merge the having clauses:
select p.productid, p.productname, p.productprice
from product p
inner join soldvia s on p.productid = s.productid
group by p.productid, p.productname, p.productprice
having sum(s.noofitems) > 3 and count(s.tid) <= 1
Notes:
always use standard, explicit joins (with the on keyword) rather than old-school, implicit joins (with a comma in the from clause): this old syntax should not be used in new code
table aliases make the query easier to write and read
in a multi-table query, always qualify all column names with the table they belong to, so the query is unambiguous and easier to understand