Download Solution: Click to Download Solution
Solution File Name: Task1_234AAA.doc
Unzip Password: prestobear.com
Do the queries for:
a. Display the name of the customer who has purchased the most cars from Archie’s Luxury Motors.
b. For each sales agent, display the total number of sales they have made thus far. Order by number of sales decreasing.
c. For each month in 2014, display the total profit (i.e. using the attributes purchasedPrice and agreedPrice) generated from car sales only. Do not consider any discounts.
d. Display the details (i.e. Manufacturer name, model name, type) of the top selling European car
e. Display the details of the best junior sales agent (i.e. ID, Name, DOB) who is under the age of
25, in terms of their average negotiation profit (consider the askingPrice and
agreedPrice attributes). The agent returned must have at least three sales to their name.
f. What is the most common car colour that junior agents sold in the month of January 2014?
g. Display the total profit to date for Archie’s Luxury Motors. Note that you must take into
consideration all car sales (ignoring unsold cars as they may be sold at a later date) and viewing
party shows, and any VIP discounts that may apply. Do not subtract any commission owed to
the senior sales agents.
h. Display the average number of sales transactions (i.e. car sales) per month.
i. Display the details (i.e. Manufacturer name, model name) of the cars that are in the top five in
terms of highest mileage. Sort the result set in descending order. (Please note: Your solution
must use a correlated subquery. The use of OLAP functions (such as RANK, etc.) is not
j. Display the most popular car model across all sales transactions and viewing parties.