Oracle__5. Oracle 1Z0-051 Exam - Functions Flashcards
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;
The first SELECT will not count any nulls in column a, while the second SELECT will count all rows
What is the simplest syntax to return all columns of a table as well as AVG(column1)?
SELECT t1., AVG(column1)FROM T1GROUP BY T1.
What function can be used in the WHERE clause if you want all records from column1 from table1 ending with an ‘n’?
WHERE SUBSTR(column,-1,1) = ‘n’
“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””?”
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
What is returned from the following statement? SELECT SUBSTR( ‘ABCdefgh’, 2, 4) FROM dual
BCde
What is returned from the following statement? SELECT SUBSTR( ‘ABCdefgh’, 5) FROM dual
efghIf the length is not specific all remaining characters are returned
What is returned from the following statement? SELECT SUBSTR( ‘ABCdefgh’, -3, 2) FROM dual
fg
Notice the spaces before and after the string, what is returned from the following statement? SELECT TRIM( ‘ Tech ‘) FROM dual
Tech spaces in front and after have been removed
What is returned from the following statement? SELECT TRIM( ‘T’ FROM ‘Tech’) FROM dual
ech
What is returned from the following statement? SELECT TRIM(LEADING ‘0’ FROM ‘0001240’) FROM dual
1240
What is returned from the following statement? SELECT TRIM(TRAILING ‘x’ FROM ‘xTechx’) FROM dual
xTech
What is returned from the following statement? SELECT TRIM( BOTH ‘1’ FROM ‘1Tech111’) FROM dual
Tech
What is returned from the following statement? SELECT RTRIM( ‘techxyxyxyxxx’, ‘xyz’) FROM dual
techThe RTRIM remove all characters that match regardless of pattern
What is returned from the following statement? SELECT LTRIM( ‘1443Tech1545’, ‘431’) FROM dual
Tech1545
What is returned from the following statement? SELECT INITCAP( ‘GEORGE BURNS’) FROM dual
George BurnsInitCap capitalizes the first letter of each word
What is returned from the following statement? SELECT UPPER( ‘GeorGe Burns 123’) FROM dual
GEORGE BURNS 123All letters are capitalized
What is returned from the following statement? SELECT LOWER( ‘GeorGe Burns 123’) FROM dual
george burns 123All letters are lower case
What is returned from the following statement? SELECT LENGTH( null) FROM dual
null
What is returned from the following statement? SELECT LENGTH( ‘’) FROM dual
null
What is returned from the following statement? SELECT LENGTH( ‘ ‘) FROM dual
1There is one space
What is returned from the following statement? SELECT LENGTH( ‘test’) FROM dual
4
What is returned from the following statement? SELECT CONCAT( ‘A’, ‘B’) FROM dual
AB
What is returned from the following statement? SELECT ‘A’ || ‘B’ FROM dual
AB
Using the CONTACT function what is the syntax for concatenating ‘A’, ‘B’ and ‘C’?
CONCAT( CONCAT( ‘A’, ‘B’), ‘C’)2 nested CONCAT Functions, because the CONCAT function can only take 2 arguments
Using the || operator what is the syntax for concatenating ‘A’,’B’ and ‘C’?
‘A’ || ‘B’ || ‘C’
What is returned from the following statement? SELECT REPLACE( ‘123123Tech’, ‘123’, ‘2’) FROM dual
222222Tech
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
What is returned from the following statement? SELECT INSTR( ‘abcabc’, ‘bc’) FROM dual
2INSTR function finds the first occurrence of the string
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.
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
What is returned from the following statement? SELECT LPAD( ‘1234’, 6, ‘*’) FROM dual
**1234
What is returned from the following statement? SELECT LPAD( ‘1234’, ‘2’, ‘*’) FROM dual
1234
What is returned from the following statement? SELECT RPAD( ‘1234’, 6, ‘*’) FROM dual
1234**
What is returned from the following statement? SELECT RPAD( ‘1234’, ‘2, ‘*’) FROM dual
1234
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
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
What is returned from the following statement? SELECT CHR( 84) FROM dual
T
What is returned from the following statement? SELECT ABS( -23.65) FROM dual
23.65
What is returned from the following statement? SELECT ROUND( 125.612) FROM dual
126The value of zero is assumed
What is returned from the following statement? SELECT ROUND( 125.661, 1) FROM dual
125.7
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
What is returned from the following statement? SELECT TRUNC( 125.8) FROM dual
125
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
What is returned from the following statement? SELECT TRUNC( 125.612, 1) FROM dual
125.6
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 (+/-)
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 (+/-)
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 (+/-)
what is returned from the following statement? SELECT TO_NUMBER( ‘1210.73’) FROM dual
1210.73
What is returned from the following statement? SELECT TO_NUMBER( ‘1210.73’, ‘999.99’) FROM dual
1210.73
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
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
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
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
What is returned from the following statement? SELECT NULLIF( 12, 12) FROM dual
nullThe NULLIF function returns a null if both arguments are equal
What is returned from the following statement? SELECT NULLIF( 12, 13) FROM dual
12
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
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.
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.
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.
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.
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.
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%’
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
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