Data Transformation
Learn data transformation through subqueries: cast, convert, union, and union all.
We'll cover the following...
The examples in the lessons refer to the “Sales.ProductSales” table.
Sales.ProductSales Table
TransactionID | ProductID | UnitPrice | Quantity | CustomerID | Supt_PkgID | Supt_PkgCost |
1 | 1 | 1000.00 | 1 | 1 | 1 | 0.00 |
2 | 1 | 1000.00 | 1 | 2 | 1 | 0.00 |
3 | 2 | 1500.00 | 2 | 3 | 2 | 250.00 |
4 | 3 | 750.00 | 1 | 4 | 2 | 250.00 |
5 | 4 | 2500.00 | 1 | 5 | 2 | 250.00 |
6 | 1 | 1000.00 | 1 | 5 | 2 | 250.00 |
Subqueries
A subquery is a query that is nested within another query. These can be nested in SELECT, INSERT, UPDATE, and DELETE statements, as well as the WHERE clause. When used in the WHERE clause, it creates further conditions.
In the example below, the inner query in line 3 runs first and returns the highest value in the UnitPrice column. The outer query will then run and use the results from the subquery for its WHERE condition. Overall, we want to obtain all columns/headers for the order ...