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
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.
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.
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
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.