Creating a View

To create a view, you use the following syntax:

CREATE VIEW ViewName AS Query [WITH CHECK OPTION]

Here, Query can be any query you like, which could involve any of the techniques you've seen in previous chapters.

For example:

CREATE VIEW StudentSummary
AS
SELECT Student.StudentID, Student.Name,
   COUNT(*) AS ExamsTaken
FROM Student
INNER JOIN StudentExam
ON Student.StudentID = StudentExam.StudentID
GROUP BY Student.StudentID, Student.Name

Here you create a view called StudentSummary, which consists of a StudentID column, a Name column, and an ExamsTaken column, which is a count of how many exams that student has taken. After doing this, it's possible to use the following query:

SELECT StudentID, Name, ExamsTaken FROM StudentSummary

This will return the desired order count information without having to use the clumsy syntax required to calculate this information.

You can use views wherever you might otherwise use tables in SQL statements, so you can query them, perform joins with them, and so on.

Note

You'll look at the WITH CHECK OPTION clause in just a moment when you learn about modifying data using views.

CREATING A VIEW (SQL SERVER)

For SQL Server, you should execute the following statement against the InstantUniversity database:

CREATE VIEW ClassAttendees AS
SELECT Class.ClassID, SUBSTRING(Professor.Name,
   LEN(Professor.Name) - CHARINDEX(' ', REVERSE(Professor.Name)) + 2, 100)
   + ', '
   + LEFT(Professor.Name, LEN(Professor.Name)
   - CHARINDEX(' ', REVERSE(Professor.Name)))
   AS Name, 'Professor' AS Role
FROM Professor
   INNER JOIN Class ON Professor.ProfessorID =
      Class.ProfessorID
UNION
SELECT Enrollment.ClassID, SUBSTRING(Student.Name,
       LEN(Student.Name) - CHARINDEX(' ', REVERSE(Student.Name)) + 2, 100)
       + ', '
       + LEFT(Student.Name,
              LEN(Student.Name) - CHARINDEX(' ', REVERSE(Student.Name)))
       AS Name, 'Student'
FROM Student
   INNER JOIN Enrollment ON Student.StudentID =
      Enrollment.StudentID

Oracle and DB2 will use a slightly different syntax for this example, as you'll see in a moment.

Then, execute the following statement:

SELECT ClassID, Name, Role FROM ClassAttendees

You should see the following output (the first six rows are shown):

   ClassID Name           Role

   ------- -------------- ---------
   1       Dawson, Prof.  Professor
   1       Jones, John    Student
   1       Picard, Julia  Student
   1       Wolff, Anna    Student
   2       Dawson, Prof.  Professor
   2       Jones, John    Student
   ...     ...            ...

In this example, you've used a modified version of the complicated string manipulation query from Chapter 6, "Combining SQL Queries," to build a view called ClassAttendees. This view shows who takes part in each class in the university, both students and professors.

This is an ideal use of a view because you wouldn't want to use the whole lengthy query every time you wanted such information. You could refine the results by adding clauses into the SELECT statement to filter the results, for example:

SELECT ClassID, Name, Role
FROM ClassAttendees
WHERE ClassID = 1;

This statement would return only the attendees of the first class on Monday morning.

CREATING A VIEW (ORACLE)

The main difference between the SQL Server implementation of this example and the Oracle implementation is that the code that modifies the names of the students and professors will be constructed differently, as you can see in the following code equivalent:

CREATE VIEW ClassAttendees AS
SELECT Class.ClassID,
   SUBSTR(Professor.Name, INSTR(Professor.Name, ' ') + 1)
           || ', '
           || SUBSTR(Professor.Name, 1,
              INSTR(Professor.Name, ' ') - 1)
   AS Name, 'Professor' AS Role
FROM Professor
   INNER JOIN Class ON Professor.ProfessorID =
      Class.ProfessorID
UNION
SELECT Enrollment.ClassID,
   SUBSTR(Student.Name, INSTR(Student.Name, ' ') + 1)
           || ', '
           || SUBSTR(Student.Name, 1,
              INSTR(Student.Name, ' ') - 1)
   AS Name, 'Student'
FROM Student
   INNER JOIN Enrollment ON Student.StudentID = Enrollment.StudentID;

This code will produce the same results as the SQL Server equivalent.

Finally, let's quickly look at the change in syntax to implement the same view in DB2.

CREATING A VIEW (DB2)

Note that when using DB2, in addition to the string manipulation code being slightly different, each column must be explicitly named using the AS Name syntax:

CREATE VIEW ClassAttendees AS
SELECT Class.ClassID AS ClassID,
   SUBSTR(Professor.Name, POSSTR(Professor.Name, ' ') + 1)
          || ', '
          || SUBSTR(Professor.Name, 1,
                POSSTR(Professor.Name, ' ') - 1)
   AS Name, 'Professor' AS Role
FROM Professor
   INNER JOIN Class ON Professor.ProfessorID =
      Class.ProfessorID
UNION
SELECT Enrollment.ClassID AS ClassID,
   SUBSTR(Student.Name, POSSTR(Student.Name, ' ') + 1) || ', ' ||
   SUBSTR(Student.Name, 1, POSSTR(Student.Name, ' ') - 1)
   AS Name, 'Student' AS Role FROM Student INNER JOIN Enrollment
ON Student.StudentID = Enrollment.StudentID;

Again, this code will produce the same results as the implementations shown previously.