Solve the following questions using Oracle. You are not allowed to use the syntax of any DBMS other than Oracle. Make sure to upload an electronic copy of your solution to your CSC335 TRACE folder. Name the file hw4.sql. Also, make sure to turn in a stapled hard copy in class on the due date. Find the ids of instructors who are also students using a set operation. Assume that a person is identified by her or his id. So, if the same id appears in both instructor and student, then that person is both an instructor and a student. Remember: set operation means union, intersect or set difference. Find the ids of instructors who are also students using the set membership operator. Find the ids of instructors who are also students using a set comparison operator. Find the ids of instructors who are also students using the exists construct. Find the names and ids of the students who have taken all the courses that are offered by their departments. Notice, the table course contains information about courses offered by departments. Find the names and ids of the students who have taken exactly one course in the Spring 2010 semester. Find the names and ids of the students who have taken at most one course in the Spring 2010 semester. Notice, at most one means one or zero. So, the answer should include students who did not take any course during that semester Write a query that uses a derived relation to find the student(s) who have taken at least two courses in the Spring 2010 semester. Schema of the output should be (id, number_courses). Remember: derived relation means a subquery in the from clause. 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). Use an outer join to find names of the instructors who never taught any course. [Extra credit: 10 points] Write a query that uses the with clause or a derived relation to find the id and number of courses that have been taken by student(s) who have taken the most number of courses. Schema of the output should be (id, number_courses). reate 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 );