Hey, Cloud Gurus,
welcome to our second lesson
on meeting the tools of the trade.
This time, focused on Transact-SQL.
In this lesson,
we're going to talk about T-SQL uses,
we're going to go over some code examples,
and wrap everything up with a review.
And yes, this is a super short lesson
not because Transact-SQL is not important,
in fact, it is a very powerful tool,
but this is something
you should be pretty familiar with already
as a data professional.
This lesson focuses more
on how it's used in Azure data engineering,
as well as how it might pop up on the exam.
If you don't have a strong background in Transact-SQL,
feel free to check out our SQL Deep Dive.
That's focused on ANSI SQL, button-based in Azure,
and will help you become familiar with the language.
Starting out with T-SQL uses,
T-SQL is how people most commonly refer to Transact-SQL.
This is a powerful language
that is used in a variety of scenarios
to move and transform data.
This can be done either within ETL,
or extract, transform, and load software,
or as standalone scripts.
It can be utilized within Azure Machine Learning
using the Apply SQL Transformation module,
and oftentimes, people don't think of T-SQL
in relation to things like machine learning,
but it's really used across every data field:
data science, data engineering, machine learning--
it's used in a little bit of everything.
Particular to this module
that's actually running SQLite rather than true T-SQL,
but it can take up to 3 data sets
and has very similar syntax due to the ANSI SQL standard.
Some ways you might see T-SQL being used
is to create tables for results or to save datasets,
performing custom transformations on data types,
or creating aggregates.
For instance, if you have a string column
but need to cast that into an int data type,
T SQL can be used for that.
You can also filter or alter the data
and return the query results as a table.
Let's go over some common code examples
of how you might see T-SQL used.
First, a simple select,
or selecting recency, frequency, monetary, time, and class
from a dataset called T1,
or time is between 3 and 20.
And this is against a blood donation dataset,
so we're simply selecting the fields we want
from the time range that we want.
Another example might be a more complex form of select
called a join.
In this example, we're just selecting the distinct place IDs
from one dataset: T2.
We're also selecting the name, city, state,
price, and entree from there,
as well as the average rating renamed as average rating.
We're pulling from 2 different datasets, T1, and T2,
and rejoining on the place ID column.
And so we're going to pull the matches
from both of those data sets
and then group them together by the place ID column
in the second dataset.
All that to say
is joining 2 tables to create a dataset
that combines the specified restaurant features
with average ratings for each restaurant.
This is a common way you will see T-SQL used,
especially in data engineering,
pulling multiple data sets together
so that we can either visualize that information
or further transform and massage it.
Another example would be using aggregates.
Here, we're returning a dataset
containing the restaurant ID,
along with the average rating for the restaurant.
You can see we're selecting the distinct place ID
and then aggregating the ratings into an average
and grouping by that place ID.
This is very helpful for summarizing information,
especially as you're creating high-level dashboards.
Another example would be of casting.
Going back to our original select,
this time we're taking time
and casting it as a date-time type.
Dates can be stored in a variety of formats,
including timestamp, and sometimes in order
to match data together from different sources,
you have to change the data type
so that they work well together.
In this instance, we're casting time
and making sure it's of that date-time data type.
By way of review,
Transacts SQL can be used in a variety of situations
to create structures, query
or transform data, and save datasets.
You can perform common sequel operations
in Azure Machine Learning
using the Apply SQL Transformation module.
And that's it for our super quick look at Transact-SQL.
Remember that you don't need to be an expert
in this language for the exam.
Simply be able to recognize queries
and their general structure,
and maybe be able to fill in the missing pieces
based on a few different choices given to you,
but don't underestimate this tool
or its value in your day-to-day data engineering work.
The better you know this language,
the better it will serve you.
And as I said, can be used in a variety of tasks
across all data disciplines,
but that's it for now.
When you're ready,
let's go onto meeting another item
you can place in your tool belt.
I'll see you there.