Question

Consider the following relational schema (the primary keys are underlined and foreign keys are italic) ITEM(ItemName, ItemType, ItemColour) DEPARTMENT(Deptname, DeptFloor, DeptPhone, Manager) EMPLOYEE(EmpNo, EmpFname, EmpSalary, DeptName, SupervisedBy) SUPPLIER(SupNo, SupName) SALE(SaleNo, SaleQty, ItemName, DeptName) DELIVERY(DeliNo, DeliQty, ItemName, DeptName, SupNo) Write the SQL statements for the following queries: C1. Find the names of items sold on first and second floors. [1 mark] C2. For each department, list the department name and average salary of the employees where the average salary of the employees is great than $28,000. [1 mark] C3. List the name and salary of the managers with no more than 10 employees. [2 marks] C4. List the names of the employees who earn more than any employee in the Deliver department. [2 mark

Answer 1

SOLUTION :

1.

Select Item.ItemName from Item inner join Sale on Items.ItemName = Sale.ItemName inner join Department on Sale.DeptName = Department.DeptName where DeptFloor = 1 or DeptFloor = 2;

2.

Select Department.DeptName , Avg(EmpSalary) from Department inner join Employee on Department.DeptName = Employee.DeptName group by Department.DeptName having Avg(EmpSalary) > 28000;

3.

Select M.EmpFname, M.EmpSalary from Employee M inner join Employee E on M.EmpNo = E.SupervisedBy group by M.EmpFname, M.EmpSalary having Count(E.EmpNo) <= 10;

4.

Select EmpFname from Employee where EmpSalary > Any (Select EmpSalary from Employee where DeptName = 'Delivery');