Hello need help with the following Oracle SQL statements and queries: 1. Write a

Question

Hello need help with the following Oracle SQL statements and queries:

1. Write a select statement that returns the total of INVOICE_TOTAL, count of INVOICE_TOTAL and average of INVOICE_TOTAL values using the INVOICES table.

2. Write the select statement for question 1 for invoices with invoice date after May 1, 2014.

3. Write a select statement that returns a list of the unique states that our customers live in. Order the data by state name in alphabetical order.

4. Write a select statement that will return the vendor_id, highest payment_total and lowest payment_total for each vendor_id using the invoices table.

5. Write a select statement to modify question 4 to only show the data for vendor_id values of 100 or more.

6. Write a select statement to modify question 4 to show the data for records whose highest payment_total exceeds $1000.

7. Write a select statement to list the city and state and the number of customers using the CUSTOMERS table for customers who do not reside in TX and OR. Sort the data by state name. I'm also needing a summary row, so I need to use the ROLLUP operator.

Explanation / Answer

Below is the answer for the queries:

1. select sum(INVOICE_TOTAL),avg(INVOICE_TOTAL),count(INVOICE_TOTAL) from INVOICES

2. select sum(INVOICE_TOTAL),avg(INVOICE_TOTAL),count(INVOICE_TOTAL) from INVOICES where INVOICE_DATE > '5/1/2014'

3. select distinct STATES from INVOICES ORDER BY STATE_NAME ASC

4. select vendor_id , MAX(payment_total) , MIN(payment_total) from INVOICES where vendor_id in (SELECT vendor_id from INVOICES)

5. select vendor_id , MAX(payment_total) , MIN(payment_total) from INVOICES where vendor_id > 100

6.  select vendor_id , MAX(payment_total) , MIN(payment_total) from INVOICES where payment_total > 1000

7. select city , state , count(customers) from customers where state NOT IN ('TX','OR') ORDER BY state ASC WITH ROLLUP.

Related Questions

Navigate

• Browse (All)
• Browse H
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.