# 📝 Quiz Database Design Tutorial: PostgreSQL Schema & Commands

🎯 What You'll Learn

By the end of this tutorial, you will:

Design flexible quiz and question tables using PostgreSQL
Store multiple question types (MCQ, true/false, fill-in-blank, matching, code) in one schema
Use JSONB for dynamic options and correct answers
Write complete CRUD commands for quiz management
Build queries for quiz attempts, scoring, and analytics

📐 Part 1: Understanding the Quiz Tables

Why Separate `quizzes` and `quiz_questions`?

Table	Purpose	Analogy
`quizzes`	Quiz container (settings, rules, timing)	Test paper cover
`quiz_questions`	Individual questions inside the quiz	Questions on the paper

One quiz has many questions. This one-to-many relationship keeps settings separate from content.

🔍 Part 2: The `quizzes` Table — Quiz Settings

Schema Definition

sql
1CREATE TABLE quizzes (
2    quiz_id         UUID PRIMARY KEY DEFAULT gen_random_uuid(),
3    lesson_id       UUID NOT NULL REFERENCES module_lessons(lesson_id),
4    title           VARCHAR(200),
5    time_limit_mins INT,
6    pass_score      INT DEFAULT 70,
7    max_attempts    INT DEFAULT 3
8);

Column-by-Column Breakdown

Column	Type	Purpose	Example
`quiz_id`	UUID	Unique quiz identifier	`a1b2c3d4-...`
`lesson_id`	UUID FK	Links quiz to a lesson	Lesson "1.4 Quiz: HTML Basics"
`title`	VARCHAR(200)	Quiz display name	"HTML & CSS Fundamentals Quiz"
`time_limit_mins`	INT	Time allowed (NULL = unlimited)	15 minutes
`pass_score`	INT	Percentage needed to pass	70 = 70%
`max_attempts`	INT	How many tries allowed	3 attempts

INSERT Examples

sql
1-- Create a quiz for a specific lesson
2INSERT INTO quizzes (lesson_id, title, time_limit_mins, pass_score, max_attempts)
3VALUES (
4    'lesson-uuid-for-html-basics',
5    'HTML & CSS Fundamentals Quiz',
6    15,
7    70,
8    3
9)
10RETURNING quiz_id;
11-- Returns: quiz-uuid-123
12
13-- Create an untimed practice quiz
14INSERT INTO quizzes (lesson_id, title, pass_score, max_attempts)
15VALUES (
16    'lesson-uuid-for-js-practice',
17    'JavaScript Practice Quiz',
18    80,
19    5
20);
21
22-- Get all quizzes for a course
23SELECT 
24    q.quiz_id,
25    q.title,
26    q.time_limit_mins,
27    q.pass_score,
28    q.max_attempts,
29    ml.title AS lesson_title,
30    cm.title AS module_title
31FROM quizzes q
32JOIN module_lessons ml ON q.lesson_id = ml.lesson_id
33JOIN course_modules cm ON ml.module_id = cm.module_id
34WHERE cm.course_id = 'course-uuid-here'
35ORDER BY cm.module_order, ml.lesson_order;

🔍 Part 3: The `quiz_questions` Table — Question Bank

Schema Definition

sql
1CREATE TABLE quiz_questions (
2    question_id     UUID PRIMARY KEY DEFAULT gen_random_uuid(),
3    quiz_id         UUID NOT NULL REFERENCES quizzes(quiz_id),
4    question_text   TEXT NOT NULL,
5    question_type   VARCHAR(20) DEFAULT 'mcq'
6                    CHECK (question_type IN ('mcq', 'true_false', 'fill_blank', 'matching', 'code')),
7    options         JSONB,
8    correct_answer  JSONB,
9    points          INT DEFAULT 1,
10    order_index     INT
11);

Column-by-Column Breakdown

Column	Type	Purpose
`question_id`	UUID	Unique question ID
`quiz_id`	UUID FK	Parent quiz
`question_text`	TEXT	The actual question
`question_type`	VARCHAR + CHECK	Validates allowed types
`options`	JSONB	Answer choices (flexible per type)
`correct_answer`	JSONB	Correct response + explanation
`points`	INT	Score weight (default 1)
`order_index`	INT	Display order in quiz

Why `JSONB` for `options` and `correct_answer`?

Different question types need different data shapes:

