Various skills are necessary to work with data residing in our sql databases. The following provides a training template to achieve necessary skills.
After completing this tutorial you will have learned basic t-sql programmming skills.
Please direct all DBA communication to Stephen Swan
Upon completion, you may request a certificate of completion for Beginning T-SQL from the DBA.
- 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
Use the AdventureWorks database to answer the following questions. Write down all the commands you run to accomplish the tasks.
- Return the first name, last lame, product name, and sale price for all products sold in the month of October 2011.
- Return the CustomerID, first name, and last name of the customers who have made no purchases.
- 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.
- Return the average sale price by product category name.
- Add the following Customer and Sale information to the database.
- FirstName: Chris
- LastName: Kringle
- City: Henryville
- State: IN
- Zip: 47126
- ProductID: 1000
- SalePrice: 205
- SaleDate: 12/31/2011
- Delete the customer(s) from the database who are from the state of Maine ('ME').
- Return the product category and the average sale price for those customers who have purchased two or more products.
- 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.
- Number of sales by product category where the average discounted price is 10 or more dollars greater than the average unit price.
- 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.
Upon completion, you may request a certificate to identify your accomplishments from the dba. Completion of the Beginning T-SQL test will be required.
View the following videos
Sign up and have a solution accepted for one of the challenges at http://beyondrelational.com/tc/. Your name should appear on the accepted solutions post for that challenge. Completion of this challenge will automatically qualify you for an Advanced T-SQL certificate from the DBA.