Answered step by step
Verified Expert Solution
Link Copied!

Question

1 Approved Answer

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

blur-text-image

Get Instant Access to Expert-Tailored Solutions

See step-by-step solutions with expert insights and AI powered tools for academic success

Step: 2

blur-text-image

Step: 3

blur-text-image

Ace Your Homework with AI

Get the answers you need in no time with our AI-driven, step-by-step assistance

Get Started

Recommended Textbook for

Databases And Information Systems 1 International Baltic Conference Dbandis 2020 Tallinn Estonia June 19 2020 Proceedings

Authors: Tarmo Robal ,Hele-Mai Haav ,Jaan Penjam ,Raimundas Matulevicius

1st Edition

303057671X, 978-3030576714

Students also viewed these Databases questions

Question

Why We Form Relationships Managing Relationship Dynamics?

Answered: 1 week ago