Write a query that will be used for invoices.  The query needs to show Order Line level information, and then the final row will show the Order Totals.  To earn the extra credit, you must use the UNION set operator.

The OrderLine level columns should include OrderID, OrderDate, ProductName, Price, Quantity, and LineTotal.

The Order Total level information should include OrderID, OrderDate, and OrderTotal.  For ProductName, show "Order Total."  Align OrderTotal with LineTotal.

Hints:

1. Write a query that shows 1 row for each Order Line from OrderID 6 with the columns specified above.
2. Write a separate query with 1 row showing the Order Total with the columns specified above.
3. Make sure the queries are Union Compatible
4. Combine the queries by adding the UNION keyword between them
5.  

Transcribed Image Text:

PK PK Employees EmployeelD FirstName MiddleName LastName StreetAddress City State ZipCode Title Salary SMALLINT +++ VARCHAR(25) VARCHAR(25) VARCHAR(25) VARCHAR(35) VARCHAR(25) CHAR(2) VARCHAR(10) VARCHAR(25) DECIMAL (8,2) Have Dependents FirstName LastName: BirthDate SSN EmployeelD SMALLINT DependentNumber TINYINT VARCHAR(25) VARCHAR(25) DATE CHAR(11) Fulfill PK OPK FK FK Customers MEDIUMINT HH VARCHAR(25) VARCHAR(25) VARCHAR(25) StreetAddress VARCHAR(35) VARCHAR(25) Customer D FirstName MiddleName LastName City State ZipCode # I Orders OrderID EmployeelD CustomerlD OrderDate OrderTime PK, FK OrderlD PK, FK ProductID Price Quantity CHAR(2) VARCHAR(10) Place INT SMALLINT MEDIUMINT DATE TIME Have OrderLines INT SMALLINT DECIMAL(7.2) SMALLINT H+ Have Have Sold On KPK, FK Customer D PK 士PK CustomerEmails PK, FK PK KPK, FK OrderlD PK, FK StatusID EmailAddress PrimaryEmailFlag CHAR(1) OrderStatuses StatusDate Status Time SalesPrice Products ProductID ProductName ProductDescription InventoryOnHand MEDIIUMINT VARCHAR(254) Have INT TINYINT DATE TIME SMALLINT |||| VARCHAR(25) VARCHAR(100) DECIMAL (7.2) SMALLINT ProductPriceHistory SMALLINT ProductID PriceChangeDate DATE SalesPrice DECIMAL(7.2) Are On Supplied By HPK Statuses TINYINT VARCHAR(25) StatusDescription VARCHAR(100) StatusID StatusName ProductVendors KPK, FK ProductID PK, FK VendorID UnitCost SMALLINT SMALLINT DECIMAL(7.2) Supply HPK Vendors SMALLINT VARCHAR(25) VendorDescription VARCHAR(100) VendorID VendorName PK PK Employees EmployeelD FirstName MiddleName LastName StreetAddress City State ZipCode Title Salary SMALLINT +++ VARCHAR(25) VARCHAR(25) VARCHAR(25) VARCHAR(35) VARCHAR(25) CHAR(2) VARCHAR(10) VARCHAR(25) DECIMAL (8,2) Have Dependents FirstName LastName: BirthDate SSN EmployeelD SMALLINT DependentNumber TINYINT VARCHAR(25) VARCHAR(25) DATE CHAR(11) Fulfill PK OPK FK FK Customers MEDIUMINT HH VARCHAR(25) VARCHAR(25) VARCHAR(25) StreetAddress VARCHAR(35) VARCHAR(25) Customer D FirstName MiddleName LastName City State ZipCode # I Orders OrderID EmployeelD CustomerlD OrderDate OrderTime PK, FK OrderlD PK, FK ProductID Price Quantity CHAR(2) VARCHAR(10) Place INT SMALLINT MEDIUMINT DATE TIME Have OrderLines INT SMALLINT DECIMAL(7.2) SMALLINT H+ Have Have Sold On KPK, FK Customer D PK 士PK CustomerEmails PK, FK PK KPK, FK OrderlD PK, FK StatusID EmailAddress PrimaryEmailFlag CHAR(1) OrderStatuses StatusDate Status Time SalesPrice Products ProductID ProductName ProductDescription InventoryOnHand MEDIIUMINT VARCHAR(254) Have INT TINYINT DATE TIME SMALLINT |||| VARCHAR(25) VARCHAR(100) DECIMAL (7.2) SMALLINT ProductPriceHistory SMALLINT ProductID PriceChangeDate DATE SalesPrice DECIMAL(7.2) Are On Supplied By HPK Statuses TINYINT VARCHAR(25) StatusDescription VARCHAR(100) StatusID StatusName ProductVendors KPK, FK ProductID PK, FK VendorID UnitCost SMALLINT SMALLINT DECIMAL(7.2) Supply HPK Vendors SMALLINT VARCHAR(25) VendorDescription VARCHAR(100) VendorID VendorName

Answer rating: 100% (QA)

To create a query that shows Order Line level information and includes a final row for Order Totals ... View the full answer