Chapter 7 WORKING WITH VIEWS 437 APPLY YOUR
Chapter 7 WORKING WITH VIEWS 437 APPLY YOUR KNOWLEDGE query: CREATE VIEW SelectedColumns AS SELECT Au_ID, State, au_FName FROM Authors 3. Step 2 creates a view that includes only the State and Au_fname columns. To create a view that includes only rows where the first name has the letter J as the first letter, enter the following code: CREATE VIEW SelectedRows AS SELECT * FROM Authors WHERE au_fname LIKE J% 4. Test your view by running a SELECT against the view. Your SELECT statement should return all columns and rows so that you can see how much data is now available through the view. If you have not already done so, you should also SELECT the entire Authors table, in order to see how much data is in the base table. Exercise 7.2 Renaming and Dropping Views This example demonstrates renaming and dropping views using sp_rename and DROP VIEW. Any job that is done can either be done better or becomes obsolete. This exercise shows you how to change an established view s name, as well as remove an obsolete view. Estimated Time: 10 minutes. 1. Open the SQL Server Query Analyzer by selecting it from the Start menu. Connect to your server, and change to the Pubs database. 2. Examine the contents of the Titles table by selecting the entire contents. 3. You first have to create a view. To do this, enter the following code: CREATE VIEW ExampleView AS SELECT * FROM titles WHERE type = business 4. Test your new view by selecting against it. 5. To change the previously created view s name to TestView, enter the following code: sp_rename ExampleView , TestView , OBJECT 6. Examine what views exist in the Pubs database using the following statement: SELECT name FROM sysobjects WHERE type= V 7. To delete TestView (formerly ExampleView), enter the following query: DROP VIEW TestView 8. Re-examine the views that exist in the Pubs database using the same command that you used in Step 6. Exercise 7.3 Creating a Partitioned View This exercise demonstrates creating a partitioned view from member tables. You start by creating two base tables, and then you join them through the creation of a view. Estimated Time: 15 minutes. 1. Open the SQL Server Query Analyzer by selecting it from the Start menu. Connect to your server, and change to the Pubs database. 2. To begin, you will need to create two tables that act as members the tables that will be joined together. To do this, follow this code: CREATE TABLE member1 ( Identification INT
For high quality website hosting services please check cheap web hosting website.