Oracle Advanced SQL and PL/SQL Developer Certification Questions and Answers (Dumps and Practice Questions)

Question : What value is returned after executing the following statement?
select CAST( '22-Aug-2003' AS varchar2(30) ) from dual;

1. This would convert the date (ie: 22-Aug-2003) into a varchar2(30) value.
2. This would convert the date (ie: 22-Aug-2003) into a Number(30) value.
3. Access Mostly Uused Products by 50000+ Subscribers
4. Error



Correct Answer : Get Lastest Questions and Answer :
Exp:






Question : What value is returned after executing the following statement?
CEIL(-32.65)

1. -33
2. -32
3. Access Mostly Uused Products by 50000+ Subscribers
4. -33.0



Correct Answer : Get Lastest Questions and Answer :
Exp: The syntax for the Oracle/PLSQL CEIL function is:

CEIL( number )
Parameters or Arguments

number is the value used to find the smallest integer value.






Question : Select the statement which is equivalent to below block
IF address1 is not null THEN
result := address1;

ELSIF address2 is not null THEN
result := address2;

ELSIF address3 is not null THEN
result := address3;

ELSE
result := null;

END IF;

1. SELECT COMPOSE( address1, address2, address3 ) result
FROM suppliers;
2. SELECT COALESCE( address1, address2, address3 ) result
FROM suppliers;
3. Access Mostly Uused Products by 50000+ Subscribers
FROM suppliers;
4. SELECT NVL( address1, address2, address3 ) result
FROM suppliers;



Correct Answer : Get Lastest Questions and Answer :
Exp: The Oracle/PLSQL COALESCE function returns the first non-null expression in the list. If all expressions evaluate to null, then the COALESCE function will return null. SYNTAX
The syntax for the Oracle/PLSQL COALESCE function is:
COALESCE( expr1, expr2, ... expr_n )
Parameters or Arguments : expr1 to expr_n are the expressions to test for non-null values.
The COALESCE function can be used in Oracle/PLSQL. You could use the coalesce function in a SQL statement as follows:
SELECT COALESCE( address1, address2, address3 ) result
FROM suppliers;
The COALESCE function will compare each value, one by one.

Related Questions

---

Question : Select the clauses of the SELECT statement which help for selection and projection?

1. SELECT, FROM
2. ORDER BY, WHERE
3. Access Mostly Uused Products by 50000+ Subscribers
4. SELECT, ORDER BY


Question : Which of the following WHERE clauses contains an error? The SELECT and FROM clauses are SELECT * FROM HADOOPEXAM:

1. WHERE JOIN_DATE IN ('02-JUN-2004');
2. WHERE USER_ID IN ('1000','4000','2000');
3. Access Mostly Uused Products by 50000+ Subscribers
4. WHERE RANK BETWEEN 0.1 AND 0.5;



Question : Choose the WHERE clause that extracts the USER_NAME values containing the character literal "ab" from the DEPARTMENTS table.
The SELECT and FROM clauses are SELECT USER_NAME FROM DEPARTMENTS:

1. WHERE USER_NAME IN ('%a%b');
2. WHERE USER_NAME LIKE '%ab%';
3. Access Mostly Uused Products by 50000+ Subscribers
4. WHERE USER_NAME CONTAINS 'a%b';




Question : Select the statement about the ORDER BY clause which is not correct.

1. When using the ORDER BY clause, it always appears as the last clause in a SELECT statement.
2. The ORDER BY clause may appear in a SELECT statement that does not contain a WHERE clause.
3. Access Mostly Uused Products by 50000+ Subscribers
4. Positional sorting is accomplished by specifying the numeric position of a column as it appears in the SELECT list, in the ORDER BY clause.





Question : If a table is created without specifying a schema, in which schema will it be?

1. It will be an orphaned table, without a schema.
2. The creation will fail.
3. Access Mostly Uused Products by 50000+ Subscribers
4. It will be in the PUBLIC schema.


Question : How can you delete the values from one column of every row in a table? (Choose the best answer.)

1. Use the DELETE COLUMN command.
2. Use the TRUNCATE COLUMN command.
3. Access Mostly Uused Products by 50000+ Subscribers
4. Use the DROP COLUMN command.