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!

I just got torn to pieces on my latest asignment

Discussion in 'SQL PL/SQL' started by pditty8811, Feb 25, 2014.

  1. pditty8811

    pditty8811 Active Member

    Messages:
    53
    Likes Received:
    0
    Trophy Points:
    130
    Here is the assignment. I got a 22/50

    Code (Text):


    50 - (7*4) = 22
    > -- Lab4 grading run
    > /*
    --------------------------------------------------------------------------------
    Lab4 q1.    Who are customers that have not placed orders?
    CUSTOMERS.custid, CUSTOMERS.cname
    Show in customer name order. Use an EXISTS clause (no JOINs).
    STUDENT submission code and results followed by answer results.
    --------------------------------------------------------------------------------
    */
    > @q1
    > /*

    1.  Who are customers that have not placed orders?
    o   File to create: q1.sql
    o   Projection: CUSTOMERS.custid, CUSTOMERS.cname
    o   Instructions: Show in customer name order. Use an EXISTS clause (no JOINs).
    */
    > SELECT CUSTOMERS.CUSTID,
    CUSTOMERS.CNAME
    FROM CUSTOMERS
    WHERE EXISTS (SELECT *
      FROM ORDERS
      WHERE CUSTOMERS.CUSTID = ORDERS.CUSTID)
        CUSTID CNAME                  
    ---------- -------------------------
             1 Stanwood Consulting      
             2 Vallecito Industries      
             3 Wellesley Inc.            
            11 Agorist Distributors      
            12 Alexandria Liquor Co.    
            15 Blumenfeld Educational Co
            16 Cardiff Industries        
            18 DRT Marine Lab            
            21 Kalakaua Corporation      
            22 Ladera Enterprises        
            25 Mission Hills Inc.        
            26 Monterey University      
            44 Fancy Felines            
           111 Scarlet Talent Agency    
            42 Company42                

     15 rows selected

    > @a1 -- suggested answer file
    Cust# Customer         *ANSWER*
    ----- -------------------------
       10 Acton Computers          
       43 Berryville Supplies      
       33 Black Cat Stores          
      112 Coffee Snob Roasters      
       17 Del Toro Enterprises      
       79 Durell Equipment          
       20 Fredericks and Assoc.    
       53 Mentone Enterprises      
       27 Quinton College          
       28 Rensselaer & Co.          
       29 Ridgewood_Bergman Co.    
       30 South Burlingame Co.      
        4 White Court              

     13 rows selected

    > set ECHO ON
    > /*
    --------------------------------------------------------------------------------
    Lab4 q2.    Delete the customers who have not placed orders.
    DELETE from the CUSTOMERS table all customers who have not placed orders.
    STUDENT submission code and results followed by answer results.
    --------------------------------------------------------------------------------
    */
    > @q2
    > /*

    2.  Delete the customers who have not placed orders.
    o   File to create: q2.sql
    o   Projection: none
    o   Instructions: Write a DELETE query to delete from the CUSTOMERS table all customers who have not placed orders.

    */
    > DELETE FROM CUSTOMERS
    WHERE NOT EXISTS (SELECT *
      FROM ORDERS
      WHERE CUSTOMERS.CUSTID = ORDERS.CUSTID)
    13 rows deleted.
    > @a2 -- suggested answer file should return zero rows having just deleted 13 rows
    no rows selected


    > set ECHO ON
    > /*
    --------------------------------------------------------------------------------
    Lab4 q3.    How are all of our salespeople doing?
    SALESPERSONS.empid, SALESPERSONS.ename, SUM(ORDERITEMS.qty*INVENTORY.price)
    Display the total dollar value that each and every sales person has sold.
    List in ascending order of the total amounts sold.
    STUDENT submission code and results followed by answer results.
    --------------------------------------------------------------------------------
    */
    > @q3
    > /*

    3.  How are all of our salespeople doing?
    o   File to create: q3.sql
    o   Projection: SALESPERSONS.empid, SALESPERSONS.ename,
            SUM(ORDERITEMS.qty*INVENTORY.price)
    o   Instructions: Display the total dollar value that each and every sales person has sold. If you want to show zero use NVL(). List in ascending order of the total amounts sold.
    */
    > SELECT SALESPERSONS.EMPID,
    SALESPERSONS.ENAME,
    NVL(SUM(ORDERITEMS.QTY * INVENTORY.PRICE), 0)AS TOTAL
    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.PARTID
    GROUP BY SALESPERSONS.EMPID, SALESPERSONS.ENAME
    ORDER BY TOTAL ASC
         EMPID ENAME                TOTAL
    ---------- --------------- ----------
           107 Gloria Garcia       146.35
           109 Kevin Kody           264.5
           106 Faulkner Forest        577
           102 Burbank Burkett     640.35
           101 Andrew Allen         917.4
           105 Edward Everling    1040.45
           103 Charles Cox        1069.45
           108 Harvey Harrison       2140
           404 DaleAnn Dahlman     2464.4
           104 Dale Dahlman        2464.4
           204 Dan Dahlman         2464.4
           214 Dotty Domuch       2687.48

     12 rows selected

    > @a3 -- suggested answer file
    Emp# Salesperson *ANSWER* Total Sales
    ---- -------------------- -----------
    110  Larry Little                $.00
    221  Lori Little                 $.00
    400  Larry Little Jr             $.00
    107  Gloria Garcia            $146.35
    109  Kevin Kody               $264.50
    106  Faulkner Forest          $577.00
    102  Burbank Burkett          $640.35
    101  Andrew Allen             $917.40
    105  Edward Everling        $1,040.45
    103  Charles Cox            $1,069.45
    108  Harvey Harrison        $2,140.00
    404  DaleAnn Dahlman        $2,464.40
    204  Dan Dahlman            $2,464.40
    104  Dale Dahlman           $2,464.40
    214  Dotty Domuch           $2,687.48

     15 rows selected

    > set ECHO ON
    > /*
    --------------------------------------------------------------------------------
    Lab4 q4.    What is the value of each and every order?
    ORDERS.orderid, SUM(ORDERITEM.qty*INVENTORY.price)
    Display the total dollar value for each and every order.
    List in dollar value ascending.
    STUDENT submission code and results followed by answer results.
    --------------------------------------------------------------------------------
    */
    > @q4
    > /*

    4.  What is the value of each and every order?
    o   File to create: q4.sql
    o   Projection: ORDERS.orderid, SUM(ORDERITEM.qty*INVENTORY.price)
    o   Instructions: Display the total dollar value for each and every order. If you want to show zero use NVL(). List in dollar value ascending.

    */
    > SELECT ORDERS.ORDERID,
    CONCAT('$', NVL(SUM(ORDERITEMS.QTY * INVENTORY.PRICE), 0)) AS VALUE
    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.PARTID
    GROUP BY ORDERS.ORDERID
    ORDER BY VALUE ASC
       ORDERID VALUE                                  
    ---------- -----------------------------------------
          6128 $128.5                                    
          6168 $146.35                                  
          6109 $172.5                                    
          6315 $187.14                                  
          6219 $209.5                                    
          9219 $209.5                                    
          6196 $2140                                    
          4442 $2464.4                                  
          6314 $2500.34                                  
          6227 $254.5                                    
          6218 $254.5                                    
          6157 $256.95                                  
          6215 $264.5                                    
          6175 $348.15                                  
          6238 $35                                      
          6237 $378.3                                    
          9237 $378.3                                    
          6099 $395                                      
          6148 $400.35                                  
          6155 $431.95                                  
          6217 $467.85                                  
          9174 $488.45                                  
          6174 $488.45                                  
          6216 $522.4                                    
          9228 $570                                      
          6228 $570                                      
          6239 $577                                      
          6129 $818.15                                  
          9129 $818.15                                  

     29 rows selected

    > @a4 -- suggested answer file
    Orders *ANSWER* Values
    ------ ---------------
     4000             $.00
     6107             $.00
     6124             $.00
     6000             $.00
     6238           $35.00
     6128          $128.50
     6168          $146.35
     6109          $172.50
     6315          $187.14
     9219          $209.50
     6219          $209.50
     6227          $254.50
     6218          $254.50
     6157          $256.95
     6215          $264.50
     6175          $348.15
     6237          $378.30
     9237          $378.30
     6099          $395.00
     6148          $400.35
     6155          $431.95
     6217          $467.85
     6174          $488.45
     9174          $488.45
     6216          $522.40
     6228          $570.00
     9228          $570.00
     6239          $577.00
     6129          $818.15
     9129          $818.15
     6196        $2,140.00
     4442        $2,464.40
     6314        $2,500.34

     33 rows selected

    > set ECHO ON
    > /*
    --------------------------------------------------------------------------------
    Lab4 q5.    Who is our most profitable salesperson?
    S.empid, S.ename, SUM(OI.qty*I.price) - S.salary
    A salesperson's profit (or loss) is the difference between what the person sold
    and what the person earns (accumulated sales less salary).  
    If the value is positive then there is a profit, otherwise there is a loss.  
    The most profitable salesperson, therefore, is the person with the greatest
    profit or smallest loss.  Display the most profitable salesperson.
    There may be more than one salesperson with the same high profit.
    STUDENT submission code and results followed by answer results.
    --------------------------------------------------------------------------------
    */
    > @q5
    > /*

    5.  Who is our most profitable salesperson?
    o   File to create: q5.sql
    o   Projection: SALESPERSONS.empid, SALESPERSONS.ename,
            SUM(ORDERITEMS.qty*INVENTORY.price) - SALESPERSONS.salary
    o   Instructions: A salesperson's profit (or loss) is the difference between what the person sold and what the person earns (accumulated sales less salary).  If the value is positive then there is a profit, otherwise there is a loss.  The most profitable salesperson, therefore, is the person with the greatest profit or smallest loss.  Display the most profitable salesperson. There may be more than one salesperson with the same high profit.

    */
    > SELECT MAX(PROFIT),
    EMPID,
    ENAME
    FROM (
    SELECT SALESPERSONS.EMPID AS EMPID,
    SALESPERSONS.ENAME AS ENAME,
    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.PARTID
    GROUP BY SALESPERSONS.EMPID, SALESPERSONS.ENAME, SALESPERSONS.SALARY)
    GROUP BY EMPID, ENAME, PROFIT
    ORDER BY PROFIT DESC
    MAX(PROFIT)      EMPID ENAME        
    ----------- ---------- ---------------
        -1535.6        404 DaleAnn Dahlman
       -11082.6        101 Andrew Allen    
      -11359.65        102 Burbank Burkett
      -14853.65        107 Gloria Garcia  
      -17065.48        214 Dotty Domuch    
         -24423        106 Faulkner Forest
         -27860        108 Harvey Harrison
       -30735.5        109 Kevin Kody      
      -47359.55        105 Edward Everling
      -52930.55        103 Charles Cox    
       -69535.6        104 Dale Dahlman    
       -69535.6        204 Dan Dahlman    

     12 rows selected

    > @a5 -- suggested answer file
    Emp#  Employee    *ANSWER*      Profit
    ----- -------------------- -----------
      214 Dotty Domuch           $1,452.92

    > @SalespersonsProfits -- Display salespersons with rank, salary, sales and profit
    Emp#  Salesperson     Rank Emp Salary Total Sales Good Profit | *BAD*Profit |
    ----- --------------- ---- ---------- ----------- ----------- - ----------- -
      101 Andrew Allen      1   $1,000.00     $917.40     -$82.60 |     -$82.60 |
      102 Burbank Burkett   1   $1,000.00     $640.35    -$359.65 |    -$359.65 |
      103 Charles Cox       2   $2,000.00   $1,069.45    -$930.55 |    -$930.55 |
      104 Dale Dahlman      2   $2,000.00   $2,464.40     $464.40 |     $464.40 |
      105 Edward Everling   2   $2,200.00   $1,040.45  -$1,159.55 |  -$1,159.55 |
      106 Faulkner Forest   3   $2,500.00     $577.00  -$1,923.00 |  -$1,923.00 |
      107 Gloria Garcia     3   $2,500.00     $146.35  -$2,353.65 |  -$2,353.65 |
      108 Harvey Harrison   3   $3,000.00   $2,140.00    -$860.00 |    -$860.00 |
      109 Kevin Kody        3   $3,100.00     $264.50  -$2,835.50 |  -$2,835.50 |
      110 Larry Little      1   $1,500.00        $.00  -$1,500.00 |        $.00 |
      204 Dan Dahlman       2   $2,000.00   $2,464.40     $464.40 |     $464.40 |
      214 Dotty Domuch      1   $1,234.56   $2,687.48   $1,452.92 |   $1,452.92 |
      221 Lori Little       1   $1,919.19        $.00  -$1,919.19 |        $.00 |
      400 Larry Little Jr   1   $4,444.44        $.00  -$4,444.44 |        $.00 |
      404 DaleAnn Dahlman   2   $2,000.00   $2,464.40     $464.40 |     $464.40 |

     15 rows selected

    > set ECHO ON
    > /*
    --------------------------------------------------------------------------------
    Lab4 q6.    Who is our second-most profitable salesperson?
    S.empid, S.ename, SUM(OI.qty*I.price) - S.salary
    The second-most profitable salesperson is the person with the next greatest
    profit or next smallest loss. Display the second-most profitable salesperson.
    There may be more than one. Do not hard-code the results of #5 into this query -
    that simply creates a data-dependent query.
    STUDENT submission code and results followed by answer results.
    --------------------------------------------------------------------------------
    */
    > @q6
    > /*

    6.  Who is our second-most profitable salesperson?
    o   File to create: q6.sql
    o   Projection: SALESPERSONS.empid, SALESPERSONS.ename,  
            SUM(ORDERITEMS.qty*INVENTORY.price) - SALESPERSONS.salary
    o   Instructions: The second-most profitable salesperson is the person with the next greatest profit or next smallest loss. Display the second-most profitable salesperson. There may be more than one. Do not hard-code the results of #5 into this query - that simply creates a data-dependent query.

    */
    > SELECT *
    FROM (SELECT SALESPERSONS2.*,
    ROWNUM RNUM
    FROM (
    SELECT SALESPERSONS.empid, SALESPERSONS.ename,
            (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.PARTID
    WHERE ROWNUM <= 2
    GROUP BY SALESPERSONS.EMPID, SALESPERSONS.ENAME, SALESPERSONS.SALARY
    ORDER BY PROFIT DESC) SALESPERSONS2)
    WHERE RNUM >= 2
    no rows selected


    > @a6 -- suggested answer file
    Emp#  Employee    *ANSWER*  2nd Profit
    ----- -------------------- -----------
      204 Dan Dahlman              $464.40
      104 Dale Dahlman             $464.40
      404 DaleAnn Dahlman          $464.40

    > @SalespersonsProfits -- Display all salespersons with rank, salary, sales and profit
    Emp#  Salesperson     Rank Emp Salary Total Sales Good Profit | *BAD*Profit |
    ----- --------------- ---- ---------- ----------- ----------- - ----------- -
      101 Andrew Allen      1   $1,000.00     $917.40     -$82.60 |     -$82.60 |
      102 Burbank Burkett   1   $1,000.00     $640.35    -$359.65 |    -$359.65 |
      103 Charles Cox       2   $2,000.00   $1,069.45    -$930.55 |    -$930.55 |
      104 Dale Dahlman      2   $2,000.00   $2,464.40     $464.40 |     $464.40 |
      105 Edward Everling   2   $2,200.00   $1,040.45  -$1,159.55 |  -$1,159.55 |
      106 Faulkner Forest   3   $2,500.00     $577.00  -$1,923.00 |  -$1,923.00 |
      107 Gloria Garcia     3   $2,500.00     $146.35  -$2,353.65 |  -$2,353.65 |
      108 Harvey Harrison   3   $3,000.00   $2,140.00    -$860.00 |    -$860.00 |
      109 Kevin Kody        3   $3,100.00     $264.50  -$2,835.50 |  -$2,835.50 |
      110 Larry Little      1   $1,500.00        $.00  -$1,500.00 |        $.00 |
      204 Dan Dahlman       2   $2,000.00   $2,464.40     $464.40 |     $464.40 |
      214 Dotty Domuch      1   $1,234.56   $2,687.48   $1,452.92 |   $1,452.92 |
      221 Lori Little       1   $1,919.19        $.00  -$1,919.19 |        $.00 |
      400 Larry Little Jr   1   $4,444.44        $.00  -$4,444.44 |        $.00 |
      404 DaleAnn Dahlman   2   $2,000.00   $2,464.40     $464.40 |     $464.40 |

     15 rows selected

    > set ECHO ON
    > /*
    --------------------------------------------------------------------------------
    Lab4 q7.    Add a new salesperson
    INSERT a new salesperson into the database with the following attribute values:
    Empid is ten greater than the largest existing empid (no hard-coding, use SELECT)
    Ename is your name (hard-code your name here but watch the field size)
    [Rank] is the rank is associated with the highest-paid salesperson (SELECT)
    Salary is 10% more than the lowest-paid salesperson (another SELECT clause).
    STUDENT submission code and results followed by answer results.
    --------------------------------------------------------------------------------
    */
    > -- Adding new employee w/ID 999 to test rank value assignment
    > INSERT INTO SALESPERSONS (EmpID, Ename, Rank, Salary)
    VALUES (999,'Hi Celery',3,4444.4444)
    1 rows inserted.
    > @SalespersonsProfits  -- Display all salespersons with rank, salary, sales and profit
    Emp#  Salesperson     Rank Emp Salary Total Sales Good Profit | *BAD*Profit |
    ----- --------------- ---- ---------- ----------- ----------- - ----------- -
      101 Andrew Allen      1   $1,000.00     $917.40     -$82.60 |     -$82.60 |
      102 Burbank Burkett   1   $1,000.00     $640.35    -$359.65 |    -$359.65 |
      103 Charles Cox       2   $2,000.00   $1,069.45    -$930.55 |    -$930.55 |
      104 Dale Dahlman      2   $2,000.00   $2,464.40     $464.40 |     $464.40 |
      105 Edward Everling   2   $2,200.00   $1,040.45  -$1,159.55 |  -$1,159.55 |
      106 Faulkner Forest   3   $2,500.00     $577.00  -$1,923.00 |  -$1,923.00 |
      107 Gloria Garcia     3   $2,500.00     $146.35  -$2,353.65 |  -$2,353.65 |
      108 Harvey Harrison   3   $3,000.00   $2,140.00    -$860.00 |    -$860.00 |
      109 Kevin Kody        3   $3,100.00     $264.50  -$2,835.50 |  -$2,835.50 |
      110 Larry Little      1   $1,500.00        $.00  -$1,500.00 |        $.00 |
      204 Dan Dahlman       2   $2,000.00   $2,464.40     $464.40 |     $464.40 |
      214 Dotty Domuch      1   $1,234.56   $2,687.48   $1,452.92 |   $1,452.92 |
      221 Lori Little       1   $1,919.19        $.00  -$1,919.19 |        $.00 |
      400 Larry Little Jr   1   $4,444.44        $.00  -$4,444.44 |        $.00 |
      404 DaleAnn Dahlman   2   $2,000.00   $2,464.40     $464.40 |     $464.40 |
      999 Hi Celery         3   $4,444.44        $.00  -$4,444.44 |        $.00 |

     16 rows selected

    > set ECHO ON
    > @q7
    > /*

    7.  Add a new salesperson
    o   File to create:  q7.sql
    o   Projection: none
    o   Instructions: Write an INSERT query to insert a new salesperson into the database with the following attribute values:
    ?   Empid should be ten greater than the largest existing empid (no hard-coding, use SELECT)
    ?   Ename needs to be your name (hard-code your name here but watch the field size)
    ?   [Rank] will be whichever rank is associated with the highest-paid salesperson (SELECT)
    ?   Salary is to be 10% more than the lowest-paid salesperson (another SELECT clause).

    */
    > SELECT ENAME
    FROM SALESPERSONS
    ENAME        
    ---------------
    Andrew Allen    
    Burbank Burkett
    Charles Cox    
    Dale Dahlman    
    Edward Everling
    Faulkner Forest
    Gloria Garcia  
    Harvey Harrison
    Kevin Kody      
    Larry Little    
    Dotty Domuch    
    Lori Little    
    Dan Dahlman    
    Larry Little Jr
    DaleAnn Dahlman
    Hi Celery      

     16 rows selected

    > INSERT INTO SALESPERSONS (EMPID)
    SELECT MAX(EMPID)+10
    FROM SALESPERSONS

    Error starting at line 20 in command:
    INSERT INTO SALESPERSONS (EMPID)
    SELECT MAX(EMPID)+10
    FROM SALESPERSONS
    Error report:
    SQL Error: ORA-01400: cannot insert NULL into ("VICKI"."SALESPERSONS"."ENAME")
    01400. 00000 -  "cannot insert NULL into (%s)"
    *Cause:    
    *Action:
    > INSERT INTO SALESPERSONS (ENAME)
    VALUES('John Smith)

    Error starting at line 25 in command:
    INSERT INTO SALESPERSONS (ENAME)
    VALUES()
    Error report:
    SQL Error: ORA-01400: cannot insert NULL into ("VICKI"."SALESPERSONS"."EMPID")
    01400. 00000 -  "cannot insert NULL into (%s)"
    *Cause:    
    *Action:
    > INSERT INTO SALESPERSONS (RANK)
    SELECT RANK
    FROM SALESPERSONS
    WHERE ROWNUM = 1
    ORDER BY SALARY DESC

    Error starting at line 29 in command:
    INSERT INTO SALESPERSONS (RANK)
    SELECT RANK
    FROM SALESPERSONS
    WHERE ROWNUM = 1
    ORDER BY SALARY DESC
    Error report:
    SQL Error: ORA-01400: cannot insert NULL into ("VICKI"."SALESPERSONS"."EMPID")
    01400. 00000 -  "cannot insert NULL into (%s)"
    *Cause:    
    *Action:
    > INSERT INTO SALESPERSONS (SALARY)
    SELECT (SALARY * .1) + SALARY
    FROM SALESPERSONS
    WHERE ROWNUM = 1
    ORDER BY SALARY ASC

    Error starting at line 36 in command:
    INSERT INTO SALESPERSONS (SALARY)
    SELECT (SALARY * .1) + SALARY
    FROM SALESPERSONS
    WHERE ROWNUM = 1
    ORDER BY SALARY ASC
    Error report:
    SQL Error: ORA-01400: cannot insert NULL into ("VICKI"."SALESPERSONS"."EMPID")
    01400. 00000 -  "cannot insert NULL into (%s)"
    *Cause:    
    *Action:
    > @a7 -- suggested answer file adds salesperson too
    1 rows inserted.
    > DELETE FROM SALESPERSONS WHERE EmpID >= 999
    2 rows deleted.
    > -- Remove new salespersons for program reruns
    > @q7 -- Rerun of student submission code then answer code and the final display
    ENAME        
    ---------------
    Andrew Allen    
    Burbank Burkett
    Charles Cox    
    Dale Dahlman    
    Edward Everling
    Faulkner Forest
    Gloria Garcia  
    Harvey Harrison
    Kevin Kody      
    Larry Little    
    Dotty Domuch    
    Lori Little    
    Dan Dahlman    
    Larry Little Jr
    DaleAnn Dahlman

     15 rows selected


    Error starting at line 20 in command:
    INSERT INTO SALESPERSONS (EMPID)
    SELECT MAX(EMPID)+10
    FROM SALESPERSONS
    Error report:
    SQL Error: ORA-01400: cannot insert NULL into ("VICKI"."SALESPERSONS"."ENAME")
    01400. 00000 -  "cannot insert NULL into (%s)"
    *Cause:    
    *Action:

    Error starting at line 25 in command:
    INSERT INTO SALESPERSONS (ENAME)
    VALUES()
    Error report:
    SQL Error: ORA-01400: cannot insert NULL into ("VICKI"."SALESPERSONS"."EMPID")
    01400. 00000 -  "cannot insert NULL into (%s)"
    *Cause:    
    *Action:

    Error starting at line 29 in command:
    INSERT INTO SALESPERSONS (RANK)
    SELECT RANK
    FROM SALESPERSONS
    WHERE ROWNUM = 1
    ORDER BY SALARY DESC
    Error report:
    SQL Error: ORA-01400: cannot insert NULL into ("VICKI"."SALESPERSONS"."EMPID")
    01400. 00000 -  "cannot insert NULL into (%s)"
    *Cause:    
    *Action:

    Error starting at line 36 in command:
    INSERT INTO SALESPERSONS (SALARY)
    SELECT (SALARY * .1) + SALARY
    FROM SALESPERSONS
    WHERE ROWNUM = 1
    ORDER BY SALARY ASC
    Error report:
    SQL Error: ORA-01400: cannot insert NULL into ("VICKI"."SALESPERSONS"."EMPID")
    01400. 00000 -  "cannot insert NULL into (%s)"
    *Cause:    
    *Action:
    > @a7 -- suggested answer file adds salesperson too
    1 rows inserted.
    > @SalespersonsProfits  -- Display all salespersons with rank, salary, sales and profit      
    Emp#  Salesperson     Rank Emp Salary Total Sales Good Profit | *BAD*Profit |
    ----- --------------- ---- ---------- ----------- ----------- - ----------- -
      101 Andrew Allen      1   $1,000.00     $917.40     -$82.60 |     -$82.60 |
      102 Burbank Burkett   1   $1,000.00     $640.35    -$359.65 |    -$359.65 |
      103 Charles Cox       2   $2,000.00   $1,069.45    -$930.55 |    -$930.55 |
      104 Dale Dahlman      2   $2,000.00   $2,464.40     $464.40 |     $464.40 |
      105 Edward Everling   2   $2,200.00   $1,040.45  -$1,159.55 |  -$1,159.55 |
      106 Faulkner Forest   3   $2,500.00     $577.00  -$1,923.00 |  -$1,923.00 |
      107 Gloria Garcia     3   $2,500.00     $146.35  -$2,353.65 |  -$2,353.65 |
      108 Harvey Harrison   3   $3,000.00   $2,140.00    -$860.00 |    -$860.00 |
      109 Kevin Kody        3   $3,100.00     $264.50  -$2,835.50 |  -$2,835.50 |
      110 Larry Little      1   $1,500.00        $.00  -$1,500.00 |        $.00 |
      204 Dan Dahlman       2   $2,000.00   $2,464.40     $464.40 |     $464.40 |
      214 Dotty Domuch      1   $1,234.56   $2,687.48   $1,452.92 |   $1,452.92 |
      221 Lori Little       1   $1,919.19        $.00  -$1,919.19 |        $.00 |
      400 Larry Little Jr   1   $4,444.44        $.00  -$4,444.44 |        $.00 |
      404 DaleAnn Dahlman   2   $2,000.00   $2,464.40     $464.40 |     $464.40 |
      414 Vicki's Answer    1   $1,100.00        $.00  -$1,100.00 |        $.00 |

     16 rows selected

    > set ECHO ON
    > /*
    --------------------------------------------------------------------------------
    Lab4 q8.    Give a raise to our best salesperson(s).
    UPDATE query to increase the value of the SALESPERSONS.salary column by 10%
    for the most profitable salesperson(s).
    STUDENT submission code and results followed by answer results.
    --------------------------------------------------------------------------------
    */
    > @a8 -- suggested answer file projects correct salesperson(s) for raise [BEFORE]
         EMPID ENAME                 RANK     SALARY
    ---------- --------------- ---------- ----------
           214 Dotty Domuch             1    1234.56

    > set ECHO ON
    > @q8
    > /*

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

    */
    > UPDATE SALESPERSONS
    SET SALESPERSONS.SALARY = ((SALESPERSONS.SALARY * .1) + SALESPERSONS.SALARY)
    WHERE SALESPERSONS.EMPID = (SELECT SUM((ORDERITEMS.qty * INVENTORY.price) - SALESPERSONS.salary) AS PROFIT
    FROM SALESPERSONS,
    ORDERS,
    ORDERITEMS,
    INVENTORY)
    0 rows updated.
    > @a8 -- suggested answer file projects correct salesperson(s) for raise [AFTER]
         EMPID ENAME                 RANK     SALARY
    ---------- --------------- ---------- ----------
           214 Dotty Domuch             1    1234.56

    > @SalespersonsProfits  -- Display all salespersons with salary, sales and profit
    Emp#  Salesperson     Rank Emp Salary Total Sales Good Profit | *BAD*Profit |
    ----- --------------- ---- ---------- ----------- ----------- - ----------- -
      101 Andrew Allen      1   $1,000.00     $917.40     -$82.60 |     -$82.60 |
      102 Burbank Burkett   1   $1,000.00     $640.35    -$359.65 |    -$359.65 |
      103 Charles Cox       2   $2,000.00   $1,069.45    -$930.55 |    -$930.55 |
      104 Dale Dahlman      2   $2,000.00   $2,464.40     $464.40 |     $464.40 |
      105 Edward Everling   2   $2,200.00   $1,040.45  -$1,159.55 |  -$1,159.55 |
      106 Faulkner Forest   3   $2,500.00     $577.00  -$1,923.00 |  -$1,923.00 |
      107 Gloria Garcia     3   $2,500.00     $146.35  -$2,353.65 |  -$2,353.65 |
      108 Harvey Harrison   3   $3,000.00   $2,140.00    -$860.00 |    -$860.00 |
      109 Kevin Kody        3   $3,100.00     $264.50  -$2,835.50 |  -$2,835.50 |
      110 Larry Little      1   $1,500.00        $.00  -$1,500.00 |        $.00 |
      204 Dan Dahlman       2   $2,000.00   $2,464.40     $464.40 |     $464.40 |
      214 Dotty Domuch      1   $1,234.56   $2,687.48   $1,452.92 |   $1,452.92 |
      221 Lori Little       1   $1,919.19        $.00  -$1,919.19 |        $.00 |
      400 Larry Little Jr   1   $4,444.44        $.00  -$4,444.44 |        $.00 |
      404 DaleAnn Dahlman   2   $2,000.00   $2,464.40     $464.40 |     $464.40 |
      414 Vicki's Answer    1   $1,100.00        $.00  -$1,100.00 |        $.00 |

     16 rows selected

    > @SalesDB_ORDERSreport  -- Display status of ORDERS prior to student clean up
    OrderID OrderDate Lines Note        
    ------- --------- ----- -------------
     4000   02/22/14     0  No line items
     4442   02/22/14     2                
     6000   02/22/14     0  No line items
     6099   12/15/95     5                
     6107   11/15/95     0  No line items
     6109   12/15/95     4                
     6124   12/12/95     0  No line items
     6128   11/15/95     2                
     6129   12/15/95     3                
     6148   12/15/95     7                
     6155   12/12/95     5                
     6157   12/15/95     4                
     6168   12/15/95     6                
     6174   12/12/95     9                
     6175   12/12/95     9                
     6196   11/15/95    10                
     6215   12/12/95    10                
     6216   10/12/95     7                
     6217   11/15/95     8                
     6218   12/15/95    10                
     6219   12/15/95     6                
     6227   10/15/95    10                
     6228   11/15/95     8                
     6237   10/15/95    10                
     6238   11/15/95     2                
     6239   12/15/95    10                
     6314   02/22/14    13                
     6315   02/22/14     3                
     9129   02/22/14     3                
     9174   02/22/14     9                
     9219   02/22/14     6                
     9228   02/22/14     8                
     9237   02/22/14    10                

     33 rows selected

    > set ECHO ON
    > /*
    --------------------------------------------------------------------------------
    Lab4 q9.    Clean up the orders.
    DELETE rows from the ORDERS table that are not associated with any rows
    in ORDERITEMS, i.e. remove the orders having no line items.
    STUDENT submission code and results followed by answer results.
    --------------------------------------------------------------------------------
    */
    > @q9
    > /*

    9.  Clean up the orders.
    o   File to create:  q9.sql
    o   Projection: none
    o   Instructions: Write a DELETE query to delete rows from the ORDERS table that are not associated with any rows in ORDERITEMS, i.e. remove the orders having no line items.

    */
    > DELETE FROM ORDERS
    WHERE ORDERID NOT IN (SELECT ORDERID
                          FROM ORDERITEMS)
    4 rows deleted.
    > @a9 -- suggested answer file should return zero rows
    no rows selected


    > @SalesDB_ORDERSreport  -- Display status of ORDERS after student clean up
    OrderID OrderDate Lines Note        
    ------- --------- ----- -------------
     4442   02/22/14     2                
     6099   12/15/95     5                
     6109   12/15/95     4                
     6128   11/15/95     2                
     6129   12/15/95     3                
     6148   12/15/95     7                
     6155   12/12/95     5                
     6157   12/15/95     4                
     6168   12/15/95     6                
     6174   12/12/95     9                
     6175   12/12/95     9                
     6196   11/15/95    10                
     6215   12/12/95    10                
     6216   10/12/95     7                
     6217   11/15/95     8                
     6218   12/15/95    10                
     6219   12/15/95     6                
     6227   10/15/95    10                
     6228   11/15/95     8                
     6237   10/15/95    10                
     6238   11/15/95     2                
     6239   12/15/95    10                
     6314   02/22/14    13                
     6315   02/22/14     3                
     9129   02/22/14     3                
     9174   02/22/14     9                
     9219   02/22/14     6                
     9228   02/22/14     8                
     9237   02/22/14    10                

     29 rows selected

    > set ECHO ON
    > /*
    --------------------------------------------------------------------------------
    Lab4 q10.   Provide your thoughts about the Oracle process, queries, the lab
    questions, whatever, and please include an estimate of the amount of time you
    spent on this lab. This is basically a free-bee five points if you write a
    paragraph. Feel free to vent or rave about your experiences so far with Oracle
    and not just your install/configuration issues. Please write enough to be worth
    five points! Enclose everything in a comment box or use line commenting.
    --------------------------------------------------------------------------------
    */
    > @q10
    > /*

    10. Provide your thoughts about the Oracle process, queries, the lab questions, whatever, and please include an estimate of the amount of time you spent on this lab. This is basically a free-bee five points if you write a paragraph here. Feel free to vent or rave about your experiences so far with Oracle and not just your install/configuration issues. Please write enough to be worth five points! Thanks.
    o   File to create:  q10.sql
    o   Projection: none
    o   Instructions: Enclose everything in a comment box or use line commenting.

    */
    > /*
    I hate ORACLE. I hate it with a passion. I also hate the documentation for it, or the lack there of, and I hate SQL developer.
    I think the program is garbage and its poorly designed. It's obviously not meant for Windows OS in mind, and the installation process
    was also lazy. The developers of SQL Developer should get fired. I spent 7 days on this assignment and now I'm pissed.
    */
    > @EndRun
    anonymous block completed
    ********************************************************************************                                         22-FEB-14
    ********************************************************************************



     
    She's gonna fail me and I've done every assignment. I did the math, it is now mathmatically impossible for me to score higher than a D, which is a fail because this is a required course for me.

    She's apparently holding a grudge against me because I don't like ORACLE. She asked for my opinion in question 10 and I told her. She also threw a fit that I was working through the assignments too fast. If you don't want students to work through the assignments too fast then make the assignments only available on certain dates. :rolleyes:

    Is my assignment as bad as she makes it out to be?
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    For the first question it should have used NOT EXISTS so you return the customers who have NOT placed orders; your query returned those who HAVE placed orders:


    Code (SQL):

    SELECT CUSTOMERS.CUSTID,
    CUSTOMERS.CNAME
    FROM CUSTOMERS
    WHERE NOT EXISTS (SELECT 1
      FROM ORDERS
      WHERE CUSTOMERS.CUSTID = ORDERS.CUSTID);
     

    What is interesting is that you did get the NOT EXISTS condition correct for the delete of customers. Question 3 needs an outer join as you need to return NULL for those salespeople who haven't sold anything (no orders are associated with their ids). And again for Question 4 -- an outer join is necessary since there are entries in the ORDERS table which have no line items in the ORDERITEMS table. A query of ORDERS and ORDERITEMS would have shown that:


    Code (SQL):

    SELECT o.orderid, oi.partid
    FROM orders o LEFT OUTER JOIN orderitems oi ON (oi.orderid = o.orderid);
     

    The above query would return non-null partid values for orders having line items and NULL partid values for orders without any line items.


    Your instructor is correct -- you are, apparently, working through these much faster than you should, trying to solve the problems before having the information necessary to correctly do so. And coming here to get help when you haven't had the necessary instruction in class is another big mistake as we can help you with the code but cannot make you understand the concepts.


    You say she's holding a grudge against you but I cannot see any reason why she would do that. I do see that she's frustrated with your headstrong approach to working the problems before having the suitable instruction necessary to properly work on them. I suppose you're trying to get through the course as fast as possible since, in your own words, "I don't like ORACLE". You don't give any reason, valid or otherwise, as to WHY you don't like Oracle; I can only surmise it's because you are having a difficult time understanding it, which is not made any better by rushing through assignments and expecting us to help you through them.


    You can't put the blame on your instructor if your grade is not what you need it to be; rushing through assignments never helps anyone and usually creates more harm than good. Your instructor is there to HELP you, not hinder you. You should ask questions of HER when you don't understand a topic; we certainly can't help you as we are not teaching her course.
     
    Sadik likes this.