Web Hosting Java Blog

Chapter 7 WORKING WITH VIEWS 409 Before you can use the wizard, you have to open it. You will find all the wizards in Enterprise Manager in the Tools Menu. The Create View Wizard is in the Database section, as shown in Figure 7.9. Select it and choose OK. FIGURE 7.9 The Create View Wizard is accessible through the Wizards dialog. The Welcome screen for the wizard shows you what to expect during the wizard. This welcome screen is shown in Figure 7.10. The five steps to the Create View Wizard are 1. Select the database that will be used for the view. 2. Select one or more tables for use in the view. creation process. 3. Select one or more columns from the selected tables. 4. Type a WHERE clause to restrict the returned rows. 5. Name the new view. The wizard provides a way of specifying advanced options, such as ENCRYPTION or SCHEMABINDING, but does simplify the view NOTE Changing Object Names The sp_rename system procedure is used to rename any database object in your database, so it s one you should remember.

For high quality website hosting services please check java web hosting website.

408 Part I EXAM PREPARATION TIPEasily Creating Views Views can also be easily created using the Create View Wizard. This is useful to know when creating views for personal use, but the exam will not test you on this process. EXAM FROM AuthorsView WHERE State = CA To display the newly created view, query it using SELECT * FROM AuthorsCA You should notice that only the authors living in California are listed. If the authors in California represent a piece of information that is regularly required, you now have an easy way to extract that data from a view that was already useful. You should now have a clear understanding of how to enhance a view by using the summary power of aggregates, the data consolidation of joins, and the refined filtering of basing a view on another view. If you do not need advanced view features, you can make use of the Create View Wizard. Creating Views with the Wizard If you are in a hurry and just want a quick view with all the basic elements, you can use the Create View Wizard. This is a five-step process that takes only a few seconds to complete.

For high quality website hosting services please check cheap web hosting website.

Chapter 7 WORKING WITH VIEWS 407 SELECT * FROM AuthorSummaryInfo FIGURE 7.8 Incorporating joins in views. Joins enable views to act as data consolidation points. Next, you see how to re-filter the data by creating a view based on another view. Views on Views Similar to the way a table serves as the base for a view, a view can gather its information from another view. Creating a view using an existing view as the underlying information source helps when you want to further refine criteria on an existing view. To create a view referencing a base view, examine the following code listing. This creates a view called AuthorsView that includes information on all authors, regardless of the states they live in. The next view, AuthorsCA, creates a view that references AuthorsView and selects only those authors from California: CREATE VIEW AuthorsView AS SELECT Au_fname, State, Au_lname FROM Authors GO CREATE VIEW AuthorsCA AS SELECT Au_fname, State

For high quality jboss hosting services please check jboss web hosting website.

406 Part I EXAM PREPARATION Implementing joins within your views enables you to consolidate related data that may be scattered around your database. When you incorporate joins into your view, you are literally assembling information from diverse tables, but not actually storing data on the view, because the view does not contain the actual data. Joins are used to display data from multiple tables in a single resultset. They help in the management of your data by saving the join definition, thereby releasing the user from needing to know how to construct the join. A resultset may use joins to gather data from a number of tables say two, three, or four. You can reference up to 256 tables in a single SELECT statement or view. Take the Pubs database, for instance. To find out the royalty rate of an author, you can t just query the Authors table, because it doesn t store adequate information; you have to join two tables. The purpose of a resultset is to gather information into a single pot. To create a view that joins data, follow Step by Step 7.3. STEP BY STEP 7.3 Creating a View That Incorporates Joins 1. Open the Query Analyzer by selecting it from the Start menu. Log in with the appropriate credentials. 2. In this example, you create a view that holds the author s name from the Authors table as well as the titles written by that author from the Titles table. The only way to do this is by creating a multi-join, first by creating a join to the TitleAuthor table and then a second join to the Titles table. To create a multi-join view that joins the Authors table, TitleAuthor table, and Titles table, execute the following: CREATE VIEW AuthorSummaryInfo AS SELECT Authors.au_fname, Authors.State, title FROM Authors JOIN titleauthor ON Authors.au_ID = .TitleAuthor.Au_ID JOIN titles on titleauthor. Title_ID = .Titles.Title_ID 3. To see the view you created, run the following as shown here and in Figure 7.8:

For high quality jboss hosting services please check jboss web hosting website.

