ALMS DB

Implement the following tasks using ORACLE SQL*Plus.

Download the file ALMSchema.sql from the LMS site and run it on ORACLE SQL*Plus. This file contains all the CREATE and INSERT statements you will need for this assignment

To run the file, issue the following command: @D:dbfALMSchema.sql

Where D:dbf is the location of the file (for example).

NOTE: YOU DO NOT NEED TO INSERT MORE DATA INTO THE TABLES.

The list of tables available for this assignment is the following:

Manufacturer(manufacturerID, name, region)
Model(modelNo, name, type, previousModel, manufacturerID)
Car(VIN, dateAcquired, yearBuilt, purchasedPrice, askingPrice, currentMileage, modelNo)
CarColour(VIN, colour)
Feature(featureID, desc, category)
CarFeature(VIN, featureID)
Customer(custID, name, DOB, streetAddress, suburb, postcode, gender, phoneNo, email, type)
CustomerPreference(custID, featureID)
SalesAgent(agentID, name, DOB)
SeniorAgent(agentID, yearPromoted)
JuniorAgent(agentID, supervisor)
SalesTransaction(VIN, custID, agentID, dateOfSale, agreedPrice)
ViewingParty(viewingPartyID, contactNo, email)
Organisation(viewingPartyID, name)
InternationalGuests(viewingPartyID, country)
CarsViewed(VIN, viewingPartyID, dateViewed, amountPaid)
UserAccount(custID, dateRegistered)
Contest(contestNo, startDate, endDate, prizeDesc, contestWinner)
ContestParticipants(custID, contestNo)

NOTE: PK is printed underlined and FK is printed italic in italics.

Task 1

Using the tables provided above, provide SQL statements for the following queries.

• Display the name of the customer who has purchased the most cars from Archies Luxury Motors.
• For each sales agent, display the total number of sales they have made thus far. Order by number of sales decreasing.
• 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.
• Display the details (i.e. Manufacturer name, model name, type) of the top selling European car model.
• 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
• What is the most common car colour that junior agents sold in the month of January 2014?
• Display the total profit to date for Archies 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.
• Display the average number of sales transactions (i.e. car sales) per month.
• 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).
• 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.

• Write a stored procedure that displays the contact details of viewing parties who havent 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.
• 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.
• Write a stored function that uses a senior agents 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).

Task 3

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 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 cars 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).
• A Trigger that is activated when a new UserAccount is accidentally associated with a non-VIP Customer.