Question

Please do these carefully.

Student = (SSN, Name, Major)

Course = ( CourseNumber, PrerequisiteCourseNumber, Course Title, NumberUnits)

Section = ( CourseNumber, Quarter, RoomNumber, DayTime), where DayTime is of the form MW 1:0-2:00PM.

Enrollment = (SSN,CourseNumber, Quarter, Grade)// Grade is either Null or a letter grade.

Express the following queries using appropriate SQL statements with a minimum number of operations:

1. List the name of every student and SSN who has not taken any course more than once.

2. List every two CourseNumber and their titles which have the same prerequisites.

3. List the name and major of every student who has taken all the courses that meet MW afternoon.

4. List the name and major of every student who has not taken any course that meets MW afternoon.

5. List every CourseNumber and Quarter which has the highest enrollment.

6. List the name and major of every student who has completed the highest number of units.

7. List every Course tile which is a prerequisite for the largest number of courses.

Answer 1

1.

Select Name, SSN from Student where SSN in (Select SSN.Enrollment from Enrollment JOIN Course where Enrollment.CourseNumber = Section.CourseNumber);

2.

Select A.CourseNumber as Course1, A.CourseTitle as Course1Title, B,CourseNumber as Course2, B.CourseTitle as Course2Title from Course A, Course B where A.CourseNumber <> B.CourseNumber AND A.PrerequisiteCourseNumber = B.PrerequisiteCourseNumber;