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 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 model.
e. Display the details of the best junior sales agent (i.e. ID, Name, DOB) who is under the age of
2, in terms of their average negotiation profit (consider the askingPrice and agreed Price 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 permissible).
j. Display the most popular car model across all sales transactions and viewing parties.
Task 2
Provide the implementation of the following stored procedures and function. For submission, please include both the PL/SQL code and an execute procedure/SQL statement to demonstrate the functionality.
a. Write a stored procedure that displays the contact details of viewing parties who haven't visited the dealership for over six months. For organisations, include the organisation name in the output. For international guests, include their country in the output.
b. Write a stored procedure that accepts a particular year as input, and as output displays the number of cars sold grouped by the 3 mileage groups (Low Mileage: <50000km, Medium
Mileage: >=50000km & <150000km, High Mileage: >=150000km). Also incl