| 
  • If you are citizen of an European Union member nation, you may not use this service unless you are at least 16 years old.

  • You already know Dokkio is an AI-powered assistant to organize & manage your digital files & messages. Very soon, Dokkio will support Outlook as well as One Drive. Check it out today!

View
 

Lecture - Complex Queries with Registration System

Page history last edited by Dr. Ron Eaglin 11 years, 3 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.