MCQ: Array of 4 text options
True/False: Only 2 options
Matching: Pairs of items
Code: Language, starter code, expected output
Fill-in-blank: Array of acceptable answers

JSONB lets you store all these in one column without creating separate tables for each type.

🧩 Part 4: Question Type Examples with JSONB

Type 1: Multiple Choice (MCQ)

sql
1INSERT INTO quiz_questions (quiz_id, question_text, question_type, options, correct_answer, points, order_index)
2VALUES (
3    'quiz-uuid-123',
4    'What does HTML stand for?',
5    'mcq',
6    '["Hyper Text Markup Language", "High Tech Modern Language", "Hyper Transfer Markup Language", "Home Tool Markup Language"]',
7    '{"answer": 0, "explanation": "HTML stands for HyperText Markup Language. It is the standard markup language for documents designed to be displayed in a web browser."}',
8    1,
9    1
10);

JSONB structure:

json
1-- options
2["Option A", "Option B", "Option C", "Option D"]
3
4-- correct_answer
5{"answer": 0, "explanation": "Because..."}

Type 2: True or False

sql
1INSERT INTO quiz_questions (quiz_id, question_text, question_type, options, correct_answer, points, order_index)
2VALUES (
3    'quiz-uuid-123',
4    'The <div> tag is an inline element in HTML.',
5    'true_false',
6    '["True", "False"]',
7    '{"answer": 1, "explanation": "The <div> tag is a block-level element, not inline."}',
8    1,
9    2
10);

JSONB structure:

json
1-- options
2["True", "False"]
3
4-- correct_answer
5{"answer": 1, "explanation": "Block-level element..."}

Type 3: Fill in the Blank

sql
1INSERT INTO quiz_questions (quiz_id, question_text, question_type, options, correct_answer, points, order_index)
2VALUES (
3    'quiz-uuid-123',
4    'In CSS, the property used to change text color is ________.',
5    'fill_blank',
6    null,
7    '{"answers": ["color", "colour"], "explanation": "The color property sets the text color.", "case_sensitive": false}',
8    1,
9    3
10);

JSONB structure:

json
1-- options (null for fill_blank)
2null
3
4-- correct_answer
5{
6  "answers": ["color", "colour"],
7  "explanation": "The color property...",
8  "case_sensitive": false
9}

Type 4: Matching

sql
1INSERT INTO quiz_questions (quiz_id, question_text, question_type, options, correct_answer, points, order_index)
2VALUES (
3    'quiz-uuid-123',
4    'Match the CSS property with its function.',
5    'matching',
6    '[
7      {"left": "margin", "right": "Space outside the border"},
8      {"left": "padding", "right": "Space inside the border"},
9      {"left": "border", "right": "Edge around the element"},
10      {"left": "width", "right": "Total horizontal size"}
11    ]',
12    '{"pairs": [[0,1], [1,0], [2,2], [3,3]], "explanation": "Margin is outside, padding is inside."}',
13    2,
14    4
15);

JSONB structure:

json
1-- options
2[
3  {"left": "margin", "right": "Space outside..."},
4  {"left": "padding", "right": "Space inside..."}
5]
6
7-- correct_answer
8{"pairs": [[0,1], [1,0]], "explanation": "..."}

Type 5: Code Challenge

sql
1INSERT INTO quiz_questions (quiz_id, question_text, question_type, options, correct_answer, points, order_index)
2VALUES (
3    'quiz-uuid-123',
4    'Write a function that returns the sum of two numbers.',
5    'code',
6    '{"language": "javascript", "starter_code": "function sum(a, b) {\\n  // Your code here\\n}"}',
7    '{"expected_output": "3", "test_cases": [{"input": "sum(1,2)", "output": "3"}, {"input": "sum(5,5)", "output": "10"}], "explanation": "Simply return a + b"}',
8    3,
9    5
10);

JSONB structure:

json
1-- options
2{
3  "language": "javascript",
4  "starter_code": "function sum(a, b) {\n  // Your code here\n}"
5}
6
7-- correct_answer
8{
9  "expected_output": "3",
10  "test_cases": [
11    {"input": "sum(1,2)", "output": "3"},
12    {"input": "sum(5,5)", "output": "10"}
13  ],
14  "explanation": "Simply return a + b"
15}

⚡ Part 5: Querying Quiz Data

Get All Questions for a Quiz (in Order)

