Complex Queries with Registration System
Summary of Video
Shows how to create complex queries with the registration system case study. This creates a complex query to determine if a student has taken all pre-requisite courses for a course.
Prerequisites
Case Study - Registration Database
Video Link
http://online1.daytonastate.edu/player2.php?id=7b41bfa5085806dfa24b8c9de0ce567f
Support Materials
Table Structure
|
Courses (id, course information)
Students (id, student information)
Semester (id Semester information)
CourseOffering (id, CourseID, SemesterID)
StudentEnrollment (id, StudentID, CourseOfferingID)
Prerequisites (id, CourseID, PCourseID)
|
|
Select a Prequisite with a known courseID
|
SELECT PCourseID FROM Prerequisites WHERE CourseID = @CourseID
|
|
Select Courses a student has taken given
@StudentID
returns CourseID
|
SELECT CourseID FROM CourseOffering WHERE id IN
(SELECT CourseOfferingID FROM StudentEnrollment WHERE StudentID = @StudentID)
|
|
Select Prerequisites of all courses a student has taken. Just
finds the pre-requisites and returns the CourseID of the courses.
|
SELECT PCourseID FROM Prerequisites WHERE CourseID IN
(SELECT CourseID FROM CourseOffering WHERE id IN
(SELECT CourseOfferingID FROM StudentEnrollment WHERE StudentID = @StudentID))
|
|
Finds the list of all pre-requisites for a course that a student has not taken.
|
SELECT PCourseID FROM Prerequisites WHERE CourseID = @CourseID
AND
PCourseID NOT IN
(SELECT CourseID FROM CourseOffering WHERE id IN
(SELECT CourseOfferingID FROM StudentEnrollment WHERE StudentID = @StudentID))
|
| |
|
| |
|
All Materials Copyright 2012 Dr. Ron Eaglin
Comments (0)
You don't have permission to comment on this page.