• Shuffle
    Toggle On
    Toggle Off
  • Alphabetize
    Toggle On
    Toggle Off
  • Front First
    Toggle On
    Toggle Off
  • Both Sides
    Toggle On
    Toggle Off
  • Read
    Toggle On
    Toggle Off
Reading...
Front

How to study your flashcards.

Right/Left arrow keys: Navigate between flashcards.right arrow keyleft arrow key

Up/Down arrow keys: Flip the card between the front and back.down keyup key

H key: Show hint (3rd side).h key

A key: Read text to speech.a key

image

Play button

image

Play button

image

Progress

1/68

Click to flip

68 Cards in this Set

  • Front
  • Back
What are the "arithmetic comparison conditions" for WHERE statements
Equal to =
Greater than >
Greater than or equal to >=
Less than <
Less than or eaqul to <=
not equal to <>
What are the four main "comparison conditions" for WHERE statements
BETWEEN..AND.. (inclusive)
IN(set) Match any in (1,2,..)
LIKE Match a char. 'pattern%'
IS NULL -is a null value
What are three "Logical Conditions"
AND Returns true if both are
OR Returns true if either are
NOT Returns true if false
What are the rules of precedence
1 Arithmetic
2 Concatenation
3 Comparison conditions
4 IS [NOT] NULL, LIKE, IN
5 [NOT] BETWEEN
6 NOT
7 AND
8 OR
override () parentheses
What are the five types of single row functions?
General
Character
Number
Date
Conversion
What are the two sub catagories of character functions?
Case-manipulation functions and Character manipulation functions
Name the character -manipulation functions
COMCAT SUBSTR LENGTH INSTR LPAD RPAD TRIM REPLACE
Name the Case-manipulation functions
LOWER UPPER INITCAP
What does the LOWER character function do?
LOWER(column/expression) converts alpha characters values to lower case
What does the UPPER character function do?
UPPER(column/expression) Converts alpha characters to upper case
What does the INITCAP character function do?
INITCAP(column/expression) Converts alpha characters to upper case for first letter of the word.
What does the SUBSTR character function do?
SUBSTR(column/expression, m,n) Returns characters from character value starting at m for n characters long
What does the CONCAT character function do?
CONCAT(column1/expression1, Column2/expression2)brings together value1 with value2
What does the LENGTH character function do?
LENGTH(column/expression)Returns the number of characters in the expression
What does the INSTR character function do?
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
What does the character function LPAD and RPAD do?
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.
What does the character function TRIM do?
TRIM(leading/trailing/both,
'trim_character' FROM 'trim_source')
Trim heading or trailing characters from character string.
What does the character function REPLACE do?
REPLACE(text, search_string, replacement_string)
Searches a text expression for a character string and replaces if found.
What are the number functions from the original five single row functions
ROUND, TRUNC, MOD
What does the ROUND character function do?
ROUND(column/expression, n)Rounds the cloumn, expression or value to n decimal places. neg goes left
What does the TRUNC number function do?
TRUNC(column/expression, n)
truncates the column, expression, value to n decimal places. neg goes left
What does the MOD number function do?
MOD(value1, Value2)
Returns the remander of value1 divided by value2
How does Oracle store dates?
Century,year,month,day,hours,minute,seconds
Oracle stores date from when to when?
01-JAN-(4712 BC) to 31-DEC-(9999 AD)
DD-MON-YY and DD-MON-RR are not the same in centuries.
What is SYSDATE
SYSDATE is a date function that returns the current database date and time
When using the SYSDATE function what "dummy" table should be used to return a value?
FROM DUAL;
What arithmetic operatiors can be used with date functions?
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
List the six date functions
MONTHS_BETWEEN, ADD_MONTHS, NEXT_DAY, ROUND, TRUNC
What does the MONTHS_BETWEEN function do?
MONTHS_BETWEEN(date1, date2) finds the number of months between dates. either negative or positive
What does the ADD_MONTHS function do?
ADD_MONTHS(date, n) adds n number of calendar months to date. n could be negative
What do the NEXT_DAY date function do?
NEXT_DAY(date, 'char')finds the date of the next specified day when 'char' is a number 1-7 or a 'Friday' character string
What do the LAST_DAY date function do?
LAST_DAY(date)finds the day of the last day of the month that is in date.
What do the ROUND date function do?
ROUND(date, 'fmt') fmt=format
Returns the date rounded to the unit specified in the fmt model
What do the TRUNC date function do?
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
What are the two methods by which Oracle can use conversion functions to convert data types?
Implicit (by Oracle) and Explicit (by the user)
What are four Implicit Data Type Conversion functions?
VARCHAR2 or CHAR to NUMBER
VARCHAR2 or CHAR to DATE
NUMBER to VARCHAR2
DATE to VARCHAR2
What are three Explicit date type conversions functions?
TO_CHAR
TO_NUMBER
TO_DATE
What does the TO_CHAR explicit data type conversion do?
TO_CHAR(number|date, fmt, nlsparams)
Converts a number or date value to a VARCHAR2 character string with the format model fmt
What does the TO_NUMBER explicit data type conversion do?
TO_NUMBER(char, fmt, nlsparams)
Converts a character string containing digits to a number in the format specified by the optional format model fmt
What does the TO_DATE explicit data type conversion do?
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
What does the NLSPArams parameter specify?
It specifies the decimal character, group separator, local currancy symbol and international currency symbol. For dates is specifies the language for abbreviations, etc.
What are some common date formats?
YYYY, MM, MONTH, DAY, WW (week), DY (three letter abv.), MON
What are some common time formats?
AM, PM, A.M., P.M., HH, HH12 (12 hour), HH24 (twenty four hour), MI (minute), SS (seconds), SSSSS (seconds past midnight)
What are some number suffixes that spell out numbers?
TH ex. DDTH for TH on number
SP ex. DDSP for four (spelled out)
SPTH THSP for FOURTH
Can punctuaation (/,.) and quated strings 'of the' be used in the TO_CHAR clause?
YES, these are valid formats.
What are six standard format values for numbers when using TO_CHAR?
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
What are some of the additional format model elements for number functions? looking for 5
MI 99999MI 1234-
PR 99999PR <1234>
EEEE 99.9999EEEE 1.234E+03
V 9999V99 123400
B B9999.99 1234
What command is used to retrive the database time zone?
SELECT DBTIMEZONE
FROM DUAL
What is SESSIONTIMEZONE?
It is the time zone of the location from where the user has logged on to the database.
What are some time zone Keywords?
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
What fields can the extract function use?
any of the fields like hour, minute, day, month
What is the extract function syntax for data and time fields.
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
What function is used to convert a string to a value of the time and date value type.
TO_TIMESTAMP('char', fmt, 'nlsparam')
What function is used to store data and time value with the time zone in a chartacter format
TO_TIMESTAMP_TZ('char', fmt, 'nlsparam')

