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)

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
*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)
Spellchecker

Explanation / Answer

1. Following is the SQL query that lists each customer name and their income for customers with a balance between 300 and 600:

Query to copy:

SELECT LastName||' '||SUBSTR(FirstName,1,1)||'.' Name,

'$'||to_char(Income,'9,999,999.00') Income

FROM tblCustomer

WHERE balance BETWEEN 300 AND 600;

2.

Following are the rows that will be returned from this query:

3.

The function that is used to format the income column is TO_CHAR. The function is used to convert a number to string. It is used to format the number so that commas are inserted where required.

Related Questions

Navigate

• Browse (All)
• Browse R
• Subjects

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