Question

1. We are preparing to archive old data from the Sales Orders Modify database. Before we do this, we would like to update the OrderTotal column for each order to be sure that it reflects the total of the line items in that order. First, write a SELECT query showing what the OrderTotal should be for each order. Include three columns: the order number, the current order total and the calculated order total (as NewOrderTotal). Use a subquery as the third column to calculate the total of the line items for that order. (944 rows)

Answer 1

To update the OrderTotal column for each order in the Sales Orders Modify database, you can use a SELECT query with a subquery to calculate the total of the line items for each order.

Step-by-step explanation:

To update the OrderTotal column for each order in the Sales Orders Modify database, you can use a SELECT query with a subquery to calculate the total of the line items for each order. Here is an example of how such a query could be written:

SELECT OrderNumber, OrderTotal, (SELECT SUM(LineTotal) FROM LineItems WHERE OrderNumber = Orders.OrderNumber) AS NewOrderTotal FROM Orders;

In this query, the subquery calculates the SUM of the LineTotal column from the LineItems table for each order, while the main query selects the OrderNumber and OrderTotal columns from the Orders table. The calculated total is included as the column NewOrderTotal.