Details You will be given typical business questions that can be answered with a

ID: 3917367 • Letter: D

Question

Details

You will be given typical business questions that can be answered with an SQL query.

Treat them like real business concerns you were asked to handle.

Just because the business didn't spell out a requirement doesn't mean you aren't responsible. For example, just because #1 specified sorting doesn't mean you're off the hook for sorting on #3.

In addition, don't give out too much information to the business. The CEO doesn't give a @#$%! about the item id#. For example, #4 should be only one row with the item name and the volume. Again, don't whine about "it doesn't say to include the volume"--that's a huge disfavor you get in school. Ambiguity is the rule in real life. Deal with it. You have to make business decisions in what to report andhow to format it.

Make column headers human readable in the result table. In fact, even if they are perfectly human readable, such as "name", change it to "Customer Name" or "Top Grossing Customer"

1. I need a list of customer mailing labels sorted by zip code. (The post office gives you a cheaper rate if you pre-sort it for them. I know this because in a past life I printed and mailed municipal power bills.)

2. I need to know the percentage of repeat customers.

3. I need to know the 2 largest orders by gross revenue.

4. I need to know the number one selling item by volume, and who the vendor is.

5. Are any vendors also customers? If so, who?

6. Get me a list of our items by total revenue.

7. Which vendors grossed the most?

8. Which item is in the most orders?

9. Which customers have the most orders?

10. What are our cheapest and most expensive items? What's their volume? What's the price? What's the total revenue?

Setting Up MySQL and Populating the Data

1. The tables are already created and populated at http://sqlfiddle.com/#!9/284793 (Links to an external site.)Links to an external site.

2. Write your own queries to answer the questions above.

Explanation / Answer

select cust_name as customer_name ,cust_address as customer_address ,cust_city as customer_city ,cust_state as customer_state,cust_zip as customer_zip,cust_country as customer_country
from Customers
order by cust_zip;

output:

Fun4All

select (ro.No_of_rpt_ordr/c.No_of_distinct_ordr)*100 as "Percentage of repeat customers"
from
(select count(distinct cust_id) as No_of_distinct_ordr
from Customers) c
cross join
(select count(*) as No_of_rpt_ordr
from
(select cust_id
from Orders
group by cust_id
having count(*) > 1) o ) ro;

Output:

select order_num, sum(quantity)*sum(item_price) "Gross revenue"
from OrderItems
group by order_num
order by 2 desc
limit 2;

Output:

5602.5

select p.prod_name as product_name,v.vend_name
from
(select result.prod_id,result.items,result.rank from (
SELECT prod_id,
items,
IF(items=@last,@curRank:=@curRank,@curRank:=@_sequence) AS rank,
@_sequence:=@_sequence+1,
@last:=items
FROM (select prod_id, sum(quantity) as items
from OrderItems
group by prod_id) a , (SELECT @curRank := 1, @_sequence:=1, @last:=0) r
ORDER BY prod_id asc) as result
where result.rank=1) a
join products p
on a.prod_id=p.prod_id
join Vendors v
on p.vend_id=v.vend_id

Output:

Rabbit bean bag toy

select c.cust_name,v.vend_name
from Customers c
join Vendors v
on c.cust_name=v.vend_name;

Output:

no records, Hence to customer is a vendor.

select prod_id, sum(quantity)*sum(item_price) "Total revenue"
from OrderItems
group by prod_id
order by prod_id;

Output:

RGAN01

select v.vend_name,sum(a.Total_revenue) "Revenue"
from
(select prod_id, sum(quantity)*sum(item_price) as Total_revenue
from OrderItems
group by prod_id) a
join products p
on a.prod_id=p.prod_id
join Vendors v
on p.vend_id=v.vend_id
group by v.vend_name;

Output:

Doll House Inc.

