Hello I am using MySQL AP Database.

Please help answer questions below.

1. (15 pts) Write a SELECT statement that returns:

- the Vendor Name

- InvoiceCount: the number of invoices for that Vendor

- InvoiceAverage: the average invoice total for that Vendor

Include only the results where the Invoice Average is more than 500.00. Display in descending order for the Invoice Count column. The correct solution has 19 rows. Show the full query and at least the first 10 rows in the results grid.

2. (15 pts) Write a SELECT statement that returns:

- Vendor Name

- Vendor State

- VendorSum: the sum of all the invoices for that Vendor

- VendorMax: the amount of the largest invoice for that vendor

Include only the vendors with invoices that have a balance due. Display in descending order of VendorSum. The correct solution has 7 rows. Show the full query and all rows in the results grid.

3. (15 pts) Write a SELECT statement that returns:

- Vendor Name

- Vendor State

Use a subquery to include only the vendors with a default account number that corresponds to the Office Supplies account description. The correct solution has 8 rows. Show the full query and all the rows in the results grid.

4. (20 pts) Write a SELECT statement that returns:

- Vendor Name

- Invoice Number

- Invoice Line Item Amount

Use a subquery to display only the records that have an Invoice Line Item Amount that is greater than the Average Invoice Line Item Amount. Display in descending Invoice Line Item Amount order. The correct solution has 20 rows. Show the full query and the first 10 rows.

5a. (5 pts) Write a statement that creates a VendorTest table from the Vendors table and copies all the records. Show the full query and the Message area.

5b. (5 pts) Write a SELECT statement to show the records in the VendorTest table. Size the window so only the full query, the first 5 columns and the first 10 rows are displayed in the screen shot.

6a. (20 pts) Write a statement that inserts a record into the VendorTest table with the following information:

(use a column list)

- VendorID: (next available unique ID)

- VendorName: Peterbrooke Chocolatier

- VendorAddress1: 4414 Town Center Pkwy.

- VendorAddress2: #219

- VendorCity: Jacksonville

- VendorState: FL

- VendorZipCode: 32246

- VendorPhone: (904) 398-2499

- DefaultTermsID: 1

- DefaultAccountNo: 580

Points will be deducted if you turn IDENTITY_INSERT ON.

Show the full query and the Messages area.

6b. (5 pts) Write a SELECT statement to show the new record inserted into the VendorTest table. Display the records in descending VendorID order. Size the window so only the full query, the first 5 columns, and the first 4 rows are displayed in the screen shot.

7a. (15 pts) Write a statement to update the record just inserted into the VendorTest table:

the new contact person for Peterbrooke is Coco LeMaize

Show the full query and the Messages area.

7b. (5 pts) Write a SELECT statement to show the updated record in the VendorTest table. Display the records in descending VendorID order. Use the horizontal slider bar to display the 5 columns from Vendor State to the First Name. Show the full query, the 5 columns specified, and the first 3 rows.

8a. (10 pts) Remove all Florida vendors from the VendorTest Table. Show the full query and the Messages area.

8b. (5 pts) Write a SELECT statement to show the VendorTest table. Display the records in descending VendorID order. Size the window so only the full query, the first 4 columns, and the first 4 rows are displayed in the screen shot.

8c. (5 pts) Write a statement that removes the VendorTest table from the AP database. Show the full query and the Messages area.

Vendors VendorID Vendor Name Vendor Address 1 Vendor Address2 VendorCity Vendor State Vendor ZipCode Vendor Phone Vendor Contact Name VendorContactFName Default TermsID DefaultAccountNo Invoices * InvoiceID VendorID InvoiceNumber Invoice Date Invoice Total Payment Total Credit Total TermsID InvoiceDueDate Payment Date Terms TermsID TermsDescription TermsDue Days GLAccounts Account No Account Description InvoiceLineItems InvoiceID InvoiceSequence AccountNo InvoicelineItemAmount InvoiceLineItem Description Vendors VendorID Vendor Name Vendor Address 1 Vendor Address2 VendorCity Vendor State Vendor ZipCode Vendor Phone Vendor Contact Name VendorContactFName Default TermsID DefaultAccountNo Invoices * InvoiceID VendorID InvoiceNumber Invoice Date Invoice Total Payment Total Credit Total TermsID InvoiceDueDate Payment Date Terms TermsID TermsDescription TermsDue Days GLAccounts Account No Account Description InvoiceLineItems InvoiceID InvoiceSequence AccountNo InvoicelineItemAmount InvoiceLineItem Description