Chapter 7 WORKING WITH VIEWS 405 Northwind database. Running the following code produces a view named QuantityAnalysis: CREATE VIEW QuantityAnalysis AS SELECT OrderID, MIN (Quantity) AS minimum, MAX (Quantity) .AS maximum, AVG (Quantity) as Average, SUM (Quantity) AS Total FROM [Order Details] GROUP BY Order_ID Now, to see the results that this query produces, find out what the view will produce. To do this, execute the following query, as shown in Figure 7.7: SELECT * FROM QuantityAnalysis The summary or statistical features of aggregates are not the only way to enhance a view; you can also join tables to consolidate information. FIGURE 7.7 Reporting summary data using aggregates in views. Joins and Views Ever wondered how to gather information from multiple tables to form a view definition? This can be accomplished with a join.

For high quality jboss hosting services please check jboss web hosting website.

Chapter 7 WORKING WITH VIEWS 405 Northwind database. Running the following code produces a view named QuantityAnalysis: CREATE VIEW QuantityAnalysis AS SELECT OrderID, MIN (Quantity) AS minimum, MAX (Quantity) .AS maximum, AVG (Quantity) as Average, SUM (Quantity) AS Total FROM [Order Details] GROUP BY Order_ID Now, to see the results that this query produces, find out what the view will produce. To do this, execute the following query, as shown in Figure 7.7: SELECT * FROM QuantityAnalysis The summary or statistical features of aggregates are not the only way to enhance a view; you can also join tables to consolidate information. FIGURE 7.7 Reporting summary data using aggregates in views. Joins and Views Ever wondered how to gather information from multiple tables to form a view definition? This can be accomplished with a join.

For reliable and cheap web hosting services please check cheap web hosting website.

Chapter 7 WORKING WITH VIEWS 405 Northwind database. Running the following code produces a view named QuantityAnalysis: CREATE VIEW QuantityAnalysis AS SELECT OrderID, MIN (Quantity) AS minimum, MAX (Quantity) .AS maximum, AVG (Quantity) as Average, SUM (Quantity) AS Total FROM [Order Details] GROUP BY Order_ID Now, to see the results that this query produces, find out what the view will produce. To do this, execute the following query, as shown in Figure 7.7: SELECT * FROM QuantityAnalysis The summary or statistical features of aggregates are not the only way to enhance a view; you can also join tables to consolidate information. FIGURE 7.7 Reporting summary data using aggregates in views. Joins and Views Ever wondered how to gather information from multiple tables to form a view definition? This can be accomplished with a join.

For high quality java hosting services please check java web hosting website.

Chapter 7 WORKING WITH VIEWS 405 Northwind database. Running the following code produces a view named QuantityAnalysis: CREATE VIEW QuantityAnalysis AS SELECT OrderID, MIN (Quantity) AS minimum, MAX (Quantity) .AS maximum, AVG (Quantity) as Average, SUM (Quantity) AS Total FROM [Order Details] GROUP BY Order_ID Now, to see the results that this query produces, find out what the view will produce. To do this, execute the following query, as shown in Figure 7.7: SELECT * FROM QuantityAnalysis The summary or statistical features of aggregates are not the only way to enhance a view; you can also join tables to consolidate information. FIGURE 7.7 Reporting summary data using aggregates in views. Joins and Views Ever wondered how to gather information from multiple tables to form a view definition? This can be accomplished with a join.

For high quality website hosting services please check tomcat web hosting website.

404 Part I EXAM PREPARATION FIGURE 7.6 Accessing the dependencies of a view. Enhancing Views You can create view definitions beyond mundane SELECT statements that simply place data statements into a resultset. With views, you can use joins to get data from multiple tables. This is useful because the desired data is not always stored on a single table (in fact, this is rarely the case). You can also leverage the complexity of aggregate functions in your views in the same way you would use them on a normal table. You can further refine data retrieval by building views on views, so that data is filtered on the appropriate conditions. Aggregates and Views Aggregates offer a great deal of calculation power and views can make use of them. These aggregates include AVG, COUNT, DISTINCT, and similar functions. Leveraging the power of aggregates enables you to create useful reports that can be produced based on data in a table. The following example outlines the importance of aggregates with the example of a user who requests to see a report on the minimum, maximum, average and total values of the Quantity column (grouped by orderID) from the Order Details table in the

For high quality java hosting services please check java web hosting website.

Chapter 7 WORKING WITH VIEWS 403 STEP BY STEP 7.2 Accessing Views 1. Open the Query Analyzer by selecting it from the Start menu and logging in. 2. To view the definition of titleview from the Pubs database (see Figure 7.5), execute the following: USE pubs GO EXEC sp_helptext titleview 3. To display the view s associated tables, execute the following query. This process is shown in Figure 7.6. EXEC sp_depends titleview FIGURE 7.5 Accessing the definition of a view.

If you looking for unlimited one inclusive web hosting plan please check unlimited web hosting website.