Question

Using SQL, write a query that will display the vendor name and the amount due for...

Using SQL, write a query that will display the vendor name and the amount due for each vendor. The Vendor table has a list of vendor id and vendor name. The invoice table holds invoice num, vendor id, total amt, payment amt, and credit amount. To calculate amount due, you will take the total amt and subtract payment and credit amount to get this final total. Each vendor should only appear once in the display and amount due will be displayed as currency.

Homework Answers

Answer #1

Query ---- select vendor_name,TO_CHAR(round(sum(total_amt-credit_amt-payment_amt),2),'L99G999D99MI',
'NLS_NUMERIC_CHARACTERS = ''. ''
NLS_CURRENCY = ''$'' ') as AmountDue from vendor v join invoice i on i.vendor_id=v.vendor_id group by vendor_name;

Output

DDL statement for tests

create table vendor(
vendor_id int primary key,
vendor_name varchar(50)
);

create table invoice
(
invoice_id int primary key,
vendor_id int references vendor(vendor_id),
total_amt numeric(10,2),
payment_amt numeric(10,2),
credit_amt numeric(10,2)
);

insert into vendor values(1,'ABC');
insert into vendor values (2,'Dip');
insert into vendor values(3,'Roma');

insert into invoice values(1,1,1000,200,200);
insert into invoice values(2,2,700,300,200);
insert into invoice values(3,3,900,300,200);
insert into invoice values(4,2,800,300,200);
insert into invoice values(5,3,600,300,200);
insert into invoice values(6,2,800,230,200);
insert into invoice values(6,2,800,230,200);
insert into invoice values(7,1,500,230.20,200);


select vendor_name,TO_CHAR(round(sum(total_amt-credit_amt-payment_amt),2),'L99G999D99MI',
'NLS_NUMERIC_CHARACTERS = ''. ''
NLS_CURRENCY = ''$'' ') as AmountDue from vendor v join invoice i on i.vendor_id=v.vendor_id group by vendor_name;

Explanation

In Oracle the format for currency is

'NLS_NUMERIC_CHARACTERS = ''dg''
   NLS_CURRENCY = ''text''
   NLS_ISO_CURRENCY = territory  in to_char

dg repesents The characters d and g represent the decimal character and group separator, respectively. They must be different single-byte characters. Here it is a .

Here in this case L99G999D99MI(for large values use L999G999D99MI in accordance to the size otherwise you may get a ######)  L represents for local MI for trailing minus

9G999 Returns in the specified position the group separator (the current value of the NLS_NUMERIC_CHARACTER parameter). You can specify multiple group separators in a number format model.

Similarly 99D99 Returns in the specified position the decimal character, which is the current value of the NLS_NUMERIC_CHARACTER parameter. The default is a period (.).

Know the answer?
Your Answer:

Post as a guest

Your Name:

What's your source?

Earn Coins

Coins can be redeemed for fabulous gifts.

Not the answer you're looking for?
Ask your own homework help question
Similar Questions
Using CIS495 sample database, write the SQL query: a. In the orderdetails table, list the unique...
Using CIS495 sample database, write the SQL query: a. In the orderdetails table, list the unique order which has more than 3 productCode in each order. b. List the customer name and address who purchase more than 5 items (ProductCode) in the order.  
Write a SQL statement for the following query: Relational Schema: Part( P #, PName, Producer, Year,...
Write a SQL statement for the following query: Relational Schema: Part( P #, PName, Producer, Year, Price) (P# is the primary key) Customer( C#, CName, City) (C# is the primary key) Supply(S#, P#, C#, Quantity, Amount, Date) (S# is the primary key) Query: For each Apple product supplied to more than 10 different customers in Chicago in 2020, list product number, product name, total supply quantity, and total supply amount. Sort the result by product name.
Question 1: Group by and Aggregates: Write SQL statements to answer the following questions using Assignment...
Question 1: Group by and Aggregates: Write SQL statements to answer the following questions using Assignment 4’s schema (Customer-Invoice-Line-Product-Vendor). Make sure that your SQL script runs without any errors. Submit your answers in a .SQL file. 1 (2 Points) - Find the count of distinctvendors thatsupplied products that are priced lowerthan 185? 2 (2 Points) - For each vendor, find their product that has the lowest product quantity. Your output should include vendor code, vendor name, product description and product...
I'm really struggling with these homework questions it's mainly 1 and 2, so if you can't...
I'm really struggling with these homework questions it's mainly 1 and 2, so if you can't help with all then please just one and two I've spent hours on those two and can't seem to get past them. SQL That being said, if you could help with these 5 I would very much appreciate it: INVOICES(table) Columns INVOICE_ID VENDOR_ID INVOICE_NUMBER INVOICE_DATE INVOICE_TOTAL PAYMENT_TOTAL CREDIT_TOTAL TERMS_ID INVOICE_DUE_DATE PAYMENT_DATE VENDORS(table) Columns VENDOR_ID VENDOR_NAME VENDOR_ADDRESS1 VENDOR_ADDRESS2 VENDOR_CITY VENDOR_STATE VENDOR_ZIP_CODE VENDOR_PHONE DEFAULT_TERMS_ID DEFAULT_ACCOUNT_NUMBER GENERAL_LEDGER_ACCOUNTS(table)...
Perform SQL queries in the given data structure. write an SQL query that gives the number...
Perform SQL queries in the given data structure. write an SQL query that gives the number of courses taken for every student in the student table. For each instructor, show the instructor name and the number of sections that have been taught by that instructor. You do not need to include instructors who have never taught a section. List in order of decreasing the number of sections taught. Give the number of semester/year combinations in which sections have been offered....
Use the Donor database attached to the assignment answer the following: Question 1: Create a block...
Use the Donor database attached to the assignment answer the following: Question 1: Create a block to retrieve and display pledge and payment information for a specific donor. For each pledge payment from the donor, display the pledge ID, pledge amount, number of monthly payments, payment date, and payment amount. The list should be sorted by pledge ID and then by payment date. For the first payment made for each pledge, display “first payment” on that output row. 2 marks...
Save your select statements as a script. Place the semicolon at the end of each SQL...
Save your select statements as a script. Place the semicolon at the end of each SQL statement. Please number your select statements from 1 to 8 in your script and comment out each number. Include your name and student number as a comment at the top of your script. The name of the script has to be Assignment1_JohnSmith. Instead of JohnSmith use your First Name and Last Name. Upload your script trough Blackboard. Use SQL Developer to create the My...
Project 2 statement Please write this in JAVA. Please read this entire statement carefully before you...
Project 2 statement Please write this in JAVA. Please read this entire statement carefully before you start doing anything… This project involves implementing a simple university personnel management program. The program contains two different kinds of objects: students and faculty. For each object, the program stores relevant information such as university ID, name, etc. Different information is stored depending on the type of the object. For example, a student has a GPA, while a faculty has a title and department...
SQL Write the queries necessary to obtain the required information 1) Use sub query and “in”...
SQL Write the queries necessary to obtain the required information 1) Use sub query and “in” keyword to print the salesreps (ids) who have taken order for the companies ‘Zetacorp’ or ‘JCP Inc.’ . Duplicate rows are not allowed 2) Use sub query to find the id and the name of every sales rep that represents at least one customer with a credit limit of greater than $5000. 3) Use sub query and keyword “exists” to list the id and...
Question 1 of 15 Linsay’s Landscaping wants to make notes in QuickBooks each time she services...
Question 1 of 15 Linsay’s Landscaping wants to make notes in QuickBooks each time she services a client to streamline the invoicing process at the end of the month. She does not want these records to affect any month or year to date financial reports throughout the month until she invoices the client. Which of the following transaction types do you recommend she uses to achieve this goal? A. Invoice B. Credit Memo C. Sales Receipt D. Delayed Charge E....
ADVERTISEMENT
Need Online Homework Help?

Get Answers For Free
Most questions answered within 1 hours.

Ask a Question
ADVERTISEMENT