Learn Data Transformation Through Practice
Practice the concepts of distinct, stored procedures, and views.
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 has come to get some information regarding current sales and they would like a list ...