Display all the columns in Project for those projects that have a total hours_wo
ID: 3601598 • Letter: D
Question
Display all the columns in Project for those projects that have a total hours_worked that
exceeds 100.
The issue is that to get to the hours table with the total_hours worked, I need to go through the Assignment table which has a Primary key eid that is also found in the Hours table. I have to use the SUM(total_hours) > 100 but I keep getting an aggregate error. I'm about ready to throw the laptop at the wall!!! I'm using an Access database to do the SQL queries. Also, I can't use any joins in the query, it has to be done with selects and sub queries only!
, c. Asg1 : Database-DAClass!410Fall2017 Asg1.ac RELATIONSHIP TOOLS FILE HOME CREATE EXTERNAL DATA DATABASE TOOLS DESIGN n i Hide Table XClear Layout Direct Relationships Edit Relationships Relationship Report Show Table All Relationships Tools Relationships SECURITY WARNING Some active content has been disabled. Click for more details. Enable Content Relationships Title Employee code name min salary max_salary tcode dcode ssn last_name first name middle init Assignment eid pcode start end hours_per week Hours Department code name fill_level salary budget eid pcode monday hours worked salary Project name dcode start estimated hours Ready NUM LOCKExplanation / Answer
Query: select * from Project where code in (select pcode from Hours where hours_worked > 100);
Exxplanation: there is aforeign key relationship ship bwtween project and assignment table and assignement tab le and hours table. Which indirectly implies the code column in project table and pcode column in hours table indicates the same column. So directly i have used this relation ship in the above query. The sub query will fetch all the rows who have worked more than 100 hours now main query will filter the rows obtained in the mai n query with the help of rows obtained in the sub query. Hours table will defnitely have rows related to project table as the indirect relationship between these three tables exist.