Following the breezy orientation course, the Microsoft Professional Program for Data Science curriculum digs into the Microsoft dialect of SQL known as Transact-SQL. This course briefly addresses updating data, stored procedures, transactions and error handling -- but the bulk of the course concerns extracting data from SQL Server. This is the 2nd in a 10-part online course sequence for which I'm documenting my experience for others. The course title is "DAT201x: Querying with Transact-SQL"
Querying with Transact-SQL
Transact-SQL gives you a lot of dexterity for pulling out data and the video series covers these topics comprehensively if not very deeply. All standard features of SELECT (where, group by, having clauses) but also topics like joins/intersect/except, correlated sub-queries, common-table expressions, grouping sets, and rollup/cube topics are included.
The video content by Graeme Malcolm & Geoff Allix is quite good. I'd previously viewed these in preparation for Microsoft's exam 70-461 "Querying SQL Server 2012" so this go-around was more of a refresher. The site allows you to playback the videos at an accelerated rate and at 1.25x you can still comprehend what these two "Brits" are saying. As an alternate approach, you can easily download the videos and then play them using something like VLC Media player at other accelerated playback rates. Whatever method is used -- you are looking at upwards of 5-6 hours of video content spread over 35-45 videos. All content is organized by way of a navigation bar at the top that walks you step by step through the curriculum.
The course is composed of 11 Modules, each containing a series of video tutorials and a lab (PDF of syllabus available here.) The lab is based entirely on the AdventureWorksLT database which I restored to a local database server on our network. You don't technically need an instance of SQL Server to complete the lab because the Edx.org site makes use of a virtual lab called "DataCamp" to allow you to answer the lab questions. As in the screenshot below, you are given scenarios and expected to complete the partial query provided at top right of the screen. The labs are pretty well thought-out, and guide you along the way. IMO they effectively emphasized what was covered in the video. The lab constitutes 60% of your grade for the course. There is no time-limit, and if you have a local instance of SQL you can easily copy paste from the virtual lab to experiment on your local instance.
The exam is composed of 21 multiple choice type questions. The emphasis is again on extracting data and the exam questions were typically less challenging than the lab questions. The exam constitutes 40% of your course grade.
In approximately 10 hours, I selectively reviewed videos, completed the majority of the labs and then took the exam. Up to this point I had been auditing the course. On completion of the exam, I decided to pay the fee and get the certification for the class. If you are in doubt about passing the course the first time -- know that this approach to the class is an option. If you pay for the course up front and don't complete it within the 3 month window or fail to obtain higher than 70% -- you just ate that certification fee and your recourse is to take the class over (and if you are going for the certificate...) paying the fee once again. If in doubt about the topics -- audit this class!
This course was pretty easy for me as I'd recently studied the topics for the 70-461 exam and have used SQL for a long time. If you are new to the topics you should expect a 50-60 hour commitment. I would recommend setting up a local SQL instance and purchasing Itzik Ben-Gan's book "T-SQL Fundamentals" and/or the 70-461 study guide from Microsoft.