sql
1SELECT 
2    qz.title AS quiz_title,
3    qz.time_limit_mins,
4    qq.order_index,
5    qq.question_text,
6    qq.question_type,
7    qq.options,
8    qq.correct_answer,
9    qq.points
10FROM quizzes qz
11JOIN quiz_questions qq ON qz.quiz_id = qq.quiz_id
12WHERE qz.quiz_id = 'quiz-uuid-123'
13ORDER BY qq.order_index;

Get Quiz Summary (Total Points, Question Count)

sql
1SELECT 
2    qz.quiz_id,
3    qz.title,
4    COUNT(qq.question_id) AS total_questions,
5    COALESCE(SUM(qq.points), 0) AS total_points,
6    qz.pass_score,
7    qz.time_limit_mins
8FROM quizzes qz
9LEFT JOIN quiz_questions qq ON qz.quiz_id = qq.quiz_id
10WHERE qz.quiz_id = 'quiz-uuid-123'
11GROUP BY qz.quiz_id, qz.title, qz.pass_score, qz.time_limit_mins;

Find Quizzes by Question Type

sql
1-- Which quizzes have code questions?
2SELECT DISTINCT 
3    qz.quiz_id,
4    qz.title,
5    ml.title AS lesson_title
6FROM quizzes qz
7JOIN quiz_questions qq ON qz.quiz_id = qq.quiz_id
8JOIN module_lessons ml ON qz.lesson_id = ml.lesson_id
9WHERE qq.question_type = 'code';

Search Questions by Content

sql
1-- Find all questions containing "CSS"
2SELECT 
3    qz.title AS quiz_title,
4    qq.order_index,
5    qq.question_text,
6    qq.question_type
7FROM quiz_questions qq
8JOIN quizzes qz ON qq.quiz_id = qz.quiz_id
9WHERE qq.question_text ILIKE '%CSS%'
10ORDER BY qz.title, qq.order_index;

🏗️ Part 6: Quiz Attempts & Student Answers

Additional Table: `quiz_attempts`

sql
1CREATE TABLE quiz_attempts (
2    attempt_id      UUID PRIMARY KEY DEFAULT gen_random_uuid(),
3    enrollment_id   UUID NOT NULL REFERENCES enrollments(enrollment_id),
4    quiz_id         UUID NOT NULL REFERENCES quizzes(quiz_id),
5    started_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
6    completed_at    TIMESTAMP,
7    score           INT,
8    max_possible    INT,
9    percentage      DECIMAL(5,2),
10    passed          BOOLEAN,
11    attempt_number  INT DEFAULT 1
12);

Additional Table: `quiz_answers`

sql
1CREATE TABLE quiz_answers (
2    answer_id       UUID PRIMARY KEY DEFAULT gen_random_uuid(),
3    attempt_id      UUID NOT NULL REFERENCES quiz_attempts(attempt_id),
4    question_id     UUID NOT NULL REFERENCES quiz_questions(question_id),
5    student_answer  JSONB NOT NULL,
6    is_correct      BOOLEAN,
7    points_earned   INT DEFAULT 0,
8    answered_at     TIMESTAMP DEFAULT CURRENT_TIMESTAMP
9);

Example: Student Submits Answers

sql
1-- 1. Start attempt
2INSERT INTO quiz_attempts (enrollment_id, quiz_id, attempt_number)
3VALUES ('enroll-uuid', 'quiz-uuid-123', 1)
4RETURNING attempt_id;
5-- Returns: attempt-uuid-456
6
7-- 2. Record answer for MCQ (selected option 0)
8INSERT INTO quiz_answers (attempt_id, question_id, student_answer, is_correct, points_earned)
9VALUES (
10    'attempt-uuid-456',
11    'question-uuid-mcq-1',
12    '{"selected": 0}',
13    true,
14    1
15);
16
17-- 3. Record answer for fill-in-blank
18INSERT INTO quiz_answers (attempt_id, question_id, student_answer, is_correct, points_earned)
19VALUES (
20    'attempt-uuid-456',
21    'question-uuid-fill-1',
22    '{"text": "color"}',
23    true,
24    1
25);
26
27-- 4. Complete attempt and calculate score
28WITH score_calc AS (
29    SELECT 
30        qa.attempt_id,
31        SUM(qa.points_earned) AS earned,
32        SUM(qq.points) AS possible
33    FROM quiz_answers qa
34    JOIN quiz_questions qq ON qa.question_id = qq.question_id
35    WHERE qa.attempt_id = 'attempt-uuid-456'
36    GROUP BY qa.attempt_id
37)
38UPDATE quiz_attempts
39SET 
40    completed_at = CURRENT_TIMESTAMP,
41    score = sc.earned,
42    max_possible = sc.possible,
43    percentage = ROUND((sc.earned::DECIMAL / NULLIF(sc.possible, 0)) * 100, 2),
44    passed = (ROUND((sc.earned::DECIMAL / NULLIF(sc.possible, 0)) * 100, 2) >= q.pass_score)
45FROM score_calc sc, quizzes q
46WHERE quiz_attempts.attempt_id = sc.attempt_id
47AND quiz_attempts.quiz_id = q.quiz_id;

