Join us on March 24 on a new episode of AI42 with Jean-Pierre Voogt focused on T-SQL for Data Science.
This is the 6th session of the AI42 series! This time we will learn about T-SQL. This is the outline of the session:
Tools of the trade
We need to decide if we want to store our data in our own environment where we are in full control of security, or move the data to the cloud where we do not have to focus on infrastructure and just the data and our data pipeline. We also need to have a look at what tools we want to use for exploring our data.
1. Cloud vs On-Premises
1.1 Cloud Options 1. Creating an Azure Account and a Azure SQL Database
1.2 On-Premises Options
1.2.1 Installing SQL Server Express/Developer Edition
2. Tools of the Trade (IDE)
2.1 SQL Server Management Studio
2.2 Azure Data Studio
2.3 Visual Studio Code
2.4 Spark SQL in Databricks
3. How to start writing code
3.1 Queries
3.2 Notebooks
Introduction to the basics of the T-SQL language:
Starting with the basics of the T-SQL language, how to filter the data we want to see. We will also look at what is a database object.
1. Database Objects
1.1 Database - Schemas - Tables - Columns - Rows - Record
2. Working with the data
2.1 Selecting and limiting
2.1.1 SELECT
2.1.2 TOP/LIMIT
2.2 Filtering Data
2.2.1 WHERE
2.3 Inline Cleaning and Modifying of our dataset
2.3.1 UDF's and BUILT-IN Functions
2.4 Grouping
2.4.1 GROUP BY
2.5 Aggregations (SUM/MIN/MAX/AVG)
2.5.1 Ordering
2.5.2 ORDER BY
Working with multiple datasets and changing
In this module, we will be working with multiple datasets and even make changes to the data which would persist
1. Working with multiple datasets
1.1 Joining datasets together
1.1.1 LEFT/RIGHT/FULL JOIN
1.2 SET operators
1.2.1 UNION/INTERSECT/EXCEPT/MINUS
2. Building basic Processes
2.1 Stored Procedures
2.2 Views
3. Data Modifications
3.1 Adding new rows
3.1.1 INSERT
3.1.2 IMPORT
3.2 Removing rows
3.2.1 DELETE
3.3 Changing records
3.3.1 UPDATE
4. Analytical Functions
4.1 Window Functions
4.2 Pivot and Unpivot data
4.3 Analytical Aggregations
4.4 Common Table Expressions (CTE)
GUEST SPEAKER
Jean-Pierre Voogt (He/Him) is a South African Data Tinkerer and Microsoft Fanboy and works as a Data Engineer. He is also the leader for the Johannesburg Data Platform User Group, a Formula 1 enthusiast and a father. Jean-Pierre speaks at the Johannesburg SQL User Group, trying to give back to the SQL community as much as possible. He loves to tinker with SQL Server and see how he can approach a problem with a different angle.