MariaDB 10.4

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE student 
(
  s_id INTEGER NOT NULL,
  s_name VARCHAR (20) NOT NULL,
  CONSTRAINT student_pk PRIMARY KEY (s_id)
);
CREATE TABLE quiz 
(
  q_id         INTEGER       NOT NULL,
  q_title   VARCHAR  (50) NOT NULL,
  CONSTRAINT ql_pk PRIMARY KEY (q_id)
);
CREATE TABLE student_score
(
  ss_s_id  INTEGER   NOT NULL,
  ss_q_id INTEGER   NOT NULL,
  score   INTEGER   NOT NULL,
  ts    TIMESTAMP NOT NULL,

  CONSTRAINT sp_pk PRIMARY KEY (ss_s_id, ss_q_id),
  CONSTRAINT sp_s_no_fk FOREIGN KEY (ss_s_id)  REFERENCES student (s_id),
  CONSTRAINT sp_ql_id   FOREIGN KEY (ss_q_id) REFERENCES quiz (q_id)
);
INSERT INTO student VALUES
(12345678, 'Student1_name'),
(40204123, 'Student2_name'),
(40213894, 'Student3_name'),
(98765432, 'Student4_name');   -- <<-- Extra student record! Not possibe under previous 
                               -- <<-- schema!
INSERT INTO quiz (q_id, q_title) 
VALUES
(1, 'Quiz 1'),
(2, 'Quiz 2'),
(3, 'Quiz 3'),
(4, 'Quiz 4');
INSERT INTO student_score (ss_s_id, ss_q_id, score, ts) VALUES
(12345678, 1, 80, '2021-01-15 16:07:43'),
(12345678, 2, 25, '2021-01-13 17:15:10'),
(12345678, 3, 90, '2021-01-17 22:41:55'),
(12345678, 4, 90, '2021-01-17 22:41:55'),

(40204123, 1, 80, '2021-01-12 15:37:11'),
(40204123, 2, 75, '2021-01-12 15:38:06'),
(40204123, 3, 30, '2021-01-13 22:13:13'),

(40213894, 1, 90, '2021-01-14 21:52:00'),
(40213894, 2, 95, '2021-01-17 22:42:50'),
(40213894, 4, 100, '2021-01-17 22:42:50');
SELECT q.*, ss.* 
FROM quiz q 
LEFT JOIN student_score ss 
  ON q.q_id = ss.ss_q_id 
WHERE ss.ss_s_id = 40204123 
OR ss.ss_s_id IS NULL;


q_id
q_title
ss_s_id
ss_q_id
score
ts


1
Quiz 1
40204123
1
80
2021-01-12 15:37:11

2
Quiz 2
40204123
2
75
2021-01-12 15:38:06

3
Quiz 3
40204123
3
30
2021-01-13 22:13:13


SELECT q.q_id, q.q_title
FROM quiz q;


q_id
q_title


1
Quiz 1

2
Quiz 2

3
Quiz 3

4
Quiz 4


SELECT 
  s_id, s_name 
FROM student s
WHERE s.s_id = 40204123;


s_id
s_name


40204123
Student2_name


  SELECT 
    q.q_id, q.q_title,
    s.s_id, s.s_name 
  FROM quiz q
  CROSS JOIN student s
  WHERE s.s_id = 40204123;


q_id
q_title
s_id
s_name


1
Quiz 1
40204123
Student2_name

2
Quiz 2
40204123
Student2_name

3
Quiz 3
40204123
Student2_name

4
Quiz 4
40204123
Student2_name


SELECT 
  q.q_id, q.q_title,
  s.s_id, s.s_name, COALESCE(ss.score, 0) AS score
FROM quiz q
CROSS JOIN student s
LEFT JOIN student_score ss
  ON ss.ss_s_id = s.s_id
  AND ss.ss_q_id = q.q_id
WHERE s.s_id IN (40204123, 40213894)
ORDER BY s.s_id, q.q_id;


q_id
q_title
s_id
s_name
score


1
Quiz 1
40204123
Student2_name
80

2
Quiz 2
40204123
Student2_name
75

