If you don't have a SQL Server yet, you can use a free SQL Server Evaluation Edition or Developer Edition. You can download any of them from the SQL Server downloads site at https://www.microsoft.com/en-ca/sql-server/sql-server-downloads.

You just start SQL Server setup, and then from the Feature Selection page select the following:

• Database Engine Services
• Underneath Machine Learning (ML) Services (In-Database)
• With both languages, R and Python, selected, like you can see in the next screenshot

After that, all you need is client tools, and you can start writing the code. The following screenshot shows the SQL Server setup Feature Selection page with the appropriate features selected:

The next step is to install client tools. Of course, you need SQL Server Management Studio (SSMS). You can download it at https://docs.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms?view=sql-server-2017. If you are not familiar with this tool, please use the SSMS at https://docs.microsoft.com/en-us/sql/ssms/tutorials/tutorial-sql-server-management-studio?view=sql-server-2017 to learn the basics about this tool.

Finally, you need some demo data. I will mostly use the AdventureWorksDW2017 demo database in this book. You can find this demo database and other Microsoft samples at https://github.com/Microsoft/sql-server-samples/releases. I will point you to the appropriate sources when I use any other demo data in this book.

After you install everything mentioned so for, you are ready to start learning or renewing the knowledge of the T-SQL SELECT statement.

You probably already know that the most important SQL statement is the mighty SELECT statement you use to retrieve data from your databases. Every database developer knows the basic clauses and their usage:

• SELECT: Defines the columns returned, or a projection of all table columns
• FROM: Lists the tables used in the query and how they are associated, or joined
• WHERE: Filters the data to return only the rows that satisfy the condition in the predicate
• GROUP BY: Defines the groups over which the data is aggregated
• HAVING: Filters the data after the grouping with conditions that refer to aggregations
• ORDER BY: Sorts the rows returned to the client application

Let's start with the simplest concept of SQL that every Tom, Dick, and Harry is aware of! The simplest query to retrieve the data you can write includes the SELECT and the FROM clauses. In the select clause, you can use the star character, literally SELECT *, to denote that you need all columns from a table in the result set. The following code switches to the AdventureWorksDW2017 database context and selects all data from the dbo.DimEmployee table:

USE AdventureWorksDW2017;
GO
SELECT *
FROM dbo.DimEmployee;

This query returns 296 rows, all employees with all columns.

SELECT EmployeeKey, FirstName, LastName
FROM dbo.DimEmployee;

EmployeeKey FirstName LastName
----------- --------- ----------
1           Guy       Gilbert
2           Kevin     Brown
3           Roberto   Tamburello

SELECT EmployeeKey,
 FirstName + ' ' + LastName AS [Full Name]
INTO dbo.EmpFUll
FROM dbo.DimEmployee;
GO
SELECT EmployeeKey, [Full Name]
FROM dbo.EmpFUll;

Here is the partial result:

EmployeeKey Full Name
----------- ------------------
1           Guy Gilbert
2           Kevin Brown
3           Roberto Tamburello

As you have seen before, there are 296 employees. If you check the full result of the first query, you might notice that there is a column named SalesPersonFlag in the dbo.DimEmployee table. You might want to check which of the employees are also salespeople. You can filter the results of a query with the WHERE clause, as the following query shows:

SELECT EmployeeKey, FirstName, LastName
FROM dbo.DimEmployee
WHERE SalesPersonFlag = 1;

This query returns 17 rows only.

In a relational database, you typically have data spread in multiple tables. Each table represents a set of entities of the same kind, such as employees in the examples you have seen so far. In order to make result sets meaningful for the business your database supports, most of the time you need to retrieve data from multiple tables in the same query. You need to join two or more tables based on some conditions. The most frequent kind of a join is the inner join. An inner join returns only rows for which the condition in the join predicate for the two joined tables evaluates to true. Note that in a relational database, you have three-valued logic, because there is always a possibility that a piece of data is unknown. You mark the unknown with the NULL keyword. A predicate can thus evaluate to true, false, or NULL. For an inner join, the order of the tables involved in the join is not important.

In the following example, you can see the dbo.DimEmployee table joined with an inner join to the dbo.FactResellerSales table:

