Exam Review Flashcards
makes it easier to query fields where data isn’t always sorted in a standard format
case-manipulation functions
3 purposes of column aliases (other than display)
- enhances formatting
- calculated columns
- concatenated columns
does CONCAT allow character strings?
no
____ refer to pattern matching in a search string
wildcards
general function: displays replacement values as specified
DECODE
date function: displays the last day of the month specified
LAST_DAY
4 math operators usable in calculated columns
add, subtract, multiply, divide
3 conversion functions in SQL that allow conversion from one data type to another
- TO_CHAR
- TO_NUMBER
- TO_DATE
can be used to force priority to the expression contained within them
parentheses
t or f. always use the ROUND function for age or years worked
f, always use TRUNC
if the length is not indicated in SUBSTR, oracle assumes ____
to the end of string
NULL means the value is one of these 4 things
- unassigned
- unavailable
- inapplicable
- unknown
order of operations: ___ and ___ are processed prior to ___ and ___
multiplication/division/addition/subtraction
similar to find and replace function in word
REPLACE
for TO_NUMBER dates must be changed into a ____ before applying TO_NUMBER
character string
in NULLIF(expr1, expr2), what is returned if expr1 <> expr2?
expr1 value is returned
t or f. data can be sorted by multiple columns
t
syntax that subtracts a number of days to a date
date - number
in the function ROUND(column, p), what does p stand for?
p = position to which the data should be rounded
dates within the syntax can be a date ___ or a hard-coded date ___
column, value
what do v, r, and d stand for in DECODE(column, v, r, d)
v = value r = replacement value d = default
not in text: a format element for use with dates that prevents zero-padding of numeric elements and extra space when using commas
fm
comparison operator: used to search for values within a specified list of values
IN
comparison operator: used for searches with a ‘wildcard’ when you do not know the exact value
LIKE
an ___ represents one value
underscore (_)
date function: displays the date of the next specified day of the week
NEXT_DAY
for MONTHS_BETWEEN(date 1, date 2): if date 2 is earlier than date 1, the result is ___
positive
t or f. NULL is not zero, nor can it be equal or unequal to any value
t
t or f. in LPAD/RPAD, characters require delimiters while numbers do not
t
negative numbers can be used as p in a SUBSTR function to indicate a start from the ____
end of the string
Functions that accept numeric data and return numeric results
number functions
in LPAD/RPAD function:
LPAD(columnName, l, s) what do l and s stand for?
l = length of character string after padding s = symbol to use as padding
combination of partial data values (characters or numbers, etc) plus one or more wildcards
search strings
for the TRIM function, a ____ from the beginning and/or end of a value must be indicated
character string
in the WHERE clause, single row functions state a ____ or ____ values returned
condition, restrict
general function: returns a null value if 2 expressions are equal
NULLIF
character manipulation function: returns a portion of the data value
SUBSTR
concatenation operator can join data from 2 or more columns with these 3 things
- character string(s)
- constant value
- arithmetic expression
ORDER BY clause can sort by these 3 column elements
column
- name
- alias
- number
comparison operator: used to ensure a value is within the field
IS NOT NULL
comparison operator: opposite of LIKE
NOT LIKE
column number is assigned based on ____ within the ____ clause
column location, SELECT
changes the column name displayed
column alias
t or f. character manipulation functions in the WHERE clause does not change how the information looks
t
conditions that can be in the where clause
columns, expressions, constants and comparison operators
conversion function: converts a character string to a DATE
TO_DATE
WHERE clause operator: limits number of rows displayed by a query
row restriction
character manipulation functions: single-row function that does the same thing as the concatenation pipe
CONCAT
conversion function: converts a character string with digits to a NUMBER
TO_NUMBER
when p is positive in the ROUND function, the data is rounded to the __ side of the decimal
right
used to modify the display of query results
character manipulation functions
symbols used for column alias
” “
4 types of columns you can use column aliases on
- underscored columns
- calculated columns
- inconsistent case columns
- concatenated columns
t or f. output matches the alias case
t
removes a specific string of characters from the beginning or end of a data value
TRIM
t or f. date, character, and number types can be used with the NVL function, but the data types must match
t
comparison operator: value within a specific inclusive range
BETWEEN…AND
clause: can be used to sort rows returned by a query
ORDER BY
comparison operator: used to check for NULL values in a field
IS NULL
for NEXT_DAY(date, value), the value in the syntax can be one of these 2 things`
- a number representing the day (1 for Sun, 2 for Mon…)
2. a character string such as ‘Wednesday’
can specify whether to sort ascending or descending
ASC or DESC
for MONTHS_BETWEEN(date 1, date 2): if date 1 is earlier than date 2, the result is ___
negative
functions that are used to fill in the area on the left or right of a character string
LPAD and RPAD
syntax that adds or subtracts a number of hours to a date
date +/- numberofhours/24
concatenation operator
|| also known as a ‘pipe’
t or f. TRIM can be used on its own or as LTRIM or RTRIM
t
in the SELECT clause, single row functions change the ____
display
in NVL(column, n), n represents the ___
replacement value
fm only needs to be used in the ____ within one ____
first instance, TO_CHAR
in the SUBSTR syntax SUBSTR(columnName, p, l), what do p and l stand for?
p = starting position l = length of string returned
WHERE ROWNUM or =?
<
1 constraint for column aliases
length
math operators can be used in any SQL statement clause except the ____ clause
FROM
t or f. case does not matter when it comes to search strings
f
conversion function: converts a NUMBER or DATE to character string (VARCHAR2)
TO_CHAR
date function: calculates number of months between 2 specified dates
MONTHS_BETWEEN
syntax that subtracts one date from another and displays results in days
date - date
t or f. column aliases are a permanent change to the column name
f
conversion function most often used with TO_CHAR (date) to perform calculations
TO_NUMBER
column alias change the ____ of the column output
output display
t or f. the IN condition can be used with any data type (date, number, VARCHAR, etc.)
t
number function that cuts off number values at the decimal place
TRUNC
general function: changes display of a null value
NVL
character manipulation functions: determines the number of characters in a string
LENGTH
5 single row functions
- character
- number
- date
- conversion
- general
‘p’ if you want to round to the whole number
0
mathematical expressions that can be comprised of column names, constant numeric values and mathematical operators
calculated columns
useful when the user is uncertain of the case of data values
character manipulation functions
in CASE, a ___ is necessary
column alias
3 case manipulation functions
- LOWER
- UPPER
- INITCAP
clause that contains a condition that must be met
WHERE clause
the state of being linked together
concatenation
‘p’ if you want to round to the nearest 10
-1
fm stands for ____
format model
comparison operator: value not within specific inclusive range
NOT BETWEEN…AND
general function: allows various actions to be applied within a single IF-THEN-ELSE logic statement; used extensively in industry
CASE
in BETWEEN AND, the range contains both an upper and lower limit with the ____ limit specified first
lower
what are the 5 single-row functions
- character
- number
- date
- conversion
- general
by default, the ORDER BY clause will sort in ____ order
ascending
WHERE clause: if condition is false, rows ____ be returned and if condition is true, rows ____ be returned
won’t, will
in NULLIF(expr1, expr2), what is returned if expr1 = expr2?
null
when p is negative in the ROUND function, the data is rounded to the __ side of the decimal
left
t or f. if used, the ORDER BY clause must be the first clause of the statement
f
in industry, wildcards are often used with ____
diagnosis or intervention codes
CONCAT can be used in ____ clause
WHERE
general function: simpler version of CASE
DECODE
conversion function used to get around oracle’s default date format (DD-MON-YY)
TO_DATE
a ___ represents multiple values
percentage sign (%)
comparison operator: used to search for values not within a specified list of values
NOT IN
date function: adds a specified number of months to the specified date
ADD_MONTHS
t or f. all single-row functions can be used in the WHERE clause as well as the SELECT clause
t
general function: evaluates expression(s) and displays replacement values as specified
CASE
what can’t be used in the WHERE clause
column aliases
‘p’ if you want to round to the nearest 10th
1
a query can have several conditions if ___ or ___ operators are used
AND/OR
syntax to add a number of days to a date
date + number
2 most frequently used number functions
- ROUND
2. TRUNC
