Exercises


Exercise 1

Use Query Analyzer or SQL Server Management Studio to create and execute a new query, and view the results:

  1. Open a connection to your local or remote test server.

  2. Indicate that you want to run queries against the AdventureWorks2000 database.

  3. Execute the following SQL statement:

     SELECT * FROM Contact 
  4. Check the status bar for the numbers of rows returned by the query.

  5. Check the results with the solution.

this query returns 996 rows. all columns are returned for all rows in the contact table.

Answers

This query returns 996 rows. All columns are returned for all rows in the Contact table.

Exercise 2

Insert a row using generated SQL script:

  1. Using Query Analyzer or SQL Server Management Studio, expand the AdventureWorks2000 database in the object browser. Right-click the ProductCategory table and select Script Object to New Window As Insert.

  2. In the new query editor window, remove the references to the ProductCategoryID column on both lines. On the top line, delete all text from and including the first open square bracket, [, to the first end square bracket, ], and the following comma. On the second line, remove all text from and including the first open angled bracket, <, to the first close angled bracket, >, and the following comma.

  3. One the second line, replace the placeholders (angled brackets and all text between them) for each of the columns as follows: Replace the Name with 'Widget' (including the single quotes). Replace the ModifiedDate and rowguid with the word DEFAULT (no quotes).

  4. Select the AdventureWorks2000 database from the database selection drop-down list on the toolbar and execute the query.

  5. Enter the following query to view the contents of the ProductCategory table:

     SELECT * FROM ProductCategory.  
  6. Highlight this statement and execute this query.

  7. Verify that a new row was added to the results. Check the modified SQL expression that you generated with the solution.

image from book

Answers

The modified script should resemble the following:

 INSERT INTO [AdventureWorks2000].[dbo].[ProductCategory] ([Name], [ModifedDate],  [rowguid]) VALUES ('Widget', Default, Default) 

The INSERT command script contains some unnecessary details that can be eliminated. Using this technique can help you to get started to learn the basic structure of certain Transact-SQL statements. After you've done this a few times, you will likely find it easier to write statements without assistance.

Generated script typically uses multi-part object references with names in square brackets to handle spaces and reserved words.

The Default key word is used to fill these two columns with the default values specified in the table definition.




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