Two subqueries work fine individually but "SQL command not properly ended" when join by except [Explain like I'm five]

Question

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

Answer 1

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