Advertisement
Guest User

Untitled

a guest
Jun 24th, 2019
65
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 1.81 KB | None | 0 0
  1. CREATE TABLE test_question
  2. (
  3. tq_id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
  4. q_text VARCHAR (256) NOT NULL
  5. );
  6.  
  7. INSERT INTO test_question (q_text) VALUES ('q1');
  8. INSERT INTO test_question (q_text) VALUES ('q2');
  9. INSERT INTO test_question (q_text) VALUES ('q3');
  10. INSERT INTO test_question (q_text) VALUES ('q4');
  11. INSERT INTO test_question (q_text) VALUES ('q5');
  12. INSERT INTO test_question (q_text) VALUES ('q6');
  13.  
  14. CREATE TABLE assigned_question
  15. (
  16. aq_id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
  17. st_id INTEGER,
  18. qu_id INTEGER,
  19. q_text VARCHAR (256)
  20. );
  21.  
  22. INSERT INTO assigned_question (st_id, qu_id)
  23. (
  24. WITH RECURSIVE student AS (
  25. SELECT 1 AS s_no
  26. UNION ALL
  27. SELECT s_no + 1 AS value
  28. FROM student
  29. WHERE student.s_no <= 5 -- students - 6 questions
  30. ),
  31. question AS (
  32. SELECT 1 AS q_no
  33. UNION ALL
  34. SELECT q_no + 1 AS value
  35. FROM question
  36. WHERE question.q_no <= 4 -- students - 5 questions
  37. )
  38. SELECT * FROM student
  39. CROSS JOIN question
  40. ORDER BY s_no, RAND()
  41. );
  42.  
  43. SELECT aq.aq_id, aq.st_id, aq.qu_id, tq.q_text
  44. FROM assigned_question aq
  45. JOIN test_question tq
  46. ON aq.qu_id = tq.tq_id;
  47.  
  48. aq_id st_id qu_id q_text
  49. 1 1 2 q2
  50. 2 1 5 q5
  51. ... results omitted for brevity
  52. 6 2 4 q4
  53. 7 2 3 q3
  54.  
  55. aq_id st_id qu_id q_text
  56. 1 1 4 q4
  57. 2 1 1 q1
  58. 3 1 3 q3
  59. 4 1 2 q2
  60. 5 1 5 q5
  61.  
  62. 6 2 4 q4
  63. 7 2 3 q3
  64. 8 2 1 q1
  65. 9 2 5 q5
  66. 10 2 2 q2
  67.  
  68. 11 3 2 q2
  69. 12 3 3 q3
  70. 13 3 4 q4
  71. 14 3 1 q1
  72. 15 3 5 q5
  73.  
  74. 16 4 4 q4
  75. 17 4 2 q2
  76. 18 4 3 q3
  77. 19 4 1 q1
  78. 20 4 5 q5
  79.  
  80. 21 5 2 q2
  81. 22 5 4 q4
  82. 23 5 3 q3
  83. 24 5 1 q1
  84. 25 5 5 q5
  85.  
  86. 26 6 5 q5
  87. 27 6 1 q1
  88. 28 6 3 q3
  89. 29 6 4 q4
  90. 30 6 2 q2
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement