Learn Data Transformation Through Practice
Explore how to transform and present data effectively by writing SQL queries using aggregates, joins, CAST, and CONVERT functions. Learn to retrieve filtered and formatted data for diverse business needs including sales analysis and product information display.
We'll cover the following...
Problem
Please refer to the tables below.
Prod.Products Table
ProductID | Name | ProductNumber | Color | StandardCost | ListPrice | SIZE |
1 | LL Crankarm | CA-5965 | Black | 0 | 100 | NULL |
2 | ML Crankarm | CA-6738 | Black | 0 | 300 | NULL |
3 | HL Crankarm | CA-7457 | Black | 0 | 100 | NULL |
4 | Chainring Bolts | CB-2903 | Silver | 0 | 400 | NULL |
5 | Chainring Nuts | CN-6137 | Silver | 0 | 400 | NULL |
Sales.ProductSales Table
ProductID | UnitPrice | Quantity | CustomerID | SuptPkgID | SuptPkgCost |
1 | 1000.00 | 1 | 1 | 1 | 0.00 |
1 | 1000.00 | 1 | 2 | 1 | 0.00 |
2 | 1500.00 | 2 | 3 | 2 | 250.00 |
3 | 750.00 | 1 | 4 | 2 | 250.00 |
4 | 2500.00 | 1 | 5 | 2 | 250.00 |
1 | 1000.00 | 1 | 5 | 2 | 250.00 |
Cust.Customers Table
CustomerID | CustomerName | FTPAddress |
1 | GeoStudy,Inc | ftp.geostudy.com |
2 | Weather Watchers | sftp.weatherinfo.com |
3 | GIS In Motion | ftp.gismotion.com |
4 | GIS Tracker | sftp.gistracking.com |
5 | Earth Mapping | sftp.emap.com |
Part A
Suppose a person from the sales team only needs to see the top ten latest sales records because they need to gather information about those particular customers. Create a query that they can use to retrieve the ProductID, UnitPrice, Quantity, ProdTotalCost, and CustomerID.
Remember to present the ten latest records, we would have to sort them by newest to oldest.
Part B
Suppose a sales manager ...