🧪 Part 7: Hands-On Learning Exercises

Exercise 1: Create a Complete Quiz

Write SQL to:

Create a quiz with 10-minute time limit and 60% pass score
Add 3 MCQ questions
Add 1 true/false question
Add 1 fill-in-blank question

Solution:

sql
1-- Step 1: Create quiz
2INSERT INTO quizzes (lesson_id, title, time_limit_mins, pass_score, max_attempts)
3VALUES (
4    'lesson-js-basics-uuid',
5    'JavaScript Basics Quiz',
6    10,
7    60,
8    3
9)
10RETURNING quiz_id;
11-- Assume quiz_id = 'qz-js-001'
12
13-- Step 2: Add questions
14INSERT INTO quiz_questions (quiz_id, question_text, question_type, options, correct_answer, points, order_index)
15VALUES
16('qz-js-001', 'What is the output of typeof null?', 'mcq', '["null", "object", "undefined", "number"]', '{"answer": 1, "explanation": "typeof null returns object due to a JS bug"}', 1, 1),
17('qz-js-001', 'Which keyword declares a constant variable?', 'mcq', '["var", "let", "const", "static"]', '{"answer": 2, "explanation": "const declares constants"}', 1, 2),
18('qz-js-001', 'JavaScript is a statically typed language.', 'true_false', '["True", "False"]', '{"answer": 1, "explanation": "JavaScript is dynamically typed"}', 1, 3),
19('qz-js-001', 'The function keyword to declare an arrow function uses _________.', 'fill_blank', null, '{"answers": ["=>", "arrow"], "explanation": "Arrow functions use =>"}', 1, 4);

Exercise 2: Validate Quiz Before Publishing

Write a query that checks if a quiz has at least 3 questions and total points >= 5.

Solution:

sql
1SELECT 
2    qz.quiz_id,
3    qz.title,
4    COUNT(qq.question_id) AS question_count,
5    COALESCE(SUM(qq.points), 0) AS total_points,
6    CASE 
7        WHEN COUNT(qq.question_id) >= 3 AND COALESCE(SUM(qq.points), 0) >= 5 
8        THEN 'READY TO PUBLISH' 
9        ELSE 'INCOMPLETE' 
10    END AS publish_status
11FROM quizzes qz
12LEFT JOIN quiz_questions qq ON qz.quiz_id = qq.quiz_id
13WHERE qz.quiz_id = 'qz-js-001'
14GROUP BY qz.quiz_id, qz.title;

Exercise 3: Get Student Quiz History

Write a query showing all quiz attempts for a student with pass/fail status.

Solution:

sql
1SELECT 
2    c.title AS course_title,
3    ml.title AS lesson_title,
4    qz.title AS quiz_title,
5    qa.attempt_number,
6    qa.score,
7    qa.max_possible,
8    qa.percentage,
9    qa.passed,
10    qa.completed_at
11FROM quiz_attempts qa
12JOIN quizzes qz ON qa.quiz_id = qz.quiz_id
13JOIN module_lessons ml ON qz.lesson_id = ml.lesson_id
14JOIN course_modules cm ON ml.module_id = cm.module_id
15JOIN courses c ON cm.course_id = c.course_id
16WHERE qa.enrollment_id = 'enroll-student-uuid'
17ORDER BY qa.completed_at DESC;

📊 Part 8: Performance Indexes

