Question

CIS336: Lab 6: Group Functions and Subqueries

I desperatly need help with this MYSQL lab. Any help is appreciated. I don't need the outputs, I just need working codes.

1.Write a query to display the description of a product and the number of orders for that product. Sort the display by the description.

2.Redo the previous problem but this time just display those product names and the number of sales that have more than 3 sales.

3. Use a SubQuery to determine which sales reps have a sale that is greater than the average sale. Display the sales rep ID. Only show the sales rep ID once.

4. Use a SubQuery to display the city of all offices whose sales are below the average target for all the offices.

5. Use a SubQuery to list all sales rep IDs that have an average sales dollar amount greater the average sales dollar amount of the sales reps. You can use a correlated sub query or an correlated subquery. You need a correlated sub query if you wish to compare the average sales dollar amount to the average sales dollar amount to all the other sales reps NOT including the the sales rep you are comparing to.

Here are the tables and fields:

TABLE NAME: OFFICES

OFFICEID	CITY	REGION	MGRID	TARGET	SALES
22	Denver	Western	6	300000.00	186042.00
11	New York	Eastern	9	575000.00	692637.00
12	Chicago	Eastern	NULL	800000.00	735042.00

TABLE NAME=SALESREPS

REPID	NAME	HIREDATE	QUOTA	SALES	OFFICEID
1	Bill Adams	2008-10-11	350000.00	367911.00	13
2	Mary Jones	2013-01-04	200000.00	234877.00	13
3	Sue Smith	2009-06-15	250000.00	145000.00	12

TABLE NAME=CUSTOMERS

CUSTID	COMPANY	PHONE	CREDITLIMIT	CUSTREPID
2111	JCP Inc	4569483748	50000.00	8
2102	First Corp	9148425732	65000.00	8
2103	Division Mfg.	8374475757	50000.00	5

TABLE NAME=PRODUCTS

PRODUCTID	DESCRIPTION	PRODUCTPRICE	QUANTITY_ON_HAND
2A45C	Deluxe Driller	1290.99	12
4100Y	Welder	2343.99	24
XK47	Reducer	1854.00	100

TABLE NAME=ORDERS

ORDID	CUSTID	ORDERDATE	PRODUCTID	REPID	QTY	TOTALAMT
112961	2117	2017-01-02	2A447	2	6	33600.00
113012	2111	2017-01-03	41003	2	2	6312.00
112989	2101	2017-01-03	114	1	1	1567.00

Answer 1

1.SELECT DESCRIPTION,QTY

   FROM PRODUCTS NATURAL JOIN ORDERS

   ORDER BY DESCRIPTION

2.SELECT DESCRIPTION

   FROM PRODUCTS NATURAL JOIN ORDERS

   WHERE QTY > 3

3.SELECT DISTICNT REPID

   FROM SALESREPS

   WHERE SALES>(SELECT AVG(SALES)

                                FROM SALESREPS)

4.SELECT CITY FROM

    OFFICES WHERE

    SALES<(SELECT AVG(TARGET)

                   FROM SALESREPS)

5.SEELCT REPID

   FROM SALESREPS S

   WHERE SALES>(SELECT AVG(SALES)

                               FROM SALESREPS R

                               WHERE S.REPID<>R.REPID)

NOTE:'<>' is not equal symbol in MySQL