Using the output shown in Figure P7.12 as your guide, generate a list of customer purchases, including the subtotals for each of the invoice line numbers. The subtotal is a derived attribute calculated by multiplying LINE_UNITSS by LINE_PRICE . Sort the output by customer code, invoice number, and product description. Be certain to use the column aliases as shown in the figure. CUs CODE Units Bought INV_NUMBER P_DESCRIPT Unit Price Subtotal Rat-tail file, 1/8-in. fine 10011 1002 2.00 4.99 9.98 10011 1004 Claw hammer 2.00 9.95 19.90 Rat-tail file, 1/8-in. fine 10011 1004 3.00 4.99 14.97 10011 1008 Claw hammer 1.00 9.95 9.95 10011 1008 PVC pipe, 3.5-in., 8-ft 5.00 5.87 29.35 10011 1008 Steel matting, 48'x1/6", .5" mesh 3.00 119.95 359.85 10012 1003 7.25-in. pwr. saw blade 5.00 14.99 74.95 10012 1003 B&D cordless drill, 1/2-in. 1.00 38.95 38.95 10012 1003 Hrd. cloth, 1/4-in., 2x50 1.00 39.95 39.95 10014 1001 7.25-in. pwr. saw blade 1.00 14.99 14.99

Using the output shown in Figure P7.12 as your guide, generate a list of customer purchases, including the subtotals for each of the invoice line numbers. The subtotal is a derived attribute calculated by multiplying LINE_UNITSS by LINE_PRICE . Sort the output by customer code, invoice number, and product description. Be certain to use the column aliases as shown in the figure. CUs CODE Units Bought INV_NUMBER P_DESCRIPT Unit Price Subtotal Rat-tail file, 1/8-in. fine 10011 1002 2.00 4.99 9.98 10011 1004 Claw hammer 2.00 9.95 19.90 Rat-tail file, 1/8-in. fine 10011 1004 3.00 4.99 14.97 10011 1008 Claw hammer 1.00 9.95 9.95 10011 1008 PVC pipe, 3.5-in., 8-ft 5.00 5.87 29.35 10011 1008 Steel matting, 48'x1/6", .5" mesh 3.00 119.95 359.85 10012 1003 7.25-in. pwr. saw blade 5.00 14.99 74.95 10012 1003 B&D cordless drill, 1/2-in. 1.00 38.95 38.95 10012 1003 Hrd. cloth, 1/4-in., 2x50 1.00 39.95 39.95 10014 1001 7.25-in. pwr. saw blade 1.00 14.99 14.99

Database System Concepts

7th Edition

ISBN:9780078022159

Author:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan

Publisher:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan

Chapter1: Introduction

Section: Chapter Questions

Problem 1PE

See similar textbooks

Similar questions

