CSE2/4DBF Assignment 2 Task 1

Task 1 [50 marks]

Using the tables provided above, provide SQL statements for the following queries.
a. Display the name of the customer who has purchased the most cars from Archie’s Luxury Motors.
b. For each sales agent, display their ID and name, along with the total number of sales they have made thus far. Order by number of sales decreasing.
c. For each month in 2020, display the total profit (i.e., using the attributes purchased Price and agreed Price) generated from car sales only. Do not consider any discounts.
d. Display the details (i.e., Manufacturer name, model name, type, and the number of times it was sold) of the top selling European car model. Hint: use the manufacturer region information.
e. Display the average number of sales transactions (i.e., car sales) per month. Hint: count the number of sales for each month, then divide the count by the number of years the dealership has been making transactions for.
f. 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 later) and viewing party shows, and any VIP discounts that may apply. Do not subtract any commission owed to the senior sales agents.
- Note: A VIP customer gets a 5% discount for any car purchase greater than or equal to $50,000 AUD. However, the agreed price stored in the database is without the discount. For each car sold to a VIP customer with an agreed price of $50,000 AUD or more, you should subtract this 5% discount from the agreed price when calculating the profit. Also note that the profit from each viewing party show is equal to the amount paid for the show.

[a – e: 8 marks each, f: 10 marks – 50%]