Refer to the tblCustomer table below. As a data analyst, you have been requested

Question

Refer to the tblCustomer table below. As a data analyst, you have been requested to select rows that meet the following requirements: List each customer name and their income for customers with a balance between 300 and 600. The customer name will be in the format of last name and first letter of first name followed by a '.': Smith B. - give this column an alias. The income must be displayed with a $, commas around the thousands and two decimal places. Give this column an alias. Assume that the Income field is defined as a money data type.

tblCustomer:

CustID (PK)

10

Paid

*Overdue Status indicates customers with a balance that is 30 or more days overdue

Part 1: Write the complete SQL statement needed to return the rows that meet these requirements.

Part 2: What rows will be returned from this query? List the customer ID only of the rows to be returned.

Part 3: What function did you use to format the income column? Explain why you used this function. List one other purpose of this function. (Points : 25)

CustID (PK)

LastName FirstName Phone City Region Overdue Status* Preferred Income NumOrders Balance Birthdate

10

Smith Bob 123-4567 Orlando North

Paid

Y 10000 5 100 1/1/1978 12 Ramirez Jose 422-1234 Apex South Paid Y 50000 16 600 2/8/1970 13 Washington Terrell 951-1234 Sujour East [null] Y 20000 25 700 3/5/1985 15 Jones Bill 963-7894 AxeCrossing West Unpaid Y 16900 2 500 12/1/1990 17 Morales Sandy 951-1234 Orlando North Paid N 85000 90 400 8/3/1986 21 Principe Michelle 963-4561 Apex South Paid N 94000 85 300 7/2/1974 23 Mavis John 147-4561 Sujour East Unpaid Y 25000 26 600 3/5/1989

Explanation / Answer

Part1:

MySQL statement that concates the last name with first letter of first name
with '.' dot symbole . Then give a name as NAME.
Use format to format the INCOME amount to 2 decimal places and thousands
saparator and concat the result with '$' sign

The WHERE condition BETWEEN 300 and 600 checks the income values
in the range of the 300 and 600

Query:
SELECT concat(LastName, substr(FirstName, 1,2), '.') AS NAME,
concat(format(INCOME,2),'$') AS AMOUNT from tblCustomer
where INCOME BETWEEN 300 AND 600;

------------------------------------------------------------------------------------------------------------

Part2:
Except the first row, all other rows will be displayed since
the first row balance amount is 100.

SELECT CustID from tblCustomer
where INCOME BETWEEN 300 AND 600;

------------------------------------------------------------------------------------------------------------

Part3:
The function is used to format that INCOME is format function.
The syntax of the format function format(number, decimalPlaces)
number is the either decimal or double value to be formatted.
decimalPlaces is number of decimals to format the number.

Related Questions

Navigate

• Browse (All)
• Browse R
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.