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

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

start_min numeric(2) check (start_min >= 0 and start_min

end_hr numeric(2) check (end_hr >= 0 and end_hr

end_min numeric(2) check (end_min >= 0 and end_min

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

);

-1. What are the names of students who have taken at least one CS class? Give student names onLy, order alphabetically, don 't show duplicates, and limit to 10; -2. For each department, find the maximum salary of instructors in that department. Each line of the output should be a department name and a number. 3. For each instructor ID, show the number of courses taught in 2010. -Show every instructor, even if no classes taught in 2010. Each line of output should give an instructor ID and a count -Give the output in order of decreasing number of courses taught in 2010, and limit to 10 instructors. -4. Which student (by ID) has received the most grades of "A" or "A-"? Give a single output line with the student's ID, name, and the count. 5. Which courses have not been taught at any time? -For each such course give the course ID and title. List in order of course title Lim it output to 10 courses -1. What are the names of students who have taken at least one CS class? Give student names onLy, order alphabetically, don 't show duplicates, and limit to 10; -2. For each department, find the maximum salary of instructors in that department. Each line of the output should be a department name and a number. 3. For each instructor ID, show the number of courses taught in 2010. -Show every instructor, even if no classes taught in 2010. Each line of output should give an instructor ID and a count -Give the output in order of decreasing number of courses taught in 2010, and limit to 10 instructors. -4. Which student (by ID) has received the most grades of "A" or "A-"? Give a single output line with the student's ID, name, and the count. 5. Which courses have not been taught at any time? -For each such course give the course ID and title. List in order of course title Lim it output to 10 courses