Oracle Sql Statements

Front 1

What are the "arithmetic comparison conditions" for WHERE statements

Back 1

Equal to =
Greater than >
Greater than or equal to >=
Less than <
Less than or eaqul to <=
not equal to <>

Front 2

What are the four main "comparison conditions" for WHERE statements

Back 2

BETWEEN..AND.. (inclusive)
IN(set) Match any in (1,2,..)
LIKE Match a char. 'pattern%'
IS NULL -is a null value

Front 3

What are three "Logical Conditions"

Back 3

AND Returns true if both are
OR Returns true if either are
NOT Returns true if false

Front 4

What are the rules of precedence

Back 4

1 Arithmetic
2 Concatenation
3 Comparison conditions
4 IS [NOT] NULL, LIKE, IN
5 [NOT] BETWEEN
6 NOT
7 AND
8 OR
override () parentheses

Front 5

What are the five types of single row functions?

Back 5

General
Character
Number
Date
Conversion

Front 6

What are the two sub catagories of character functions?

Back 6

Case-manipulation functions and Character manipulation functions

Front 7

Name the character -manipulation functions

Back 7

COMCAT SUBSTR LENGTH INSTR LPAD RPAD TRIM REPLACE

Front 8

Name the Case-manipulation functions

Back 8

LOWER UPPER INITCAP

Front 9

What does the LOWER character function do?

Back 9

LOWER(column/expression) converts alpha characters values to lower case

Front 10

What does the UPPER character function do?

Back 10

UPPER(column/expression) Converts alpha characters to upper case

Front 11

What does the INITCAP character function do?

Back 11

INITCAP(column/expression) Converts alpha characters to upper case for first letter of the word.

Front 12

What does the SUBSTR character function do?

Back 12

SUBSTR(column/expression, m,n) Returns characters from character value starting at m for n characters long

Front 13

What does the CONCAT character function do?

Back 13

CONCAT(column1/expression1, Column2/expression2)brings together value1 with value2

Front 14

What does the LENGTH character function do?

Back 14

LENGTH(column/expression)Returns the number of characters in the expression

Front 15

What does the INSTR character function do?

Back 15

INSTR(column/expression, 'string', m, n)Returns the numeric position of named string where m is start position and n is occurance of string where default is 1

Front 16

What does the character function LPAD and RPAD do?

Back 16

LPAD(column/expression, n, 'string')Pads the character value right-jusstified to a total width of n character positions. RPAD does the same to the left.

Front 17

What does the character function TRIM do?

Back 17

TRIM(leading/trailing/both,
'trim_character' FROM 'trim_source')
Trim heading or trailing characters from character string.

Front 18

What does the character function REPLACE do?

Back 18

REPLACE(text, search_string, replacement_string)
Searches a text expression for a character string and replaces if found.

Front 19

What are the number functions from the original five single row functions

Back 19

ROUND, TRUNC, MOD

Front 20

What does the ROUND character function do?

Back 20

ROUND(column/expression, n)Rounds the cloumn, expression or value to n decimal places. neg goes left

Front 21

What does the TRUNC number function do?

Back 21

TRUNC(column/expression, n)
truncates the column, expression, value to n decimal places. neg goes left

Front 22

What does the MOD number function do?

Back 22

MOD(value1, Value2)
Returns the remander of value1 divided by value2

Front 23

How does Oracle store dates?

Back 23

Century,year,month,day,hours,minute,seconds

Front 24

Oracle stores date from when to when?

Back 24

01-JAN-(4712 BC) to 31-DEC-(9999 AD)
DD-MON-YY and DD-MON-RR are not the same in centuries.

Front 25

What is SYSDATE

Back 25

SYSDATE is a date function that returns the current database date and time

Front 26

When using the SYSDATE function what "dummy" table should be used to return a value?

Back 26

FROM DUAL;

Front 27

What arithmetic operatiors can be used with date functions?

Back 27

