Oracle 1Z0-071 Online Practice Questions and Exam Preparation

Oracle 1Z0-071 Online Questions & Answers

• Question 211:

  Examine this query and its output:

  

  Examine this query with an incomplete WHERE clause:

  

  Which two are true about operators that can be used in the WHERE clause? (Choose two.)

  A. Using ANY will display all the product names except the product named Fork.
  B. Using IN will display all the product names.
  C. Using NOT IN or ANY will give the same result.
  D. Using ANY will display all the product names.
  E. Using NOT IN or ANY will give the same result.
  A. Using ANY will display all the product names except the product named Fork.
  E. Using NOT IN or ANY will give the same result.

• Question 212:

  BOOK_SEQ is an existing sequence in your schema.

  Which two CREATE TABLE commands are valid?

  

  A. Option A
  B. Option B
  C. Option C
  D. Option D
  E. Option E
  A. Option A
  D. Option D

• Question 213:

  Which four statements are true regarding primary and foreign key constraints and the effect they can have on table data?

  A. Only the primary key can be defined at the column and table level.
  B. The foreign key columns and parent table primary key columns must have the same names.
  C. It is possible for child rows that have a foreign key to remain in the child table at the time the parent row is deleted.
  D. A table can have only one primary key but multiple foreign keys.
  E. Primary key and foreign key constraints can be defined at both the column and table level.
  F. A table can have only one primary key and one foreign key.
  G. It is possible for child rows that have a foreign key to be deleted automatically from the child table at the time the parent row is deleted
  C. It is possible for child rows that have a foreign key to remain in the child table at the time the parent row is deleted.
  D. A table can have only one primary key but multiple foreign keys.
  E. Primary key and foreign key constraints can be defined at both the column and table level.
  G. It is possible for child rows that have a foreign key to be deleted automatically from the child table at the time the parent row is deleted

• Question 214:

  Which three are true about multitable INSERT statements? (Choose three.)

  A. They can insert each computed row into more than one table.
  B. They can be performed on remote tables.
  C. They can be performed on external tables using SQL*Loader.
  D. They can be performed on views.
  E. They can be performed only by using a subquery.
  F. They can be performed on relational tables.
  A. They can insert each computed row into more than one table.
  E. They can be performed only by using a subquery.
  F. They can be performed on relational tables.

• Question 215:

  Which three are true about privileges and roles? (Choose three.)

  A. System privileges always set privileges for an entire database.
  B. PUBLIC can be revoked from a user.
  C. All roles are owned by the SYS schema.
  D. A user has all object privileges for every object in their schema by default.
  E. A role is owned by the user who created it.
  F. A role can contain a combination of several privileges and roles.
  G. PUBLIC acts as a default role granted to every user in a database.
  D. A user has all object privileges for every object in their schema by default.
  F. A role can contain a combination of several privileges and roles.
  G. PUBLIC acts as a default role granted to every user in a database.

• Question 216:

  Examine the description of the EMPLOYEES table:

  

  Which statement increases each employeeandapos;s SALARY by the minimum SALARY for their DEPARTM ENT_ID?

  A. UPDATE employees e1 SET salary =(SELECT e2. salary + MIN(e2.salary) FROM employees e2 WHERE e1.department_ id = e2. department_id GROUP BY e2. department_id) ;
  B. UPDATE employees e1 SET salary = salary + (SELECT MIN(e1. salary) FROM employees e2 WHERE e1.department_id = e2 .department_id);
  C. UPDATE employees e1 SET salary = salary+(SELECT MIN (salary) FROM employees e2) ;
  D. UPDATE employees e1 SET salary= (SELECT e1.salary + MIN(e2.salary) FROM employees e2 WHERE e1. department_ id = e2.department_id);
  D. UPDATE employees e1 SET salary= (SELECT e1.salary + MIN(e2.salary) FROM employees e2 WHERE e1. department_ id = e2.department_id);

• Question 217:

  Which two statements are true? (Choose two.)

  A. CASE is a function and DECODE is not.
  B. Neither CASE nor DECODE is a function.
  C. All conditions evaluated using CASE can also be evaluated using DECODE.
  D. All conditions evaluated using DECODE can also be evaluated using CASE.
  E. DECODE is a function and CASE is not.
  F. Both CASE and DECODE are functions.
  D. All conditions evaluated using DECODE can also be evaluated using CASE.
  E. DECODE is a function and CASE is not.

• Question 218:

  Which two statements are true about Data Manipulation Language (DML) statements?

  A. An INSERT INTO...VALUES.. statement can add multiple rows per execution to a table.
  B. An UPDATE... SET... statement can modify multiple rows based on multiple conditions on a table.
  C. A DELETE FROM..... statement can remove rows based on only a single condition on a table.
  D. An INSERT INTO... VALUES..... statement can add a single row based on multiple conditions on a table.
  E. A DELETE FROM..... statement can remove multiple rows based on multiple conditions on a table.
  F. An UPDATE....SET.... statement can modify multiple rows based on only a single condition on a table.
  B. An UPDATE... SET... statement can modify multiple rows based on multiple conditions on a table.
  E. A DELETE FROM..... statement can remove multiple rows based on multiple conditions on a table.

  ---

  Explanation/Reference:

  References: http://www.techonthenet.com/sql/and_or.php

• Question 219:

  Which two are true about savepoints? (Choose two.)

  A. After issuing a savepoint, you can roll back to the savepoint name within the current transaction.
  B. They make uncommitted updates visible to sessions owned by other users.
  C. You can commit updates done between two savepoints without committing other updates in the current transaction.
  D. A ROLLBACK TO SAVEPOINT command issued before the start of a transaction results in an error.
  E. They make uncommitted updates visible to other sessions owned by the same user.
  F. After issuing a savepoint, you cannot roll back the complete transaction.
  A. After issuing a savepoint, you can roll back to the savepoint name within the current transaction.
  D. A ROLLBACK TO SAVEPOINT command issued before the start of a transaction results in an error.

• Question 220:

  View the exhibit and examine the description of the DEPARTMENTS and EMPLOYEES tables.

  

  You wrote this SQL statement to retrieve EMPLOYEE_ID, FIRST_NAME, and DEPARTMENT NAME, for all employees:

  SELECT employee_id, first_name, department_name

  FROM employees

  NATURAL JOIN departments;

  The desired output is not obtained after executing the above SQL statement. What could be the reason for this?

  A. The table prefix is missing for the column names in the SELECT clause.
  B. The NATURAL JOIN clause is missing the USING clause.
  C. The DEPARTMENTS table is not used before the EMPLOYEES table in the FROM clause.
  D. The EMPLOYEES and DEPARTMENTS tables have more than one column with the same column name and data type.
  D. The EMPLOYEES and DEPARTMENTS tables have more than one column with the same column name and data type.

  ---

  Explanation/Reference:

  Natural join needs only one column to be the same in each table. The EMPLOYEES and DEPARTMENTS tables have two columns that are the same (Department_ID and Manager_ID)