maclem8223
[H]ard|Gawd
- Joined
- Oct 28, 2013
- Messages
- 1,849
Wasn't sure where to put this. I'm learning SQL and I've got a practice problem I'm stuck. Having trouble fully understanding sub-queries and joins. Basics of tables.
STORE
PK - STORE_NUM
STORE_NAME
EMPLOYEE
PK-EMP_NUM
FK-STORE_NUM
WORKS
PK-EMP_NUM
PK-PROJ_NUM
WORK_HOURS
Employees can only belong to one store and employees can bill many hours. The question ask to display the STORE_NUM, and STORE_NAME that has billed the most hours. Below is my query that doesn't fully work or make sense to me, but it is where I'm at. Having trouble grasping how to pull information across tables I guess. This is in oracle by the way, any help would be appreciated. If more info is needed please let me know. Just looking for some insight and a push in the right direction. Thanks in advance.
SELECT STORE_NUM, STORE_NAME
FROM STORE JOIN EMPLOYEE USING(STORE_NUM)
JOIN (SELECT EMP_NUM, Max(WORK_HOURS) MAXHRS
FROM WORKS GROUP BY EMP_NUM) USING(EMP_NUM)
GROUP BY STORE_NUM, MAXHRS
ORDER BY MAXHRS DESC;
STORE
PK - STORE_NUM
STORE_NAME
EMPLOYEE
PK-EMP_NUM
FK-STORE_NUM
WORKS
PK-EMP_NUM
PK-PROJ_NUM
WORK_HOURS
Employees can only belong to one store and employees can bill many hours. The question ask to display the STORE_NUM, and STORE_NAME that has billed the most hours. Below is my query that doesn't fully work or make sense to me, but it is where I'm at. Having trouble grasping how to pull information across tables I guess. This is in oracle by the way, any help would be appreciated. If more info is needed please let me know. Just looking for some insight and a push in the right direction. Thanks in advance.
SELECT STORE_NUM, STORE_NAME
FROM STORE JOIN EMPLOYEE USING(STORE_NUM)
JOIN (SELECT EMP_NUM, Max(WORK_HOURS) MAXHRS
FROM WORKS GROUP BY EMP_NUM) USING(EMP_NUM)
GROUP BY STORE_NUM, MAXHRS
ORDER BY MAXHRS DESC;