Question

Write SQL code please. Tables are attached with their appropriate fields for reference.

The Marketing Department requires a vendor listing that shows the following fields: 1. - Vendor Name Vendor Contact First Name - Vendor Contact Last Name - Vendor City - Vendor State - Vendor Zip Code Note: Sort by vendor state The Finance Department requires a report that shows payment totals greater than $50.00. List the following fields 2. - Vendorid - Payment Total - Payment Date The Sales Department requires a report that shows all vendors who are located in the states of New York, Alaska, California and Florida. List all fields in the vendors table 3. Using the Vendor Contacts table, create a report that list the vendorid field. vendorid, what is the names of each vendor? 4. By

Answer 1

The SQL codes are very easy to understand.

Queries 1 and 3 are using SQL joins (inner join here) as they require data from two tables. These are joijoi by their common field vendor_id . The other two are very straightforward. The last query is implemented with a simple group by statement. The below image shows the answer :

- Lo VENDOR CONTACT.LAST-N AMe, VENDOR-CITY, VENDOR STATG VENDOR- 21popE from VENDORS ORDER By VENDOR STATE ELECT VENDORS VENDORID, INVoICE PAVME NIT-TO TAL INV 이CES.PAY ME NT-D Ate fROM VENDORS INNER TOIN NVOI CEs ON VENDORs VENDOR ID INvoICES v WHERE INVOIC PAY MENT-1orAL 50 조, E LECTFRon vENpORs WHERE VE NDORS-STATE IN Ow YO 4: SELECT VENDORS . VENDOR-MAHE VENDOR S İ VENDOR-ID INNER OIN VE DORCONTACTS O VENDOR VENDOR-Ip GROUP By VENDORS.VENDOR-NAME, VENDORS. VENDR-İD PROM VE NDORS VENDOR-CoNTACTS VE NDOR-ID