Changes between Version 7 and Version 8 of DatabaseProgramming-AdvDb

Timestamp:

06/08/26 08:46:51 (8 days ago)

Author:

231175

Comment:

--

DatabaseProgramming-AdvDb

@@ -77 +77 @@
                        then ((v_earned_points::numeric / v_total_points) * 100)::int
                    else 0 end;
-    
+
     v_passed := v_score >= v_passing_score;
 
@@ -89 +89 @@
 
     insert into quiz_attempt_answer (correct, quiz_question_id, quiz_attempt_id)
-    select qao.correct,
-           (a ->> 'question_id')::bigint,
-           v_attempt_id
+    select
+        -- all correct options were selected AND no incorrect options were selected
+        (count(*) filter (where qao.correct = true) =
+         (select count(*)
+          from quiz_answer_option
+          where quiz_question_id = (a ->> 'question_id')::bigint
+            and correct = true))
+            and
+        (count(*) filter (where qao.correct = false) = 0),
+        (a ->> 'question_id')::bigint,
+        v_attempt_id
     from jsonb_array_elements(p_answers) a
-             join quiz_answer_option qao on qao.id = (a ->> 'answer_option_id')::bigint;
+             join quiz_answer_option qao on qao.id = (a ->> 'answer_option_id')::bigint
+    group by (a ->> 'question_id');
 
     insert into quiz_attempt_answer_selected_options (quiz_attempt_answer_id, quiz_answer_option_id)