CIDM6350 American Fork Auto Sales

Instructions: In this project, you will analyze American Fork Auto Sales (AFAS) practices using SQL queries.

Step1: Use the generate_cars script provided to generate the CARS schema and CUSTOMER, ENCOUNTER, CREDITRATING, and SALESPERSON tables in MySQL.

a. After running the CREATE SCHEMA statement (CREATE SCHEMA IF NOT EXISTS cars;) in the script, set the DEFAULT schema to CARS using the DBMS option (Hint: first click the "refresh" button on the left side bar to display the newly created schema - cars; and then right click this schema - click Set as Default Schema)

b. Then uncomment the rest of the text and execute the SQL again (Hint: remove /* in the script and then run the rest statements to create tables and insert instances into tables)

c. Suggestion: have a look at ERD of this schema and use the "select *" statements to have an overall of tables and their records before you move to next steps.

Step2: Create and save a .sql script file with 8 SELECT queries that produce the results below.

Project Queries:

1. Generate a list of salespeople sorted descending by hire date. Show the ID, first name, last name, hire date, and salary for each salesperson.

2. Generate a list of customers whose last name begins with the letter "M." Show the first and last names of these customers. Sort the list of customers descending by last name.

3. Generate a list of customers with annual incomes greater than $80,000 that purchased a car. Show the first name, last name, and annual income for each of these customers. (HINT: Purchase will have a value of "Yes")

4. Generate a list of customers that purchased cars from salesperson "Jesse". Show the first name, last name, and annual income for each of these customers.

5. Which customers purchased vehicles despite having a "Poor," Very Poor, or Extremely Poor credit description? Show the first name, last name, and credit description for these customers.

6. Salesperson "Jungpil" recently worked with a customer who he would like to contact again. Unfortunately, he cannot quite remember if the customer's last name was Hansen or Hanson. Construct a query to help Jungpil find the correct customer to follow up with. Show the customers first name, last name, and phone number.

7. Generate a query to find the average income of customers who purchased a car on their interaction.

8. Construct a query to show salespeople's first name and the average annual income of their customers in your result. (HINT: You do not need to include a criterion for Purchase in this query)