Little help with an Oracle SQL question?

Status
Not open for further replies.
maclem8223

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;
 
Shouldn’t there be a SUM in the queries as one store can have multiple employees and hours ? Or you looking for the single highest hours ?
 
ChristianVirtual said:
Shouldn’t there be a SUM in the queries as one store can have multiple employees and hours ? Or you looking for the single highest hours ?
Click to expand...
Yeah I posted the wrong bit, my apologies. This is where I'm at right now which gives me the top STORE total but I can't figure out how to connect that to the STORE_NUM and STORE_NAME.

SELECT Max(STORETOTAL)

FROM
(SELECT DISTINCT STORE_NUM,
SUM(EMPHOURS)AS STORETOTAL
FROM STORE JOIN
EMPLOYEE USING(STORE_NUM)
JOIN
(SELECT EMP_NUM, SUM(WORK_HOURS) AS EMPHOURS
FROM WORKS
GROUP BY EMP_NUM
)USING(EMP_NUM)
GROUP BY STORE_NUM)
 
Last edited:
Ok, next suggestion ...

Start with creating dedicated views for the sub queries here. Easier to understand and debug as you will see better if it is working as expected. For a final solution you still could combine (also using then the views).
 
We can't use views for this set of questions we got. He said he wants us to be comfortable utilizing sub-queries correctly rather than views just yet. I think of views just like functions and would love to use them but... :(

"Views should be dedicated solely for queries that will be ran often, otherwise use a sub-query" ~Professor
 
Hmm, if teacher want ...ok. I hoped it will be easier to structure a solution. But fine ...

So next hint: try to see if you most lowest subquery deliver what is need and adjust. I think there need to be already a join in ... (I try not to post any solution but guide you through)
 
Last edited:
Try to run the lowest subquery as single query first before include as subquery. Then easier to see
 
I read once more, one emp only in one store I missed ... though I believe in a next level of question that get changed o_O
 
Here is what I have now and it runs as intended I just can't figure out how to display just the top row.
Displays the store_num, name, and totals in descending order.

SELECT STORE_NUM, STORE_NAME, Max(STORETOTAL) AS TOPSTORE
FROM (SELECT DISTINCT STORE_NUM, STORE_NAME,
SUM(EMPHOURS)AS STORETOTAL
FROM DEPARTMENT JOIN
EMPLOYEE USING(STORE_NUM)
JOIN
(SELECT EMP_NUM, SUM(WORK_HOURS) AS EMPHOURS
FROM WORKS
GROUP BY EMP_NUM
)USING(EMP_NUM)
GROUP BY STORE_NUM, STORE_NAME)
GROUP BY STORE_NUM, STORE_NAME
ORDER BY TOPSTORE DESC;
 
My oracle got rusty but something like this

SELECT val
FROM rownum_order_test
ORDER BY val DESC
FETCH FIRST 1 ROWS ONLY;
 
Thanks, found that one this morning which was helpful but won't work for my situation. Can't use FETCH or ROWNUM since we haven't used them in class. Hence my frustration and why I'm stuck:(
 
Ok; my next proposal is bad ; really bad and my appologies upfront:
If you can’t use limiting criteria then you could determine once with a sub query the max value and use that in a where-clause or having-clause to minimize the result to the one store where sum(hours) = max(hours).

Feel dirty now, need a shower :nailbiting:
 
Last edited:
ChristianVirtual said:
Ok; my next proposal is bad ; really bad and my appologies upfront:
If you can’t use limiting criteria then you could determine once with a sub query the max value and use that in a where-clause or having-clause to minimize the result to the one store where sum(hours) = max(hours).

Feel dirty now, need a shower :nailbiting:
Click to expand...

Which is what I think I'm supposed to do, just not sure how to implement it.
 
That is much appreciated. I've got like five or six problems now that are at the last step. Something just isn't clicking.
 
Here is one working solution

select * from cvstore where store_num =
(
SELECT cvemployee.store_num
FROM cvWORKs join cvemployee on cvworks.emp_num = cvemployee.emp_num
GROUP BY cvemployee.store_num, cvemployee.EMP_NUM
having sum(work_hours) =
(
select max(emphours) from
(
SELECT cvemployee.store_num, SUM(WORK_HOURS) AS EMPHOURS
FROM cvWORKs join cvemployee on cvworks.emp_num = cvemployee.emp_num
GROUP BY cvemployee.store_num
) as sq
)
)

How it works ?

1) get the sum for each store (via join of employee and works)
SELECT cvemployee.store_num, SUM(WORK_HOURS) AS EMPHOURS
FROM cvWORKs join cvemployee on cvworks.emp_num = cvemployee.emp_num
GROUP BY cvemployee.store_num

2) get the MAX() value from it using subquery
select max(emphours) from
(
SELECT cvemployee.store_num, SUM(WORK_HOURS) AS EMPHOURS
FROM cvWORKs join cvemployee on cvworks.emp_num = cvemployee.emp_num
GROUP BY cvemployee.store_num
) as sq

