103 lines
3.2 KiB
Markdown
103 lines
3.2 KiB
Markdown
|
# Lab 2
|
||
|
|
|
||
|
|
*UPDATED: 12 Feb. Clarified scenario 6, 7 and 8.*
|
||
|
|
|
||
|
|
This lab has you practice advanced queries against tables with aggregates, grouping and subqueries.
|
||
|
|
|
||
|
|
For each scenario:
|
||
|
|
|
||
|
|
- Include the scenario #
|
||
|
|
- The SQL query (properly formatted)
|
||
|
|
|
||
|
|
*Note: DO NOT hard code any primary key IDs in your queries.*
|
||
|
|
|
||
|
|
This lab uses the `WideWorldImporters` database.
|
||
|
|
|
||
|
|
## Scenario 1
|
||
|
|
|
||
|
|
Get the lowest (as `LowestTemperature`) and highest temperature (as `HighestTemperature`) recorded for the vehicle temperatures (`Warehouse.VehicleTemperatures`).
|
||
|
|
|
||
|
|
Rows: 1
|
||
|
|
|
||
|
|
## Scenario 2
|
||
|
|
|
||
|
|
Gets the list of `SalesPersonPersonID` and their total sales order counts (as `TotalOrders`) from `Sales.Orders`.
|
||
|
|
|
||
|
|
Order the results by the highest total orders.
|
||
|
|
|
||
|
|
Rows: 10
|
||
|
|
|
||
|
|
## Scenario 3
|
||
|
|
|
||
|
|
Get the customer ID and total number of invoices (as `TotalInvoices`) from `Sales.Invoices` for the year 2015.
|
||
|
|
|
||
|
|
Group the invoices by the customer ID.
|
||
|
|
|
||
|
|
Order the results by the highest number of invoices descending followed by the customer ID.
|
||
|
|
|
||
|
|
Rows: 657
|
||
|
|
|
||
|
|
## Scenario 4
|
||
|
|
|
||
|
|
Get the order ID and total price (as `TotalOrderPrice`) from `Sales.Orders` for the customer `Debbie Molina` for the year 2016.
|
||
|
|
The total order price is the `Quantity` x `UnitPrice` of the order lines associated with the order.
|
||
|
|
|
||
|
|
Order the results by the total price from highest to lowest.
|
||
|
|
|
||
|
|
Rows: 10
|
||
|
|
|
||
|
|
## Scenario 5
|
||
|
|
|
||
|
|
Get the customer ID and total orders (as `TotalOrders`) from `Sales.Orders` for all customers who have at least 20 orders in 2016.
|
||
|
|
|
||
|
|
Order the results by the total orders descending and then by customer ID.
|
||
|
|
|
||
|
|
Rows: 87
|
||
|
|
|
||
|
|
## Scenario 6
|
||
|
|
|
||
|
|
Get the stock item ID, stock item name from and total quantity sold of the top 50 stock items sold (based upon quantity) of all time.
|
||
|
|
|
||
|
|
Order the results by the total quantity descending.
|
||
|
|
|
||
|
|
This will require two queries. The first query will get the stock items and the total quantity sold from the `OrderLines` table. For example if stock item 1
|
||
|
|
is on 3 orders with a quantity of 1, 3 and 5 then the total quantity is 9.
|
||
|
|
|
||
|
|
The second query will combine the stock item totals with the stock items from `Warehouse.StockItems` to get the top 50 items.
|
||
|
|
|
||
|
|
*Note: This is using `OrdersLines` and not `InvoiceLines`.*
|
||
|
|
|
||
|
|
*Note: This requires a subquery or CTE. A join by itself will not work.*
|
||
|
|
|
||
|
|
Rows: 50
|
||
|
|
|
||
|
|
## Scenario 7
|
||
|
|
|
||
|
|
Get the customer ID, customer name and total orders for the top 10 customers who have the LEAST number of orders.
|
||
|
|
|
||
|
|
Order the results by total orders and then by customer name.
|
||
|
|
|
||
|
|
This will require two queries. The first query will get the customers and their total orders.
|
||
|
|
|
||
|
|
The second query will combine the total order query with the remaining customer data to get the top 10.
|
||
|
|
|
||
|
|
*Note: Some customers may have no orders so they should show up in the top 10, if they exist.*
|
||
|
|
|
||
|
|
*Note: This will require either a subquery or a CTE.*
|
||
|
|
|
||
|
|
Rows: 10
|
||
|
|
|
||
|
|
## Scenario 8
|
||
|
|
|
||
|
|
Get the delivery method ID and name from `Application.DeliveryMethods` that are not used in the `Sales.Customers` table.
|
||
|
|
|
||
|
|
Order by the delivery method name.
|
||
|
|
|
||
|
|
This will require two queries. The first query should get the delivery methods that are not being used.
|
||
|
|
|
||
|
|
The second query should combine the unused methods with the remaining data needed to generate the desired results.
|
||
|
|
|
||
|
|
*Note: This will require either a subquery or a CTE.*
|
||
|
|
|
||
|
|
Rows: 9
|