Create a new table, EMP_PROMOTION to store the employees information. You must use a subquery to create the new table. Make the salary column invisible. Display the structure of the table in SQL Developer. Also use a SELECT statement to display all records from the new table EMP_PROMOTION.
Reference table 6-28. Include both SQL commands and results/answers in your answers. In the NONCAT table, increase the price of each item in category BRD by five percent. (Hint: Multiply each price by 1.05.) In the NONCAT table, increase the length of the DESCRIPTION column to 40 characters.
Management is considering closing on Sundays and would like to have a feeling for the kinds of customers that come to the dealership on Sunday. Create a totals query that shows the count of encounters on the Sundays of July 2015—grouped by the credit description held by the customer of the encounter. The Sundays in July of 2015 are the 5th, 12th, 19th, and 26th. Your answer should have two columns (CreditDescription and encounter_count) and should be sorted alphabetically by credit description. This is what Ive been working with and doesn't give me results, just error messages. SELECT R.CreditDescription, E.CustomerID FROM Encounter E JOIN Customer C ON E.CustomerID = C.CustomerID JOIN CreditRating R ON C.CreditID = R.CreditID WHERE E.EncDate = STR_TO_DATE('2015-07-05','2015-07-12','2015-07-19','2015-07-26'); ORDER BY creditrating.creditid, ASC; creditrating creditid creditdescription minfico maxfico comments customer customerid cfirstname clastname…
The Colombia City office of StayWell indicated that there is a new tenant by the name of Yigit Yilmaz staying at the property with an ID of 13. You need to include this new resident in the RESIDENTS table.
please use input variable for live sql and explain the code
Create a View to show the city name of any location that has a department. Only show the city name once in the results. You must use a non-correlated subquery.Call this view: Department_LocationsInclude a second query to display from the View after it is created. Refer to the attached for the table.
Given the table PRODUCT(ProductID, ProductName, ProductClass, ProductWeight, ProductStyle, ProductColor, ProductPrice). Note: ProductWeight and ProductPrice are numeric fields, and the other attributes are character fields, i.e., store text. Write a SQL query to display the product class whose average price is at least 50
Add a column named ON_HAND_VALUE to the NONCAT table. The on-hand value is a seven-digit number with two decimal places that represents the product of the number of units on hand and the price. Then set all values of ON_HAND_VALUE to ON_HAND * PRICE.
SQL "List the item ID, description, and on-hand value (units on hand * unit price) of each item in category CAT. (On-hand value is technically units on hand * cost, but there is no COST column in the ITEM table). Assign the name ON_HAND_VALUE to the computed column. The query is calculating the total on-hand value of items by category. The query will need to use the SUM function to add the total on-hand value of items in each category." I tried SELECT ITEM_ID, DESCRIPTION, ON_HAND * PRICE AS ON_HAND_VALUE FROM ITEM WHERE CATEGORY = 'CAT'; The query was completed, however it didn't display the right results. What am I doing wrong?
Rewrite the query below to also show INDATE as [Stock Date]: SELECT PRODUCT.P_CODE, PRODUCT.P_DESCRIPT AS DESCRIPTION, PRODUCT.P_PRICE AS [Unit Price], PRODUCT.P_QOH AS QTYFROM PRODUCT; 2) Add ORDER BY INDATE DESC to the above query. 3) Rewrite the query below to show only products with a discount: SELECT PRODUCT.P_DESCRIPT, PRODUCT.P_QOH, PRODUCT.P_PRICE, [P_PRICE]*[P_Qoh] AS Expr1FROM PRODUCT;
Using the STUDENT table structure shown, do the following: Attribute Name Sample Value Sample Value Sample Value Sample Value Sample Value INV_NUM 211347 211347 211347 211348 211349 PROD_NUM AA-E3422QW QD-300932X RU-995748G AA-E3422QW GH-778345P SALE_DATE 15-Jan-2018 15-Jan-2018 15-Jan-2018 15-Jan-2018 16-Jan-2018 PROD_LABEL Rotary sander 0.25-in. drill bit Band saw Rotary sander Power drill VEND_CODE 211 211 309 211 157 VEND_NAME NeverFail, Inc. NeverFail, Inc. BeGood, Inc. NeverFail, Inc. ToughGo, Inc. QUANT_SOLD 1 8 1 2 1 PROD_PRICE $49.95 $3.45 $39.99 $49.95 $87.75 A. Write the relational schema, draw its dependency diagram and identify all dependencies, including all partial and transitive dependencies. You can assume that the table does not contain repeating groups and that any invoice number may reference more than one product. (Hint: This table…
For this problem create a (temporary) table called instructor_course_nums. Write a procedure that accepts an instructor ID as input. The procedure calculates the total number of course sections taught by that instructor, and adds a tuple to the temporary table consisting of the instructors ID number, name, and total courses taught - call these attributes: ID, name, and tot_courses. If the instructor already has an entry in the table,then the procedure makes sure the total number of courses taught in the temporary table is up-to-date. You must name your procedure: Written in PostgreSQL