SELECT e.EmployeeKey, e.FirstName, e.LastName,
 fr.SalesAmount
FROM dbo.DimEmployee AS e
 INNER JOIN dbo.FactResellerSales AS fr
  ON e.EmployeeKey = fr.EmployeeKey;

Here are the partial results:

EmployeeKey FirstName LastName SalesAmount
----------- --------- -------- -----------
285         Tsvi      Reiter   2024.994
285         Tsvi      Reiter   6074.982
285         Tsvi      Reiter   2024.994

In the previous query, you can see that table aliases are used. If a column's name is unique across all tables in the query, you can use it without a table name. If not, you need to use table name in front of the column, to avoid ambiguous column names, in the table.column format. In the previous query, the EmployeeKey column appears in both tables. Therefore, you need to precede this column name with the table name of its origin to avoid ambiguity. You can shorten the two-part column names by using table aliases. You specify table aliases in the FROM clause. Once you specify table aliases, you must always use the aliases; you can't refer to the original table names in that query anymore. Please note that a column name might be unique in the query at the moment when you write the query. However, later somebody could add a column with the same name in another table involved in the query. If the column name is not preceded by an alias or by the table name, you would get an error when executing the query because of the ambiguous column name. In order to make the code more stable and more readable, you should always use table aliases for each column in the query.

The previous query returned 60,855 rows. It is always recommended to know at least approximately the number of rows your query should return. This number is the first control of the correctness of the result set, or said differently, whether the query is written in a logically correct way. If every sale has an employee, as it should have, then the previous query should have returned exactly the number of rows dbo.FactResellerSales has. You can quickly check the number of rows in the dbo.FactResellerSales table with the help of the COUNT(*) aggregate function, as the following query shows:

SELECT COUNT(*) AS ResellerSalesCount
FROM dbo.FactResellerSales;

The result is, as you probably expected, 60,855 rows.

You can join multiple tables in a single query. The following code joins seven tables in a single query. Note that all of the joins are still inner joins. The query returns 60,855 rows again, with at least 1 column from each table involved in the query:

SELECT e.EmployeeKey, e.FirstName, e.LastName,
 r.ResellerKey, r.ResellerName,
 d.DateKey, d.CalendarYear, d.CalendarQuarter,
 p.ProductKey, p.EnglishProductName,
 ps.EnglishProductSubcategoryName,
 pc.EnglishProductCategoryName,
 fr.OrderQuantity, fr.SalesAmount
FROM dbo.DimEmployee AS e
 INNER JOIN dbo.FactResellerSales AS fr
  ON e.EmployeeKey = fr.EmployeeKey
 INNER JOIN dbo.DimReseller AS r
  ON r.ResellerKey = fr.ResellerKey
 INNER JOIN dbo.DimDate AS d
  ON fr.OrderDateKey = d.DateKey
 INNER JOIN dbo.DimProduct AS p
  ON fr.ProductKey = p.ProductKey
 INNER JOIN dbo.DimProductSubcategory AS ps
  ON p.ProductSubcategoryKey = ps.ProductSubcategoryKey
 INNER JOIN dbo.DimProductCategory AS pc
  ON ps.ProductCategoryKey = pc.ProductCategoryKey;

In the dbo.Employees table, there are 17 salespeople. Do all of them have at least one sale, at least one row from the dbo.FactResellerSales table associated with the employee key of that salesperson? You can check how many distinct employees have sales associated with them with the help of the DISTINCT keyword:

SELECT DISTINCT fr.EmployeeKey
FROM dbo.FactResellerSales AS fr;

The query returns 17 rows. Now imagine that you would like to list all sales rows together with the employees' data, but you also need to include in the result the employees that are not salespeople, that do now have any row associated with their EmployeeKey column in the fact table. You can use an outer join to fulfill this task.

With an outer join, you preserve the rows from one or both tables, even if they don't have a match in the other table. The result set returned includes all of the matched rows, like what you get from an inner join plus the preserved rows. Within an outer join, the order of the tables involved in the join might be important. If you use LEFT OUTER JOIN, the rows from the left table are preserved. If you use RIGHT OUTER JOIN, the rows from the right table are preserved. Of course, in both cases, the order of the tables involved in the join is important. With FULL OUTER JOIN, you preserve the rows from both tables, and the order of the tables is not important. The following query uses a left outer join to preserve the rows from the dbo.DimEmployee table:

SELECT e.EmployeeKey, e.FirstName, e.LastName,
 fr.SalesAmount
FROM dbo.DimEmployee AS e
 LEFT OUTER JOIN dbo.FactResellerSales AS fr
  ON e.EmployeeKey = fr.EmployeeKey;

The query returns 61,134 rows. Did we get all of the employees in the result? You can check this by checking the distinct EmployeeKey after the outer join:

SELECT DISTINCT e.EmployeeKey
FROM dbo.DimEmployee AS e
 LEFT OUTER JOIN dbo.FactResellerSales AS fr
  ON e.EmployeeKey = fr.EmployeeKey;

The query returns 296 rows, which is the number of employees.

Joining more than two tables is not tricky if all of the joins are inner joins. The order of joins is not important. However, you might want to execute an outer join after all of the inner joins. If you don't control the join order with the outer joins, it might happen that a subsequent inner join filters out the preserved rows of an outer join. You can control the join order with parentheses. The following query uses the right outer join to preserve all employees and makes sure that this join is executed after all inner joins:

SELECT e.EmployeeKey, e.FirstName, e.LastName,
 r.ResellerKey, r.ResellerName,
 d.DateKey, d.CalendarYear, d.CalendarQuarter,
 p.ProductKey, p.EnglishProductName,
 ps.EnglishProductSubcategoryName,
 pc.EnglishProductCategoryName,
 fr.OrderQuantity, fr.SalesAmount
FROM (dbo.FactResellerSales AS fr
 INNER JOIN dbo.DimReseller AS r
  ON r.ResellerKey = fr.ResellerKey
 INNER JOIN dbo.DimDate AS d
  ON fr.OrderDateKey = d.DateKey
 INNER JOIN dbo.DimProduct AS p
  ON fr.ProductKey = p.ProductKey
 INNER JOIN dbo.DimProductSubcategory AS ps
  ON p.ProductSubcategoryKey = ps.ProductSubcategoryKey
 INNER JOIN dbo.DimProductCategory AS pc
  ON ps.ProductCategoryKey = pc.ProductCategoryKey)
 RIGHT OUTER JOIN dbo.DimEmployee AS e
  ON e.EmployeeKey = fr.EmployeeKey;

The query returns 61,134 rows, as it should. Note that with the usage of the parenthesis, the order of joins is defined in the following way:

• Perform all inner joins, with an arbitrary order among them

• Execute the left outer join after all of the inner joins

Many times, you need to aggregate data in groups. This is where the GROUP BY clause comes in handy. The following query aggregates the sales data for each employee:

SELECT e.EmployeeKey,
 MIN(e.LastName) AS LastName,
 SUM(fr.OrderQuantity)AS EmpTotalQuantity,
 SUM(fr.SalesAmount) AS EmpTotalAmount
FROM dbo.DimEmployee AS e
 INNER JOIN dbo.FactResellerSales AS fr
  ON e.EmployeeKey = fr.EmployeeKey
GROUP BY e.EmployeeKey;

The query returns 17 aggregated rows. Here are the results, abbreviated to the first three rows only:

EmployeeKey LastName   EmpTotalQuantity EmpTotalAmount
----------- ---------- ---------------- --------------
284         Vargas     11544            3609447.2163
295         Valdez     6898             1790640.2311
281         Blythe     23058            9293903.0055

In the SELECT clause, you can have only the columns used for grouping, or aggregated columns. That is why the LastName column in the SELECT list is used in the MIN() aggregate function. You need to get a scalar, a single aggregated value for each row for each column not included in the GROUP BY list.

Sometimes, you need to filter aggregated data. For example, you might need to find only the employees for which the sum of the order quantity did not reach 10,000. You can filter the result set on the aggregated data by using the HAVING clause:

SELECT e.EmployeeKey,
 MIN(e.LastName) AS LastName,
 SUM(fr.OrderQuantity)AS EmpTotalQuantity,
 SUM(fr.SalesAmount) AS EmpTotalAmount
