Brainscape's Knowledge GenomeTM

Browse over 1 million classes created by top students, professors, publishers, and experts.


See full index

DSO SQL > Oracle__5. Oracle 1Z0-051 Exam - Functions > Flashcards

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
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.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
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’

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
5
Q

What is returned from the following statement? SELECT SUBSTR( ‘ABCdefgh’, 2, 4) FROM dual

A

BCde

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
7
Q

What is returned from the following statement? SELECT SUBSTR( ‘ABCdefgh’, -3, 2) FROM dual

A

fg

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
9
Q

What is returned from the following statement? SELECT TRIM( ‘T’ FROM ‘Tech’) FROM dual

A

ech

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
10
Q

What is returned from the following statement? SELECT TRIM(LEADING ‘0’ FROM ‘0001240’) FROM dual

A

1240

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
11
Q

What is returned from the following statement? SELECT TRIM(TRAILING ‘x’ FROM ‘xTechx’) FROM dual

A

xTech

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
12
Q

What is returned from the following statement? SELECT TRIM( BOTH ‘1’ FROM ‘1Tech111’) FROM dual

A

Tech

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
14
Q

What is returned from the following statement? SELECT LTRIM( ‘1443Tech1545’, ‘431’) FROM dual

A

Tech1545

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
16
Q

What is returned from the following statement? SELECT UPPER( ‘GeorGe Burns 123’) FROM dual

A

GEORGE BURNS 123All letters are capitalized

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
17
Q

What is returned from the following statement? SELECT LOWER( ‘GeorGe Burns 123’) FROM dual

A

george burns 123All letters are lower case

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
18
Q

What is returned from the following statement? SELECT LENGTH( null) FROM dual

A

null

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
19
Q

What is returned from the following statement? SELECT LENGTH( ‘’) FROM dual

A

null

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
20
Q

What is returned from the following statement? SELECT LENGTH( ‘ ‘) FROM dual

A

1There is one space

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
21
Q

What is returned from the following statement? SELECT LENGTH( ‘test’) FROM dual

A

4

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
22
Q

What is returned from the following statement? SELECT CONCAT( ‘A’, ‘B’) FROM dual

A

AB

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
23
Q

What is returned from the following statement? SELECT ‘A’ || ‘B’ FROM dual

A

AB

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
25
Q

Using the || operator what is the syntax for concatenating ‘A’,’B’ and ‘C’?

A

‘A’ || ‘B’ || ‘C’

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
26
Q

What is returned from the following statement? SELECT REPLACE( ‘123123Tech’, ‘123’, ‘2’) FROM dual

A

222222Tech

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
27
Q

What is returned from the following statement? SELECT REPLACE( ‘123123Tech’, ‘123’) FROM dual

A

TechIf the 3rd argument of the REPLACE function is empty then the search string is deleted from the 1st argument

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
28
Q

What is returned from the following statement? SELECT INSTR( ‘abcabc’, ‘bc’) FROM dual

A

2INSTR function finds the first occurrence of the string

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
29
Q

What is returned from the following statement? SELECT INSTR( ‘abcabc’, ‘bc’, 1, 2) FROM dual

A

5INSTR functions finds the 2nd occurrence of the string starting at the 1 position.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
30
Q

What is returned from the following statement? SELECT INSTR( ‘abcabc’, ‘bc, -3, 1) FROM dual

A

5INSTR find the the first occurrence of the string starting -3 position from the end of the string

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
31
Q

What is returned from the following statement? SELECT LPAD( ‘1234’, 6, ‘*’) FROM dual

A

**1234

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
32
Q

What is returned from the following statement? SELECT LPAD( ‘1234’, ‘2’, ‘*’) FROM dual

A

1234

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
33
Q

What is returned from the following statement? SELECT RPAD( ‘1234’, 6, ‘*’) FROM dual

A

1234**

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
34
Q

What is returned from the following statement? SELECT RPAD( ‘1234’, ‘2, ‘*’) FROM dual

A

1234

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
35
Q

What is returned from the following statement? SELECT COALESCE (null, 1, null) FROM dual

A

1COALESCE returns the first non-null value in the list or column names

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
36
Q

What is returned from the following statement? SELECT ASCII( ‘T2’) FROM dual