select p.prod_name as Product_name
from
(select prod_id,count(*) as No_of_prod
from OrderItems
group by prod_id
order by No_of_prod desc
limit 1) a
join products p
on a.prod_id=p.prod_id

Output:

18 inch teddy bear

select c.cust_name
from
(select cust_id,count(order_num)
from Orders
group by cust_id
order by count(order_num) desc
limit 1) a
join customers c
on a.cust_id=c.cust_id;

Output:

Village Toys

10.

select p.prod_name as product_name,a.items as price,a.quantity as volume,(a.quantity*a.items) as "Total revenue"
from
(select result.prod_id,result.items,result.quantity,result.rank from (
SELECT prod_id,
items,quantity,
IF(items=@last,@curRank:=@curRank,@curRank:=@_sequence) AS rank,
@_sequence:=@_sequence+1,
@last:=items
FROM (select prod_id,min(item_price) items,sum(quantity) quantity
from OrderItems
group by prod_id
order by min(item_price)) a , (SELECT @curRank := 1, @_sequence:=1, @last:=0) r
ORDER BY prod_id asc) as result
where result.rank=1) a
join products p
on a.prod_id=p.prod_id
UNION
select p.prod_name as product_name,a.items as price,a.quantity as volume,(a.quantity*a.items) as "Total revenue"
from
(select prod_id,max(item_price) items,sum(quantity) quantity
from OrderItems
group by prod_id
order by max(item_price) desc
limit 1) a
join products p
on a.prod_id=p.prod_id

Output:

521.4

Doll House

Inc.

customer_name customer_address customer_city customer_state customer_zip customer_country Fun4All 1 Sunny Place Muncie IN 42222 USA Kids Place 333 South Lake Drive Columbus OH 43333 USA Village Toys 200 Maple Lane Detroit MI 44444 USA The Toy Store 4545 53rd Street Chicago IL 54545 USA

Fun4All

Output:

Percentage of repeat customers

select order_num, sum(quantity)*sum(item_price) "Gross revenue"
from OrderItems
group by order_num
order by 2 desc
limit 2;

Output:

order_num Gross revenue 20007 9980 20009

5602.5

Output:

product_name vend_name Fish bean bag toy Doll House Inc. Bird bean bag toy Doll House Inc.

Rabbit bean bag toy

select c.cust_name,v.vend_name
from Customers c
join Vendors v
on c.cust_name=v.vend_name;

Output:

no records, Hence to customer is a vendor.

select prod_id, sum(quantity)*sum(item_price) "Total revenue"
from OrderItems
group by prod_id
order by prod_id;

Output:

prod_id Total revenue BNBG01 3229.2 BNBG02 3229.2 BNBG03 3229.2 BR01 1377.6 BR02 89.9 BR03 7665.9

RGAN01

Output:

vend_name Revenue Bears R Us 9133.4

Doll House Inc.

select p.prod_name as Product_name
from
(select prod_id,count(*) as No_of_prod
from OrderItems
group by prod_id
order by No_of_prod desc
limit 1) a
join products p
on a.prod_id=p.prod_id

Output:

Product_name

18 inch teddy bear

select c.cust_name
from
(select cust_id,count(order_num)
from Orders
group by cust_id
order by count(order_num) desc
limit 1) a
join customers c
on a.cust_id=c.cust_id;

Output:

cust_name

Village Toys

10.

Output:

product_name price volume Total revenue Rabbit bean bag toy 2.49 360 896.4 Fish bean bag toy 2.49 360 896.4 Bird bean bag toy 2.49 360 896.4 18 inch teddy bear 11.99 165 1978.35 10209

521.4

Doll House

Inc.

829 Riverside Drive Phoenix AZ 88888 USA

Navigate

Details TechMedia, Inc. is a U.S. firm that is planning to build a new productio

Details Your program will process letters from Santa’s mailbag, using the MailBa

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

Details You will be given typical business questions that can be answered with a

Question

Explanation / Answer

Related Questions

Navigate