**Problem 12**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_UNITS` 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.**Table Description (Figure P7.12):**The table lists customer purchases and includes the following columns:- **CUS_CODE**: Customer Code- **INV_NUMBER**: Invoice Number- **P_DESCRIPT**: Product Description- **Units Bought**: Number of Units Purchased- **Unit Price**: Price per Unit- **Subtotal**: Total Price for the Line Item (Units Bought x Unit Price)**Table Contents:**| CUS_CODE | INV_NUMBER | P_DESCRIPT | Units Bought | Unit Price | Subtotal ||----------|------------|--------------------------------|--------------|------------|----------|| 10011 | 1002 | Rat-tail file, 1/8-in. fine | 2.00 | 4.99 | 9.98 || 10011 | 1004 | Claw hammer | 2.00 | 9.95 | 19.90 || 10011 | 1004 | Rat-tail file, 1/8-in. fine | 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, 4'x8'x1/6', .5" mesh | 3.00 | 119.95 | 359.85 || 10012 | 1003 | 7.25-in. pwr. saw blade | 5.00 | 14.99 | 74.95 ||

Query syntax: SELECT column1, column2,...columnNFROM table_name[WHERE][GROUP BY][HAVING][ORDER BY column(s) [ASC|DESC]]Task :We need to provide query that generate list of customer purchases, including the subtotals for each of theinvoice line numbers. Required Query : SELECT INVOICE.CUS_CODE, INVOICE.INV_NUMBER, PRODUCT.P_DESCRIPT, LINE.LINE_UNITS AS 'Units Bought',LINE.LINE_PRICE AS 'Unit Price', LINE.LINE_UNITS*LINE.LINE_PRICE AS Subtotal FROM CUSTOMER, INVOICE, LINE, PRODUCTWHERE CUSTOMER.CUS_CODE = INVOICE.CUS_CODE AND INVOICE.INV_NUMBER =LINE.INV_NUMBER AND PRODUCT.P_CODE = LINE.P_CODE ORDER BY INVOICE.CUS_CODE, INVOICE.INV_NUMBER, PRODUCT.P_DESCRIPT;

Engineering

Computer Science

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_UNITS 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.

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_UNITS 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.

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 TABLE employees8(employee_id NUMBER(10) PRIMARY KEY,first_name VARCHAR(25) UNIQUE,last_name VARCHAR(25) UNIQUE,job_id NUMBER(3),salary NUMBER(6) NOT NULL,department_name VARCHAR2(20));CREATE TABLE jobs8(job_id NUMBER(3) PRIMARY KEY,job_title VARCHAR2(25),min_salary NUMBER(4)check(min_salary >=2900),max_salary NUMBER(6)check(max_salary<=100000)); CREATE TABLE departments8(department_id NUMBER(2) PRIMARY KEY,department_name VARCHAR2(30),location_name VARCHAR2(15) DEFAULT 'Istanbul'); how can I do this question according to these 3 tables ? Create a complex view that joining with three tables.
The 'customer' table contains ten columns but no row. The table is then updated with 10 new rows and 3 rows removed. What is the table 'customer's degree and cardinality?
Display the first 10 rows of the invoice number, 2 line numbers, 2 product SKUs, 2 product descriptions, and brand ID for the sales of “sealer” and “top-coat” related products of the same brand on the same invoice sorted by the invoice number. (See partial result below, it has only 2 rows) Hints: Assign two different alias to both the"lin" and "product" tables. Then do the joins treating them as 4 differnet tables.
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.
The 'customer' table contains ten columns but no rows. The table is then updated with 10 new rows and 3 rows discarded. What is the table'customer's degree and cardinality?
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…
In our dataset, each job has a predefined salary range. Expand on the previous query and explore this salary range per customer. The query should display the attributes as problem 1 but also include the name of their position, and salary ranges for each employee. Finally, add a column that calculates the difference between each employee’s current salary and the base (minimum) salary for their job. Renamed this last calculated field as “Pay_above_minimum”.
Create a procedure to allow company employee to add new product to the database. This procedure needs only IN parameters.
Task 6: The Car Maintenance team considered that the available maintenance tasks should also have the price information in the database. Alter the MAINTENANCE_TYPES table to include a new column named MAINTENANCE_PRICE of type FLOAT. ANSWER IN MYSQL PLEASE
In cell D5, create a formula using the VLOOKUP function to determine the cost per participant for the Game Room workshop based on the Workshop ID in cell A5. Use range H4:I11 as the lookup table and the cost per participant listed in column 2 of the lookup table. Do not enter a calue for the optionional range_lookup argument.
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
Task 2: ' The Driver Relationship team wants to ensure that the all driving license IDs in the active drivers table have the length of 7. Alter the ACTIVE_DRIVERS table created in Chapter 8, Activity 1 to check the length of the DRIVER_DRIVING_LICENSE_ID.