SQL Training

Setup

• Install Azure Data Studio
• Read the following tutorial or watch this video on how to use SQL Server Management Studio
• Connect to the AdventureWorks database on the iwudata server if you want to play around.

Training

• http://sqlcourse.com/ - Online training to learn about basic sql (select, insert, delete).  Go at your own pace.  Provides an online sql database to try things on.
• http://sqlcourse2.com/ - Online training to learn about additional query topics (aggregates, grouping, ordering, operators).  Go at your own pace with online database

Test

Use the AdventureWorks database to answer the following questions.  Write down all the commands you run to accomplish the tasks.

1. Return the first name, last lame, product name, and sale price for all products sold in the month of October 2011.
2. Return the CustomerID, first name, and last name of the customers who have made no purchases.
3. Return the first name, last name, unit price, discount, and the difference between the unit price and discounted price for all Sales. The difference must be returned as a positive number. 
4. Return the average sale price by product category name.
5. Add the following Customer and Sale information to the database.
   1. FirstName: Chris
   2. LastName: Kringle
   3. City: Henryville
   4. State: IN
   5. Zip: 47126
   6. ProductID: 1000
   7. SalePrice: 205
   8. SaleDate: 12/31/2011
6. Delete the customer(s) from the database who are from the state of Maine ('ME').
7. Return the product category and the average sale price for those customers who have purchased two or more products.
8. Update the unit price to the average sale price of those sales occurring between 10/10/2011 and 10/20/2011 for the AWC Logo Cap product.
9. Number of sales by product category where the average discounted price is 10 or more dollars greater than the average unit price.
10. Without using a declared iterative construct, return sale date and the running total for all sales, ordered by the sale date in ascending order for October 2011.

The resultant sql script and answers should be sent to the DBA.