1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

UPDATE with INNER JOINS

Discussion in 'SQL PL/SQL' started by pditty8811, Jan 27, 2014.

  1. pditty8811

    pditty8811 Active Member

    Messages:
    53
    Likes Received:
    0
    Trophy Points:
    130
    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 (Text):

    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. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
  3. pditty8811

    pditty8811 Active Member

    Messages:
    53
    Likes Received:
    0
    Trophy Points:
    130
    I'm not seeing inner joins in your examples...
     
  4. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    It would help greatly if you would post create table statements and some sample data.
     
  5. pditty8811

    pditty8811 Active Member

    Messages:
    53
    Likes Received:
    0
    Trophy Points:
    130
    #
    describe cis276.customers;

    create table customers
    (
    custid number(4) not null,
    cname char(25),
    credit char(1),
    primary key (custid)
    );


    describe cis276.orders;

    create table orders
    (
    orderid number(4) not null,
    empid number(4),
    custid number(4),
    salesdate date,
    primary key (orderid)
    );


    describe cis276.salespersons;

    create table salespersons
    (
    empid number(4) not null,
    ename char(15),
    "rank" number(2),
    salary number(8,2),
    primary key (empid)
    );


    describe cis276.orderitems;

    create table orderitems
    (
    orderid number(4),
    detail number(2),
    partid number(4),
    qty number(2),
    primary key (orderid)
    );


    describe cis276.inventory;

    create table inventory
    (
    partid number(4) not null,
    description char(12),
    stockqty number(4),
    reorderpnt number(4),
    price number(8,2),
    primary key (partid)
    );
    #
     
  6. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    will be it is quite good if you give here dml a script for filling of tables....

    your formula for calculation of a profit is correct?


    P.S.

    in documentation on oracle there is a complete information...
    here at a forum similar problems were already solved not once....



    simple example of modification of your update-operator....

    Code (SQL):

    UPDATE S
    SET SALARY = SALARY * 1.1
    FROM SALESPERONS AS S
    WHERE S.EMPID=(SELECT MAX(EMPID) KEEP (DENSE_RANK LAST ORDER BY PROFIT)
                  FROM (SELECT
                           SP.EMPID,
                           SUM((OI.qty * I.price) - SP.salary) AS PROFIT
                           FROM
                                  SALESPERSONS SP
                           INNER  JOIN ORDERS O        ON SP.EMPID = O.EMPID
                            INNER JOIN ORDERITEMS IO  ON O.ORDERID = OI.ORDERID
                            INNER JOIN INVENTORY I    ON OI.PARTID = I.PART
                        GROUP BY SP.EMPID    
                        )
                    )    

     
     
  7. pditty8811

    pditty8811 Active Member

    Messages:
    53
    Likes Received:
    0
    Trophy Points:
    130
    Thank you. This worked great.