A

84The ASCII function returns the ascii code from the first letter in the string

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
37
Q

What is returned from the following statement? SELECT CHR( 84) FROM dual

A

T

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
38
Q

What is returned from the following statement? SELECT ABS( -23.65) FROM dual

A

23.65

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
39
Q

What is returned from the following statement? SELECT ROUND( 125.612) FROM dual

A

126The value of zero is assumed

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
40
Q

What is returned from the following statement? SELECT ROUND( 125.661, 1) FROM dual

A

125.7

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
41
Q

What is returned from the following statement? SELECT ROUND( 125.5 ,-1) FROM dual

A

130The minus means round 1 digit to the left of the decimal

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
42
Q

What is returned from the following statement? SELECT TRUNC( 125.8) FROM dual

A

125

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
43
Q

What is returned from the following statement? SELECT TRUNC( 126.34, -1) FROM dual

A

120The minus mean truncate 1 digit to the left of the decimal

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
44
Q

What is returned from the following statement? SELECT TRUNC( 125.612, 1) FROM dual

A

125.6

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
45
Q

What is returned from the following statement? SELECT TO_CHAR( 1210.73, ‘9999.9’) FROM dual

A

1210.7The TO_CHAR function will have a leading space added when a format model is used for the sign (+/-)

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
46
Q

What is returned from the following statement? SELECT TO_CHAR( 1210.73, ‘9,999.9’) FROM dual

A

1,210.7The TO_CHAR function will have a leading space added when a format model is used for the sign (+/-)

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
47
Q

What is returned from the following statement? SELECT TO_CHAR( 1210.73, ‘009999.9’) FROM dual

A

001210.7The TO_CHAR function will have a leading space added when a format model is used for the sign (+/-)

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
48
Q

what is returned from the following statement? SELECT TO_NUMBER( ‘1210.73’) FROM dual

A

1210.73

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
49
Q

What is returned from the following statement? SELECT TO_NUMBER( ‘1210.73’, ‘999.99’) FROM dual

A

1210.73

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
50
Q

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

A

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
51
Q

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

A

B

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
52
Q

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

A

D

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
53
Q

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

A

DECODE only allows single values and no ranges

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
54
Q

What is returned from the following statement? SELECT NULLIF( 12, 12) FROM dual

A

nullThe NULLIF function returns a null if both arguments are equal

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
55
Q

What is returned from the following statement? SELECT NULLIF( 12, 13) FROM dual

A

12

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
56
Q

What is returned from the following statement? SELECT NULLIF( null, 12) FROM dual

A

This will cause an errora Null value cannot be explicitly added to the NULLIF function

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
57
Q

What is returned from the following statement? SELECT NULLIF( ‘A’, ‘B’) FROM dual

A

Anumeric, text and date data types are allowed to be compared, but both arguments must match in data types.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
58
Q

If the value of ID is null, what is returned from the following statement SELECT NVL( id, 1) FROM dual

A

1The 2nd argument is return from an NVL function only if the first argument is null.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
59
Q

If the value of ID has a value of 2, what is returned from the following statement SELECT NVL( id, 1) FROM dual

A

2The 2nd argument is return from an NVL function only if the first argument is null.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
60
Q

If the value of ID is null, what is returned from the following statement SELECT NVL2( id, 1, 3) FROM dual

A

3For an NLV2 function, the first value is returned if ID is not null. The second value is returned if the ID is null.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
61
Q

If the value of ID has a value of 2, what is returned from the following statement SELECT NVL2( id, 1, 3) FROM dual

A

1For an NLV2 function, the first value is returned if ID is not null. The second value is returned if the ID is null.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
62
Q

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’.

A

WHERE UPPER(Parts) LIKE ‘H%’

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
63
Q

If the SELECT clause has one or more of the functions SUM, COUNT, MIN, MAX or AVG function, what other clause if needed?

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
64
Q

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

A numeric data type, which can also be interpreted as a date data type

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
65
Q

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.

A

WHERE INSTR(customer_name,’e’) <> 0AND SUBSTR(customer_name,-2,1) = ‘a’

66
Q

What is returned from the following statement? SELECT COUNT(DISTINCT dept_id) FROM employees WHERE last_name = ‘Smith’