3
Quiz 3
40204123
Student2_name
30

4
Quiz 4
40204123
Student2_name
0

1
Quiz 1
40213894
Student3_name
90

2
Quiz 2
40213894
Student3_name
95

3
Quiz 3
40213894
Student3_name
0

4
Quiz 4
40213894
Student3_name
100


SELECT 
  q.q_id, q.q_title,
  s.s_id, s.s_name, COALESCE(ss.score, 0) AS score
FROM quiz q
CROSS JOIN student s
LEFT JOIN student_score ss
  ON ss.ss_s_id = s.s_id
  AND ss.ss_q_id = q.q_id
ORDER BY s.s_id, q.q_id;


q_id
q_title
s_id
s_name
score


1
Quiz 1
12345678
Student1_name
80

2
Quiz 2
12345678
Student1_name
25

3
Quiz 3
12345678
Student1_name
90

4
Quiz 4
12345678
Student1_name
90

1
Quiz 1
40204123
Student2_name
80

2
Quiz 2
40204123
Student2_name
75

3
Quiz 3
40204123
Student2_name
30

4
Quiz 4
40204123
Student2_name
0

1
Quiz 1
40213894
Student3_name
90

2
Quiz 2
40213894
Student3_name
95

3
Quiz 3
40213894
Student3_name
0

4
Quiz 4
40213894
Student3_name
100

1
Quiz 1
98765432
Student4_name
0

2
Quiz 2
98765432
Student4_name
0

3
Quiz 3
98765432
Student4_name
0

4
Quiz 4
98765432
Student4_name
0


--
--  Can do it this way - not recommended! See performance analysis!
--


SELECT
  tab.q_id, tab.q_title,
  tab.s_id, tab.s_name, COALESCE(ss.score, 0) AS the_score
FROM
  student_score ss
RIGHT JOIN
(
  SELECT 
    q.q_id, q.q_title,
    s.s_id, s.s_name 
  FROM quiz q
  CROSS JOIN
  student s
  WHERE s.s_id IN (40204123, 40213894)  -- test with and without
) AS tab
ON ss.ss_s_id = tab.s_id
AND ss.ss_q_id = tab.q_id
ORDER BY tab.s_id, tab.q_id;


q_id
q_title
s_id
s_name
the_score


1
Quiz 1
40204123
Student2_name
80

2
Quiz 2
40204123
Student2_name
75

3
Quiz 3
40204123
Student2_name
30

4
Quiz 4
40204123
Student2_name
0

1
Quiz 1
40213894
Student3_name
90

2
Quiz 2
40213894
Student3_name
95

3
Quiz 3
40213894
Student3_name
0

4
Quiz 4
40213894
Student3_name
100

q_id	q_title	ss_s_id	ss_q_id	score	ts
1	Quiz 1	40204123	1	80	2021-01-12 15:37:11
2	Quiz 2	40204123	2	75	2021-01-12 15:38:06
3	Quiz 3	40204123	3	30	2021-01-13 22:13:13

q_id	q_title	s_id	s_name
1	Quiz 1	40204123	Student2_name
2	Quiz 2	40204123	Student2_name
3	Quiz 3	40204123	Student2_name
4	Quiz 4	40204123	Student2_name

q_id	q_title	s_id	s_name	score
1	Quiz 1	12345678	Student1_name	80
2	Quiz 2	12345678	Student1_name	25
3	Quiz 3	12345678	Student1_name	90
4	Quiz 4	12345678	Student1_name	90
1	Quiz 1	40204123	Student2_name	80
2	Quiz 2	40204123	Student2_name	75
3	Quiz 3	40204123	Student2_name	30
4	Quiz 4	40204123	Student2_name	0
1	Quiz 1	40213894	Student3_name	90
2	Quiz 2	40213894	Student3_name	95
3	Quiz 3	40213894	Student3_name	0
4	Quiz 4	40213894	Student3_name	100
1	Quiz 1	98765432	Student4_name	0
2	Quiz 2	98765432	Student4_name	0
3	Quiz 3	98765432	Student4_name	0
4	Quiz 4	98765432	Student4_name	0