chapter 5.1

Question 1

how does oracle store dates internally

Answer 1

stores dates as numbers

Question 2

date information is displayed as _______

Answer 2

DD-Mon-YYYY

Question 3

what are the 2 types of data conversion

Answer 3

implicit data type
explicit data type

Question 4

example of implicit data conversion

Answer 4

oracle server can automatically convert char data to NUMBER and DATE data types

Question 5

what is 1 way to ensure reliability in sql statements

Answer 5

it is best to explicitly make data type conversions to ensure reliability in SQL statements

Question 6

what is example of explicit data types

Answer 6

character data type to number datatype using TO_NUMBER function

Number data type to character data type using TO_CHAR

Question 7

syntax for to_char when converting date to character data

Answer 7

TO_CHAR(date column name, ‘format model you specify’)

Question 8

how do you spell out a number using format model

Answer 8

use sp

Question 9

how to have the number appear as an ordinal using format model

Answer 9

use th

Question 10

why use fm element

Answer 10

remove padded blanks or remove leading zeroes from the output

Question 11

how to specify the numeric day of year

Answer 11

DDD

Question 12

how to specify the numeric day of the month

Answer 12

DD

Question 13

how to specify the numeric day of the week

Answer 13

D

Question 14

how to specify the hours

Answer 14

HH

Question 15

how to specify the minutes

Answer 15

MI

Question 16

how to specify the seconds

Answer 16

SS

Question 17

how to get 24 hour time with am/pm

Answer 17

HH24:MI:SS AM

Question 18

sql format to convert number to character data

Answer 18

TO_CHAR(number, ‘format model ‘)

Question 19

how do you want to format numbers in money format ($xx,xxx)

Answer 19

SELECT TO_CHAR(SALARY, ‘$99,999’) AS “SALARY”
FROM EMPLOYEE;

Question 20

how do you perform character conversion to number

Answer 20

USE TO_NUMBER(CHARACTER STRING, ‘format model’)

the format model is optional but should be included if character string contains any characters other than numbers

Question 21

what happens if in the TO_NUMBER function the column value has 4 values but the format model has only 3 characters

Answer 21

invalid number error

Question 22

what function to convert a character string to a date format

Answer 22

to_date(‘character string’, ‘format model ‘)

Question 23

what does the format model tells the server in the to_date function

Answer 23

tells the server what the character string looks like

Question 24

what does the fx (format exact) modifier specify

Answer 24

specifies the exact matching for the character argument and the date format model

Question 25

SELECT TO_DATE(‘MAY10, 1989’, ‘fxMonDD, YYYY’) as “convert” from dual

Answer 25

10-May-1989

Question 26

some legacy databases may still use the ______ year format

Answer 26

2 digit year (YY)

Question 27

result of

SELECT TO_DATE(‘27-Oct-95’, ‘DD-Mon-YY’)
AS “DATE”
FROM DUAL;

Answer 27

27-Oct-2095

2 digit year is interpreted as 2095, however, this may not be what was intended.

Question 28

what happens if the date format is specified with RR format

Answer 28

if the 2 digits of the current year are 0-49 and the specified 2 digit year is 0-49 then the return date is in current century

if the 2 digits of the current year are 0-49 and the specified 2 digit year is 50-99 then the return date is in century before the current one

if the 2 digits of the current year are 50-99 and the specified 2 digit year is 0-49 then the return date is in century after the current one

if the 2 digits of the current year are 50-99 and the specified 2 digit year is 50-99 then the return date is in current century