A

The number of departments which has an employee with the last name of Smith

67
Q

What is returned from the following statement? SELECT COUNT(dept_id) FROM employees WHERE last_name = ‘Smith’

A

The total number of employees whose last name is Smith

68
Q

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.

A

SELECT ename, LENGTH(ename), INSTR(ename, ‘g’) FROM table1

69
Q

What is return from the following statement? SELECT item_no, AVG(qty) FROM table1 HAVING AVG(qty) > MIN(qty) * 2 GROUP BY item_no

A

It returns the item number with the average quantity, where the average quantity is more than double the minimum quantity for that item.

70
Q

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

A

SELECT item_no, AVG(qty)FROM table1HAVING min(qty) > avg(qty)GROUP BY item_no

71
Q

What is the result of the following statement? SELECT promo_name, DECODE(NULLIF(promo_date, promo_date), NULL, ‘Same Day’) FROM promos

A

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
Q

What is the result of the following statement? SELECT promo_name, DECODE((NULLIF(promo_date, promo_date-1)), NULL, ‘Same Day’) FROM promos

A

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
Q

What arithmetic operation can be performed by a SQL function that is built into the Oracle database equivalent to addition?

A

SUM()

74
Q

What arithmetic operation can be performed by a SQL function that is built into the Oracle database equivalent to subtraction?

A

No function

75
Q

What arithmetic operation can be performed by a SQL function that is built into the Oracle database equivalent to finding the lowest value?

A

MIN()

76
Q

What arithmetic operation can be performed by a SQL function that is built into the Oracle database equivalent to raising to a power?

A

POWER()

77
Q

What arithmetic operation can be performed by a SQL function that is built into the Oracle database equivalent to finding a quotient?

A

no function

78
Q

What arithmetic operation can be performed by a SQL function that is built into the Oracle database equivalent to finding a remainder?

A

MOD()

79
Q

Can a single row function return a data type value different from the argument?

A

YesTO_CHAR() convert one data type to another

80
Q

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?

A

They need to all be the same data type

81
Q

Are the following 2 statements equivalent? COALESCE(expr1,expr2, expr3) NVL(expr3,NVL(expr2,expr1))

A

NoIf the NVL expressions were in reverse order it would be equivalent to the COALESCENVL(expr1, NVL(expr2, expr3))

82
Q

“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”

A

The result is Smi17DEC80

83
Q

What is the maximum number of parameters allowed in the REPLACE function?

A

3

84
Q

What is the mandatory number of parameters for the REPLACE function?

A

2

85
Q

What is the general syntax of the REPLACE function?

A

REPLACE(source string, search string, [replacement string])

86
Q

What function will compare two values and determine if they are equal?

A

NULLIF(string1, string2) it returns a null if the 2 parts are equal

87
Q

How many mandatory parameters are required for the DECODE clause?

A

3DECODE(expression1, compare1, ifTrue[, ifFalse])

88
Q

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

A

DECODE(field1,5,10,DECODE(field2,6,15),20)

89
Q

What function returns all lower case letters for a text string?

A

LOWER()

90
Q

What function returns all upper case letters for a text string?

A

UPPER()

91
Q

What function returns upper case letter for the first letter of each word and the rest lower case?

A

INITCAP()

92
Q

What is the symbol for the concatenate operator?

A

|| (two parallel lines)

93
Q

If there are no parenthesis, which are evaluated first: Concatenation operators or Comparison conditions

A

Concatenation operator then Comparison conditions

94
Q

What are the 2 main return types (not data types) of functions?

A
  1. single row2. multiple row
95
Q

In what 3 clauses can single row functions be used?

A
  1. SELECT2. WHERE3. ORDER BY
96
Q

List 3 single row characters functions that modify the case of the string?

A
  1. LOWER2. UPPER3. INITCAP
97
Q

What function can be used for concatenating two strings?

A

CONCAT( string1, string2)

98
Q

What 2 single row character functions return numbers?

A
  1. LENGTH()2. INSTR()
99
Q

What is the result of the following statement? TRIM(‘W’ FROM ‘HellowWorld’)

A

HellowWorldThe trim only removed matching characters from the beginning or end of the phrase.

100
Q

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?

A

