Murach’s Oracle SQL and PL/SQL – Exercise 4 – Guaranteed 100% score

Lab Price = $9
[orb_cyber_store id=”466″]
Please feel free to send us your queries at: [email protected]
Problem Statement

Exercises 4

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 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 the sum of payment_total minus credit_total from the invoices table
The result set should have one row for each invoice with a non-zero balance. Sort the result set by vendor_name in ascending order.

3. Write a SELECT statement that returns three columns:

vendor_name vendor_name from the Vendors table
default_account default_account_number from the Vendors table
description account_description from the General_Ledger_Accounts table
The result set should have one row for each vendor, and it should be sorted by account_description and then by vendor_name.

4. Write a SELECT statement that returns five columns from three tables:

vendor_name vendor_name from the Vendors table
invoice_date invoice_date from the Invoices table
invoice_number invoice_number from the Invoices table
li_sequence invoice_sequence from the Invoice_Line_Items table
li_amount line_item_amt from the Invoice_Line_Items table

Use these aliases for the tables: Ven for the Vendors table, Inv for the Invoices table, and LI for the Invoice_Line_Items table. Also, sort the final result set by vendor_name, invoice_date, invoice_number, and invoice_sequence.

5. Write a SELECT statement that returns three columns:
vendor_id vendor_id from the Vendors table
vendor_name vendor_name from the Vendors table
contact_name A concatenation of vendor_contact_first_name and vendor_contact_last_name with a space in between
The result set should have one row for each vendor whose contact has the same last name as another vendor’s contact, and it should be sorted by vendor_contact_last_name. Hint: Use a self-join.

6. Write a SELECT statement that returns two columns from the General_Ledger_Accounts table: account_number and account_description. The result set should have one row for each account number that has never been used. Sort the final result set by account_number. Hint: Use an outer join to the Invoice_Line_Items table.

7. Use the UNION operator to generate a result set consisting of two columns from the Vendors table: vendor_name and vendor_state. If the vendor is in California, the vendor_state value should be “CA”; otherwise, the VendorState value should be “Outside CA.” Sort the final result set by vendor_name.

Relevant Material
Instructions
* Please click on “Buy Now” button to complete the purchase.Please note that the payment is done through PayPal.
* If you have any issues with the payment method, please let us know at [email protected] and that can be discussed and considered.
* Your paypal has to be pre-loaded in order to complete the purchase or otherwise please discuss it with us at [email protected].
* As soon as the payment is received through PayPal, download link of the solution will automatically be sent to the address used in Paypal.
* Please check your junk mails as the download link email might go there.
* All the contents are compressed in one zip folder.
* In case if you get stuck at any point during the payment process, please immediately contact us at [email protected] and we will fix it with you.
* We try our best to reach back to you on immediate basis. However, please expect to get a response within 8 hours from our side.
* Comments/Feedbacks are truely welcomed and there might be some incentives for you for the next lab/quiz/assignment.
* In case of any query, please donot hesitate to contact us at [email protected].

******************************************** Good Luck ***************************************************

Payment Details
Lab Price = $9
[orb_cyber_store id=”466″]
Please feel free to send us your queries at: [email protected]

Leave a Reply