Dismiss Notice
Join Physics Forums Today!
The friendliest, high quality science and math community on the planet! Everyone who loves science is here!

Basic SQL queries

  1. Jan 20, 2016 #1

    WWGD

    User Avatar
    Science Advisor
    Gold Member

    Hi all,
    I am reviewing some material and I have been stuck on these (I am a slow starter, I guess)

    1) I have the table Customer, which has an entry on sales reps for customers. I want to know which sales
    rep handles the most customers. Here is what is given

    Customer ( CustNum PK, CustName, RepNum,....)

    Where RepNum is the sales rep that handles a given customer.

    My attempt is : select repnum from customer, group by repnum having Count(RepNum) =
    (select max(count(repnum)) from customer, group by repnum)

    Does this work?

    -------------------------------------------------------

    2) I want to know which warehouse houses the most products (given by OnHand below), using the table part:

    Part(PartNum, warehouse, OnHand ,..... )

    My attempt: select warehouse from part group by warehouse having sum(OnHand) = (select

    max(sum(onHand)) from part, group by warehouse ).

    Does that work?

    Thanks. Sorry, My SQL is not running, so I cannot test the queries myself.
     
  2. jcsd
  3. Jan 20, 2016 #2

    fresh_42

    Staff: Mentor

    How urgent is it and do you have some csv test data?
     
  4. Jan 20, 2016 #3

    WWGD

    User Avatar
    Science Advisor
    Gold Member

  5. Jan 20, 2016 #4

    fresh_42

    Staff: Mentor

    comma separated values
    My experiences date back a while but I have a DB server running. To me your queries look a bit complicated, however, I do not dare to judge them. Normally I use the try and error method to fix my errors in sql statements. Guess I could try the second one right away but I have no good data on customer reps. My question on how urgent it is, is due to the fact that my sleep is (again) overdue.o
    Edit: try an hex editor to open it, or 'textpad'. The latter opens everything and should be available as demo version.
     
  6. Jan 21, 2016 #5

    WWGD

    User Avatar
    Science Advisor
    Gold Member

    Thank you, I can send you the schema as an attachment
     
  7. Jan 21, 2016 #6

    fresh_42

    Staff: Mentor

  8. Jan 21, 2016 #7

    jedishrfu

    Staff: Mentor

    Last edited: Jan 21, 2016
Know someone interested in this topic? Share this thread via Reddit, Google+, Twitter, or Facebook




Similar Discussions: Basic SQL queries
  1. I need a SQL query (Replies: 3)

  2. SQL basics (Replies: 4)

  3. Need Help on SQL query (Replies: 3)

Loading...