Write a query against the ADDRESS table to select a list of names and phone numbers.

Q. Write a query against the ADDRESS table to select a list of names and phone numbers. The output should match these requirements:

Table ADDRESS (contains following eight columns)

 

Last Name	VARCHAR (25)
FirstName	VARCHAR (25)
Street	VARCHAR (50)
City	VARCHAR (25)
State	CHAR (2)
Zip	NUMBER
Phone	VARCHAR(12)
Ext	VARCHAR(5)

• The name column should contain both the first and last names with a blank space between them.

Use the string concatenation.

• The second column will contain the phone number.

• Phone number should be in the format (999) 999-9999. Use the SUBSTR function and CONCAT.

• Order the query by last name then first name.

 

Answer =

 

SELECT CONCAT(FirstName , ‘ ’, LastName), CONCAT (‘(’, SUBSTR(Phone, 1, 3), ‘) ’, SUBSTR(Phone, 4, 3), ‘-’, SUBSTR(Phone, 7)) FROM ADDRESS ORDER BY LastName, FirstName;