Database: (Please make short answers) 6. Find the names and ids of the students

Question

Database: (Please make short answers)

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

Query for question 6 :

select Name, ID from student where ID in (select ID from takes where (semester = 'spring' and year=2010) group by id having count(id) = 1);

Query for question 7:

select Name, ID from student where ID in (select ID from takes where (semester = 'spring' and year=2010) group by id having count(id) <= 1);

Query for question 8:

select id, subquery1.number_course as number_course from student,
(select ID, sum(course_id) as number_course from takes group by id having sum(course_id) = 2) subquery1
where (semester = 'spring' and YEAR = 2010);

Query for question 10:

select instructor.name from instructor
left outer join
on takes
instructor.id = takes.id;

Related Questions

Navigate

• Browse (All)
• Browse D
• Subjects

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