date + number adds days
date - number subtracts days
date - date subtracts one day from another
date + number/24 adds a number of hours to a date

Front 28

List the six date functions

Back 28

MONTHS_BETWEEN, ADD_MONTHS, NEXT_DAY, ROUND, TRUNC

Front 29

What does the MONTHS_BETWEEN function do?

Back 29

MONTHS_BETWEEN(date1, date2) finds the number of months between dates. either negative or positive

Front 30

What does the ADD_MONTHS function do?

Back 30

ADD_MONTHS(date, n) adds n number of calendar months to date. n could be negative

Front 31

What do the NEXT_DAY date function do?

Back 31

NEXT_DAY(date, 'char')finds the date of the next specified day when 'char' is a number 1-7 or a 'Friday' character string

Front 32

What do the LAST_DAY date function do?

Back 32

LAST_DAY(date)finds the day of the last day of the month that is in date.

Front 33

What do the ROUND date function do?

Back 33

ROUND(date, 'fmt') fmt=format
Returns the date rounded to the unit specified in the fmt model

Front 34

What do the TRUNC date function do?

Back 34

TRUNC(date, 'fmt') fmt=format
Returns the date with the time portion truncated to the unit specified in the fmt model. fmt is omitted then defaults to nearest day

Front 35

What are the two methods by which Oracle can use conversion functions to convert data types?

Back 35

Implicit (by Oracle) and Explicit (by the user)

Front 36

What are four Implicit Data Type Conversion functions?

Back 36

VARCHAR2 or CHAR to NUMBER
VARCHAR2 or CHAR to DATE
NUMBER to VARCHAR2
DATE to VARCHAR2

Front 37

What are three Explicit date type conversions functions?

Back 37

TO_CHAR
TO_NUMBER
TO_DATE

Front 38

What does the TO_CHAR explicit data type conversion do?

Back 38

TO_CHAR(number|date, fmt, nlsparams)
Converts a number or date value to a VARCHAR2 character string with the format model fmt

Front 39

What does the TO_NUMBER explicit data type conversion do?

Back 39

TO_NUMBER(char, fmt, nlsparams)
Converts a character string containing digits to a number in the format specified by the optional format model fmt

Front 40

What does the TO_DATE explicit data type conversion do?

Back 40

TO_DATE(char, fmt, nlsparams)
Converts a character string representing a date to a date value according to the fmt specified. no fmt then default is DD-MON-YY

Front 41

What does the NLSPArams parameter specify?

Back 41

It specifies the decimal character, group separator, local currancy symbol and international currency symbol. For dates is specifies the language for abbreviations, etc.

Front 42

What are some common date formats?

Back 42

YYYY, MM, MONTH, DAY, WW (week), DY (three letter abv.), MON

Front 43

What are some common time formats?

Back 43

AM, PM, A.M., P.M., HH, HH12 (12 hour), HH24 (twenty four hour), MI (minute), SS (seconds), SSSSS (seconds past midnight)

Front 44

What are some number suffixes that spell out numbers?

Back 44

TH ex. DDTH for TH on number
SP ex. DDSP for four (spelled out)
SPTH THSP for FOURTH

Front 45

Can punctuaation (/,.) and quated strings 'of the' be used in the TO_CHAR clause?

Back 45

YES, these are valid formats.

Front 46

What are six standard format values for numbers when using TO_CHAR?

Back 46

9 will represent a number
0 forces a display zero
$ floating $ placement
L uses local currency symbol
. displays a decimal point
, displays a thousand symbol

Front 47

What are some of the additional format model elements for number functions? looking for 5

Back 47

MI 99999MI 1234-
PR 99999PR <1234>
EEEE 99.9999EEEE 1.234E+03
V 9999V99 123400
B B9999.99 1234

Front 48

What command is used to retrive the database time zone?

Back 48

SELECT DBTIMEZONE
FROM DUAL

Front 49

What is SESSIONTIMEZONE?

Back 49

It is the time zone of the location from where the user has logged on to the database.

