1Z0-071 | Far Out Oracle Database 12c SQL 1Z0-071 Training Materials

NEW QUESTION 1
Which statements are correct regarding indexes? (Choose all that apply.)

• A. A non-deferrable PRIMARY KEY or UNIQUE KEY constraint in a table automatically attempts to creates 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, the corresponding indexes are automatically dropped.
• D. For each DML operation performed, the corresponding indexes are automatically updated.

Answer: ACD

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

NEW QUESTION 2
Evaluate this ALTER TABLE statement: (Choose the best answer.) ALTER TABLE orders
SET UNUSED (order_date); Which statement is true?

• A. After executing the ALTER TABLE command, a new column called ORDER_DATE can be added 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 restore the ORDER_DATE column.
• D. The DESCRIBE command would still display the ORDER_DATE column.

Answer: A

NEW QUESTION 3
Which three arithmetic operations can be performed on a column by using a SQL function that is built into Oracle database? (Choose three.)

• A. Finding the lowest value
• B. Finding the quotient
• C. Raising to a power
• D. Subtraction
• E. Addition

Answer: ACE

NEW QUESTION 4
Which two tasks can be performed by using Oracle SQL statements?

• A. changing the password for an existing database user
• B. connecting to a database instance
• C. querying data from tables across databases
• D. starting up a database instance
• E. executing operating system (OS) commands in a session

Answer: AC

Explanation:
References:
http://www.techonthenet.com/oracle/password.php
https://docs.oracle.com/cd/B28359_01/server.111/b28324/tdpii_distdbs.htm

NEW QUESTION 5
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. The column with a UNIQUE constraint can store NULLS.
• D. You can have more than one column in a table as part of a primary key.

Answer: CD

NEW QUESTION 6
Evaluate the following statement. INSERT ALL
WHEN order_total < 10000 THEN INTO small_orders
WHEN order_total > 10000 AND order_total < 20000 THEN INTO medium_orders
WHEN order_total > 200000 THEN INTO large_orders
SELECT order_id, order_total, customer_id FROM orders;
Which statement is true regarding the evaluation of rows returned by the subquery in the INSERT statement?

• A. Each row is evaluated by the first WHEN clause and if the condition is false then the row would be evaluated by the subsequent when clauses.
• B. All rows are evaluated by all the three WHEN clauses.
• C. Each row is evaluated by the first WHEN clause and if the condition is true, then the row would be evaluated by the subsequent when clauses.
• D. The INSERT statement will return an error because the ELSE clause is missing.

Answer: B

NEW QUESTION 7
Evaluate the following CREATE TABLE command:

Which statement is true regarding the above SQL statement?

• A. It would execute successfully and only ORD_ITM_IDX index would be created.
• B. It would give an error because the USING INDEX clause cannot be used on a composite primary.
• C. It would execute successfully and two indexes ORD_ITM_IDX and ORD_ITM_ID PK would be created.
• D. It would give an error because the USING INDEX is not permitted in the CRETAE TABLE command.

Answer: A

NEW QUESTION 8
You notice a performance change in your production Oracle 12c database. You want to know which change caused this performance difference.
Which method or feature should you use?

• A. Compare Period ADDM report.
• B. AWR Compare Period report.
• C. Active Session History (ASH) report.
• D. Taking a new snapshot and comparing it with a preserved snapshot.

Answer: B

NEW QUESTION 9
Which two statements are true about sequences created in a single instance database? (Choose two.)

• A. When the MAXVALUE limit for the sequence is reached, you can increase the MAXVALUE limit by using the ALTER SEQUENCE statement.
• B. DELETE <sequencename> would remove a sequence from the database.
• C. The numbers generated by a sequence can be used only for one table.
• D. CURRVAL is used to refer to the last sequence number that has been generated.
• E. When a database instance shuts down abnormally, the sequence numbers that have been cached but not used would be available once again when the database instance is restarted.

Answer: AD

Explanation:
References:
http://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_2012.htm#SQLRF00817
https://docs.oracle.com/cd/A84870_01/doc/server.816/a76989/ch26.htm

NEW QUESTION 10
View the exhibit and examine the ORDERS table. ORDERS
Name Null? Type
ORDER ID NOT NULL NUMBER(4) ORDATE DATE DATE CUSTOMER ID NUMBER(3) ORDER TOTAL NUMBER(7,2)
The ORDERS table contains data and all orders have been assigned a customer ID. Which statement would add a NOT NULL constraint to the CUSTOMER_ID column?

• A. ALTER TABLE ordersMODIFY CONSTRAINT orders_cust_id_nn NOT NULL (customer_id);
• B. ALTER TABLE ordersADD CONSTRAINT orders_cust_id_nn NOT NULL (customer_id);
• C. ALTER TABLE ordersMODIFY customer_id CONSTRAINT orders_cust_nn NOT NULL (customer_id);
• D. ALTER TABLE ordersADD customer_id NUMBER(6)CONSTRAINT orders_cust_id_nn NOT NULL;

Answer: C

NEW QUESTION 11
Which two statements best describe the benefits of using the WITH clause? (Choose two.)

• A. It can improve the performance of a large query by storing the result of a query block having the WITH clause in the session's temporary tablespace.
• B. It enables sessions to reuse the same query block in a SELECT statement, if it occurs more than once in a complex query.
• C. It enables sessions to store a query block permanently in memory and use it to create complex queries.
• D. It enables sessions to store the results of a query permanently.

Answer: AB

NEW QUESTION 12
The BOOKS_TRANSACTIONS table exists in your database. SQL>SELECT * FROM books_transactions ORDER BY 3; What is the outcome on execution?

• A. The execution fails unless the numeral 3 in the ORDER BY clause is replaced by a column name.
• B. Rows are displayed in the order that they are stored in the table only for the three rows with the lowest values in the key column.
• C. Rows are displayed in the order that they are stored in the table only for the first three rows.
• D. Rows are displayed sorted in ascending order of the values in the third column in the table.

Answer: D

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

• A. The ORDER BY Clause can be used in a subquery.
• B. A subquery can be used in the FROM clause of a SELECT statement.
• C. If a subquery returns NULL, the main query may still return rows.
• D. A subquery can be placed in a WHERE clause, a GROUP BY clause, or a HAVING clause.
• E. Logical operators, such as AND, OR and NOT, cannot be used in the WHERE clause of a subquery.

Answer: ABC

NEW QUESTION 14
You must write a query that prompts users for column names and conditions every time it is executed. (Choose the best answer.)
The user must be prompted only once for the table name. Which statement achieves those objectives?

• A. SELECT &col1, '&col2'FROM &tableWHERE &&condition = '&cond';
• B. SELECT &col1, &col2 FROM "&table"WHERE &condition =&cond;
• C. SELECT &col1, &col2 FROM &&tableWHERE &condition = &cond;
• D. SELECT &col1, &col2 FROM &&tableWHERE &condition = &&cond

Answer: C

NEW QUESTION 15
When does a transaction complete? (Choose all that apply.)

• A. When a PL/SQL anonymous block is executed
• B. When a DELETE statement is executed
• C. When a data definition language statement is executed
• D. When a TRUNCATE statement is executed after the pending transaction
• E. When a ROLLBACK command is executed

Answer: CDE

NEW QUESTION 16
......