Transforming data by using T-SQL

T-SQL is a procedural language that is used by both dedicated and serverless SQL pools in Synapse. Similar to the transformations that we have seen in Spark, T-SQL also provides a rich set of transformations. Let's look at some of the important ones here:

• SELECT—To select data from a subset of columns, as follows:

  [firstName], [lastName] from dbo.Driver WHERE [city] = 'New York';

• ORDER BY—To sort rows by a particular column, as follows:

  SELECT [firstName], [lastName] from dbo.Driver ORDER BY [firstName];

• DISTINCT—To select unique rows from the input, as follows:

  SELECT DISTINCT [firstName], [lastName] from dbo.Driver;

• GROUP BY—To group rows by columns so that aggregate operations can be performed on them, as follows:

  SELECT [gender], AVG([salary]) AS 'AVG salary' from dbo.Driver GROUP BY [gender];

• UNION—To combine rows from two tables containing the same schema, as follows...