1. Specify the following queries in SQL on the database schema of Figure 1.2.
   1. Retrieve the name of each course and the instructor who taught that course. If an instructor taught a course more than one time, remove the redundant information.

SELECT Name

FROM STUDENT 

WHERE Major='COSC'

1. For each section of those courses offered by CS department, retrieve the course number, semester, year, and number of students who took the section.

            SELECT course_name

            FROM COURSE, SECTION

WHERE COURSE.course_number=SECTION.course_number AND Instructor='King' AND (Year='85' OR Year='86')

Another possible SQL query uses nesting as follows:

            SELECT course_name

            FROM COURSE

            WHERE course_number IN (SELECT Course Number FROM SECTION

      WHERE Instructor='King' AND (Year='85' OR Year='86'))

1. For each student who took more than 2 sections, retrieve the name, student number, major of the student and the number of sections taken by the student.

SELECT course_number, Semester, Year, COUNT (*)

FROM SECTION, GRADE_REPORT

WHERE Instructor='King' AND SECTION.SectionIdentifier=GRADE_REPORT.SectionIdentifier

GROUP BY course_number, Semester, Year

STUDENT

COURSE

SECTION

GRADE_REPORT

PREREQUISITE

Name Student_number Class Major Smith 17 1 CS Brown 8 2 CS