Write the DML select statements to create the following reports:

2. Report the First name and last name of customers, however you should only report the first letter of the first name and the entire last name. Example:

Instead of: Bob Squarepants You should report B Squarepants

Hint: Use the SUBSTR function

3. Report the First name and last name of customers, however you should only report the first letter of the first name accompanied by a dot and the entire last name. Example:

Instead of: Bob Squarepants You should report B. Squarepants

Hint: Use the CONCAT function

4. Report the Artist Name and the average Unit_Price per artist.

5. Report the total number of items sold per order_id Hint: you should use the Order_Details table

6. Count the number of customers per state. Report the results in order so that the states with the most customers are listed first. Name the column that lists the count of customers as State_Ordering

7. Repeat the previous query but this time report only the states in which you have more than 1 customers. Can you use the WHERE clause to solve this?

8. Can you use SQL to report the current date and time;

9. Find the distinct names of the vendors who have invoices with invoice_total larger than 10,000.

10. Find all the vendors who have invoices that have not been paid yet. (Hint: the invoice_total will be different than the payment_total).

11. Rewrite the above query in a total of 3 ways: Using equijoins, using INNER JOIN and using NATURAL JOIN.

12. A. Find the item titles and the corresponding order ids. B. Now count the number of orders each item has been included in. C. Now report only those items that have been included in more than 5 different orders

13. Count all the vendor contact first names, then count all the distinct vendor contact first names. Finally find the vendor contact first names that exist more than once.

14. A. Find the order ids for each of the customers whose last name is Blanca B. Find all the customers whose last name is Blanca and the order ids for each of their orders C. Repeat the query of part B, This time if there are any NULL values substitute them with 0.

15. A. Modify the query of part 14C to get the total order quantities per customer (sum over order_qty per customer). Should you use both first and last name? What would happen if you use only the first name and two customers have the same first name? B. Now generalize the query for all customers and check how the number of returned records changes based on whether you are using only the first, only the last or both first and last names.

16. Find the vendor_name and the invoice_number for the vendors who have invoices, but only for those invoices that have valid invoicelines.

17. Find the vendor_id and the corresponding invoice_id for the vendors who have invoices, even if these invoices do not have valid invoicelines.

18. Find the vendor_id and the corresponding invoice_id for all the vendors, even the ones with no invoices, even if these invoices do not have valid invoicelines.