Premium

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



Question : Which create table statement is valid?
: Which create table statement is valid?
1. A
2. B
3. C
4. D

Correct Answer : 4
Explanation: PRIMARY KEY Constraint
A PRIMARY KEY constraint creates a primary key for the table. Only one primary key can be
created for each table. The PRIMARY KEY constraint is a column or a set of columns that
uniquely identifies each row in a table. This constraint enforces the uniqueness of the column or
column combination and ensures that no column that is part of the primary key can contain a null
value.
Note: Because uniqueness is part of the primary key constraint definition, the Oracle server
enforces the uniqueness by implicitly creating a unique index on the primary key column or
columns.





Question : Which statement is true regarding the UNION operator?
: Which statement is true regarding the UNION operator?
1. By default, the output is not sorted.
2. Null values are not ignored during duplicate checking.
3. Names of all columns must be identical across all select statements.
4. The number of columns selected in all select statements need not be the same.

Correct Answer : 4
Explanation: The SQL UNION query allows you to combine the result sets of two or more SQL SELECT
statements. It removes duplicate rows between the various SELECT statements.
Each SQL SELECT statement within the UNION query must have the same number of fields in the
result sets with similar data types. You can combine multiple queries using the set operators UNION, UNION ALL, INTERSECT, and MINUS. All set operators have equal precedence. If a SQL statement contains multiple set operators, then Oracle Database evaluates them from the left to right unless parentheses explicitly specify another order.

The corresponding expressions in the select lists of the component queries of a compound query must match in number and must be in the same datatype group (such as numeric or character).

If component queries select character data, then the datatype of the return values are determined as follows:
If both queries select values of datatype CHAR, then the returned values have datatype CHAR.
If either or both of the queries select values of datatype VARCHAR2, then the returned values have datatype VARCHAR2.
If component queries select numeric data, then the datatype of the return values is determined by numeric precedence:
If any query selects values of type BINARY_DOUBLE, then the returned values have datatype BINARY_DOUBLE.
If no query selects values of type BINARY_DOUBLE but any query selects values of type BINARY_FLOAT, then the returned values have datatype BINARY_FLOAT.
If all queries select values of type number, then the returned values have datatype NUMBER.
In queries using set operators, Oracle does not perform implicit conversion across datatype groups. Therefore, if the corresponding expressions of component queries resolve to both character data and numeric data, Oracle returns an error.






Question :
:
1. A,C
2. B,C
3. A,B
4. B,D
4. C,D


Correct Answer : 5
Explanation: All of them work, the difference is in the TRIM.
A and B do not TRIM
fxMonth
=> FX = Requires exact matching between the character data and the format model.
C and D does
fmMonth
=> FM = Returns a value with no leading or trailing blanks.


Related Questions

Question : Which join is performed by the following query? (Choose the best answer.)
SELECT E.USER_ID, J.CLICK_ID FROM TABLE1 E and TABLE2 J ON (E.CLICKS < J.MAX_CLICKS);
 : Which join is performed by the following query? (Choose the best answer.)
1. Equijoin
2. Nonequijoin
3. Access Mostly Uused Products by 50000+ Subscribers
4. Outer join





Question : Which of the following statements are syntactically correct?
 : Which of the following statements are syntactically correct?
1. SELECT * FROM EMPLOYEES E JOIN DEPARTMENTS D USING (DEPARTMENT_ID);
2. SELECT * FROM EMPLOYEES JOIN DEPARTMENTS D USING (D.DEPARTMENT_ID);
 3. Access Mostly Uused Products by 50000+ Subscribers
4. None of the above





Question : The EMPLOYEES and DEPARTMENTS tables have two identically named columns: DEPARTMENT_ID and MANAGER_ID.
Which of these statements joins these tables based only on common DEPARTMENT_ID values?
 : The EMPLOYEES and DEPARTMENTS tables have two identically named columns: DEPARTMENT_ID and MANAGER_ID.
1. SELECT * FROM EMPLOYEES NATURAL JOIN DEPARTMENTS;
2. SELECT * FROM EMPLOYEES E NATURAL JOIN DEPARTMENTS D ON E.DEPARTMENT_ID=D.DEPARTMENT_ID;
 3. Access Mostly Uused Products by 50000+ Subscribers
4. None of the above





Question : Which of the following statements are syntactically correct?

: Which of the following statements are syntactically correct?
1. SELECT E.EMPLOYEE_ID, J.JOB_ID PREVIOUS_JOB, E.JOB_ID CURRENT_JOB FROM JOB_HISTORY J CROSS JOIN EMPLOYEES E ON (J.START_DATE=E.HIRE_DATE);
2. SELECT E.EMPLOYEE_ID, J.JOB_ID PREVIOUS_JOB, E.JOB_ID CURRENT_JOB FROM JOB_HISTORY J JOIN EMPLOYEES E ON (J.START_DATE=E.HIRE_DATE);
 3. Access Mostly Uused Products by 50000+ Subscribers
4. None of the above





Question : If the REGIONS table, which contains rows, is cross joined to the COUNTRIES table, which contains rows,
how many rows appear in the final results set? (Choose the best answer.)
 : If the REGIONS table, which contains rows, is cross joined to the COUNTRIES table, which contains rows,
1. 100
2. 4
 3. Access Mostly Uused Products by 50000+ Subscribers
4. None of the above





Question :Which three SQL statements would display the value . as $, .?
 :Which three SQL statements would display the value . as $, .?
1. A,B,C
2. B,C,D
 3. Access Mostly Uused Products by 50000+ Subscribers
4. D,E,F
5. A,D,E