Consider the database schema and the current state for the COMPANY relational database shown in Figure...
Fantastic news! We've Found the answer you've been seeking!
Question:
Transcribed Image Text:
Consider the database schema and the current state for the COMPANY relational database shown in Figure 1. Specify the following queries in SQL on the COMPANY database schema given. (a) Retrieve the names of all employees along with the number of dependents they are related to. (b) Retrieve the names of all employees have no projects to work on. (c) Retrieve a list of projects with average number of hours greater than 15 hours per week. Results should present project number along with the average number of hours for each project. (d) Find a list of departments that are controlling two projects or more. The result should list department number along with the number of projects relevant. EMPLOYEE Address Frame Minit Lname B Smith 1234567891965-01-09 731 Fondren, Houston, TX John Franklin T Wong Alicia 333445555 1955-12-06 638 Voss, Houston, TX J Zelaya 999887777 1968-01-19 3321 Castle, Spring, TX Jennifer S Wallace 987654321 1941-06-20 291 Berry, Bellaire, TX Ramesh K Narayan 666884444 1962-09-15 975 Fire Oak, Humble, TX Joyce A English 453453453 1972-07-31 5631 Rice, Houston, TX F Ahmad V Jabbar 987987987 1969-03-29 980 Dallas, Houston, TX James E Borg 888665555 1937-11-10 450 Stone, Houston, TX M M M DEPARTMENT Dname Research Administration Headquarters WORKS ON Essn 123456789 123456789 666884444 453453453 453453453 333445555 333445556 333445555 333445555 999887777 999887777 987987987 987987987 987654321 987654321 888665555 Dnumber 5 4 1 Pno Hours 1 32.5 2 7.5 3 40.0 1 20.0 2 20.0 2 10.0 10.0 10.0 10.0 3 10 20 30 10 10 Son 30 30 30.0 10.0 35.0 5.0 20.0 20 15.0 20 NULL Bdate Mgr son 333445555 987654321 888665555 DEPENDENT Esan Mgr start date 1988-05-22 1995-01-01 1981-06-19 PROJECT Prame 333445555 333445555 333445555 Joy 987654321 123456789 123456789 123456789 ProductX Product ProductZ Alice Theodore Sex Salary Super san Dno M 30000 333445555 M F F Dependent name Sex F Abner Michael Alice Elizabeth 40000 888665555 5 25000 987654321 4 43000 888665555 4 38000 333445555 5 25000 333445555 5 25000 987654321 4 55000 NULL 1 3 Computerization 10 Reorganization 20 Newbenefits 30 DEPT LOCATIONS Dnumber 1 4 5 Phumber 1 2 5 5 Dlocation Houston Stafford Belaire Bellaire Sugarland Houston Stafford Houston Stafford Sugarland Houston Plocation Dnum 5 5 5 4 1 4 Bdate Relationship 1906-04-05 Daughter 1983-10-25 Son M F M M 1988-01-04 Son F 1988-12-30 Daughter F 1967-05-05 Spouse 1958-05-03 Spouse 1942-02-28 Spouse Figure 1: A relational database schema for a COMPANY database Consider the database schema and the current state for the COMPANY relational database shown in Figure 1. Specify the following queries in SQL on the COMPANY database schema given. (a) Retrieve the names of all employees along with the number of dependents they are related to. (b) Retrieve the names of all employees have no projects to work on. (c) Retrieve a list of projects with average number of hours greater than 15 hours per week. Results should present project number along with the average number of hours for each project. (d) Find a list of departments that are controlling two projects or more. The result should list department number along with the number of projects relevant. EMPLOYEE Address Frame Minit Lname B Smith 1234567891965-01-09 731 Fondren, Houston, TX John Franklin T Wong Alicia 333445555 1955-12-06 638 Voss, Houston, TX J Zelaya 999887777 1968-01-19 3321 Castle, Spring, TX Jennifer S Wallace 987654321 1941-06-20 291 Berry, Bellaire, TX Ramesh K Narayan 666884444 1962-09-15 975 Fire Oak, Humble, TX Joyce A English 453453453 1972-07-31 5631 Rice, Houston, TX F Ahmad V Jabbar 987987987 1969-03-29 980 Dallas, Houston, TX James E Borg 888665555 1937-11-10 450 Stone, Houston, TX M M M DEPARTMENT Dname Research Administration Headquarters WORKS ON Essn 123456789 123456789 666884444 453453453 453453453 333445555 333445556 333445555 333445555 999887777 999887777 987987987 987987987 987654321 987654321 888665555 Dnumber 5 4 1 Pno Hours 1 32.5 2 7.5 3 40.0 1 20.0 2 20.0 2 10.0 10.0 10.0 10.0 3 10 20 30 10 10 Son 30 30 30.0 10.0 35.0 5.0 20.0 20 15.0 20 NULL Bdate Mgr son 333445555 987654321 888665555 DEPENDENT Esan Mgr start date 1988-05-22 1995-01-01 1981-06-19 PROJECT Prame 333445555 333445555 333445555 Joy 987654321 123456789 123456789 123456789 ProductX Product ProductZ Alice Theodore Sex Salary Super san Dno M 30000 333445555 M F F Dependent name Sex F Abner Michael Alice Elizabeth 40000 888665555 5 25000 987654321 4 43000 888665555 4 38000 333445555 5 25000 333445555 5 25000 987654321 4 55000 NULL 1 3 Computerization 10 Reorganization 20 Newbenefits 30 DEPT LOCATIONS Dnumber 1 4 5 Phumber 1 2 5 5 Dlocation Houston Stafford Belaire Bellaire Sugarland Houston Stafford Houston Stafford Sugarland Houston Plocation Dnum 5 5 5 4 1 4 Bdate Relationship 1906-04-05 Daughter 1983-10-25 Son M F M M 1988-01-04 Son F 1988-12-30 Daughter F 1967-05-05 Spouse 1958-05-03 Spouse 1942-02-28 Spouse Figure 1: A relational database schema for a COMPANY database
Expert Answer:
Answer rating: 100% (QA)
Following are the SQL Queries a select eSsn as Employee NoCONCATeF... View the full answer
Related Book For
Fundamentals Of Database Systems
ISBN: 9780133970777
7th Edition
Authors: Ramez Elmasri, Shamkant Navathe
Posted Date:
Students also viewed these databases questions
-
Consider the following schematic for a multiplexer-based implementation for a 3-input, 1-output basic Boolean function. Fill in the truth table and write the Boolean expression for Z? $1,50 0 11 (10...
-
Specify queries a, b, c, e, f, i, and j of Exercise 8.16 in both tuple and domain relational calculus. Exercise 8.16 Specify the following queries on the COMPANY relational database schema shown in...
-
Specify the following queries on the COMPANY relational database schema shown in Figure 5.5 using the relational operators discussed in this chapter. Also show the result of each query as it would...
-
client with celiac disease asks for a nutrition plan, what should the trainer recommend? Refer to a dietician 3-day food log to prescribe meal plan A templated meal plan Give a breakdown of...
-
Consider three-dimensional, incompressible, irrotational flow. Use two methods to prove that the viscous term in the Navier-Stokes equation is zero: (a) Using vector notation; and (b) Expanding out...
-
Armina established a service business to be known as Armina Photography on July 1, 2015. During the first month, the following transactions occurred: July 1- Amina transferred cash of P300,00 from...
-
The following data available for four stroke petrol engine : bore \(=20 \mathrm{~cm}\) stroke to bore ratio \(=\) 1.5 , speed \(=350 \mathrm{rpm}, \mathrm{p}_{\mathrm{m}}=2.75 \mathrm{bar}\), net...
-
Spencer, CPA, has been engaged to audit the fair value measurements of Christensen & Son, a high- tech company in the Midwest. During the audit, Spencer must obtain evidence that management of...
-
Find resources that confirm the importance of budgeting to organizations and answer the question below. Using your work experience think about how budgets can influence the choices made by top...
-
When buying or leasing a new car, one of the factors that customers consider is the type of fuel it uses. Some people prefer vehicles that use diesel fuel, while others favor vehicles that use...
-
5. Consider the titration of a 25.0 mL sample of 0.100 M in NH3 (Kb = 4.4 10-4) with 0.100 M HCl. a) Calculate the pH after adding 5.00 mL of the titrant. b) Calculate the pH at the equivalence...
-
Sports Memorabilia Pty Ltd (SMP) is an auction house for sporting items. Waleed and Declan are the directors of SMP. Waleed's Uncle Silvester is an investor and has a 20% holding in SMP. Waleed and...
-
QUESTIONS 1. Should he risk the account by submitting only the rescue mission ad? Or should he let Teal also see the ad that showed the helicopter shooting up the village, knowing that he would...
-
Why does Justice Murray Sinclair have a love/hate relationship with the Royal Proclamation of 1763?
-
1 . A company purchases new cement manufacturing assets that cost $ 1 8 million. This is classified in the 1 5 - year property class using MACRS - GDS . What would be the depreciation allowance at...
-
Demonstrate your knowledge and skills in constructing an eco-map to analyze important connections between systems in one's social environment, using yourself as the focal system. Directions: Create...
-
Jones company has a target capital structure of 40% debt 10% preferred stock and 50% common equity. The company cost of debt is 8%. Its cost of prefeered stock is 10%. Its cost of retained earnings...
-
Using the information in P11-2B, compute the overhead controllable variance and the overhead volume variance. Data From Problem 11-2B: Huang Company uses a standard cost accounting system to account...
-
Describe the execution workflow of the MapReduce programming environment.
-
Using the DBD system, verify your answers to the following exercises: a. 14.24 (3NF only) b. 14.25 c. 14.27 d. 14.28 Exercise 14.24 Consider the universal relation R = {A, B, C, D, E, F, G, H, I, J}...
-
What are covert channels? Give an example of a covert channel.
-
One of the main fears that retail startups have is if they will be able to compete against Walmart, Home Depot, and the other big-box stores. This is a legitimate fear. Big-box stores continue to...
-
Influencer marketing is a type of marketing where companies partner with influencers, who have a significant following on social media platforms, to promote their products or services. Influencers...
-
In the summer of 2015, Stephen Kuhl and Kabeer Chopra made their way to Philadelphia to prepare for business school. It was their first semester in the Wharton MBA program. They were both in an...
Study smarter with the SolutionInn App