Write an SQL query to list the branch number, staff name and salary .. (SQL)?

Question

Question A-
TABLE - http://i53.tinypic.com/zn8yns.jpg

You are required to write the appropriate CREATE TABLE and ALTER TABLE statements to implement the tables above.

You must ensure the following:

i. All data types are accurately defined;

ii. All primary key and foreign key constraints are accurately defined and labelled.
Answer to question A -

I am pretty sure this is the correct answer:

CREATE TABLE Branch
(
BranchNo smallint,
Address char(50),
TelNo char(20),
PRIMARY KEY (BranchNo),
Mgr_StaffNo smallint FOREIGN KEY REFERENCES Staff(StaffNo)
)

CREATE TABLE Staff
(
StaffNo smallint,
StaffName char(50),
Salary decimal(7,2),
PRIMARY KEY (StaffNo),
BranchNo smallint FOREIGN KEY REFERENCES Branch(BranchNo)
)

Question B-

TABLE - http://i53.tinypic.com/zn8yns.jpg

Assume the tables you’ve implemented in a) have been populated with some realistic data. You are now required to do the following:
Write an SQL query to list the branch number, staff name and salary for those members of staff who earn higher than the average salary for their branch; sort by branch number.


IM stuck on question b, please Help (best answer will get 10 points :D)
Thanks in advance

Answer 1

Your query could be something like:



SELECT BranchNo, StaffName, salary FROM Staff a WHERE salary >

(SELECT AVG(salary) FROM Staff b WHERE b.BranchNo = a.BranchNo)

ORDER BY BranchNo



Note that qualifiers as to table are only necessary in the subquery since there is no confusion which Staff table is being referenced in the main query. However, this is somewhat inefficient since the average would be recomputed for every row of the Staff table. A far better method:



SELECT BranchNo, StaffName, salary FROM Staff JOIN

(SELECT BranchNo AS AvgBranch, AVG(salary) AS AvgSalary

FROM Staff GROUP BY BranchNo) ON BranchNo = AvgBranch

WHERE salary > AvgSalary ORDER BY BranchNo

Answer 2

I truly hope your professor didn't write these tables. You have what sounds like a one to many relationship (one branch for many staff members)... table: staff attributes: name, membership, etc. table: structure attributes: branch, etc. So, that would look like staff -> structure (which means there's no purpose for the Mgr_StaffNo field) If someone can be a part of more than one branch (i.e. many to many relationship), it's ... table: staff attributes: name, etc. table: membership (this is a connector table) attributes: name, branch table: structure attributes: branch, etc. In this case, it looks like staff <- membership -> structure I'm sorry I didn't give you a direct SQL query, but what you gave for a) makes it hard to provide a solid query.

Answer 3

Select

Staff.BranchNo

,Staff.StaffName

,Staff.Salary



from Staff, (select count(*) as Cnt from staff) a, (select sum(salary) as sm from staff) b

where Staff.Salary > b.sm/c.cnt;