UPPER(‘&name’)The single quotes are needed

101
Q

What function is used to retrieve all strings ending with ‘n’?

A

SUBSTR(column_name,-1,1) = ‘n’

102
Q

What function returns the remainder of a division?

A

MOD()

103
Q

What is returned from ROUND(45.926,2)?

A

45.93

104
Q

What is returned from TRUNC(45.926,2)?

A

45.92

105
Q

What is returned from ROUND(45.923,-1)?

A

50

106
Q

What is returned from TRUNC(45.923,-1)?

A

40

107
Q

What is returned from TRUNC(45.923)?

A

45

108
Q

List 3 explicit data type conversions?

A
  1. TO_CHAR2. TO_NUMBER3. TO_DATE
109
Q

Are numerical values in the TO_CHAR function with a format element Truncated or Rounded?

A

Rounded

110
Q

What is returned from NULLIF(‘A’,’A’)?

A

null

111
Q

What is returned from NVL(null,’A’)?

A

A

112
Q

What is returned from NVL2(NULL,’A’,’B’)?

A

A

113
Q

What 4 data types can be used in an NVL function?

A
  1. Number2. Date3. Char4. Varchar2
114
Q

What is returned from NVL2(1,null,3)?

A

3

115
Q

What is returned from NVL2(null,1,2)?

A

1

116
Q

What is returned from NULLIF(‘A’,’B’)?

A

A

117
Q

What is returned if expr1 is 12? CASE expr1 WHEN 10 THEN ‘A’ WHEN 20 THEN ‘B’ ELSE ‘C’ END

A

C

118
Q

What is returned if expre1 is 12? DECODE( expr1, 10, ‘A’, 20, ‘B’, ‘C’)

A

C

119
Q

List at least 5 group functions.

A
  1. AVG2. COUNT3. MAX4. MIN5. SUMAlso6. STDDEV7. VARIENCE
120
Q

What keyword can be used inside all Group Functions?

A

DISTINCT Such as AVG(DISTINCT fieldname)The Keyword ALL is default

121
Q

How are Nulls handled by default in all group functions?

A

all nulls are ignored by default

122
Q

How can you include nulls in a the AVG function?

A

Use NVL, NVL2 or COALESCE functionsSuch as AVG(NVL(fieldname,0)) which will replace zeros for null in the calculation.

123
Q

All Group functions use numeric values. List 3 of those group functions which can also be used on other data types?

A
  1. MIN2. MAX3. COUNT
124
Q

What is the syntax of the COUNT function for counting all unique city_name with not being case sensitive?

A

COUNT ( DISTINCT ( UPPER (city_name)))orCOUNT ( DISTINCT ( LOWER (city_name)))

125
Q

Date, numeric and character functions that change a date data types are called what type of functions?

A

conversion functions

126
Q

What is returned from the following statement? SELECT LENGTH(TO_CHAR(‘012’)) FROM dual

A

3

127
Q

What is returned from the following statement? SELECT LENGTH(TO_CHAR(‘012’,’000’)) FROM dual

A

4The TO_CHAR function will have a leading space added when a format model is used because of the possibility of a sign (+/-)

128
Q

What is returned from the following statement? SELECT LENGTH(TO_CHAR(‘012’,’FM000’)) FROM dual

A

3The FM remove the leading space

129
Q

What is returned from the following statement? SELECT LENGTH(TO_CHAR(‘-012’,’000’)) FROM dual

A

4The TO_CHAR function leading space is filled by the sign (+/-)

130
Q

The is the difference in the return from the following statements? TO_CHAR(9,DDSPTH) TO_CHAR(9,DDTHSP)

A

The returns are the same, the order of the format element suffixes SP or TH does not matter.NINETH

131
Q

What is returned from the following statement? SELECT ‘Today’ || ‘'’s Special’ FROM dual

A

Today’s SpecialThe three quotes return one quoteAlso any extra spaces in a statement outside of quotes are ignored

132
Q

What is returned from the following statement? SELECT NVL(Null,1) FROM dual

A

NullNull cannot be explicitly specified in an NVL function

133
Q

What is returned from the following statement if the value in column1 is a null? SELECT NVL(column1,1) FROM Dual

A

1

134
Q

What are the 3 character case conversion functions?

