FA-0743Data & AnalyticsSoftware Development

SQL Server Data Analytics with T-SQL and Python

Relational queries, data preparation and modelling awareness

Introduction

Why this course

Develop practical SQL Server analytics skills from an existing understanding of relational databases and SQL. Guided exercises cover database access, T-SQL queries, data preparation, stored procedures, functions and a small Python/pandas analysis.

The course introduces statistical and machine-learning approaches using supplied examples. SQL Server 2019 is retained as historical feature context; tooling and lab platforms are selected for compatibility, and deployment exercises use a lab rather than certify production readiness.

Learning outcomes

Explain relational concepts and SQL versus T-SQL.
Connect to and inspect a compatible SQL Server lab, including container and remote-access considerations.
Create databases, tables and views; query, filter, join and modify sample data.
Use selected stored procedures and functions, and import/export data.
Prepare and clean data and work with date/time, JSON and XML examples.
Use basic Python/pandas for analysis and visualisation.
Compare selected modelling approaches and distinguish Machine Learning Services from legacy SSAS data mining.

Prerequisites

Basic understanding of SQL and relational databases; expert T-SQL knowledge is not required.
Supported SQL tools such as SSMS or Visual Studio Code with MSSQL extension, plus suitable Python tools for the supplied exercise.
Reliable internet and authorised access to the designated lab; compatible host/runtime and relevant remote/file-transfer tools where used.
SQL Server Linux container exercises use a supported x86-64 Linux host and a version-compatible image/platform.

Training outline

3 modules

01Day 1 — Database Environment and Query Foundations1 topics

Environment and SQL tools

RDBMS and SQL Server concepts; SQL versus T-SQL.
Production deployment considerations contrasted with a training lab.
Containers versus virtual machines, including VirtualBox; remote access and a compatible Linux container demonstration.
SSMS database/table inspection, functions, stored procedures, basic permissions, import and export.
Use supported VS Code MSSQL tooling in place of retired Azure Data Studio.

T-SQL query practice

SELECT, WHERE, LIKE, ORDER BY, IN and BETWEEN.
INSERT, UPDATE, DELETE and ALTER on sample data.
Aggregates, GROUP BY, subqueries and joins; create a simple view.
Data cleaning and checking query results.

02Day 2 — Reusable SQL and Python Analysis5 topics

Stored procedures and functions, with a practical assessment.
Date/time values and selected JSON/XML examples.
Python data types, conditions, loops, functions and pandas using prepared exercises.
Import/export and transformations for analysis; basic statistics and selected visualisations.
Translate a small analytics question into SQL and Python steps.

03Day 3 — Data Mining and Machine-Learning Overview7 topics

Data-mining concepts, algorithms and their data requirements.
Compare association, clustering, decision trees, linear/logistic regression and naïve Bayes at introductory level.
Explore a selected prepared model and appropriate independent evaluation.
Introduce neural networks and sequence clustering conceptually; identify the legacy SQL Server Analysis Services context.
SSAS data mining was discontinued in SQL Server 2022; do not treat those historical algorithms as a current built-in database-engine feature.
Demonstrate Python analytics with SQL data, or Machine Learning Services in an appropriately preconfigured compatible environment; in-database execution needs separate feature/runtime configuration.
Review limitations and the completed SQL/analytics exercises.

A programme built around your team.

Share your training goals and requirements.