Question

Write the SQL queries that accomplish the tasks using the AP Database 1. Write a SQL...

Write the SQL queries that accomplish the tasks using the AP Database

1. Write a SQL statement to retrieve the InvoiceID, InvoiceDate, InvoiceTotal, BalanceDue, PaymentDate, and AgeofInvoices for the invoices that have more than 30 days of invoice age. Sort the result set by AgeofInvoice in a descending order. (Note: BalanceDue = InvoiceTotal-PaymentTotal-CreditTotal AgeofInvoice is the number of days difference between invoicedate and paymentdate.)

2. For each invoicedate that has more than one invoice, show the invoiceDate and calculate the following values: InvoiceCount, InvoiceSum, InvoiceAvg, InvoiceMin, and InvoiceMax. Sort the result by InvoiceCount, then by InvoiceSum. The definitions of the calculated values are in the following table: InvoiceCount - Number of invoices in each invoicedate. InvoiceSum - Sum of the invoiceTotal of invoices in each invoicedate. InvoiceAvg - Average of the invoiceTotal of invoices in each invoicedate. InvoiceMin - Lowest amount of the InvoiceTotal of invoices in each invoicedate. InvoiceMax - Highest amount of the InvoiceTotal of invoices in each invoicedate

Engineering   Computer-Science   
0 0
Add a commentTranscribed image text
Next >

Homework Answers

Answer #1

0 0
Know the answer?
Your Answer:

Post as a guest

Your Name:

What's your source?

Earn Coins

Coins can be redeemed for fabulous gifts.

Log In

Sign Up

Not the answer you're looking for?
Ask your own homework help question
Similar Questions
Translating MongoDB Queries to SQL - WILL UPVOTE!! 1. Write the equivalent sql queries for all...
Translating MongoDB Queries to SQL - WILL UPVOTE!! 1. Write the equivalent sql queries for all mongoDB queries. The following is an example of what you are asked to do. Assume the collection name is the table name in an sql database. Example: MongoDB Query: db.zips.aggregate([ {$match: {state: "MO"}}, {$group: {_id: "$city", totalPop: {$sum: "$pop"}}}, {$sort: {totalPop: -1}}, {$limit: 10} ]) SQL Result: Select city, SUM(pop) AS "totalPop" From zips Where state = "MO" Group By city Order By SUM(pop)...
Using the Company database in Oracle, construct SQL queries for the following (note: I will make...
Using the Company database in Oracle, construct SQL queries for the following (note: I will make the files to create and populate the Company database available on Isadore shortly): List the last name and address of managers who have a dependent with the same first name as themselves. Retrieve the names of all employees who are directly supervised by ‘Franklin Wong’. Retrieve the names of employees in the Research department who work more than 20 hours per week on the...
Specify the following queries in SQL on the COMPANY database. Show the result of each query....
Specify the following queries in SQL on the COMPANY database. Show the result of each query. Retrieve the names of all employees in department 5 who work more than 10 hours per week on the ‘ProductX’ project. List the names of all employees who have a dependent with the same first name as themselves. Find the names of all employees who are directly supervised by ‘Franklin Wong’. Specify the following updates using the SQL update commands. Show the state of...
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...
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.  
***This is a complete question. Do not tag this as incomplete. Write SQL queries to the...
***This is a complete question. Do not tag this as incomplete. Write SQL queries to the below Relational Database. • Regarding the SQL queries: • Do not use SELECT * in any query. • Do not use any SQL features (including Nullif) that were not covered in this course. • Do not use subqueries IF joins can be used to answer a question. However, some questions may require the use of subqueries. The Movie Database Notes: TheaterNum, MovieNum, and ActorNum...
Write the following questions as queries in SQL. Use only the operators discussed in class (in...
Write the following questions as queries in SQL. Use only the operators discussed in class (in particular, no outer joins or windows). Type your answers. Before starting, make sure you understand the schema of the database. If you are in doubt about it, please ask the instructor. Assume a database with schema ACTOR(name,age,address,nationality) MOVIE(title,year,genre,budget,director-name,studio) APPEARS(name,title,salary) 1. Find the title, director and studio of the most expensive movie of 2010 (note: there can be ties!). 2. Find the title, director and...
Class-In Assignment 3: Chapters 4&5 How to retrieve data from two or more tables Exercises 1....
Class-In Assignment 3: Chapters 4&5 How to retrieve data from two or more tables Exercises 1. Write a SELECT statement that returns all columns from the Vendors table inner-joined with all columns from the Invoices table. 2. Write a SELECT statement that returns four columns: vendor_name vendor_name from the Vendors table invoice_number invoice_number from the Invoices table invoice_date invoice_date from the Invoices table balance_due invoice_total minus payment_total minus credit_total from the Invoices table The result set should have one row...
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.
Lab 5 Queries with Multiple Tables In this lab, we do queries more than one table....
Lab 5 Queries with Multiple Tables In this lab, we do queries more than one table. SQL provides two different techniques for querying data from multiple tables: • The SQL subquery • The SQL join As you will learn, although both work with multiple tables, they are used for slightly different purposes. We used WMCRM database which is what we created in Lab 4. Here is the summary of the database schema (where schema is used in its meaning of...
ADVERTISEMENT
Need Online Homework Help?

Get Answers For Free
Most questions answered within 1 hours.

Ask a Question
ADVERTISEMENT