SQL (a) Write a SELECT command that returns the vendor name, the invoice id, the

Question

SQL

(a) Write a SELECT command that returns the vendor name, the invoice id, the invoice sequence number, and the line item amount for each invoice that would list more than one item. While using a subquery that tests for an invoice sequence number > 1 will be holpful, the command will require joins as woll (b) Then write two result dataset rows of sample data that might be retrieved by the query. What is actually retrieved depends on what is in the table. The data you write does not have to match any data that is actually in our sample tables See the table structure shown below. Table: invoices Nullable Default Data Constraint Column Name ata Type invoice id vendor_id invoice_n invoice date invoice total number(9,2) payment total number(9,2) credit total terms id invoice due date date payment _date date PK FK No number varchar2(50) date No number(9,2) Yes No No FK Table: invoice line items Column Name invoice i Nullable Default Data Constraint No No Data Type NUMBER PK,FK PK FK sequence NUMBER account number NUMBER line item amt line item description VARCHAR2(100) No NUMBER(9,2) No Table:vendors Column Name Nullable Default Data No Data Type Constraint vendor name vendor address1 vendor address2 vendor city vendor_state vendor_zip_code vendor ohone UNIQUE varchar2(50) No varchar2(50) Yes varchar2(50) Yes varchar2(50) No char(2) varchar2(20) No varchar2iSO) Yes No

Explanation / Answer

Answer is as follows:

a) Sql Query is as follows :

SELECT vendor_name, invoice_id, invoice_sequence, line_item_amt FROM Vendor JOIN Invoices ON Vendor.vendor_id = Invoices.vendor_id as vi JOIN Inoice_line_items ON vi.invoice_id = Invoice_line_items.invoice_id WHERE IN(Select invoice_sequence FROM Inoice_line_items WHERE invoice_sequence > 1 ) ;

The query is generated as information given in the query.

vi is object created for joined table Vendor and Invoice.

b) Sample data

if there is any query please ask in comments.....

vendor_name invoice_no invoice_sequence line_ine_amt XYZ 123 5 2.025 MNP 568 3 50.365

Related Questions

Navigate

• Browse (All)
• Browse S
• Subjects

---

---

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