3) use this MAX() subquery in the HAVING clause to find the store number with equal aggregated sum of working hours
SELECT cvemployee.store_num
FROM cvWORKs join cvemployee on cvworks.emp_num = cvemployee.emp_num
GROUP BY cvemployee.store_num, cvemployee.EMP_NUM
having sum(work_hours) =
(
select max(emphours) from
(
SELECT cvemployee.store_num, SUM(WORK_HOURS) AS EMPHOURS
FROM cvWORKs join cvemployee on cvworks.emp_num = cvemployee.emp_num
GROUP BY cvemployee.store_num
) as sq
　)​

4) use the store number from that subquery to get the name of the store with again a subquery


Test data used:
cvStore
Screen Shot 2017-11-10 at 19.42.09.png

cvEmployee
Screen Shot 2017-11-10 at 19.42.18.png

cvWorks
Screen Shot 2017-11-10 at 19.42.25.png



Result:
Screen Shot 2017-11-10 at 19.49.24.png

Test it a bit more with multiple data set to make sure
 
Last edited:
and if you like the solution and have some spare CPU cycles free then please join us over in the distributed computing team for a challenge we have

https://hardforum.com/threads/wcg-13th-annual-2017-birthday-challenge.1945559/

all what is needed is to
1) install BOINC client, assign "World Community Grid"
2) join the team with this link: https://join.worldcommunitygrid.org?recruiterId=887739&teamId=BP5XNJBR9N1 and
3) crunch for a few hours/days on World Community Grid helping our team to win ...

but only if you like and your own computer can deal with some workload (CPU only, no GPU needed)



Anyway: it was my pleasure to help you
 
Not sure about Oracle,

In mssql, to get the result, its
SELECT ... ORDER BY ... TOP 1

in mysql, it's
SELECT ... ORDER BY ... LIMIT 1

I would imagine oracle is similar. You Could just order your working query (which lists all the results, and that's fine), then wrap another simple select query around the top of it to limit it to the first result only.
 
I suggested that too; but as per OP's response earlier he was not allowed to use the easy way ... see in post #16
 
Then I suppose you could wrap it around with something like SELECT MAX() query, since you know you want the highest value.

... edit...

As suggested in Post 21
 
maclem8223 said:
Thanks for the help, I'm not getting any results with step 3. It runs and shows the column name; however, the answer is blank.
Click to expand...
can you share your data ? Like the snapshots I added ? are the types for store_num the same; I did the mistake first to have store_num in employee as text while it was integer in the store-table
 
ChristianVirtual said:
can you share your data ? Like the snapshots I added ? are the types for store_num the same; I did the mistake first to have store_num in employee as text while it was integer in the store-table
Click to expand...

FIGURED IT OUT! Excuse me, sorry for yelling but I spent waaaaaaaaay to much time on this one. ChristianVirtual thank you so much for your efforts in helping me.
My only concern with joining BOINC is can I keep an AIO on a cpu running 24/7? If so, then who gives a shit. I've got a i5 6500, 16GB, 1050ti sitting idle about 35 weeks out of the year. I'm in.
Oh, my solution.....the order by STORE_NUM is in case there is ever a tie(y)

SELECT STORE_NUM, STORE_NAME
FROM DEPARTMENT
JOIN (SELECT STORE_NUM,
Sum(WORK_HOURS) AS EMPHOURS
FROM WORKS JOIN EMPLOYEE USING(EMP_NUM)
GROUP BY STORE_NUM) USING(STORE_NUM)
WHERE EMPHOURS = (SELECT Max(EMPHOURS)
FROM (SELECT STORE_NUM,
Sum(WORK_HOURS) AS EMPHOURS
FROM WORKS JOIN EMPLOYEE USING(EMP_NUM)
GROUP BY STORE_NUM))
ORDER BY STORE_NUM;




EDIT: Sorry Brian_B thank you for your input as well.
 
Last edited:
Yes, you can with AIO. My main rig has an AIO cooler that I run BOINC on 24/7. No issues.

In fact, I even have 6 GPUs that have those AIO water loops that I also run BOINC on 24/7.

So join the team!

The challenge starts on the 16th of this month.
 
Skillz said:
Yes, you can with AIO. My main rig has an AIO cooler that I run BOINC on 24/7. No issues.

In fact, I even have 6 GPUs that have those AIO water loops that I also run BOINC on 24/7.

So join the team!

The challenge starts on the 16th of this month.
Click to expand...
Alright. I'm in. I see you post all the time in GenMay regarding challenges and I've always been intrigued so I'll try it out.
In the interim anyone wanna help me with a unary join question? :)
 
Skillz said:
What's the question?
Click to expand...

Create a query that list the EMP_NUM, EMP_FNAME, EMP_LNAME, SUPERVISOR (both first and last name) for each EMPLOYEE.
The unary is where EMP_SUPERV is the EMP_NUM of that employee's supervisor. Does that make sense? We haven't learned Union or self-joins yet so I wasn't sure if there was a way of doing it without.

EMPLOYEE
PK-EMP_NUM
EMP_FNAME
EMP_LNAME
EMP_SUPERV
 
Status
Not open for further replies.
Back
Top