this is book from Murach\'s Oracle SQL and PL/SQL pg156-157 please help me 1.Wri

Question

this is book from Murach's Oracle SQL and PL/SQL pg156-157 please help me 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 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

Explanation / Answer

1)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 T
• Subjects

---

---

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