FROM dbo.DimEmployee AS e
 INNER JOIN dbo.FactResellerSales AS fr
  ON e.EmployeeKey = fr.EmployeeKey
GROUP BY e.EmployeeKey
HAVING SUM(fr.OrderQuantity) < 10000;

The query returns eight rows only. Note that you can't use column aliases from the SELECT clause in any other clause introduced in the previous query. The SELECT clause logically executes after all other clauses from the query, and the aliases are not known yet. However, the ORDER BY clause, which sorts the result, executes after the SELECT clause, and therefore the columns aliases are already known and you can refer to them. The following query shows the nine employees with sum of the OrderQuantity variable greater than 10,000, sorted in descending order by this sum:

SELECT e.EmployeeKey,
 MIN(e.LastName) AS LastName,
 SUM(fr.OrderQuantity)AS EmpTotalQuantity,
 SUM(fr.SalesAmount) AS EmpTotalAmount
FROM dbo.DimEmployee AS e
 INNER JOIN dbo.FactResellerSales AS fr
  ON e.EmployeeKey = fr.EmployeeKey
GROUP BY e.EmployeeKey
HAVING SUM(fr.OrderQuantity) > 10000
ORDER BY EmpTotalQuantity DESC;

You can see the shortened results as follows:

EmployeeKey LastName   EmpTotalQuantity EmpTotalAmount
----------- ---------- ---------------- --------------
282         Mitchell   27229            10367007.4286
283         Carson     27051            10065803.5429
291         Pak        26231            8503338.6472

Aggregating data over the complete input rowset or aggregating in groups produces aggregated rows only, either one row for the whole input rowset or one row per group. Sometimes, you need to return aggregates together with the detail data. One way to achieve this is by using subqueries, which are queries inside queries.

The next query shows an example of using two subqueries in a single query: in the SELECT clause, a subquery that calculates the sum of quantity for each employee. It returns a scalar value. The subquery refers to the employee key from the outer query. The subquery can't execute without the outer query. This is a correlated subquery. There is another subquery in the FROM clause that calculates the overall quantity for all employees. This query returns a table, although it is a table with a single row and a single column. This query is a self-contained subquery, independent of the outer query. A subquery in the FROM clause is also called a derived table.

Another type of join is used to add the overall total to each detail row. A cross-join is a Cartesian product of two input rowsets: each row from one side is associated with every single row from the other side. No join condition is needed. A cross-join can produce an unwanted, huge result set. For example, if you cross-join just 1,000 rows from the left side of the join with 1,000 rows from the right side, you get 1,000,000 rows in the output. Therefore, typically you want to avoid a cross-join in production. However, in the example in the following query, 60,855 from the left-side rows is cross-joined to a single row from the subquery, therefore producing only 60,855. Effectively, this means that the overall total column is added to each detail row:

SELECT e.EmployeeKey, e.LastName,
 fr.SalesAmount,
 (SELECT SUM(fr1.SalesAmount)
  FROM dbo.FactResellerSales AS fr1
  WHERE fr1.EmployeeKey = e.EmployeeKey)
  AS TotalPerEmployee,
 frt.GrandTotal
FROM (dbo.DimEmployee AS e
 INNER JOIN dbo.FactResellerSales AS fr
  ON e.EmployeeKey = fr.EmployeeKey)
 CROSS JOIN
  (SELECT SUM(fr2.SalesAmount) AS GrandTotal
   FROM dbo.FactResellerSales AS fr2) AS frt
ORDER BY e.EmployeeKey;

Here is the abbreviated output of the previous query:

EmployeeKey LastName   SalesAmount  TotelPerEmployee GrandTotal
----------- ---------- ------------ ---------------- -------------
272         Jiang      1619.52      1092123.8562     80450596.9823
272         Jiang      1445.1898    1092123.8562     80450596.9823
272         Jiang      714.7043     1092123.8562     80450596.9823

In the previous example code, the correlated subquery in the SELECT clause has to logically execute once per row of the outer query. The query was partially optimized by moving the self-contained subquery for the overall total in the FROM clause, where it logically executes only once. Although SQL Server can optimize correlated subqueries and convert them to joins, there exists a much better and more efficient way to achieve the same result as the previous query returned. You can do this by using the window functions.

