Oracle Oracle Certifications 1Z0-071 Questions & Answers

• Question 81:

  Sales data of a company is stored in two tables, SALES1 and SALES2, with some data being duplicated across the tables. You want to display the results from the SALES1 table, Which are not present in the SALES2 table.

  

  Which set operator generates the required output?

  A. INTERSECT

  B. UNION

  C. PLUS

  D. MINUS

  E. SUBTRACT

  Correct Answer: D

  References: https://docs.oracle.com/cd/B19306_01/server.102/b14200/queries004.htm

• Question 82:

  Evaluate the following ALTER TABLE statement:

  ALTER TABLE orders

  SET UNUSED (order_date);

  Which statement is true?

  A. After executing the ALTER TABLE command, you can add a new column called ORDER_DATE to the ORDERS table.

  B. The ORDER_DATE column must be empty for the ALTER TABLE command to execute successfully.

  C. ROLLBACK can be used to get back the ORDER_DATE column in the ORDERS table.

  D. The DESCRIBE command would still display the ORDER_DATE column.

  Correct Answer: A

• Question 83:

  Evaluate the following SQL statements that are issued in the given order:

  CREATE TABLE emp

  (emp_no NUMBER(2) CONSTRAINT emp_emp_no_pk PRIMARY KEY,

  ename VARCHAR2(15),

  salary NUMBER (8,2),

  mgr_no NUMBER(2) CONSTRAINT emp_mgr_fk REFERENCES emp(emp_no));

  ALTER TABLE emp

  DISABLE CONSTRAINT emp_emp_no_pk CASCADE;

  ALTER TABLE emp

  ENABLE CONSTRAINT emp_emp_no_pk;

  What would be the status of the foreign key EMP_MGR_PK?

  A. It would remain disabled and can be enabled only by dropping the foreign key constraint and recreating it.

  B. It would remain disabled and has to be enabled manually using the ALTER TABLE command.

  C. It would be automatically enabled and immediate.

  D. It would be automatically enabled and deferred.

  Correct Answer: B

• Question 84:

  Which three statements are true regarding the data types? (Choose three.)

  A. The minimum column width that can be specified for a VARCHAR2 data type column is one.

  B. Only one LONG column can be used per table.

  C. A TIMESTAMP data type column stores only time values with fractional seconds.

  D. The BLOB data type column is used to store binary data in an operating system file.

  E. The value for a CHAR data type column is blank-padded to the maximum defined column width.

  Correct Answer: ABE

• Question 85:

  Which three statements are true regarding subqueries? (Choose three.)

  A. Multiple columns or expressions can be compared between the main query and subquery.

  B. Subqueries can contain ORDER BY but not the GROUP BY clause.

  C. Main query and subquery can get data from different tables.

  D. Subqueries can contain GROUP BY and ORDER BY clauses.

  E. Main query and subquery must get data from the same tables.

  F. Only one column or expression can be compared between the main query and subquery.

  Correct Answer: ACD

  References: http://docs.oracle.com/javadb/10.6.2.1/ref/rrefsqlj13658.html

• Question 86:

  Which statement is true regarding the default behavior of the ORDER BY clause?

  A. In a character sort, the values are case-sensitive.

  B. NULL values are not considered at all by the sort operation.

  C. Only those columns that are specified in the SELECT list can be used in the ORDER BY clause.

  D. Numeric values are displayed from the maximum to the minimum value if they have decimal positions.

  Correct Answer: A

• Question 87:

  Examine the structure of the MEMBERS table.

  

  Which query can be used to display the last names and city names only for members from the states MO and MI?

  A. SELECT last_name, city FROM members WHERE state ='MO' AND state ='MI';

  B. SELECT last_name, city FROM members WHERE state LIKE 'M%';

  C. SELECT last_name, city FROM members WHERE state IN ('MO', 'MI');

  D. SELECT DISTINCT last_name, city FROM members WHERE state ='MO' OR state ='MI';

  Correct Answer: C

• Question 88:

  Which two statements are true regarding roles? (Choose two.)

  A. A role can be granted to itself.

  B. A role can be granted to PUBLIC.

  C. A user can be granted only one role at any point of time.

  D. The REVOKE command can be used to remove privileges but not roles from other users.

  E. Roles are named groups of related privileges that can be granted to users or other roles.

  Correct Answer: BE

  References: http://docs.oracle.com/cd/E25054_01/network.1111/e16543/authorization.htm#autoId28

• Question 89:

  Which two statements are true regarding constraints? (Choose two)

  A. A constraint is enforced only for an INSERT operation on a table.

  B. A foreign key cannot contain NULL values.

  C. A column with the UNIQUE constraint can store NULLS.

  D. You can have more than one column in a table as part of a primary key.

  Correct Answer: CD

• Question 90:

  Evaluate the following statement.

  

  Which statement is true regarding the evaluation of rows returned by the subquery in the INSERT statement?

  A. They are evaluated by all the three WHEN clauses regardless of the results of the evaluation of any other WHEN clause.

  B. They are evaluated by the first WHEN clause. If the condition is true, then the row would be evaluated by the subsequent WHEN clauses.

  C. They are evaluated by the first WHEN clause. If the condition is false, then the row would be evaluated by the subsequent WHEN clauses.

  D. The insert statement would give an error because the ELSE clause is not present for support in case none of WHEN clauses are true.

  Correct Answer: A

  References: http://psoug.org/definition/WHEN.htm