Exam Review Flashcards
1
Q
makes it easier to query fields where data isn’t always sorted in a standard format
A
case-manipulation functions
2
Q
3 purposes of column aliases (other than display)
A
- enhances formatting
- calculated columns
- concatenated columns
3
Q
does CONCAT allow character strings?
A
no
4
Q
____ refer to pattern matching in a search string
A
wildcards
5
Q
general function: displays replacement values as specified
A
DECODE
6
Q
date function: displays the last day of the month specified
A
LAST_DAY
7
Q
4 math operators usable in calculated columns
A
add, subtract, multiply, divide
8
Q
3 conversion functions in SQL that allow conversion from one data type to another
A
- TO_CHAR
- TO_NUMBER
- TO_DATE
9
Q
can be used to force priority to the expression contained within them
A
parentheses
10
Q
t or f. always use the ROUND function for age or years worked
A
f, always use TRUNC
11
Q
if the length is not indicated in SUBSTR, oracle assumes ____
A
to the end of string
12
Q
NULL means the value is one of these 4 things
A
- unassigned
- unavailable
- inapplicable
- unknown
13
Q
order of operations: ___ and ___ are processed prior to ___ and ___
A
multiplication/division/addition/subtraction
14
Q
similar to find and replace function in word
A
REPLACE
15
Q
for TO_NUMBER dates must be changed into a ____ before applying TO_NUMBER
A
character string
16
Q
in NULLIF(expr1, expr2), what is returned if expr1 <> expr2?
A
expr1 value is returned
17
Q
t or f. data can be sorted by multiple columns
A
t
18
Q
syntax that subtracts a number of days to a date
A
date - number
19
Q
in the function ROUND(column, p), what does p stand for?
A
p = position to which the data should be rounded
20
Q
dates within the syntax can be a date ___ or a hard-coded date ___
A
column, value
21
Q
what do v, r, and d stand for in DECODE(column, v, r, d)
A
v = value r = replacement value d = default
22
Q
not in text: a format element for use with dates that prevents zero-padding of numeric elements and extra space when using commas
A
fm
23
Q
comparison operator: used to search for values within a specified list of values
A
IN
24
Q
comparison operator: used for searches with a ‘wildcard’ when you do not know the exact value
A
LIKE
25
an ___ represents one value
underscore (_)
26
date function: displays the date of the next specified day of the week
NEXT_DAY
27
for MONTHS_BETWEEN(date 1, date 2): if date 2 is earlier than date 1, the result is ___
positive
28
t or f. NULL is not zero, nor can it be equal or unequal to any value
t
29
t or f. in LPAD/RPAD, characters require delimiters while numbers do not
t
30
negative numbers can be used as p in a SUBSTR function to indicate a start from the ____
end of the string
31
Functions that accept numeric data and return numeric results
number functions
32
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
```
33
combination of partial data values (characters or numbers, etc) plus one or more wildcards
search strings
34
for the TRIM function, a ____ from the beginning and/or end of a value must be indicated
character string
35
in the WHERE clause, single row functions state a ____ or ____ values returned
condition, restrict
36
general function: returns a null value if 2 expressions are equal
NULLIF
37
character manipulation function: returns a portion of the data value
SUBSTR
38
concatenation operator can join data from 2 or more columns with these 3 things
1. character string(s)
2. constant value
3. arithmetic expression
39
ORDER BY clause can sort by these 3 column elements
column
1. name
2. alias
3. number
40
comparison operator: used to ensure a value is within the field
IS NOT NULL
41
comparison operator: opposite of LIKE
NOT LIKE
42
column number is assigned based on ____ within the ____ clause
column location, SELECT
43
changes the column name displayed
column alias
44
t or f. character manipulation functions in the WHERE clause does not change how the information looks
t
45
conditions that can be in the where clause
columns, expressions, constants and comparison operators
46
conversion function: converts a character string to a DATE
TO_DATE
47
WHERE clause operator: limits number of rows displayed by a query
row restriction
48
character manipulation functions: single-row function that does the same thing as the concatenation pipe
CONCAT
49
conversion function: converts a character string with digits to a NUMBER
TO_NUMBER
50
when p is positive in the ROUND function, the data is rounded to the __ side of the decimal
right
51
used to modify the display of query results
character manipulation functions
52
symbols used for column alias
" "
53
4 types of columns you can use column aliases on
1. underscored columns
2. calculated columns
3. inconsistent case columns
4. concatenated columns
54
t or f. output matches the alias case
t
55
removes a specific string of characters from the beginning or end of a data value
TRIM
56
t or f. date, character, and number types can be used with the NVL function, but the data types must match
t
57
comparison operator: value within a specific inclusive range
BETWEEN...AND
58
clause: can be used to sort rows returned by a query
ORDER BY
59
comparison operator: used to check for NULL values in a field
IS NULL
60
for NEXT_DAY(date, value), the value in the syntax can be one of these 2 things`
1. a number representing the day (1 for Sun, 2 for Mon...)
| 2. a character string such as 'Wednesday'
61
can specify whether to sort ascending or descending
ASC or DESC
62
for MONTHS_BETWEEN(date 1, date 2): if date 1 is earlier than date 2, the result is ___
negative
63
functions that are used to fill in the area on the left or right of a character string
LPAD and RPAD
64
syntax that adds or subtracts a number of hours to a date
date +/- numberofhours/24
65
concatenation operator
|| also known as a 'pipe'
66
t or f. TRIM can be used on its own or as LTRIM or RTRIM
t
67
in the SELECT clause, single row functions change the ____
display
68
in NVL(column, n), n represents the ___
replacement value
69
fm only needs to be used in the ____ within one ____
first instance, TO_CHAR
70
in the SUBSTR syntax SUBSTR(columnName, p, l), what do p and l stand for?
```
p = starting position
l = length of string returned
```
71
WHERE ROWNUM or =?
<
72
1 constraint for column aliases
length
73
math operators can be used in any SQL statement clause except the ____ clause
FROM
74
t or f. case does not matter when it comes to search strings
f
75
conversion function: converts a NUMBER or DATE to character string (VARCHAR2)
TO_CHAR
76
date function: calculates number of months between 2 specified dates
MONTHS_BETWEEN
77
syntax that subtracts one date from another and displays results in days
date - date
78
t or f. column aliases are a permanent change to the column name
f
79
conversion function most often used with TO_CHAR (date) to perform calculations
TO_NUMBER
80
column alias change the ____ of the column output
output display
81
t or f. the IN condition can be used with any data type (date, number, VARCHAR, etc.)
t
82
number function that cuts off number values at the decimal place
TRUNC
83
general function: changes display of a null value
NVL
84
character manipulation functions: determines the number of characters in a string
LENGTH
85
5 single row functions
1. character
2. number
3. date
4. conversion
5. general
86
'p' if you want to round to the whole number
0
87
mathematical expressions that can be comprised of column names, constant numeric values and mathematical operators
calculated columns
88
useful when the user is uncertain of the case of data values
character manipulation functions
89
in CASE, a ___ is necessary
column alias
90
3 case manipulation functions
1. LOWER
2. UPPER
3. INITCAP
91
clause that contains a condition that must be met
WHERE clause
92
the state of being linked together
concatenation
93
'p' if you want to round to the nearest 10
-1
94
fm stands for ____
format model
95
comparison operator: value not within specific inclusive range
NOT BETWEEN...AND
96
general function: allows various actions to be applied within a single IF-THEN-ELSE logic statement; used extensively in industry
CASE
97
in BETWEEN AND, the range contains both an upper and lower limit with the ____ limit specified first
lower
98
what are the 5 single-row functions
1. character
2. number
3. date
4. conversion
5. general
99
by default, the ORDER BY clause will sort in ____ order
ascending
100
WHERE clause: if condition is false, rows ____ be returned and if condition is true, rows ____ be returned
won't, will
101
in NULLIF(expr1, expr2), what is returned if expr1 = expr2?
null
102
when p is negative in the ROUND function, the data is rounded to the __ side of the decimal
left
103
t or f. if used, the ORDER BY clause must be the first clause of the statement
f
104
in industry, wildcards are often used with ____
diagnosis or intervention codes
105
CONCAT can be used in ____ clause
WHERE
106
general function: simpler version of CASE
DECODE
107
conversion function used to get around oracle's default date format (DD-MON-YY)
TO_DATE
108
a ___ represents multiple values
percentage sign (%)
109
comparison operator: used to search for values not within a specified list of values
NOT IN
110
date function: adds a specified number of months to the specified date
ADD_MONTHS
111
t or f. all single-row functions can be used in the WHERE clause as well as the SELECT clause
t
112
general function: evaluates expression(s) and displays replacement values as specified
CASE
113
what can't be used in the WHERE clause
column aliases
114
'p' if you want to round to the nearest 10th
1
115
a query can have several conditions if ___ or ___ operators are used
AND/OR
116
syntax to add a number of days to a date
date + number
117
2 most frequently used number functions
1. ROUND
| 2. TRUNC
