Exercises


The exercises for this chapter and Chapter 4 provide numbered, step-by-step instructions. The solutions for these exercises are the final query, commands, or result. The exercises for subsequent chapters provide less-detailed instructions. You should use the material in each chapter to determine the appropriate steps and to find the solutions.

Exercise 1

  1. Using Enterprise Manager or SQL Server Management Studio, create a new view in the AdventureWorks2000 database. You won't actually save the view, but use this option to open the graphical query designer.

  2. Add the Product table to the designer.

  3. Select the ProductID, Name, and ListPrice columns by checking the corresponding boxes in the table window.

  4. Sort the results by the Name column in ascending order using the Sort Type option.

  5. Check the SQL expression in the third pane of the graphical query designer with the solution.

image from book

Answers

The SQL Expression in the third pane of the graphical query designer should be as follows:

 SELECT   TOP 100 PERCENT ProductID, Name, ListPrice FROM     dbo.Product ORDER BY Name 

Exercise 2

  1. Using Enterprise Manager or SQL Server Management Studio, create a new view in the AdventureWorks2000 database as you did in Exercise 1.

  2. Add two tables: the Product table and the ProductSubCategory table.

  3. For the ProductSubCategory table, select the Name column and create an alias for it as SubCategory. For the Product table, select the Name column and create an alias for it as ProductName. Also select the ListPrice column from this table.

  4. Sort the query by the ProductSubCategory Name column and then the Product table Name column, both in ascending order.

  5. Execute the query and scroll through the results using the fourth pane in the designer. Check the SQL expression displayed in the third pane of the designer with the solution.

image from book

Answers

The SQL expression in the third pane of the graphical query designer should be as follows (formatting may vary):

 SELECT   TOP 100 PERCENT dbo.ProductSubCategory.Name AS SubCategory,  dbo.Product.Name AS ProductName, dbo.Product.ListPrice FROM       dbo.Product INNER JOIN dbo.ProductSubCategory  ON dbo.Product.ProductSubCategoryID =  dbo.ProductSubCategory.ProductSubCategoryID ORDER BY dbo.ProductSubCategory.Name, dbo.Product.Name 

Exercise 3

Write a simple query using the query editor window in Query Analyzer or the SQL Server Manager Studio using the following steps:

  1. Open Query Analyzer or SQL Server Management Studio.

  2. If you are using Query Analyzer for SQL Server 2000, you will be prompted for connection information. Enter localhost for the server name or the name of your server if connecting remotely. If you are using SQL Server 2005, create a new query and provide this same connection information.

  3. Select the AdventureWorks2000 database from the database selection list in the toolbar.

  4. Enter the following SQL script in the query window:

     SELECT * FROM Product WHERE ListPrice > 4000 
  5. To find out how many products have a list price greater than $4000, execute this query and check the row count in the status bar.

this query returns 58 rows. these are all of the products with a list price greater then $4,000.

Answers

This query returns 58 rows. These are all of the products with a list price greater then $4,000.

Exercise 4

Execute a simple query using command-line utilities:

  1. If you are using SQL Server 2000, open a command prompt and execute the OSQL utility to utilize Windows Integrated (Enterprise) security. If you are using SQL Server 2005, use the SQLCMD utility to utilize Windows Integrated (Enterprise) security. Each statement should be followed by a batch delineation command.

  2. Indicate that you want run statements using the AdventureWorks2000 database.

  3. Execute the following SQL statement and view the results:

     SELECT ProductCategoryID, Name FROM ProductCategory 
  4. Exit the command-line utility and then the command window.

  5. Check your statements with the solution.

image from book

Answers

Command-line statements should resemble the following:

 > OSQL –E (or SQLCMD –E)  > USE AdventureWorks2000 > GO > SELECT ProductCategoryID, Name FROM ProductCategory  > GO > EXIT > EXIT 

For simple Transact-SQL commands, the OSQL and SQLCMD command-line query utilities are nearly identical. The SQLCMD tool supports additional enhancements for SQL Server 2005.

The –E switch indicates that the database query engine should open a new connection using Windows Integrated, or Enterprise, security; using the current user's Windows credentials.

The session will be opened using the default database, typically the Master database, so it is necessary to explicitly name the target database for this session. Commands are executed in batch mode, and no commands are actually processed until a batch directive statement (GO) is issued.

The first EXIT command is processed by the query utility, which exits the session, closes the connection, and returns control to the command shell prompt. The second EXIT statement is processed by the command shell. This closes the command prompt window and terminates the command session.




Beginning Transact-SQL with SQL Server 2000 and 2005
Beginning Transact-SQL With SQL Server 2000 and 2005
ISBN: 076457955X
EAN: 2147483647
Year: 2006
Pages: 131
Authors: Paul Turley

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net