Solution:
SELECT section_id, TO_CHAR(start_date_time, 'HH24:MI')
FROM section
WHERE TO_CHAR(start_date_time, 'HH24:MI') ='10:30'
SECTION_ID TO_CH
---------- -----
85 10:30
95 10:30
104 10:30
109 10:30
116 10:30
122 10:30
6 rows selected.
DAY SECTION_ID --- ---------- Mon 82 Tue 144 Wed 107 3 rows selected.
Solution:
SELECT TO_CHAR(start_date_time, 'D') day, section_id FROM section WHERE section_id IN (82, 144, 107) ORDER BY to_char(start_date_time, 'D')
Select the distinct course costs of all the courses. If the course cost is unknown, substitute a zero. Format the output with a leading $ sign, and separate the thousands with a comma. Display two digits after the decimal point. The query's output should look like the following result:
COST
-----------
$0.00
$1,095.00
$1,195.00
$1,595.00
4 rows selected.
Solution:
SELECT DISTINCT TO_CHAR(NVL(cost, 0), '$99,990.99') cost FROM course
or:
SELECT DISTINCT TO_CHAR(COALESCE(cost, 0), '$99,990.99') cost FROM course
What, if anything, is wrong with the following SQL statement?
SELECT zip + 100 FROM zipcode
Solution: The query executes, but doesn't make sense because you don't do calculations on the ZIP column, that's one of the reasons why the ZIP column in VARCHAR2 datatype format and it stores leading zeros. Additionally, any calculation should not rely on an implicit conversion; it is better to use the TO_NUMBER function.
For the students enrolled on January 30, 1999, display the columns STUDENT_ID and ENROLL_DATE.
Solution: There are many possible solutions, here are just a few listed:
SELECT student_id, enroll_date
FROM enrollment
WHERE enroll_date> = TO_DATE('30-JAN-1999', 'DD-MON-YYYY')
AND enroll_date < TO_DATE('31-JAN-1999', 'DD-MON-YYYY')
or:
SELECT student_id, enroll_date FROM enrollment WHERE enroll_date >= DATE '1999-01-30' AND enroll_date < DATE '1999-01-31'
or:
SELECT student_id, enroll_date
FROM enrollment
WHERE TRUNC(enroll_date) = TO_DATE('30-JAN-1999', 'DD-MON-YYYY')
Resulting output:
STUDENT_ID ENROLL_DA
---------- ---------
102 30-JAN-99
102 30-JAN-99
103 30-JAN-99
104 30-JAN-99
105 30-JAN-99
106 30-JAN-99
106 30-JAN-99
107 30-JAN-99
108 30-JAN-99
109 30-JAN-99
109 30-JAN-99
11 rows selected.
Execute the following SQL statements. Explain the individual statements.
SELECT SESSIONTIMEZONE, CURRENT_TIMESTAMP FROM dual; ALTER SESSION SET TIME_ZONE = '-8:00'; SELECT SESSIONTIMEZONE, CURRENT_TIMESTAMP FROM dual; ALTER SESSION SET TIME_ZONE = '-5:00';
Solution:
SELECT SESSIONTIMEZONE, CURRENT_TIMESTAMP
FROM dual
SESSIONTIMEZONE CURRENT_TIMESTAMP
-------------------- -----------------------------------
-05:00 02-APR-02 03.27.27.000000 PM -05:00
1 row selected.
Explanation: The SESSIONTIMEZONE function shows the current session's time zone displacement value expressed in hours and minutes from the UTC. In this case it's 5 hours before UTC. The CURRENT_TIMESTAMP function returns the current local date, time, fractional seconds, and the time zone displacement value. Note that your results will vary in terms of date, time, and perhaps time zone displacement values.
ALTER SESSION SET TIME_ZONE = '-8:00';
Session altered.
Explanation: The ALTER SESSION statement changes the time zone displacement to 8 hours before UTC, thus simulating west coast PST time.
SELECT SESSIONTIMEZONE, CURRENT_TIMESTAMP
FROM dual
SESSIONTIMEZONE CURRENT_TIMESTAMP
-------------------- -----------------------------------
-08:00 02-APR-02 12.27.41.000000 PM -08:00
1 row selected.
Explanation: When re-executing the SESSIONTIMEZONE and CURRENT_TIMESTAMP function, you notice that the session's time zone now change to −8:00 hours before UTC. In addition the local time is now displayed in local PST time.
ALTER SESSION SET TIME_ZONE = '-5:00';
Session altered.
Explanation: The ALTER SESSION statement changes the time zone displacement back to the original time. If you had a different time zone than EST, change it back to the value that the original SESSIONTIMEZONE function returned.
Conclusion: CURRENT_TIMESTAMP function is sensitive to the session time zone. Note that the ALTER SESSION statement is only effective for the duration of the current session. As soon as you log out the values are reset to the default.