The following query uses the SUM window aggregate function to calculate the total over each employee and the overall total. The OVER clause defines the partitions, or the windows of the calculation. The first calculation is partitioned over each employee, meaning that the total quantity per employee is reset to zero for each new employee. The second calculation uses an OVER clause without specifying partitions, meaning the calculation is done over all input rowsets. This query produces exactly the same result as the previous one:

SELECT e.EmployeeKey, e.LastName,
 fr.SalesAmount,
 SUM(fr.SalesAmount) OVER(PARTITION BY e.EmployeeKey)
  AS TotalPerEmployee,
 SUM(fr.SalesAmount) OVER()
 AS GrandTotal
FROM dbo.DimEmployee AS e
 INNER JOIN dbo.FactResellerSales AS fr
  ON e.EmployeeKey = fr.EmployeeKey
ORDER BY e.EmployeeKey;

Now assume that you need to calculate some statistics of the totals of the employees' orders. You need to calculate the running total amount for employees, and the moving average of this total over the last three employees, ordered by the employee key. This means you need to calculate the totals over employees in advance, and then use aggregate functions on these aggregates. You could do aggregations over employees in advance in a derived table. However, there is another way to achieve this. You can define the derived table in advance, in the WITH clause of the SELECT statement. This subquery is called a common table expression, or CTE.

CTEs are more readable than derived tables and might be also more efficient. You could use the result of the same CTE multiple times in the outer query. If you use derived tables, you need to define them multiple times if you want to use them multiple times in the outer query. The following query shows the usage of CTE to calculate the total amount for all employees and then just shows the results in the outer query:

WITH EmpTotalCTE AS
(
SELECT e.EmployeeKey,
 MIN(e.LastName) AS LastName,
 SUM(fr.SalesAmount) AS TotalPerEmployee
FROM dbo.DimEmployee AS e
 INNER JOIN dbo.FactResellerSales AS fr
  ON e.EmployeeKey = fr.EmployeeKey
GROUP BY e.EmployeeKey
)
SELECT EmployeeKey, LastName,
 TotalPerEmployee
FROM EmpTotalCTE
ORDER BY EmployeeKey;

The query returns 17 rows, one for each employee, with the total sales amount for this employee. Now let's add the running total and the moving average in the outer query:

WITH EmpTotalCTE AS
(
SELECT e.EmployeeKey,
 MIN(e.LastName) AS LastName,
 SUM(fr.SalesAmount) AS TotalPerEmployee
FROM dbo.DimEmployee AS e
 INNER JOIN dbo.FactResellerSales AS fr
  ON e.EmployeeKey = fr.EmployeeKey
GROUP BY e.EmployeeKey
)
SELECT EmployeeKey, LastName,
 TotalPerEmployee,
 SUM(TotalPerEmployee)
  OVER(ORDER BY EmploYeeKey
       ROWS BETWEEN UNBOUNDED PRECEDING
                AND CURRENT ROW)
  AS RunningTotal,
 AVG(TotalPerEmployee)
  OVER(ORDER BY EmploYeeKey

       ROWS BETWEEN 2 PRECEDING
                AND CURRENT ROW)
  AS MovingAverage
FROM EmpTotalCTE
ORDER BY EmployeeKey;

Here are the partial results with the first five and last two rows:

EmployeeKey LastName  TotelPerEmployee RunningTotal   MovingAverage
----------- --------- ---------------- -------------- -------------
272         Jiang     1092123.8562     1092123.8562   1092123.8562
281         Blythe    9293903.0055     10386026.8617  5193013.4308
282         Mitchell  10367007.4286    20753034.2903  6917678.0967
283         Carson    10065803.5429    30818837.8332  9908904.659
284         Vargas    3609447.2163     34428285.0495  8014086.0626
…
295         Valdez    1790640.2311     79028786.0571  1425236.791
296         Tsoflias  1421810.9252     80450596.9823  1128325.2026

Note that the running total for the last employee, sorted by the employee key, is the grand total. You can also check whether the running total and moving average are calculated correctly.

