Oracle 1Z0-071 Online Practice Questions and Exam Preparation

Oracle 1Z0-071 Online Questions & Answers

• Question 121:

  View the exhibit and examine the descriptions of the DEPT and LOCATIONS tables.

  

  You want to update the CITY column of the DEPT table for all the rows with the corresponding value in the CITY column of the LOCATIONS table for each department.

  Which SQL statement would you execute to accomplish the task?

  A. UPDATE dept d SET city = ALL (SELECT city FROM locations l WHERE d.location_id = l.location_id);
  B. UPDATE dept d SET city = (SELECT city FROM locations l) WHERE d.location_id = l.location_id;
  C. UPDATE dept d SET city = ANY (SELECT city FROM locations l)
  D. UPDATE dept d SET city = (SELECT city FROM locations l WHERE d.location_id = l.location_id);
  D. UPDATE dept d SET city = (SELECT city FROM locations l WHERE d.location_id = l.location_id);

• Question 122:

  Examine this command:

  TRUNCATE TABLE test;

  Table truncated.

  Which two are true? (Choose two.)

  A. The structure of the TEST table is removed.
  B. All the indexes on the TEST table are dropped.
  C. All the constraints on the TEST table are dropped.
  D. Removed rows can not be recovered using the ROLLBACK command.
  E. All the rows in the TEST table are removed.
  B. All the indexes on the TEST table are dropped.
  E. All the rows in the TEST table are removed.

• Question 123:

  Examine this query:

  SELECT SUBSTR (SYSDATE,1,5) `Result' FROM DUAL

  Which statement is true?

  A. It fails unless the expression is modified to TO-CHAR(SUNBSTR(SYSDATE,1,5)
  B. It fails unless the expression is modified to SUBSTR (TO_ CHAR(SYSDATE),1,5)
  C. It fails unless the expression is modified to SUBSTR (TO_ CHAR(TRUNC(SYSDATE)),1,5)
  D. It executes successfully with an implicit data type conversion
  D. It executes successfully with an implicit data type conversion

• Question 124:

  Which three statements are true about indexes and their administration in an Oracle database? (Choose three.)

  A. The same table column can be part of a unique and non-unique index.
  B. A descending index is a type of function-based index.
  C. An INVINSIBLE index is not maintained when DML is performed on its underlying table.
  D. If a query filters on an indexed column then it will always be used during execution of the query.
  E. An index can be created as part of a CREATE TABLE statement.
  F. An UNUSABLE index is maintained when DML is performed on its underlying table.
  A. The same table column can be part of a unique and non-unique index.
  B. A descending index is a type of function-based index.
  F. An UNUSABLE index is maintained when DML is performed on its underlying table.

• Question 125:

  Which three statements are true about the Oracle join and ANSI Join syntax?

  A. The Oracle join syntax only supports right outer joins,
  B. The Oracle join syntax supports creation of a Cartesian product of two tables.
  C. The SQL:1999 compliant ANSI join syntax supports natural joins.
  D. The Oracle join syntax supports natural joins.
  E. The Oracle join syntax performs better than the SQL:1999 compliant ANSI join syntax.
  F. The SQL:1999 compliant ANSI join syntax supports creation of a Cartesian product of two tables.
  G. The Oracle join syntax performs less well than the SQL:1999 compliant ANSI Join Answer.
  B. The Oracle join syntax supports creation of a Cartesian product of two tables.
  C. The SQL:1999 compliant ANSI join syntax supports natural joins.
  F. The SQL:1999 compliant ANSI join syntax supports creation of a Cartesian product of two tables.

• Question 126:

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

  A. WHERE and HAVING clauses can be used in the same statement only if applied to different table columns.
  B. The WHERE clause can be used to exclude rows after dividing them into groups.
  C. The HAVING clause can be used with aggregating functions in subqueries.
  D. The WHERE clause can be used to exclude rows before dividing them into groups.
  E. Aggregating functions and columns used in HAVING clauses must be specified in the SELECT list of a query.
  C. The HAVING clause can be used with aggregating functions in subqueries.
  D. The WHERE clause can be used to exclude rows before dividing them into groups.

• Question 127:

  Examine the description of the EMPLOYEES table

  

  Which two queries return the highest salary in the table?

  A. SELECT MAX(salary) FROM employees GROUP By department_id HAVING MAX(salary) = MAX(MAX(salary));
  B. SELECT MAX(salary) FROM employees GROUP By department_id;
  C. SELECT department_id, MAX(salary) FROM employees GROUP By department_id;
  D. SELECT MAX(salary) FROM employees;
  E. SELECT MAX(MAX(salary)) FROM employees GROUP By department_id;
  D. SELECT MAX(salary) FROM employees;
  E. SELECT MAX(MAX(salary)) FROM employees GROUP By department_id;

• Question 128:

  Which three statements are true about Structured Query Language (SQL)?

  A. It guarantees atomicity, consistency, isolation, and durability (ACID) features
  B. It best supports relational databases
  C. It is used to define encapsulation and polymorphism for a relational table
  D. It requires that data be contained in hierarchical data storage
  E. It is the only language that can be used for both relational and object-oriented databases
  F. It provides independence for logical data structures being manipulated from the underlying physical data storage
  A. It guarantees atomicity, consistency, isolation, and durability (ACID) features
  B. It best supports relational databases
  F. It provides independence for logical data structures being manipulated from the underlying physical data storage

• Question 129:

  Which three are true about the CREATE TABLE command?

  A. It can include the CREATE...INDEX statement for creating an index to enforce the primary key constraint.
  B. The owner of the table should have space quota available on the tablespace where the table is defined.
  C. It implicitly executes a commit.
  D. It implicitly rolls back any pending transactions.
  E. A user must have the CREATE ANY TABLE privilege to create tables.
  F. The owner of the table must have the UNLIMITED TABLESPACE system privilege.
  A. It can include the CREATE...INDEX statement for creating an index to enforce the primary key constraint.
  B. The owner of the table should have space quota available on the tablespace where the table is defined.
  C. It implicitly executes a commit.

• Question 130:

  The PROD_ID column is the foreign key in the SALES table.Which references the PRODUCTS table.

  Similarly,the CUST_ID and TIME_ID columns are Also foreign keys in the SALES table referencing the CUSTOMERS and TIMES tables, respectively.

  Evaluate the following CREATE TABLE command:

  CREATE TABLE new_sales(prod_id, I cust_id, order_date DEFAULT SYSDATE)

  AS SELECT I prod_id,cust_id,time_id FROM sales.

  Which statement is true regarding the above command?

  A. The NEW_SALES table would not get created because the DEFAULT value cannot be specified in the column definition.
  B. The NEW_SALES table would get created and all the NOT NULL constraints defined on the specified columns would be passed to the new table.
  C. The NEW_SALES table would not get created because the column names in the CREATE TABLE command and the SELECT clause I do not match.
  D. The NEW_SALES table would get created and all the FOREIGN KEY constraints defined on the specified columns would be passed to the new table
  B. The NEW_SALES table would get created and all the NOT NULL constraints defined on the specified columns would be passed to the new table.