View Resolution

View Resolution
Having considered how to create and use views, we now look more closely at how a query
on a view is handled. To illustrate the process of view resolution, consider the following
query that counts the number of properties managed by each member of staff at branch
office B003. This query is based on the StaffPropCnt view of Example 6.5:
SELECT staffNo, cnt
FROM StaffPropCnt
WHERE branchNo = ‘B003’
ORDER BY staffNo;
View resolution merges the above query with the defining query of the StaffPropCnt view
as follows:
(1) The view column names in the SELECT list are translated into their corresponding
column names in the defining query. This gives:
SELECT s.staffNo AS staffNo, COUNT(*) AS cnt
(2) View names in the FROM clause are replaced with the corresponding FROM lists of
the defining query:
FROM Staff s, PropertyForRent p
(3) The WHERE clause from the user query is combined with the WHERE clause of the
defining query using the logical operator AND, thus:
WHERE s.staffNo = p.staffNo AND branchNo = ‘B003’
(4) The GROUP BY and HAVING clauses are copied from the defining query. In this
example, we have only a GROUP BY clause:
GROUP BY s.branchNo, s.staffNo
(5) Finally, the ORDER BY clause is copied from the user query with the view column
name translated into the defining query column name:
ORDER BY s.staffNo
(6) The final merged query becomes:
SELECT s.staffNo AS staffNo, COUNT(*) AS cnt
FROM Staff s, PropertyForRent p
WHERE s.staffNo = p.staffNo AND branchNo = ‘B003’
GROUP BY s.branchNo, s.staffNo
ORDER BY s.staffNo;