Creating Complex Views for Reporting

Pre-Requisites

 Spring 2008 Assignment 2 Review 

SQL View Creation Code

This code goes with the Spring 2008 Assignment series at  Assignment Assistance

Drop View StudentView
GO

CREATE VIEW StudentView AS
SELECT s.StudentID as 'Student ID',
      dbo.PersonName.PersonSurNameText as 'First Name', 
      dbo.PersonName.PersonGivenNameText as 'Last Name',
      dbo.ContactInformation.EmailIDText as 'E-Mail', 
      dbo.ContactInformation.MobileTelephoneNumberText as 'Cell Phone', 
      dbo.ContactInformation.TelephoneNumberText as 'Home Phone',   
       
      (SELECT  dbo.Address.AddressText 
           FROM dbo.Address, dbo.ContactInformation, dbo.Student
           WHERE dbo.Student.StudentID = s.StudentID
             AND dbo.ContactInformation.PersonContactInformationID = dbo.Student.StudentContactInformationID
             AND dbo.ContactInformation.PersonHomeAddressID = dbo.Address.AddressID) as 'Home Address',
     
      (SELECT  dbo.Address.AddressText 
           FROM dbo.Address, dbo.ContactInformation, dbo.Student
           WHERE dbo.Student.StudentID = s.StudentID
             AND dbo.ContactInformation.PersonContactInformationID = dbo.Student.StudentContactInformationID
             AND dbo.ContactInformation.PersonCurrentAddressID = dbo.Address.AddressID) as 'Current Address'

 FROM dbo.Student s
      
      INNER JOIN dbo.PersonName
      ON s.StudentNameID = dbo.PersonName.NameID

      INNER JOIN dbo.ContactInformation
      ON s.StudentContactInformationID = dbo.ContactInformation.PersonContactInformationID

GO

SELECT * FROM StudentView