Database Questions 6. Find the names and ids of the students who have taken exac

Question

Database Questions

6. Find the names and ids of the students who have taken exactly one course in the Spring 2010 semester.

7. 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

8. 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.

9. 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).

10. Use an outer join to find names of instructors who did not teach any course in the Spring 2010 semester.

11. [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).

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
   );

Explanation / Answer

6. Find the names and ids of the students who have taken exactly one course in the Spring 2010 semester.

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
   );

with temp1(id,course_id) as (select ID,course_is from takes where semester = "Spring" and year = 2010)

with temp2(id) as (select id from temp as t1,temp as t2 where t1.id = t2.id and t1.course_id <> t2.course.id)

with temp3(id) as (select id from temp1 Except select id from temp2)

select name,id from student,temp3 where student.ID = temp3.id

temp1 = all students who have taken some course in spring 2010

temp2 = students who have taken more than one course in spring 2010

temp3 = students who have taken exactly one course in spring 2010.

7. 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

with temp1(id,course_id) as (select ID,course_is from takes where semester = "Spring" and year = 2010)

with temp2(id) as (select id from temp as t1,temp as t2 where t1.id = t2.id and t1.course_id <> t2.course.id)

with temp3(id) as (select ID from student Except select id from temp2)

select name,id from student,temp3 where student.ID = temp3.id

temp1 = all students who have taken some course in spring 2010

temp2 = students who have taken more than one course in spring 2010

temp3 = students who have taken zero or one course in spring 2010.

8. 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.

select id,count(course_id) as number_courses from

select ID,course_id from takes where semester = "Spring" and year = 2010 as temp(id,course_id)

group by id having count(course_id) >= 2

9. 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).

with temp1(id,num_courses) as

select id,count(course_id) as num_courses from

select ID,course_id from teaches as temp(id,course_id)

group by id

select name,ID,num_courses from instructor,temp1 where instructor.ID = temp1.id

Please let me know in case of any doubt.

Thanks

Related Questions

Navigate

• Browse (All)
• Browse D
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.