MySQL

1. Using AP database. Write a statement that will display all the columns in the Invoices table where the invoice total is greater than 25.

2. Using AP database. Write a statement that will take data from the Invoices and Vendors table and show the Vendor Name and Vendor ID for every invoice that has not been paid yet. (Hint, look for NULL values.)

3. Using AP database. Show the vendor id, invoice number, and calculate the balance due for each row in the invoice table. Sort by the largest balance first. The result set should only show the first 8 rows.

4. Using my_guitar_shop database. Write a query that takes data from Customers and Orders and joins them together to show the customer name, customer id, and order date. Sort alphabetically by customer name.

5. Using my_guitar_shop database. Combine the Categories and Products table to show all of the columns in the products table along with the matching category name in the category table. Order them by the category name.

6. Using my_guitar_shop database. Update the customers database so the first customer (id = 1) in the list has a different first name, last name, and email address (can be anything)

7. Using EX database. In the employees table, add a new record for Jeffrey Smitherson with a dept number of 6 and manager ID of 1. Have the primary key use the next default value.

8. Using my_guitar_shop database. Find the average price of all the items in the products database. Make sure you round the average to cents. The query should only show a label field and the average.

9. Using my_guitar_shop database. List the product name and product id for the most expensive item in the products table. This should only return one row.

10. Using AP database. Create a query from the invoices and vendors table that will show the vendor state, the number of invoices for that state, and the average of all those invoices. This will be grouped by state and also ordered by state alphabetically.

Databases: https://github.com/kush-chan/databases

AP Database: Create Book Databases

My_Guitar_Shop Database: mgs_ex_starts

EX Database: Create Book Databases