#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2013
    Posts
    54
    Rep Power
    2

    UPDATE with INNER JOINS


    I'm doing some homework. I need someone to point me in the right direction.

    The question is:
    8. Give a raise to our best salesperson(s).
    o File to create: q8.sql
    o Projection: none
    o Instructions: Write an UPDATE query to increase the value of the SALESPERSONS.salary column by 10% for the most profitable salesperson(s).

    This is what I have so far, but I'm off track. How do I do inner joins to get the values of SUM((ORDERITEMS.qty * INVENTORY.price) - SALESPERSONS.salary), which is the most profitable salesperson equation, in an Update clause?

    Code:
    UPDATE S
    SET SALARY = ((SALARY * .1) + SALARY)
    FROM SALESPERONS AS S
      INNER JOIN ORDERS
      ON SALESPERSONS.EMPID = ORDERS.EMPID
        INNER JOIN ORDERITEMS
          ON ORDERS.ORDERID = ORDERITEMS.ORDERID
            INNER JOIN INVENTORY
            ON ORDERITEMS.PARTID = INVENTORY.PARTID
    WHERE PROFIT=(SELECT MAX(PROFIT)
                  FROM (SELECT SUM((ORDERITEMS.qty * INVENTORY.price) - SALESPERSONS.salary) AS PROFIT
                  FROM SALESPERSONS
                    INNER JOIN ORDERS
                     ON SALESPERSONS.EMPID = ORDERS.EMPID
                    INNER JOIN ORDERITEMS
                     ON ORDERS.ORDERID = ORDERITEMS.ORDERID
                    INNER JOIN INVENTORY
                     ON ORDERITEMS.PARTID = INVENTORY.PART)
                  ;
  2. #2
  3. Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Sep 2006
    Posts
    859
    Rep Power
    388

    Talking


    Originally Posted by pditty8811
    I'm doing some homework. I need someone to point me in the right direction.

    The question is:
    8. Give a raise to our best salesperson(s).
    o File to create: q8.sql
    o Projection: none
    o Instructions: Write an UPDATE query to increase the value of the SALESPERSONS.salary column by 10% for the most profitable salesperson(s).

    . . . E t c . . .
    Could you solve it?

    Here are some tips:

    1) Perhaps profit should be: "(Orderitems.Qty * (Inventory.Price - Inventory.Cost) - Sales.Commission) AS Profit"?
    2) You need to define how many (limit number) of "best salesperson(s)": top 3 ,5, 10? -- Or the Sum-Total amount of "10%" to distribute.
    3) Then "divide & conquer" :

    a) Begin with the query to produce the most profitable orders
    b) Order by most profitable
    c) Add the lookup of the salesperson(s)
    d) Distribute up to the established limit (# persons or sum of amount)
    e) Convert final query to UPDATE statement.

    Good Luck!
    Last edited by LKBrwn_DBA; February 28th, 2014 at 12:28 PM.

IMN logo majestic logo threadwatch logo seochat tools logo