Oracle Oracle Certifications 1Z0-071 Questions & Answers

• Question 261:

  Which two statements are true about a self join?

  A. It can be a left outer join.

  B. It must be an equijoin.

  C. It must be a full outer join.

  D. The join key column must have an index.

  E. It can be an inner join.

  Correct Answer: AE

• Question 262:

  Examine the description of the BOOKS table:

  

  The table has 100 rows.

  Examine this sequence of statements issued in a new session:

  INSERT INTO books VALUES ('ADV112', 'Adventures of Tom Sawyer', NULL, NULL);

  SAVEPOINT a;

  DELETE FROM books;

  ROLLBACK TO SAVEPOINT a;

  ROLLBACK;

  Which two statements are true? (Choose two.)

  A. The second ROLLBACK command replays the delete.

  B. The first ROLLBACK command restores the 101 rows that were deleted and commits the inserted row.

  C. The first ROLLBACK command restores the 101 rows that were deleted, leaving the inserted row still to be committed.

  D. The second ROLLBACK command undoes the insert.

  E. The second ROLLBACK command does nothing.

  Correct Answer: CD

• Question 263:

  Which two statements are true about external tables? (Choose two.)

  A. Indexes can be created on them.

  B. You can populate them from existing data in the database by using the CREATE TABLE AS SELECT command.

  C. DML statements cannot be used on them.

  D. Their data can be retrieved by using only SQL or PL/SQL.

  E. Their metadata and actual data are both stored outside the database.

  Correct Answer: BC

• Question 264:

  View the Exhibit and examine the structure of the ORDERS table.

  The columns ORDER_MODE and ORDER_TOTAL have the default values `direct' and 0 respectively.

  

  Which two INSERT statements are valid? (Choose two.)

  A. INSERT INTO orders VALUES (1,'09-mar-2007', `online','', 1000);

  B. INSERT INTO orders (order_id,order_date,order_mode, (customer_id,order_total) VALUES (1,TO_DATE(NULL), `online', 101, NULL);

  C. INSERT INTO (SELECT order_id,order_date,customer_id FROM orders) VALUES (1,'09-mar-2007', 101);

  D. INSERT INTO orders VALUES (1,'09-mar-2007', DEFAULT, 101, DEFAULT);

  E. INSERT INTO orders (order_id,order_date,order_mode,order_total) VALUES (1,'10-mar-2007',`online',1000);

  Correct Answer: CD

• Question 265:

  Examine this schema information:

  1.

  EMPLOYEES.DEPARTMENT_ID has a foreign key referencing DEPARTMENTS.DEPARTMENT_ID.

  2.

  EMP_VIEW is based on the EMPLOYEES and DEPARTMENTS tables.

  3.

  EMP_VIEW has columns EMPLOYEE_ID, EMPLOYEE_NAME and DEPARTMENT_NAME.

  You must add a new column, MANAGER_ID, from the EMPLOYEES table, to the view, showing each employee's manager.

  Which statement will do this?

  A. ALTER VIEW emp_view ADD (employee.manager_id);

  B. ALTER VIEW emp_view MODIFY ( SELECT employee_id, employee_name, department_name, manager_id FROM employees e, departments d WHERE e.department_id = d.department_id);

  C. ALTER VIEW emp_view ADD (SELECT manager_id FROM employees);

  D. CREATE OR REPLACE VIEW emp_view AS SELECT employee_id, employee_name, department_name, manager_id FROM employees e, departments d WHERE e.department_id = d.department_id;

  Correct Answer: D

• Question 266:

  Which two join conditions in a FROM clause are non-equijoins? (Choose two.)

  A. table1 JOIN table2 USING (column1, column2)

  B. table1 JOIN table2 ON (table1.column BETWEEN table2.column1 AND table2.column2)

  C. table1 NATURAL JOIN table2

  D. table1 JOIN table2 ON (table1.column >= table2.column)

  E. table1 JOIN table2 ON (table1.column = table2.column) WHERE table2.column LIKE `A%'

  Correct Answer: BD

• Question 267:

  Examine this query:

  SELECT 2 FROM dual d1 CROSS JOIN dual d2 CROSS JOIN dual d3;

  What is returned upon execution?

  A. 0 rows

  B. an error

  C. 8 rows

  D. 6 rows

  E. 1 row

  F. 3 rows

  Correct Answer: E

• Question 268:

  Which two statements are true about an Oracle database?

  A. A table can have multiple primary keys.

  B. A table can have multiple foreign keys.

  C. A NUMBER column without data has a zero value.

  D. A column definition can specify multiple data types.

  E. A VARCHAR2 column without data has a NULL value.

  Correct Answer: BE

• Question 269:

  Examine the description of the BRICKS table;

  

  Examine the description of the BRICKS_STAGE table;

  

  Which two queries execute successfully?

  

  A. Option A

  B. Option B

  C. Option C

  D. Option D

  E. Option E

  Correct Answer: AD

• Question 270:

  Which two statements are true about date/time functions in a session where NLS_DATE_PORMAT is set to DD-MON-YYYY SH24:MI:SS

  A. SYSDATE can be used in expressions only if the default date format is DD-MON-RR.

  B. CURRENT_TIMESTAMP returns the same date as CURRENT_DATE.

  C. CURRENT_DATE returns the current date and time as per the session time zone

  D. SYSDATE and CURRENT_DATE return the current date and time set for the operating system of the database server.

  E. CURRENT_TIMESTAMP returns the same date and time as SYSDATE with additional details of functional seconds.

  F. SYSDATE can be queried only from the DUAL table.

  Correct Answer: CE