SQL Server Transact SQL Querying assisted by GitHub CoPilot

Fundamentally, this course will take you through the basics of querying SQL Server databases using Transact SQL (TSQL) with the assistance of GitHub CoPilot.

The course is divided into the following sections.

A) Orientation and Setup

B) Foundations

C) Builtin Functions and Datatypes

D) Filtering and Ordering

E) FROM Clause

• Basics

• with JOINS

F) SubQueries

G) Analytical Functions

• Grouped Aggregates

• Set Operators

• Pivot

• Window Functions

H) Extra Innings

• Cornucopia Programming - bringing together various programming elements form the course at large.

• Data Warehousing - most of the course is conducted using the transactional Adventureworks database. Here we look briefly at coding against a dimensionally modelled data warehouse - AdventureworksDW

• Query Data Shaping - takes a look at intermediate programming techniques we may employ to output a final result set.

• Data Shaping Exercise - explores the use of Window functions to facilitate data wrangling like 'deduplication', 'gap detection', 'generate new keys' for a data set, 'trimmed mean'.

• Voice to Prompt - very brief survey of the possible use of voice to text capabilities in generating AI prompts to generate code.

The programming in all of these sections is facilitated through the use of Azure Data Studio and the use of Jupyter Books and Notebooks.

Through the use of AI prompts, GitHub Copilot generates code that is to be reviewed by the programmer as to its accuracy and functionality.

The combination of learning the 'prompting' and code reviews aids the student in learning the syntax and functionality of TSQL