Exercises


Exercise 1

Create a new product category for Snorkels. Demonstrate two methods to add a record to this table using the SELECT and VALUES statements.

image from book

Answers

 INSERT INTO ProductCategory (Name) SELECT 'Snorkels' INSERT INTO ProductCategory (Name) VALUES ('Snorkels') 

Exercise 2

Populate a new table called RoadBikes with the contents of product records of this sub category. Your query must filter on the value 'Road Bike'.

image from book

Answers

 SELECT Product.* INTO RoadBikes  FROM Product INNER JOIN ProductSubCategory ON Product.ProductSubCategoryID = ProductSubCategory.ProductSubCategoryID  WHERE ProductSubCategory.Name = 'Road Bike' 

Exercise 3

Write a query that will delete product records for products that do not have sales order detail records and where the SubCategoryID is 5. Also write a query that will add a new record to the ProductSubCategory table with the Name column value set to Accessory. Include both of these statements in a single transaction and execute them in a batch. Check the row count of the Product table before and after to determine whether records were deleted.

image from book

Answers

 BEGIN TRANSACTION DELETE FROM Product WHERE ProductID IN ( SELECT Product.ProductID FROM Product LEFT OUTER JOIN SalesOrderDetail ON Product.ProductID = SalesOrderDetail.ProductID WHERE SalesOrderDetail.ProductID IS NULL AND Product.ProductSubCategoryID = 5 ) INSERT INTO ProductCategory (Name) SELECT ‘Accessory’ COMMIT TRANSACTION 

No product records were deleted because the second query raised an error, causing the transaction to roll-back.




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