Front 50

What are some time zone Keywords?

Back 50

ALTER SESSION
SET TIME ZONE = '-300';
TZ_OFFSET('CANADA/PACIFIC')
FROM V$TIMEZONE_NAMES;
CURRENT_DATE sensitive to the seesion time zone where as SYSDATE is not sensitive
CURRENT_TIMESTAMP(percision)
LOCALTIMESTAMP

Front 51

What fields can the extract function use?

Back 51

any of the fields like hour, minute, day, month

Front 52

What is the extract function syntax for data and time fields.

Back 52

SELECT EXTRACT (YEAR< MONTH< DAY,HOUR, MINUTE,SECOND, TIMEZONE_HOUR, TIMEZONE_MINUTE, TIMEZONE_REGION, TIMEZONE_ABBR
FROM datetime_value_expression, interval_value_expression)
FROM table name

Front 53

What function is used to convert a string to a value of the time and date value type.

Back 53

TO_TIMESTAMP('char', fmt, 'nlsparam')

Front 54

What function is used to store data and time value with the time zone in a chartacter format

Back 54

TO_TIMESTAMP_TZ('char', fmt, 'nlsparam')

note TZH = timezonehour
TZM = timezoneminute

Front 55

What function is used to convert a TIMESTAMP value to a TIMESTAMP WITH TIME ZONE value?

Back 55

FROM_TZ(TIMESTAMP 'timestamp_value', 'time_zone_value')

note TZR = timezone region
TZD = timezone region with daylight savings

Front 56

If adding a number of years or months to a date value they may be store in character format. What is the function to convert a character value to an INTERVAL YEAR TO MONTH datatype?

Back 56

TO_YMINTERVAL('CHAR')

Front 57

What function is used to convert a NULL value to an actual value?

Back 57

NVL (expr1, expr2)

expr1 is the source value
expr2 is the target value

Front 58

When using the NVL function what datatypes can be used?

Back 58

any datatype. If they are different Oracle converts it to expression1's datatype and returns the value in expression1's datatype.

Front 59

What is the function to evalute weather a column is NULL and return a value based on this result

Back 59

NVL2(expr1, expr2, expr3)
expr1 is the source value
expr2 is returned if not null
expr3 is returned if null

Front 60

what are the data type used in NVL2?

Back 60

NVL2 can use any data type except LONG. If expr2 and expr3 are different types then expr3 is converted to expr2. The data type returned is always the same as expr2's datatype.

Front 61

What function is used to compare two expressions and return a NULL value or an expression

Back 61

NULLIF(expr1,expr2)

both expr are = then returns a NULL. If not equal then returns expr1. Hence you can not specify 'NULL' for first expression

Front 62

What function is used to compare multiple expressions and return a value based on the result?

Back 62

COALESCE(expr1,expr2,expr3...exprn)
expr1 returned if not NULL
expr2 returned if expr1 NULL
and expr2 not NULL
exprN returned if preceeding expr(s) are NULL

Front 63

What are the two methods for using IF-THEN-ELSE logic within a SQL statement?

Back 63

CASE expression and the DECODE function

Front 64

What is the syntax of the case expression?

Back 64

Case expr WHEN comparison_expr1 THEN return_expr1 [WHEN comparision_expr2 THEN return_expr2 WHEN comparison_exprn THEN return_exprn ELSE else_expr]

END

Front 65

When using a CASE expression all data types must be same and of what type?

Back 65

CHAR, VARCHAR2, NCHAR, NVARCHAR2

Front 66

What is the single row function that can do IF-THEN-ELSE queries

Back 66

DECODE(column_name, search1, result1, [search2, result2,]
[searchn, resultn,]
[default])
Default represent the final ELSE condition (no default then NULL is returned)

Front 67

What is it called when function are put inside functions?

Back 67

Nested functions

Front 68

TRUE or False. You evaluate nested functions starting with the innermostlevel and working toward the outermost level?

Back 68

True