Oracle Oracle Certifications 1Z0-071 Questions & Answers

• Question 61:

  Which three tasks can be performed using SQL functions built into Oracle Database?

  A. displaying a date in a nondefault format

  B. finding the number of characters in an expression

  C. substituting a character string in a text expression with a specified string

  D. combining more than two columns or expressions into a single column in the output

  Correct Answer: ABC

• Question 62:

  The user SCOTT who is the owner of ORDERS and ORDER_ITEMS tables issues this GRANT command:

  GRANT ALL

  ON orders, order_items

  TO PUBLIC;

  What must be done to fix the statement?

  A. PUBLIC should be replaced with specific usernames.

  B. ALL should be replaced with a list of specific privileges.

  C. WITH GRANT OPTION should be added to the statement.

  D. Separate GRANT statements are required for the ORDERS and ORDER_ITEMS tables.

  Correct Answer: D

  References: http://docs.oracle.com/javadb/10.8.3.0/ref/rrefsqljgrant.html

• Question 63:

  You are designing the structure of a table in Which two columns have the specifications:

  COMPONENT_ID - must be able to contain a maximum of 12 alphanumeric characters and must uniquely identify the row EXECUTION_DATETIME ?contains Century, Year, Month, Day, Hour, Minute, Second to the maximum precision and

  is used for calculations and comparisons between components.

  Which two options define the data types that satisfy these requirements most efficiently? (Choose two.)

  A. The EXECUTION_DATETIME must be of INTERVAL DAY TO SECOND data type.

  B. The EXECUTION_DATETIME must be of TIMESTAMP data type.

  C. The EXECUTION_DATETIME must be of DATE data type.

  D. The COMPONENT_ID must be of ROWID data type.

  E. The COMPONENT_ID must be of VARCHAR2 data type.

  F. The COMPONENT_ID column must be of CHAR data type.

  Correct Answer: CF

• Question 64:

  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.

  Correct Answer: D

  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)

• Question 65:

  Which three statements are correct regarding indexes? (Choose three.)

  A. A non-deferrable PRIMARY KEY or UNIQUE KEY constraint in a table automatically attempts to create a unique index.

  B. Indexes should be created on columns that are frequently referenced as part of any expression.

  C. When a table is dropped, corresponding indexes are automatically dropped.

  D. For each DML operation performed on a table, the corresponding indexes are automatically updated if required.

  Correct Answer: ACD

  References: http://viralpatel.net/blogs/understanding-primary-keypk-constraint-in-oracle/

• Question 66:

  Which statement correctly grants a system privilege?

  A. GRANT CREATE VIEW ON table1 TO user1;

  B. GRANT ALTER TABLE TO PUBLIC;

  C. GRANT CREATE TABLE TO user1, user2;

  D. GRANT CREATE SESSION TO ALL;

  Correct Answer: C

• Question 67:

  View the exhibit and examine the structure of ORDERS and CUSTOMERS tables.

  

  Which INSERT statement should be used to add a row into the ORDERS table for the customer whose CUST_LAST_NAME is Roberts and CREDIT_LIMIT is 600? Assume there exists only one row with CUST_LAST_NAME as Roberts and CREDIT_LIMIT as 600.

  A. INSERT INTO (SELECT o.order_id, o.order_date, o.order_mode, c.customer_id, o.order_total FROM orders o, customers c WHERE o.customer_id = c.customer_id AND c.cust_last_name='Roberts' AND c.credit_limit=600) VALUES (1,'10-mar-2007', 'direct', (SELECT customer_id FROM customers WHERE cust_last_name='Roberts' AND credit_limit=600), 1000);

  B. INSERT INTO orders (order_id, order_date, order_mode, (SELECT customer_id FROM customers WHERE cust_last_name='Roberts' AND credit_limit=600), order_total) VALUES (1,'10-mar-2007', 'direct', andcustomer_id, 1000);

  C. INSERT INTO orders VALUES (1,'10-mar-2007', 'direct', (SELECT customer_id FROM customers WHERE cust_last_name='Roberts' AND credit_limit=600), 1000);

  D. INSERT INTO orders (order_id, order_date, order_mode, (SELECT customer_id FROM customers WHERE cust_last_name='Roberts' AND credit_limit=600), order_total) VALUES (1,'10-mar-2007', 'direct', andandcustomer_id, 1000);

  Correct Answer: C

• Question 68:

  Which statement is true about an inner join specified in a query's WHERE clause?

  A. It only applies for equijoin conditions.

  B. It applies for equijoin and nonequijoin conditions.

  C. It requires column names to be the same in all tables being joined.

  D. It must have primary-key and foreign-key constraints defined on the join columns.

  Correct Answer: B

• Question 69:

  Which task can be performed by using a single Data Manipulation Language (DML) statement?

  A. adding a column constraint while inserting a row into a table

  B. adding a column with a default value while inserting a row into a table

  C. removing all data only from a single column on Which a unique constraint is defined

  D. removing all data only from a single column on Which a primary key constraint is defined

  Correct Answer: C

• Question 70:

  Examine the structure of the BOOKS_TRANSACTIONS table:

  

  You want to display the member IDs, due date, and late fee as $2 for all transactions. Which SQL statement must you execute?

  A. SELECT member_id AS MEMBER_ID, due_date AS DUE_DATE, $2 AS LATE_FEE FROM BOOKS_TRANSACTIONS;

  B. SELECT member_id 'MEMBER ID', due_date 'DUE DATE', '$2 AS LATE FEE' FROM BOOKS_TRANSACTIONS;

  C. SELECT member_id AS "MEMBER ID", due_date AS "DUE DATE", '$2' AS "LATE FEE" FROM BOOKS_TRANSACTIONS;

  D. SELECT member_id AS "MEMBER ID", due_date AS "DUE DATE", $2 AS "LATE FEE" FROM BOOKS_TRANSACTIONS;

  Correct Answer: C