2778a-enu-labmanual

Transcript

QuickStart Intelligence O F F I C I A L M I C R O S O F T L E A R N I N G P R O D U C T 2778A Lab Instructions and Lab Answer Keys: Writing Queries Using Microsoft® SQL Server® 2008 Transact-SQL This is a unique copy of the course material identified by code cbbd3d2c-1b8b-4127-8769-5439cb34900e, and provided to you by Holly Stiver. It is illegal to reprint, redistribute, or resell this content. The Licensed Content is licensed "as-is." Microsoft does not support this Licensed Content in any way and Microsoft gives no express warranties, guarantees or conditions. Please report any unauthorized use of this content to [email protected] or by calling +1 800-785-3448. QuickStart Intelligence Information in this document, including URL and other Internet Web site references, is subject to change without notice. Unless otherwise noted, the example companies, organizations, products, domain names, e-mail addresses, logos, people, places, and events depicted herein are fictitious, and no association with any real company, organization, product, domain name, e-mail address, logo, person, place or event is intended or should be inferred. Complying with all applicable copyright laws is the responsibility of the user. Without limiting the rights under copyright, no part of this document may be reproduced, stored in or introduced into a retrieval system, or transmitted in any form or by any means (electronic, mechanical, photocopying, recording, or otherwise), or for any purpose, without the express written permission of Microsoft Corporation. Microsoft may have patents, patent applications, trademarks, copyrights, or other intellectual property rights covering subject matter in this document. Except as expressly provided in any written license agreement from Microsoft, the furnishing of this document does not give you any license to these patents, trademarks, copyrights, or other intellectual property. The names of manufacturers, products, or URLs are provided for informational purposes only and Microsoft makes no representations and warranties, either expressed, implied, or statutory, regarding these manufacturers or the use of the products with any Microsoft technologies. The inclusion of a manufacturer or product does not imply endorsement of Microsoft of the manufacturer or product. Links may be provided to third party sites. Such sites are not under the control of Microsoft and Microsoft is not responsible for the contents of any linked site or any link contained in a linked site, or any changes or updates to such sites. Microsoft is not responsible for webcasting or any other form of transmission received from any linked site. Microsoft is providing these links to you only as a convenience, and the inclusion of any link does not imply endorsement of Microsoft of the site or the products contained therein. © 2008 Microsoft Corporation. All rights reserved. Microsoft, and Windows are either registered trademarks or trademarks of Microsoft Corporation in the United States and/or other countries. All other trademarks are property of their respective owners. Product Number: 2778A Part Number: X17-38829 Released: 11/2008 This is a unique copy of the course material identified by code cbbd3d2c-1b8b-4127-8769-5439cb34900e, and provided to you by Holly Stiver. It is illegal to reprint, redistribute, or resell this content. The Licensed Content is licensed "as-is." Microsoft does not support this Licensed Content in any way and Microsoft gives no express warranties, guarantees or conditions. Please report any unauthorized use of this content to [email protected] or by calling +1 800-785-3448. QuickStart Intelligence Lab Instructions: Getting Started with Databases and Transact-SQL in SQL Server 2008 1 Module 1 Lab Instructions: Getting Started with Databases and Transact-SQL in SQL Server 2008 Contents: Exercise 1: Explore the Components and Execute Queries in SQL Server Management Studio Exercise 2: Start and Use sqlcmd Exercise 3: Generate a Report from a SQL Server Database using Microsoft Office Excel 2 5 6 This is a unique copy of the course material identified by code cbbd3d2c-1b8b-4127-8769-5439cb34900e, and provided to you by Holly Stiver. It is illegal to reprint, redistribute, or resell this content. The Licensed Content is licensed "as-is." Microsoft does not support this Licensed Content in any way and Microsoft gives no express warranties, guarantees or conditions. Please report any unauthorized use of this content to [email protected] or by calling +1 800-785-3448. QuickStart Intelligence 2 Lab Instructions: Getting g Started with Databa ases and Transact-SQ QL in SQL Server 2008 8 Lab: Using U SQL S Ser rver Ma anagem ment Stu udio and SQLC CMD Exercise 1: Explore th he Components and Execute Queries in SQL Serve er M Manageme ent Studio Sc cenario Yo ou are the dat tabase adminis strator of Adve enture Works. The Human Resources depa artment requir res that yo ou generate se everal reports by using SQL Server S 2008. You Y need to ge enerate reports with a list of em mployee addre esses sorted by y departments s or a list of ad ddresses of em mployees residing in the Unit ted St tates. Th he main tasks for this exercis se are as follow ws: 1. . 2. . 3. . 4. . 5. . 6. . 7. . Launch SQL L Server Management Stud dio. Navigate th hrough the online help. Resize, hide e, and close Ob bject Explorer and Solution n Explorer. Create a ne ew solution and explore the solution objec cts in Object Explorer. Add projects to an existin ng solution and create queries in the proje ects. Connect to SQL Server an nd execute a query. q Use Visual Query Builde er to return row ws from a tabl le. Task 1: Launc ch the SQL Server S Management Stu udio • • Start 2778A A-NY-SQL-01 1 and logon as Student using g the password Pa$$w0rd. Start the Se erver Manage ement Studio and connect to t the server. This is a unique copy of the course material identified by code cbbd3d2c-1b8b-4127-8769-5439cb34900e, and provided to you by Holly Stiver. It is illegal to reprint, redistribute, or resell this content. The Licensed Content is licensed "as-is." Microsoft does not support this Licensed Content in any way and Microsoft gives no express warranties, guarantees or conditions. Please report any unauthorized use of this content to [email protected] or by calling +1 800-785-3448. QuickStart Intelligence Lab Instructions: Getting Started with Databases and Transact-SQL in SQL Server 2008 3 Task 2: Navigate through online Help • Navigate to SQL Server Books Online | Getting Started | Initial Installation | Overview of SQL Server Installation | Features and Tools Overview |SQL Server Studios Overview | Introducing SQL Server Management Studio | Tutorial: SQL Server Management Studio | Lesson 1: Basic Navigation in SQL Server Management Studio. View the Lesson 1: Basic Navigation in SQL Server Management Studio page. View the Connecting with Registered Servers and Object Explorer page. • • Task 3: Resize, hide, and close Object Explorer and Solution Explorer • • • • Resize the Contents pane. Close the Help window. Auto Hide the Solution Explorer pane. Auto Hide the Object Explorer pane. Task 4: Create a new solution and explore the solution objects in Object Explorer • • • • • Dock the Object Explorer pane. View the AdventureWorks2008 tables. Create a new query. Dock the Solution Explorer pane. Add a new project. • • Name: PersonAddress Create a new connection. Task 5: Add projects to an existing solution and create queries in the projects • • Create a new query. Enter the query window. • • • • Query: USE AdventureWorks2008 Execute the query. Notice the active database is changed from master to AdventureWorks2008. Add additional text to the query window. • Query: SELECT DISTINCT CITY FROM Person.Address • • Execute the query. Rename SQLQuery1.sql. • Query: Address.sql • • • Save Address.sql. Save PersonAddress.ssmssqlproj. Add a new project. • Name: HumanResourcesDepartment • Create a new connection. This is a unique copy of the course material identified by code cbbd3d2c-1b8b-4127-8769-5439cb34900e, and provided to you by Holly Stiver. It is illegal to reprint, redistribute, or resell this content. The Licensed Content is licensed "as-is." Microsoft does not support this Licensed Content in any way and Microsoft gives no express warranties, guarantees or conditions. Please report any unauthorized use of this content to [email protected] or by calling +1 800-785-3448. QuickStart Intelligence 4 Lab Instructions: Getting Started with Databases and Transact-SQL in SQL Server 2008 • • Create a new query. Rename the query. • Name: Department.sql • Enter the query window. • Query: USE AdventureWorks2008 • • • Execute the query. Notice the active database is changed from master to AdventureWorks2008. Add additional text to the query window. • Query: SELECT Name, GroupName FROM HumanResources.Department • • • • Execute the query. Save Department.sql. Save HumanResourcesDepartment.ssmssqlproj. Save solution as: • Name: AdventureWorks2008.ssmssl. • Close SQL Server Management Studio. Task 6: Connect to SQL Server and execute a Query • • Start Server Management Studio and connect to the server. Open the file. • • • • Folder: PersonAddress, File: Address.sql Connect to Database Engine. Execute the query. Task 7: Use Visual Query Builder to return rows from a table • Create a new query. • • Database: AdventureWorks2008 Open the Query Designer. • On the Query menu, click Design Query in Editor. • • • • Examine the Add Table dialog box. Select all columns. Execute the query. Close SQL Server Management Studio. Results: After this exercise, you should have explored the components and executed queries in the SQL Server Management Studio. This is a unique copy of the course material identified by code cbbd3d2c-1b8b-4127-8769-5439cb34900e, and provided to you by Holly Stiver. It is illegal to reprint, redistribute, or resell this content. The Licensed Content is licensed "as-is." Microsoft does not support this Licensed Content in any way and Microsoft gives no express warranties, guarantees or conditions. Please report any unauthorized use of this content to [email protected] or by calling +1 800-785-3448. QuickStart Intelligence Lab Instructions: Getting Started with Databases and Transact-SQL in SQL Server 2008 5 Exercise 2: Start and Use sqlcmd Scenario You need to perform the query previously created using the sqlcmd utility. The main tasks for this exercise are as follows: 1. 2. 3. 4. Start the sqlcmd utility and connect to a default instance of SQL Server. Run a T-SQL script file by using sqlcmd. Run a T-SQL script file and save the output to a text file. Review the output file. Task 1: Start the sqlcmd utility and connect to a default instance of SQL Server • • • Open Command Prompt. Type sqlcmd. End the sqlcmd session. Task 2: Run a Transact-SQL script file by using sqlcmd • In the Command Prompt window, type a sqlcmd command: • Command: sqlcmd -S NY-SQL-01 -i E:\MOD01\Labfiles\Starter\Department.sql Task 3: Run a Transact-SQL script file and save the output to a text file • In the Command Prompt window, type a sqlcmd command: • • Command: sqlcmd -S NY-SQL-01 -i E:\MOD01\Labfiles\Starter\Department.sql -o E:\MOD01\Labfiles\Solution\DeptList.txt Close Command Prompt. Task 4: Review the output file • • Start Microsoft® Windows Explorer. Open the output file: • • • • Directory: E:\MOD01\Labfiles\Solution\ File: DeptList.txt Close Notepad. Close Windows Explorer. Results: After this exercise, you should have started and used sqlcmd to create reports. This is a unique copy of the course material identified by code cbbd3d2c-1b8b-4127-8769-5439cb34900e, and provided to you by Holly Stiver. It is illegal to reprint, redistribute, or resell this content. The Licensed Content is licensed "as-is." Microsoft does not support this Licensed Content in any way and Microsoft gives no express warranties, guarantees or conditions. Please report any unauthorized use of this content to [email protected] or by calling +1 800-785-3448. QuickStart Intelligence 6 Lab Instructions: Getting Started with Databases and Transact-SQL in SQL Server 2008 Exercise 3: Generate a Report from a SQL Server Database using Microsoft Office Excel Scenario You need to analyze the details of the newly hired employees by using a database diagram in SQL Server Management Studio. The main tasks for this exercise are as follows: 1. 2. 3. 4. Launch Excel. Create a new data connection in the workbook. Select the data to import and its format. View the report. Task 1: Launch Excel • Launch Excel. • Location: All Programs | Microsoft Office | Microsoft Office Excel 2007 Task 2: Create a new data connection in the workbook • Get external data from SQL Server. • Server name: NY-SQL-01 Task 3: Select the data to import and its format • Select the database and table. • • • Database: AdventureWorks2008 Table Name: Address Save data connection file and finish. • • Description: AdventureWorks2008 Addresses Friendly Name: Addresses • • Examine Authentication Settings. Click Finish. Task 4: View the report • • • • • Notice the options for the Import Data dialog box. Accept the default settings. Examine the results. Close Microsoft Office Excel. Turn off 2778A-NY-SQL-01 and delete changes. Results: After this exercise, you should create a report from a SQL Server database using Microsoft Office Excel. This is a unique copy of the course material identified by code cbbd3d2c-1b8b-4127-8769-5439cb34900e, and provided to you by Holly Stiver. It is illegal to reprint, redistribute, or resell this content. The Licensed Content is licensed "as-is." Microsoft does not support this Licensed Content in any way and Microsoft gives no express warranties, guarantees or conditions. Please report any unauthorized use of this content to [email protected] or by calling +1 800-785-3448. QuickStart Intelligence Lab Instructions: Querying and Filtering Data 1 Module 2 Lab Instructions: Querying and Filtering Data Contents: Exercise 1: Retrieve Data by Using the SELECT Statement Exercise 2: Filter Data by Using Different Search Conditions Exercise 3: Use Functions to Work with Null Values Exercise 4: Formatting Result Sets 2 4 6 8 This is a unique copy of the course material identified by code cbbd3d2c-1b8b-4127-8769-5439cb34900e, and provided to you by Holly Stiver. It is illegal to reprint, redistribute, or resell this content. The Licensed Content is licensed "as-is." Microsoft does not support this Licensed Content in any way and Microsoft gives no express warranties, guarantees or conditions. Please report any unauthorized use of this content to [email protected] or by calling +1 800-785-3448. QuickStart Intelligence 2 Lab Instructions: Queryin ng and Filtering Data a Lab: Queryin ng and Filterin ng Data a Exercise 1: Retrieve Data D by Us sing the SE ELECT Statement Sc cenario Adventure Works manageme ent is attemptin ng to learn mo ore about their customers an nd they would like to co ontact each on ne with a surve ey. You have been b asked to produce a list of contacts. In n this exercise, you will create e simple SELEC CT statement queries. Th he main tasks for this exercis se are as follow ws: 1. . 2. . 3. . Launch SQL L Server Management Studio o. Generate a report by usin ng the SELECT * statement with the FROM M clause. Generate a report by usin ng the SELECT statement wit th the FROM clause. Task 1: Launc ch SQL Serve er Managem ment Studio • • In the Lab Launcher, L next t to 2778A-NY Y-SQL-01, click k Launch. Log on as Administrator A r with the pass sword of Pa$$ $w0rd. Task 2: Gener rate a report by using th he SELECT * statement with the FRO OM clause • • • Start SQL Server Management Studio and connect to o NY-SQL-01. uery window, create c a query y that uses the AdventureWo orks2008 datab base and displays all In a new qu columns an nd all rows from m the Person.P Person table. Execute the e query and br rowse the resu ults. Task 3: Gener rate a report by using th he SELECT statement with the FROM M clause • In a new qu uery window, type t and execu ute an SQL sta atement that: • Accesses the Person.Person table in n the Adventu ureWorks2008 database. This is a unique copy of the course material identified by code cbbd3d2c-1b8b-4127-8769-5439cb34900e, and provided to you by Holly Stiver. It is illegal to reprint, redistribute, or resell this content. The Licensed Content is licensed "as-is." Microsoft does not support this Licensed Content in any way and Microsoft gives no express warranties, guarantees or conditions. Please report any unauthorized use of this content to [email protected] or by calling +1 800-785-3448. QuickStart Intelligence Lab Instructions: Querying and Filtering Data 3 • • Includes the FirstName, LastName, and MiddleName columns. Browse the result set in the Results pane and notice that only the specified columns appear. Results: After this exercise, you should have learned how to create a basic SELECT statement to retrieve data from a table. This is a unique copy of the course material identified by code cbbd3d2c-1b8b-4127-8769-5439cb34900e, and provided to you by Holly Stiver. It is illegal to reprint, redistribute, or resell this content. The Licensed Content is licensed "as-is." Microsoft does not support this Licensed Content in any way and Microsoft gives no express warranties, guarantees or conditions. Please report any unauthorized use of this content to [email protected] or by calling +1 800-785-3448. QuickStart Intelligence 4 Lab Instructions: Querying and Filtering Data Exercise 2: Filter Data by Using Different Search Conditions Scenario The marketing department is conducting an audit of catalog inventory of socks and tights for a mail order campaign. In order to provide the best chance of creating orders, they have determined that most are willing to spend between $50 and $180 for these items. They have also found that most tights that are purchased in the store are size M and L. They have requested a list of all items the company carries within these parameters. In this exercise, you will learn the different methods used to query for specific rows of data from a table. The main tasks for this exercise are as follows: 1. 2. 3. 4. 5. Generate a report by using the SELECT statement with a COMPARISON operator. Generate a report by using the SELECT statement with the AND and LIKE operators. Generate a report by using the SELECT statement with the OR operator. Generate a report by using the SELECT statement with the BETWEEN operator. Generate a report by using the SELECT statement with the IN operator. Task 1: Generate a report by using the SELECT statement with a COMPARISON operator • In a new query window enter and execute a SELECT statement that: • • • • Accesses the Production.Product table in the AdventureWorks2008 database. Includes the ProductNumber, Name, ListPrice, Color, Size, and Weight columns. The ListPrice must be lower than $100. Browse the result set. Task 2: Generate a report by using the SELECT statement with the AND and LIKE operators • Change the SELECT statement so that it: • • • • • Accesses information in the Production.Product table of the AdventureWorks2008 database. Includes ProductNumber, Name, ListPrice, Color, Size, and Weight The ListPrice is less than $100 The ProductNumber column begins with the string SO. Browse the result set and notice that all of the rows returned contain the word "SO". Task 3: Generate a report by using the SELECT statement with the OR operator • • • Change the query to include rows with the TG as well as SO in the Name column. Execute the query. Browse the result set and note the additional rows for tights. Task 4: Generate a report by using the SELECT statement with the BETWEEN operator • Change the query to select rows that: • • • Display rows with SO included in the Product Number. Or rows of tights that have a list price between $50 and $180. Execute the query. This is a unique copy of the course material identified by code cbbd3d2c-1b8b-4127-8769-5439cb34900e, and provided to you by Holly Stiver. It is illegal to reprint, redistribute, or resell this content. The Licensed Content is licensed "as-is." Microsoft does not support this Licensed Content in any way and Microsoft gives no express warranties, guarantees or conditions. Please report any unauthorized use of this content to [email protected] or by calling +1 800-785-3448. QuickStart Intelligence Lab Instructions: Querying and Filtering Data 5 • Browse the result set and note that the number of rows has changed and the column data matches the new search conditions. Task 5: Generate a report by using the SELECT statement with the IN operator • • • Add a search condition that limits 'tights' to sizes 'M' and 'L'. Execute the query. Note the additional filtering of the data rows within the result set. Results: After this exercise, you should have learned how to use several different comparison operators to create reports specific to different user needs. This is a unique copy of the course material identified by code cbbd3d2c-1b8b-4127-8769-5439cb34900e, and provided to you by Holly Stiver. It is illegal to reprint, redistribute, or resell this content. The Licensed Content is licensed "as-is." Microsoft does not support this Licensed Content in any way and Microsoft gives no express warranties, guarantees or conditions. Please report any unauthorized use of this content to [email protected] or by calling +1 800-785-3448. QuickStart Intelligence 6 Lab Instructions: Querying and Filtering Data Exercise 3: Use Functions to Work with Null Values Scenario Upon checking through all of the catalog inventory records in the database, management has discovered that many items do not have a product line assigned to them. They would like a listing of all these items so they can make corrections in the system. In this exercise, you will learn ways to locate and handle NULL values when querying data. The main tasks for this exercise are as follows: 1. 2. 3. 4. 5. Generate a report by using the SELECT statement with the NULL function. Generate a report by using the SELECT statement with the IS NULL function. Generate a report by using the SELECT statement with the ISNULL function to rename values. Generate a report by using the SELECT statement with the ISNULL function and a column alias. Generate a report by using the SELECT statement with the ISNULL function and the COALESCE and CONVERT functions. Task 1: Generate a report by using the SELECT statement with the NULL function • In a new query window, enter and execute a SELECT statement that: • • • • Retrieves rows from the Production.Product table in the AdventureWorks2008 database. Includes ProductNumber, Name, and Weight. Uses the = operator to check ProductLine for a NULL value. Browse the result set and note the total number of rows. Task 2: Generate a report by using the SELECT statement with the IS NULL function • • • Change the statement to use IS NULL to select rows with a value of NULL in the ProductLine column. Execute the query. Browse the result set and notice the number of rows returned and the data displayed in the ProductLine column. Task 3: Generate a report by using the SELECT statement with the ISNULL function to rename values • In a new query window, enter and execute a SELECT statement that: • • • • • Accesses the Production.Product table in the AdventureWorks2008 database. Displays the ProductNumber, Name, Weight, and ProductLine columns. Use the ISNULL() function to display 'NA' when a NULL value is encountered in the ProductLine column. Browse the result set and note the additional column and values. Also make note of the column headings. Task 4: Generate a report by using the SELECT statement with the ISNULL function and a column alias • • Change the statement to add "Product Line" as a column heading for the ProductLine column. Execute the query. This is a unique copy of the course material identified by code cbbd3d2c-1b8b-4127-8769-5439cb34900e, and provided to you by Holly Stiver. It is illegal to reprint, redistribute, or resell this content. The Licensed Content is licensed "as-is." Microsoft does not support this Licensed Content in any way and Microsoft gives no express warranties, guarantees or conditions. Please report any unauthorized use of this content to [email protected] or by calling +1 800-785-3448. QuickStart Intelligence Lab Instructions: Querying and Filtering Data 7 • Note that the result set is identical but with a column heading over the new column. Task 5: Generate a report by using the SELECT statement with the ISNULL function and the COALESCE and CONVERT functions • Rewrite the statement to use the COALESCE() function to create a new column named Measurement so that: • • • • • If the Weight column has a value it is shown in the Measurement column. If the Weight column is NULL but the Size column is not NULL, display the value in the Measurement column. If both columns have NULL values display 'NA'. Execute the query. Browse the result set and notice the new values in the 'Measurement' column. Results: After this exercise, you should have learned to handle NULL values in a result set by identifying them and replacing them with alternate values when necessary. This is a unique copy of the course material identified by code cbbd3d2c-1b8b-4127-8769-5439cb34900e, and provided to you by Holly Stiver. It is illegal to reprint, redistribute, or resell this content. The Licensed Content is licensed "as-is." Microsoft does not support this Licensed Content in any way and Microsoft gives no express warranties, guarantees or conditions. Please report any unauthorized use of this content to [email protected] or by calling +1 800-785-3448. QuickStart Intelligence 8 Lab Instructions: Querying and Filtering Data Exercise 4: Formatting Result Sets Scenario • • The marketing department needs a listing of items in the product file grouped by product line, description, and price. Management likes your original contacts list and would like you to refine it just a little so that the names are presented in a single column with the format of LastName, FirstName MiddleInitial. In this exercise, you will learn how to format result sets to make them easier to read and interpret. The main tasks for this exercise are as follows: 1. 2. 3. 4. 5. 6. Format a result set by using the ORDER BY clause. Format a result set by using the ORDER BY clause and the DESC keyword. Format a result set by using the ORDER BY clause and the DISTINCT keyword. Format a result set by concatenating strings. Format a result set by concatenating strings and using column aliasing. Format a result set by using the SUBSTRING function. Task 1: Format a result set by using the ORDER BY clause • In a new query window, write a SELECT statement that: • • • • Accesses the Production.Product table in the AdventureWorks2008 database. Includes ProductNumber, Name, and Class. Sorts the rows by the Class column. Execute the query and browse the result set. Task 2: Format a result set by using the ORDER BY clause and the DESC keyword • Rewrite the SELECT statement to: • • • • • Add the ListPrice column. Sort the data by Class. Sort by ListPrice in descending order within each class. Execute the query. Browse the new result set. Task 3: Format a result set by using the DISTINCT keyword • Write a new SELECT statement that will: • • • • • • Access the Production.Product table in the AdventureWorks2008 database. Includes the Color column. Shows only one row for each color. For all products that have 'helmet' as part of the Name. Execute the query. Browse the result set. This is a unique copy of the course material identified by code cbbd3d2c-1b8b-4127-8769-5439cb34900e, and provided to you by Holly Stiver. It is illegal to reprint, redistribute, or resell this content. The Licensed Content is licensed "as-is." Microsoft does not support this Licensed Content in any way and Microsoft gives no express warranties, guarantees or conditions. Please report any unauthorized use of this content to [email protected] or by calling +1 800-785-3448. QuickStart Intelligence Lab Instructions: Querying and Filtering Data 9 Task 4: Format a result set by concatenating strings • Create a new listing that will: • • • • Access the Person.Person table in the AdventureWorks2008 database. Create a derived column made up of the LastName and FirstName columns concatenated with a comma and a space between them. Execute the query. Browse the result set and notice the new column has no meaningful heading. Task 5: Format a result set by concatenating strings and using column aliasing • • • Rewrite the query to assign the column name 'Contacts'. Execute the query. Note the new column name in the result set. Task 6: Format a result set by using the SUBSTRING function • • • • • Rewrite the statement so that it will search for all rows with the LastName beginning with 'Mac'. Use the SUBSTRING() function. Execute the query. Browse the result set. Turn off the 2778A-NY-SQL-01 virtual machine and discard changes. Results: After this exercise, you should have learned how to format the result sets to make them more readable. This is a unique copy of the course material identified by code cbbd3d2c-1b8b-4127-8769-5439cb34900e, and provided to you by Holly Stiver. It is illegal to reprint, redistribute, or resell this content. The Licensed Content is licensed "as-is." Microsoft does not support this Licensed Content in any way and Microsoft gives no express warranties, guarantees or conditions. Please report any unauthorized use of this content to [email protected] or by calling +1 800-785-3448. QuickStart Intelligence Lab Instructions: Grouping and Summarizing Data 1 Module 3 Lab Instructions: Grouping and Summarizing Data Contents: Exercise 1: Summarizing Data by Using Aggregate Functions Exercise 2: Summarizing Grouped Data Exercise 3: Ranking Grouped Data Exercise 4: Creating Crosstab Queries 2 5 9 11 This is a unique copy of the course material identified by code cbbd3d2c-1b8b-4127-8769-5439cb34900e, and provided to you by Holly Stiver. It is illegal to reprint, redistribute, or resell this content. The Licensed Content is licensed "as-is." Microsoft does not support this Licensed Content in any way and Microsoft gives no express warranties, guarantees or conditions. Please report any unauthorized use of this content to [email protected] or by calling +1 800-785-3448. QuickStart Intelligence 2 Lab Instructions: Groupin ng and Summarizing g Data Lab: Groupin ng and Summ marizing g Data Exercise 1: Summariz zing Data by Using Aggregate Functions s Sc cenario Yo ou are the dat tabase adminis strator of Adve enture Works. The HR depar rtment wants you y to prepare ea re eport on the number of vaca ation hours of the vice presid dents of the co ompany, and another a report t on the nu umber of emp ployees with in ncomplete add dresses. In n this exercise, you will use built b in aggrega ate functions to t summarize data in differe ent ways. You will also w work around NU ULL values in your y summarie es. Th his exercise’s main m tasks are: 1. . 2. . 3. . 4. . 5. . Start the 27 778A-NY-SQL L-01 virtual ma achine, log on as Administr rator, and laun nch SQL Serve er Manageme ent Studio. Create and execute a que ery that display ys a single sum mmary value fo or all rows. Create and execute a second query tha at displays a sin ngle summary y value for all rows. Create and execute a thir rd query that computes a total. Modify a query to eliminate NULL values. This is a unique copy of the course material identified by code cbbd3d2c-1b8b-4127-8769-5439cb34900e, and provided to you by Holly Stiver. It is illegal to reprint, redistribute, or resell this content. The Licensed Content is licensed "as-is." Microsoft does not support this Licensed Content in any way and Microsoft gives no express warranties, guarantees or conditions. Please report any unauthorized use of this content to [email protected] or by calling +1 800-785-3448. QuickStart Intelligence Lab Instructions: Grouping and Summarizing Data 3 Task 1: Start the 2778A-NY-SQL-01 virtual machine, log on as Administrator, and launch SQL Server Management Studio • • Start 6235A-NY-SQL-01, and log on as Administrator with the password of Pa$$w0rd. Launch SQL Server Management Studio. Task 2: Create and execute a query that displays a single summary value for all the rows • • • Create a query that will return the average vacation hours and the total sick leave hours for Vice Presidents. Use the AVG and SUM aggregate functions. Execute the query and review the results. Task 3: Create and execute a second query that displays a single summary value for all the rows • • • Create a query that returns a count of employees. Use the COUNT function. Execute the query and review the results. Task 4: Create and execute a third query that computes the total number of employees with the Addressline2 value as NULL • • • Create a query that returns a count of employees where the AddressLine2 column has NULL values. Use the COUNT and ISNULL functions. Execute the query and review the results. This is a unique copy of the course material identified by code cbbd3d2c-1b8b-4127-8769-5439cb34900e, and provided to you by Holly Stiver. It is illegal to reprint, redistribute, or resell this content. The Licensed Content is licensed "as-is." Microsoft does not support this Licensed Content in any way and Microsoft gives no express warranties, guarantees or conditions. Please report any unauthorized use of this content to [email protected] or by calling +1 800-785-3448. QuickStart Intelligence 4 Lab Instructions: Grouping and Summarizing Data Task 5: Modify the query to eliminate the NULL values • • Create a query that returns the same values as Task 4 but without taking NULL values into account. Use the COUNT function: • • Remember that the COUNT function when used with a column name will ignore NULL values. Execute the query and review the results. Results: After this exercise, you should have launched SQL Server Management Studio and created queries to display summary values for rows. You should have also created queries to compute totals and eliminate NULL values. This is a unique copy of the course material identified by code cbbd3d2c-1b8b-4127-8769-5439cb34900e, and provided to you by Holly Stiver. It is illegal to reprint, redistribute, or resell this content. The Licensed Content is licensed "as-is." Microsoft does not support this Licensed Content in any way and Microsoft gives no express warranties, guarantees or conditions. Please report any unauthorized use of this content to [email protected] or by calling +1 800-785-3448. QuickStart Intelligence Lab Instructions: Grouping and Summarizing Data 5 Exercise 2: Summarizing Grouped Data Scenario The warehouse manager requires three reports on the average number of hours taken to manufacture a product, the order quantity and the price list of each product, and the safety stock level for red, blue, and black helmets. This exercise’s main tasks are: 1. Create and execute a query to compute the average number of days to manufacture a product. 2. Create and execute a query to display the various colors of a particular product and computes the average ListPrice of the colors. 3. Create and execute a query to generate a report that lists the average order quantity and sum of line total for each product with a line total that exceeds $1000000.00 and with average quantity less than 3. 4. Create and execute a query to group the products and then compute the sum of the quantity shelfwise. 5. Create and execute a query to generate a summary report. 6. Distinguish summary and aggregated data from actual table rows. 7. Create and execute a query to generate a report of the summary columns by using the GROUPING function. 8. Create and execute a query that generates a report that displays all the products, the unit price, the unit price discount, and the sum of the columns. This is a unique copy of the course material identified by code cbbd3d2c-1b8b-4127-8769-5439cb34900e, and provided to you by Holly Stiver. It is illegal to reprint, redistribute, or resell this content. The Licensed Content is licensed "as-is." Microsoft does not support this Licensed Content in any way and Microsoft gives no express warranties, guarantees or conditions. Please report any unauthorized use of this content to [email protected] or by calling +1 800-785-3448. QuickStart Intelligence 6 Lab Instructions: Grouping and Summarizing Data Task 1: Create and execute a query that computes the average number of days to manufacture a product • • • Create a query that returns the product ID and average days to manufacture from the Products table. Use AVG. Execute the query and review the results. Task 2: Create and execute a query that displays the various colors of a particular product and computes the average ListPrice of the colors • • • • Create a query that returns the color and average ListPrice for a specific product. Use a GROUP BY to ensure the averages are computed for the correct column. Use a WHERE clause to restrict the query to a single product number. Execute the query and review the results. This is a unique copy of the course material identified by code cbbd3d2c-1b8b-4127-8769-5439cb34900e, and provided to you by Holly Stiver. It is illegal to reprint, redistribute, or resell this content. The Licensed Content is licensed "as-is." Microsoft does not support this Licensed Content in any way and Microsoft gives no express warranties, guarantees or conditions. Please report any unauthorized use of this content to [email protected] or by calling +1 800-785-3448. QuickStart Intelligence Lab Instructions: Grouping and Summarizing Data 7 Task 3: Create and execute a query to generate a report that lists the average order quantity and sum of line total for each product with a line total that exceeds $1000000.00, and with average quantity less than 3 • • • Create a query that returns the ProductID, average OrderQty and total of LineTotal. Use a GROUP BY to ensure the averages are computed for the correct column. Use a HAVING clause to restrict the query to run for cases where the total and averages of LineTotal and OrderQty respectively are in the proper ranges. • • Don’t be afraid to use functions in the HAVING clause. Execute the query and review the results. Task 4: Create and execute a query to group the products and then compute the sum of the quantity shelf-wise • • • Create a query that returns the ProductID Shelf and total Quantity for a group of products. Use a GROUP BY with the ROLLUP operator to calculate the columns in a specific order. Execute the query and review the results. Task 5: Create and execute a query to generate a summary report • • • • Create a query that summarizes sales information from the SalesOrderDetail table using the CUBE operator. Sum the number of orders. Specify the CUBE operator with the correct columns from the table (orders are related to products and the id of the SalesOrderDetail row. Execute the query and review the results. Task 6: Distinguish the rows generated by the summary or aggregations and actual table rows • • • • Create a query that summarizes sales quotas and total YTD sales from the SalesPerson table using the CUBE operator. Use the GROUPING operator in tandem with the CUBE operator to indicate the column not being aggregated. Sum the SalesYTD column. Execute the query and review the results. Task 7: Create and execute a query to generate a report of the summary columns by using the GROUPING function • • • • Create a query that summarizes information about the ProductInventory table using the CUBE operator and GROUPING operator. The CUBE operator should be used with multiple columns and the GROUPING should be used with one of those columns. Sum the Quantity column. Execute the query and review the results. This is a unique copy of the course material identified by code cbbd3d2c-1b8b-4127-8769-5439cb34900e, and provided to you by Holly Stiver. It is illegal to reprint, redistribute, or resell this content. The Licensed Content is licensed "as-is." Microsoft does not support this Licensed Content in any way and Microsoft gives no express warranties, guarantees or conditions. Please report any unauthorized use of this content to [email protected] or by calling +1 800-785-3448. QuickStart Intelligence 8 Lab Instructions: Grouping and Summarizing Data Task 8: Create and execute a query to generate a report that displays all the products, the unit price, the unit price discount, and the sum of the columns • • • Create a query to summarize information about the SalesOrderDetail table using the COMPUTE clause. Sum the UnitPrice and UnitPriceDiscount columns without using aggregate functions and GROUP BY - use the COMPUTE clause. Execute the query and review the results. Results: After this exercise, you have learned how to create queries to compute averages, display additional data and perform summations. You should have also learned how to create queries using aggregation functions and the GROUPING function. This is a unique copy of the course material identified by code cbbd3d2c-1b8b-4127-8769-5439cb34900e, and provided to you by Holly Stiver. It is illegal to reprint, redistribute, or resell this content. The Licensed Content is licensed "as-is." Microsoft does not support this Licensed Content in any way and Microsoft gives no express warranties, guarantees or conditions. Please report any unauthorized use of this content to [email protected] or by calling +1 800-785-3448. QuickStart Intelligence Lab Instructions: Grouping and Summarizing Data 9 Exercise 3: Ranking Grouped Data Scenario The marketing manager wants you to prepare a report on the year-to-date sales of salespersons and rank their names based on their performance. This exercise’s main tasks are: 1. 2. 3. 4. Generate row numbers for rows returned by a query. Create and execute a second query using the RANK function. Create and execute a third query using the DENSE_RANK function. Create and execute a fourth query that sorts data in descending order and group’s data into categories. Task 1: Generate row numbers for each of the rows in the year-to-date sales of the salespersons • • • • Create a query that returns numbered rows for the SalesPerson table. Use the ROW_NUMBER function, specifying the ordering. Filter the results to prune away sales persons with null territories and no sales. Execute the query and review the results. Task 2: Create and execute a second query using the RANK function • • • Create a query that returns numbered rows for the ProductInventory table. Use the RANK function, specifying the ordering. Execute the query and review the results. Task 3: Create and execute a third query using the DENSE_RANK function • • • Create a query that returns numbered rows for the ProductInventory table. Use the DENSE_RANK function, specifying the ordering. Execute the query and review the results. This is a unique copy of the course material identified by code cbbd3d2c-1b8b-4127-8769-5439cb34900e, and provided to you by Holly Stiver. It is illegal to reprint, redistribute, or resell this content. The Licensed Content is licensed "as-is." Microsoft does not support this Licensed Content in any way and Microsoft gives no express warranties, guarantees or conditions. Please report any unauthorized use of this content to [email protected] or by calling +1 800-785-3448. QuickStart Intelligence 10 Lab Instructions: Grouping and Summarizing Data Task 4: Create and execute a fourth query that sorts data in descending order and group’s data into categories • • • • Create a query that returns numbered rows for the SalesPerson table. Use the NTILE function, using the SalesYTD column for the ordering. Filter the results to prune away sales persons with null territories and no sales. Execute the query and review the results. Results: After this exercise, you have learned how to create queries that use the various ranking functions to organize and rank grouped data. This is a unique copy of the course material identified by code cbbd3d2c-1b8b-4127-8769-5439cb34900e, and provided to you by Holly Stiver. It is illegal to reprint, redistribute, or resell this content. The Licensed Content is licensed "as-is." Microsoft does not support this Licensed Content in any way and Microsoft gives no express warranties, guarantees or conditions. Please report any unauthorized use of this content to [email protected] or by calling +1 800-785-3448. QuickStart Intelligence Lab Instructions: Grouping and Summarizing Data 11 Exercise 4: Creating Crosstab Queries In this exercise, you will use built in aggregate functions to summarize data in different ways. You will also work around NULL values in your summaries. This exercise’s main tasks are: 1. Create and execute a query using SELECT and the PIVOT operator. 2. Create and execute a second query using SELECT and the UNPIVOT operator. Task 1: Create and execute a query using SELECT and the PIVOT operator • • • • • • Create a query that returns a pivoted table expression from the Product table. Use the PIVOT operator. Filter the input to only use "helmet" products. Sum the amount of each helmet by the colors of Red, Blue and Black. Order the output by product name. Execute the query and review the results. Task 2: Create and execute a second query using SELECT and the UNPIVOT operator • • Create a query that uses the UNPIVOT operator to turn a table expression into column values. Execute the query and review the results. This is a unique copy of the course material identified by code cbbd3d2c-1b8b-4127-8769-5439cb34900e, and provided to you by Holly Stiver. It is illegal to reprint, redistribute, or resell this content. The Licensed Content is licensed "as-is." Microsoft does not support this Licensed Content in any way and Microsoft gives no express warranties, guarantees or conditions. Please report any unauthorized use of this content to [email protected] or by calling +1 800-785-3448. QuickStart Intelligence 12 Lab Instructions: Grouping and Summarizing Data Results: After this exercise, you have learned how to use the PIVOT operator to create a crosstab query and also how to use the UNPIVOT operator. This is a unique copy of the course material identified by code cbbd3d2c-1b8b-4127-8769-5439cb34900e, and provided to you by Holly Stiver. It is illegal to reprint, redistribute, or resell this content. The Licensed Content is licensed "as-is." Microsoft does not support this Licensed Content in any way and Microsoft gives no express warranties, guarantees or conditions. Please report any unauthorized use of this content to [email protected] or by calling +1 800-785-3448. QuickStart Intelligence Lab Instructions: Grouping and Summarizing Data 13 Lab Shutdown After you complete the lab, you must shut down the 2778A-NY-SQL-01 virtual machine and discard any changes. This is a unique copy of the course material identified by code cbbd3d2c-1b8b-4127-8769-5439cb34900e, and provided to you by Holly Stiver. It is illegal to reprint, redistribute, or resell this content. The Licensed Content is licensed "as-is." Microsoft does not support this Licensed Content in any way and Microsoft gives no express warranties, guarantees or conditions. Please report any unauthorized use of this content to [email protected] or by calling +1 800-785-3448. QuickStart Intelligence Lab Instructions: Joining Data from Multiple Tables 1 Module 4 Lab Instructions: Joining Data from Multiple Tables Contents: Exercise 1: Querying Multiple Tables by Using Joins Exercise 2: Applying Joins for Typical Reporting Needs Exercise 3: Combining and Limiting Result Sets 2 4 5 This is a unique copy of the course material identified by code cbbd3d2c-1b8b-4127-8769-5439cb34900e, and provided to you by Holly Stiver. It is illegal to reprint, redistribute, or resell this content. The Licensed Content is licensed "as-is." Microsoft does not support this Licensed Content in any way and Microsoft gives no express warranties, guarantees or conditions. Please report any unauthorized use of this content to [email protected] or by calling +1 800-785-3448. QuickStart Intelligence 2 Lab Instructions: Joining Data from Multiple Tables Lab: Joining Data fr rom Mu ultiple Tables Exercise 1: Querying Multiple Tables by Using Join ns Sc cenario Yo ou are the dat tabase develop per of Adventu ure Works. You u have been as sked by the va arious manage ers to pr repare the following reports s: • • • Ds for those em mployees who are also salesp people. List of employee login ID List of prod ducts produced d by the comp pany, regardles ss of whether those products have reviews s written about them m or not. List of all sa ales persons re egardless of wh hether or not they are assign ned a sales ter rritory. he main tasks for this exercis se are as follow ws: Th 1. . 2. . 3. . Launch SQL L Server Management Studio o. Create and execute an Inner Join. Create and execute a Left t Outer Join an nd a Right Out ter Join. Task 1: Launc ch SQL Serve er Managem ment Studio • • • Start the 27 778A-NY-SQL-01 virtual mac chine, and log on as Administrator with the password of Pa$$w0rd. In Microsof ft® Windows® ® Explorer, bro owse to E:\MO OD04\Labfiles\Starter and run LabSetup.cm md. Start SQL Server Management Studio. Task 2: Create e and execu ute an Inner Join • • Create a qu uery that will re eturn the Logi inID column based on the BusinessEntityID D. Use an Inne er Join to join the HumanResources.Emplo oyee table with h the Sales.Sale esPerson table e. This is a unique copy of the course material identified by code cbbd3d2c-1b8b-4127-8769-5439cb34900e, and provided to you by Holly Stiver. It is illegal to reprint, redistribute, or resell this content. The Licensed Content is licensed "as-is." Microsoft does not support this Licensed Content in any way and Microsoft gives no express warranties, guarantees or conditions. Please report any unauthorized use of this content to [email protected] or by calling +1 800-785-3448. QuickStart Intelligence Lab Instructions: Joining Data from Multiple Tables 3 • Execute the query and review the results. Task 3: Create and execute a Left Outer Join and a Right Outer Join • • • Create a Left Outer Join that uses the Product and ProductReview tables on their ProductID columns to show only those products for which reviews have been written. Create a Right Outer Join that uses the SalesTerritory and SalesPerson tables on their TerritoryID columns to show any territory that has been assigned to a salesperson. Execute the query and review the results. Results: After this exercise, you should have launched SQL Server Management Studio and created and executed an Inner Join. You should have also created and executed a Left Outer Join and a Right Outer Join. This is a unique copy of the course material identified by code cbbd3d2c-1b8b-4127-8769-5439cb34900e, and provided to you by Holly Stiver. It is illegal to reprint, redistribute, or resell this content. The Licensed Content is licensed "as-is." Microsoft does not support this Licensed Content in any way and Microsoft gives no express warranties, guarantees or conditions. Please report any unauthorized use of this content to [email protected] or by calling +1 800-785-3448. QuickStart Intelligence 4 Lab Instructions: Joining Data from Multiple Tables Exercise 2: Applying Joins for Typical Reporting Needs Scenario You have now been asked by the various managers of Adventure Works to prepare the following reports: • • • List of all products that are produced by vendors. List of products that are created by all vendors, eliminating duplicates using the DISTINCT operator. List of product subcategories that have at least two different prices less than $15. The main tasks for this exercise are as follows: 1. 2. 3. Create and execute a query using a Self Join. Create and execute a query using a Self Join and the DISTINCT clause. Create and execute a Non-Equi Join with an equality and a non-equality operator. Task 1: Create and execute a query using a Self Join • • • Create a query that will return a list of all products on the Purchasing.ProductVendor table that are produced by all vendors. Use a Self Join and order the results by ProductID. Execute the query and review the results. Task 2: Create and execute a query using a Self Join and the DISTINCT clause • • • Create a query that will return a list of all products on the Purchasing.ProductVendor table that are produced by vendors. Use a Self Join that uses the DISTINCT clause, and order the results by ProductID. Execute the query and review the results. Task 3: Create and execute a Non-Equi Join with an equality and a non-equality operator • • • Create a query that will return a list of all product subcategories that have at least two different prices less than $15. Use an Inner Join to join the Production.Product table to itself and a non-equality operator on the ListPrice column to select two different prices. Execute the query and review the results. Results: After this exercise, you should have created and executed a query using a self join, created and executed a query using a self join and the DISTINCT clause, and created and executed a non-equi join with both an equality and non-equality operator. This is a unique copy of the course material identified by code cbbd3d2c-1b8b-4127-8769-5439cb34900e, and provided to you by Holly Stiver. It is illegal to reprint, redistribute, or resell this content. The Licensed Content is licensed "as-is." Microsoft does not support this Licensed Content in any way and Microsoft gives no express warranties, guarantees or conditions. Please report any unauthorized use of this content to [email protected] or by calling +1 800-785-3448. QuickStart Intelligence Lab Instructions: Joining Data from Multiple Tables 5 Exercise 3: Combining and Limiting Result Sets Scenario You have been asked by the various managers of Adventure Works to prepare the following reports: • • • • • Combined list of table TestA and table TestB using the UNION ALL operator. List of all product IDs from the Production.Product table that are not on the Production.WorkOrder table. List of all product IDs with distinct values on both the Production.Product table and Production.WorkOrder table. List of the top 15% of products from the Production.Product table, ordered by ProductID. Randomly generated list of 10% of people from the Person.Person table including first name and surname. The main tasks for this exercise are as follows: 1. 2. 3. 4. Combine the result sets of two queries by using the UNION ALL operator. Limit result sets by using the EXCEPT clause with the SELECT statement. Limit result sets by using the INTERSECT clause with the SELECT statement. Limit result sets by using the TOP and TABLESAMPLE operators. Task 1: Combine the result sets of two queries by using the UNION ALL operator • • • Create a query that will return a combined list of all columns on the TestA and TestB tables. Do not limit the results of the query with any operators. Execute the query and review the results. Task 2: Limit result sets by using the EXCEPT clause with the SELECT statement • • Create a query using EXCEPT that lists all product IDs from the Production.Product table that are not on the Production.WorkOrder table. Execute the query and review the results. Task 3: Limit result sets by using the INTERSECT clause with the SELECT statement • • Create a query using INTERSECT that lists all product IDs from the Production.Product table that are not on the Production.WorkOrder table. Execute the query and review the results. Task 4: Limit result sets by using the TOP and TABLESAMPLE operators • • • • Create a query that lists of the top 15% of products from the Production.Product table, ordered by ProductID. Execute the query and review the results. Create a query that randomly generates a list of 10% of people from the Person.Person table including first name and surname. Execute the query and review the results. Results: After this exercise, you should have combined the result sets of two queries by using the UNION ALL operator. You should have also limited result sets by using the EXCEPT and INTERSECT clauses. Finally, you should have limited result sets using the TOP and TABLESAMPLE operators. This is a unique copy of the course material identified by code cbbd3d2c-1b8b-4127-8769-5439cb34900e, and provided to you by Holly Stiver. It is illegal to reprint, redistribute, or resell this content. The Licensed Content is licensed "as-is." Microsoft does not support this Licensed Content in any way and Microsoft gives no express warranties, guarantees or conditions. Please report any unauthorized use of this content to [email protected] or by calling +1 800-785-3448. QuickStart Intelligence 6 Lab Instructions: Joining Data from Multiple Tables Lab Shutdown After you complete the lab, you must shut down the 2778A-NY-SQL-01 virtual machine and discard any changes. This is a unique copy of the course material identified by code cbbd3d2c-1b8b-4127-8769-5439cb34900e, and provided to you by Holly Stiver. It is illegal to reprint, redistribute, or resell this content. The Licensed Content is licensed "as-is." Microsoft does not support this Licensed Content in any way and Microsoft gives no express warranties, guarantees or conditions. Please report any unauthorized use of this content to [email protected] or by calling +1 800-785-3448. QuickStart Intelligence Lab Instructions: Working with Subqueries 1 Module 5 Lab Instructions: Working with Subqueries Contents: Exercise 1: Writing Basic Subqueries Exercise 2: Writing Correlated Subqueries Exercise 3: Comparing Subqueries with Joins and Temporary Tables Exercise 4: Using Common Table Expressions 2 4 5 6 This is a unique copy of the course material identified by code cbbd3d2c-1b8b-4127-8769-5439cb34900e, and provided to you by Holly Stiver. It is illegal to reprint, redistribute, or resell this content. The Licensed Content is licensed "as-is." Microsoft does not support this Licensed Content in any way and Microsoft gives no express warranties, guarantees or conditions. Please report any unauthorized use of this content to [email protected] or by calling +1 800-785-3448. QuickStart Intelligence 2 Lab Instructions: Working with Subqueries Lab: Working g with Subque S eries Exercise 1: Writing Ba asic Subqu ueries Sc cenario Yo ou are a datab base developer r of Adventure e Works. You have been aske ed by the Prod duct Manager to pr repare the following reports s: • • B List that contains prices for Chainring Bolts. w types pro oduced at any time. List of all wheel he main tasks for this exercis se are as follow ws: Th 1. . 2. . 3. . Launch SQL L Server Management Stud dio. Create a Ba asic Subquery. Create a Su ubquery with the EXISTS Key yword. Task 1: Launc ch SQL Serve er Managem ment Studio • • Start the 27 778A-NY-SQL L-01 virtual ma achine, and log g on as Admin nistrator with the password d of Pa$$w0rd. Open SQL Server Manag gement Studi io . Task 2: Create e a basic sub bquery • • Create a subquery to sele ect the name of o any product ts that have a list price equal to the list price of P roduct table. 'Chainring Bolts' on the Production.Pr e query and re eview the results. Execute the Task 3: Create e a subquer ry with the EXISTS Keyw word • Create subq query with the e EXISTS keywo ord that will re eturn all items of the type 'W Wheels' from th he Production n.ProductSub bcategory table. This is a unique copy of the course material identified by code cbbd3d2c-1b8b-4127-8769-5439cb34900e, and provided to you by Holly Stiver. It is illegal to reprint, redistribute, or resell this content. The Licensed Content is licensed "as-is." Microsoft does not support this Licensed Content in any way and Microsoft gives no express warranties, guarantees or conditions. Please report any unauthorized use of this content to [email protected] or by calling +1 800-785-3448. QuickStart Intelligence Lab Instructions: Working with Subqueries 3 • Execute the query and review the results. Results: After this exercise, you should have launched SQL Server Management Studio and created and executed a basic subquery. You should have also created and executed a subquery with the EXISTS keyword. This is a unique copy of the course material identified by code cbbd3d2c-1b8b-4127-8769-5439cb34900e, and provided to you by Holly Stiver. It is illegal to reprint, redistribute, or resell this content. The Licensed Content is licensed "as-is." Microsoft does not support this Licensed Content in any way and Microsoft gives no express warranties, guarantees or conditions. Please report any unauthorized use of this content to [email protected] or by calling +1 800-785-3448. QuickStart Intelligence 4 Lab Instructions: Working with Subqueries Exercise 2: Writing Correlated Subqueries Scenario You have been asked by a Sales Manager to prepare the following reports: • • List of each employee who has received a bonus of over $5000. List of sales where the quantity is less than the average quantity for sales of that product. The main tasks for this exercise are as follows: 1. 2. Create a Correlated Subquery. Create a Correlated Subquery with Comparison Operators. Task 1: Create a correlated subquery • • • Create a correlated subquery that returns the last name and first name of each employee who has received a bonus of over $5000. Join the Person.Person table with the HumanResources.Employee table in order to select the required columns. Execute the query and review the results. Task 2: Create a correlated subquery with comparison operators • • • Create a correlated subquery that returns a list of sales where the quantity of sales is less than the average quantity of sales for that product. Use the ProductID and OrderQty columns on the Sales.SalesOrderDetail table. Execute the query and review the results. Results: After this exercise, you should have created a correlated subquery and also created a correlated subquery with comparison operators. This is a unique copy of the course material identified by code cbbd3d2c-1b8b-4127-8769-5439cb34900e, and provided to you by Holly Stiver. It is illegal to reprint, redistribute, or resell this content. The Licensed Content is licensed "as-is." Microsoft does not support this Licensed Content in any way and Microsoft gives no express warranties, guarantees or conditions. Please report any unauthorized use of this content to [email protected] or by calling +1 800-785-3448. QuickStart Intelligence Lab Instructions: Working with Subqueries 5 Exercise 3: Comparing Subqueries with Joins and Temporary Tables Scenario You have also been asked by the Senior Database Administrator to prepare several reports with system performance in mind: • • List that contains prices for chainring bolts using a join instead of a subquery. Test report using temporary tables. The main tasks for this exercise are as follows: 1. 2. Create a Subquery and a Join that Produce the same Result Set. Create a Temporary Table. Task 1: Create a subquery and a join that produce the same result set • • Create a subquery to select the name of any products that have a list price equal to the list price of 'Chainring Bolts' on the Production.Product table. Create and execute a join to select the name of any products that have a list price equal to the list price of 'Chainring Bolts' on the Production.Product table. Task 2: Create a temporary table • • • Create a query that creates a temporary table with a primary key value of 1. In the same query, select all columns from the temporary table. Execute the query and review the results. Results: After this exercise you should have created a subquery and a join that produced the same result set. You should have also created a temporary table. This is a unique copy of the course material identified by code cbbd3d2c-1b8b-4127-8769-5439cb34900e, and provided to you by Holly Stiver. It is illegal to reprint, redistribute, or resell this content. The Licensed Content is licensed "as-is." Microsoft does not support this Licensed Content in any way and Microsoft gives no express warranties, guarantees or conditions. Please report any unauthorized use of this content to [email protected] or by calling +1 800-785-3448. QuickStart Intelligence 6 Lab Instructions: Working with Subqueries Exercise 4: Using Common Table Expressions Scenario Finally, you have been asked by the Director of Sales to prepare the following reports: • • List of the total number of sales orders and most recent sales order date for each salesperson. Hierarchical list of employees including manager ID, employee ID, and employee title limited to the Research and Development Group. The main tasks for this exercise are as follows: 1. 2. Create a Common Table Expression. Create a Recursive Query using a Common Table Expression. Task 1: Create a Common Table Expression (CTE) • • Create a CTE named Sales_CTE that returns a list of the total number of sales orders and most recent sales order date for each salesperson. Refer to the sample file E:\MOD05\Labfiles\Solution\Sales_CTE.sql as needed. Task 2: Create a recursive query using a CTE • Create a recursive query using a CTE that returns a hierarchical list of product assemblies and components required to build the bicycle for ProductAssemblyID = 800. Use the AssemblyID, PerAssemblyQty, and EndDate columns of the Production.BillOfMaterials table. Also, create a column called ComponentLevel that has a value of 0 for the anchor member, and increases by 1 for each level of the hierarchy. Refer to the sample file E:\MOD05\Labfiles\Solution\ProductBOM.sql as needed. Results: After this exercise, you should have created a common table expression. You should have also created a recursive query using a common table expression. • This is a unique copy of the course material identified by code cbbd3d2c-1b8b-4127-8769-5439cb34900e, and provided to you by Holly Stiver. It is illegal to reprint, redistribute, or resell this content. The Licensed Content is licensed "as-is." Microsoft does not support this Licensed Content in any way and Microsoft gives no express warranties, guarantees or conditions. Please report any unauthorized use of this content to [email protected] or by calling +1 800-785-3448. QuickStart Intelligence Lab Instructions: Working with Subqueries 7 Lab Shutdown After you complete the lab, you must shut down the 2778A-NY-SQL-01 virtual machine and discard any changes. This is a unique copy of the course material identified by code cbbd3d2c-1b8b-4127-8769-5439cb34900e, and provided to you by Holly Stiver. It is illegal to reprint, redistribute, or resell this content. The Licensed Content is licensed "as-is." Microsoft does not support this Licensed Content in any way and Microsoft gives no express warranties, guarantees or conditions. Please report any unauthorized use of this content to [email protected] or by calling +1 800-785-3448. QuickStart Intelligence Lab Instructions: Modifying Data in Tables 1 Module 6 Lab Instructions: Modifying Data in Tables Contents: Exercise 1: Inserting Data into Tables Exercise 2: Deleting Data from Tables Exercise 3: Updating Data in Tables Exercise 4: Working with Transactions 3 5 6 7 This is a unique copy of the course material identified by code cbbd3d2c-1b8b-4127-8769-5439cb34900e, and provided to you by Holly Stiver. It is illegal to reprint, redistribute, or resell this content. The Licensed Content is licensed "as-is." Microsoft does not support this Licensed Content in any way and Microsoft gives no express warranties, guarantees or conditions. Please report any unauthorized use of this content to [email protected] or by calling +1 800-785-3448. QuickStart Intelligence 2 Lab Instructions: Modifyi ing Data in Tables Lab: Modifyi ing Data This is a unique copy of the course material identified by code cbbd3d2c-1b8b-4127-8769-5439cb34900e, and provided to you by Holly Stiver. It is illegal to reprint, redistribute, or resell this content. The Licensed Content is licensed "as-is." Microsoft does not support this Licensed Content in any way and Microsoft gives no express warranties, guarantees or conditions. Please report any unauthorized use of this content to [email protected] or by calling +1 800-785-3448. QuickStart Intelligence Lab Instructions: Modifying Data in Tables 3 Exercise 1: Inserting Data into Tables Scenario You are the database developer at Adventure Works. You have been asked by the senior database administrator to: • Include new product information about an existing product: UnitMeasureCode F2 • Name Square Feet ModifiedDate GETDATE() Include information about new departments and their managers: PhoneNumberTypeI BusinessEntityID PhoneNumber D ModifiedDate 1705 1706 864-555-2101 712-555-0118 3 1 GETDATE() GETDATE() • Include information about a faulty product: Name Operator error ModifiedDate GETDATE() The main tasks for this exercise are as follows: 1. 2. 3. 4. Launch SQL Server Management Studio. Create an INSERT Statement that Adds Values to a Table. Create an INSERT Statement Using the INTO Syntax. Create an INSERT Statement Using the OUTPUT Syntax. Task 1: Launch SQL Server Management Studio • • Start the 2778A-NY-SQL-01 virtual machine, and log on as Administrator with the password of Pa$$w0rd. Open SQL Server Management Studio. Task: 2 Create an INSERT statement that adds values to a table • • • Create an INSERT statement that will include new product information about an existing product into the Production.Unit measure table. Use the information provided in the scenario above. Execute the query and review the results. Task 3: Create an INSERT statement that adds multiple rows to a table • • • Create an INSERT statement that will include information about new departments and their managers into the Person.PersonPhone table. Use the information provided in the scenario above. Execute the query and review the results. This is a unique copy of the course material identified by code cbbd3d2c-1b8b-4127-8769-5439cb34900e, and provided to you by Holly Stiver. It is illegal to reprint, redistribute, or resell this content. The Licensed Content is licensed "as-is." Microsoft does not support this Licensed Content in any way and Microsoft gives no express warranties, guarantees or conditions. Please report any unauthorized use of this content to [email protected] or by calling +1 800-785-3448. QuickStart Intelligence 4 Lab Instructions: Modifying Data in Tables Task 4: Create an INSERT statement using the OUTPUT syntax • Create an INSERT statement that will include information about a faulty product into a new table created with the following query: DECLARE @MyTableVar table( ScrapReasonID smallint, Name varchar(50), ModifiedDate datetime); • • Insert the ScrapreasonID, Name, and ModifiedDate columns and rows from the Production.ScrapReason table into @MyTableVar using the INSERT and OUTPUT statements. Display the result set of the table variable and display the result set of the table. Results: After this exercise, you should have launched SQL Server Management Studio, created and executed an INSERT statement that adds values to a table, created and executed an INSERT statement using the INTO syntax, and created and executed an INSERT statement using the OUTPUT syntax. This is a unique copy of the course material identified by code cbbd3d2c-1b8b-4127-8769-5439cb34900e, and provided to you by Holly Stiver. It is illegal to reprint, redistribute, or resell this content. The Licensed Content is licensed "as-is." Microsoft does not support this Licensed Content in any way and Microsoft gives no express warranties, guarantees or conditions. Please report any unauthorized use of this content to [email protected] or by calling +1 800-785-3448. QuickStart Intelligence Lab Instructions: Modifying Data in Tables 5 Exercise 2: Deleting Data from Tables Scenario The sales manager has asked you to make the following changes to the database: • Delete all instances of product cost history where standard cost is greater than $1000.00. The director of sales has now asked you to make the following updates to the database: • • • Remove rows from the sales person quota history based on the year-to-date sales stored in the SalesPerson table, where year-to-date sales are greater-than $1,000,000.00. Delete the Sales.ShoppingCartItem table and display the results of the deletion. Truncate the Production.TransactionHistory table and display the results. The main tasks for this exercise are as follows: 1. 2. 3. 4. Create a DELETE Statement Using the WHERE Syntax. Create a DELETE Statement Using the FROM Syntax. Create a DELETE Statement Using the OUTPUT Syntax. Create a TRUNCATE TABLE Statement. Task 1: Create a DELETE statement using the WHERE syntax • • Create a DELETE statement that deletes all instances of product cost history where standard cost is greater than $1000.00. Execute the query and review the results. Task 2: Create a DELETE statement using the FROM syntax • Create a DELETE statement using the FROM syntax that removes rows from the sales person quota history based on the year-to-date sales stored in the SalesPerson table, where year-to-date sales are greater-than $1,000,000.00. Execute the query and review the results. • Task 3: Create a DELETE statement using the OUTPUT syntax • • Create a DELETE statement using the OUTPUT syntax that deletes the Sales.ShoppingCartItem table and display the results of the deletion. Execute the query and review the results. Task 4: Create a TRUNCATE TABLE statement • Create a TRUNCATE TABLE statement that truncates the Production.TransactionHistory table, displays the count of table rows before the statement is executed, and also displays the count of table rows after the statement is executed. Execute the query and review the results. Results: After this exercise, you should have created and executed a DELETE statement using the WHERE syntax and created and executed a DELETE statement using the FROM syntax. You should have also created and executed a DELETE statement using the OUTPUT syntax and then created and executed a TRUNCATE TABLE statement. • This is a unique copy of the course material identified by code cbbd3d2c-1b8b-4127-8769-5439cb34900e, and provided to you by Holly Stiver. It is illegal to reprint, redistribute, or resell this content. The Licensed Content is licensed "as-is." Microsoft does not support this Licensed Content in any way and Microsoft gives no express warranties, guarantees or conditions. Please report any unauthorized use of this content to [email protected] or by calling +1 800-785-3448. QuickStart Intelligence 6 Lab Instructions: Modifying Data in Tables Exercise 3: Updating Data in Tables Scenario A vice president at the AdventureWorks company has now asked you to make the following updates to the database: • • • • Update the Sales.SalesPerson table to set the bonus amount to $6000, the commission percent to .10, and the sales quota to NULL. Change all instances of Road-250 products on the Production.Product table with the color Red to the color Metallic Red. Modify the year-to-date sales of the Sales.SalesPerson table to reflect the most recent sales recorded in the Sales.SalesOrderHeader table. Update the VacationHours column in the HumanResources.Employee table by 25 percent for the first 10 rows. The main tasks for this exercise are as follows: 1. 2. 3. 4. Create an UPDATE Statement Using the SET Syntax. Create an UPDATE Statement Using the WHERE Syntax. Create an UPDATE Statement Using the FROM Syntax. Create an UPDATE Statement Using the OUTPUT Syntax. Task 1: Create an UPDATE statement using the SET syntax • • Create an UPDATE statement using the SET syntax that updates the Sales.SalesPerson table to set the bonus amount to $6000, the commission percent to .10, and the sales quota to NULL. Execute the query and review the results. Task 2: Create an UPDATE statement using the WHERE syntax • • Create an UPDATE statement using the WHERE syntax that changes all instances of Road-250 products on the Production.Product table with the color Red to the color Metallic Red. Execute the query and review the results. Task 3: Create an UPDATE statement using the FROM syntax • Create an UPDATE statement using the FROM syntax that modifies the year-to-date sales of the Sales.SalesPerson table to reflect the most recent sales recorded in the Sales.SalesOrderHeader table. Execute the query and review the results. • Task 4: Create an UPDATE statement using the OUTPUT syntax • • Create an UPDATE statement using the OUTPUT syntax that updates the VacationHours column in the HumanResources.Employee table by 25 percent for the first 10 rows. Execute the query and review the results. Results: After this exercise, you should have created and executed an UPDATE statement using the SET syntax, created and executed an UPDATE statement using the WHERE IN (SELECT) syntax, and created and executed an UPDATE statement using the FROM syntax. You should have also created and executed an UPDATE statement using the OUTPUT syntax. This is a unique copy of the course material identified by code cbbd3d2c-1b8b-4127-8769-5439cb34900e, and provided to you by Holly Stiver. It is illegal to reprint, redistribute, or resell this content. The Licensed Content is licensed "as-is." Microsoft does not support this Licensed Content in any way and Microsoft gives no express warranties, guarantees or conditions. Please report any unauthorized use of this content to [email protected] or by calling +1 800-785-3448. QuickStart Intelligence Lab Instructions: Modifying Data in Tables 7 Exercise 4: Working with Transactions Scenario Finally, the senior database administrator has asked you to implement the following transactions on the database: • • Create and commit a transaction that will delete JobCandidateID 13 from the HumanResources.JobCandidate table. Create and commit a repeatable read transaction that will return all rows from the HumanResources.EmployeePayHistory table and all rows from the HumanResources.Department table. The main tasks for this exercise are as follows: 1. 2. Create a Simple Transaction. Set a Transaction Isolation Level. Task 1: Create a simple transaction • • Create and commit a transaction that will delete JobCandidateID 13 from the HumanResources.JobCandidate table. Execute the query and review the results. Task 2: Set a Transaction Isolation Level • Create and commit a repeatable read transaction that will return all rows from the HumanResources.EmployeePayHistory table and all rows from the HumanResources.Department table. Execute the query and review the results. Results: After this exercise, you should have created a simple transaction. You should have also set the transaction isolation level for a new transaction. • Lab Shutdown After you complete the lab, you must shut down the 2778A-NY-SQL-01 virtual machine and discard any changes. This is a unique copy of the course material identified by code cbbd3d2c-1b8b-4127-8769-5439cb34900e, and provided to you by Holly Stiver. It is illegal to reprint, redistribute, or resell this content. The Licensed Content is licensed "as-is." Microsoft does not support this Licensed Content in any way and Microsoft gives no express warranties, guarantees or conditions. Please report any unauthorized use of this content to [email protected] or by calling +1 800-785-3448. QuickStart Intelligence Lab Instructions: Querying Metadata, XML, and Full-Text Indexes 1 Module 7 Lab Instructions: Querying Metadata, XML, and Full-Text Indexes Contents: Exercise 1: Querying Metadata Exercise 2: Querying XML Data Exercise 3: Creating and Querying Full-Text Indexes 2 4 5 This is a unique copy of the course material identified by code cbbd3d2c-1b8b-4127-8769-5439cb34900e, and provided to you by Holly Stiver. It is illegal to reprint, redistribute, or resell this content. The Licensed Content is licensed "as-is." Microsoft does not support this Licensed Content in any way and Microsoft gives no express warranties, guarantees or conditions. Please report any unauthorized use of this content to [email protected] or by calling +1 800-785-3448. QuickStart Intelligence 2 Lab Instructions: Queryin ng Metadata, XML, and Full-Text Indexes Lab: Que erying Metada ata, XML L, and Full-Tex xt Index xes Exercise 1: Querying Metadata Sc cenario Yo ou are the dat tabase adminis strator at Adve enture Works. The company requires that you perform an a an nnual audit of the database systems and their efficiency. As part of the e audit, you ne eed to verify the m metadata eleme ents of the AdventureWorks s database. Th he main tasks for this exercis se are as follow ws: 1. . 2. . 3. . 4. . 5. . Launch SQL L Server Management Stud dio. Query meta adata using sy ystem catalog views v to get a list of column n names for the e Person table e. Query meta adata using th he information schema to ge et column information for the Address table. Query meta adata using dy ynamic manag gement views to t get informa ation about rec cently execute ed commands. Query meta adata using sy ystem stored procedures to get g column inf formation for the t Employee table. Task 1: Launc ch SQL Serve er Managem ment Studio • • Start the 27 778A-NY-SQL L-01 virtual ma achine, and log g on as Admin nistrator with the password d of Pa$$w0rd. . Open SQL Server Manag gement Studi io. Task 2: Query y metadata using u System m Catalog Views V • • • Create a qu uery that will re eturn the column name from m sys.columns. Use a Join to t join the sys. .columns view with the sys.ta ables view on object_id. Use e a WHERE clau use to restrict the result set to th he Person tab ble. e query and re eview the results. Execute the This is a unique copy of the course material identified by code cbbd3d2c-1b8b-4127-8769-5439cb34900e, and provided to you by Holly Stiver. It is illegal to reprint, redistribute, or resell this content. The Licensed Content is licensed "as-is." Microsoft does not support this Licensed Content in any way and Microsoft gives no express warranties, guarantees or conditions. Please report any unauthorized use of this content to [email protected] or by calling +1 800-785-3448. QuickStart Intelligence Lab Instructions: Querying Metadata, XML, and Full-Text Indexes 3 Task 3: Query metadata using the Information Schema • • Create a query that will return all values from the INFORMATION_SCHEMA.Columns view for the Address table. Execute the query and review the results. Task 4: Query metadata using Dynamic Management Views • • • Create a query that will return all values from the sys.dm_exec_requests view. Refine the query to group the results by the command name, count the number of occurrences of each, and return the average total elapsed time of each command. Execute the query and review the results. Task 5: Query metadata using System Stored Procedures • • Create a query that executes the sp_columns stored procedure and send 'Employee' to the @table_name parameter and 'HumanResources' to the @table_owner parameter. Execute the query and review the results. Results: After this exercise, you should have launched SQL Server Management Studio and queried metadata using system catalog views, the information schema, dynamic management views, and system stored procedures. This is a unique copy of the course material identified by code cbbd3d2c-1b8b-4127-8769-5439cb34900e, and provided to you by Holly Stiver. It is illegal to reprint, redistribute, or resell this content. The Licensed Content is licensed "as-is." Microsoft does not support this Licensed Content in any way and Microsoft gives no express warranties, guarantees or conditions. Please report any unauthorized use of this content to [email protected] or by calling +1 800-785-3448. QuickStart Intelligence 4 Lab Instructions: Querying Metadata, XML, and Full-Text Indexes Exercise 2: Querying XML Data Scenario One important task includes the retrieving of the orders data in an XML format from an external system. To do this, you need to query the required orders data and retrieve it in an XML format. The main tasks for this exercise are as follows: 1. 2. Create XML output using For XML. Query XML data using OpenXML. Task 1: Create XML output using For XML • • • Create a query that will return the CustomerID, SalesOrderID, and Status columns from the Sales.SalesOrderHeader table. Refine query by ordering by CustomerID and specify the output to be in XML by using For XML Auto. Execute the query and review the results. Task 2: Query XML data using OpenXML • • • Open the predefined query at E:\Mod07\Labfiles\Exercise2Task2. Add a call to sp_xml_preparedocument to prepare the XML for the query. Execute the query and review the results. Results: After this exercise, you should have created a query of relational data and produced the output in XML format. You should have also created a query that converts data in XML format to a relational format. This is a unique copy of the course material identified by code cbbd3d2c-1b8b-4127-8769-5439cb34900e, and provided to you by Holly Stiver. It is illegal to reprint, redistribute, or resell this content. The Licensed Content is licensed "as-is." Microsoft does not support this Licensed Content in any way and Microsoft gives no express warranties, guarantees or conditions. Please report any unauthorized use of this content to [email protected] or by calling +1 800-785-3448. QuickStart Intelligence Lab Instructions: Querying Metadata, XML, and Full-Text Indexes 5 Exercise 3: Creating and Querying Full-Text Indexes Scenario The company requires your help to create search functionality for products. The web site development team is developing an enhanced search feature for the online catalog. To support that functionality, you need to implement a full-text catalog for the product descriptions. The main tasks for this exercise are as follows: 1. 2. 3. Create a full-text index for the product description column. Query the full-text index using FREETEXT. Query the full-text index using CONTAINS. Task 1: Create a full-text index • • • • Use the New Full Text Catalog dialog in SQL Server Management Studio to create a new full-text index named ProductDescriptionCatalog. Edit the properties of ProductDescriptionCatalog to include the Production.ProductDescription.Description column. Set a schedule for the catalog to be populated every hour. Rebuild the catalog. Task 2: Query the full-text index using FREETEXT • • Create a query that lists all products from Production.ProductDescription where the Description column contains a form of the word "lightest". Execute the query and review the results. Task 3: Query the full-text index using CONTAINS • • Create a query that lists all products from Production.ProductDescription where the Description column contains a form of the word "lightest" which is near the word "best". Execute the query and review the results. Results: After this exercise, you should have created a new full-text catalog and queried that catalog using both the FREETEXT and CONTAINS predicates. Lab Shutdown After you complete the lab, you must shut down the 2778A-NY-SQL-01 virtual machine and discard any changes. This is a unique copy of the course material identified by code cbbd3d2c-1b8b-4127-8769-5439cb34900e, and provided to you by Holly Stiver. It is illegal to reprint, redistribute, or resell this content. The Licensed Content is licensed "as-is." Microsoft does not support this Licensed Content in any way and Microsoft gives no express warranties, guarantees or conditions. Please report any unauthorized use of this content to [email protected] or by calling +1 800-785-3448. QuickStart Intelligence Lab Instructions: Using Programming Objects for Data Retrieval 1 Module 8 Lab Instructions: Using Programming Objects for Data Retrieval Contents: Exercise 1: Creating Views Exercise 2: Creating User-Defined Functions Exercise 3: Creating Stored Procedures Exercise 4: Writing Distributed Queries 2 3 4 5 This is a unique copy of the course material identified by code cbbd3d2c-1b8b-4127-8769-5439cb34900e, and provided to you by Holly Stiver. It is illegal to reprint, redistribute, or resell this content. The Licensed Content is licensed "as-is." Microsoft does not support this Licensed Content in any way and Microsoft gives no express warranties, guarantees or conditions. Please report any unauthorized use of this content to [email protected] or by calling +1 800-785-3448. QuickStart Intelligence 2 Lab Instructions: Using Programming Objects for Data Retrieval Lab: Using Prog grammi ing Objects for r Data Retrieva R al Exercise 1: Creating Views V Sc cenario As the database e administrator at Adventure e Works, you need n to create views which present p a simp plified ve ersion of table es with only the e relevant colu umns from the e AdventureWo orks database. . The Person table co ontains a lot of information but in most ca ases only the name n is require ed. In this exer rcise, you will create c a view that will re eturn only the name and ID elements e from m that table. Th he main task fo or this exercise e are as follow ws: • Create a vie ew to select on nly the name and a ID column ns from the Pe erson table. Task 1: Create e a view to select s only the t name an nd ID columns from the Person tabl le • • • In SQL Serv ver Managem ment Studio, create c a new vi iew. In the view, , select BusinessEntityID, Firs stName, MiddleName, and LastName L from m the Person.Person table. ry it. Create the view and quer Results: After this exercise, you y should ha ave created a view v and queried it using a T-SQL T query. This is a unique copy of the course material identified by code cbbd3d2c-1b8b-4127-8769-5439cb34900e, and provided to you by Holly Stiver. It is illegal to reprint, redistribute, or resell this content. The Licensed Content is licensed "as-is." Microsoft does not support this Licensed Content in any way and Microsoft gives no express warranties, guarantees or conditions. Please report any unauthorized use of this content to [email protected] or by calling +1 800-785-3448. QuickStart Intelligence Lab Instructions: Using Programming Objects for Data Retrieval 3 Exercise 2: Creating User-Defined Functions Scenario You are the database administrator at Adventure Works. You need to create several user-defined functions to meet the custom requirements of your organization. You will create a scalar user-defined function that removes the time part from the datetime() object and present only the date related information. Yet another user-defined function retrieves details about purchase orders of a particular vendor when supplied with a VendorID. The main tasks for this exercise are as follows: 1. 2. 3. Launch SQL Server Management Studio. Create a scalar valued user-defined function that removes the time part from a datetime value. Create a table valued user-defined function that retrieves purchase orders by VendorID. Task 1: Launch SQL Server Management Studio • • Start the 2778A-NY-SQL-01 virtual machine, and log on as Administrator with the password of Pa$$w0rd. Open SQL Server Management Studio. Task 2: Create a scalar valued user-defined function • • In the AdventureWorks2008 database, create a new scalar valued user-defined function that returns varchar(10) and accepts a datetime input parameter. In the body of the function, perform the logic to remove the time portion from the datetime parameter value. For information on datetime functions, refer to SQL Server® Books Online and search for DATEPART. Create the function and test it by passing the current date to it in a T-SQL query. The current datetime value can be obtained by using the GETDATE() function. • Task 3: Create a table valued user-defined function • • • In the AdventureWorks2008 database, create a new table valued user-defined function that accepts an int input parameter as the VendorID. In the body of the function, query the Purchasing.PurchaseOrderHeader table using the VendorID in the parameter. Create the function and test it by passing the VendorID 1624 to it in a T-SQL query. Results: After this exercise, you should have launched SQL Server Management Studio and created and tested both a scalar valued and table valued user-defined function. This is a unique copy of the course material identified by code cbbd3d2c-1b8b-4127-8769-5439cb34900e, and provided to you by Holly Stiver. It is illegal to reprint, redistribute, or resell this content. The Licensed Content is licensed "as-is." Microsoft does not support this Licensed Content in any way and Microsoft gives no express warranties, guarantees or conditions. Please report any unauthorized use of this content to [email protected] or by calling +1 800-785-3448. QuickStart Intelligence 4 Lab Instructions: Using Programming Objects for Data Retrieval Exercise 3: Creating Stored Procedures Scenario As the database administrator at Adventure Works, you need to create stored procedures which perform common tasks. The Person table is queried often to retrieve a person’s name using the ID column. In this exercise, you will create a stored procedure that will retrieve a person's name using the ID. The main tasks for this exercise are as follows: 1. Create a stored procedure to select the name columns from the Person table for a given ID. Task 1: Create a stored procedure to select the name columns from the Person table for a given ID • • • • In SQL Server Management Studio, create a new stored procedure. Accept an integer PersonID value as an input parameter. In the stored procedure, select FirstName, MiddleName, and LastName from the view created in Exercise 2. Create the stored procedure. Results: After this exercise, you should have created a stored procedure that queries a view. This is a unique copy of the course material identified by code cbbd3d2c-1b8b-4127-8769-5439cb34900e, and provided to you by Holly Stiver. It is illegal to reprint, redistribute, or resell this content. The Licensed Content is licensed "as-is." Microsoft does not support this Licensed Content in any way and Microsoft gives no express warranties, guarantees or conditions. Please report any unauthorized use of this content to [email protected] or by calling +1 800-785-3448. QuickStart Intelligence Lab Instructions: Using Programming Objects for Data Retrieval 5 Exercise 4: Writing Distributed Queries Scenario The Adventure Works sales system produces a monthly sales summary report in Microsoft® Office Excel® format. This Excel file is placed on your database server every time it is created. You will execute an ad hoc distributed query against the data in that spreadsheet. Finally, you will create a linked server to that spreadsheet and query the linked server. The main tasks for this exercise are as follows: 1. 2. 3. 4. Enable distributed queries on the server. Create and execute an ad hoc distributed query against the spreadsheet. Create a linked server to the spreadsheet. Query the linked spreadsheet. Task 1: Enabling distributed queries • Execute the following statements: sp_configure 'show advanced options', 1 reconfigure sp_configure 'Ad Hoc Distributed Queries', 1 reconfigure. Task 2: Executing an ad hoc distributed query against a Microsoft Office Excel spreadsheet • Create a query that uses the OPENROWSET function with the following parameters: 'Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=E:\Mod08\Labfiles\SalesSummary2008.xls', 'SELECT Country, TotalSales FROM [Sheet1$]' Task 3: Create a linked server to a Microsoft Office Excel spreadsheet • Execute the sp_addlinkedserver system stored procedure with the following parameters: 'SalesSummary', 'Jet 4.0', 'Microsoft.Jet.OLEDB.4.0', 'E:\Mod08\Labfiles\SalesSummary2008.xls', NULL, 'Excel 8.0' Task 4: Query the linked Microsoft Office Excel spreadsheet • • Create a query for the 'Sheet1$' table of the 'SalesSummary' linked server using a four part name. Execute the query and review the results. Results: After this exercise, you should have executed an ad hoc distributed query and created and queried a linked server. This is a unique copy of the course material identified by code cbbd3d2c-1b8b-4127-8769-5439cb34900e, and provided to you by Holly Stiver. It is illegal to reprint, redistribute, or resell this content. The Licensed Content is licensed "as-is." Microsoft does not support this Licensed Content in any way and Microsoft gives no express warranties, guarantees or conditions. Please report any unauthorized use of this content to [email protected] or by calling +1 800-785-3448. QuickStart Intelligence 6 Lab Instructions: Using Programming Objects for Data Retrieval Lab Shutdown After you complete the lab, you must shut down the 2778A-NY-SQL-01 virtual machine and discard any changes. This is a unique copy of the course material identified by code cbbd3d2c-1b8b-4127-8769-5439cb34900e, and provided to you by Holly Stiver. It is illegal to reprint, redistribute, or resell this content. The Licensed Content is licensed "as-is." Microsoft does not support this Licensed Content in any way and Microsoft gives no express warranties, guarantees or conditions. Please report any unauthorized use of this content to [email protected] or by calling +1 800-785-3448. QuickStart Intelligence Lab Instructions: Using Advanced Techniques 1 Module 9 Lab Instructions: Using Advanced Techniques Contents: Exercise 1: Using Execution Plans Exercise 2: Converting Data Types Exercise 3: Implementing a Hierarchy Exercise 4: Using Cursors and Set-Based Queries 2 4 6 7 This is a unique copy of the course material identified by code cbbd3d2c-1b8b-4127-8769-5439cb34900e, and provided to you by Holly Stiver. It is illegal to reprint, redistribute, or resell this content. The Licensed Content is licensed "as-is." Microsoft does not support this Licensed Content in any way and Microsoft gives no express warranties, guarantees or conditions. Please report any unauthorized use of this content to [email protected] or by calling +1 800-785-3448. QuickStart Intelligence 2 Lab Instructions: Using Advanced Techniques s Lab 9: Using g Advan nced Te echniqu ues Exercise 1: Using Exe ecution Pla ans Sc cenario Yo ou are a Datab base Administr rator at Adven nture Works. As A part of an ef ffort to analyze e database pe erformance, th he Senior Data abase Administ trator has aske ed you to beco ome familiar with w query exec cution pl lans. In order to t do this, you u need to know w how to view estimated and d actual execution plans, as well w as sa ave execution plans and view w them in XML L format. Th he main tasks for this exercis se are as follow ws: 1. . 2. . 3. . 4. . Start the 27 778A-NY-SQL-01 virtual mac chine and log on as Adminis strator. View an est timated execution plan. View an act tual execution plan. Save an exe ecution plan. Task 1: Start the t 2778A-N NY-SQL-01 virtual mach hine and log g on as Administrator • Start NY-SQ QL-01 and log g on as Admin nistrator with a password of f Pa$$w0rd. Task 2: View an estimate ed execution n plan • • • Use SQL Se erver Manage ement Studio to open the fi ile E:\Mod09\ \Labfiles\Star rter\Exercise0 01\Order-Prod ductID.sql. Display the e Estimated Ex xecution Plan n. Review the components of o the executio on plan. Task 3: View an actual ex xecution plan • Set the que ery to include the t Actual Exe ecution Plan. This is a unique copy of the course material identified by code cbbd3d2c-1b8b-4127-8769-5439cb34900e, and provided to you by Holly Stiver. It is illegal to reprint, redistribute, or resell this content. The Licensed Content is licensed "as-is." Microsoft does not support this Licensed Content in any way and Microsoft gives no express warranties, guarantees or conditions. Please report any unauthorized use of this content to [email protected] or by calling +1 800-785-3448. QuickStart Intelligence Lab Instructions: Using Advanced Techniques 3 • • Run the query. Review the components of the execution plan. Task 4: Save an execution plan • • Save the execution plan as a .sqlplan file to E:\Mod09\Labfiles\Starter\Exercise01\OrderProductID.sqlplan. View the XML code for the execution plan and save it to E:\Mod09\Labfiles\Starter\Exercise01\Order-ProductID.xml. Results: After this exercise, you should have viewed estimated and actual execution plans for queries, and saved the execution plans to files. This is a unique copy of the course material identified by code cbbd3d2c-1b8b-4127-8769-5439cb34900e, and provided to you by Holly Stiver. It is illegal to reprint, redistribute, or resell this content. The Licensed Content is licensed "as-is." Microsoft does not support this Licensed Content in any way and Microsoft gives no express warranties, guarantees or conditions. Please report any unauthorized use of this content to [email protected] or by calling +1 800-785-3448. QuickStart Intelligence 4 Lab Instructions: Using Advanced Techniques Exercise 2: Converting Data Types Scenario Some application queries have been producing unexpected results. The Senior Database Administrator suspects that some of the problems may be due to incorrect data type conversion. The Senior DBA has asked you to spend some time learning about data type conversion so that you can help resolve these problems. The main tasks for this exercise are as follows: 1. 2. Use implicit conversions. Use explicit conversions. Task 1: Use implicit conversions • • Use SQL Server Management Studio to look up the data type for the SalariedFlag column in the HumanResources.Employee table in the AdventureWorks2008 database. Create a new query on the AdventureWorks2008 database with the following code: SELECT NationalIDNumber, LoginID, SalariedFlag FROM HumanResources.Employee WHERE SalariedFlag = 'False' • • • Review the Results pane. SQL Server automatically converted "False" to a data type of bit to compare it to the SalariedFlag column. Look up the data types for the ListPrice and StandardCost columns in the Production.Product table. Create a new query on the AdventureWorks2008 database with the following code: DECLARE @NewListPrice nvarchar(10) = '39.99' DECLARE @NewStandardCost float = 12.45 UPDATE Production.Product SET ListPrice = @NewListPrice, StandardCost = @NewStandardCost WHERE ProductID = '2' • • Run the query. Create a new query on the AdventureWorks2008 database with the following code: SELECT Name, StandardCost, ListPrice from Production.Product WHERE ProductID = '2' • • Run the query. In the results pane, notice that the ListPrice and StandardCost columns have been updated. SQL Server automatically converted the nvarchar and float types in the update query to the money data type used by the columns. Open the file E:\Mod09\Labfiles\Starter\Exercise02\UpdateDemographics.sql. Notice that the variable @NewDemographics is an nvarchar data type and contains a valid XML string. Run the query. Create a new query on the AdventureWorks2008 database with the following code: • • • • This is a unique copy of the course material identified by code cbbd3d2c-1b8b-4127-8769-5439cb34900e, and provided to you by Holly Stiver. It is illegal to reprint, redistribute, or resell this content. The Licensed Content is licensed "as-is." Microsoft does not support this Licensed Content in any way and Microsoft gives no express warranties, guarantees or conditions. Please report any unauthorized use of this content to [email protected] or by calling +1 800-785-3448. QuickStart Intelligence Lab Instructions: Using Advanced Techniques 5 SELECT FirstName, MiddleName, LastName, Demographics FROM Person.Person WHERE BusinessEntityID = 15 • • Run the query. In the Results pane, click the link in the Demographics column. SQL Server converted the nvarchar string to the xml data type automatically because the XML was valid. Task 2: Use explicit conversions • • • Open the file E:\Mod09\Labfiles\Starter\Exercise02\EmployeePayRate.sql. Run the query and take note of the error message. Change the line of the query that begins with SELECT, so that it reads as follows: SELECT (p.FirstName + ' ' + p.LastName + ' ' + CONVERT(nvarchar, s.Rate)) • • • • • Run the query. In the Results pane, there is a single column with the employee name and pay rate combined. Open the file E:\Mod09\Labfiles\Starter\Exercise02\PayRateLastChange.sql. Run the query. Review the results of the query. Notice that the last column does not show both the pay rate and date. In the query pane, modify the SELECT statement so that it begins as follows: SELECT p.FirstName, p.LastName, (CAST(s.Rate AS nvarchar) + ' ' + s.RateChangeDate) • • Run the query, and take note of the error message. Modify the SELECT statement again, so that it reads as follows: SELECT p.FirstName, p.LastName, (CAST(s.Rate AS nvarchar) + ' ' + CAST(s.RateChangeDate as nvarchar)) • Run the query. Notice that the query now runs and the last column displays the correct information. Results: After this exercise, you should have verified that SQL Server automatically performs implicit conversions, and you should have also successfully run queries that performed explicit data type conversions. This is a unique copy of the course material identified by code cbbd3d2c-1b8b-4127-8769-5439cb34900e, and provided to you by Holly Stiver. It is illegal to reprint, redistribute, or resell this content. The Licensed Content is licensed "as-is." Microsoft does not support this Licensed Content in any way and Microsoft gives no express warranties, guarantees or conditions. Please report any unauthorized use of this content to [email protected] or by calling +1 800-785-3448. QuickStart Intelligence 6 Lab Instructions: Using Advanced Techniques Exercise 3: Implementing a Hierarchy Scenario The Sales department at Adventure Works is reorganizing its sales territories to reflect the organization’s hierarchy. In order to represent the hierarchical structure of the sales territories, you have decided to create a new table in the database and implement the hierarchy using the hierarchyid data type. The main tasks for this exercise are as follows: 1. 2. Create a hierarchy. Query a hierarchy. Task 1: Create a hierarchy • • • • • • • • • • • • • Open the Project E:\Mod09\Labfiles\Starter\Exercise03\SalesTerritory\SalesTerritory.ssmssln. In Solution Explorer, open the CreateTerritoryTable.sql file. Review and then run the query. In Solution Explorer, open the CreateTableIndex.sql file. Review and then run the query. In Solution Explorer, open the InsertRootTerritory.sql file. Review and then run the query. In Solution Explorer, open the InsertChildTerritory.sql file. Review and then run the query. In Solution Explorer, open the CreateTerritorySP.sql file. Review and then run the query. In Solution Explorer, open the PopulateTerritoryTable.sql file. Review and then run the query. Task 2: Query a hierarchy • • • In Solution Explorer, open the GetTerritoryOrg.sql file. Review and then run the query. Review the Results to see how the nodes in the hierarchy relate to one another. Pay particular attention to the Text_OrgNode, OrgNode, and OrgLevel columns. Results: After this exercise, you should have created a table for the Sales department hierarchy and populated it. This is a unique copy of the course material identified by code cbbd3d2c-1b8b-4127-8769-5439cb34900e, and provided to you by Holly Stiver. It is illegal to reprint, redistribute, or resell this content. The Licensed Content is licensed "as-is." Microsoft does not support this Licensed Content in any way and Microsoft gives no express warranties, guarantees or conditions. Please report any unauthorized use of this content to [email protected] or by calling +1 800-785-3448. QuickStart Intelligence Lab Instructions: Using Advanced Techniques 7 Exercise 4: Using Cursors and Set-Based Queries Scenario You are working with a test copy of the AdventureWorks2008 database that has no pricing data in the Production.Product table. You need to populate both the ListPrice and StandardCost fields of the table, and have decided use a cursor to update ListPrice data and use a set-based query to update StandardCost data to see how the two approaches compare. The main tasks for this exercise are as follows: 1. 2. Update the ListPrice column. Update the StandardCost column. Task 1: Update the ListPrice column • Create a new query on the AdventureWorks2008 database with the following code: SELECT ProductID, Name, StandardCost, ListPrice FROM Production.Product • • • • • Review the StandardCost and ListPrice columns in the Results pane. Keep this query pane open. Open the E:\Mod09\Labfiles\Starter\Exercise04\UpdateListPriceCursor.sql file. Review and execute the query. When the update is complete, run the SELECT query again. Review the ListPrice column in the Results pane. The ListPrice column has been updated. Task 2: Update the StandardCost column • Create a new query on the AdventureWorks2008 database with the following code: UPDATE Production.Product SET StandardCost = 7.99 • • • • • Run the query. When the update is complete, run the SELECT query again. Review the StandardCost column in the Results pane. The StandardCost column has been updated. Close SQL Server Management Studio without saving changes. Turn off the virtual machine and discard changes. Results: After this exercise, you should have used a cursor to update the ListPrice column and used a set-based query to update the StandardCost column of the Production.Product table. This is a unique copy of the course material identified by code cbbd3d2c-1b8b-4127-8769-5439cb34900e, and provided to you by Holly Stiver. It is illegal to reprint, redistribute, or resell this content. The Licensed Content is licensed "as-is." Microsoft does not support this Licensed Content in any way and Microsoft gives no express warranties, guarantees or conditions. Please report any unauthorized use of this content to [email protected] or by calling +1 800-785-3448. QuickStart Intelligence Lab Answer Keys: Getting Started with Databases and Transact-SQL in SQL Server 2008 1 Module 1 Lab Answer Keys: Getting Started with Databases and Transact-SQL in SQL Server 2008 Contents: Exercise 1: Explore the Components and Execute Queries in SQL Server Management Studio Exercise 2: Start and Use sqlcmd Exercise 3: Generate a Report from a SQL Server Database using Microsoft Office Excel 2 5 6 This is a unique copy of the course material identified by code cbbd3d2c-1b8b-4127-8769-5439cb34900e, and provided to you by Holly Stiver. It is illegal to reprint, redistribute, or resell this content. The Licensed Content is licensed "as-is." Microsoft does not support this Licensed Content in any way and Microsoft gives no express warranties, guarantees or conditions. Please report any unauthorized use of this content to [email protected] or by calling +1 800-785-3448. QuickStart Intelligence 2 Lab Answer Keys: Getting Started with Databases and Transact-SQL in SQL Server 2008 Lab: Using SQL Server Management Studio and SQLCMD Exercise 1: Explore the components and execute queries in SQL Server Management Studio Task 1: Launch the SQL Server Management Studio 1. 2. 3. 4. 5. In the Lab Launcher, next to 2778A-NY-SQL-01, click Launch. Log on to NYC-SQL-01 as Student using the password Pa$$w0rd. On the Start menu, click All Programs, click Microsoft SQL Server 2008, and then click SQL Server Management Studio. The Microsoft SQL Server Management Studio window opens, and then the Connect to Server dialog box appears. In the Connect to Server dialog box, verify that the Server type, Server name, and Authentication fields have been populated correctly, and then click Connect to accept the default settings. Task 2: Navigate through online help 1. 2. 3. On the Help menu, click Contents. The Server 2008 Combined Help Collection - Microsoft Document Explorer window appears. In the Contents pane, navigate to SQL Server Books Online | Getting Started | Initial Installation | Overview of SQL Server Installation | Features and Tools Overview |SQL Server Studios Overview | Introducing SQL Server Management Studio | Tutorial: SQL Server Management Studio | Lesson 1: Basic Navigation in SQL Server Management Studio. The Lesson 1: Basic Navigation in SQL Server Management Studio page opens in the right pane. In the Contents pane, expand Lesson 1: Basic Navigation in SQL Server Management Studio, and then click the topic Connecting with Registered Servers and Object Explorer. The content of the page opens in the right pane. 4. 5. 6. Task 3: Resize, hide, and close Object Explorer and Solution Explorer 1. 2. 3. 4. 5. 6. Reduce the size of the Contents pane by resizing it. Scroll down the Connecting with Registered Servers and Object Explorer page to view its contents. Click the Close button to close the Help window. On the View menu, click Solution Explorer. In Solution Explorer, click Auto Hide to hide the pane. In Object Explorer, click Auto Hide to hide the pane. Task 4: Create a new solution and explore the solution objects in Object Explorer 1. 2. 3. 4. 5. 6. 7. Open Object Explorer by clicking the Object Explorer tab. In Object Explorer, click Auto Hide to dock the pane. In the Object Explorer, double-click the Databases folder, double-click AdventureWorks2008, and then double-click Tables. You can scroll through the pane to view the list of tables. On the toolbar, click New Query. The SQLQuery1.sql - NY-SQL-01.AdventureWorks2008 window opens. Open Solution Explorer by clicking the Solution Explorer tab. In Solution Explorer, click Auto Hide to dock the pane. This is a unique copy of the course material identified by code cbbd3d2c-1b8b-4127-8769-5439cb34900e, and provided to you by Holly Stiver. It is illegal to reprint, redistribute, or resell this content. The Licensed Content is licensed "as-is." Microsoft does not support this Licensed Content in any way and Microsoft gives no express warranties, guarantees or conditions. Please report any unauthorized use of this content to [email protected] or by calling +1 800-785-3448. QuickStart Intelligence Lab Answer Keys: Getting Started with Databases and Transact-SQL in SQL Server 2008 3 8. 9. 10. 11. 12. 13. 14. In Solution Explorer, right-click Solution 'Solution1' (1 project), point to Add, and then click New Project. The Add New Project dialog box appears. In the Templates box, verify that the SQL Server Scripts template is highlighted. In the Name field, select SQL Server Scripts1, type PersonAddress, and then click OK. The PersonAddress project will be created under Solution ‘Solution1’ (1 project). In Solution Explorer, in the PersonAddress project, right-click Connections, and then click New Connection. The Connect to Server dialog box appears. Verify the connection information, and then click OK. Task 5: Add projects to an existing solution and create queries in the projects 1. 2. 3. 4. 5. 6. 7. 8. 9. 10. 11. 12. 13. 14. 15. 16. 17. 18. 19. 20. 21. 22. 23. 24. 25. 26. 27. 28. 29. 30. 31. 32. 33. In the Connections folder of the PersonAddress project, right-click NY-SQL-01:NY-SQL-01\Student, and then click New Query. The SQLQuery1.sql object appears in the Queries folder. In the query window, type USE AdventureWorks2008, and then press ENTER. On the toolbar, click Execute. The active database is changed to AdventureWorks2008. In the query window, type SELECT DISTINCT CITY FROM Person.Address. On the toolbar, click Execute. The list of cities in the Person.Address table is displayed without repetition in the Results pane. In the Queries folder of PersonAddress project, right-click SQLQuery1.sql, and then click Rename. Rename the query as Address.sql. The query has been renamed to Address.sql. On the File menu, click Save Address.sql. In Solution Explorer, right-click PersonAddress, and then click Save PersonAddress.ssmssqlproj. Right-click Solution ‘Solution1’ (1 project), point to Add, and then click New Project. The Add New Project dialog box appears. In the Templates box, verify that the SQL Server Scripts template is highlighted. In the Name field, select SQL Server Scripts1, type HumanResourcesDepartment, and then click OK. The HumanResourcesDepartment project is created under Solution ‘Solution1’ (2 projects). In the HumanResourceDepartments project, right-click Connections, and then click New Connection. The Connect to Server dialog box appears. Verify the connection information, and then click OK. In the Connections folder, under HumanResourceDepartments, right-click NY-SQL-01:NY-SQL01\Student and then click New Query. The SQLQuery1.sql folder appears. In the Queries folder of HumanResourcesDepartment project, right-click SQLQuery1.sql, and then click Rename. Rename the query as Department.sql. The query has been renamed to Department.sql. In the query window, type USE AdventureWorks2008, and then press ENTER. On the toolbar, click Execute. The active database is changed to AdventureWorks2008. In the query window, type SELECT Name, GroupName FROM HumanResources.Department. On the toolbar, click Execute. The Name and GroupName columns are displayed in the Results pane. On the File menu, and click Save Department.sql. This is a unique copy of the course material identified by code cbbd3d2c-1b8b-4127-8769-5439cb34900e, and provided to you by Holly Stiver. It is illegal to reprint, redistribute, or resell this content. The Licensed Content is licensed "as-is." Microsoft does not support this Licensed Content in any way and Microsoft gives no express warranties, guarantees or conditions. Please report any unauthorized use of this content to [email protected] or by calling +1 800-785-3448. QuickStart Intelligence 4 Lab Answer Keys: Getting Started with Databases and Transact-SQL in SQL Server 2008 34. In Solution Explorer, right-click the HumanResourcesDepartment project, and then click Save HumanResourcesDepartment.ssmssqlproj. 35. Click Solution ‘Solution1’ (2 projects). 36. On the File menu, click Save Solution1 As. 37. The Save File As dialog box appears. Verify that the Projects folder is selected in the Save in: list. 38. In the File name field, type AdventureWorks2008.ssmssln, and then click Save. 39. Click the Close button to close SQL Server Management Studio. Task 6: Connect to SQL Server and execute a query 1. 2. 3. 4. 5. 6. 7. 8. On the Start menu, click All Programs, click Microsoft SQL Server 2008, and then click SQL Server Management Studio. The Microsoft SQL Server Management Studio window opens, and then the Connect to Server dialog box appears. In the Connect to Server dialog box, verify that the Server type, Server name, and Authentication fields have been populated correctly, and then click Connect to accept the default settings. On the File menu, point to Open, and then click File. The Open File dialog box appears. In the Open File window, navigate to the PersonAddress folder, click Address.sql, and then click Open. If the Connect to Database Engine application window appears, click Connect. On the toolbar, click Execute. The results are displayed in the Results pane. It should be similar to the results in the earlier task. Task 7: Use Visual Query Builder to return rows from a table 1. 2. 3. 4. 5. 6. 7. 8. 9. 10. 11. 12. 13. 14. 15. 16. In Object Explorer, expand the Databases folder. Right-click AdventureWorks2008 database, and then click New Query. On the Query menu, click Design Query in Editor. The Query Designer dialog box appears, and the Add Table dialog box appears. In the Add Table dialog box, verify that the Address (Person) table is selected, and then click Add. The Address (Person) dialog box appears. The columns of the Address table are listed in the Address (Person) dialog box. In the Add Table dialog box, click Close. In the Address (Person) dialog box, select the * (All Columns) check box. In the Query Designer dialog box, click OK. Notice the query in the SQLQuery1.sql - NY-SQL-01.AdventureWorks2008 query pane. On the toolbar, click Execute. All the columns in the Person.Address table are displayed in the Results pane. Click the Close button to close SQL Server Management Studio. The Microsoft SQL Server Management Studio dialog box appears. Click No to close the solution without saving changes. Results: After this exercise, you should have explored the components and executed queries in the SQL Server Management Studio. This is a unique copy of the course material identified by code cbbd3d2c-1b8b-4127-8769-5439cb34900e, and provided to you by Holly Stiver. It is illegal to reprint, redistribute, or resell this content. The Licensed Content is licensed "as-is." Microsoft does not support this Licensed Content in any way and Microsoft gives no express warranties, guarantees or conditions. Please report any unauthorized use of this content to [email protected] or by calling +1 800-785-3448. QuickStart Intelligence Lab Answer Keys: Getting Started with Databases and Transact-SQL in SQL Server 2008 5 Exercise 2: Start and use sqlcmd Task 1: Start the sqlcmd utility and connect to a default instance of SQL Server 1. 2. 3. 4. On the Start menu click All Programs, click Accessories, and then click Command Prompt. The Command Prompt window opens. At the command prompt, type sqlcmd, and then press ENTER. You now have a trusted connection to the default instance of SQL Server that is running on your computer. 1> is the sqlcmd prompt that specifies the line number. Each time you press ENTER, the number increases by one. At the sqlcmd prompt, type exit, and then press ENTER, to end the sqlcmd session. 5. Task 2: Run a Transact-SQL script file by using sqlcmd 1. 2. 3. In the Command Prompt window, type: sqlcmd -S NY-SQL-01 -i E:\MOD01\Labfiles\Starter\Department.sql. Press ENTER. Notice that the results are similar to the results returned in the earlier task. Task 3: Run a Transact-SQL script file and save the output to a text file 1. 2. 3. In the Command Prompt window, type: sqlcmd -S NY-SQL-01 -i E:\MOD01\Labfiles\Starter\Department.sql -o E:\MOD01\Labfiles\Solution\DeptList.txt. Press ENTER. Notice that there is no output in the Command Prompt window. Task 4: Review the output file 1. 2. 3. 4. 5. 6. 7. 8. 9. On the Start menu, click Computer. The Windows Explorer window opens. Navigate to E:\MOD01\Labfiles\Solution\. In the details pane, double-click DeptList.txt. The DeptList.txt - Notepad window opens. Notice that the results are similar to the results returned in the earlier task. Close Notepad. Close Windows Explorer. Close Command Prompt. Results: After this exercise, you should have started and used sqlcmd to create reports. This is a unique copy of the course material identified by code cbbd3d2c-1b8b-4127-8769-5439cb34900e, and provided to you by Holly Stiver. It is illegal to reprint, redistribute, or resell this content. The Licensed Content is licensed "as-is." Microsoft does not support this Licensed Content in any way and Microsoft gives no express warranties, guarantees or conditions. Please report any unauthorized use of this content to [email protected] or by calling +1 800-785-3448. QuickStart Intelligence 6 Lab Answer Keys: Getting Started with Databases and Transact-SQL in SQL Server 2008 Exercise 3: Generate a report from a SQL Server database using Microsoft Office Excel Task 1: Launch Excel 1. 2. On the Start menu, click All Programs, click Microsoft Office, and then click Microsoft Office Excel 2007. The Microsoft Excel window opens. Task 2: Create a new data connection in the workbook 1. 2. 3. 4. On the Data menu, click Get External Data, click From Other Sources, and then click From SQL Server. The Data Connection Wizard wizard appears. In the Server name field, type NY-SQL-01. Click Next. Task 3: Select the data to import and its format On the Select Database and Table page, in the Select the database that contains the data you want list, click AdventureWorks2008. 2. In the Connect to a specific table box, in the Name column, click Address. 3. Click Next. 4. In the Save Data Connection File and Finish page, in the Description field, type AdventureWorks2008 Addresses. 5. In the Friendly Name field, type Addresses. 6. Click Authentication Settings. 7. The Excel Services Authentication Settings dialog box appears. 8. Notice the three options for authentication and notice that Windows Authentication is selected. 9. Click Cancel. 10. Click Finish. 1. Task 4: View the report 1. 2. 3. 4. 5. 6. 7. 8. 9. The Import Data dialog box appears. Notice the options for importing the data. Click OK to accept the default settings. The Microsoft Excel-Book 1 is populated with the names of various cities. Examine the results. Close Microsoft Office Excel. The Microsoft Office Excel dialog box appears indicating whether you need to save the changes that you have made to Book 1. Click No. Turn off 2778A-NY-SQL-01 and delete changes. Results: After this exercise, you should create a report from a SQL Server database using Microsoft Office Excel. This is a unique copy of the course material identified by code cbbd3d2c-1b8b-4127-8769-5439cb34900e, and provided to you by Holly Stiver. It is illegal to reprint, redistribute, or resell this content. The Licensed Content is licensed "as-is." Microsoft does not support this Licensed Content in any way and Microsoft gives no express warranties, guarantees or conditions. Please report any unauthorized use of this content to [email protected] or by calling +1 800-785-3448. QuickStart Intelligence Lab Answer Keys: Querying and Filtering Data 1 Module 2 Lab Answer Keys: Querying and Filtering Data Contents: Exercise 1: Exploring SQL Server Management Studio Exercise 2: Filter Data by Using Various Search Conditions Exercise 3: Using Functions to Work with Null Values Exercise 4: Formatting Result Sets 2 2 5 6 This is a unique copy of the course material identified by code cbbd3d2c-1b8b-4127-8769-5439cb34900e, and provided to you by Holly Stiver. It is illegal to reprint, redistribute, or resell this content. The Licensed Content is licensed "as-is." Microsoft does not support this Licensed Content in any way and Microsoft gives no express warranties, guarantees or conditions. Please report any unauthorized use of this content to [email protected] or by calling +1 800-785-3448. QuickStart Intelligence 2 Lab Answer Keys: Querying and Filtering Data Lab: Querying and Filtering Data Exercise 1: Exploring SQL Server Management Studio Task 1: Launch SQL Server Management Studio 1. 2. In the Lab Launcher, next to 2778A-NY-SQL-01, click Launch. Log on as Administrator with the password of Pa$$w0rd. Task 2: Generate a report by using the SELECT * statement with the FROM clause 1. 2. On the Start menu, point to All Programs, click Microsoft SQL Server 2008 and click SQL Server Management Studio. In the Connect to Server dialog box, verify that the Server type, Server name, and the Authentication boxes have been populated correctly, and click Connect to accept the default settings. On the toolbar of SQL Server Management Studio, click New Query. A new query opens in the query window. In the query window type the following SQL Statement: USE AdventureWorks2008 GO SELECT * FROM Person.Person 3. 4. 5. 6. Click Execute. Browse the result set in the Results pane. Task 3: Generate a report by using the SELECT statement with the FROM clause 1. 2. On the toolbar of SQL Server Management Studio, click New Query. A new query opens in the query window. In the new query window type and execute the following SQL statement: USE AdventureWorks2008 GO SELECT Title, FirstName, LastName, MiddleName FROM Person.Person 3. 4. Click Execute. The result set is displayed in the Results pane. Browse the result set in the Results pane and notice that only the specified columns appear. Results: After this exercise, you should have learned how to create a basic SELECT statement to retrieve data from a table. Exercise 2: Filter Data by Using Various Search Conditions Task 1: Generate a report by using the SELECT statement with a COMPARISON operator 1. 2. On the toolbar of SQL Server Management Studio, click New Query. A new query opens in the query window. In the query window enter and execute the following SQL statement: USE AdventureWorks2008 GO SELECT ProductNumber, Name, ListPrice, Color, Size, Weight This is a unique copy of the course material identified by code cbbd3d2c-1b8b-4127-8769-5439cb34900e, and provided to you by Holly Stiver. It is illegal to reprint, redistribute, or resell this content. The Licensed Content is licensed "as-is." Microsoft does not support this Licensed Content in any way and Microsoft gives no express warranties, guarantees or conditions. Please report any unauthorized use of this content to [email protected] or by calling +1 800-785-3448. QuickStart Intelligence Lab Answer Keys: Querying and Filtering Data 3 FROM Production.Product WHERE ListPrice < $100 3. Click Execute. The result set is displayed in the Results pane. Task 2: Generate a report by using the SELECT statement with the AND and LIKE operators 1. Enter the following SQL Statement in the Query window: USE AdventureWorks2008 GO SELECT ProductNumber, Name, ListPrice, Color, Size, Weight FROM Production.Product WHERE ListPrice < $100 AND ProductNumber LIKE 'SO%' 2. Click Execute. The result set is displayed in the Results pane. Task 3: Generate a report by using the SELECT statement with the OR operator 1. Change the SQL statement to read: USE AdventureWorks2008 GO SELECT ProductNumber, Name, ListPrice, Color, Size, Weight FROM Production.Product WHERE ListPrice < $100 AND ProductNumber Like 'SO%' OR ProductNumber Like 'TG%' 2. Click Execute. The result set is displayed in the Results pane. Task 4: Generate a report by using the SELECT statement with the BETWEEN operator 1. Click New Query and type the following SQL statement into the Query window: USE AdventureWorks2008 GO SELECT ProductNumber, Name, ListPrice, Color, Size, Weight FROM Production.Product WHERE ProductNumber LIKE 'SO%' OR (ListPrice Between $50 and $180 AND ProductNumber LIKE 'TG%') 2. Click Execute. The result set is displayed in the Results pane. Task 5: Generate a report by using the SELECT statement with the IN operator 1. Rewrite the SQL statement to read as follows: USE AdventureWorks2008 GO SELECT ProductNumber, Name, ListPrice, Color, Size, Weight FROM Production.Product WHERE ProductNumber LIKE 'SO%' OR (ListPrice Between $50 and $180 AND ProductNumber LIKE 'TG%' AND Size IN ('M', 'L')) This is a unique copy of the course material identified by code cbbd3d2c-1b8b-4127-8769-5439cb34900e, and provided to you by Holly Stiver. It is illegal to reprint, redistribute, or resell this content. The Licensed Content is licensed "as-is." Microsoft does not support this Licensed Content in any way and Microsoft gives no express warranties, guarantees or conditions. Please report any unauthorized use of this content to [email protected] or by calling +1 800-785-3448. QuickStart Intelligence 4 Lab Answer Keys: Querying and Filtering Data 2. Click Execute. The result set is displayed in the Results pane. Results: After this exercise, you should have learned how to use several different comparison operators to create reports specific to different user needs. This is a unique copy of the course material identified by code cbbd3d2c-1b8b-4127-8769-5439cb34900e, and provided to you by Holly Stiver. It is illegal to reprint, redistribute, or resell this content. The Licensed Content is licensed "as-is." Microsoft does not support this Licensed Content in any way and Microsoft gives no express warranties, guarantees or conditions. Please report any unauthorized use of this content to [email protected] or by calling +1 800-785-3448. QuickStart Intelligence Lab Answer Keys: Querying and Filtering Data 5 Exercise 3: Using Functions to Work with Null Values Task 1: Generate a report by using the SELECT statement with the NULL function 1. 2. On the toolbar of SQL Server Management Studio, click New Query. A new query opens in the query window. In the query window, type the following SQL statements: USE AdventureWorks2008 GO SELECT ProductNumber, Name, Weight FROM Production.Product WHERE ProductLine = NULL 3. 4. Click Execute. Notice that no result set is displayed in the Results pane. Task 2: Generate a report by using the SELECT statement with the IS NULL function 1. Rewrite the SQL statement to use IS NULL: USE AdventureWorks2008 GO SELECT ProductNumber, Name, Weight FROM Production.Product WHERE ProductLine IS NULL 2. 3. Click Execute. Browse the result set displayed in the Results pane. Task 3: Generate a report by using the SELECT statement with the ISNULL function to rename values 1. 2. On the toolbar of SQL Server Management Studio, click New Query. A new query opens in the query window. In the query window type the following SQL statement: USE AdventureWorks2008 GO SELECT ProductNumber, Name, Weight, ISNULL(ProductLine, 'NA') FROM Production.Product 3. 4. Click Execute. The result set is displayed in the Results pane. Notice that the new column shows NA in all rows that Weight has a NULL value. But the column has no heading. Task 4: Generate a report by using the SELECT statement with the ISNULL function and a column alias 1. Change the statement as follows, to add Product Line as a column heading for the ProductLine column: USE AdventureWorks2008 GO SELECT ProductNumber, Name, Weight, ISNULL(ProductLine, 'NA') AS ‘Product Line’ This is a unique copy of the course material identified by code cbbd3d2c-1b8b-4127-8769-5439cb34900e, and provided to you by Holly Stiver. It is illegal to reprint, redistribute, or resell this content. The Licensed Content is licensed "as-is." Microsoft does not support this Licensed Content in any way and Microsoft gives no express warranties, guarantees or conditions. Please report any unauthorized use of this content to [email protected] or by calling +1 800-785-3448. QuickStart Intelligence 6 Lab Answer Keys: Querying and Filtering Data FROM Production.Product 2. 3. Click Execute. The result set is displayed in the Results pane. Note that the new column now has the heading of Product Line. Task 5: Generate a report by using the SELECT statement with the ISNULL function and the COALESCE and CONVERT functions 1. Click New Query and enter the following SQL Statement into the Query window: USE AdventureWorks2008 GO SELECT ProductNumber, Name, COALESCE(CONVERT(NVARCHAR,Weight), SIZE, 'NA') AS Measurement, ISNULL(ProductLine, 'NA') AS ‘Product Line’ FROM Production.Product 2. 3. Click Execute. The result set is displayed in the Results pane. Notice the values in the Measurement column. Results: After this exercise, you should have learned to handle NULL values in a result set by identifying them and replacing them with alternate values when necessary. Exercise 4: Formatting Result Sets Task 1: Format a result set by using the ORDER BY clause 1. 2. Click New Query. In the new query window type the following SQL statement: USE AdventureWorks2008 GO SELECT ProductNumber, Name, Class FROM Production.Product ORDER BY Class 3. Click Execute. Task 2: Format a result set by using the ORDER BY clause and the DESC keyword 1. 2. Click New Query. Type the following SQL statement into the Query window: USE AdventureWorks2008 GO SELECT ProductNumber, Name, Class, ListPrice FROM Production.Product ORDER BY Class, ListPrice Desc 3. Click Execute. The result set is displayed in the Results pane. Task 3: Format a result set by using the DISTINCT keyword 1. 2. Click New Query. In the query window, enter the following SQL statement: USE AdventureWorks2008 This is a unique copy of the course material identified by code cbbd3d2c-1b8b-4127-8769-5439cb34900e, and provided to you by Holly Stiver. It is illegal to reprint, redistribute, or resell this content. The Licensed Content is licensed "as-is." Microsoft does not support this Licensed Content in any way and Microsoft gives no express warranties, guarantees or conditions. Please report any unauthorized use of this content to [email protected] or by calling +1 800-785-3448. QuickStart Intelligence Lab Answer Keys: Querying and Filtering Data 7 GO SELECT DISTINCT Color FROM Production.Product WHERE ProductNumber LIKE 'HL%' 3. Click Execute. The result set is displayed in the Results pane. Task 4: Format a result set by concatenating strings 1. 2. Click New Query. In the query window type the following SQL statement: USE AdventureWorks2008 GO SELECT LastName + ',' + FirstName FROM Person.Person 3. Click Execute. The result set is displayed in the Results pane. Task 5: Format a result set by concatenating strings and using column aliasing 1. Rewrite the query to assign the column name Contacts. USE AdventureWorks2008 GO SELECT (LastName + ',' + FirstName) AS Contacts FROM Person.Person 2. Click Execute. The result set is displayed in the Results pane. Task 6: Format a result set by using the SUBSTRING function 1. Rewrite the statement so that it will search for all rows with the LastName beginning with Mac. USE AdventureWorks2008 GO SELECT (LastName + ',' + FirstName) AS Contacts FROM Person.Person WHERE SUBSTRING (LastName,1,3)= 'Mac' 2. 3. 4. Click Execute. The result set is displayed in the Results pane. On the File menu, click Exit and answer No to the save queries prompt. Turn off NY-SQL-01 and discard changes. Results: After this exercise, you should have learned how to format the result sets to make them more readable. This is a unique copy of the course material identified by code cbbd3d2c-1b8b-4127-8769-5439cb34900e, and provided to you by Holly Stiver. It is illegal to reprint, redistribute, or resell this content. The Licensed Content is licensed "as-is." Microsoft does not support this Licensed Content in any way and Microsoft gives no express warranties, guarantees or conditions. Please report any unauthorized use of this content to [email protected] or by calling +1 800-785-3448. QuickStart Intelligence Lab Answer Keys: Grouping and Summarizing Data 1 Module 3 Lab Answer Keys: Grouping and Summarizing Data Contents: Exercise 1: Summarizing Data by Using Aggregate Functions Exercise 2: Summarizing Grouped Data Exercise 3: Ranking Grouped Data Exercise 4: Creating Crosstab Queries 2 4 6 7 This is a unique copy of the course material identified by code cbbd3d2c-1b8b-4127-8769-5439cb34900e, and provided to you by Holly Stiver. It is illegal to reprint, redistribute, or resell this content. The Licensed Content is licensed "as-is." Microsoft does not support this Licensed Content in any way and Microsoft gives no express warranties, guarantees or conditions. Please report any unauthorized use of this content to [email protected] or by calling +1 800-785-3448. QuickStart Intelligence 2 Lab Answer Keys: Grouping and Summarizing Data Lab: Grouping and Summarizing Data Exercise 1: Summarizing Data by Using Aggregate Functions Task 1: Start the 2778A-NY-SQL-01 virtual machine, log on as Administrator, and launch SQL Server Management Studio 1. 2. 3. 4. 5. 6. In the Lab Launcher, next to 2778A-NY-SQL-01, click Launch. Log on as Administrator with the password of Pa$$w0rd. Click Start | All Programs | Microsoft SQL Server 2008, and then click SQL Server Management Studio. In the Connect to Server dialog box, verify that Server type is set to Database Engine and Server name is set to NY-SQL-01, and then click Connect. Maximize Microsoft SQL Server Management Studio. In Object Explorer, expand NY-SQL-01 | Databases. Task 2: Write a query that displays a single summary value for all the rows 1. 2. 3. On the toolbar of SQL Server Management Studio, click New Query. A new query opens in the query window. In the Available Databases list on the toolbar, click AdventureWorks2008. You are connected to the AdventureWorks2008 database. In the query window, type the following SQL statements: SELECT AVG(VacationHours) AS 'AverageVacationHours', SUM(SickLeaveHours) AS 'TotalSickLeave Hours' FROM HumanResources.Employee WHERE JobTitle LIKE '%Vice President%' 4. On the toolbar, click Execute. Task 3: Write a second query that displays a single summary value for all rows 1. 2. On the toolbar of SQL Server Management Studio, click New Query. In the query window, type the following SQL statement: SELECT COUNT(*) FROM HumanResources.Employee 3. Select the statement, and on the toolbar click Execute. Task 4: Write a query that computes the total number of employees with the AddressLine2 value as NULL 1. 2. On the toolbar of SQL Server Management Studio, click New Query. In the query window, type the following SQL statement: SELECT COUNT(*) FROM Person.Address WHERE ISNULL (AddressLine2, '0') = '0' 3. Select the statement, and on the toolbar click Execute. This is a unique copy of the course material identified by code cbbd3d2c-1b8b-4127-8769-5439cb34900e, and provided to you by Holly Stiver. It is illegal to reprint, redistribute, or resell this content. The Licensed Content is licensed "as-is." Microsoft does not support this Licensed Content in any way and Microsoft gives no express warranties, guarantees or conditions. Please report any unauthorized use of this content to [email protected] or by calling +1 800-785-3448. QuickStart Intelligence Lab Answer Keys: Grouping and Summarizing Data 3 Task 5: Modify the query and eliminate the NULL values 1. 2. On the toolbar of SQL Server Management Studio, click New Query. In the query window, type the following SQL statement: SELECT COUNT (AddressLine2) FROM Person.Address 3. 4. 5. Select the query, and on the toolbar, click Execute. On the toolbar of SQL Server Management Studio, click New Query. In the query window, type the SQL statement: SELECT COUNT (DISTINCT AddressLine2) FROM Person.Address 6. Select the statement, and on the toolbar click Execute. Results: After this exercise you should have launched SQL Server Management Studio and created queries to display summary values for rows. You should have also created queries to compute totals and eliminate NULL values. This is a unique copy of the course material identified by code cbbd3d2c-1b8b-4127-8769-5439cb34900e, and provided to you by Holly Stiver. It is illegal to reprint, redistribute, or resell this content. The Licensed Content is licensed "as-is." Microsoft does not support this Licensed Content in any way and Microsoft gives no express warranties, guarantees or conditions. Please report any unauthorized use of this content to [email protected] or by calling +1 800-785-3448. QuickStart Intelligence 4 Lab Answer Keys: Grouping and Summarizing Data Exercise 2: Summarizing Grouped Data Task 1: Write a query that computes the average number of days to manufacture a product 1. 2. 3. On the toolbar of SQL Server Management Studio, click New Query. A new query opens in the query window. In the Available Databases list on the toolbar, click AdventureWorks2008. You are connected to the AdventureWorks2008 database. In the query window, type the following SQL statements: SELECT ProductID, AVG(DaysToManufacture) AS 'AvgDaysToManufacture' FROM Production.Product GROUP BY ALL ProductID 4. On the toolbar, click Execute. Task 2: Write a query that displays the various colors of a particular product and the average ListPrice of the colors 1. 2. On the toolbar of SQL Server Management Studio, click New Query. In the query window, type the following SQL statements: SELECT Color, AVG(ListPrice) AS 'AvgListPrice' FROM Production.Product WHERE ProductNumber = 'FR-R72R-58' GROUP BY ALL Color 3. Select the statement, and on the toolbar click Execute. Task 3: Generate a report that lists the average order quantity and sum of line total for each product with a line total that exceeds $1000000.00 and with average quantity less than 3 1. 2. On the toolbar of SQL Server Management Studio, click New Query. In the query window, type the following SQL statements: SELECT ProductID, AVG(OrderQty) AS 'AverageQuantity', SUM(LineTotal) AS Total FROM Sales.SalesOrderDetail GROUP BY ProductID HAVING SUM(LineTotal) > $1000000.00 AND AVG(OrderQty) < 3 3. Select the statement, and on the toolbar click Execute. Task 4: Group the products and then compute the sum of the quantity shelf-wise 1. 2. On the toolbar of SQL Server Management Studio, click New Query. In the query window, type the following SQL statements: SELECT ProductID, Shelf, SUM(Quantity) AS 'QtySum' FROM Production.ProductInventory GROUP BY ROLLUP(ProductID, Shelf) 3. Select the statement, and on the toolbar click Execute. This is a unique copy of the course material identified by code cbbd3d2c-1b8b-4127-8769-5439cb34900e, and provided to you by Holly Stiver. It is illegal to reprint, redistribute, or resell this content. The Licensed Content is licensed "as-is." Microsoft does not support this Licensed Content in any way and Microsoft gives no express warranties, guarantees or conditions. Please report any unauthorized use of this content to [email protected] or by calling +1 800-785-3448. QuickStart Intelligence Lab Answer Keys: Grouping and Summarizing Data 5 Task 5: Generate a summary report 1. 2. On the toolbar of SQL Server Management Studio, click New Query. In the query window, type the following SQL statements: SELECT SalesOrderID, ProductID, SUM(OrderQty) AS SumQuantity FROM Sales.SalesOrderDetail GROUP BY CUBE(SalesOrderID, ProductID) ORDER BY SalesOrderID, ProductID 3. Select the statement, and on the toolbar click Execute. Task 6: Distinguish the rows generated by the summary or aggregations and actual table rows 1. 2. On the toolbar of SQL Server Management Studio, click New Query. In the query window, type the following SQL statements: SELECT SalesQuota, SUM(SalesYTD) 'TotalSalesYTD', GROUPING(SalesQuota) AS 'Grouping' FROM sales.SalesPerson GROUP BY CUBE(SalesQuota) 3. Select the statement, and on the toolbar click Execute. Task 7: Generate a report of the summary columns by using the GROUPING function 1. 2. On the toolbar of SQL Server Management Studio, click New Query. In the query window, type the following SQL statements: SELECT ProductID, Shelf, SUM(Quantity) AS 'TotalQuantity', GROUPING (Shelf) AS 'Shelfgrouping' FROM Production.ProductInventory GROUP BY CUBE(ProductID, Shelf) 3. Select the statement, and on the toolbar click Execute. Task 8: Generate a report that displays all the products, the unit price, the unit price discount, and the sum of the columns 1. 2. On the toolbar of SQL Server Management Studio, click New Query. In the query window, type the following SQL statements: SELECT SalesOrderID, UnitPrice, UnitPriceDiscount FROM Sales.SalesOrderDetail ORDER BY SalesOrderID COMPUTE SUM(UnitPrice), SUM(UnitPriceDiscount) 3. Select the statement, and on the toolbar click Execute. Results: After this exercise you have learned how to create queries to compute averages, display additional data and perform summations. You should have also learned how to create queries using aggregation functions and the GROUPING function. This is a unique copy of the course material identified by code cbbd3d2c-1b8b-4127-8769-5439cb34900e, and provided to you by Holly Stiver. It is illegal to reprint, redistribute, or resell this content. The Licensed Content is licensed "as-is." Microsoft does not support this Licensed Content in any way and Microsoft gives no express warranties, guarantees or conditions. Please report any unauthorized use of this content to [email protected] or by calling +1 800-785-3448. QuickStart Intelligence 6 Lab Answer Keys: Grouping and Summarizing Data Exercise 3: Ranking Grouped Data Task 1: Generate row numbers for each of the rows in the year-to-date sales of the salespersons 1. 2. 3. On the toolbar of SQL Server Management Studio, click New Query. A new query opens in the query window. In the Available Databases list on the toolbar, click AdventureWorks2008. You are connected to the AdventureWorks2008 database. In the query window, type the following SQL statements: SELECT ROW_NUMBER() OVER(ORDER BY SalesYTD DESC) AS 'Row Number', BusinessEntityID, SalesYTD FROM Sales.SalesPerson S WHERE TerritoryID IS NOT NULL AND SalesYTD<>0 4. On the toolbar, click Execute. Task 2: Generate a report that ranks the products by using the RANK function 1. 2. On the toolbar of SQL Server Management Studio, click New Query. In the query window, type the following SQL statements: SELECT RANK() OVER (PARTITION BY LocationID ORDER BY Quantity DESC) AS RANK, ProductID, LocationID, Quantity FROM Production.ProductInventory ORDER BY RANK 3. Select the statement, and on the toolbar click Execute. Task 3: Generate a report that ranks the products by using the DENSE_RANK function 1. 2. On the toolbar of SQL Server Management Studio, click New Query. In the query window, type the following SQL statements: SELECT DENSE_RANK() OVER (PARTITION BY LocationID ORDER BY Quantity ProductID, LocationID, Quantity FROM Production.ProductInventory ORDER BY RANK DESC) AS RANK, 3. Select the statement, and on the toolbar click Execute. Task 4: Generate a report that lists the details of salespersons based on their year-todate sales in descending order, and then group them into four categories 1. 2. On the toolbar of SQL Server Management Studio, click New Query. In the query window, type the following SQL statements: SELECT NTILE(4) OVER(ORDER BY SalesYTD DESC) AS 'Quartile',SalesYTD, BusinessEntityID FROM Sales.SalesPerson WHERE TerritoryID IS NOT NULL AND SalesYTD <> 0 3. Select the statement and on the toolbar, click Execute. Results: After this exercise you have learned how to create queries that use the various ranking functions to organize and rank grouped data. This is a unique copy of the course material identified by code cbbd3d2c-1b8b-4127-8769-5439cb34900e, and provided to you by Holly Stiver. It is illegal to reprint, redistribute, or resell this content. The Licensed Content is licensed "as-is." Microsoft does not support this Licensed Content in any way and Microsoft gives no express warranties, guarantees or conditions. Please report any unauthorized use of this content to [email protected] or by calling +1 800-785-3448. QuickStart Intelligence Lab Answer Keys: Grouping and Summarizing Data 7 Exercise 4: Creating Crosstab Queries Task 1: Execute a simple SELECT statement using the PIVOT operator 1. 2. 3. On the toolbar of SQL Server Management Studio, click New Query. A new query opens in the query window. In the Available Databases list on the toolbar, click AdventureWorks2008. You are connected to the AdventureWorks2008 database. In the query window, type the following SQL statements: SELECT Name, [RED], [BLUE], [BLACK] FROM (SELECT SafetyStockLevel, Color, Name FROM Production.Product) P PIVOT ( SUM (SafetyStockLevel) FOR Color IN ([RED], [BLUE], [BLACK]) ) AS PVT WHERE Name LIKE '%Helmet%' ORDER BY Name 4. On the toolbar, click Execute. Task 2: Execute a simple SELECT statement by using the UNPIVOT operator 1. 2. On the toolbar of SQL Server Management Studio, click New Query. In the query window, type the following SQL statements: SELECT Name, Attribute, Value FROM (SELECT Name, CAST (ProductLine as SQL_Variant) ProductLine, CAST (StandardCost as Sql_variant) StandardCost, CAST (ListPrice as sql_variant) ListPrice FROM Production.Product) P UNPIVOT (Value FOR Attribute IN ([ProductLine], [StandardCost], [ListPrice])) AS UnPVT Order By Name Desc 3. 4. Select the statement, and on the toolbar click Execute. Turn off the 2778A-NY-SQL-01 virtual machine and discard any changes. Results: After this exercise you have learned how to use the PIVOT operator to create a crosstab query and also how to use the UNPIVOT operator. This is a unique copy of the course material identified by code cbbd3d2c-1b8b-4127-8769-5439cb34900e, and provided to you by Holly Stiver. It is illegal to reprint, redistribute, or resell this content. The Licensed Content is licensed "as-is." Microsoft does not support this Licensed Content in any way and Microsoft gives no express warranties, guarantees or conditions. Please report any unauthorized use of this content to [email protected] or by calling +1 800-785-3448. QuickStart Intelligence Lab Answer Keys: Joining Data from Multiple Tables 1 Module 4 Lab Answer Keys: Joining Data from Multiple Tables Contents: Exercise 1: Querying Multiple Tables by Using Joins Exercise 2: Applying Joins for Typical Reporting Needs Exercise 3: Combining and Limiting Result Sets 2 4 5 This is a unique copy of the course material identified by code cbbd3d2c-1b8b-4127-8769-5439cb34900e, and provided to you by Holly Stiver. It is illegal to reprint, redistribute, or resell this content. The Licensed Content is licensed "as-is." Microsoft does not support this Licensed Content in any way and Microsoft gives no express warranties, guarantees or conditions. Please report any unauthorized use of this content to [email protected] or by calling +1 800-785-3448. QuickStart Intelligence 2 Lab Answer Keys: Joining Data from Multiple Tables Lab: Joining Data from Multiple Tables Exercise 1: Querying Multiple Tables by Using Joins Task 1: Launch SQL Server Management Studio 1. 2. 3. 4. 5. 6. 7. 8. 9. In the Lab Launcher, next to 2778A-NY-SQL-01, click Launch. Log on as Administrator with the password of Pa$$w0rd. Start Windows Explorer and browse to E:\MOD04\Labfiles\Starter. Double-click LabSetup.cmd. Close Windows Explorer. Click Start | All Programs | Microsoft SQL Server 2008, and then click SQL Server Management Studio. In the Connect to Server dialog box, verify that Server type is set to Database Engine and Server name is set to NY-SQL-01, and then click Connect. Maximize Microsoft SQL Server Management Studio. In Object Explorer, expand NY-SQL-01 | Databases. Task 2: Create and execute an inner join 1. 2. Right-click the AdventureWorks2008 database and then click New Query. In the new, blank query window type the following T-SQL statement: SELECT e.LoginID FROM HumanResources.Employee AS e INNER JOIN Sales.SalesPerson AS s ON e.BusinessEntityID = s.BusinessEntityID; 3. 4. On the toolbar, click Execute. Review the results of the query and then close the query editor without saving the query. Task 3: Create and execute a left outer join and a right outer join 1. 2. On the toolbar, click New Query. In the new, blank query window type the following T-SQL statement: SELECT p.Name, pr.ProductReviewID FROM Production.Product p LEFT OUTER JOIN Production.ProductReview pr ON p.ProductID = pr.ProductID; 3. 4. 5. 6. On the toolbar, click Execute. Review the results of the query and then close the query editor without saving the query. On the toolbar, click New Query. In the new, blank query window type the following T-SQL statement: SELECT st.Name AS Territory, sp.BusinessEntityID FROM Sales.SalesTerritory st RIGHT OUTER JOIN Sales.SalesPerson sp ON st.TerritoryID = sp.TerritoryID; 7. 8. On the toolbar, click Execute. Review the results of the query and then close the query editor without saving the query. This is a unique copy of the course material identified by code cbbd3d2c-1b8b-4127-8769-5439cb34900e, and provided to you by Holly Stiver. It is illegal to reprint, redistribute, or resell this content. The Licensed Content is licensed "as-is." Microsoft does not support this Licensed Content in any way and Microsoft gives no express warranties, guarantees or conditions. Please report any unauthorized use of this content to [email protected] or by calling +1 800-785-3448. QuickStart Intelligence Lab Answer Keys: Joining Data from Multiple Tables 3 9. Leave Microsoft SQL Server Management Studio open for the next exercise. Results: After this exercise, you should have launched SQL Server Management Studio and created and executed an Inner Join. You should have also created and executed a Left Outer Join and a Right Outer Join. This is a unique copy of the course material identified by code cbbd3d2c-1b8b-4127-8769-5439cb34900e, and provided to you by Holly Stiver. It is illegal to reprint, redistribute, or resell this content. The Licensed Content is licensed "as-is." Microsoft does not support this Licensed Content in any way and Microsoft gives no express warranties, guarantees or conditions. Please report any unauthorized use of this content to [email protected] or by calling +1 800-785-3448. QuickStart Intelligence 4 Lab Answer Keys: Joining Data from Multiple Tables Exercise 2: Applying Joins for Typical Reporting Needs Task 1: Create and execute a query using a self join 1. 2. On the toolbar, click New Query. In the new, blank query window type the following T-SQL statement: SELECT pv1.ProductID, pv1.BusinessEntityID FROM Purchasing.ProductVendor pv1 INNER JOIN Purchasing.ProductVendor pv2 ON pv1.ProductID = pv2.ProductID AND pv1.BusinessEntityID <> pv2.BusinessEntityID ORDER BY pv1.ProductID; 3. 4. On the toolbar, click Execute. Review the results of the query and then close the query editor without saving the query. Task 2: Create and execute a query using a self join and the DISTINCT clause 1. 2. On the toolbar, click New Query. In the new, blank query window type the following T-SQL statement: SELECT DISTINCT pv1.ProductID, pv1.BusinessEntityID FROM Purchasing.ProductVendor pv1 INNER JOIN Purchasing.ProductVendor pv2 ON pv1.ProductID = pv2.ProductID AND pv1.BusinessEntityID <> pv2.BusinessEntityID ORDER BY pv1.ProductID; 3. 4. On the toolbar, click Execute. Review the results of the query and then close the query editor without saving the query. Task 3: Create and execute a non-equi join with an equality and a non-equality operator 1. 2. On the toolbar, click New Query. In the new, blank query window type the following T-SQL statement: SELECT DISTINCT p1.ProductSubcategoryID, p1.ListPrice FROM Production.Product p1 INNER JOIN Production.Product p2 ON p1.ProductSubcategoryID = p2.ProductSubcategoryID AND p1.ListPrice <> p2.ListPrice WHERE p1.ListPrice < $15 AND p2.ListPrice < $15 ORDER BY ProductSubcategoryID; 3. 4. 5. On the toolbar, click Execute. Review the results of the query and then close the query editor without saving the query. Leave Microsoft SQL Server Management Studio open for the next exercise. Results: After this exercise, you should have created and executed a query using a self join, created and executed a query using a self join and the DISTINCT clause, and created and executed a non-equi join with both an equality and non-equality operator. This is a unique copy of the course material identified by code cbbd3d2c-1b8b-4127-8769-5439cb34900e, and provided to you by Holly Stiver. It is illegal to reprint, redistribute, or resell this content. The Licensed Content is licensed "as-is." Microsoft does not support this Licensed Content in any way and Microsoft gives no express warranties, guarantees or conditions. Please report any unauthorized use of this content to [email protected] or by calling +1 800-785-3448. QuickStart Intelligence Lab Answer Keys: Joining Data from Multiple Tables 5 Exercise 3: Combining and Limiting Result Sets Task 1: Combine the result sets of two queries by using the UNION ALL operator 1. 2. On the toolbar, click New Query. In the new, blank query window type the following T-SQL statement: SELECT * FROM TestA UNION ALL SELECT * FROM TestB; 3. 4. On the toolbar, click Execute. Review the results of the query and then close the query editor without saving the query. Task 2: Limit result sets by using the EXCEPT clause with the SELECT statement 1. 2. On the toolbar, click New Query. In the new, blank query window type the following T-SQL statement: SELECT ProductID FROM Production.Product EXCEPT SELECT ProductID FROM Production.WorkOrder; 3. 4. On the toolbar, click Execute. Review the results of the query and then close the query editor without saving the query. Task 3: Limit result sets by using the INTERSECT clause with the SELECT statement 1. 2. On the toolbar, click New Query. In the new, blank query window type the following T-SQL statement: SELECT ProductID FROM Production.Product INTERSECT SELECT ProductID FROM Production.WorkOrder; 3. 4. On the toolbar, click Execute. Review the results of the query and then close the query editor without saving the query. Task 4: Limit result sets by using the TOP and TABLESAMPLE operators 1. 2. On the toolbar, click New Query. In the new, blank query window type the following T-SQL statement: SELECT TOP(15) PERCENT ProductID FROM Production.Product ORDER BY ProductID; 3. 4. 5. 6. On the toolbar, click Execute. Review the results of the query and then close the query editor without saving the query. On the toolbar, click New Query. In the new, blank query window type the following T-SQL statement: This is a unique copy of the course material identified by code cbbd3d2c-1b8b-4127-8769-5439cb34900e, and provided to you by Holly Stiver. It is illegal to reprint, redistribute, or resell this content. The Licensed Content is licensed "as-is." Microsoft does not support this Licensed Content in any way and Microsoft gives no express warranties, guarantees or conditions. Please report any unauthorized use of this content to [email protected] or by calling +1 800-785-3448. QuickStart Intelligence 6 Lab Answer Keys: Joining Data from Multiple Tables SELECT FirstName, LastName FROM Person.Person TABLESAMPLE (10 PERCENT); 7. 8. 9. On the toolbar, click Execute. Review the results of the query and then close the query editor without saving the query. Turn off the 2778A-NY-SQL-01 virtual machine and discard any changes. Results: After this exercise, you should have combined the result sets of two queries by using the UNION ALL operator. You should have also limited result sets by using the EXCEPT and INTERSECT clauses. Finally you should have limited result sets using the TOP and TABLESAMPLE operators. This is a unique copy of the course material identified by code cbbd3d2c-1b8b-4127-8769-5439cb34900e, and provided to you by Holly Stiver. It is illegal to reprint, redistribute, or resell this content. The Licensed Content is licensed "as-is." Microsoft does not support this Licensed Content in any way and Microsoft gives no express warranties, guarantees or conditions. Please report any unauthorized use of this content to [email protected] or by calling +1 800-785-3448. QuickStart Intelligence Lab Answer Keys: Working with Subqueries 1 Module 5 Lab Answer Keys: Working with Subqueries Contents: Exercise 1: Writing Basic Subqueries Exercise 2: Writing Correlated Subqueries Exercise 3: Comparing Subqueries with Joins and Temporary Tables Exercise 4: Using Common Table Expressions 2 3 4 5 This is a unique copy of the course material identified by code cbbd3d2c-1b8b-4127-8769-5439cb34900e, and provided to you by Holly Stiver. It is illegal to reprint, redistribute, or resell this content. The Licensed Content is licensed "as-is." Microsoft does not support this Licensed Content in any way and Microsoft gives no express warranties, guarantees or conditions. Please report any unauthorized use of this content to [email protected] or by calling +1 800-785-3448. QuickStart Intelligence 2 Lab Answer Keys: Working with Subqueries Lab: Working with Subqueries Exercise 1: Writing Basic Subqueries Task 1: Launch SQL Server Management Studio 1. 2. 3. 4. 5. 6. In the Lab Launcher, next to 2778A-NY-SQL-01, click Launch. Log on as Administrator with the password of Pa$$w0rd. Click Start | All Programs | Microsoft SQL Server 2008, and then click SQL Server Management Studio. In the Connect to Server dialog box, verify that Server type is set to Database Engine and Server name is set to NY-SQL-01, and then click Connect. Maximize Microsoft SQL Server Management Studio. In Object Explorer ensure that NY-SQL-01 (SQL Server 10.0.1600 - NY-SQL-01\Administrator) is expanded and then expand Databases. Task 2: Create a basic subquery 1. 2. Right-click the AdventureWorks2008 database and then click New Query. In the new, blank query window type the following T-SQL statement: SELECT Name FROM Production.Product WHERE ListPrice = (SELECT ListPrice FROM Production.Product WHERE Name = 'Chainring Bolts'); 3. 4. On the toolbar, click Execute. Review the results of the query and then close the query editor without saving the query. Task 3: Create a subquery with the EXISTS keyword 1. 2. On the toolbar, click New Query. In the new, blank query window type the following T-SQL statement: SELECT Name FROM Production.Product WHERE EXISTS (SELECT * FROM Production.ProductSubcategory WHERE ProductSubcategoryID = Production.Product.ProductSubcategoryID AND Name = 'Wheels'); 3. 4. 5. On the toolbar, click Execute. Review the results of the query and then close the query editor without saving the query. Leave Microsoft SQL Server Management Studio open for the next exercise. Results: After this exercise, you should have launched SQL Server Management Studio and created and executed a basic subquery. You should have also created and executed a subquery with the EXISTS keyword. This is a unique copy of the course material identified by code cbbd3d2c-1b8b-4127-8769-5439cb34900e, and provided to you by Holly Stiver. It is illegal to reprint, redistribute, or resell this content. The Licensed Content is licensed "as-is." Microsoft does not support this Licensed Content in any way and Microsoft gives no express warranties, guarantees or conditions. Please report any unauthorized use of this content to [email protected] or by calling +1 800-785-3448. QuickStart Intelligence Lab Answer Keys: Working with Subqueries 3 Exercise 2: Writing Correlated Subqueries Task 1: Create a correlated subquery 1. 2. On the toolbar, click New Query. In the new, blank query window type the following T-SQL statement: SELECT DISTINCT c.LastName, c.FirstName FROM Person.Person c JOIN HumanResources.Employee e ON e.BusinessEntityID = c.BusinessEntityID WHERE 5000.00 IN (SELECT Bonus FROM Sales.SalesPerson sp WHERE e.BusinessEntityID = sp.BusinessEntityID); 3. 4. On the toolbar, click Execute. Review the results of the query and then close the query editor without saving the query. Task 2: Create a correlated subquery with comparison operators 1. 2. On the toolbar, click New Query. In the new, blank query window type the following T-SQL statement: SELECT ProductID, OrderQty FROM Sales.SalesOrderDetail s1 WHERE s1.OrderQty < (SELECT AVG (s2.OrderQty) FROM Sales.SalesOrderDetail s2 WHERE s2.ProductID = s1.ProductID); 3. 4. 5. On the toolbar, click Execute. Review the results of the query and then close the query editor without saving the query. Leave Microsoft SQL Server Management Studio open for the next exercise. Results: After this exercise, you should have created a correlated subquery and also created a correlated subquery with comparison operators. This is a unique copy of the course material identified by code cbbd3d2c-1b8b-4127-8769-5439cb34900e, and provided to you by Holly Stiver. It is illegal to reprint, redistribute, or resell this content. The Licensed Content is licensed "as-is." Microsoft does not support this Licensed Content in any way and Microsoft gives no express warranties, guarantees or conditions. Please report any unauthorized use of this content to [email protected] or by calling +1 800-785-3448. QuickStart Intelligence 4 Lab Answer Keys: Working with Subqueries Exercise 3: Comparing Subqueries with Joins and Temporary Tables Task 1: Create a subquery and a join that produce the same result set 1. 2. On the toolbar, click New Query. In the new, blank query window type the following T-SQL statement: /* SELECT statement built using a subquery. */ SELECT Name FROM Production.Product WHERE ListPrice = (SELECT ListPrice FROM Production.Product WHERE Name = 'Chainring Bolts' ) /* SELECT statement built using a join that returns the same result set. */ SELECT Prd1.Name FROM Production.Product AS Prd1 JOIN Production.Product AS Prd2 ON (Prd1.ListPrice = Prd2.ListPrice) WHERE Prd2.Name = 'Chainring Bolts'; 3. 4. On the toolbar, click Execute. Review the results of the query and then close the query editor without saving the query. Task 2: Create a temporary table 1. 2. On the toolbar, click New Query. In the new, blank query window type the following T-SQL statement: CREATE TABLE #MyTempTable (cola INT PRIMARY KEY); INSERT INTO #MyTempTable VALUES (1); SELECT * FROM #MyTempTable; 3. 4. 5. On the toolbar, click Execute. Review the results of the query and then close the query editor without saving the query. Leave Microsoft SQL Server Management Studio open for the next exercise. Results: After this exercise you should have created a subquery and a join that produced the same result set. You should have also created a temporary table. This is a unique copy of the course material identified by code cbbd3d2c-1b8b-4127-8769-5439cb34900e, and provided to you by Holly Stiver. It is illegal to reprint, redistribute, or resell this content. The Licensed Content is licensed "as-is." Microsoft does not support this Licensed Content in any way and Microsoft gives no express warranties, guarantees or conditions. Please report any unauthorized use of this content to [email protected] or by calling +1 800-785-3448. QuickStart Intelligence Lab Answer Keys: Working with Subqueries 5 Exercise 4: Using Common Table Expressions Task 1: Create a common table expression (CTE) 1. 2. On the toolbar, click New Query. In the new, blank query window type the following T-SQL statement: WITH Sales_CTE (SalesPersonID, NumberOfOrders, MaxDate) AS ( SELECT SalesPersonID, COUNT(*), MAX(OrderDate) FROM Sales.SalesOrderHeader GROUP BY SalesPersonID ) SELECT E.BusinessEntityID, OS.NumberOfOrders, OS.MaxDate FROM HumanResources.Employee AS E JOIN Sales_CTE AS OS ON E.BusinessEntityID = OS.SalesPersonID ORDER BY E.BusinessEntityID; 3. 4. On the toolbar, click Execute. Review the results of the query and then close the query editor without saving the query. Task 2: Create a recursive query using a CTE 1. 2. On the toolbar, click New Query. In the new, blank query window type the following T-SQL statement: USE AdventureWorks2008; GO WITH Parts(AssemblyID, ComponentID, PerAssemblyQty, EndDate, ComponentLevel) AS ( SELECT b.ProductAssemblyID, b.ComponentID, b.PerAssemblyQty, b.EndDate, 0 AS ComponentLevel FROM Production.BillOfMaterials AS b WHERE b.ProductAssemblyID = 800 AND b.EndDate IS NULL UNION ALL SELECT bom.ProductAssemblyID, bom.ComponentID, p.PerAssemblyQty, bom.EndDate, ComponentLevel + 1 FROM Production.BillOfMaterials AS bom INNER JOIN Parts AS p ON bom.ProductAssemblyID = p.ComponentID AND bom.EndDate IS NULL ) SELECT AssemblyID, ComponentID, Name, PerAssemblyQty, EndDate, ComponentLevel FROM Parts AS p INNER JOIN Production.Product AS pr ON p.ComponentID = pr.ProductID ORDER BY ComponentLevel, AssemblyID, ComponentID; GO 3. 4. 5. On the toolbar, click Execute. Review the results of the query and then close the query editor without saving the query. Turn off the 2778A-NY-SQL-01 virtual machine and discard any changes. Results: After this exercise you should have created a common table expression. You should have also created a recursive query using a common table expression. This is a unique copy of the course material identified by code cbbd3d2c-1b8b-4127-8769-5439cb34900e, and provided to you by Holly Stiver. It is illegal to reprint, redistribute, or resell this content. The Licensed Content is licensed "as-is." Microsoft does not support this Licensed Content in any way and Microsoft gives no express warranties, guarantees or conditions. Please report any unauthorized use of this content to [email protected] or by calling +1 800-785-3448. QuickStart Intelligence Lab Answer Keys: Modifying Data in Tables 1 Module 6 Lab Answer Keys: Modifying Data in Tables Contents: Exercise 1: Inserting Data into Tables Exercise 2: Deleting Data from Tables Exercise 3: Updating Data in Tables Exercise 4: Working with Transactions 2 4 5 7 This is a unique copy of the course material identified by code cbbd3d2c-1b8b-4127-8769-5439cb34900e, and provided to you by Holly Stiver. It is illegal to reprint, redistribute, or resell this content. The Licensed Content is licensed "as-is." Microsoft does not support this Licensed Content in any way and Microsoft gives no express warranties, guarantees or conditions. Please report any unauthorized use of this content to [email protected] or by calling +1 800-785-3448. QuickStart Intelligence 2 Lab Answer Keys: Modifying Data in Tables Lab: Modifying Data Exercise 1: Inserting Data into Tables Task 1: Launch SQL Server Management Studio 1. 2. 3. 4. 5. 6. In the Lab Launcher, next to 2778A-NY-SQL-01, click Launch. Log on as Administrator with the password of Pa$$w0rd. Click Start | All Programs | Microsoft SQL Server 2008, and then click SQL Server Management Studio. In the Connect to Server dialog box, verify that Server type is set to Database Engine and Server name is set to NY-SQL-01, and then click Connect. Maximize Microsoft SQL Server Management Studio. In Object Explorer expand NY-SQL-01 | Databases. Task 2: Create an INSERT statement that adds values to a table 1. 2. Right-click the AdventureWorks2008 database and then click New Query. In the new, blank query window type the following T-SQL statement: INSERT INTO Production.UnitMeasure VALUES (N'F2', N'Square Feet', GETDATE()); 3. 4. On the toolbar, click Execute. Review the results of the query and then close the query editor without saving the query. Task 3: Create an INSERT statement that adds multiple rows to a table 1. 2. On the toolbar, click New Query. In the new, blank query window type the following T-SQL statement: INSERT INTO Person.PersonPhone VALUES (N'1705', N'864-555-2101', N'3', GETDATE()), (N'1706', N'712-555-0118', N'1', GETDATE()); 3. 4. On the toolbar, click Execute. Review the results of the query and then close the query editor without saving the query. Task 4: Create an INSERT statement using the OUTPUT syntax 1. 2. On the toolbar, click New Query. In the new, blank query window type the following T-SQL statement: DECLARE @MyTableVar table( ScrapReasonID smallint, Name varchar(50), ModifiedDate datetime); INSERT Production.ScrapReason OUTPUT INSERTED.ScrapReasonID, INSERTED.Name, INSERTED.ModifiedDate INTO @MyTableVar VALUES (N'Operator error', GETDATE()); --Display the result set of the table variable. SELECT ScrapReasonID, Name, ModifiedDate FROM @MyTableVar; --Display the result set of the table. SELECT ScrapReasonID, Name, ModifiedDate FROM Production.ScrapReason; This is a unique copy of the course material identified by code cbbd3d2c-1b8b-4127-8769-5439cb34900e, and provided to you by Holly Stiver. It is illegal to reprint, redistribute, or resell this content. The Licensed Content is licensed "as-is." Microsoft does not support this Licensed Content in any way and Microsoft gives no express warranties, guarantees or conditions. Please report any unauthorized use of this content to [email protected] or by calling +1 800-785-3448. QuickStart Intelligence Lab Answer Keys: Modifying Data in Tables 3 3. 4. 5. On the toolbar, click Execute. Review the results of the query and then close the query editor without saving the query. Leave Microsoft SQL Server Management Studio open for the next exercise. Results: After this exercise, you should have launched SQL Server Management Studio, created and executed an INSERT statement that adds values to a table, created and executed an INSERT statement using the INTO syntax, and created and executed an INSERT statement using the OUTPUT syntax. This is a unique copy of the course material identified by code cbbd3d2c-1b8b-4127-8769-5439cb34900e, and provided to you by Holly Stiver. It is illegal to reprint, redistribute, or resell this content. The Licensed Content is licensed "as-is." Microsoft does not support this Licensed Content in any way and Microsoft gives no express warranties, guarantees or conditions. Please report any unauthorized use of this content to [email protected] or by calling +1 800-785-3448. QuickStart Intelligence 4 Lab Answer Keys: Modifying Data in Tables Exercise 2: Deleting Data from Tables Task 1: Create a DELETE statement using the WHERE syntax 1. 2. On the toolbar, click New Query. In the new, blank query window type the following T-SQL statement: DELETE FROM Production.ProductCostHistory WHERE StandardCost > 1000.00; 3. 4. On the toolbar, click Execute. Review the results of the query and then close the query editor without saving the query. Task 2: Create a DELETE statement using the FROM syntax 1. 2. On the toolbar, click New Query. In the new, blank query window type the following T-SQL statement: DELETE FROM Sales.SalesPersonQuotaHistory FROM Sales.SalesPersonQuotaHistory AS spqh INNER JOIN Sales.SalesPerson AS sp ON spqh.BusinessEntityID = sp.BusinessEntityID WHERE sp.SalesYTD > 1000000.00; 3. 4. On the toolbar, click Execute. Review the results of the query and then close the query editor without saving the query. Task 3: Create a DELETE statement using the OUTPUT syntax 1. 2. On the toolbar, click New Query. In the new, blank query window type the following T-SQL statement: DELETE Sales.ShoppingCartItem OUTPUT DELETED.*; 3. 4. On the toolbar, click Execute. Review the results of the query and then close the query editor without saving the query. Task 4: Create a TRUNCATE TABLE statement 1. 2. On the toolbar, click New Query. In the new, blank query window type the following T-SQL statement: SELECT COUNT(*) AS BeforeTruncateCount FROM Production.TransactionHistory; TRUNCATE TABLE Production.TransactionHistory; SELECT COUNT(*) AS AfterTruncateCount FROM Production.TransactionHistory; 3. 4. 5. On the toolbar, click Execute. Review the results of the query and then close the query editor without saving the query. Leave Microsoft SQL Server Management Studio open for the next exercise. Results: After this exercise, you should have created and executed a DELETE statement using the WHERE syntax and created and executed a DELETE statement using the FROM syntax. You should have also created and executed a DELETE statement using the OUTPUT syntax and then created and executed a TRUNCATE TABLE statement. This is a unique copy of the course material identified by code cbbd3d2c-1b8b-4127-8769-5439cb34900e, and provided to you by Holly Stiver. It is illegal to reprint, redistribute, or resell this content. The Licensed Content is licensed "as-is." Microsoft does not support this Licensed Content in any way and Microsoft gives no express warranties, guarantees or conditions. Please report any unauthorized use of this content to [email protected] or by calling +1 800-785-3448. QuickStart Intelligence Lab Answer Keys: Modifying Data in Tables 5 Exercise 3: Updating Data in Tables Task 1: Create an UPDATE statement using the SET syntax 1. 2. On the toolbar, click New Query. In the new, blank query window type the following T-SQL statement: UPDATE Sales.SalesPerson SET Bonus = 6000, CommissionPct = .10, SalesQuota = NULL; 3. 4. On the toolbar, click Execute. Review the results of the query and then close the query editor without saving the query. Task 2: Create an UPDATE statement using the WHERE syntax 1. 2. On the toolbar, click New Query. In the new, blank query window type the following T-SQL statement: UPDATE Production.Product SET Color = N'Metallic Red' WHERE Name LIKE N'Road-250%' AND Color = N'Red'; 3. 4. On the toolbar, click Execute. Review the results of the query and then close the query editor without saving the query. Task 3: Create an UPDATE statement using the FROM syntax 1. 2. On the toolbar, click New Query. In the new, blank query window type the following T-SQL statement: UPDATE Sales.SalesPerson SET SalesYTD = SalesYTD + SubTotal FROM Sales.SalesPerson AS sp JOIN Sales.SalesOrderHeader AS so ON sp.BusinessEntityID = so.SalesPersonID AND so.OrderDate = (SELECT MAX(OrderDate) FROM Sales.SalesOrderHeader WHERE SalesPersonID = sp.BusinessEntityID); 3. 4. On the toolbar, click Execute. Review the results of the query and then close the query editor without saving the query. Task 4: Create an UPDATE statement using the OUTPUT syntax 1. 2. On the toolbar, click New Query. In the new, blank query window type the following T-SQL statement: DECLARE @MyTableVar table( EmpID INT NOT NULL, OldVacationHours INT, NewVacationHours INT, ModifiedDate DATETIME); UPDATE TOP (10) HumanResources.Employee SET VacationHours = VacationHours * 1.25 OUTPUT INSERTED.BusinessEntityID, DELETED.VacationHours, INSERTED.VacationHours, INSERTED.ModifiedDate INTO @MyTableVar; This is a unique copy of the course material identified by code cbbd3d2c-1b8b-4127-8769-5439cb34900e, and provided to you by Holly Stiver. It is illegal to reprint, redistribute, or resell this content. The Licensed Content is licensed "as-is." Microsoft does not support this Licensed Content in any way and Microsoft gives no express warranties, guarantees or conditions. Please report any unauthorized use of this content to [email protected] or by calling +1 800-785-3448. QuickStart Intelligence 6 Lab Answer Keys: Modifying Data in Tables SELECT EmpID, OldVacationHours, NewVacationHours, ModifiedDate FROM @MyTableVar; 3. 4. 5. On the toolbar, click Execute. Review the results of the query and then close the query editor without saving the query. Leave Microsoft SQL Server Management Studio open for the next exercise. Results: After this exercise you should have created and executed an UPDATE statement using the SET syntax, created and executed an UPDATE statement using the WHERE IN (SELECT) syntax, and created and executed an UPDATE statement using the FROM syntax. You should have also created and executed an UPDATE statement using the OUTPUT syntax. This is a unique copy of the course material identified by code cbbd3d2c-1b8b-4127-8769-5439cb34900e, and provided to you by Holly Stiver. It is illegal to reprint, redistribute, or resell this content. The Licensed Content is licensed "as-is." Microsoft does not support this Licensed Content in any way and Microsoft gives no express warranties, guarantees or conditions. Please report any unauthorized use of this content to [email protected] or by calling +1 800-785-3448. QuickStart Intelligence Lab Answer Keys: Modifying Data in Tables 7 Exercise 4: Working with Transactions Task 1: Create a simple transaction 1. 2. On the toolbar, click New Query. In the new, blank query window type the following T-SQL statement: BEGIN TRANSACTION CandidateDelete; DELETE FROM HumanResources.JobCandidate WHERE JobCandidateID = 13; COMMIT TRANSACTION CandidateDelete; 3. 4. On the toolbar, click Execute. Review the results of the query and then close the query editor without saving the query. Task 2: Set a transaction isolation level 1. 2. On the toolbar, click New Query. In the new, blank query window type the following T-SQL statement: SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; BEGIN TRANSACTION; SELECT * FROM HumanResources.EmployeePayHistory; SELECT * FROM HumanResources.Department; COMMIT TRANSACTION; 3. 4. 5. On the toolbar, click Execute. Review the results of the query and then close the query editor without saving the query. Turn off the 2778A-NY-SQL-01 virtual machine and discard any changes. Results: After this exercise, you should have created a simple transaction. You should have also set the transaction isolation level for a new transaction. This is a unique copy of the course material identified by code cbbd3d2c-1b8b-4127-8769-5439cb34900e, and provided to you by Holly Stiver. It is illegal to reprint, redistribute, or resell this content. The Licensed Content is licensed "as-is." Microsoft does not support this Licensed Content in any way and Microsoft gives no express warranties, guarantees or conditions. Please report any unauthorized use of this content to [email protected] or by calling +1 800-785-3448. QuickStart Intelligence Lab Answer Keys: Querying Metadata, XML, and Full-Text Indexes 1 Module 7 Lab Answer Keys: Querying Metadata, XML, and Full-Text Indexes Contents: Exercise 1: Querying Metadata Exercise 2: Querying XML Data Exercise 3: Creating and Querying Full-Text Indexes 2 4 5 This is a unique copy of the course material identified by code cbbd3d2c-1b8b-4127-8769-5439cb34900e, and provided to you by Holly Stiver. It is illegal to reprint, redistribute, or resell this content. The Licensed Content is licensed "as-is." Microsoft does not support this Licensed Content in any way and Microsoft gives no express warranties, guarantees or conditions. Please report any unauthorized use of this content to [email protected] or by calling +1 800-785-3448. QuickStart Intelligence 2 Lab Answer Keys: Querying Metadata, XML, and Full-Text Indexes Lab: Querying Metadata, XML, and Full-Text Indexes Exercise 1: Querying Metadata Task 1: Launch SQL Server Management Studio 1. 2. 3. 4. 5. 6. In the Lab Launcher, next to 2778A-NY-SQL-01, click Launch. Log on as Administrator with the password of Pa$$w0rd. Click Start | All Programs | Microsoft SQL Server 2008, and then click SQL Server Management Studio. In the Connect to Server dialog box, verify that Server type is set to Database Engine and Server name is set to NY-SQL-01, and then click Connect. Maximize Microsoft SQL Server Management Studio. In Object Explorer, ensure that NY-SQL-01 (SQL Server 10.0.1600 - NY-SQL-01\Administrator) is expanded, and then expand Databases. Task 2: Query metadata using system catalog views 1. 2. Right-click the AdventureWorks2008 database, and then click New Query. In the new, blank query window, type the following T-SQL statement: SELECT c.name FROM sys.columns c JOIN sys.tables t ON t.object_id = c.object_id WHERE t.name = 'Person' 3. 4. On the toolbar, click Execute. Review the results of the query and then close the query editor without saving the query. Task 3: Query metadata using the Information Schema 1. 2. Right-click the AdventureWorks2008 database, and then click New Query. In the new, blank query window, type the following T-SQL statement: SELECT * FROM INFORMATION_SCHEMA.columns WHERE TABLE_NAME = 'Address' 3. 4. On the toolbar, click Execute. Review the results of the query and then close the query editor without saving the query. Task 4: Query metadata using Dynamic Management Views 1. 2. Right-click the AdventureWorks2008 database, and then click New Query. In the new, blank query window, type the following T-SQL statement: SELECT count(*), Command, AVG(total_elapsed_time) AS AvgTime FROM sys.dm_exec_requests GROUP BY Command 3. 4. On the toolbar, click Execute. Review the results of the query and then close the query editor without saving the query. This is a unique copy of the course material identified by code cbbd3d2c-1b8b-4127-8769-5439cb34900e, and provided to you by Holly Stiver. It is illegal to reprint, redistribute, or resell this content. The Licensed Content is licensed "as-is." Microsoft does not support this Licensed Content in any way and Microsoft gives no express warranties, guarantees or conditions. Please report any unauthorized use of this content to [email protected] or by calling +1 800-785-3448. QuickStart Intelligence Lab Answer Keys: Querying Metadata, XML, and Full-Text Indexes 3 Task 5: Query metadata using System Stored Procedures 1. 2. Right-click the AdventureWorks2008 database, and then click New Query. In the new, blank query window, type the following T-SQL statement: EXEC sp_columns @table_name = N'Employee', @table_owner = N'HumanResources'; 3. 4. On the toolbar, click Execute. Review the results of the query and then close the query editor without saving the query. Results: After this exercise, you should have launched SQL Server Management Studio and queried metadata using system catalog views, the information schema, dynamic management views, and system stored procedures. This is a unique copy of the course material identified by code cbbd3d2c-1b8b-4127-8769-5439cb34900e, and provided to you by Holly Stiver. It is illegal to reprint, redistribute, or resell this content. The Licensed Content is licensed "as-is." Microsoft does not support this Licensed Content in any way and Microsoft gives no express warranties, guarantees or conditions. Please report any unauthorized use of this content to [email protected] or by calling +1 800-785-3448. QuickStart Intelligence 4 Lab Answer Keys: Querying Metadata, XML, and Full-Text Indexes Exercise 2: Querying XML Data Task 1: Creating XML output using for XML 1. 2. Right-click the AdventureWorks2008 database. and then click New Query. In the new, blank query window, type the following T-SQL statement: SELECT CustomerID, SalesOrderID, Status FROM Sales.SalesOrderHeader OrderHeader ORDER BY CustomerID FOR XML AUTO 3. 4. On the toolbar, click Execute. Review the results of the query and then close the query editor without saving the query. Task 2: Querying XML data using OPENXML 1. 2. 3. In the SQL Server Management Studio File menu, click Open | File. The File Open dialog appears. Select E:\Mod07\Labfiles\Exercise2Task2, and then click Open. Before the SELECT statement, type the following statement: EXEC sp_xml_preparedocument @idoc OUTPUT, @doc; 4. 5. On the toolbar, click Execute. Review the results of the query and then close the query editor without saving the query. Results: After this exercise, you should have created a query of relational data and produced the output in XML format. You should have also created a query that converts data in XML format to a relational format. This is a unique copy of the course material identified by code cbbd3d2c-1b8b-4127-8769-5439cb34900e, and provided to you by Holly Stiver. It is illegal to reprint, redistribute, or resell this content. The Licensed Content is licensed "as-is." Microsoft does not support this Licensed Content in any way and Microsoft gives no express warranties, guarantees or conditions. Please report any unauthorized use of this content to [email protected] or by calling +1 800-785-3448. QuickStart Intelligence Lab Answer Keys: Querying Metadata, XML, and Full-Text Indexes 5 Exercise 3: Creating and Querying Full-Text Indexes Task 1: Creating a full-text index 1. 2. 3. 4. 5. 6. 7. 8. 9. 10. 11. 12. 13. 14. 15. 16. 17. 18. 19. 20. 21. 22. 23. 24. In the Object Explorer, expand NY-SQL-01 | Databases | AdventureWorks2008 | Storage. In the Object Explorer, right-click Full Text Catalogs and click New Full-Text Catalog. The New Full-Text Catalog dialog appears. In the Full-text catalog name field, type ProductDescriptionCatalog. In the Owner field, type dbo. Click OK. In the Object Explorer, expand Full Text Catalogs. Right-click ProductDescriptionCatalog, and then click Properties. In Select a page, click Tables/Views. In the All eligible table/view objects in this database list, select Production.ProductDescription. Click -> to add the table to the Table/view objects assigned to the catalog list. In the Eligible columns list, select Description. In Select a page, click Population Schedule. Click New. In the Name field, type EveryHour. In the Schedule type list, select Recurring. In the Occurs list, ensure that Daily is selected. In the Daily frequency list, select Occurs every 1 hour. In the Starting at field, enter 12:01:00 AM. In the Ending at field, enter 11:59:00 PM. Click OK. In the Full-Text Catalog Properties dialog, click OK. In the Object Explorer, right-click on ProductDescriptionCatalog, and then click Rebuild. In the Rebuild Full-Text Catalog dialog, click OK. Click Close. Task 2: Querying the full-text index using FREETEXT 1. 2. Right-click the AdventureWorks2008 database, and then click New Query. In the new, blank query window. type the following T-SQL statement: SELECT * FROM Production.ProductDescription WHERE FREETEXT(Description, 'lightest') 3. 4. On the toolbar, click Execute. Review the results of the query and then close the query editor without saving the query. Task 3: Querying the full-text index using CONTAINS 1. 2. Right-click the AdventureWorks2008 database, and then click New Query. In the new, blank query window, type the following T-SQL statement: SELECT * FROM Production.ProductDescription WHERE CONTAINS(Description, 'lightest NEAR best') 3. 4. 5. On the toolbar, click Execute. Review the results of the query and then close the query editor without saving the query. Turn off 2778A-NY-SQL-01 virtual machine and discard any changes. This is a unique copy of the course material identified by code cbbd3d2c-1b8b-4127-8769-5439cb34900e, and provided to you by Holly Stiver. It is illegal to reprint, redistribute, or resell this content. The Licensed Content is licensed "as-is." Microsoft does not support this Licensed Content in any way and Microsoft gives no express warranties, guarantees or conditions. Please report any unauthorized use of this content to [email protected] or by calling +1 800-785-3448. QuickStart Intelligence 6 Lab Answer Keys: Querying Metadata, XML, and Full-Text Indexes Results: After this exercise, you should have created a new full-text Index and queried that Index using both the FREETEXT and CONTAINS predicates. This is a unique copy of the course material identified by code cbbd3d2c-1b8b-4127-8769-5439cb34900e, and provided to you by Holly Stiver. It is illegal to reprint, redistribute, or resell this content. The Licensed Content is licensed "as-is." Microsoft does not support this Licensed Content in any way and Microsoft gives no express warranties, guarantees or conditions. Please report any unauthorized use of this content to [email protected] or by calling +1 800-785-3448. QuickStart Intelligence Lab Answer Keys: Using Programming Objects for Data Retrieval 1 Module 8 Lab Answer Keys: Using Programming Objects for Data Retrieval Contents: Exercise 1: Creating Views Exercise 2: Creating User-Defined Functions Exercise 3: Creating Stored Procedures Exercise 4: Writing Distributed Queries 2 3 5 6 This is a unique copy of the course material identified by code cbbd3d2c-1b8b-4127-8769-5439cb34900e, and provided to you by Holly Stiver. It is illegal to reprint, redistribute, or resell this content. The Licensed Content is licensed "as-is." Microsoft does not support this Licensed Content in any way and Microsoft gives no express warranties, guarantees or conditions. Please report any unauthorized use of this content to [email protected] or by calling +1 800-785-3448. QuickStart Intelligence 2 Lab Answer Keys: Using Programming Objects for Data Retrieval Lab: Using Programming Objects for Data Retrieval Exercise 1: Creating Views Task 1: Launch SQL Server Management Studio 1. 2. 3. 4. 5. 6. In the Lab Launcher, next to 2778A-NY-SQL-01, click Launch. Log on as Administrator with the password of Pa$$w0rd. Click Start | All Programs | Microsoft SQL Server 2008, and then click SQL Server Management Studio. In the Connect to Server dialog box, verify that Server type is set to Database Engine and Server name is set to NY-SQL-01, and then click Connect. Maximize Microsoft SQL Server Management Studio. In Object Explorer, ensure that NY-SQL-01 (SQL Server 10.0.1600 - NY-SQL-01\Administrator) is expanded and then expand Databases. Task 2: Create a view to select only the ID and Name columns from the Person table 1. 2. Right-click the AdventureWorks2008 database, and then click New Query. In the new, blank query window, type the following T-SQL statement: CREATE VIEW [dbo].[vwPersonName] AS SELECT BusinessEntityID, FirstName, MiddleName, LastName FROM Person.Person GO 3. 4. 5. 6. On the toolbar, click Execute. Review the results of the query and then close the query editor without saving the query. Right-click the AdventureWorks2008 database, and then click New Query. In the new, blank query window, type the following T-SQL statement: SELECT * FROM vwPersonName 7. 8. On the toolbar, click Execute. Review the results of the query and then close the query editor without saving the query. Results: After this exercise, you should have created a view to restrict the number of columns returned for a table. This is a unique copy of the course material identified by code cbbd3d2c-1b8b-4127-8769-5439cb34900e, and provided to you by Holly Stiver. It is illegal to reprint, redistribute, or resell this content. The Licensed Content is licensed "as-is." Microsoft does not support this Licensed Content in any way and Microsoft gives no express warranties, guarantees or conditions. Please report any unauthorized use of this content to [email protected] or by calling +1 800-785-3448. QuickStart Intelligence Lab Answer Keys: Using Programming Objects for Data Retrieval 3 Exercise 2: Creating User-Defined Functions Task 1: Create a scalar valued user-defined function 1. 2. Right-click the AdventureWorks2008 database and then click New Query. In the new, blank query window, type the following T-SQL statement: CREATE FUNCTION fnGetDate ( @input datetime ) RETURNS varchar(10) AS BEGIN DECLARE @result varchar(10) SET @result = CONVERT(varchar(10), @input, 103) RETURN @result END GO 3. 4. 5. 6. On the toolbar, click Execute. Review the results of the query and then close the query editor without saving the query. Right-click the AdventureWorks2008 database, and then click New Query. In the new, blank query window, type the following T-SQL statement: SELECT GETDATE() SELECT dbo.fnGetDate(GETDATE()) 7. 8. On the toolbar, click Execute. Review the results of the query and then close the query editor without saving the query. Task 2: Create an in-line table-valued user-defined function 1. 2. Right-click the AdventureWorks2008 database, and then click New Query. In the new, blank query window, type the following T-SQL statement: CREATE FUNCTION fnGetVendorPurchaseOrders ( @VendorID int ) RETURNS TABLE AS RETURN ( SELECT PurchaseOrderID, RevisionNumber, [Status], OrderDate, ShipDate, SubTotal, TotalDue FROM Purchasing.PurchaseOrderHeader WHERE VendorID = @VendorID ) GO This is a unique copy of the course material identified by code cbbd3d2c-1b8b-4127-8769-5439cb34900e, and provided to you by Holly Stiver. It is illegal to reprint, redistribute, or resell this content. The Licensed Content is licensed "as-is." Microsoft does not support this Licensed Content in any way and Microsoft gives no express warranties, guarantees or conditions. Please report any unauthorized use of this content to [email protected] or by calling +1 800-785-3448. QuickStart Intelligence 4 Lab Answer Keys: Using Programming Objects for Data Retrieval 3. 4. 5. 6. On the toolbar, click Execute. Review the results of the query and then close the query editor without saving the query. Right-click the AdventureWorks2008 database, and then click New Query. In the new, blank query window, type the following T-SQL statement: SELECT * FROM fnGetVendorPurchaseOrders(1624) 7. 8. On the toolbar, click Execute. Review the results of the query and then close the query editor without saving the query. Results: After this exercise, you should have launched SQL Server Management Studio and created both a scalar and table-valued user-defined function. This is a unique copy of the course material identified by code cbbd3d2c-1b8b-4127-8769-5439cb34900e, and provided to you by Holly Stiver. It is illegal to reprint, redistribute, or resell this content. The Licensed Content is licensed "as-is." Microsoft does not support this Licensed Content in any way and Microsoft gives no express warranties, guarantees or conditions. Please report any unauthorized use of this content to [email protected] or by calling +1 800-785-3448. QuickStart Intelligence Lab Answer Keys: Using Programming Objects for Data Retrieval 5 Exercise 3: Creating Stored Procedures Task 1: Create a stored procedure to select the Name columns from the Person table for a given ID 1. 2. Right-click the AdventureWorks2008 database, and then click New Query. In the new, blank query window, type the following T-SQL statement: CREATE Procedure pGetPersonName @PersonID nvarchar(15) AS SELECT BusinessEntityID, FirstName, MiddleName, LastName FROM dbo.vwPersonName WHERE BusinessEntityID = @PersonID GO 3. 4. On the toolbar, click Execute. Review the results of the query and then close the query editor without saving the query. Results: After this exercise, you should have created a stored procedure that returns a person’s name using their ID. This is a unique copy of the course material identified by code cbbd3d2c-1b8b-4127-8769-5439cb34900e, and provided to you by Holly Stiver. It is illegal to reprint, redistribute, or resell this content. The Licensed Content is licensed "as-is." Microsoft does not support this Licensed Content in any way and Microsoft gives no express warranties, guarantees or conditions. Please report any unauthorized use of this content to [email protected] or by calling +1 800-785-3448. QuickStart Intelligence 6 Lab Answer Keys: Using Programming Objects for Data Retrieval Exercise 4: Writing Distributed Queries Task 1: Enabling distributed queries 1. 2. Right-click the AdventureWorks2008 database, and then click New Query. In the new, blank query window, type the following T-SQL statement: sp_configure 'show advanced options', 1 reconfigure GO sp_configure 'Ad Hoc Distributed Queries', 1 reconfigure 3. 4. On the toolbar, click Execute. Review the results of the query and then close the query editor without saving the query. Task 2: Executing an ad hoc distributed query against a Microsoft Office Excel spreadsheet 1. 2. Right-click the AdventureWorks2008 database, and then click New Query. In the new, blank query window, type the following T-SQL statement: SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=E:\Mod08\Labfiles\SalesSummary2008.xls', 'SELECT Country, TotalSales FROM [Sheet1$]') 3. 4. On the toolbar, click Execute. Review the results of the query and then close the query editor without saving the query. Task 3: Create a linked server to a Microsoft Office Excel spreadsheet 1. 2. Right-click the AdventureWorks2008 database. and then click New Query. In the new, blank query window. type the following T-SQL statement: EXEC sp_addlinkedserver 'SalesSummary', 'Jet 4.0', 'Microsoft.Jet.OLEDB.4.0', 'E:\Mod08\Labfiles\SalesSummary2008.xls', NULL, 'Excel 8.0' 3. 4. On the toolbar, click Execute. Review the results of the query and then close the query editor without saving the query. Task 4: Query the linked Microsoft Office Excel spreadsheet 1. 2. Right-click the AdventureWorks2008 database, and then click New Query. In the new, blank query window, type the following T-SQL statement: SELECT * FROM SalesSummary...Sheet1$ 3. 4. 5. On the toolbar, click Execute. Review the results of the query and then close the query editor without saving the query. Turn off the 2778A-NY-SQL-01 virtual machine and discard any changes. This is a unique copy of the course material identified by code cbbd3d2c-1b8b-4127-8769-5439cb34900e, and provided to you by Holly Stiver. It is illegal to reprint, redistribute, or resell this content. The Licensed Content is licensed "as-is." Microsoft does not support this Licensed Content in any way and Microsoft gives no express warranties, guarantees or conditions. Please report any unauthorized use of this content to [email protected] or by calling +1 800-785-3448. QuickStart Intelligence Lab Answer Keys: Using Programming Objects for Data Retrieval 7 Results: After this exercise, you should have used an ad hoc distributed query to query data from a spreadsheet. You have also created a linked server and queried it using a four part name. This is a unique copy of the course material identified by code cbbd3d2c-1b8b-4127-8769-5439cb34900e, and provided to you by Holly Stiver. It is illegal to reprint, redistribute, or resell this content. The Licensed Content is licensed "as-is." Microsoft does not support this Licensed Content in any way and Microsoft gives no express warranties, guarantees or conditions. Please report any unauthorized use of this content to [email protected] or by calling +1 800-785-3448. QuickStart Intelligence Lab Answer Keys: Using Advanced Techniques 1 Module 9 Lab Answer Keys: Using Advanced Techniques Contents: Exercise 1: Using Execution Plans Exercise 2: Converting Data Types Exercise 3: Implementing a Hierarchy Exercise 4: Using Cursors and Set-Based Queries 2 3 6 7 This is a unique copy of the course material identified by code cbbd3d2c-1b8b-4127-8769-5439cb34900e, and provided to you by Holly Stiver. It is illegal to reprint, redistribute, or resell this content. The Licensed Content is licensed "as-is." Microsoft does not support this Licensed Content in any way and Microsoft gives no express warranties, guarantees or conditions. Please report any unauthorized use of this content to [email protected] or by calling +1 800-785-3448. QuickStart Intelligence 2 Lab Answer Keys: Using Advanced Techniques Lab: Using Advanced Techniques Exercise 1: Using Execution Plans Task 1: Start the 2778A-NY-SQL-01 virtual machine and log on as Student 1. 2. In the Lab Launcher, next to 2778A-NY-SQL-01, click Launch. Log on to NY-SQL-01 as Student using the password Pa$$w0rd. Task 2: View an estimated execution plan 1. 2. 3. 4. 5. 6. 7. 8. On the desktop, click Start, point to All Programs, point to Microsoft SQL Server 2008, and then click SQL Server Management Studio. The Connect to Server dialog box appears. Click Connect. On the File menu, point to Open and then click File. The Open File dialog box appears. Browse to E:\MOD09\Labfiles\Srarter\Exercise01. Click Order-ProductID.sql and then click Open. In the database list on the toolbar, click AdventureWorks2008. In the query pane, right-click anywhere on the query and then click Display Estimated Execution Plan. On the Execution Plan tab, take note of the flow of the execution plan and the cost of the operations. Task 3: View an actual execution plan 1. 2. 3. 4. In the query pane, right-click anywhere on the query, and then click Include Actual Execution Plan. On the toolbar, click Execute. In the Results pane, click the Execution Plan tab. Take note of the flow of the execution plan and the cost of the operations. Task 4: Save an execution plan 1. 2. 3. 4. 5. 6. 7. 8. Right-click anywhere in the Execution Plan tab, and then click Save Execution Plan As. The Save As dialog box appears. Browse to E:\MOD09\Labfiles\Starter. In the File name field, type Order-ProductID and then click Save. Right-click anywhere in the Execution Plan tab, and then click Show Execution Plan XML. On the File menu, click Save Execution Plan.xml As. The Save File As dialog box appears. Browse to E:\MOD09\Labfiles\Starter. In the File name field, type Order-ProductID.xml, and then click Save. Close the Order-ProductID.xml pane. Results: After this exercise, you should have viewed estimated and actual execution plans for a query, and saved an execution plan to an XML file. This is a unique copy of the course material identified by code cbbd3d2c-1b8b-4127-8769-5439cb34900e, and provided to you by Holly Stiver. It is illegal to reprint, redistribute, or resell this content. The Licensed Content is licensed "as-is." Microsoft does not support this Licensed Content in any way and Microsoft gives no express warranties, guarantees or conditions. Please report any unauthorized use of this content to [email protected] or by calling +1 800-785-3448. QuickStart Intelligence Lab Answer Keys: Using Advanced Techniques 3 Exercise 2: Converting Data Types Task 1: Use implicit conversions 1. 2. 3. 4. In Object Explorer, expand Database | AdventureWorks2008 | Tables | HumanResources.Employee | Columns. Notice that the SalariedFlag column has a data type of bit. Right-click AdventureWorks2008, and then click New Query. In the query pane, type the following query: SELECT NationalIDNumber, LoginID, SalariedFlag FROM HumanResources.Employee WHERE SalariedFlag = 'False' 5. 6. 7. 8. 9. On the toolbar, click Execute. In the Results pane, review the result set and noticed that the values in the SalariedFlag column are 0. In Object Explorer, expand AdventureWorks2008 | Tables | Production.Product | Columns. Notice that the StandardCost and ListPrice columns have a data type of money. Right-click AdventureWorks2008, and then click New Query. 10. In the query pane, type the following query: DECLARE @NewListPrice nvarchar(10) = '39.99' DECLARE @NewStandardCost float = 12.45 UPDATE Production.Product SET ListPrice = @NewListPrice, StandardCost = @NewStandardCost WHERE ProductID = '2' 11. On the toolbar, click Execute. 12. On the toolbar, click New Query. 13. In the query pane, type the following query: SELECT Name, StandardCost, ListPrice FROM Production.Product WHERE ProductID = '2' 14. On the toolbar click, Execute. 15. In the results pane, notice that the StandardCost and ListPrice columns have been updated. 16. On the File menu, point to Open, and then click File. 17. The Open File dialog box appears. Browse to E:\MOD09\Labfiles\Starter\Exercise02. 18. Click UpdateDemographics.sql, and then click Open. 19. In the query pane, notice that the @NewDemographics variable has a data type of nvarchar, and contains a valid XML string. 20. On the toolbar, click Execute. This is a unique copy of the course material identified by code cbbd3d2c-1b8b-4127-8769-5439cb34900e, and provided to you by Holly Stiver. It is illegal to reprint, redistribute, or resell this content. The Licensed Content is licensed "as-is." Microsoft does not support this Licensed Content in any way and Microsoft gives no express warranties, guarantees or conditions. Please report any unauthorized use of this content to [email protected] or by calling +1 800-785-3448. QuickStart Intelligence 4 Lab Answer Keys: Using Advanced Techniques 21. On the toolbar, click New Query. 22. In the query pane, type the following query: SELECT FirstName, MiddleName, LastName, Demographics FROM Person.Person WHERE BusinessEntityID = 15 23. On the toolbar, click Execute. 24. In the Results pane, click the link in the Demographics column. 25. The Demographics.xml window opens. Notice that the string has been converted and stored as XML. 26. Close the Demographics.xml window. Task 2: Use explicit conversions 1. 2. 3. 4. 5. 6. On the File menu, point to Open and then click File. The Open File dialog box appears. Browse to E:\MOD09\Labfiles\Starter\Exercise02. Click EmployeePayRate.sql, and then click Open. On the toolbar, click Execute. In the Messages pane, take note of the error that occurs. In the query pane, modify the line beginning with SELECT so that it reads as follows: SELECT (p.FirstName + ' ' + p.LastName + ' ' + CONVERT(nvarchar, s.Rate)) 7. 8. 9. On the toolbar, click Execute. In the Results pane, notice that there is a single column of employees and pay rates combined. On the File menu, point to Open, and then click File. 10. The Open File dialog box appears. Browse to E:\MOD09\Labfiles\Starter\Exercise02. 11. Click PayRateLastChange.sql, and then click Open. 12. On the toolbar, click Execute. 13. In the Results pane, notice that the query runs successfully, but the last column does not show both the pay rate and date. 14. In the query pane, modify the line beginning with SELECT so that it reads as follows: SELECT p.FirstName, p.LastName, (CAST(s.Rate AS nvarchar) + ' ' + s.RateChangeDate) 15. On the toolbar, click Execute. 16. In the Messages pane, take note of the error. 17. In the query pane, modify the line beginning with SELECT so that it reads as follows: SELECT p.FirstName, p.LastName, (CAST(s.Rate AS nvarchar) + ' ' + CAST(s.RateChangeDate as nvarchar)) 18. On the toolbar, click Execute. 19. In the Results pane, notice that the query now succeeds and includes the pay rate and date in the last column. This is a unique copy of the course material identified by code cbbd3d2c-1b8b-4127-8769-5439cb34900e, and provided to you by Holly Stiver. It is illegal to reprint, redistribute, or resell this content. The Licensed Content is licensed "as-is." Microsoft does not support this Licensed Content in any way and Microsoft gives no express warranties, guarantees or conditions. Please report any unauthorized use of this content to [email protected] or by calling +1 800-785-3448. QuickStart Intelligence Lab Answer Keys: Using Advanced Techniques 5 20. Close the PayRateChange.sql file. Click No when prompted to save changes. Results: After this exercise, you should have converted several different data types to other data types using explicit and implicit conversion. This is a unique copy of the course material identified by code cbbd3d2c-1b8b-4127-8769-5439cb34900e, and provided to you by Holly Stiver. It is illegal to reprint, redistribute, or resell this content. The Licensed Content is licensed "as-is." Microsoft does not support this Licensed Content in any way and Microsoft gives no express warranties, guarantees or conditions. Please report any unauthorized use of this content to [email protected] or by calling +1 800-785-3448. QuickStart Intelligence 6 Lab Answer Keys: Using Advanced Techniques Exercise 3: Implementing a Hierarchy Task 1: Create a hierarchy 1. 2. 3. 4. 5. 6. 7. 8. 9. 10. 11. 12. 13. 14. 15. On the File menu, point to Open and then click Project/Solution. The Open Project dialog box appears. Browse to E:\MOD09\Labfiles\Starter\Exercise03\SalesTerritory. Click SalesTerritory.ssmssln, and then click Open. In Solution Explorer, expand Queries, and then double-click CreateTerritoryTable.sql. On the toolbar, click Execute. In Solution Explorer, double-click CreateTableIndex.sql. On the toolbar, click Execute. In Solution Explorer, double-click InsertRootTerritory.sql. On the toolbar, click Execute. In Solution Explorer, double-click InsertChildTerritory.sql. On the toolbar, click Execute. In Solution Explorer, double-click CreateTerritorySP.sql. On the toolbar. click Execute. In Solution Explorer, double-click PopulateTerritoryTable.sql. On the toolbar, click Execute. Task 2: Query a hierarchy 1. 2. 3. 4. 5. 6. In Solution Explorer, double-click GetTerritoryOrg.sql. On the toolbar, click Execute. In the Results table, notice the results and how the rows relate to one another. In Solution Explorer, double-click GetNATerritories.sql. On the toolbar, click Execute. In the Results pane, notice that only the territories that report to North America are shown. Results: After this exercise, you should have created a hierarchy to store hierarchical data using the hierarchyid data type. This is a unique copy of the course material identified by code cbbd3d2c-1b8b-4127-8769-5439cb34900e, and provided to you by Holly Stiver. It is illegal to reprint, redistribute, or resell this content. The Licensed Content is licensed "as-is." Microsoft does not support this Licensed Content in any way and Microsoft gives no express warranties, guarantees or conditions. Please report any unauthorized use of this content to [email protected] or by calling +1 800-785-3448. QuickStart Intelligence Lab Answer Keys: Using Advanced Techniques 7 Exercise 4: Using Cursors and Set-Based Queries Task 1: Update the ListPrice column 1. 2. On the toolbar, click New Query. In the query pane, type the following query: SELECT ProductID, Name, StandardCost, ListPrice FROM Production.Product 3. 4. 5. 6. 7. 8. 9. On the toolbar, click Execute. In the Results pane, review the ListPrice and StandardCost columns. Keep the query pane open. On the File menu, point to Open, and then click File. The Open File dialog box appears. Browse to E:\MOD09\Labfiles\Starter\Exercise04. Click UpdateListPriceCursor.sql, and then click Open. On the toolbar, click Execute. Switch back to the SELECT query above and then on the toolbar, click Execute. 10. In the Results pane, verify that the ListPrice column has been updated. Keep the query pane open. Task 2: Update the StandardCost column 1. 2. On the toolbar, click New Query. In the query pane, type the following query: UPDATE Production.Product SET StandardCost = 7.99 3. 4. 5. 6. 7. On the toolbar click Execute. Switch back to the SELECT query above, and then on the toolbar, click Execute. In the Results pane, verify that the StandardCost column has been updated. Close SQL Server Management Studio. Click No if prompted to save changes. Turn off the virtual machine and discard changes. Results: After this exercise, you should have written a cursor to perform operations on a result set, and rewritten a cursor to use a set-based approach. This is a unique copy of the course material identified by code cbbd3d2c-1b8b-4127-8769-5439cb34900e, and provided to you by Holly Stiver. It is illegal to reprint, redistribute, or resell this content. The Licensed Content is licensed "as-is." Microsoft does not support this Licensed Content in any way and Microsoft gives no express warranties, guarantees or conditions. Please report any unauthorized use of this content to [email protected] or by calling +1 800-785-3448.