4 Replies Latest reply on Feb 1, 2018 2:45 PM by mathguy

    Remarks about the last problem (Assignment 6) in the Week 2 homework set

    mathguy

      Hi,

       

      I wasn't too impressed with a number of things about Assignment 6 in the Week 2 homework set.  I am sharing my observations here, perhaps I missed something - if so please share.

       

      1. Oracle itself, for the past 25 years (I'm told), has advocated in favor of SQL Standard syntax for joins. Yet the problem instructs us to use a CTAS statement where there is a join of three tables, using the old, last-century, deprecated, comma-syntax for joins. This is even harder to understand since this MOOC is offered by Oracle itself.

       

      2. In the Hints, there is mention of an explicit (hard-coded) course number, 530. Why? There is no such thing in the problem statement. The problem asks us to update the rows with first_name = 'JACK', nothing about any particular course number. Then: the problem says "print the updated RECORD" (notice the singular) when in fact three records are updated. This is a trivial issue, obviously, but it suggests poor quality control.

       

      3. Most importantly in my opinion: I understand that this is a PL/SQL course, so we are practicing doing things in PL/SQL even if they could be done more efficiently in plain SQL. Even so, though - even if we are going to do an UPDATE, say, in an anonymous block - we should not be encouraged (as we are in the Hints) to create unnecessary variables, perform unneeded assignments etc. The problem can be solved trivially, for example, as shown below. Whenever we use SQL within PL/SQL, the SQL statement should still be as efficient as possible, not the opposite.

       

       

      --  SPOILER ALERT:   Shown below is one way to solve the assignment, so if you don't want to see it yet, read no further!   --

       

       

       

       

      --  SPOILER ALERT:   Shown below is one way to solve the assignment, so if you don't want to see it yet, read no further!   --

       

       

       

       

      --  LAST WARNING:   Shown below is one way to solve the assignment, so if you don't want to see it yet, read no further!   --

       

       

       

       

       

      begin

        update results

        set    stars = rpad('*', marks/20, '*')

        where  first_name = 'JACK' and marks >= 20;

      end;

      /

        • 1. Re: Remarks about the last problem (Assignment 6) in the Week 2 homework set
          Anjani Pothula-Oracle

          Hi mathguy

           

          The main intention of these homework assignments are to give you a chance to practice the concepts that you learned in each week. These assignments are not promoting any Oracle standards.

           

          This assignment (#6) is given to let you practice implementing a FOR loop. Thats why, we asked you to write a FOR loop to calculate the number of stars for a particular student and a particular exam,

          This doesn't mean we are advocating hard coding. The only intention is to avoid nested loops. If the student and exam are not fixed, you will have to write a nested loop logic to calculate the number of stars for all the exams appeared by all the students! Same holds true with the script (JOIN query) given to create the RESULTS table. The  main intention is to have a table created that can be used to insert the STARS value.

           

          Finally, the SQL query you provided in your post is not the expected solution to this assignment. Try to solve the assignment using PL/SQL code FOR loop as instructed in the homework doc.

           

          Thanks,

          Anjani.

          • 2. Re: Remarks about the last problem (Assignment 6) in the Week 2 homework set
            3620791

            I came up with the following Code.

            It is working but would like some input due to the Loop appearing to be useless in this instance.

             

            1. SET SERVEROUTPUT ON

            2. DECLARE

            3. v_fname RESULTS.FIRST_NAME%TYPE :='JACK';

            4. v_exam_id results.exam_id%TYPE := 530;

            5. v_asterisk results.stars%TYPE := NULL;

            6. v_marks results.marks%TYPE;

            7. BEGIN

            8. SELECT First_name, Exam_id, Marks, Stars

            9. INTO v_fname, v_exam_id, v_marks, v_asterisk

            10. FROM results

            11. Where First_Name=V_Fname And Exam_Id=V_Exam_Id;

            12. For I In 1..Round(V_Marks/20,0) Loop

            13. Case

            14.     When V_Marks = 100 Then Update Results Set Stars='*****' Where First_Name='JACK' And Exam_Id=530;

            15.     When V_Marks Between  80 And 99 Then Update Results Set Stars='****' Where First_Name='JACK' And Exam_Id=530;

            16.     When V_Marks Between 60 And 79 Then Update Results Set Stars='***' Where First_Name='JACK' And Exam_Id=530;

            17.     When V_Marks Between 40 And 59 Then Update Results Set Stars='**' Where First_Name='JACK' And Exam_Id=530;

            18.     When V_Marks Between 20 And 39 Then Update Results Set Stars='*' Where First_Name='JACK' And Exam_Id=530;

            19.     When V_Marks Between 0 And 19 Then Update Results Set Stars=Null Where First_Name='JACK' And Exam_Id=530;

            20.   End Case;

            21. end LOOP;

            22. END;

            23. /

            • 3. Re: Remarks about the last problem (Assignment 6) in the Week 2 homework set
              Anjani Pothula-Oracle

              HI 3620791,

               

              You used CASE construct and hard coded the number of asterisks Therefore, you don't need a loop here. Your solution is not the expected one.

              We want you to add star for every 20 marks in a loop. Your loop construct is almost correct. But, instead of using CASE in the loop, use a simple assignment statement to append a star every time the control enters the loop...that's it !

               

              The expression used in your FOR loop is partially incorrect -

              For I In 1..Round(V_Marks/20,0) Loop - why is there a ,0 after v_marks/20? you should change it to NVL(ROUND(v_marks/20),0)

              Thanks,

              Anjani.

              • 4. Re: Remarks about the last problem (Assignment 6) in the Week 2 homework set
                mathguy

                I am not sure what is incorrect about ROUND(x, 0). The 0 means 0 decimal places (round to the nearest integer). That is the default for the ROUND function - ROUND(x, 0) means the same thing as ROUND(x), but this doesn’t make the first version ”incorrect”.

                 

                Rather, what is incorrect is the use of ROUND in the first place. The correct function to use for this problem is TRUNC. Indeed, a score of 15 did not “achieve 20 marks” so it should get 0 stars. ROUND(15/20) is 1, not 0.

                 

                Good call on the possibility that MARKS may be null. Indeed, the column is nullable - and the FOR loop doesn’t take kindly to NULL in the IN clause. So nulls must indeed be handled. I would have handled that differently - with an IF statement right before the FOR clause (allowing the specific handling of null MARKS in the ELSE clause, if needed - either now or in the future). But the main point is that nulls must be considered whenever we write code.