Question:

Practice using these three tables, what are the best queries for MySQL according to the situational scenarios? The first practice queries is completed for you.

Table - Customer

CustomerID - Primary Key

CustomerFirstName

CustomerLastName

CustomerPhone

AnnualIncome

CreditRating

Table - Encounter

EncID - Primary Key

SalesPersonID - Foreign Key

CustomerID - Foreign Key

EncDate

Purchase (Yes or blank)

Table - Salesperson

SalesID - Primary Key

SalesFirstName

SalesLastName

SalesHireDate

SalesSalary

1.Practice creating a list of salespeople sorted descending by hire date. Show the ID, first name, last

name, hire date, and salary for each salesperson.

SELECT SalesID, SalesFirstname, Saleslastname, saleshiredate, salessalary

FROM salesperson

ORDER BY saleshiredate DESC;

2.Practice creating a list of customers whose last name begins with the letter S. Show the first and

last names of these customers. Sort the list of customers descending by last name.

3.Practice creating a list of customers with annual incomes greater than $65,000 that purchased a car.

Purchase will have a value of Yes.

4.Practice creating a list of customers that purchased cars from salesperson Michael. Show the first

name, last name, and annual income for each of these customers.

5.Practice creating, customers purchased vehicles despite having a Poor, Very Poor, or Extremely

Poor credit rating? Show the first name, last name, and credit description for these

customers.

6.Practice creating, Salesperson Thomas recently worked with a customer who he would like to contact again.

Unfortunately, he cannot quite remember if the customer's last name was Smith or

Smithe. Construct a query to help Thomas find the correct customer to follow up with.

Show the customer's first name, last name, and phone number.

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

interaction.

8.Practice constructing a totals query to examine the relationship between salespeople and the annual

income of the customers they target in their interactions. Show the salespeople's first name

and the average annual income of their customers in your result. You do not need to

include a criterion for Purchase in this query.

Thank you for any help in understanding how to create these situations.