SQL Training
Overview
Various skills are necessary to work with data residing in our sql databases. The following provides a training template to achieve necessary skills.
Goal
After completing this tutorial you will have learned basic t-sql programmming skills.
Please direct all DBA communication to Stephen Swan
Courses
Beginning T-SQL
Upon completion, you may request a certificate of completion for Beginning T-SQL from the DBA.
Setup
- Install SQL Management Studio
- Read the following tutorial or watch this video on how to use SQL Server Management Studio
- Connect to the AdventureWorks database on the warehousedb 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
The DBA will create a database for you to complete the following questions.
- Return the First Name, Last Name, Product Name, and Sale Price for all products sold in the month of October 2005.
- Return the CustomerID, First Name, and Last Name of those individuals in the Customer table who have made no Sales purchases.
- Return the First Name, Last Name, Sale Price, Recommended Sale Price, and the difference between the Sale Price and Recommended Sale Price for all Sales. The difference must be returned as a positive number.
- Return the average Sale Price by Product Category.
- Add the following Customer and Sale information to the database.
- FirstName: Chris
- LastName: Kringle
- City: Henryville
- State: IN
- Zip: 47126
- ProductID: 3
- SalePrice: 205
- SaleDate: 12/31/2005
- 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 Sale Price to the Recommended Sale Price of those Sales occurring between 6/10/2005 and 6/20/2005.
- Number of Sales by Product Category where the average Recommended Price is 10 or more dollars greater than the average Sale 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.
The resultant sql script and answers should be sent to the DBA.
Additional Training
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
Challenge
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.