• For each problem below, make sure you report in a Word file both the query and a snapshot of the Oracle query output.
• Note that you will need to first create and populate a corresponding Oracle database by running the SQL commands provided in the file “Create_Insert_Lab3.txt” posted on iCollege under Lab Assignment 3 folder alongside this document.

Step 1: Copy and paste the content of the file “Create_Insert_Lab3.txt” (on iCollege à Lab Assignment à Lab Assignment 3) into Oracle and ensure that all the CREATE and INSERT commands are run successfully. This will create and populate the tables you will use for running your SELECT queries.

Step 2: Write a query for each of the following scenarios

1. Write a query that returns three columns from the Vendors table: vendor_name, vendor_contact_last_name, and vendor_contact_first_name. Sort the results by last name and then by first name.
2. Write a query that returns one column from the Vendors table named full_name. Create this column from the vendor_contact_first_name and vendor_contact_last_name columns separated by a comma (as in Smith, John). Include contacts whose last name begins Sort the results by last name, then first name. Sort the results by last name and then by first name.
3. Write a query that returns all columns from the Vendors table joined with all columns from the Invoices table.
4. Write a query that returns the Vendor name, Invoice number, invoice date, and balance due. Show only those invoices with non-zero balance. Sort the results by vendor name in ascending order.
5. Write a query that returns the vendor name, default account number, and description. Sort by account description then vendor name.
6. Write a query that returns the vendor name, invoice date, invoice number, li_sequence, and li_amount. Use appropriate aliases in the output and sort the results by vendor_name.
7. Write a query that returns the total Invoice Total for each vendor id. Sort the results by vendor id.
8. Write a query that returns the vendor name, the count of invoices per vendor and the sum of Invoice Total. Sort the results by payment total in descending order.
9. Write a query that returns the vendor name, the count of invoices for each vendor, and the sum of invoice total. Sort the results by invoice count descending.
10. Write a query that returns for each general ledger account, the account description, the count of entries for each account, and the sum the line item amounts. Include the results with a count of accounts of more than one.
11. Rewrite the following query using a subquery.

SELECT DISTINCT vendor_name

FROM vendors JOIN invoices ON vendors.vendor_id = invoices.vendor_id

ORDER BY vendor_name;

1. Write a query that returns the invoices that have a payment’s total that’s greater than the average payment_total for all paid invoices. Display the invoice number and invoice total.

2. Assignment Submission

Prepare a Word document for the assignment

• Name the document as follows: assignment #_your lastname, for example Assignment3_Jabr
• Include in the Word file your answers from the Textbook questions part
• Include in the Word file a snapshot of the results of each of your queries after running them in Oracle.

Upload the Word document to the Lab Assignment 3 dropbox in the iCollege class site by the due date.

Note that a late assignment is accepted 24 hours after the due date with a 10% penalty. The assignment dropbox is configured to accept late submissions up to 24 hours after the due date/time. Lab assignment submission by email is not accepted.

You must complete the assignment independently and submit your original work. “Plagiarism or duplicate lab assignments will be given a grade of ‘zero’, a point deduction equivalent to one final grade level (i.e. from a B- to a C-), and a charge of academic dishonesty. Both the person copying the assignment and the person supplying the copy will be penalized equally.”

-- Drop and Create the tablesDROP TABLE vendor_contacts;DROP TABLE invoice_archive;DROP TABLE invoice_line_items;DROP TABLE invoices;DROP TABLE vendors;DROP TABLE general_ledger_accounts;DROP TABLE terms;CREATE TABLE vendor_contacts(vendor_idNUMBERNOT NULL,last_nameVARCHAR2(50)NOT NULL,first_nameVARCHAR2(50)NOT NULL);CREATE TABLE invoice_archive(invoice_idNUMBERNOT NULL,vendor_idNUMBERNOT NULL,invoice_numberVARCHAR2(50)NOT NULL,invoice_dateDATENOT NULL,invoice_totalNUMBERNOT NULL,payment_totalNUMBERNOT NULL,credit_totalNUMBERNOT NULL,terms_idNUMBERNOT NULL,invoice_due_dateDATENOT NULL,payment_dateDATE);CREATE TABLE general_ledger_accounts(account_numberNUMBERNOT NULL,account_descriptionVARCHAR2(50)NOT NULL,CONSTRAINT gl_accounts_pkPRIMARY KEY (account_number),CONSTRAINT gl_account_description_uqUNIQUE (account_description));CREATE TABLE terms(terms_idNUMBERNOT NULL,terms_descriptionVARCHAR2(50)NOT NULL,terms_due_daysNUMBERNOT NULL,CONSTRAINT terms_pkPRIMARY KEY (terms_id));CREATE TABLE vendors(vendor_idNUMBERNOT NULL,vendor_nameVARCHAR2(50)NOT NULL,vendor_address1VARCHAR2(50),vendor_address2VARCHAR2(50),vendor_cityVARCHAR2(50)NOT NULL,vendor_stateCHAR(2)NOT NULL,vendor_zip_codeVARCHAR2(20)NOT NULL,vendor_phoneVARCHAR2(50),vendor_contact_last_nameVARCHAR2(50),vendor_contact_first_nameVARCHAR2(50),default_terms_idNUMBERNOT NULL,default_account_numberNUMBERNOT NULL,

• 1494576163-Create_Insert_Lab3.txt

ComputerScienceExpert

(11)

Status NEW Posted 12 May 2017 08:05 AM My Price 11.00

-----------

Attachments

file 1494576166-Solutions file 2.docx preview (51 words )

H-----------ell-----------o S-----------ir/-----------Mad-----------am ----------- Th-----------ank----------- yo-----------u f-----------or -----------you-----------r i-----------nte-----------res-----------t a-----------nd -----------buy-----------ing----------- my----------- po-----------ste-----------d s-----------olu-----------tio-----------n. -----------Ple-----------ase----------- pi-----------ng -----------me -----------on -----------cha-----------t I----------- am----------- on-----------lin-----------e o-----------r i-----------nbo-----------x m-----------e a----------- me-----------ssa-----------ge -----------I w-----------ill----------- be----------- qu-----------ick-----------ly -----------onl-----------ine----------- an-----------d g-----------ive----------- yo-----------u e-----------xac-----------t f-----------ile----------- an-----------d t-----------he -----------sam-----------e f-----------ile----------- is----------- al-----------so -----------sen-----------t t-----------o y-----------our----------- em-----------ail----------- th-----------at -----------is -----------reg-----------ist-----------ere-----------d o-----------n -----------THI-----------S W-----------EBS-----------ITE-----------. ----------- Th-----------ank----------- yo-----------u -----------

Not Rated(0)

• Buy Answer