note TZH = timezonehour
TZM = timezoneminute
What function is used to convert a TIMESTAMP value to a TIMESTAMP WITH TIME ZONE value?
FROM_TZ(TIMESTAMP 'timestamp_value', 'time_zone_value')

note TZR = timezone region
TZD = timezone region with daylight savings
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?
TO_YMINTERVAL('CHAR')
What function is used to convert a NULL value to an actual value?
NVL (expr1, expr2)

expr1 is the source value
expr2 is the target value
When using the NVL function what datatypes can be used?
any datatype. If they are different Oracle converts it to expression1's datatype and returns the value in expression1's datatype.
What is the function to evalute weather a column is NULL and return a value based on this result
NVL2(expr1, expr2, expr3)
expr1 is the source value
expr2 is returned if not null
expr3 is returned if null
what are the data type used in NVL2?
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.
What function is used to compare two expressions and return a NULL value or an expression
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
What function is used to compare multiple expressions and return a value based on the result?
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
What are the two methods for using IF-THEN-ELSE logic within a SQL statement?
CASE expression and the DECODE function
What is the syntax of the case expression?
Case expr WHEN comparison_expr1 THEN return_expr1 [WHEN comparision_expr2 THEN return_expr2 WHEN comparison_exprn THEN return_exprn ELSE else_expr]

END
When using a CASE expression all data types must be same and of what type?
CHAR, VARCHAR2, NCHAR, NVARCHAR2
What is the single row function that can do IF-THEN-ELSE queries
DECODE(column_name, search1, result1, [search2, result2,]
[searchn, resultn,]
[default])
Default represent the final ELSE condition (no default then NULL is returned)
What is it called when function are put inside functions?
Nested functions
TRUE or False. You evaluate nested functions starting with the innermostlevel and working toward the outermost level?
True