Question
Generate a list of salespeople sorted descending by hire date. Show the first name, last name, hire date, and salary for each salesperson. Generate a
Generate a list of salespeople sorted descending by hire date. Show the first name, last name, hire date, and salary for each salesperson.
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.
Generate a list of customers with annual incomes greater than $80,000 who purchased cars from salesperson Katie. Show the first name, last name, and annual income for each of these customers.
Which customers purchased vehicles from salesperson Dudley despite having a Poor, Very Poor, or Extremely Poor credit rating? Show the first name, last name, and credit description for these customers.
Salesperson Colin recently had an encounter with a customer who he would like to contact again. Unfortunately, he cant remember if the customers last name was Hansen or Hanson. Construct a query to help Colin find the correct customer to follow up with. Show the customers first name, last name, and phone number.
Construct a query to determine how many encounters resulted in the customer declining to make a purchase. (hint: A customer can have many encounters)
Customers with low annual income or poor credit ratings often have trouble qualifying for credit. A new, local credit agency may be better able to serve this market segment than current options. The manager would like to talk to a few salespeople who have had the most encounters with these customers. Create a list that shows each salesperson (first and last name), the number of encounters each has had with customers that have an annual income of $25,000 or less or have a credit description that is "Very Poor" or "Extremely Poor". Order the results alphabetically by the salesperson's last name. Your query should have three columns: sFirstName, sLastName, encounter_count.
Generate a query to find the average income of customers who purchased a car on their interaction. Assume customers only have one car purchase (i.e., their income wont be listed twice).
Construct a query to show customers with an annual income greater than $100,000 who have a credit rating of Fair, Poor, Very Poor, or Extremely Poor. Show each customers first name, last name, credit description, and annual income.
Management is considering closing on Sundays and would like to have a feeling for the kinds of customers that come to the dealership on Sunday. Create a totals query that shows the count of encounters on Sundays of July, 2015--grouped by the credit description held by the customer of the encounter. The Sundays in July of 2015 are the 5th, 12th, 19th, and 26th. Your answer should have two columns (CreditDescription and encounter_count) and should be sorted alphabetically by credit description.
Encounter CreditRating 3 CreditID CreditDescription MinFICO MaxFICO Comments Customer * CustomerID CFirstName CLastName CPhone Annuallncome CreditID 3 EnciD SalesID Customer EncDate Purchase Sales Person Y SalesID SFirstName SlastName SHireDate Salary Encounter CreditRating 3 CreditID CreditDescription MinFICO MaxFICO Comments Customer * CustomerID CFirstName CLastName CPhone Annuallncome CreditID 3 EnciD SalesID Customer EncDate Purchase Sales Person Y SalesID SFirstName SlastName SHireDate SalaryStep by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started