sql
1-- Essential indexes for quiz tables
2CREATE INDEX idx_quizzes_lesson ON quizzes(lesson_id);
3CREATE INDEX idx_quiz_questions_quiz ON quiz_questions(quiz_id);
4CREATE INDEX idx_quiz_questions_type ON quiz_questions(question_type);
5CREATE INDEX idx_quiz_questions_order ON quiz_questions(quiz_id, order_index);
6
7-- For attempts and answers
8CREATE INDEX idx_quiz_attempts_enrollment ON quiz_attempts(enrollment_id);
9CREATE INDEX idx_quiz_attempts_quiz ON quiz_attempts(quiz_id);
10CREATE INDEX idx_quiz_answers_attempt ON quiz_answers(attempt_id);
11CREATE INDEX idx_quiz_answers_question ON quiz_answers(question_id);
12
13-- JSONB index for searching questions by type within a quiz
14CREATE INDEX idx_quiz_questions_options ON quiz_questions USING GIN (options);
15CREATE INDEX idx_quiz_questions_correct ON quiz_questions USING GIN (correct_answer);

🎯 Quick Reference: Most Used Commands

sql
1-- 1. Get full quiz with all questions
2SELECT 
3    qz.title, qz.time_limit_mins, qz.pass_score,
4    qq.order_index, qq.question_text, qq.question_type, qq.options, qq.points
5FROM quizzes qz
6JOIN quiz_questions qq ON qz.quiz_id = qq.quiz_id
7WHERE qz.quiz_id = 'quiz-uuid'
8ORDER BY qq.order_index;
9
10-- 2. Duplicate a quiz (copy questions)
11WITH new_quiz AS (
12    INSERT INTO quizzes (lesson_id, title, time_limit_mins, pass_score, max_attempts)
13    SELECT lesson_id, title || ' (Copy)', time_limit_mins, pass_score, max_attempts
14    FROM quizzes WHERE quiz_id = 'old-quiz-uuid'
15    RETURNING quiz_id
16)
17INSERT INTO quiz_questions (quiz_id, question_text, question_type, options, correct_answer, points, order_index)
18SELECT nq.quiz_id, qq.question_text, qq.question_type, qq.options, qq.correct_answer, qq.points, qq.order_index
19FROM quiz_questions qq
20CROSS JOIN new_quiz nq
21WHERE qq.quiz_id = 'old-quiz-uuid';
22
23-- 3. Update question order
24UPDATE quiz_questions 
25SET order_index = 5 
26WHERE question_id = 'question-uuid';
27
28-- 4. Delete a question from a quiz
29DELETE FROM quiz_questions 
30WHERE question_id = 'question-uuid';

This quiz schema design gives you maximum flexibility for any question type while keeping queries fast and data integrity strong. Start with quizzes and quiz_questions, then add quiz_attempts and quiz_answers when you're ready to track student performance.

# 📝 Quiz Database Design Tutorial: PostgreSQL Schema & Commands

🎯 What You'll Learn

📐 Part 1: Understanding the Quiz Tables

Why Separate quizzes and quiz_questions?

🔍 Part 2: The quizzes Table — Quiz Settings

Schema Definition

Column-by-Column Breakdown

INSERT Examples

🔍 Part 3: The quiz_questions Table — Question Bank

Schema Definition

Column-by-Column Breakdown

Why JSONB for options and correct_answer?

🧩 Part 4: Question Type Examples with JSONB

Type 1: Multiple Choice (MCQ)

Type 2: True or False

Type 3: Fill in the Blank

Type 4: Matching

Type 5: Code Challenge

⚡ Part 5: Querying Quiz Data

Get All Questions for a Quiz (in Order)

Get Quiz Summary (Total Points, Question Count)

Find Quizzes by Question Type

Search Questions by Content

🏗️ Part 6: Quiz Attempts & Student Answers

Additional Table: quiz_attempts

Additional Table: quiz_answers

Example: Student Submits Answers

🧪 Part 7: Hands-On Learning Exercises

Exercise 1: Create a Complete Quiz

Exercise 2: Validate Quiz Before Publishing

Exercise 3: Get Student Quiz History

📊 Part 8: Performance Indexes

🎯 Quick Reference: Most Used Commands

Why Separate `quizzes` and `quiz_questions`?

🔍 Part 2: The `quizzes` Table — Quiz Settings

🔍 Part 3: The `quiz_questions` Table — Question Bank

Why `JSONB` for `options` and `correct_answer`?

Additional Table: `quiz_attempts`

Additional Table: `quiz_answers`