MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE `exam` (
  `sub_code` varchar(3) NOT NULL,
  `roll_no` int NOT NULL,
  `marks` int DEFAULT NULL,
  `test` varchar(3) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
CREATE TABLE `student` (
  `roll_no` int NOT NULL,
  `name` varchar(20) DEFAULT NULL,
  `stream` varchar(5) DEFAULT NULL,
  PRIMARY KEY (`roll_no`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
CREATE TABLE `subject` (
  `sub_code` varchar(3) NOT NULL,
  `name` varchar(30) DEFAULT NULL,
  PRIMARY KEY (`sub_code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
INSERT INTO subject
VALUES("301", "English"),
("041","Maths"),
("042","Physics"),
("043","Chemistry"),
("065","Informatics Practices"),
("055","Accountancy"),
("030","Economics"),
("054","Business Studies"),
("040","Biology");
--INSERT INTO subject VALUES('044', 'Missing Subject');

-- add in the line above to eliminate NULLs in the query 
-- at the bottom marked "Missing subject"!
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '--INSERT INTO subject VALUES('044', 'Missing Subject');

-- add in the line abov' at line 1

-- MT1


-- Maths:
INSERT INTO exam
VALUES
("041", 1 , 29, "MT1"),
("041", 2, 43, "MT1" ),
("041", 6, 37, "MT1"),
("041", 7, 33, "MT1"),
("041", 10, 40, "MT1"),
("041", 12, 44, "MT1"),
("041", 13, 23, "MT1"),
("041", 14, 25, "MT1"),
("041", 17, 30, "MT1"),
("041", 18, 39, "MT1"),
("041", 19, 27, "MT1"),
("041", 20, 28, "MT1");


-- Accountancy:
INSERT INTO exam
VALUES
("055", 2, 32, "MT1"),
("055", 4, 39, "MT1"),
("055", 7, 42, "MT1"),
("055", 8, 40, "MT1"),
("055", 9, 45, "MT1"),
("055", 11, 40, "MT1"),
("055", 12, 39, "MT1"),
("055", 13, 29, "MT1"),
("055", 15, 42, "MT1"),
("055", 18, 45, "MT1");



-- IP:
INSERT INTO exam
VALUES
("065", 1, 46, "MT1"),
("065" ,3, 39, "MT1"),
("065", 4, 39, "MT1"),
("065", 5, 35, "MT1"),
("065", 6, 44, "MT1"),
("065", 8, 41, "MT1"),
("065", 9, 49, "MT1"),
("065", 10, 46, "MT1"),
("065", 11, 40, "MT1"),
("065", 14, 29, "MT1"),
("065", 15, 35, "MT1"),
("065", 16, 38, "MT1"),
("065", 17, 44, "MT1"),
("065", 19, 30, "MT1"),
("065", 20, 30, "MT1");

-- Eco:
INSERT INTO exam
VALUES
("030", 2, 37, "MT1"),
("030", 4, 44 , "MT1"),
("030", 7, 35, "MT1"),
("030", 8, 38, "MT1"),
("030", 9, 34, "MT1"),
("030", 11, 39, "MT1"),
("030", 12, 43, "MT1"),
("030", 13, 35, "MT1"),
("030", 15, 40, "MT1"),
("030", 18, 35, "MT1");

-- BST:
INSERT INTO exam
VALUES
("054", 2, 34, "MT1"),
("054", 4, 47, "MT1"), 
("054", 7, 26, "MT1"),
("054", 8, 37, "MT1"),
("054", 9 , 34, "MT1"),
("054", 11, 37, "MT1"),
("054", 12, 41, "MT1"),
("054", 13, 38, "MT1"),
("054",15 , 35, "MT1"),
("054",18 , 29, "MT1");

-- Chem:
INSERT INTO exam
VALUES
("043", 1, 42, "MT1"),
("043", 3, 37, "MT1"),
("043", 5, 42, "MT1"),
("043", 6, 42, "MT1"),
("043", 10, 30, "MT1"),
("043", 14, 35, "MT1"),
("043", 16, 35, "MT1"),
("043", 17, 29, "MT1"),
("043", 19, 28, "MT1"),
("043", 20, 30, "MT1");

-- Bio:
INSERT INTO exam
VALUES
("044",3,29,"MT1"),
("044",5,39,"MT1"),
("044",16,43,"MT1");

-- Physics:
INSERT INTO exam
VALUES
("042",1,36,"MT1"),
("042",3,34,"MT1"),
("042",5,40,"MT1"),
("042",6,39,"MT1"),
("042",10,37,"MT1"),
("042",14,32,"MT1"),
("042",16,39,"MT1"),
("042",17,38,"MT1"),
("042",19,45,"MT1"),
("042",20,34,"MT1");

-- English:
INSERT INTO exam
VALUES
("301", 1, 38, "MT1"),
("301", 2, 45, "MT1"),
("301", 3, 40, "MT1"),
("301", 4, 44, "MT1"),
("301", 5, 44, "MT1"),
("301", 6, 40, "MT1"),
("301", 7, 37, "MT1"),
("301", 8, 39, "MT1"),
("301", 9, 29, "MT1"),
("301", 10, 43, "MT1"),
("301", 11, 44, "MT1"),
("301", 12, 40, "MT1"),
("301", 13, 28, "MT1"),
("301", 14, 21, "MT1"),
("301", 15, 40, "MT1"),
("301", 16, 39, "MT1"),
("301", 17, 47, "MT1"),
("301", 18, 35, "MT1"),
("301", 19, 39, "MT1"),
("301", 20, 37, "MT1");

-- MT2

-- Maths:
INSERT INTO exam
VALUES
("041", 1 , 37, "MT2"),
("041", 2, 44, "MT2" ),
("041", 6, 41, "MT2"),
("041", 7, 38, "MT2"),
("041", 10, 43, "MT2"),
("041", 12, 44, "MT2"),
("041", 13, 30, "MT2"),
("041", 14, 28, "MT2"),
("041", 17, 35, "MT2"),
("041", 18, 40, "MT2"),
("041", 19, 39, "MT2"),
("041", 20, 29, "MT2");

-- Chem:
INSERT INTO exam
VALUES
("043", 1, 45, "MT2"),
("043", 3, 43, "MT2"),
("043", 5, 39, "MT2"),
("043", 6, 39, "MT2"),
("043", 10, 25, "MT2"),
("043", 14, 38, "MT2"),
("043", 16, 44, "MT2"),
("043", 17, 37, "MT2"),
("043", 19, 37, "MT2"),
("043", 20, 35, "MT2");

 -- BST:
INSERT INTO exam
VALUES
("054", 2, 40 , "MT2"),
("054", 4, 45, "MT2"), 
("054", 7, 38, "MT2"),
("054", 8, 43, "MT2"),
("054", 9 , 32, "MT2"),
("054", 11, 39, "MT2"),
("054", 12, 38, "MT2"),
("054", 13, 33, "MT2"),
("054", 15 , 42, "MT2"),
("054", 18 , 38, "MT2");

-- Eco:
INSERT INTO exam
VALUES
("030", 2, 38, "MT2"),
("030", 4, 39 , "MT2"),
("030", 7, 30, "MT2"),
("030", 8, 40, "MT2"),
("030", 9, 44, "MT2"),
("030", 11, 40, "MT2"),
("030", 12, 40, "MT2"),
("030", 13, 35, "MT2"),
("030", 15, 38, "MT2"),
("030", 18, 37, "MT2");

-- IP
INSERT INTO exam
VALUES
("065", 1, 48, "MT2"),
("065" ,3, 42, "MT2"),
("065", 4, 44, "MT2"),
("065", 5, 40, "MT2"),
("065", 6, 38, "MT2"),
("065", 8, 38, "MT2"),
("065", 9, 42, "MT2"),
("065",10, 33, "MT2"),
("065", 11, 43, "MT2"),
("065", 14, 33, "MT2"),
("065", 15, 40, "MT2"),
("065", 16, 42, "MT2"),
("065", 17, 40, "MT2"),
("065", 19, 39, "MT2"),
("065", 20, 37, "MT2");

-- Phy:
INSERT INTO exam
VALUES
("042", 1, 43, "MT2"),
("042", 3, 35, "MT2"),
("042", 5, 42, "MT2"),
("042", 6, 42, "MT2"),
("042", 10, 19, "MT2"),
("042", 14, 22, "MT2"),
("042", 16, 41, "MT2"),
("042", 17, 43, "MT2"),
("042", 19, 28, "MT2"),
("042", 20, 39, "MT2");


-- Accountancy
INSERT INTO exam
VALUES
("055", 2, 29, "MT2"),
("055", 4, 41, "MT2"),
("055", 7, 40, "MT2"),
("055", 8, 39, "MT2"),
("055", 9, 43, "MT2"),
("055", 11, 37, "MT2"),
("055", 12, 41, "MT2"),
("055", 13, 28, "MT2"),
("055", 15, 47, "MT2"),
("055", 18, 37, "MT2");

-- English:
INSERT INTO exam
VALUES
("301", 1, 40, "MT2"),
("301", 2, 45, "MT2"),
("301", 3, 49, "MT2"),
("301", 4, 45, "MT2"),
("301", 5, 41, "MT2"),
("301", 6, 44, "MT2"),
("301", 7, 40, "MT2"),
("301", 8, 44, "MT2"),
("301", 9, 33, "MT2"),
("301", 10, 40, "MT2"),
("301", 11, 40, "MT2"),
("301", 12, 37, "MT2"),
("301", 13, 31, "MT2"),
("301", 14, 26, "MT2"),
("301", 15, 33, "MT2"),
("301", 16, 42, "MT2"),
("301", 17, 39, "MT2"),
("301", 18, 41, "MT2"),
("301", 19, 41, "MT2"),
("301", 20, 40, "MT2");

-- Bio
INSERT INTO exam
VALUES
("044",3,33,"MT2"),
("044",5,40,"MT2"),
("044",16,38,"MT2");

-- MT3

-- Maths:
INSERT INTO exam
VALUES
("041", 1 , 42, "MT3"),
("041", 2, 37, "MT3" ),
("041", 6, 43, "MT3"),
("041", 7, 42, "MT3"),
("041", 10, 36, "MT3"),
("041", 12, 40, "MT3"),
("041", 13, 45, "MT3"),
("041", 14, 40, "MT3"),
("041", 17, 40, "MT3"),
("041", 18, 44, "MT3"),
("041", 19, 42, "MT3"),
("041", 20, 35, "MT3");

-- Chem:
INSERT INTO exam
VALUES
("043", 1, 37, "MT3"),
("043", 3, 44, "MT3"),
("043", 5, 43, "MT3"),
("043", 6, 41, "MT3"),
("043", 10, 28, "MT3"),
("043", 14, 40, "MT3"),
("043", 16, 38, "MT3"),
("043", 17, 43, "MT3"),
("043", 19, 42, "MT3"),
("043", 20, 39, "MT3");

-- Eco:
INSERT INTO exam
VALUES
("030", 2, 33, "MT3"),
("030", 4, 40 , "MT3"),
("030", 7, 38, "MT3"),
("030", 8, 43, "MT3"),
("030", 9, 45, "MT3"),
("030", 11, 42, "MT3"),
("030", 12, 39, "MT3"),
("030", 13, 43, "MT3"),
("030", 15, 45, "MT3"),
("030", 18, 42, "MT3");

-- IP
INSERT INTO exam
VALUES
("065", 1, 45, "MT3"),
("065" ,3, 47, "MT3"),
("065", 4, 42, "MT3"),
("065", 5, 39, "MT3"),
("065", 6, 40, "MT3"),
("065", 8, 40, "MT3"),
("065", 9, 48, "MT3"),
("065",10, 30, "MT3"),
("065", 11, 41, "MT3"),
("065", 14, 42, "MT3"),
("065", 15, 42, "MT3"),
("065", 16, 39, "MT3"),
("065", 17, 39, "MT3"),
("065", 19, 43, "MT3"),
("065", 20, 40, "MT3");

-- BST
INSERT INTO exam
VALUES
("054", 2, 42 , "MT3"),
("054", 4, 44, "MT3"),
("054", 7, 40, "MT3"),
("054", 8, 36, "MT3"),
("054", 9 , 42, "MT3"),
("054", 11, 45, "MT3"),
("054", 12, 45, "MT3"),
("054", 13, 40, "MT3"),
("054", 15 , 46, "MT3"),
("054", 18 , 42, "MT3");

-- Eng:
INSERT INTO exam
VALUES
("301", 1, 45, "MT3"),
("301", 2, 43, "MT3"),
("301", 3, 46, "MT3"),
("301", 4, 40, "MT3"),
("301", 5, 45, "MT3"),
("301", 6, 45, "MT3"),
("301", 7, 44, "MT3"),
("301", 8, 47, "MT3"),
("301", 9, 42, "MT3"),
("301", 10, 30, "MT3"),
("301", 11, 47, "MT3"),
("301", 12, 42, "MT3"),
("301", 13, 42, "MT3"),
("301", 14, 38, "MT3"),
("301", 15, 45, "MT3"),
("301", 16, 44, "MT3"),
("301", 17, 42, "MT3"),
("301", 18, 45, "MT3"),
("301", 19, 45, "MT3"),
("301", 20, 44, "MT3");

-- Bio:
INSERT INTO exam
VALUES
("044", 3, 40, "MT3"),
("044", 5, 42,"MT3"),
("044", 16, 40,"MT3");

-- Accountancy
INSERT INTO exam
VALUES
("055", 2, 41, "MT3"),
("055", 4, 45, "MT3"),
("055", 7, 45, "MT3"),
("055", 8, 43, "MT3"),
("055", 9, 47, "MT3"),
("055", 11, 42, "MT3"),
("055", 12, 44, "MT3"),
("055", 13, 36, "MT3"),
("055", 15, 45, "MT3"),
("055", 18, 41, "MT3");

-- Physics:
INSERT INTO exam
VALUES
("042", 1, 40, "MT3"),
("042", 3, 40, "MT3"),
("042", 5, 39, "MT3"),
("042", 6, 40, "MT3"),
("042", 10, 27, "MT3"),
("042", 14, 37, "MT3"),
("042", 16, 40, "MT3"),
("042", 17, 37, "MT3"),
("042", 19, 41, "MT3"),
("042", 20, 43, "MT3");
INSERT INTO student
VALUES
(1,"Abhinash","MPC"),
(2,"Aditi","MEC"),
(3,"Alekhya","BiPC"),
(4,"Amit","CEIP"),
(5,"Anuhitha","BiPC"),
(6,"Hari","MPC"),
(7,"Jay","MEC"),
(8,"Kiran","CEIP"),
(9,"Madhav","CEIP"),
(10,"Manohar","MPC"),
(11,"Manoj","CEIP"),
(12,"Neha","MEC"),
(13,"Pawan","MEC"),
(14,"Ravi","MPC"),
(15,"Ritwik","CEIP"),
(16,"Samraddhi","BiPC"),
(17,"Smita","MPC"),
(18,"Swathi","MEC"),
(19,"Veena","MPC"),
(20,"Yogesh","MPC");
--
-- Missing subject
--

SELECT
  e.roll_no,  e.sub_code, e.marks, e.test,
  COALESCE(sb.name, '----') AS s_name, COALESCE(sb.sub_code, '----') AS s_code
FROM
  exam e
LEFT JOIN
  subject sb ON e.sub_code = sb.sub_code
WHERE sb.name IS NULL;


roll_no
sub_code
marks
test
s_name
s_code


3
044
29
MT1
----
----

5
044
39
MT1
----
----

16
044
43
MT1
----
----

3
044
33
MT2
----
----

5
044
40
MT2
----
----

16
044
38
MT2
----
----

3
044
40
MT3
----
----

5
044
42
MT3
----
----

16
044
40
MT3
----
----
roll_no	sub_code	marks	test	s_name	s_code
3	044	29	MT1	----	----
5	044	39	MT1	----	----
16	044	43	MT1	----	----
3	044	33	MT2	----	----
5	044	40	MT2	----	----
16	044	38	MT2	----	----
3	044	40	MT3	----	----
5	044	42	MT3	----	----
16	044	40	MT3	----	----