| Create a new product category for Snorkels. Demonstrate two methods to add a record to this table using the SELECT and VALUES statements. |
|
Answers
|
INSERT INTO ProductCategory (Name) SELECT 'Snorkels' INSERT INTO ProductCategory (Name) VALUES ('Snorkels') |
| 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'. |
|
Answers
|
SELECT Product.* INTO RoadBikes FROM Product INNER JOIN ProductSubCategory ON Product.ProductSubCategoryID = ProductSubCategory.ProductSubCategoryID WHERE ProductSubCategory.Name = 'Road Bike' |
| 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. |
|
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. |