| 
View
 

Lecture - Complex Queries with Registration System

Page history last edited by Dr. Ron Eaglin 11 years, 11 months ago
 

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.