common student service queries

CourseSectionQuery.sql

SELECT     c.TermCode
  , c.Crn
	, c.Subject
	, c.CourseNumb
	, c.Sequence
	, c.Name
	, s.SectionType
	, s.StartDate
	, s.EndDate
	, s.StartTime
	, s.EndTime
	, s.DaysOfWeek
FROM         
	vCourses c 
	INNER JOIN Sections s
        ON c.TermCode = s.TermCode 
            AND c.Crn = s.Crn
WHERE 
	c.TermCode = '201303'
	AND c.DepartmentId = 'MAT'

CoursesSubjectQuery.sql

SELECT     c.TermCode
  , c.Crn
	, c.Subject
	, c.CourseNumb
	, c.Sequence
	, c.Name
	, s.SectionType
	, s.StartDate
	, s.EndDate
	, s.StartTime
	, s.EndTime
	, s.DaysOfWeek
FROM         
	vCourses c 
	INNER JOIN Sections s
        ON c.TermCode = s.TermCode 
            AND c.Crn = s.Crn
WHERE 
	c.TermCode = @Term
	AND c.Subject = @Subject
	AND c.CourseNumb in ({0})

RostersSubjectQuery.sql

select s.LoginId
,s.FirstName
,s.LastName
,s.Email
,r.Crn
from CourseRoster r
  inner join vCourses c on (r.Crn = c.Crn AND r.TermCode = c.TermCode)
	inner join students s on r.LoginId = s.LoginId
where 
	r.TermCode = @Term	
	AND c.Subject = @Subject
	AND c.CourseNumb in ({0})

select i.LoginId
,i.FirstName
,i.LastName
,i.Email
,c.Crn
from CourseInstructors ci
	inner join vCourses c on (ci.Crn = c.Crn AND ci.TermCode = c.TermCode)
	inner join Instructors i on ci.LoginId = i.LoginId
where
	ci.TermCode = @Term
	AND c.Subject = @Subject
	AND c.CourseNumb in ({0})

--Regarding vCourses:

--SQL:

SELECT TermCode, Crn, Subject, CourseNumb, Sequence, Name, ISNULL
((SELECT TOP (1) DepartmentId
FROM dbo.DepartmentOverrides AS do
WHERE (Subject = dbo.Courses.Subject) AND (CourseNumb = dbo.Courses.CourseNumb) OR
(Subject = dbo.Courses.Subject) AND (CourseNumb IS NULL)
ORDER BY CASE WHEN do.coursenumb IS NULL THEN 1 ELSE 0 END), DepartmentId) AS departmentid
FROM dbo.Courses

/*
The inner select performs the following:

Checks the Department Overides table for a match on the given subject.
If a match is found then
Checks for a match on both subject and course number
If a match is found on both then use the overides table's department ID with the matching course number
Otherwise use the overides table's department ID with the NULL course, meaning the overides table's department ID with only a matching subject
Otherwise there was no corresponding subject in the Departments Overides table so use the courses table's department ID
*/

--Regarding RostersSubjectQuery.sql

/*
Missing Index Details from SQLQuery36.sql - clamps.StudentService (AESDEAN\taylor (56))
The Query Processor estimates that implementing the following index could improve the query cost by 71.8635%.
*/

/*
USE [StudentService]
GO
CREATE NONCLUSTERED INDEX [Courses_TermCodeSubjectCourseNumb_IDX]
ON [dbo].[Courses] ([TermCode].[Subject].[CourseNumb])

GO
*/

-- Regarding CoursesSubjectQuery.sql

/*
Missing Index Details from SQLQuery31.sql - clamps.StudentService (AESDEAN\taylor (51))
The Query Processor estimates that implementing the following index could improve the query cost by 54.7953%.
*/

/*
USE [StudentService]
GO
CREATE NONCLUSTERED INDEX [Sections_TermCode_CVIDX])
ON [dbo].[Sections] ([TermCode])
INCLUDE ([Crn],[SectionType],[StartDate],[EndDate],[StartTime],[EndTime],[DaysOfWeek])
GO
*/