Question
Can someone please help with these MySQL queries: 1. a query to display the description of a product and the number of orders for that
Can someone please help with these MySQL queries:
1. 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 query, but this time just display those product names and the number of sales that have more than 3 sales.
3. 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. A SubQuery to display the city of all offices whose sales are below the average target for all the offices.
5. 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 |
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started