Hour 11, Restructuring the Appearance of Data

Team-Fly

---

 

	Sams Teach Yourself SQL in 24 Hours, Third Edition By Ronald R. Plew, Ryan K. Stephens
	Table of Contents
	Appendix C.  Answers to Quizzes and Exercises

---

Hour 11, "Restructuring the Appearance of Data"

Quiz Answers

a	Used to select a portion of a character string
a	SUBSTR
b	Used to trim characters from either the right or left of a string
b	LTRIM/RTRIM
c	Used to change all letters to lowercase
c	LOWER
d	Used to find the length of a string
d	LENGTH
e	Used to combine strings
e	CONCATENATION (CONCATENATION is the same as .)

1:	True or false: Using functions in a select statement to restructure the appearance of data in output will also affect the way the data is stored in the database.
A1:	True.
2:	True or false: The outermost function is always resolved first when functions are embedded within other functions in a query.
A2:	False. The innermost function is always resolved first when embedding functions within one another.

Exercise Answers

1:	Type the following code at the mysql> prompt to concatenate each employee's last name and first name : SELECT CONCAT(LAST_NAME, ', ', FIRST_NAME) FROM EMPLOYEE_TBL;
2:	Type the following code to print each employee's concatenated name and their area code: SELECT CONCAT(LAST_NAME, ', ', FIRST_NAME), SUBSTRING(PHONE, 1, 3) FROM EMPLOYEE_TBL;
3:	Write a SQL statement that lists employee emails. Email is not a stored column. The email for each employee should be as follows : [e-mail] For example, John Smith's would be [e-mail].
4:	Write a SQL statement that lists employee emails. Email is not a stored column. The email for each employee should be as follows: [e-mail] For example, John Smith's would be [e-mail].
A4:	SELECT CONCAT(SUBSTRING(FIRST_NAME,1,1), LAST_NAME, '@PERPTECH.COM') FROM EMPLOYEE_TBL;
5:	Write a SQL statement that lists each employee's name, employee ID, and phone number in the following formats: NAME = SMITH, JOHN EMP_ID = 999-99-9999 PHONE = [phone]
A5:	NAME = SMITH, JOHN PHONE = [phone] SELECT CONCAT(LAST_NAME, ', ', FIRST_NAME), CONCAT('(',SUBSTRING(PHONE,1,3),')',SUBSTRING(PHONE,4,3),'-', SUBSTRING(PHONE,7,4) FROM EMPLOYEE_TBL;

---

Team-Fly

Top