SQL Data Manipulation

Objectives

• Use SQL to run reports
• Use joins to connect tables together
• Use aggregate functions(SUM/COUNT/AVG)
• Use conditions to limit results(WHERE Clause)
• Use sub queries to select related data

Problem

Due to the slowdown in economy MaxCar has been struggling with its sales volume. In order to keep all its employees and prevent layoffs MaxCar decided to analyze their sales data and come up with an aggresive marketing campaign. You have been hired by MaxCar as a senior data analyst and your task is to perform a thorough of their sales data and identify key sales. Below are the analysis tasks that you have to complete.

• Identify lowest sales by Region. display the 4 cities with the lowest sales. Order your results by sales from lowest to highest then by city name in descending order, then by state in ascending order. See image.
• Identify Regions with Most Loans. Display all cities along and for each city display the number of loans, the total loan amount, and the average loan amount. See image.
• Identify Amount Paid by Customer. Display total payments made by customers. Limit your results to customers who are financing loans greater than $20,000.00. Sort the results by LASTNAME, TOTAL_PAYMENTS made, then FIRST_NAME. (HINT: Use a sub-query to find all customers who are financing loans greater than $20,000.00). See image.
• Identify Customer Purchase Trends. Display all the customers that bought the same type of car(s) MAKE as Homer Simpson. Show the customer FIRSTNAME, LASTNAME, and the Car MAKE and MODEL they purchased. (HINT: Use a sub-query to find what make of car(s) Homer has purchased). Sort in ascending order by FIRSTNAME, LASTNAME.
• Identify customers without ORDERS. Display all customers who do not have any orders. Display CUSTOMER_ID, FIRST_NAME, LAST_NAME, ADDRESS(CITY, STATE, ZIP).
• Identify popular car features. Display all customers who purchased cars with heated seats. Display CUSTOMER_ID, FIRST_NAME, LAST_NAME, ADDRESS(CITY, STATE, ZIP), MAKE, MODEL, YEAR.
• Identify popular insurance coverage. Display the most popular insurance coverage and the poeple that have it. Display the coverage ID, the COVERAGE_TYPE, CUSTOMER_ID, FIRST_NAME, LAST_NAME, ADDRESS(CITY, STATE, ZIP).
• Identify Salespeople with lowest sales. Display the salesperson that has the lowest total sales Display sales person name then total amount of sale.
• Identify customers who live in the same state. Display all the customers that live in the same state as Bart Simpson. Display their CUSTOMER_ID, FIRST_NAME, LAST_NAME, ADDRESS(CITY, STATE, ZIP).
• Create a Stored Procedure to Add a new Customer to the database. Create a stored procedure to add a new customer to the database. Name the stored procedure ADD_CUSTOMER and add yourself as a customer.