BUS 630 BI assignment

Access Queries/SQL (30 points)

In groups of 2 or individually, answer 7 of the following 10 questions using the Valley Vineyards 2020 database. Copy your SQL query and the printed result and paste into a Word document for submission. I need to have the query in addition to the results. If you get a lot of output for an answer, something might be wrong. If you work with someone, put both names on the assignment, but everyone should submit an assignment. Some questions require analysis more than just the answer from the query.

Part 1. SQL 21 points

1)Produce a list of orders (customerId, customerName, City, order date, shipped date) that came from our Colorado customers.

a.How many were there for 2019 compared to 2018?

b.How does this compare to the rest of the US?

Hint: use the criteria filter

2)The Marketing Dept has requested a list of US Customers by name (alphabetically), categorized by state. Include contact information, address information, and phone.

Hint: use the group by, order by and criteria filter

3)Produce a price list of our products by product category.

Hint: use the group-by tool ()

4)What is our best-selling product in terms of number of units (all years)? How does this differ from comparing our best-selling product in terms of dollar sales volume? Explain.

Hint: use the order by or top values

5)We are sending a rep to make calls on our French customers.

a.Produce a list including contact information and sales data. We only have 3 days, so the rep can only do 3 or 4 stops.

b.Where would you send the rep and why?

Hint: Combine customer and order and then filter on country

Or try group by and count ()

6)Compare 2019 sales to 2018 for Merlin's Magic Merlot.

a.Should we continue this product?

b.How does it compare to our other Merlots?

c.What do the trends look like?

Hint: combine Order, order details and product and use the sum ()

7)The people who buy Grand Mesa Colterris:

a.also purchase what other products?

b.What could we do with this information? ()

May take a couple of queries.

8)What were 2019 dollar sales by employee?

a.How did they change from 2018 and who should we reward?

b.Who should we be concerned about?

Combine Order, Order details and Employee, filter by year (criteria line), use the ()

9)Which orders have not yet been shipped?

Hint: To know this you must first know what?

10) Considering 2018 and 2019 gross sales, what should we expect for 2020?

Part 2. Dashboard - 9 points

Design a dashboard for Valley Vineyard's owners. What elements should be on the dashboard? Consider what you would recommend should be their KPI's.

1)Design the dashboard (does not have to be functional) with at least 4 indicators. Show your rendering.

2)Describe where the data will come from.

3)What product would you recommend that Valley use for the dashboard.