MySQL 8.0

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

-- Table structure for course
DROP TABLE IF EXISTS `course`;
CREATE TABLE `course`  (
  `courseNo` int NOT NULL,
  `courseTitle` varchar(20) NOT NULL,
  PRIMARY KEY (`courseNo`)
);

-- Records of course
INSERT INTO `course` VALUES (1000, 'Software Engineering');
INSERT INTO `course` VALUES (1001, 'Internet Systems');

-- Table structure for student
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student`  (
  `studentid` int NOT NULL,
  `courseNo` int NOT NULL,
  PRIMARY KEY (`studentid`)
);

-- Records of student
INSERT INTO `student` VALUES (3000, 1000);
INSERT INTO `student` VALUES (3001, 1001);
INSERT INTO `student` VALUES (3002, 1000);
INSERT INTO `student` VALUES (3003, 1000);
INSERT INTO `student` VALUES (3004, 1001);
INSERT INTO `student` VALUES (3005, 1000);
INSERT INTO `student` VALUES (3006, 1001);
INSERT INTO `student` VALUES (3007, 1001);
INSERT INTO `student` VALUES (3008, 1001);
INSERT INTO `student` VALUES (3009, 1000);
INSERT INTO `student` VALUES (3010, 1001);
INSERT INTO `student` VALUES (3011, 1001);
SELECT c.courseNo AS 'Course ID',
       c.courseTitle AS 'Course Title',
       COUNT(s.studentID) AS 'Enrolled'
FROM course c INNER JOIN 
     student s
ON  s.courseNo = c.courseNo 
GROUP BY c.courseNo;


Course ID
Course Title
Enrolled


1000
Software Engineering
5

1001
Internet Systems
7


EXPLAIN SELECT c.courseNo AS 'Course ID',
       c.courseTitle AS 'Course Title',
       COUNT(s.studentID) AS 'Enrolled'
FROM course c INNER JOIN 
     student s
ON  s.courseNo = c.courseNo 
GROUP BY c.courseNo;


id
select_type
table
partitions
type
possible_keys
key
key_len
ref
rows
filtered
Extra


1
SIMPLE
c
null
ALL
PRIMARY
null
null
null
2
100.00
Using temporary

1
SIMPLE
s
null
ALL
null
null
null
null
12
10.00
Using where; Using join buffer (hash join)


EXPLAIN
SELECT course.courseNo AS 'Course ID',
       course.courseTitle AS 'Course Title',
       COUNT(student.studentID) AS 'Enrolled'
FROM course  INNER JOIN 
     student 
ON  student.courseNo = course.courseNo 
GROUP BY course.courseNo;


id
select_type
table
partitions
type
possible_keys
key
key_len
ref
rows
filtered
Extra


1
SIMPLE
course
null
ALL
PRIMARY
null
null
null
2
100.00
Using temporary

1
SIMPLE
student
null
ALL
null
null
null
null
12
10.00
Using where; Using join buffer (hash join)


Select course.courseNo as 'Course ID',
       course.courseTitle as 'Course Title',
       COUNT(student.studentID) AS 'Enrolled'
From course,
     student
WHERE EXISTS
        ( SELECT *
         FROM student s
         WHERE courseNo = course.courseNo )
Group by course.courseNo;


Course ID
Course Title
Enrolled


1001
Internet Systems
12

1000
Software Engineering
12

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	c	null	ALL	PRIMARY	null	null	null	2	100.00	Using temporary
1	SIMPLE	s	null	ALL	null	null	null	null	12	10.00	Using where; Using join buffer (hash join)

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	course	null	ALL	PRIMARY	null	null	null	2	100.00	Using temporary
1	SIMPLE	student	null	ALL	null	null	null	null	12	10.00	Using where; Using join buffer (hash join)