In the previous query, you can see that in the OVER() clause, I defined the frame of the calculation of the running total and the moving average for each row. For the running total, the frame is all rows from the first row in the window to the current row, and for the moving average, it is the last three rows, including the current row.

You can use many other functions for window calculations. For example, you can use the ranking functions, such as ROW_NUMBER(), to calculate some rank in the window or in the overall rowset. However, rank can be defined only over some order of the calculation. You can specify the order of the calculation in the ORDER BY sub-clause inside the OVER clause. Please note that this ORDER BY clause defines only the logical order of the calculation, and not the order of the rows returned. A standalone, outer ORDER BY at the end of the query defines the order of the result.

The following query calculates a sequential number, the row number of each row in the output, for each detail row of the input rowset. The row number is calculated once in partitions for each employee and defines the row number or the position of every single reseller for each employee, ordered by the sales amount, in descending order:

WITH EmpResTotalCTE AS
(
SELECT e.EmployeeKey, r.ResellerKey,
 MIN(e.LastName) AS LastName,
 MIN(r.ResellerName) AS ResellerName,
 SUM(fr.SalesAmount) AS EmpResTotal
FROM dbo.DimEmployee AS e
 INNER JOIN dbo.FactResellerSales AS fr
  ON e.EmployeeKey = fr.EmployeeKey
 INNER JOIN dbo.DimReseller AS r
  ON r.ResellerKey = fr.ResellerKey
GROUP BY e.EmployeeKey, r.ResellerKey
)
SELECT EmployeeKey, LastName,
 ResellerName, EmpResTotal,
 ROW_NUMBER()
  OVER(PARTITION BY EmployeeKey ORDER BY EmpResTotal DESC)
  AS PositionByEmployee
FROM EmpResTotalCTE
ORDER BY EmployeeKey, EmpResTotal DESC;

Here are the partial results:

EmployeeKey LastName  ResellerName      EmpResTotal  PositionByEmployee
----------- --------  --------------    -----------  ------------------
272         Jiang     Thorough Parts    198993.3507  1
272         Jiang     Sheet Metal       138046.3212  2
272         Jiang     Vigorous Exercise 125422.2079  3
272         Jiang     Sales and Supply  79994.1743   4
…
281         Blythe    Totes & Baskets   463030.757   1
281         Blythe    First Bike        437319.784   2
281         Blythe    Modular Cycle     389208.6639  3
281         Blythe    Fitness Toy Store 375100.3084  4
…
296         Tsoflias  Eastside Cycle    2223.7009    30
296         Tsoflias  Twin Cycles       1336.23      31
296         Tsoflias  Kids and Adults   753.768      32
296         Tsoflias  Major Bicycle     200.052      33

In the abbreviated results shown previously, each sales amount total for each reseller and employee combination is unique. That's why all of the row numbers are in the right position – 1 is before 2, which is before 3, and so on. Now imagine that the second and the third reseller (Sheet Metal and Vigorous Exercise) for the first employee (Jiang) would have the same total, for example 138,046.3212. Then you could get the wrong order of row numbers in the results, as shown here:

EmployeeKey LastName  ResellerName      EmpResTotal  PositionByEmployee
----------- --------  --------------    -----------  ------------------
272         Jiang     Thorough Parts    198993.3507  1

272         Jiang     Vigorous Exercise 138046.3212  3
272         Jiang     Sheet Metal       138046.3212  2
272         Jiang     Sales and Supply  79994.1743   4

The order of the result is defined over the sales total, and not over the row number. You can't know in advance which row will get which row number when the order of the calculation is not defined in unique values.

Let's find the top-6 sales based on the sales amount. You can do this by using the OFFSET…FETCH clause after the ORDER BY clause:

SELECT SalesOrderNumber,
 SalesOrderLineNumber,
 SalesAmount
FROM dbo.FactResellerSales
ORDER BY SalesAmount DESC
OFFSET 0 ROWS FETCH NEXT 6 ROWS ONLY;

Here are the results:

SalesOrderNumber  SalesOrderLineNumber SalesAmount
----------------- -------------------- -----------
SO55282           39                   27893.619
SO43884           17                   27055.7604
SO51131           12                   26159.2081
SO43875           12                   24938.4761
SO57054           26                   23667.8549
SO43875           10                   23190.6518

