Suggested Practices

Objective 2.1: Write and Modify Queries

• Practice 1 Write a query using the AdventureWorks database that performs a table join using the following tables:

  • Person.Contact

  • HumanResources.Employee

  • HumanResources.EmployeeDepartmentHistory

  • HumanResources.Department

  • HumanResources.Shift

The query should return at least one non-key column from each table and use a WHERE clause to filter the results according to one of the shifts in HumanResources.ShiftID.

• Practice 2 Create a table-valued function that accepts the ContactID as an input parameter. The function should perform some operation to compute a value or append a string to another string and return the result(s) in the output table. You will then write a query that utilizes the APPLY operator and the table-valued function.

• Practice 3 Locate a test or production database utilized by your company. Search through the stored procedures or user-defined functions for that database. Look for SELECT queries that do not utilize search arguments (SARGs). Determine if there is a way that the query could be rewritten to improve the performance. Remember not to make changes to the actual database, but instead, execute the queries in a separate query window.

Objective 2.2: Design Queries for Retrieving Data from XML Sources

• Practice 1 Write a SELECT query using the AdventureWorks database. The query should return both relational and XML data from the following tables:

  • Production.Product

  • Production.ProductModel

  The query should use the query method and an XQuery expression to retrieve data from the CatalogDescription column using the Production.ProductDescriptionSchemaCollection. If you wish, you can use the exist method to return data for those records in which AdventureWorks is the name of the manufacturer.

• Practice 2 Write a SELECT query using the AdventureWorks database. The query should return at least one non-key column as XML from the following tables:

  • Person.Contact

  • HumanResources.Employee

  • HumanResources.EmployeePayHistory

  The query should use the FOR XML clause and the PATH mode to return data in a hierarchical format. Experiment with the XPath expressions, which are used to specify the shape of the XML. Return data both as attributes and then as elements. Once the query is finalized, you can attempt to return the same data using the EXPLICIT mode.