## Solutions Chapter 3: Character and Number Functions Test Your Thinking

1. Write the SELECT statement that returns the following output. Be sure to use spaces and punctuation exactly as you see them. (Use the SQL*Plus commands SET FEEDBACK OFF and SET HEADING OFF to turn off the number of rows displayed at the end of the statement and to turn off the column headings. Be sure to reset these options to their defaults when you are done. For more explanations on SQL*Plus commands, refer to Appen-dix C, "SQL*Plus Command Reference.")

```Instructor: R. Chow...... Phone: 212-555-1212
Instructor: M. Frantzen.. Phone: 212-555-1212
Instructor: F. Hanks..... Phone: 212-555-1212
Instructor: C. Lowry..... Phone: 212-555-1212
Instructor: A. Morris.... Phone: 212-555-1212
Instructor: G. Pertez.... Phone: 212-555-1212
Instructor: N. Schorin... Phone: 212-555-1212
Instructor: T. Smythe.... Phone: 212-555-1212
Instructor: I. Willig.... Phone: 212-555-1212
Instructor: T. Wojick.... Phone: 212-555-1212
```

Solution:

```SET FEEDBACK OFF
SET FEEDBACK OFF
||'. '||last_name, 25,'.'),
'Phone: '|| SUBSTR(phone,1,3)||'-'
||SUBSTR(phone, 4,3)||'-'||SUBSTR(phone, 7)
FROM instructor
ORDER BY last_name
/
SET FEEDBACK ON
```
2. Rewrite the following query to replace all occurrences of the string 'Unix' with 'Linux'.

```SELECT 'I develop software on the Unix platform'
FROM dual
```

Solution:

```SELECT REPLACE('I develop software on the Unix platform',
'Unix', 'Linux')
FROM dual
REPLACE('IDEVELOPSOFTWAREONTHEUNIXPLATFO
----------------------------------------
I develop software on the Linux platform

1 row selected.
```
3. Determine which student does not have the first letter of her or his last name capitalized. Show the STUDENT_ID and LAST_NAME columns.

Solution:

```SELECT student_id, last_name
FROM student
WHERE SUBSTR(last_name,1,1) = SUBSTR(LOWER(last_name),1,1)
STUDENT_ID LAST_NAME
---------- -------------
206 annunziato

1 row selected.
```
4. Check if any of the phone numbers in the INSTRUCTOR table have been entered in the (###)###-#### format.

Solution:

```SELECT phone
FROM instructor
WHERE TRANSLATE(
phone, '0123456789',
'##########') = '(###)###-####'

no rows selected
```

You can optionally include an extra space after the '9', which will ignore any extra spaces added to the phone number. For example, numbers in the format (212) 555-1111 and (212)555-1111 would be listed in the result set. In the case of the INSTRUCTOR table, all the phone numbers do not follow this format, therefore no rows are shown in the output.

```SELECT phone, instructor_id
FROM instructor
WHERE TRANSLATE(
phone, '0123456789 ',
'##########') = '(###)###-####'

no rows selected
```
5. Explain the functionality of the following query:

```SELECT section_id, capacity,
CASE WHEN MOD(capacity, 2) <> 0 THEN capacity +1
END "Even Number"
FROM section
WHERE section_id IN (101, 146, 147)
```

Solution: The query shows for sections 101, 146, and 147 the SECTION_ID, CAPACITY and even-numbered capacity. If a value in the capacity is ODD as determined with the MOD function, the value is incremented by one.

```SECTION_ID   CAPACITY Even Number
---------- ---------- -----------
101         10
146         25          26
147         15          16

3 rows selected.
```