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

Question : You want to display percent of the employees with the highest salaries in the EMPLOYEES
table. Which query will generate the required result?

1. A
2. B
3. Access Mostly Uused Products by 50000+ Subscribers
4. D

Correct Answer : Get Lastest Questions and Answer :
Let's see the first 5% rows of the total
rows according to the salary in descending way.
Let's consider the solution in Oracle 12c, where we can use the
new FETCH {FIRST|NEXT} (pct) PERCENT ROWS clause:
SELECT /*+ GATHER_PLAN_STATISTICS */ employee_id, last_name, salary
FROM employees ORDER BY salary DESC FETCH FIRST 5 PERCENT ROWS
ONLY;

EMPLOYEE_ID LAST_NAME SALARY
----------- ------------ --------
100 King 24000
101 Kochhar 17000
102 De Haan 17000
145 Russell 14000
146 Partners 13500
201 Hartstein 13000






Question : In the customers table, the CUST_CITY column contains the value 'Paris' for the
CUST_FIRST_NAME 'Abigail'.
Evaluate the given query:
What would be the outcome?


1. Abigail PA
2. Abigail Pa
3. Access Mostly Uused Products by 50000+ Subscribers
4. An error message

Correct Answer : Get Lastest Questions and Answer :

Explanation: INITCAP returns char, with the first letter of each word in uppercase, all other letters in lowercase. Words are delimited by white space or characters that are not alphanumeric.

char can be of any of the datatypes CHAR, VARCHAR2, NCHAR, or NVARCHAR2. The return value is the same datatype as char. The database sets the case of the initial characters based on the binary mapping defined for the underlying character set. For linguistic-sensitive uppercase and lowercase, please refer to NLS_INITCAP.

This function does not support CLOB data directly. However, CLOBs can be passed in as arguments through implicit data conversion.
The following example capitalizes each word in the string:

SELECT INITCAP('the soap') "Capitals" FROM DUAL;

Capitals
---------
The Soap








Question : View the Exhibit and evaluate the structure and data in the CUST_STATUS table.
You issue the given SQL statement:
Which statement is true regarding the execution of the above query?

1. It produces an error because the AMT_SPENT column contains a null value.
2. It displays a bonus of 1000 for all customers whose AMT_SPENT is less than CREDIT_LIMIT.
3. Access Mostly Uused Products by 50000+ Subscribers
4. It produces an error because the TO_NUMBER function must be used to convert the result of
the NULLIF function before it can be used by the NVL2 function.



Correct Answer : Get Lastest Questions and Answer :
The NULLIF Function
The NULLIF function tests two terms for equality. If they are equal the function returns a null, else
it returns the first of the two terms tested.
The NULLIF function takes two mandatory parameters of any data type. The syntax is
NULLIF(ifunequal, comparison_term), where the parameters ifunequal and
comparison_term are compared. If they are identical, then NULL is returned. If they differ, the
ifunequal parameter is returned.

Related Questions

---

Question : Which comparison operator cannot be used with multiple-row subqueries?
A. ALL
B. ANY
C. IN
D. NOT IN



1. A,B
2. B,C
3. Access Mostly Uused Products by 50000+ Subscribers
4. A,B,C
5. A,B,C,D





Question : Which of these set operators will not sort the rows?

1. INTERSECT
2. MINUS
3. Access Mostly Uused Products by 50000+ Subscribers
4. UNION ALL




Question : Which of these operators will remove duplicate rows from the final result?



1. INTERSECT
2. MINUS
3. Access Mostly Uused Products by 50000+ Subscribers
4. UNION ALL
4. 1,2 and 3



Question : If a compound query contains both a MINUS and an INTERSECT operator, which will be applied first?


1. The INTERSECT, because INTERSECT has higher precedence than MINUS.
2. The MINUS, because MINUS has a higher precedence than INTERSECT.
3. Access Mostly Uused Products by 50000+ Subscribers
4. It is not possible for a compound query to include both MINUS and INTERSECT.




Question :Which of the following commands can not be rolled back?
A. COMMIT
B. DELETE
C. INSERT
D. MERGE
E. TRUNCATE
F. UPDATE



1. A,C
2. B,D
3. Access Mostly Uused Products by 50000+ Subscribers
4. C,D
5. B,E


Question : How can you change the primary key value of a row?


1. You cannot change the primary key value.
2. Change it with a simple UPDATE statement.
3. Access Mostly Uused Products by 50000+ Subscribers
4. This is only possible if the row is first locked with a SELECT FOR UPDATE.