SQL Task, Stored Procedures and Triggers for a Luxury Car Dealership

Task 1: SQL Queries for Data Analysis

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 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 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 permissible).

j. Display the most popular car model across all sales transactions and viewing parties.

[5 marks each – 50%]

Task 2 [30 marks]

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 include the total number of cars sold in the result.

c. Write a stored function that uses a senior agent’s ID as input and calculates the total commission owed to date for that agent. You also need to show an SQL statement to display the total amount of commission (i.e. the sum) owed to all of the senior agents in the database. (Refer to the case study in Assignment 1 Part 1 for a description of the commission policy).

[10 marks each – 30%]

Task 3 [20 marks]

Provide the implementation of the following triggers. For submission, please include both the PL/SQL code and an insert statement to demonstrate the trigger functionality.

a. A Trigger which automatically stores in a separate table called ‘ExcellentSale’ the Sales Agent name, car model and manufacturer name, each time the agreed price of a SalesTransaction is more than 20% of the car’s asking price. (Note: You need to create the ‘ExcellentSale’ table before implementing this trigger. To create the primary key, use a sequence that starts at 1 and increments by 1).

b. A Trigger that is activated when a new UserAccount is accidentally associated with a non-VIP Customer.

[10 marks each – 20%]