(( Query Assignment Write the SQL query to answer the following: Exercises 4 fro

Question

((

Query Assignment

Write the SQL query to answer the following:

Exercises 4 from book "murach's Oracle SQL and PL/SQL for developers)))

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

Invoice_namer    vender_name from the vedors 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    invoice_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 dy vendor_name, invoice_data,invoice_number,and invoice_sequence.

(((

Submit a single WORD file that contains the SQL statement and a screen capture of the results (as much as can fit on a single page) for each of the exercises.

)))

please , can you explain step by step

thanks

Explanation / Answer

SELECT *FROM vendors INNER JOIN invoices ON vendors.vendor_id=invoices.vendor_id 2)SELECT vendor_name, invoice_number , invoice_due_date,(invoice_total-payment_total-credit_total) AS balance_due FROM vendors INNER JOIN invoices ON vendors.vendor_id=invoices.vendor_id WHERE (invoice_total-payment_total-credit_total) >0 ORDER BY vendor_name ASC 3)SELECT DISTINCT vendor_name, default_account_number AS default_account, account_description AS description FROM vendors INNER JOIN general_ledger_account ON vendors.default_account_number= general_ledger_account.account_number ORDER BY account_description, vendor_name 4)SELECT vendor_name, invoice_date, invoice_number, invoice_sequence AS li_sequence,line_item_amt AS li_amount FROM vendors ven INNER JOIN invoices inv ON ven.vendor_id=inv.vendor_id INNER JOIN invoice_Line_items Li ON inv.invoice_id=Li.invoice_id ORDER BY vendor_name, invoice_number, invoice_sequence 5)SELECT vi.vendor_id, v1.vedor_name,v1.vendor_contact_first_name ||' '|| vi.vendor_contact_last_name AS contact_name FROM vendors v1 JOIN vendors v2 ON (v1.vendor_contact_first_name=v2.vendor_contact_first_name) AND (v1.vendor_id<>(v2.vendor_id) ORDER BY v1.vendor_contact_last_name 6)SELECT DISTINCT gla.account_number,gla.account_description FROM General_Ledger_Accounts gla LEFT OUTER JOIN invoice_line_items ON gla.account_number=invoice.line_items.acoount_number ORDER BY account_number 7)SELECT VendorName, VendorState FROM Vendors WHERE VendorState IN ('CA') UNION SELECT VendorName, 'Outside CA' AS VendorState FROM Vendors WHERE VendorState != ('CA') ORDER BY VendorName

Related Questions

Navigate

• Browse (All)
• Browse #
• Subjects

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