Reference no: EM13934872
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
|
Could the team captains have made any different decisions
: Would you have done anything differently in getting the team to embrace their differences? ?Also, consider the transition from the football camp to returning to the community. Could the team captains have made any different decisions to bring the ..
|
|
What is value of call option if underlying stock price
: What is the value of a call option if the underlying stock price is $102, the strike price is $95, the underlying stock volatility is 37 percent, and the risk-free rate is 4.1 percent? Assume the option has 124 days to expiration
|
|
Determine the adjusted balances of the accounts
: Determine the adjusted balances of the accounts if the balance in Manufacturing Overhead is considered material in amount Round to the nearest whole dollar.
|
|
Proposal of a health promotion intervention
: Assessment: Poster Presentation. For this assessment you are required to present an academic poster which details a proposal of a health promotion intervention to be carried out in the university.
|
|
Terms of their average negotiation profit
: 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.
|
|
Program in visual basic that prints * in a diamond shape
: Do you have any suggestions?
|
|
What is value of call option on stock with strike price
: A stock is currently priced at $64 and has an annual standard deviation of 44 percent. The dividend yield of the stock is 3.1 percent, and the risk-free rate is 6.1 percent. What is the value of a call option on the stock with a strike price of $61 a..
|
|
Case studyaditya mass communication private limited
: A.P. State Road Transport Corporation, Hyderabad advertised tender notice on calling for tenders for display of advertisements on the buses owned by it. According to the condition of the Tender Notice, each tender form had to be accompanied by a dema..
|
|
What are the ketone bodies
: What are the ketone bodies? Why do they have that name, and how do they arise in metabolism? Explain why they accumulate under conditions of carbohydrate starvation or in diabetes.
|