Question

Can you please write the MySQL Query sentences for the following items below?

For example: SELECT * FROM employees;

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.

   C. Generate a list of customers with annual incomes greater than $80,000 that purchased a car. HINT: Purchase will have a value of “Yes”.

   D. 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.

   E. Which 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.

   F. 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 customer’s first name, last name, and phone number.

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

   H. Construct 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.

Answer 1

A.

Select SalesPersonID,FirstName,LastName,HireDate,Salary from Salesperson order by HireDate desc;

B.

Select FirstName,LastName from Customers where LastName Lile 'M%' order by LastName desc;

C.

Select * from Customers where AnnualIncome > 80000 and Purchase = 'Yes';

D.

Select Customer.FirstName,Customer.LastName,AnnualIncome from Customer inner join SalesPerson on Customer.SalesPersonId = SalesPerson.SalespersonId where SalesPerson.FirstName = 'Jesse';

E.

Select FirstName,LastName,CreditDescription from Customer where CreditDescription IN('Poor','Very Poor','Extremely Poor');

F.

Select Customer.FirstName,Customer.LastName,PhoneNumber from Customer inner join SalesPerson on Customer.SalesPersonId = SalesPerson.SalespersonId where Customer.LastName IN('Hansen','Hanson') and SalesPerson.FirstName = 'Jungpil';

G.

Select avg(AnnualIncome) from Customers where Purchase = 'Yes';

H.

Select SalesPerson.FirstName,Customer,AnnualIncome from Customer inner join SalesPerson on Customer.SalesPersonId = SalesPerson.SalespersonId ;

Do ask if any doubt. Please upvote.