Skip over navigation to the main content.

Oracle SQL Interactive Workbook, 2nd Edition

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
SET HEADING OFF
SELECT RPAD('Instructor: '||SUBSTR(first_name,1,1)
       ||'. '||last_name, 25,'.'), 
       'Phone: '|| SUBSTR(phone,1,3)||'-'
       ||SUBSTR(phone, 4,3)||'-'||SUBSTR(phone, 7)
  FROM instructor
 ORDER BY last_name
/
SET HEADING ON
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.

Further Reading

Read Prentice Hall PTR to stay on the cutting edge of computer science and engineering.