Oracle__5. Oracle 1Z0-051 Exam - Functions Flashcards
1
Q
What will be the difference in results between these two SELECT clauses? SELECT a, count(a) FROM table GROUP BY a; SELECT a, count(*) FROM table GROUP BY a;
A
The first SELECT will not count any nulls in column a, while the second SELECT will count all rows
2
Q
What is the simplest syntax to return all columns of a table as well as AVG(column1)?
A
SELECT t1., AVG(column1)FROM T1GROUP BY T1.
3
Q
What function can be used in the WHERE clause if you want all records from column1 from table1 ending with an ‘n’?
A
WHERE SUBSTR(column,-1,1) = ‘n’
4
Q
“What is the syntax if you wanted to select columns1 and column2 from table1 where column4 is greater than zero and column7 is begins with an ““A”” or ““b”” or ““C””?”
A
SELECT column1, column2FROM table1WHERE column4 > 0AND(SUBSTR(column7,1) = ‘A’ ORSUBSTR (column7,1) = ‘b’ORSUBSTR(column7, 1) = ‘C’)Between cannot be used because of upper/lower caseParenthesis are needed because of the AND/OR combinations
5
Q
What is returned from the following statement? SELECT SUBSTR( ‘ABCdefgh’, 2, 4) FROM dual
A
BCde
6
Q
What is returned from the following statement? SELECT SUBSTR( ‘ABCdefgh’, 5) FROM dual
A
efghIf the length is not specific all remaining characters are returned
7
Q
What is returned from the following statement? SELECT SUBSTR( ‘ABCdefgh’, -3, 2) FROM dual
A
fg
8
Q
Notice the spaces before and after the string, what is returned from the following statement? SELECT TRIM( ‘ Tech ‘) FROM dual
A
Tech spaces in front and after have been removed
9
Q
What is returned from the following statement? SELECT TRIM( ‘T’ FROM ‘Tech’) FROM dual
A
ech
10
Q
What is returned from the following statement? SELECT TRIM(LEADING ‘0’ FROM ‘0001240’) FROM dual
A
1240
11
Q
What is returned from the following statement? SELECT TRIM(TRAILING ‘x’ FROM ‘xTechx’) FROM dual
A
xTech
12
Q
What is returned from the following statement? SELECT TRIM( BOTH ‘1’ FROM ‘1Tech111’) FROM dual
A
Tech
13
Q
What is returned from the following statement? SELECT RTRIM( ‘techxyxyxyxxx’, ‘xyz’) FROM dual
A
techThe RTRIM remove all characters that match regardless of pattern
14
Q
What is returned from the following statement? SELECT LTRIM( ‘1443Tech1545’, ‘431’) FROM dual
A
Tech1545
15
Q
What is returned from the following statement? SELECT INITCAP( ‘GEORGE BURNS’) FROM dual
A
George BurnsInitCap capitalizes the first letter of each word
16
Q
What is returned from the following statement? SELECT UPPER( ‘GeorGe Burns 123’) FROM dual
A
GEORGE BURNS 123All letters are capitalized
17
Q
What is returned from the following statement? SELECT LOWER( ‘GeorGe Burns 123’) FROM dual
A
george burns 123All letters are lower case
18
Q
What is returned from the following statement? SELECT LENGTH( null) FROM dual
A
null
19
Q
What is returned from the following statement? SELECT LENGTH( ‘’) FROM dual
A
null
20
Q
What is returned from the following statement? SELECT LENGTH( ‘ ‘) FROM dual
A
1There is one space
21
Q
What is returned from the following statement? SELECT LENGTH( ‘test’) FROM dual
A
4
22
Q
What is returned from the following statement? SELECT CONCAT( ‘A’, ‘B’) FROM dual
A
AB
23
Q
What is returned from the following statement? SELECT ‘A’ || ‘B’ FROM dual
A
AB
24
Q
Using the CONTACT function what is the syntax for concatenating ‘A’, ‘B’ and ‘C’?
A
CONCAT( CONCAT( ‘A’, ‘B’), ‘C’)2 nested CONCAT Functions, because the CONCAT function can only take 2 arguments
25
Using the || operator what is the syntax for concatenating 'A','B' and 'C'?
'A' || 'B' || 'C'
26
What is returned from the following statement? SELECT REPLACE( '123123Tech', '123', '2') FROM dual
222222Tech
27
What is returned from the following statement? SELECT REPLACE( '123123Tech', '123') FROM dual
TechIf the 3rd argument of the REPLACE function is empty then the search string is deleted from the 1st argument
28
What is returned from the following statement? SELECT INSTR( 'abcabc', 'bc') FROM dual
2INSTR function finds the first occurrence of the string
29
What is returned from the following statement? SELECT INSTR( 'abcabc', 'bc', 1, 2) FROM dual
5INSTR functions finds the 2nd occurrence of the string starting at the 1 position.
30
What is returned from the following statement? SELECT INSTR( 'abcabc', 'bc, -3, 1) FROM dual
5INSTR find the the first occurrence of the string starting -3 position from the end of the string
31
What is returned from the following statement? SELECT LPAD( '1234', 6, '*') FROM dual
**1234
32
What is returned from the following statement? SELECT LPAD( '1234', '2', '*') FROM dual
1234
33
What is returned from the following statement? SELECT RPAD( '1234', 6, '*') FROM dual
1234**
34
What is returned from the following statement? SELECT RPAD( '1234', '2, '*') FROM dual
1234
35
What is returned from the following statement? SELECT COALESCE (null, 1, null) FROM dual
1COALESCE returns the first non-null value in the list or column names
36
What is returned from the following statement? SELECT ASCII( 'T2') FROM dual
84The ASCII function returns the ascii code from the first letter in the string
37
What is returned from the following statement? SELECT CHR( 84) FROM dual
T
38
What is returned from the following statement? SELECT ABS( -23.65) FROM dual
23.65
39
What is returned from the following statement? SELECT ROUND( 125.612) FROM dual
126The value of zero is assumed
40
What is returned from the following statement? SELECT ROUND( 125.661, 1) FROM dual
125.7
41
What is returned from the following statement? SELECT ROUND( 125.5 ,-1) FROM dual
130The minus means round 1 digit to the left of the decimal
42
What is returned from the following statement? SELECT TRUNC( 125.8) FROM dual
125
43
What is returned from the following statement? SELECT TRUNC( 126.34, -1) FROM dual
120The minus mean truncate 1 digit to the left of the decimal
44
What is returned from the following statement? SELECT TRUNC( 125.612, 1) FROM dual
125.6
45
What is returned from the following statement? SELECT TO_CHAR( 1210.73, '9999.9') FROM dual
1210.7The TO_CHAR function will have a leading space added when a format model is used for the sign (+/-)
46
What is returned from the following statement? SELECT TO_CHAR( 1210.73, '9,999.9') FROM dual
1,210.7The TO_CHAR function will have a leading space added when a format model is used for the sign (+/-)
47
What is returned from the following statement? SELECT TO_CHAR( 1210.73, '009999.9') FROM dual
001210.7The TO_CHAR function will have a leading space added when a format model is used for the sign (+/-)
48
what is returned from the following statement? SELECT TO_NUMBER( '1210.73') FROM dual
1210.73
49
What is returned from the following statement? SELECT TO_NUMBER( '1210.73', '999.99') FROM dual
1210.73
50
If the value of ID is 5, what is returned from the following statement? SELECT DECODE ( TRUNC(( id - 1) / 10), 0, 'A', 1, 'B', 2, 'C', 'D' ) FROM table
A
51
If the value of ID is 15, what is returned from the following statement? SELECT DECODE ( TRUNC(( id - 1) / 10), 0, 'A', 1, 'B', 2, 'C', 'D' ) FROM table
B
52
If the value of ID is 66, what is returned from the following statement? SELECT DECODE ( TRUNC(( id - 1) / 10), 0, 'A', 1, 'B', 2, 'C', 'D' ) FROM table
D
53
Why is it necessary to use a formula in the DECODE statement? SELECT DECODE ( TRUNC(( id - 1) / 10), 0, 'A',, 1, 'B', 2, 'C', 'D' ) FROM table
DECODE only allows single values and no ranges
54
What is returned from the following statement? SELECT NULLIF( 12, 12) FROM dual
nullThe NULLIF function returns a null if both arguments are equal
55
What is returned from the following statement? SELECT NULLIF( 12, 13) FROM dual
12
56
What is returned from the following statement? SELECT NULLIF( null, 12) FROM dual
This will cause an errora Null value cannot be explicitly added to the NULLIF function
57
What is returned from the following statement? SELECT NULLIF( 'A', 'B') FROM dual
Anumeric, text and date data types are allowed to be compared, but both arguments must match in data types.
58
If the value of ID is null, what is returned from the following statement SELECT NVL( id, 1) FROM dual
1The 2nd argument is return from an NVL function only if the first argument is null.
59
If the value of ID has a value of 2, what is returned from the following statement SELECT NVL( id, 1) FROM dual
2The 2nd argument is return from an NVL function only if the first argument is null.
60
If the value of ID is null, what is returned from the following statement SELECT NVL2( id, 1, 3) FROM dual
3For an NLV2 function, the first value is returned if ID is not null. The second value is returned if the ID is null.
61
If the value of ID has a value of 2, what is returned from the following statement SELECT NVL2( id, 1, 3) FROM dual
1For an NLV2 function, the first value is returned if ID is not null. The second value is returned if the ID is null.
62
Using the LIKE operator and a function, create a WHERE clause that would find all records from column Parts which begin with 'h' or 'H'.
WHERE UPPER(Parts) LIKE 'H%'
63
If the SELECT clause has one or more of the functions SUM, COUNT, MIN, MAX or AVG function, what other clause if needed?
GROUP BYIf the Select Clause only has those functions without any column name or other expression, then the GROUP BY is not necessary, for example: SELECT avg(field) FROM table1
64
If hire_date is a date data type, what data type will be returned by this SQL statement? SELECT TO_NUMBER(hire_date + 7) FROM emp
A numeric data type, which can also be interpreted as a date data type
65
Create the WHERE clause that can find all records in the column customer_name where the values must have an 'e' anywhere in the string and must have an 'a' as the 2nd to last character.
WHERE INSTR(customer_name,'e') <> 0AND SUBSTR(customer_name,-2,1) = 'a'
66
What is returned from the following statement? SELECT COUNT(DISTINCT dept_id) FROM employees WHERE last_name = 'Smith'
The number of departments which has an employee with the last name of Smith
67
What is returned from the following statement? SELECT COUNT(dept_id) FROM employees WHERE last_name = 'Smith'
The total number of employees whose last name is Smith
68
Write a SQL statement which will return the value of the field ename, the total number of character of the value in the field ename, and what position is the letter 'g' in the value of the field ename from table1.
SELECT ename, LENGTH(ename), INSTR(ename, 'g') FROM table1
69
What is return from the following statement? SELECT item_no, AVG(qty) FROM table1 HAVING AVG(qty) > MIN(qty) * 2 GROUP BY item_no
It returns the item number with the average quantity, where the average quantity is more than double the minimum quantity for that item.
70
How would you modify this SQL Statement where the minimum is greater than the average for that particular item? SELECT item_no, AVG(qty) FROM table1 HAVING AVG(qty) > MIN(qty) * 2 GROUP BY item_no
SELECT item_no, AVG(qty)FROM table1HAVING min(qty) > avg(qty)GROUP BY item_no
71
What is the result of the following statement? SELECT promo_name, DECODE(NULLIF(promo_date, promo_date), NULL, 'Same Day') FROM promos
NullNULLIF(promo_date,promo_date) resolves to nullDECODE - since the expression result (first argument) is null, and the search value is a null (second argument), the third argument is returned.
72
What is the result of the following statement? SELECT promo_name, DECODE((NULLIF(promo_date, promo_date-1)), NULL, 'Same Day') FROM promos
Same DayNULLIF(promo_date,promo_date-1) does not resolve to nullDECODE - because of the extra parenthesis, the first argument is true, since the first statement is not null, the second value is returned
73
What arithmetic operation can be performed by a SQL function that is built into the Oracle database equivalent to addition?
SUM()
74
What arithmetic operation can be performed by a SQL function that is built into the Oracle database equivalent to subtraction?
No function
75
What arithmetic operation can be performed by a SQL function that is built into the Oracle database equivalent to finding the lowest value?
MIN()
76
What arithmetic operation can be performed by a SQL function that is built into the Oracle database equivalent to raising to a power?
POWER()
77
What arithmetic operation can be performed by a SQL function that is built into the Oracle database equivalent to finding a quotient?
no function
78
What arithmetic operation can be performed by a SQL function that is built into the Oracle database equivalent to finding a remainder?
MOD()
79
Can a single row function return a data type value different from the argument?
YesTO_CHAR() convert one data type to another
80
A COALESCE function returns the first non null value from its parameter list. What needs to be common to all items in the parameter list?
They need to all be the same data type
81
Are the following 2 statements equivalent? COALESCE(expr1,expr2, expr3) NVL(expr3,NVL(expr2,expr1))
NoIf the NVL expressions were in reverse order it would be equivalent to the COALESCENVL(expr1, NVL(expr2, expr3))
82
"What is the result of this statement if ename is SMITH and the date is 17-DEC-80? SELECT CONCAT(SUBSTR(INITCAP(ename),1,3), REPLACE(hiredate,'-')) ""USERID"" FROM Employees"
The result is Smi17DEC80
83
What is the maximum number of parameters allowed in the REPLACE function?
3
84
What is the mandatory number of parameters for the REPLACE function?
2
85
What is the general syntax of the REPLACE function?
REPLACE(source string, search string, [replacement string])
86
What function will compare two values and determine if they are equal?
NULLIF(string1, string2) it returns a null if the 2 parts are equal
87
How many mandatory parameters are required for the DECODE clause?
3DECODE(expression1, compare1, ifTrue[, ifFalse])
88
What is the syntax for the following statement using the DECODE function? IF field1 = 5 Then 10 Elseif field2 = 6 Then 15 else 20 end
DECODE(field1,5,10,DECODE(field2,6,15),20)
89
What function returns all lower case letters for a text string?
LOWER()
90
What function returns all upper case letters for a text string?
UPPER()
91
What function returns upper case letter for the first letter of each word and the rest lower case?
INITCAP()
92
What is the symbol for the concatenate operator?
|| (two parallel lines)
93
If there are no parenthesis, which are evaluated first: Concatenation operators or Comparison conditions
Concatenation operator then Comparison conditions
94
What are the 2 main return types (not data types) of functions?
1. single row2. multiple row
95
In what 3 clauses can single row functions be used?
1. SELECT2. WHERE3. ORDER BY
96
List 3 single row characters functions that modify the case of the string?
1. LOWER2. UPPER3. INITCAP
97
What function can be used for concatenating two strings?
CONCAT( string1, string2)
98
What 2 single row character functions return numbers?
1. LENGTH()2. INSTR()
99
What is the result of the following statement? TRIM('W' FROM 'HellowWorld')
HellowWorldThe trim only removed matching characters from the beginning or end of the phrase.
100
What function can you use so that a user does not have to remember to enter a word in all Capital letters for a condition in a WHERE clause?
UPPER('&name')The single quotes are needed
101
What function is used to retrieve all strings ending with 'n'?
SUBSTR(column_name,-1,1) = 'n'
102
What function returns the remainder of a division?
MOD()
103
What is returned from ROUND(45.926,2)?
45.93
104
What is returned from TRUNC(45.926,2)?
45.92
105
What is returned from ROUND(45.923,-1)?
50
106
What is returned from TRUNC(45.923,-1)?
40
107
What is returned from TRUNC(45.923)?
45
108
List 3 explicit data type conversions?
1. TO_CHAR2. TO_NUMBER3. TO_DATE
109
Are numerical values in the TO_CHAR function with a format element Truncated or Rounded?
Rounded
110
What is returned from NULLIF('A','A')?
null
111
What is returned from NVL(null,'A')?
A
112
What is returned from NVL2(NULL,'A','B')?
A
113
What 4 data types can be used in an NVL function?
1. Number2. Date3. Char4. Varchar2
114
What is returned from NVL2(1,null,3)?
3
115
What is returned from NVL2(null,1,2)?
1
116
What is returned from NULLIF('A','B')?
A
117
What is returned if expr1 is 12? CASE expr1 WHEN 10 THEN 'A' WHEN 20 THEN 'B' ELSE 'C' END
C
118
What is returned if expre1 is 12? DECODE( expr1, 10, 'A', 20, 'B', 'C')
C
119
List at least 5 group functions.
1. AVG2. COUNT3. MAX4. MIN5. SUMAlso6. STDDEV7. VARIENCE
120
What keyword can be used inside all Group Functions?
DISTINCT Such as AVG(DISTINCT fieldname)The Keyword ALL is default
121
How are Nulls handled by default in all group functions?
all nulls are ignored by default
122
How can you include nulls in a the AVG function?
Use NVL, NVL2 or COALESCE functionsSuch as AVG(NVL(fieldname,0)) which will replace zeros for null in the calculation.
123
All Group functions use numeric values. List 3 of those group functions which can also be used on other data types?
1. MIN2. MAX3. COUNT
124
What is the syntax of the COUNT function for counting all unique city_name with not being case sensitive?
COUNT ( DISTINCT ( UPPER (city_name)))orCOUNT ( DISTINCT ( LOWER (city_name)))
125
Date, numeric and character functions that change a date data types are called what type of functions?
conversion functions
126
What is returned from the following statement? SELECT LENGTH(TO_CHAR('012')) FROM dual
3
127
What is returned from the following statement? SELECT LENGTH(TO_CHAR('012','000')) FROM dual
4The TO_CHAR function will have a leading space added when a format model is used because of the possibility of a sign (+/-)
128
What is returned from the following statement? SELECT LENGTH(TO_CHAR('012','FM000')) FROM dual
3The FM remove the leading space
129
What is returned from the following statement? SELECT LENGTH(TO_CHAR('-012','000')) FROM dual
4The TO_CHAR function leading space is filled by the sign (+/-)
130
The is the difference in the return from the following statements? TO_CHAR(9,DDSPTH) TO_CHAR(9,DDTHSP)
The returns are the same, the order of the format element suffixes SP or TH does not matter.NINETH
131
What is returned from the following statement? SELECT 'Today' || '''s Special' FROM dual
Today's SpecialThe three quotes return one quoteAlso any extra spaces in a statement outside of quotes are ignored
132
What is returned from the following statement? SELECT NVL(Null,1) FROM dual
NullNull cannot be explicitly specified in an NVL function
133
What is returned from the following statement if the value in column1 is a null? SELECT NVL(column1,1) FROM Dual
1
134
What are the 3 character case conversion functions?
1. UPPER2. LOWER3. INITCAP
135
What is returned from the following statement? CONCAT('The number is: ',5)
The number is: 5The concat function can accept text, date or number data types.Oracle implicitly converts other data types to Char?
136
What is returned from the following statement? REPLACE(9999.0 - 1, '8', '77')
99977The expression 9999.0 - 1 is implicitly converted to text of 9998.0 then the 8 is replace with 77
137
What is returned from the following statement? MOD(3,2)
.5
138
What is returned from the following statement? MOD(3,-2)
-0.5
139
What is significant of the return of the following statement? MOD ( number, 2)
If the return is zero the number is even.If the return is 1 the number is odd.
140
What is returned from the following statement? SELECT NVL(1234,' ') FROM dual;
This statement will error, because both values must be the same data type in an NVL statement.The NVL can take numerical, date or character data types, the requirement is both arguments must match data types.
141
What is returned from the following statement? SELECT TO_NUMBER ('$3000') FROM dual;
an errorThe TO_NUMBER cannot interpret the $
142
What is returned from the following statement? SELECT TO_NUMBER('$3,000.67','$999,999.99') FROM dual;
3000.67 as a number type
143
What is returned from the following statement? SELECT NULLIF(1,2-1) FROM dual;
NullBoth values are equal
144
What is returned from the following statement? SELECT NULLIF('01-JAN-2013','01-JAN-13') FROM dual;
01-Jan-2013one string is longer than the other so they are not equal
145
When a return of a function is used as an input to another function, it is termed as?
nested functionExample: UPPER ( LOWER ('test'))
146
Which of the following can be used in the SELECT clause? MIN MAX AVG NVL
only the NVL can be used in the SELECT clause
147
Which of the following can be used in the GROUP BY clause? NVL AVG COUNT DECODE
only the AVG can be use in the GROUP BY clause
148
What is returned from the following Statement? SELECT COUNT(ALL column1) FROM table1
The count ignores the nulls in column1 but counts the duplicatesSame as: SELECT COUNT(column1) FROM Table1
149
What is restrictive of data types with the following functions? AVG() SUM() STDDEV() VARIANCE()
all take only 1 numeric data type
150
What is restrictive of the data types with the following functions? MAX() MIN()
They can take numeric, character or date data types
151
How many levels deep can Group functions be nested?
only 2
152
Can a group level function be inside a single-row function?
YesExample: TO_CHAR( MAX (12))
153
Can a single-row function be nested inside a group level function?
YesExample: MAX ( TO_CHAR (12))
154
What is the format model for converting the text '$9,784.34' to a numerical value?
TO_NUMBER( '9,784.34', '$9,999.99')orTO_NUMBER( '9,784.34', 'L9G999D99')The L signals a currency symbolThe G signals a commaThe D signals a decimal
155
What is returned from the following statement? SELECT 'this is a ' || null || 'test with nulls' from dual;
this is a test with nullsA null is treated as an empty space in a text
156
What is returned from the following statement? SELECT NULLIF( 'A', 1) FROM dual
NullThe data types do not match. A text is compare to a number
157
What is returned by this statement? SELECT NVL2(inv_amt, inv_amt * 25, 'Not Available')
errorbecause one return value is numeric and the other return value is text
158
Is this syntax correct? SELECT COUNT(DISTINCT *) FROM Table1
Yes. It will count all the unique records from table1, not counting any duplicate rows.
159
What two data types can the TRUNC function be used with?
1. NUMBER2. DATE
160
A TO_CHAR function can have what 2 data types?
Number or date
161
Will this statement execute without errors? SELECT SUM( DISTINCT NVL( subject1,0)) , MAX( subject1) FROM marks WHERE Subject1 > subject2
Yes.A DISTINCT can be within a SUM function)The GROUP BY Clause is not needed.
162
Will this statement execute without errors? SELECT AVG(int_date) FROM invoice;
No.The AVG function will not take the date data type.