The question that arises is whether order SO43875, line number 10, is the only sale with the sales amount equal to 23190.6518. You could try to execute the previous query again, but with limiting the output to the first seven rows, then eight rows, and so on. SQL Server offers another possibility, the TOP clause. You can specify TOP n WITH TIES, meaning you can get all of the rows with ties on the last value in the output. However, this way, you don't know the number of the rows in the output in advance. The following query shows this approach:

SELECT TOP 6 WITH TIES
 SalesOrderNumber, SalesOrderLineNumber,
 SalesAmount
FROM dbo.FactResellerSales
ORDER BY SalesAmount DESC;

In the results of the last query, you get seven rows:

SalesOrderNumber  SalesOrderLineNumber SalesAmount
----------------- -------------------- -----------
SO55282           39                   27893.619
SO43884           17                   27055.7604
SO51131           12                   26159.2081
SO43875           12                   24938.4761
SO57054           26                   23667.8549
SO44795           18                   23190.6518
SO43875           10                   23190.6518

The next task is to get the top three resellers by amount for a specific employee. Here is the query that returns the top four resellers for the employee with the employee key equal to 272:

SELECT TOP 3
 fr.EmployeeKey, fr.ResellerKey,
 SUM(fr.SalesAmount) AS EmpResTotal
FROM dbo.FactResellerSales AS fr
WHERE fr.EmployeeKey = 272
GROUP BY fr.EmployeeKey, fr.ResellerKey
ORDER BY EmpResTotal DESC;

You need to perform the calculation for each employee. The APPLY Transact-SQL operator comes in handy here. You use it in the FROM clause. You apply, or execute, a table expression defined on the right side of the operator once for each row of the input rowset from the left side of the operator. There are two flavors of this operator. The CROSS APPLY version filters out the rows from the left rowset if the tabular expression on the right side does not return any row. The OUTER APPLY version preserves the row from the left side, even is the tabular expression on the right side does not return any row, similar to LEFT OUTER JOIN. Of course, columns for the preserved rows do not have known values from the right-side tabular expression. The following query uses the CROSS APPLY operator to calculate top three resellers by amount for each employee that actually does have some sales:

SELECT e.EmployeeKey, e.LastName,
 fr1.ResellerKey, fr1.EmpResTotal
FROM dbo.DimEmployee AS e
 CROSS APPLY
 (SELECT TOP 3
   fr.EmployeeKey, fr.ResellerKey,
   SUM(fr.SalesAmount) AS EmpResTotal
  FROM dbo.FactResellerSales AS fr

  WHERE fr.EmployeeKey = e.EmployeeKey
  GROUP BY fr.EmployeeKey, fr.ResellerKey
  ORDER BY EmpResTotal DESC) AS fr1
ORDER BY e.EmployeeKey, fr1.EmpResTotal DESC;

The query returns 51 rows. You can see the abbreviated results here:

EmployeeKey LastName   ResellerKey EmpResTotal
----------- ---------- ----------- -----------
272         Jiang      433         198993.3507
272         Jiang      436         138046.3212
272         Jiang      678         125422.2079
281         Blythe     328         463030.757
281         Blythe     670         437319.784
281         Blythe     4           389208.6639
…
296         Tsoflias    573        163398.0205
296         Tsoflias    87         148996.5063
296         Tsoflias    393        145407.7395

Since this was the last query in this chapter, you can clean up your demo database with the following code:

DROP TABLE dbo.EmpFUll;
GO

That's it for T-SQL for now. You will use this knowledge a lot in the forthcoming chapters for concrete data science tasks.

In this chapter, you were given a quick introduction to the mighty T-SQL SELECT statement. You are ready to query the data you have stored in a SQL Server database. However, T-SQL is a language that is specialized to work with data, to query and modify it. It logically operates on a whole dataset at once. When operating on a whole set of data, T-SQL is extremely efficient. However, it is not very efficient for advanced mathematical operations inside a single row and between rows in a row-by-row manner. This is not very appropriate for the data science tasks, where  you often need to perform such advanced mathematical analyses. Therefore, we need another language. In the next chapter, you will learn about the first option, the R language.