Database Reference
In-Depth Information
Figure 8.5
STUDENT_ADVISOR_VIEW.
TASK 2: ALTERING VIEW
You can modify a view by using the ALTER VIEW statement in the New Query editor.
For example, you can add a column called GPA. he following is the modiication to
STUDENT_ADVISOR_VIEW:
1. Assume that the Windows Azure SQL Database portal is still open. Enter the follow-
ing SQL statement to alter the view STUDENT_ADVISOR_VIEW:
ALTER VIEW STUDENT_ADVISOR_VIEW
AS
SELECT S.FirstName, S.LastName, F.LastName As [Advisor],
AVG(CASE T.Grade
WHEN 'A' THEN 4.0
WHEN 'B' THEN 3.0
WHEN 'C' THEN 2.0
WHEN 'D' THEN 1.0
WHEN 'F' THEN 0.0 END) [GPA]
FROM ((STUDENT S JOIN FACULTY F ON S.FacultyID
=
F.FacultyId)
JOIN STUDENT_CLASS T ON T.StudentID
=
S.StudentID)
GROUP BY F.LastName, S.LastName, S.FirstName
2. Highlight the code and click
Run
. As shown in Figure 8.6, the view STUDENT_
ADVISOR_VIEW is successfully modiied.
