Querying with Transact-SQL Lab 10 – Programming with Transact-SQL

Overview In this lab, you will use some basic Transact-SQL programming logic to work with data in the AdventureWorksLT database. Before starting this lab, you should view Module 10 – Programming with Transact-SQL in the Course Querying with Transact-SQL. Then, if you have not already done so, follow the instructions in the Getting Started document for this course to set up the lab environment. If you find some of the challenges difficult, don’t worry – you can find suggested solutions for all of the challenges in the Lab Solution folder for this module.

What You’ll Need 

An Azure SQL Database instance with the AdventureWorksLT sample database. Review the Getting Started document for information about how to provision this.

Challenge 1: Creating scripts to insert sales orders You want to create reusable scripts that make it easy to insert sales orders. You plan to create a script to insert the order header record, and a separate script to insert order detail records for a specified order header. Both scripts will make use of variables to make them easy to reuse. Tip: Review the documentation for variables and the IF…ELSE block in the Transact-SQL Language Reference.

1. Write code to insert an order header Your script to insert an order header must enable users to specify values for the order date, due date, and customer ID. The SalesOrderID should be generated from the next value for the SalesLT.SalesOrderNumber sequence and assigned to a variable. The script should then insert a record into the SalesLT.SalesOrderHeader table using these values and a hard-coded value of ‘CARGO TRANSPORT 5’ for the shipping method with default or NULL values for all other columns. After the script has inserted the record, it should display the inserted SalesOrderID using the PRINT command. Test your code with the following values: Order Date Today’s date

Due Date 7 days from now

Customer ID 1

Note: Support for Sequence objects was added to Azure SQL Database in version 12, which became available in some regions in February 2015. If you are using the previous version of Azure SQL database (and the corresponding previous version of the AdventureWorksLT sample database), you will need to adapt your code to insert the sales order header without specifying the SalesOrderID (which is an IDENTITY column in older versions of the sample database), and then assign the most recently generated identity value to the variable you have declared.

2. Write code to insert an order detail The script to insert an order detail must enable users to specify a sales order ID, a product ID, a quantity, and a unit price. It must then check to see if the specified sales order ID exists in the SalesLT.SalesOrderHeader table. If it does, the code should insert the order details into the SalesLT.SalesOrderDetail table (using default values or NULL for unspecified columns). If the sales order ID does not exist in the SalesLT.SalesOrderHeader table, the code should print the message ‘The order does not exist’. You can test for the existence of a record by using the EXISTS predicate. Test your code with the following values: Sales Order ID The sales order ID returned by your previous code to insert a sales order header.

Product ID 760

Quantity 1

Unit Price 782.99

Quantity 1

Unit Price 782.99

Then test it again with the following values: Sales Order ID 0

Product ID 760

Challenge 2: Updating Bike Prices Adventure Works has determined that the market average price for a bike is $2,000, and consumer research has indicated that the maximum price any customer would be likely to pay for a bike is $5,000. You must write some Transact-SQL logic that incrementally increases the list price for all bike products by 10% until the average list price for a bike is at least the same as the market average, or until the most expensive bike is priced above the acceptable maximum indicated by the consumer research. Tip: Review the documentation for WHILE in the Transact-SQL Language Reference.

1. Write a WHILE loop to update bike prices The loop should: 

 

Execute only if the average list price of a product in the ‘Bikes’ parent category is less than the market average. Note that the product categories in the Bikes parent category can be determined from the SalesLT.vGetAllCategories view. Update all products that are in the ‘Bikes’ parent category, increasing the list price by 10%. Determine the new average and maximum selling price for products that are in the ‘Bikes’ parent category.



If the new maximum price is greater than or equal to the maximum acceptable price, exit the loop; otherwise continue.

Next Steps Well done! You’ve completed the lab, and you’re ready to learn how to handle errors and implement transactions in Transact-SQL by completing Module 11 – Error handling and Transactions in the Course Querying with Transact-SQL.

Getting Started with Transact-SQL Labs - GitHub

challenges in the Lab Solution folder for this module. ... and customer ID. ... Note: Support for Sequence objects was added to Azure SQL Database in version 12 ...

624KB Sizes 26 Downloads 354 Views

