question

Michael 4 avatar image
Michael 4 asked

How many hours did consultant Janet Park work in each of her projects

CONSULTANT
C_ID    C_LAST  C_FIRST C_MI    C_ADD   C_CITY  C_STATE C_ZIP   C_PHONE C_EMAIL
100 Myers   Mark    F   10 Sky Blvd.    Orlando FL  32825   4075551212  mm@xx.com
101 Hernandez   Sheila  R   33 Left Cr. Orlando FL  32811   4071115555  sh@yyx.com
102 ZHANG   Brian       89 SE Ave.  Tampa   FL  32822   3211515555  bz@mm.org
103 Carlson Sarah   J   22 NW Pl.   Orlando FL  32825   4075111111  sc@uu.gov
104 Courtland   Paul    R   91 Water St.    Orlando FL  32800   4075151155  pc@pp.net
105 Park    Janet   S   15 High Rd. Miami   FL  32816   9541115555  jp@hr.net

PROJECT_CONSULTANTS
P_ID    C_ID    ROLL_ON_DATE    ROLL_OFF_DATE   TOTAL_HOURS
1   101 06/15/2008  12/15/2008  175
1   104 01/05/2008  12/15/2008  245
1   103 01/05/2008  06/05/2008  50
1   105 01/05/2008  12/15/2008  45
2   105 07/17/2008  09/17/2008  25
2   100 07/17/2008  09/17/2002  0
3   103 09/15/2008  03/15/2009  125
3   104 10/15/2008  12/15/2008  50
4   105 06/05/2008  06/05/2009  25
4   104 06/15/2008  12/15/2008  125
4   102 07/15/2008  12/15/2008  30
5   105 09/19/2008  03/19/2009  15
5   103 09/19/2008  03/19/2009  15
6   103 09/19/2008  03/19/2009  5
6   104 09/19/2008  03/19/2009  10
7   102 05/20/2008  12/20/2008  125
7   100 05/25/2008  12/20/2008  100
queryhomework
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
user-1392 avatar image
user-1392 answered

Your question could be better laid out, but something along the lines of:

select c.c_id, p_id, sum(total_hours) as total_hours
from consultant c
    join project_consultants p on p.c_id = c.c_id
where c.c_id = 105         -- or c.c_first = 'Janet' etc?
group by c.c_id, p.p_id

Dave.

10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.