Modern Database Management (12th Edition)
12th Edition
ISBN: 9780133544619
Author: Jeffrey A. Hoffer, Ramesh Venkataraman, Heikki Topi
Publisher: PEARSON
expand_more
expand_more
format_list_bulleted
Concept explainers
Question
Chapter 7, Problem 7.49PAE
Program Plan Intro
SQL query (for every received order) to display orderID, total payable amount of the order, and the amount of payment received with respect to the order. Display the results in the decreasing order of the difference between the total due and the amount paid.
Expert Solution & Answer
Want to see the full answer?
Check out a sample textbook solution
Students have asked these similar questions
For every order that has been received, display the order ID, the total dollar amount owed on that order (you’ll have to calculate this total from attributes in one or more tables; label this result TotalDue), and the amount received in payments on that order (assume that there is only one payment made on each order). To make this query a little simpler, you don’t have to include those orders for which no payment has yet been received. List the results in decreasing order of the difference between total due and amount paid.
List the balance characteristics of the customers who have made purchases during the current invoice cycle—that is, for the customers who appear in the INVOICE table. The results of this query are shown in Figure P7.37.
SQL:
For every invoice, display the invoice number, invoice date and the total dollar amount for all products purchased in the invoice, ordered by invoice number in descending order and then by invoice date in ascending order. Here you will use an aggregate SUM(x,y) function in your SELECT clause to calculate the total dollar amount (a calculated field – name it TOTAL). Don’t forget to join on the tables as well. [hint: you should have 8 rows of output ordered properly].
My code is as follows:
select inv_number, inv_date, sum(line_price) as 'total' from invoice, line where invoice.inv_number = line.inv_number order by inv_number desc, inv_date asc ;
I am getting this error:
ORA-00923: FROM keyword not found where expected
Any recommendations on what I am doing wrong?
Chapter 7 Solutions
Modern Database Management (12th Edition)
Ch. 7 - Prob. 7.1RQCh. 7 - Prob. 7.2RQCh. 7 - Prob. 7.3RQCh. 7 - Prob. 7.4RQCh. 7 - Prob. 7.5RQCh. 7 - Explain the relationship between EXISTS and...Ch. 7 - Prob. 7.7RQCh. 7 - Prob. 7.8RQCh. 7 - Prob. 7.9RQCh. 7 - Prob. 7.10RQ
Ch. 7 - Under what conditions can a UNION clause be used?Ch. 7 - Prob. 7.12RQCh. 7 - Prob. 7.13RQCh. 7 - List for advantages of SQL-invoked routines.Ch. 7 - Prob. 7.15RQCh. 7 - Prob. 7.16RQCh. 7 - Prob. 7.17RQCh. 7 - Prob. 7.18RQCh. 7 - Prob. 7.19RQCh. 7 - Prob. 7.20RQCh. 7 - Prob. 7.21RQCh. 7 - Prob. 7.22RQCh. 7 - Prob. 7.23RQCh. 7 - This chapter discusses the data dictionary views...Ch. 7 - Prob. 7.25PAECh. 7 - Prob. 7.26PAECh. 7 - Prob. 7.27PAECh. 7 - Are based on the class schedule 3NF relations...Ch. 7 - Prob. 7.29PAECh. 7 - Prob. 7.30PAECh. 7 - Prob. 7.31PAECh. 7 - Are based on Figure 7-17. This problem set...Ch. 7 - Prob. 7.33PAECh. 7 - Prob. 7.34PAECh. 7 - Prob. 7.35PAECh. 7 - Prob. 7.36PAECh. 7 - Prob. 7.37PAECh. 7 - Prob. 7.38PAECh. 7 - Prob. 7.39PAECh. 7 - Prob. 7.40PAECh. 7 - Prob. 7.41PAECh. 7 - Prob. 7.42PAECh. 7 - Prob. 7.43PAECh. 7 - Prob. 7.44PAECh. 7 - Write an SQL query to display the order number,...Ch. 7 - Prob. 7.46PAECh. 7 - Prob. 7.47PAECh. 7 - Prob. 7.48PAECh. 7 - Prob. 7.49PAECh. 7 - Prob. 7.50PAECh. 7 - Write an SQL query to list each customer who...Ch. 7 - Prob. 7.52PAECh. 7 - Modify Problem and Exercise 6-60 so that the list...Ch. 7 - Prob. 7.54PAECh. 7 - Prob. 7.55PAECh. 7 - Prob. 7.56PAECh. 7 - Prob. 7.57PAECh. 7 - Prob. 7.58PAECh. 7 - Prob. 7.59PAECh. 7 - Prob. 7.60PAECh. 7 - Prob. 7.61PAECh. 7 - Prob. 7.62PAECh. 7 - Prob. 7.63PAECh. 7 - Rewrite your answer to Problem and Exercise 6-71...Ch. 7 - Display the customer ID, name, and order ID for...Ch. 7 - Prob. 7.66PAECh. 7 - Prob. 7.67PAECh. 7 - Prob. 7.68PAECh. 7 - Prob. 7.69PAECh. 7 - Prob. 7.70PAECh. 7 - Prob. 7.71PAECh. 7 - Write an SQL query to list the order number,...Ch. 7 - Prob. 7.73PAECh. 7 - Prob. 7.74PAECh. 7 - Prob. 7.75PAECh. 7 - Prob. 7.76PAE
Knowledge Booster
Learn more about
Need a deep-dive on the concept behind this application? Look no further. Learn more about this topic, computer-science and related others by exploring similar questions and additional content below.Similar questions
- Ensure that the only legal values for the BDRMS column in the CONDO_UNIT table are 1, 2, or 3.arrow_forwardSQL: For every invoice, display the invoice number, invoice date and the total dollar amount for all products purchased in the invoice, ordered by invoice number in descending order and then by invoice date in ascending order. Here you will use an aggregate SUM(x,y) function in your SELECT clause to calculate the total dollar amount (a calculated field – name it TOTAL). Don’t forget to join on the tables as well. [hint: you should have 8 rows of output ordered properly]. select inv_number, inv_date sum(list_price) as 'total' from invoice, line order by desc inv_num, asc inv_date. I am getting the error: ORA-00923: FROM keyword not found where expected Am I formulating the query incorrectly?arrow_forwardby using the table in the query attached: Show the name, contact number and email address of the customer who’s using a gmail account. PLEASE SHOW ANSWER IN MY SQL USING COMMAND LINE OR CMDarrow_forward
- Create a query that SELECT all of the EMPLOYEES from the EMPLOYEE TABLE that are Mangers. Include Employee ID first name, Lastname and Salary (Hint: Self Join) /* Write a query the gives the Employee ID first name, Lastname and Salary and Project Number of EMPLOYEES who aren't currently assigned to a project (hint outer join) Create a query that lists the lastnme, edlevel , job, the number of years they've worked as of Jan 01/2002 ( hint : year function Jan 01/2002 minus hiredate), and their salary. Get the employees that have the same Job as the employee named starts with J (hint subquery from employee) and hiredate < Jan 01/2002 Sort the listing by highest salary first. */ /* Create a table with a COMPOSITE PRIMARY KEY (mininum 3 columns the table) and the second table will have will have a foreign keys to the PRIMARY KEY Run the Reverse Engineer function in MySQL workbench on these tables and provide the .MWB file in your submission Create a table in 1NF…arrow_forwardreate a query that SELECT all of the EMPLOYEES from the EMPLOYEE TABLE that are Mangers. Include Employee ID first name, Lastname and Salary (Hint: Self Join) /* Write a query the gives the Employee ID first name, Lastname and Salary and Project Number of EMPLOYEES who aren't currently assigned to a project (hint outer join) Create a query that lists the lastnme, edlevel , job, the number of years they've worked as of Jan 01/2002 ( hint : year function Jan 01/2002 minus hiredate), and their salary. Get the employees that have the same Job as the employee named starts with J (hint subquery from employee) and hiredate < Jan 01/2002 Sort the listing by highest salary first. */arrow_forwardWrite SQL codes according to the requirements given. Tables are imaginary, column names can be made up. List the item ID, description, and category for each pair of items that are in the same category. (For example, one such pair would be item FS42 and item PF19, because the category for both items is FSH.) List the invoice number and invoice date for each invoice created for the customer James Gonzalez. Use a subquery to find the sales rep ID, first name, and last name of each sales rep who represents at least one customer with a credit limit of $500. List each sales rep only once in the results.arrow_forward
- Without re-creating the table write one sql statement to answer this question. Change the definition of Riverview_customer to include the customer type, but name this column custtype. Put a restriction on the table to ensure that the customer types will include values of O, L and N. Name this restriction valid_custtypesarrow_forwardList the customer ID and first and last names for all customers located in the city of Cody. Your query should ignore case. For example, a customer with the city Cody should be included, as should customers whose city is CODY, cody, cOdY, and so onarrow_forwardCUSTOMER Customer table is composed of customer number, name and phone number. Give and fill-in the appropriate attribute name for each of the column. custID 123 124 125 126 reservelD 5001 5002 5003 5004 5005 5006 5007 RESERVATION Each reservation is for one taxi. Reservation table is composed of reservation identification number, start reservation date, end reservation date, reservation days requested by customers starting from reservation date until end of reservation date, customer number that make the reservation and taxi number assigned to the reservation. Give and fill-in the appropriate attribute name for each of the column. taxill custName Ahmad Bin Abdullah Fatimah Binti Adam LAI LA2 Ruqayya Binti Idris Sulaiman Bin Daud LA3 LA4 startDate 01/10/2019 05/10/2019 05/10/2019 15/10/2019 20/10/2019 27/10/2019 02/11/2019 taxiType endDate 03/10/2019 12/10/2019 08/10/2019 17/10/2019 25/10/2019 Sedan Sedan Van Van 30/10/2019 04/11/2019 cust Phoneno TAXI Taxi table is composed of taxi…arrow_forward
- Write a query that returns the Staff ID for the staff member who is involved in the most rentalsarrow_forwardUse Common Table Expressions (CTE) to write a query that displays the sum of all purchase orders and sum of all sales orders per each shipping method using the TotalDue column. All shipping methods must be displayed regardless of whether they have matching sales or purchases. Show the ShipMethodID, name, purchase orders total and sales orders total. Round all values to 2 decimal places. Hint: Use mutliple CTEs. Below is a sample of expected results:arrow_forwardManagement 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…arrow_forward
arrow_back_ios
SEE MORE QUESTIONS
arrow_forward_ios
Recommended textbooks for you
A Guide to SQLComputer ScienceISBN:9781111527273Author:Philip J. PrattPublisher:Course Technology Ptr
Database Systems: Design, Implementation, & Manag...Computer ScienceISBN:9781305627482Author:Carlos Coronel, Steven MorrisPublisher:Cengage Learning
Programming with Microsoft Visual Basic 2017Computer ScienceISBN:9781337102124Author:Diane ZakPublisher:Cengage Learning
Np Ms Office 365/Excel 2016 I NtermedComputer ScienceISBN:9781337508841Author:CareyPublisher:Cengage
A Guide to SQL
Computer Science
ISBN:9781111527273
Author:Philip J. Pratt
Publisher:Course Technology Ptr
Database Systems: Design, Implementation, & Manag...
Computer Science
ISBN:9781305627482
Author:Carlos Coronel, Steven Morris
Publisher:Cengage Learning
Programming with Microsoft Visual Basic 2017
Computer Science
ISBN:9781337102124
Author:Diane Zak
Publisher:Cengage Learning
Np Ms Office 365/Excel 2016 I Ntermed
Computer Science
ISBN:9781337508841
Author:Carey
Publisher:Cengage