MySQL
Q3.) (25 points) Use the ap database for this question. Write a query that selects vendors and any invoice they have to replicate the result set below, matching all data and format aspects, including column headers. This question includes 3 tables. NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL invoice_number invoice_total vendor_name vendor_contact_name vendor_phone vendor_address 203339-13 17.50 Abbey Office Furnishings Reynaldo Suscipe (559) 555-8300 4150 W Shaw Ave NULL American Booksellers Assoc NULL (800) 555-0037 828 S Broadway American Express (800) 555-3344 Box 0001 ASC Signs 1528 N Sierra Vista Ascom Hasler Mailing Systems PO Box 895 AT&T PO Box 78225 Aztek Label (714) 555-9000 Accounts Payable Baker & Taylor Books (704) 555-3500 Five Lakepointe Plaza, Ste 500 509786 6940.25 Bertelsmann Industry Svcs. Inc (805) 555-0584 28210 N Avenue Stanford NULL BFI Industries (559) 555-1551 PO Box 9369 Bill Jones NULL Secretary of State Bill Marvin Electric Inc NULL (559) 555-5106 4583 E Home Blanchard & Johnson Associates Bruce Onandonga (214) 555-3647 27371 Valderas 547481328 224.00 Blue Cross (800) 555-0912 PO Box 9061 547480102 224.00 Blue Cross (800) 555-0912 PO Box 9061 547479217 116.00 Blue Cross (800) 555-0912 PO Box 9061 Blue Shield of California (415) 555-5103 PO Box 7021 Boucher Communications Inc (215) 555-8000 1300 Virginia Dr. Ste 400 587056 2184.50 Cahners Publishing Company (301) 555-2162 Citibank Lock Box 4026 Cal State Termite (559) 555-1534 PO Box 956 California Business Machines Geraldine O'Sullivan (559) 555-5570 Gallery Plz California Chamber of Commerce (916) 555-6670 3255 Ramos Cir California Data Marketing NULL (559) 555-3801 2818 E Hamilton The result set contains 202 rows. Only a subset is shown in the preview. Pay special attention to NULL values. NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL HULL NULL NULL NULL invoice_line_items invoice_id INT invoice_sequence INT account_number INT line_item_amount DECIMAL(9,2) line_item_description VARCHAR(100) Indexes PRIMARY line_items_fk_acounts vendors vendor_id INT vendor_name VARCHAR(50) vendor_address1 VARCHAR(50) vendor_address 2 VARCHAR(50) vendor_city VARCHAR(50) vendor_state CHAR(2) vendor_zip_code VARCHAR(20) vendor_phone VARCHAR(50) vendor_contact_last_name VARCHAR(50) vendor_contact_first_name VARCHAR(50) default_terms_id INT default_account_number INT Indexes I PRIMARY vendor_name vendors_fk_terms vendors_fk_accounts general_ledger_accounts account_number INT account_description VARCHAR(50) Indexes PRIMARY account_description invoice_archive invoice_id INT vendor_id INT invoice_number VARCHAR(50) invoice_date DATE invoice_total DECIMAL(9,2) payment_total DECIMAL (9,2) credit_total DECIMAL (9,2) terms_id INT invoice_due_date DATE payment_date DATE invoices terms invoice_id INT terms_id INT vendor_id INT terms_description VARCHAR(50) invoice_number VARCHAR(50) terms_due_days INT Indexes invoice_date DATE PRIMARY invoice_total DECIMAL(9,2) payment_total DECIMAL(9,2) 1 credit_total DECIMAL (9,2) >+ terms_id INT invoice_due_date DATE paym ent_date DATE Indexes PRIMARY invoices_fk_vendors invoices_fk_terms invoices_invoice_date_ix ap Tables general_ledger_accounts Columns account_number account_description Indexes PRIMARY account_description Foreign Keys Triggers invoice_archive Columns invoice_id vendor_id invoice_number invoice_date invoice_total payment_total credit_total terms_id invoice_due_date payment_date Indexes Foreign Keys Triggers invoice_line_items Columns invoice_id invoice_sequence account_number line_item_amount line_item_description Indexes Indexes PRIMARY line_items_fk_acounts Foreign Keys |line_items_fk_acounts line_items_fk_invoices Triggers invoices Columns invoice_id vendor_id invoice_number invoice_date invoice_total payment_total credit_total terms_id invoice_due_date payment_date Indexes PRIMARY invoices_fk_vendors invoices_fk_terms invoices_invoice_date_ix Foreign Keys invoices_fk_terms invoices_fk_vendors Triggers terms Columns terms_id terms_description terms_due_days Indexes PRIMARY Foreign Keys Triggers vendor_contacts Columns Indexes Foreign Keys Triggers vendors Columns Indexes Foreign Keys Triggers Views Stored Procedures Functions
