Question

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 every two CourseNumber and their titles which have the same prerequisites.

2. List the name of every student and SSN who has completed all the courses he/she has taken and earned an A in each course, i.e. every grade is an A and no NULL value

3. List the name of every student and SSN who earned no A in any course.

4. List the name and major of every student who has only taken courses that meet MW afternoon (12 or after).

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

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

7. List every CoursaeNumber and Quarter which has the highest enrollment.

8. List every CourseNumber and CourseTitle which has the highest enrollment based on all quarters.

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

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

Answer 1

1) select a.CourseTitle , b.CourseTitle ,c.CourseNumber from Course as a,Course as b,Course as c where a.PrerequisiteCourseNumber=b.PrerequisiteCourseNumber ;

2) select a.name,a.SSN from Student a Inner Join Enrollment b on a.SSN=b.SSN where grade='A';

3)select a.name,a.SSN from Student a Inner Join Enrollment b on a.SSN=b.SSN where grade NOT LIKE 'A';

4) select name,major from student UNION section where daytime='MW';