Question

Write a SELECT statement that uses aggregate window functions to calculate the order total for each...

Write a SELECT statement that uses aggregate window functions to calculate the order total for each

musician and the order total for each musician by date. Return these columns:

The musician_id column from the Orders table

The order_date column from the Orders table

The total amount for each order item in the Order_Items table

The sum of the order totals for each musician

The sum of the order totals for each musician by date

(Hint: You can create a peer group to get

these values)

Database Schema:
------------------------------
Tables: musicians, orders, order_instruments, instruments

Table columns
-----------------------
musicians: musician_id, email_address, password, first_name, last_name, shipping_address_id, billing_address_id

orders: order_id, musician_id, order_date, ship_amount, tax_amount, ship_date, ship_address_id, card_type, card_number, card_expires, billing_address_id

order_instruments: item_id, order_id, instrument_id, item_price, discount_amount, quantity

instruments: instrument_id, category_id, instrument_code, instrument_name, description, list_price, discount_percent, date_added

Homework Answers

Answer #1

Here is the solution. Please do upvote thank you.

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
in mysql 3. Write a SELECT statement that returns one row for each customer that has...
in mysql 3. Write a SELECT statement that returns one row for each customer that has orders with these columns:   The email_address column from the Customers table   The sum of the item price in the Order_Items table multiplied by the   quantity in the Order_Items table   The sum of the discount amount column in the Order_Items table   multiplied by the quantity in the Order_Items table   Sort the result set in descending sequence by the item price total for each customer.  
Write a SELECT statement that returns one row for each general ledger account number that contains...
Write a SELECT statement that returns one row for each general ledger account number that contains three columns: The account_description column from the General_Ledger_Accounts table The count of the items in the Invoice_Line_Items table that have the same account_number. The sum of the line_item_amount columns in the Invoice_Line_Items table that have the same account_number Return only those rows where the count of line items is greater than 1. This should return 10 rows. Group the result set by the account_description...
1. Write a SELECT statement to count SKUs for each department, i.e., a table of two...
1. Write a SELECT statement to count SKUs for each department, i.e., a table of two columns, departments and their SKU counts. use cape_codd; select department, count(*) from sku_data group by department ; 2. List buyers who is responsible for at least two SKUs, in three columns, department, buyer, sku count. Sort buyers by their department names in ascending order. select department, buyer, count(sku) from sku_data group by buyer ; 3. Write a SQL statement which returns all the orders....
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...
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...
1) Add to a relational table DEPARTMENT information about the total number of employees in each...
1) Add to a relational table DEPARTMENT information about the total number of employees in each department. Note, that if a department has not employee then for such a department the total number of employees must be set to zero (0). The total number of employees must be a positive number no more than 999. Use SELECT statement to list the contents of an extended relational table DEPARTMENT in the descending order of the total number of employees. Finally, remove...
True False Select the most correct answer. Question 1 (1 point) Most databases are just in...
True False Select the most correct answer. Question 1 (1 point) Most databases are just in 1NF. Question 1 options: True False Question 2 (1 point) A subquery is always preferred over a join. Question 2 options: True False Question 3 (1 point) A database may consist only of multiple files. Question 3 options: True False Question 4 (1 point) A database may exist without a database management system. Question 4 options: True False Question 5 (1 point) The insert...
ADVERTISEMENT
Need Online Homework Help?

Get Answers For Free
Most questions answered within 1 hours.

Ask a Question
ADVERTISEMENT