I need to create the following queries using the tables below....... 1. How many

Question

I need to create the following queries using the tables below.......

1. How many books are sold each month by the publisher? This is important because quantity discounts are available from the publisher.

2. Which authors are the biggest sellers of books in our stores? This is important because the publisher offers discounts for certain authors each month.

3. What books are associated with each publisher?

4. What are the most popular products besides books that are sold in each store? In addition to books, the stores sell magazines, café-specific products like coffee and pastries, and various gift products.

5. From what region(s) (by ZIP code) do customers visit our stores? This is important because it will assist with future marketing efforts.

6. What customer data must be stored for the e-commerce portion of the website?

Employee

Customer

Store

    Author

Product

Orders   

EmployeelD Emp LastName Emp First Name EmpAddress EmpCiy Emp State EmpZp EmpPhone EmpHireDate 20 Cortland Street Springfield MA 85 Willard Avenue Springfield MA 15 Main Street Holyoke MA 115 Parker Street Springfield MA 17 Sunbrier Road Springfield MA EmpCity EmpState EmpZip EmpPhoneEmpHireDate 1 10637Smth Michael 20 Contland Street Springfield MA 01109 413-525-8956 2006-04-11 Smith Simpson Andrews irgs Baker 2 10638 3 10639 Cheryl Michelle Camron Karen 01109 413-525-4151 2010-05-09 01040 413-322-7435 2013-10-15 01129 413-726-1540 2001-11-07 01129 413-726-1456 1998-01-13 4 10640 A) 5 10641

Explanation / Answer

1. SELECT SUM(O.QTY), P.PUBLISHER
FROM PRODUCT P, ORDERS O
GROUP BY DATEPART(MONTH, O.ORDERDATE), P.PUBLISHER;

2.

SELECT MAX(O.QTY), A.*

FROM ORDER O, PRODUCT P, AUTHOR A

WHERE O.PRODUCTID=P.PRODUCTID AND

P.AUTHORID=A.AUTHORID

3.

SELECT PRODUCTGENRE, PRODUCTTYPE
FROM PRODUCT
GROUP BY PRODUCTGENRE, PUBLISHER

5.

SELECT C.CUSTZIP, C.CUSTLASTNAME, C.CUSTFIRSTNAME

FROM CUSTOMER C, STORE S, ORDER O

WHERE O.STOREID=S.STOREID AND C.CUSTOMERID=O.CUSTOMERID

6. All the customer data related to Order by the customer, Profile of customer should be stored on eCommerce website.

Related Questions

Navigate

• Browse (All)
• Browse I
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.