Sams Teach Yourself SQL in 24 Hours, Third Edition By Ronald R. Plew, Ryan K. Stephens
Table of Contents
Hour 11. Restructuring the Appearance of Data
The following workshop is composed of a series of quiz questions and practical exercises. The quiz questions are designed to test your overall understanding of the current material. The practical exercises are intended to afford you the opportunity to apply the concepts discussed during the current hour, as well as build upon the knowledge acquired in previous hours of study. Please take time to complete the quiz questions and exercises before continuing. Refer to Appendix C,"Answers to Quizzes and Exercises," for answers.
Quiz
Match the descriptions with the possible functions.
Descriptions
Functions
a
Used to select a portion of a character string
b
Used to trim characters from either the right or left of a string
c
Used to change all letters to lowercase
d
Used to find the length of a string
e
Used to combine strings
RPAD
LPAD
LENGTH
UPPER
LTRIM
RTRIM
LOWER
SUBSTR
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.
2:
True or false: The outermost function is always resolved first when functions are embedded within other functions in a query.
Exercises
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 :
FIRST.LAST@PERPTECH.COM
For example, John Smith's would be JOHN.SMITH@PERPTECH.COM.
4:
Write a SQL statement that lists employee emails. Email is not a stored column. The email for each employee should be as follows:
FIRSTINITIAL.LAST@PERPTECH.COM
For example, John Smith's would be JSMITH@PERPTECH.COM.
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 = (999)999-9999