Little help with an Oracle SQL question?

Discussion in 'Webmastering & Programming' started by maclem8223, Nov 8, 2017.

Thread Status:
Not open for further replies.
  1. maclem8223

    maclem8223 [H]ard|Gawd

    Messages:
    1,287
    Joined:
    Oct 28, 2013
    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;
     
  2. ChristianVirtual

    ChristianVirtual [H]ard DCOTM Mar 2016,Aug 2017

    Messages:
    1,639
    Joined:
    Feb 23, 2013
    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 ?
     
  3. maclem8223

    maclem8223 [H]ard|Gawd

    Messages:
    1,287
    Joined:
    Oct 28, 2013
    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: Nov 9, 2017
  4. ChristianVirtual

    ChristianVirtual [H]ard DCOTM Mar 2016,Aug 2017

    Messages:
    1,639
    Joined:
    Feb 23, 2013
    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).
     
  5. maclem8223

    maclem8223 [H]ard|Gawd

    Messages:
    1,287
    Joined:
    Oct 28, 2013
    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
     
  6. ChristianVirtual

    ChristianVirtual [H]ard DCOTM Mar 2016,Aug 2017

    Messages:
    1,639
    Joined:
    Feb 23, 2013
    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: Nov 9, 2017
  7. ChristianVirtual

    ChristianVirtual [H]ard DCOTM Mar 2016,Aug 2017

    Messages:
    1,639
    Joined:
    Feb 23, 2013
    Try to run the lowest subquery as single query first before include as subquery. Then easier to see
     
    Brian_B likes this.
  8. maclem8223

    maclem8223 [H]ard|Gawd

    Messages:
    1,287
    Joined:
    Oct 28, 2013
    Runs fine I get back 2 columns one is EMP_NUM and the second is a sum of their hours labeled EMPHOURS
     
  9. ChristianVirtual

    ChristianVirtual [H]ard DCOTM Mar 2016,Aug 2017

    Messages:
    1,639
    Joined:
    Feb 23, 2013
    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
     
  10. ChristianVirtual

    ChristianVirtual [H]ard DCOTM Mar 2016,Aug 2017

    Messages:
    1,639
    Joined:
    Feb 23, 2013
    Still try to get the store_num already in here; using join:

    Store_num, emp_num,summarized hours
     
  11. maclem8223

    maclem8223 [H]ard|Gawd

    Messages:
    1,287
    Joined:
    Oct 28, 2013
    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;
     
  12. ChristianVirtual

    ChristianVirtual [H]ard DCOTM Mar 2016,Aug 2017

    Messages:
    1,639
    Joined:
    Feb 23, 2013
    My oracle got rusty but something like this

    SELECT val
    FROM rownum_order_test
    ORDER BY val DESC
    FETCH FIRST 1 ROWS ONLY;
     
  13. maclem8223

    maclem8223 [H]ard|Gawd

    Messages:
    1,287
    Joined:
    Oct 28, 2013
    FETCH doesn't work for me in oracle
     
  14. maclem8223

    maclem8223 [H]ard|Gawd

    Messages:
    1,287
    Joined:
    Oct 28, 2013
    Is there a way to limit it using a WHERE or HAVING clause?
     
  15. maclem8223

    maclem8223 [H]ard|Gawd

    Messages:
    1,287
    Joined:
    Oct 28, 2013
    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:(
     
  16. ChristianVirtual

    ChristianVirtual [H]ard DCOTM Mar 2016,Aug 2017

    Messages:
    1,639
    Joined:
    Feb 23, 2013
    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: Nov 9, 2017
    maclem8223 likes this.
  17. maclem8223

    maclem8223 [H]ard|Gawd

    Messages:
    1,287
    Joined:
    Oct 28, 2013
    Which is what I think I'm supposed to do, just not sure how to implement it.
     
  18. ChristianVirtual

    ChristianVirtual [H]ard DCOTM Mar 2016,Aug 2017

    Messages:
    1,639
    Joined:
    Feb 23, 2013
    When I’m home tonight I can give you more hints and setup a solution on my PostgreSQL
     
  19. maclem8223

    maclem8223 [H]ard|Gawd

    Messages:
    1,287
    Joined:
    Oct 28, 2013
    That is much appreciated. I've got like five or six problems now that are at the last step. Something just isn't clicking.
     
  20. ChristianVirtual

    ChristianVirtual [H]ard DCOTM Mar 2016,Aug 2017

    Messages:
    1,639
    Joined:
    Feb 23, 2013
    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: Nov 10, 2017
    maclem8223 likes this.
  21. ChristianVirtual

    ChristianVirtual [H]ard DCOTM Mar 2016,Aug 2017

    Messages:
    1,639
    Joined:
    Feb 23, 2013
    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
     
  22. Brian_B

    Brian_B Gawd

    Messages:
    792
    Joined:
    Mar 23, 2012
    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.
     
    maclem8223 likes this.
  23. ChristianVirtual

    ChristianVirtual [H]ard DCOTM Mar 2016,Aug 2017

    Messages:
    1,639
    Joined:
    Feb 23, 2013
    I suggested that too; but as per OP's response earlier he was not allowed to use the easy way ... see in post #16
     
    maclem8223 likes this.
  24. Brian_B

    Brian_B Gawd

    Messages:
    792
    Joined:
    Mar 23, 2012
    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 likes this.
  25. ChristianVirtual

    ChristianVirtual [H]ard DCOTM Mar 2016,Aug 2017

    Messages:
    1,639
    Joined:
    Feb 23, 2013
    maclem8223 , let us know if the solution is what you are looking for ...
     
    maclem8223 likes this.
  26. maclem8223

    maclem8223 [H]ard|Gawd

    Messages:
    1,287
    Joined:
    Oct 28, 2013
    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.
     
  27. ChristianVirtual

    ChristianVirtual [H]ard DCOTM Mar 2016,Aug 2017

    Messages:
    1,639
    Joined:
    Feb 23, 2013
    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
     
    maclem8223 likes this.
  28. maclem8223

    maclem8223 [H]ard|Gawd

    Messages:
    1,287
    Joined:
    Oct 28, 2013
    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: Nov 10, 2017
    Skillz and Brian_B like this.
  29. Skillz

    Skillz [H]ard DCOTM Jan 16 March 17

    Messages:
    18,623
    Joined:
    Aug 14, 2004
    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.
     
    maclem8223 likes this.
  30. maclem8223

    maclem8223 [H]ard|Gawd

    Messages:
    1,287
    Joined:
    Oct 28, 2013
    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? :)
     
  31. Skillz

    Skillz [H]ard DCOTM Jan 16 March 17

    Messages:
    18,623
    Joined:
    Aug 14, 2004
    What's the question?
     
  32. maclem8223

    maclem8223 [H]ard|Gawd

    Messages:
    1,287
    Joined:
    Oct 28, 2013
    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
     
  33. Skillz

    Skillz [H]ard DCOTM Jan 16 March 17

    Messages:
    18,623
    Joined:
    Aug 14, 2004
  34. maclem8223

    maclem8223 [H]ard|Gawd

    Messages:
    1,287
    Joined:
    Oct 28, 2013
    Figured it out again. I'm good, thanks folks.
     
    Skillz likes this.
Thread Status:
Not open for further replies.