Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- CREATE TABLE test_question
- (
- tq_id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
- q_text VARCHAR (256) NOT NULL
- );
- INSERT INTO test_question (q_text) VALUES ('q1');
- INSERT INTO test_question (q_text) VALUES ('q2');
- INSERT INTO test_question (q_text) VALUES ('q3');
- INSERT INTO test_question (q_text) VALUES ('q4');
- INSERT INTO test_question (q_text) VALUES ('q5');
- INSERT INTO test_question (q_text) VALUES ('q6');
- CREATE TABLE assigned_question
- (
- aq_id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
- st_id INTEGER,
- qu_id INTEGER,
- q_text VARCHAR (256)
- );
- INSERT INTO assigned_question (st_id, qu_id)
- (
- WITH RECURSIVE student AS (
- SELECT 1 AS s_no
- UNION ALL
- SELECT s_no + 1 AS value
- FROM student
- WHERE student.s_no <= 5 -- students - 6 questions
- ),
- question AS (
- SELECT 1 AS q_no
- UNION ALL
- SELECT q_no + 1 AS value
- FROM question
- WHERE question.q_no <= 4 -- students - 5 questions
- )
- SELECT * FROM student
- CROSS JOIN question
- ORDER BY s_no, RAND()
- );
- SELECT aq.aq_id, aq.st_id, aq.qu_id, tq.q_text
- FROM assigned_question aq
- JOIN test_question tq
- ON aq.qu_id = tq.tq_id;
- aq_id st_id qu_id q_text
- 1 1 2 q2
- 2 1 5 q5
- ... results omitted for brevity
- 6 2 4 q4
- 7 2 3 q3
- aq_id st_id qu_id q_text
- 1 1 4 q4
- 2 1 1 q1
- 3 1 3 q3
- 4 1 2 q2
- 5 1 5 q5
- 6 2 4 q4
- 7 2 3 q3
- 8 2 1 q1
- 9 2 5 q5
- 10 2 2 q2
- 11 3 2 q2
- 12 3 3 q3
- 13 3 4 q4
- 14 3 1 q1
- 15 3 5 q5
- 16 4 4 q4
- 17 4 2 q2
- 18 4 3 q3
- 19 4 1 q1
- 20 4 5 q5
- 21 5 2 q2
- 22 5 4 q4
- 23 5 3 q3
- 24 5 1 q1
- 25 5 5 q5
- 26 6 5 q5
- 27 6 1 q1
- 28 6 3 q3
- 29 6 4 q4
- 30 6 2 q2
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement