CREATE TABLE Students ( student_id int, ); student_name varchar (50) NOT NULL, gender char (1) NULL, age float NULL, ); EMail varchar(40) NULL, EntryDate date NULL CREATE TABLE Professors ( prof_id int, prof_name varchar (50) NOT NULL dept varchar(5) NULL, officephone varchar(16) NULL EMail varchar(40) NULL webAddress varchar(40) NULL I I I

The  SQL code is as following 

-- select columns p.prof_name, s.student_name with distinct keyword to get unique values

select distinct p.prof_name, s.student_name

from Supervise as sp -- from Supervise table with an alias name sp

inner join Professors as p -- inner join Professors to Supervise with an alias name p

on sp.prof_id = p.prof_id -- on condition to join both the tables

inner join Students as s -- inner join Students to Professors with an alias name s

on sp.student_id = s.student_id -- on condition to join both the tables

union -- union operator to combine left and right queries

select p.prof_name, concat('Total ', COUNT(distinct sp.student_id)) -- select columns p.prof_name, concat('Total ', COUNT(distinct sp.student_id))

from Supervise as sp -- from Supervise table with an alias name sp

inner join Professors as p -- inner join Professors to Supervise with an alias name p

on sp.prof_id = p.prof_id -- on condition to join both the tables

group by p.prof_id, p.prof_name -- group by to aggregate per professor

order by prof_name; -- order by to sort the results by professor name