Business Analytics

Performance Lawncare – SQL In-class Exercise

This relational database (PerfLawncare) contains 3 Tables (Customers, Reps, Transactions). The Transactions Table contains several thousand instances of transactions over a two-year period.Each transaction is associated with a particular sales rep and customer and the attribute “Sales” is the revenue for a transaction. The Reps Table contains information regarding the organization’s sales reps, and the Customers Table contains information regarding the organization’s customers. All questions are for the two-year period 2012 and 2013.

  1. In August 2012 the organization’s total expenses were the largest of any month. How many transactions in August 2012 had expenses larger than the average for that month?

SELECT count(transid) AS Total

FROM transactions

WHERE transdate like (#8/1/2012#)

and expenses > (select avg(expenses) from transactions

where transdate like (#8/1/2012#))

Query1
Total
67
  1. List the Reps according to their total sales in the Western marketin over the entire period?

SELECT RepName, sum(sales) AS TotalSales

FROM transactions, Reps

WHERE transactions.RepId=Reps.RepId

and market = 'West'

GROUP BY RepName

ORDER BY Sum(sales);

Query2
RepName / TotalSales
Thomas / $21,916.00
Proctor / $22,450.00
Boykin / $24,356.00
Long / $26,161.00
Hodges / $26,860.00
Cook / $27,045.00
Kim / $28,629.00
Kelly / $30,534.00
Daniels / $31,580.00
Smith / $32,733.00
  1. List the Reps according to who generated the least total profit (sales minus expenses)over the entire period (sort from lowest to highest)

SELECT Repname, sum(sales-expenses) AS Profit

FROM transactions, Reps

WHERE transactions.RepId=Reps.RepId

GROUP BY RepName

ORDER BY sum(sales-expenses);

Query3
Repname / Profit
Kim / $47,954.00
Thomas / $48,490.00
Proctor / $52,674.00
Boykin / $55,126.00
Long / $55,196.00
Hodges / $57,848.00
Kelly / $59,178.00
Cook / $65,395.00
Smith / $72,140.00
Daniels / $76,148.00
  1. List the Customerswho generated the most profit (sales minus expenses) over the entire period for the mowers category (sort highest to lowest)

SELECT Custname, sum(sales-expenses) AS Profit

FROM Customers, Transactions

WHERE transactions.CustId=Customers.CustId

and productline ='mowers'

GROUP BY CustName

ORDER BY sum(sales-expenses) DESC;

Query4
Custname / Profit
Planet Express Inc. / $20,892.00
Buy n Large / $19,424.00
InGen / $18,755.00
Aperture Science Inc. / $18,197.00
Stark Industries / $17,949.00
Spectre / $17,681.00
Pied Piper / $17,296.00
Grey Fork Marketing / $16,986.00
Oceanic Airlines / $16,155.00
Burns Industries / $16,007.00
Cyberdyne Systems / $15,248.00
Brawndo / $15,230.00
Oscorp / $15,178.00
Gringotts / $14,396.00
Los Pollos Hermanos / $14,189.00
Virtucon / $13,604.00
Lucuna Inc. / $12,663.00
Paper Street Soap Co. / $12,525.00
Clampett Oil / $11,594.00
Umbrella Corp. / $11,227.00
  1. List the male rep and his sale, who generated the highest individual salefor customers in Arkansas in theyear of 2012 inclusive?

SELECT RepName, Sales

FROM transactions, Reps, Customers

WHERE transactions.RepId=Reps.RepId

and transactions.CustId=Customers.CustId

and Gender = 'M'

and Sales = (Select max(sales) From transactions, customers

WHERE transactions.CustId=Customers.CustId

and State = 'Arkansas' and TransDate like '*2012');

Query5
RepName / Sales
Long / $793.00
  1. List the customer name and average sales for Texas-based customers who had a female rep that produced sales that exceeded the average sales for Electric products in small markets over the entire two year period?

SELECT CustName, avg(Sales) AS AvgSales

FROM transactions, Reps, Customers

WHERE transactions.RepId=Reps.RepId

and transactions.CustId=Customers.CustId

and Gender = 'F'

and state = 'Texas'

and Sales >

(Select avg(sales) From transactions

WHERE marketsize = 'small market'

and ProductType = 'Electric')

GROUP BY Custname;

Query6
CustName / AvgSales
Burns Industries / $251.20
Clampett Oil / $218.39
Grey Fork Marketing / $273.89
Lucuna Inc. / $231.93
Planet Express Inc. / $262.73

1