Views Exercises

Example syntax for creating a view of the Northwinds database:

CREATE VIEW vwCustomerAddressList AS
SELECT CompanyName, ContactName, Address, City, Region, PostalCode, Country
FROM Customers;

The view defined above eliminates some of the columns from the original Customers table. The database administrator could then prohibit some users from accessing the Customers table by providing access to this view instead. This is an example of column-level security.

1. Now, design a new view that implements row-level security and joins two tables. Build the view that shows the above customer information (in the example syntax) for all orders associated with employeeID of "1." The security is enforced by using views to only return the rows that you want the current user to see.
   

   CREATE VIEW vwJoinTables AS
   SELECT Customers.CompanyName, Customers.ContactName, Customers.Address,
   Customers.City, Customers.Region, Customers.PostalCode, Customers.Country
   FROM Customers INNER JOIN Orders
   ON Customers.CustomerID = Orders.CustomerID WHERE employeeID = 1;

2. Next, design a view that displays the above customer information for all orders associated with the employee by the last name of "Davolio."
   

   CREATE VIEW vwCustomerAddressList2 AS
   SELECT Customers.CompanyName, Customers.ContactName, Customers.Address,
   Customers.City, Customers.Region, Customers.PostalCode, Customers.Country
   FROM Customers,Orders,Employees
   WHERE Customers.CustomerID = Orders.CustomerID
   AND Orders.EmployeeID = Employees.EmployeeID
   AND Employees.LastName = 'Davolio';

3. How would you make it so that a combination of a stored procedure and a view could be used to display the above information for any employee ID passed as a parameter to the stored procedure? Implement your design.
   

   CREATE PROCEDURE [dbo].[ViewsQuestion3](@employeeID int) AS
   SELECT *
   FROM vwCustomerAddressList,Employees
   WHERE Employees.EmployeeID = @employeeID
   GO
   
   AND
   
   CREATE VIEW vwCustomerAddressList AS
   SELECT Customers.CompanyName, Customers.ContactName, Customers.Address,
   Customers.City, Customers.Region, Customers.PostalCode, Customers.Country
   FROM Customers,Orders,Employees
   WHERE Customers.CustomerID = Orders.CustomerID
   AND Orders.EmployeeID = Employees.EmployeeID
   AND Employees.EmployeeID = 1;

4. Finally, another purpose of views is to summarize data for the user, without the user having to know how to do the summarization herself. Create an aggregate view that summarizes the total dollar amount ordered by each customer. List the company name and the total dollar amount ordered by each customer. Name the first column "Customer Name" and the second column "Total Customer Orders."
   

   CREATE VIEW vwViewsHomeWorkQuestion4 AS
   SELECT Customers.CompanyName, SUM(Quantity * UnitPrice) as TotalCustomerOrders
   FROM Customers,Orders,[Order Details]
   WHERE Customers.CustomerID = Orders.CustomerID
   AND Orders.OrderID = [Order Details].OrderID
   GROUP BY CompanyName;