A
  1. UPPER2. LOWER3. INITCAP
135
Q

What is returned from the following statement? CONCAT(‘The number is: ‘,5)

A

The number is: 5The concat function can accept text, date or number data types.Oracle implicitly converts other data types to Char?

136
Q

What is returned from the following statement? REPLACE(9999.0 - 1, ‘8’, ‘77’)

A

99977The expression 9999.0 - 1 is implicitly converted to text of 9998.0 then the 8 is replace with 77

137
Q

What is returned from the following statement? MOD(3,2)

A

.5

138
Q

What is returned from the following statement? MOD(3,-2)

A

-0.5

139
Q

What is significant of the return of the following statement? MOD ( number, 2)

A

If the return is zero the number is even.If the return is 1 the number is odd.

140
Q

What is returned from the following statement? SELECT NVL(1234,’ ‘) FROM dual;

A

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
Q

What is returned from the following statement? SELECT TO_NUMBER (‘$3000’) FROM dual;

A

an errorThe TO_NUMBER cannot interpret the $

142
Q

What is returned from the following statement? SELECT TO_NUMBER(‘$3,000.67’,’$999,999.99’) FROM dual;

A

3000.67 as a number type

143
Q

What is returned from the following statement? SELECT NULLIF(1,2-1) FROM dual;

A

NullBoth values are equal

144
Q

What is returned from the following statement? SELECT NULLIF(‘01-JAN-2013’,’01-JAN-13’) FROM dual;

A

01-Jan-2013one string is longer than the other so they are not equal

145
Q

When a return of a function is used as an input to another function, it is termed as?

A

nested functionExample: UPPER ( LOWER (‘test’))

146
Q

Which of the following can be used in the SELECT clause? MIN MAX AVG NVL

A

only the NVL can be used in the SELECT clause

147
Q

Which of the following can be used in the GROUP BY clause? NVL AVG COUNT DECODE

A

only the AVG can be use in the GROUP BY clause

148
Q

What is returned from the following Statement? SELECT COUNT(ALL column1) FROM table1

A

The count ignores the nulls in column1 but counts the duplicatesSame as: SELECT COUNT(column1) FROM Table1

149
Q

What is restrictive of data types with the following functions? AVG() SUM() STDDEV() VARIANCE()

A

all take only 1 numeric data type

150
Q

What is restrictive of the data types with the following functions? MAX() MIN()

A

They can take numeric, character or date data types

151
Q

How many levels deep can Group functions be nested?

A

only 2

152
Q

Can a group level function be inside a single-row function?

A

YesExample: TO_CHAR( MAX (12))

153
Q

Can a single-row function be nested inside a group level function?

A

YesExample: MAX ( TO_CHAR (12))

154
Q

What is the format model for converting the text ‘$9,784.34’ to a numerical value?

A

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
Q

What is returned from the following statement? SELECT ‘this is a ‘ || null || ‘test with nulls’ from dual;

A

this is a test with nullsA null is treated as an empty space in a text

156
Q

What is returned from the following statement? SELECT NULLIF( ‘A’, 1) FROM dual

A

NullThe data types do not match. A text is compare to a number

157
Q

What is returned by this statement? SELECT NVL2(inv_amt, inv_amt * 25, ‘Not Available’)

A

errorbecause one return value is numeric and the other return value is text

158
Q

Is this syntax correct? SELECT COUNT(DISTINCT *) FROM Table1

A

Yes. It will count all the unique records from table1, not counting any duplicate rows.

159
Q

What two data types can the TRUNC function be used with?

A
  1. NUMBER2. DATE
160
Q

A TO_CHAR function can have what 2 data types?

A

Number or date

161
Q

Will this statement execute without errors? SELECT SUM( DISTINCT NVL( subject1,0)) , MAX( subject1) FROM marks WHERE Subject1 > subject2

A

Yes.A DISTINCT can be within a SUM function)The GROUP BY Clause is not needed.

162
Q

Will this statement execute without errors? SELECT AVG(int_date) FROM invoice;

A

No.The AVG function will not take the date data type.

DSO SQL (15 decks)

Brainscape helps you reach your goals faster, through stronger study habits.
© 2024 Bold Learning Solutions. Terms and Conditions