Oracle 1z0-071 Exam Questions Free Practice Test

Viewing page 9 out of 10 pages

Viewing questions 81-90 out of questions

Questions # 81:

Which statement is true about the INTERSECT operator used in compound queries?

Options:

It processes NULLS in the selected columns.

INTERSECT is of lower precedence than UNION or UNION ALL.

It ignores NULLS.

Multiple INTERSECT operators are not possible in the same SQL statement.

Expert Solution

Questions # 82:

Which two statements are true about the WHERE and HAVING clauses in a SELECT statement?

Options:

The WHERE clause can be used to exclude rows after dividing them into groups

WHERE and HAVING clauses can be used in the same statement only if applied to different table columns.

The HAVING clause can be used with aggregating functions in subqueries.

Aggregating functions and columns used in HAVING clauses must be specified in these SELECT list of a query.

The WHERE clause can be used to exclude rows before dividing them into groups.

Expert Solution

Questions # 83:

Which three statements are true about Oracle synonyms?

Options:

A synonym cannot be created for a PL /SQL package.

A SEQUENCE can have a synonym.

A synonym can be available to all users .

A synonym created by one user can refer to an object belonging to another user.

Any user can drop a PUBLIC synonym.

Expert Solution

Questions # 84:

Which statement executes successfully?

Options:

SELECT TO_DATE(TO_NUMBER(INTERVATL '800' SECOND)) FROM DUAL;

SELECT TO_NUMBER(INTERVAL'800' SECOND, 'HH24:MM') FROM DUAL;

SELECT TO_DATE(INTERVAL '800' SECOND,'HH24:MM') FROM DUAL;

SELECT TO_NUWBER(TO_DATE(INTERVAL '800' SECOND)) FROM DUAL;

SELECT TO_CHAR(INTERVAL '800' SECOND, 'HH24:MM') FROM DUAL;

Expert Solution

Questions # 85:

Examine these statements:

CREATE TABLE alter_test (c1 VARCHAR2(10), c2 NUMBER(10));

INSERT INTO alter_test VALUES ('123'， 123);

COMMIT;

Which is true ahout modifyIng the columns in AITER_TEST?

Options:

c1 can be changed to NUMBER(10) and c2 can be changed to VARCHAN2 (10).

c2 can be changed to NUMBER(5) but c1 cannot be changed to VARCHAN2 (5).

c2 can be changed to VARCHAR2(10) but c1 cannot be changed to NUMBER (10).

c1 can be changed to NUMBER(10) but c2 cannot be changed to VARCHAN2 (10).

c1 can be changed to VARCHAR2(5) and c2 can be changed to NUMBER (12,2).

Expert Solution

Questions # 86:

Examine these statements:

CREATE TABLE dept (

deptno NUMBER PRIMARY KEY,

diname VARCHAR2(10) ,

mgr NUMBER ,

CONSTRAINT dept_fkey FOREIGN KEY(mgr) REFERENCES emp (empno));

CREATE TABLE emp (

Empno NUMBER PRIMARY KEY,

Ename VARCHAR2 (10) ,

deptno NUMBER,

CONSTRAINT emp_fkey FOREIGN KEY (deptno) REFERENCES dept (deptno) DISABLE);

ALTER TABLE emp MODIFY CONSTRAINT emp_fkey ENABLE;

Which two are true?

Options:

The MGR column in the DEPT table will not be able to contain NULL values.

The CREATE TABLE EMP statement must precede the CREATE TABLE DEPT statement for all threestatements to execute successfully.

Both foreign key constraint definitions must be removed from the CREATE TABLE statements, andbe added with ALTER TABLE statements once both tables are created, for the two CREATE TABLEstatements to

execute successfully in the order shown.

The DEFT FKEY constraint definition must be removed from the CREATE TABLE DEF statement.and be added with an AITER TABLE statement once both tables are created, for the two CREATE TABLE statements

to execute successfully in the order shown.

The Deptno column in the emp table will be able to contain nulls values.

All three statements execute successfully in the order shown

Expert Solution

Questions # 87:

Examine this SQL statement:

SELECT cust_id, cus_last_name "Last Name"

FROM customers

WHERE country_id = 10

UNION

SELECT cust_id CUST_NO, cust_last_name

FROM customers

WHERE country_id = 30

Identify three ORDER BY clauses, any one of which can complete the query successfully.

Options:

ORDERBY 2, 1

ORDER BY "CUST_NO"

ORDER BY 2,cust_id

ORDER BY CUST_NO

ORDER BY "Last Name"

Expert Solution

Questions # 88:

You create a table by using this command:

CREATE TABLE rate_list (rate NUMBER(6,2));

Which two are true about executing statements?

Options:

INSERT INTO rate_list VALUES (-.9) inserts the value as -.9.

INSERT INTO rate_list VALUES (0.999) produces an error.

INSERT INTO rate_list VALUES (-10) produces an error.

INSERT INTO rate_list VALUES (87654. 556) inserts the value as 87654.6.

INSERT INTO rate_list VALUES (0.551) inserts the value as .55.

INSERT INTO rate_list VALUES (-99.99) inserts the value as 99.99.

Expert Solution

Questions # 89:

Examine the description of the EMPLOYEES table:

Question # 89

The session time zone is the same as the database server

Which two statements will list only the employees who have been working with the company for more than five years?

Options:

SELECT employee_ name FROM employees WHERE (SYSDATE – hire_ data) / 365>5

SELECT employee_ name FROM employees WHERE (SYSTIMESTAMP – hire_ data) / 365>

SELECT employee_ name FROM employees WHERE (CUARENT_ DATE – hire_ data / 365>5

SELECT employee_ name FROM employees WHERE (SYSNAYW – hire_ data / 12> 3

SELECT employee_ name FROM employees WHERE (CUNACV_ DATE – hire_ data / 12> 3

Expert Solution

Questions # 90:

Question # 90

Which two queries will result in an error?

Options:

SELECT FIRST_NAME LAST_NAME FROM EMPLOYEES;

SELECT FIRST_NAME,LAST_NAME FROM EMPLOYEES;

SELECT LAST_NAME,12 * SALARY AS ANNUAL_SALARY

FROM EMPLOYEES

WHERE ANNUAL_SALARY > 100000

ORDER BY 12 * SALARY ;

SELECT LAST_NAME,12 * SALARY AS ANNUAL_SALARY

FROM EMPLOYEES

WHERE 12 * SALARY > 100000

ORDER BY ANNUAL_SALARY;

SELECT LAST_NAME,12 * SALARY AS ANNUAL_SALARY

FROM EMPLOYEES

WHERE 12 * SALARY > 100000

ORDER BY 12 * SALARY;

SELECT LAST_NAME,12 * SALARY AS ANNUAL_SALARY

FROM EMPLOYEES

WHERE ANNUAL_SALARY > 100000

ORDER BY ANNUAL_SALARY;

Expert Solution

Viewing page 9 out of 10 pages

Viewing questions 81-90 out of questions

Pass the Oracle Oracle PL 1z0-071 Questions and answers with CertsForce