Quesdon 5 Consider the database table structure shown in the figure. Ques 10 points VENDOR DE AP INVOICES PWONEJ NUMBER NUMBER INVOICE NUMBER VARCHAR BYTE) ORCE_DATE DATE INVOICE_TOTAL NUMBERS PAYMENT_TOTAL NUMBER) CREDIT TOTAL NUMBER * TERMS NUMBER (10) INVOICE DUE DATE DATE PAYMENT DATE DATE AP VENDORS VENDOR NUMBER (10) VENDOR NAME VARCHARDS BYTE) VENDOR_ADDRESS: VARCHARDYTE) VENDORLADORES VARCHAR MOBYTE) VENDOR_CITY VARCHARD GOBYTE) VENDOR STATE CHARGETED VENDOR POP_CODE VARCHARD GO BYTE) VENDOR PHONE VARCHARTE VENDOR CONTACT LASTUME VARCHARD 60 BYTE) VENDOR_CONTACT_FIRST_NAME VARCHARCUTE) DEFAULT_TERMS_JO NUMBER (10) NEFAULT ACCOUNT NUMBER NUMBER . VOICES_KONVOICE) INVOICES TERMS (TERMS_JO) INVOICES FK VENDORS (VENDOR) VOICES INVOICE_DATE_INVOICE_DATE) INVOICES_POINVOICE INVOICES_TERMS_J_JR (TERMS_) INVOICES_VENDORID_VENDORLD) VENDORS_PREVENDORJID) VENDORS VENDOR NAME U VENDOR NAME) VENDORS FK ACCOUNTS (DEFAULT_ACCOUNT_NUMBER) VENDORS_TK_TERMS DEFAULT_TERMS_ID) VENDORS OVERBERI DEFAULT_ACCOUNT NUMBER) VENDORS_TERMS_IO_JA (DEFAULT_TERMS_JO) VENDORS_VENDORLAME_WG (VENDORRAME) (a) Write a SELECT statement (compatible with an Oracle RDBMS) that generates a result set consisting of the vendor name, vendor_city, and vendor state for all vendors. The vendor state value should be "AL" for vendors in Alabama and "Outside AL" for vendors outside of Alabama. The vendor city should be the value returned for vendors in Alabama but should be "Outside Alabama for vendors outside of Alabama. The vendors in Alabama should be first in the list and alphabetised by vendor city. The vendors outside of Alabama should follow all the vendors in Alabama and be alphabetized by vendor city. "Hint: Use a UNION operator. (b) Write two 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. TT T Arial v 3 (12pt) T. Quesdon 5 Consider the database table structure shown in the figure. Ques 10 points VENDOR DE AP INVOICES PWONEJ NUMBER NUMBER INVOICE NUMBER VARCHAR BYTE) ORCE_DATE DATE INVOICE_TOTAL NUMBERS PAYMENT_TOTAL NUMBER) CREDIT TOTAL NUMBER * TERMS NUMBER (10) INVOICE DUE DATE DATE PAYMENT DATE DATE AP VENDORS VENDOR NUMBER (10) VENDOR NAME VARCHARDS BYTE) VENDOR_ADDRESS: VARCHARDYTE) VENDORLADORES VARCHAR MOBYTE) VENDOR_CITY VARCHARD GOBYTE) VENDOR STATE CHARGETED VENDOR POP_CODE VARCHARD GO BYTE) VENDOR PHONE VARCHARTE VENDOR CONTACT LASTUME VARCHARD 60 BYTE) VENDOR_CONTACT_FIRST_NAME VARCHARCUTE) DEFAULT_TERMS_JO NUMBER (10) NEFAULT ACCOUNT NUMBER NUMBER . VOICES_KONVOICE) INVOICES TERMS (TERMS_JO) INVOICES FK VENDORS (VENDOR) VOICES INVOICE_DATE_INVOICE_DATE) INVOICES_POINVOICE INVOICES_TERMS_J_JR (TERMS_) INVOICES_VENDORID_VENDORLD) VENDORS_PREVENDORJID) VENDORS VENDOR NAME U VENDOR NAME) VENDORS FK ACCOUNTS (DEFAULT_ACCOUNT_NUMBER) VENDORS_TK_TERMS DEFAULT_TERMS_ID) VENDORS OVERBERI DEFAULT_ACCOUNT NUMBER) VENDORS_TERMS_IO_JA (DEFAULT_TERMS_JO) VENDORS_VENDORLAME_WG (VENDORRAME) (a) Write a SELECT statement (compatible with an Oracle RDBMS) that generates a result set consisting of the vendor name, vendor_city, and vendor state for all vendors. The vendor state value should be "AL" for vendors in Alabama and "Outside AL" for vendors outside of Alabama. The vendor city should be the value returned for vendors in Alabama but should be "Outside Alabama for vendors outside of Alabama. The vendors in Alabama should be first in the list and alphabetised by vendor city. The vendors outside of Alabama should follow all the vendors in Alabama and be alphabetized by vendor city. "Hint: Use a UNION operator. (b) Write two 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. TT T Arial v 3 (12pt) T
