Question

Query 1 (2 pts)

Find all courses taught by Mozart. For each such course, output the title, semester, and year.

Query 2 (2 pts)

Find the names of all students who took both “Intro. to Computer Science” and “Game Design”.

Query 3 (2 pts)

Find the names of all instructors in the computer science department who never taught “Intro. to Computer Science”.

Query 4 (2 pts)

For each student, find the number of courses taken by this student. Output a table consisting of two columns: name of student, number of courses.

Query 5 (2 pts)

For each semester, find the number of students who took at least one section in this semester. Output a table consisting of three columns: semester, year, number of students. Order rows in the resulting table by the number of students in descending order.

Query 6 (2 pts)

Find all instructors who taught at least two sections. Output a table consisting of two columns: name of instructor, number of sections. Order rows in the resulting table by the number of sections in descending order.

Answer 1

Query 1

Select title , semester , year  from course inner join teaches on course.course_id = teaches.course_id inner join instructor on teaches.ID = instructor.ID where instructor.name = 'Mozart';

Query 2

Select student.name from student inner join takes on student.ID = takes.ID inner join course on takes.course_id = course.course_id where title = 'Intro. to Computer Science' and title = 'Game Design';

Query 3

Select instructor.name from instructor inner join teaches on instructor.ID = teaches .ID inner join course on course.course_id = teaches.course_id where instructor.dept_name = 'computer science' and course.title != 'Intro. to Computer Science';

Query 4

Select student.name, count(course.course_id) from student inner join takes on student.ID = takes.ID inner join course on takes.course_id = course.course_id group by student.name;

Query 5

Select semester,year, count(student.ID) as NumberOfStudents from takes inner join student on takes.ID = student.ID group by semester,year having(sec_id) >= 1 order by NumberOfStudents desc;

Query 6

Select instructor.name , count(section.sec_id) as NumberOfSections from instructor inner join teaches on instructor.ID = teaches.ID inner join section on teaches.sec_id = section.sec_id group by instructor.name having count(section.sec_id) >= 2 order by NumberOfSections desc;

Do ask if any doubt. Please upvote.