Solve the following question using Oracle.

Write a query that uses a scalar query in the select clause to find the number of distinct courses that have been taught by each instructor. Schema of the output should be (name, id, number_courses).

The data:

create table classroom

(building varchar(15),

room_number varchar(7),

capacity numeric(4,0),

primary key (building, room_number)

);

create table department

(dept_name varchar(20),

building varchar(15),

budget numeric(12,2) check (budget > 0),

primary key (dept_name)

);

create table course

(course_id varchar(8),

title varchar(50),

dept_name varchar(20),

credits numeric(2,0) check (credits > 0),

primary key (course_id),

foreign key (dept_name) references department

on delete set null

);

create table instructor

(ID varchar(5),

name varchar(20) not null,

dept_name varchar(20),

salary numeric(8,2) check (salary > 29000),

primary key (ID),

foreign key (dept_name) references department

on delete set null

);

create table section

(course_id varchar(8),

sec_id varchar(8),

semester varchar(6)

check (semester in ('Fall', 'Winter', 'Spring', 'Summer')),

year numeric(4,0) check (year > 1701 and year < 2100),

building varchar(15),

room_number varchar(7),

time_slot_id varchar(4),

primary key (course_id, sec_id, semester, year),

foreign key (course_id) references course

on delete cascade,

foreign key (building, room_number) references classroom

on delete set null

);

create table teaches

(ID varchar(5),

course_id varchar(8),

sec_id varchar(8),

semester varchar(6),

year numeric(4,0),

primary key (ID, course_id, sec_id, semester, year),

foreign key (course_id,sec_id, semester, year) references section

on delete cascade,

foreign key (ID) references instructor

on delete cascade

);

create table student

(ID varchar(5),

name varchar(20) not null,

dept_name varchar(20),

tot_cred numeric(3,0) check (tot_cred >= 0),

primary key (ID),

foreign key (dept_name) references department

on delete set null

);

create table takes

(ID varchar(5),

course_id varchar(8),

sec_id varchar(8),

semester varchar(6),

year numeric(4,0),

grade varchar(2),

primary key (ID, course_id, sec_id, semester, year),

foreign key (course_id,sec_id, semester, year) references section

on delete cascade,

foreign key (ID) references student

on delete cascade

);

create table advisor

(s_ID varchar(5),

i_ID varchar(5),

primary key (s_ID),

foreign key (i_ID) references instructor (ID)

on delete set null,

foreign key (s_ID) references student (ID)

on delete cascade

);

create table time_slot

(time_slot_id varchar(4),

day varchar(1),

start_hr numeric(2) check (start_hr >= 0 and start_hr < 24),

start_min numeric(2) check (start_min >= 0 and start_min < 60),

end_hr numeric(2) check (end_hr >= 0 and end_hr < 24),

end_min numeric(2) check (end_min >= 0 and end_min < 60),

primary key (time_slot_id, day, start_hr, start_min)

);

create table prereq

(course_id varchar(8),

prereq_id varchar(8),

primary key (course_id, prereq_id),

foreign key (course_id) references course

on delete cascade,

foreign key (prereq_id) references course

);