Can you help me out with questions 10-14 and problem 16. I attached two images because the scenario is on another page
. Include InvoiceID, InvoiceDat X d. Calculate total sales for each inventory item. from the Sales table and the Amt from the Item e. Calculate total sales. iculation query. Name the query Sale_Amount Calculation. Meme f. Calculate the month in which each sale occurred. Include InvoiceID and InvoiceDate from the Sales table. Name the query Sales_Months. (Hint: Look for the Month func- tion in the expression builder.) g. Calculate the sum of sales for each month. 9. This problem continues Problem 8. Use the Access_Practice.accdb database that you have been working on to complete the following tasks. LO 4-2 a. Go to the Relationships screen and connect the five tables, enforcing referential integrity. b. Calculate the total sales for each customer. Include CustomerID and CompanyName from the Customer table and the calculated sale amount from the Sale_Amount_ Calculation query. Name the query Total_Customer_Sales. c. Generate an e-mail user name for each employee using the first letter of the employ- ee's first name and the first five letters of the employee's last name, e.g., Rod Smith = > rsmith. Include EmployeeID, EmployeeFirstName, and EmployeeLastName in the query. Name the query Employee_Email_Generator. d. Calculate the total sales for each month. e. Determine which customer had the highest average sales amount. (Hint: Sort in descending order.) f. Assume the employees earn a 5 percent commission on sales. Calculate the total commission due to each employee. Use two queries to do these calculations. Background for Problems 10 to 14 Ellen Novotny started an online bookstore in 2017. You are Ellen's best friend and promised to help her. Ellen asked you to create a small database to track the Information on books and authors. You created the following tables: Author, Book, and Author_Book. The table Author_ Book is a relationship table to link the Author table and Book table because of the many-to- many relationship between the two tables. That is, each author may write many books and each book may have multiple authors. In general, each customer can purchase many books in a single transaction. Also, copies of the same book can be sold to different customers. Once an order is processed, the books are shipped right away. Based on these tables you have (see below), help Ellen to extract the information she needs from the database. Author Author ID Last Name First Name Email Phone number AU-1 Adams Eric Eric 168@yahoo.com (714) 833-2419 AU-2 Brown Jennifer jenifferb@gmail.com (619) 596-0012 AU-3 Davis Keith keithd@gmail.com (212) 342-5680 AU-4 Newport Kevin kevinn@hotmail.com (301) 947-7741 AU-5 Pham John johnpham@gmail.com (617) 645-3647 Sviokla Julia jsviokla@yahoo.com (805) 498-1688 AU-6 115X 10. Ellen asks you to give her a list of the books that each author wrote. Write a complete SQL Meme statement to provide the information to Ellen, including author names, book titles, publish- ers, and the years of publication. 11. Ellen wants to know how many books each author wrote. Write a SQL statement to pro- vide such information to Ellen. 12. You are going to send e-mails to inform the customers that the books ordered have been shipped. Write a SQL statement to obtain the complete information on the book title(s), unit price(s), and the number of each book purchased to be sent to the customers. 13. Ellen wants to know how many copies of each book were sold. Write a SQL statement to obtain the necessary information you think Ellen wants. LO 4-3 14. Ellen wants to know the dollar amount of total sales made on September 1, 2017. Write a onnect SQL statement to obtain the necessary information you think Ellen wants. LO 4-3 15. The Cash Receipt table below contains seven attributes. Which of those could possibly be foreign keys? LO 4-2 Cash Receipt Remittance Advice # Date Amount Customer # Check # | Invoice # Cashier # RA-220 1 1/02/2016 2549.90 C-12 201 S-101 E-13345 RA-278 11/10/2016 699.90 C-5 1457 S-108 E-13347 RA-276 1 1/30/2016 1209.70 C-9 392 S-107 E-13345 RA-289 1 1/30/2016 949.95 C-28 2558 S-105 E-13346 connect 16. A sales invoice typically includes the date of sale, salesperson, customer data, items included in the sale, and amount. Which foreign keys should be added to the following table to link all of these data elements? L0 4-2 Sales Invoice # Date Amount S-101 10/05/2016 2549.90 S-105 1 1/01/2016 949.95 S-107 1 1/02/2016 1209.70 S-108 1 1/06/2016 699.90 Additional problems are available in connect only. ooo
