Answered step by step
Verified Expert Solution
Question
1 Approved Answer
Question 2: SQL (40 points) Use the following database to answer all parts of this question: Table: EMP Primary Key: E NO E NO E
Question 2: SQL (40 points) Use the following database to answer all parts of this question: Table: EMP Primary Key: E NO E NO E NAME E RATE $400.00 $200.00 $150.00 $150.00 $120.00 $100.00 $100.00 $50.00 $50.00 $50.00 $150.00 E DEPT 10 Table: PART Primary Key: P NO P NO P NAME 1P CITY NY NY P1 P2LA P3 P4 P5 P6 SF LA NY 4. Table: PROJECT Primary Key: PR_NO PR NO PR LOC NY PR MGR PR DEPT NY LI Table: SUPPLIER Primary Key: S NO S NAME S1 S2 S3 S NO S LOC NY NY LA Table: SUPPLY Primary Key: P_NO +PR NO+ S NO Foreign Key: P_NO references PART Foreign Key: PR NO references PROJECT Foreign Key: S NO references SUPPLIER P NO PR NO S NO OTY 112 113 121 122 123 131 132 133 211 311 411 511 611 2 Table: WORK Primary Key: E NO + PR_NO Foreign Key: E NO references EMP Foreign Key: PR_NO references PROJECT E NO 2 HRS 10 20 PR NO 20 20 20 10 10 For questions 1 thru 4 show the Queries and the results. (All parts carry equal points). Use the same table names, field names, and data as presented above. Questions 1. Compute the amount to be billed to each project as "Amount". The amount for each project is computed as the sum of the amount billed to the project by all employess working on the project. The amount billed by an employee to a project is obtained as the product the hours (HRS) that the employee devotes to the project and the billing rate (E_RATE) for the employee. 2. Compute the total revenue generated by each department as "Revenue". The Revenue for each department is computed as the sum of the amount billed to all projects controlled by the department. (Note that the PR DEPT for a project represents the controlling department for the project). List the names of employees who only work in projects located (PR LOC) in New 4. List the name of employees who work on all projects
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started