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.
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.
SET SERVEROUTPUT ON
v_fname RESULTS.FIRST_NAME%TYPE :='JACK';
v_exam_id results.exam_id%TYPE := 530;
v_asterisk results.stars%TYPE := NULL;
SELECT First_name, Exam_id, Marks, Stars
INTO v_fname, v_exam_id, v_marks, v_asterisk
Where First_Name=V_Fname And Exam_Id=V_Exam_Id;
For I In 1..Round(V_Marks/20,0) Loop
When V_Marks = 100 Then Update Results Set Stars='*****' Where First_Name='JACK' And Exam_Id=530;
When V_Marks Between 80 And 99 Then Update Results Set Stars='****' Where First_Name='JACK' And Exam_Id=530;
When V_Marks Between 60 And 79 Then Update Results Set Stars='***' Where First_Name='JACK' And Exam_Id=530;
When V_Marks Between 40 And 59 Then Update Results Set Stars='**' Where First_Name='JACK' And Exam_Id=530;
When V_Marks Between 20 And 39 Then Update Results Set Stars='*' Where First_Name='JACK' And Exam_Id=530;
When V_Marks Between 0 And 19 Then Update Results Set Stars=Null Where First_Name='JACK' And Exam_Id=530;
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)
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.