Given the following relational database schema: Student = (SSN, Name, Major) Course = ( CourseNumber, PrerequisiteCourseNumber, Course Title, NumberUnits) Section = ( CourseNumber, Quarter, RoomNumber, DayTime), where DayTime is of the form MW 1:00-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: (a) List the name, SSN and number of courses the student has taken and completed, i.e. received a letter grade (courses with the same CourseNumber taken in different quarters are counted as different courses). (b) List the name, SSN and number of courses the student has taken and completed with a letter grade C or better (courses with the same CourseNumber taken in different quarters are counted as different courses). (c) List the Course Title and CourseNumber which does not have any prerequisite. (d) List the name of every student and SSN who earned an A in all courses he or she has completed, i.e. every grade is either Null or an A)
Given the following relational
Student = (SSN, Name, Major)
Course = ( CourseNumber, PrerequisiteCourseNumber, Course Title, NumberUnits)
Section = ( CourseNumber, Quarter, RoomNumber, DayTime), where DayTime is of the form MW 1:00-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:
(a) List the name, SSN and number of courses the student has taken and completed, i.e. received a letter grade (courses with the same CourseNumber taken in different quarters are counted as different courses).
(b) List the name, SSN and number of courses the student has taken and completed with a letter grade C or better (courses with the same CourseNumber taken in different quarters are counted as different courses).
(c) List the Course Title and CourseNumber which does not have any prerequisite.
(d) List the name of every student and SSN who earned an A in all courses he or she has completed, i.e. every grade is either Null or an A)
Trending now
This is a popular solution!
Step by step
Solved in 3 steps with 4 images
