Database Theory Question

[guidely]

Hi, I currently studying computer science 1st year and I have trouble with practice exercise because I got exam tomorrow can you guys help me Here is my question:

I have done some sql query am not sure It correct or not

ITEM(INO integer, INAME: varchar(10), DEPT: integer, PRICE: float, QTY:integer, COST:float)
EMP(ENO: integer, ENAME: varchar(15), SALARY: integer, COMM: integer, DEPT: integer)
DEPT(DNO: integer, DNAME: varchar(10), FLOOR: integer, MANAGER: integer)
SALE(INO integer,ENO: integer,STIME: date, QTY: integer, PRICE: float)

1) Retrieve the item number, item name and total sale value of all the item sold in the current year ( sale value is the product of QTY and Price of sale)

Ans
Select INO, INAME, SUM(QTY + PRICE) As totalSale
from Item, SALE
Where sale.STIME = (select to_char(systdate, 'YYYY') STIME
from SALE
)

2) Find the employee who receives the highest commission in each department. Display the DNO, ENAME and COMM

Ans
select DNO, ENAME, COMM
from EMP, DEPT
where EMP.COMM = ( select MAX(COMM), b.DNO
from EMP, DEPT b, DEPT A
Where b.DON = a.DNO
)

3) Find the department that offer less than ten different items. Display DNO, DNAME, floor and number of item
ANS
select: DNO, DNAME, FLOOR, SUM(QTY) as NumItem
from: DEPT, ITEM
where: DEPT < 10

Thank

[Taywin]

They are likely to be wrong because you are not supposed to have 2 "where" clauses in the same query (without grouping or certain keywords) I don't know the syntax from the top of my head here. But I will tell you what should be done...

#1 - You may need a joint table command here. You need to first retrieve all the SALE data in the current year and use the retrieved unique INO values to retrieve from the ITEM.

#2 - Same idea. You retrieve the data of highest commission of each department from EMP and use the ENO of the data to joint with the DEPT.

#3 - Same idea. First you need use count (not sum) the number of data - "count (*) as totalItem" in ITEM using "group by DEPT" and "having < totalItem 10" in order to retrieve all the DEPT values (or DNO in DEPT) used in joint with DEPT.