Practise Exercises

Exercise One

  1. Write a SELECT statement that lists the customers along with their ID numbers. Include the StoreID and the AccountNumber from the Sales.Customers table.

  2. Write a SELECT statement that lists the name, product number, and color of each product from the _Production.Product _table.

  3. Write a SELECT statement that lists the customer ID numbers and sales order ID numbers from the Sales.SalesOrderHeader table.

  4. Answer this question: Why should you specify column names rather than an asterisk when writing the select-list? Give at least two reasons.

Using Execution Plans

Use the AdventureWorks2012 database to complete this exercise. Be sure to turn on the Include Actual Execution Plan setting before you begin. Type the following code into the query window, and then complete each question.

USE AdventureWorks2012;
GO

1

SELECT LastName
FROM Person.Person
WHERE LastName = 'Smith';

2

SELECT LastName
FROM Person.Person
WHERE LastName LIKE 'Sm%';

3

SELECT LastName
FROM Person.Person
WHERE LastName LIKE '%mith';

4

SELECT ModifiedDate
FROM Person.Person
WHERE ModifiedDate BETWEEN '2005-01-01' and '2005-01-31';
  • Highlight and run queries 1 and 2. Explain why there is no difference in performance between the two queries.
  • Highlight and run queries 2 and 3. Determine which query performs the best, and explain why you think so.
  • Highlight and run queries 3 and 4. Determine which query performs the best, and explain why you think so.

Working with NULLs

Use the AdventureWorks2012 database to complete this exercise. You can find the solutions in the Appendix.

  1. Write a query that displays in the “AddressLine1 (City PostalCode)” format from the Person.Address table.
  2. Write a query using the Production.Product table displaying the product ID, color, and name columns. If the color column contains a NULL value, replace the color with No Color.
  3. Modify the query written in question 2 so that the description of the product is displayed in the “Name: Color” format. Make sure that all rows display a value even if the Color value is missing.
  4. Write a query using the Production.Product table displaying a description with the “ProductID: Name” format. Hint: You will need to use a function to write this query.
  5. Explain the difference between the ISNULL and COALESCE functions.

Mathematical Operations

Use the AdventureWorks2012 database to complete this exercise.

  1. Write a query using the Sales.SpecialOffer table. Display the difference between the MinQty and MaxQty columns along with the SpecialOfferID and Description columns.
  2. Write a query using the Sales.SpecialOffer table. Multiply the MinQty column by the DiscountPct column. Include the SpecialOfferID and Description columns in the results.
  3. Write a query using the Sales.SpecialOffer table that multiplies the MaxQty column by the DiscountPct column. If the MaxQty value is NULL, replace it with the value 10. Include the SpecialOfferID and Description columns in the results.
  4. Describe the difference between division and modulo.