Question: only need the answer, without explanation. Q3. Given the following relations of a staff project system and the database schema with sample data found in

only need the answer, without explanation.

only need the answer, without explanation. Q3.only need the answer, without explanation. Q3.only need the answer, without explanation. Q3.
Q3. Given the following relations of a staff project system and the database schema with sample data found in the Appendix on page 9. Staff (StaffNo, Name, Gender, DOB, Address, Tel) Project (ProjectNo, Title, ProjectDate, ProjectAmount) Salary (StaffNo, ProjectNo, Commission, CommissionRate) where the underlined attribute(s) is/are primary key attribute(s) (a) The commission is calculated according to the formula as shown below: Commission = project amount x commission rate. Explain whether it is necessary to keep the data for the column commission in the table Salary [2%] Answer: Unless otherwise specified, write ONE single SQL statement for each part from Q3(b) to Q3(i). (b) Find all staff without their addresses recorded. Show all the details of the staff. [2%] Answer: (c) Find all projects that were held in July. Show the project number, title and the project date. [3%] Answer: (d) Find all male staff who were born in year 1991or before. Show the staff number, name and date of birth. [2%] Answer:(e) Find all staff who worked on the project(s) with commission rate not equal to 0.02. Show the staff number, staff name, gender, project number and commission rate. [4%] Answer:(f) Show the project number and the highest commission rate for each project. Sort the result in descending order by the highest commission rate. [5%] Answer: (g) Find the project with 3 or more staff worked on it. Show the project number, project date and total number of staff working on this project. [8%] (Note: Generate the output as shown in the sample output below.) Sample output : PROJECTNO PROJECTDATE Total number of staff P01 17-JUL-14 Answer: (h) Write TWO SQL statements to remove the project P02 and its associated salary records. [2%] Answer: (i) Remove the table Salary and all its constraints. [2% ]

Step by Step Solution

There are 3 Steps involved in it

1 Expert Approved Answer
Step: 1 Unlock blur-text-image
Question Has Been Solved by an Expert!

Get step-by-step solutions from verified subject matter experts

Step: 2 Unlock
Step: 3 Unlock

Students Have Also Explored These Related Accounting Questions!