Chapter 6 Exercises

Exercise 1

Write a query to return the average weight of all touring bikes sold by Adventure Works Cycles that list for over $2,500. Use the ProductSubCategory table to determine how you should filter these products.

Answers

 SELECT AVG(Weight) FROM Product WHERE ProductSubCategoryID = 3 AND ListPrice > 2500 

In the ProductSubCategory, the sub category named 'Touring Bike' has a ProductSubCategoryID value of 3. This column is related to the foreign key column with the same name in the Product table. Using this key value, you will find touring bike records.

Twenty-eight rows match this criterion with an average weight of approximately 25.4 pounds.

Exercise 2

Designate a variable called @ProCount to hold the number of product records on record. Execute a query to return this value and assign it to the variable. Use the variable in an expression to return the value in the phrase: "There are X products on record."

Answers

 DECLARE @ProdCount Int SELECT @ProdCount = COUNT(*) FROM Product  SELECT 'There are ' + CONVERT(VarChar(5), @ProdCount) + ' products on record.' 

The SELECT statement is used to assign a variable to an aggregated value. In order to concatenate an integer value with a literal character string, it is necessary to first convert the numeric value to a compatible string. This may be done using a few different conversion functions, including CAST(), STR(), or CONVERT().

Exercise 3

Calculate the square root of the absolute value of the cosine of PI.

Answers

 SELECT SQRT(ABS(COS(PI()))) 

The result is one. The cosine is an angle in degrees converted to radians by a factor equal to the value of PI. Therefore, the cosine of PI is equal to –1. The absolute value changes the sign to positive one. The square root of one is one.

Exercise 4

How many days has it been since this book was first published on September 26, 2005? Calculate the answer using Transact-SQL functions.

Answers

 SELECT DATEDIFF(Day, '9-26-2005', GETDATE()) 

Exercise 5

Using the Individual table, return the FirstName, LastName, and the three-letter initials of all individuals who have a middle name.

Answers

 SELECT   FirstName , LastName , LEFT(FirstName, 1) + LEFT(MiddleName, 1) + LEFT(LastName, 1) AS Initials FROM Individual WHERE MiddleName IS NOT NULL