Recommend Documents

Getting Started with Transact-SQL Labs - GitHub
The SQL Server Database Engine is a complex software product. For your systems to achieve optimum performance, you need to have a knowledge of Database Engine architecture, understand how query design affects performance, and be able to implement eff

Getting Started with Transact-SQL Labs - GitHub
Getting Started document for information about how to provision this. Challenge 1: Inserting Products. Each Adventure Works product is stored in the SalesLT.

Getting Started with Transact-SQL Labs - GitHub
getting started with Transact-SQL as it requires minimal software installation and .... Visual Studio is a comprehensive software development environment for all ...

Getting Started with Transact-SQL Labs - GitHub
Before starting this lab, you should view Module 6 – Using Subqueries and APPLY in the Course. Querying with Transact-SQL. Then, if you have not already done so, follow the instructions in the Getting. Started document for this course to set up the

Getting Started with Transact-SQL Labs - GitHub
This course assumes no prior experience with Transact-SQL or databases, though ... knowledge of computer systems and database concepts will be useful. ... the module), and a graded assessment, in which you must answer all questions.

Getting Started with Transact-SQL Labs - GitHub
queries that retrieve information about these products. 1. Retrieve product model descriptions. Retrieve the product ID, product name, product model name, and product model summary for each product from the SalesLT.Product table and the SalesLT.vProd

Getting Started with Transact-SQL Labs - GitHub
An online video presentation. • A hands-on ... prerequisite software or making any required configuration changes. ... comfortable configuring service accounts).

Getting Started with Transact-SQL Labs - GitHub
Create a list of all customer contact names that includes the title, first name, middle ... Customers may provide adventure Works with an email address, a phone ...

Getting Started with Transact-SQL Labs - GitHub
In this lab, you will use SELECT queries to retrieve, sort, and filter data from the AdventureWorksLT database. Before starting this lab, you should view Module 2 ...

Getting Started with Transact-SQL Labs - GitHub
SQL Database, a cloud-based relational database service. This is the .... Page 5 .... local network or computer that's preventing the connection. If you're using a ...

Getting Started with CodeXL - GitHub
10. Source Code View . ..... APU, a recent version of Radeon Software, and the OpenCL APP SDK. This document describes ...... lel_Processing_OpenCL_Programming_Guide-rev-2.7.pdf. For GPU ... trademarks of their respective companies.

Getting Started with Go - GitHub
Jul 23, 2015 - The majority of my experience is in PHP. I ventured into Ruby, ... Compiled, Statically Typed, Concurrent, Imperative language. Originally ...

Background Getting Started - GitHub
four reference projects for building Moai: vs2008, vs2010, Android/NDK and Xcode. ... phone simulator: we've only exposed the desktop input devices supported ...

Getting Started - GitHub
Breakpoints are defined by CSS3 media queries, which can either go directly in your CSS (using the. @media directive), or in your stylesheet tags (using the media attribute). The Breakpoint Manager builds on this by letting you also define a copy of

Getting Started with Contract4J
are using it on your own risk. Submitting any suggestions, or similar, the ... Go to the Contract4J web-page and download the latest release. In my case it is ... Selecting the JAR files to add to the Contract4J library project's build path.

Getting Started with Contract4J
Go to the Contract4J web-page and download the latest release. In my case it is ... Selecting the JAR files to add to the Contract4J library project's build path.

Getting Acquainted with R - GitHub
In this case help.search(log) returns all the functions with the string 'log' in them. ... R environment your 'working directory' (i.e. the directory on your computer's file ... Later in the course we'll discuss some ways of implementing sanity check

Getting Started with Project-Based Learning
and meet the immediate needs of your students rather than being in permanent crisis-mode trying to ... help us master the bigger thing step by step. Through ...

Getting Started with Protege-Frames
Jun 6, 2006 - To create a new project later, select File | New Project.... 2. Click Create ... The internal Protege system classes :THING and .... the left, at the top of the Class Hierarchy area) to delete the class, and then clicking on :THING and.

getting started with html
Aug 28, 2009 - Figure 1: A simple web page. Example 2.1. We create a minimal page. This and other examples can be created in a simple editor such as ...