Oracle Oracle Certifications 1Z0-071 Questions & Answers

• Question 41:

  Which two statements are true regarding the USING and ON clauses in table joins?

  A. Both USING and ON clauses can be used for equijoins and nonequijoins.

  B. A maximum of one pair of columns can be joined between two tables using the ON clause.

  C. The ON clause can be used to join tables on columns that have different names but compatible data types.

  D. The WHERE clause can be used to apply additional conditions in SELECT statements containing the ON or the USING clause.

  Correct Answer: CD

• Question 42:

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

  A. When the MAXVALUE limit for a sequence is reached, it can be increased by using the ALTER SEQUENCE statement.

  B. DELETE would remove a sequence from the database.

  C. The numbers generated by an explicitly defined sequence can only be used to insert data in one table.

  D. CURRVAL is used to refer to the most recent sequence number that has been generated for a particular sequence.

  E. When a database instance shuts down abnormally, sequence numbers that have been cached but not used are available again when the instance is restarted.

  Correct Answer: AD

  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

• Question 43:

  View the exhibit and examine the structure of the SALES, CUSTOMERS, PRODUCTS and TIMES tables.

  

  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, cust_id, order_date DEFAULT SYSDATE)

  AS SELECT prod_id, cust_id, time_id FROM sales;

  Which statement is true regarding the above command?

  A. 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.

  B. The NEW_SALES table would not get created because the DEFAULT value cannot be specified in the column definition.

  C. The NEW_SALES table would not get created because the column names in the CREATE TABLE command and the SELECT clause 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.

  Correct Answer: A

• Question 44:

  Evaluate the following query:

  SQL> SELECT TRUNC (ROUND(156.00, -1),-1) FROM DUAL;

  What would be the outcome?

  A. 150

  B. 200

  C. 160

  D. 16

  E. 100

  Correct Answer: C

  References: https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions135.htm https://docs.oracle.com/cd/B28359_01/olap.111/b28126/dml_functions_2127.htm

• Question 45:

  The first DROP operation is performed on PRODUCTS table using this command:

  DROP TABLE products PURGE;

  Then a FLASHBACK operation is performed using this command:

  FLASHBACK TABLE products TO BEFORE DROP;

  Which is true about the result of the FLASHBACK command?

  A. It recovers only the table structure.

  B. It recovers the table structure, data, and the indexes.

  C. It recovers the table structure and data but not the related indexes.

  D. It is not possible to recover the table structure, data, or the related indexes.

  Correct Answer: D

  References: https://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_9003.htm

• Question 46:

  These are the steps for a correlated subquery, listed in random order:

  1.

  The WHERE clause of the outer query is evaluated.

  2.

  A candidate row is fetched from the table specified in the outer query.

  3.

  This is repeated for the subsequent rows of the table, until all the rows are processed.

  4.

  Rows are returned by the inner query, after being evaluated with the value from the candidate row in the outer query.

  Which is the correct sequence in Which the Oracle server evaluates a correlated subquery?

  A. 2, 1, 4, 3

  B. 4, 1, 2, 3

  C. 4, 2, 1, 3

  D. 2, 4, 1, 3

  Correct Answer: D

  References: http://rajanimohanty.blogspot.co.uk/2014/01/correlated-subquery.html

• Question 47:

  Examine the following query: SQL> SELECT prod_id, amount_sold FROM sales ORDER BY amount_sold FETCH FIRST 5 PERCENT ROWS ONLY;

  What is the output of this query?

  A. It displays 5 percent of the products with the highest amount sold.

  B. It displays the first 5 percent of the rows from the SALES table.

  C. It displays 5 percent of the products with the lowest amount sold.

  D. It results in an error because the ORDER BY clause should be the last clause.

  Correct Answer: C

  References: https://oracle-base.com/articles/12c/row-limiting-clause-for-top-n-queries-12cr1

• Question 48:

  Which statement is true regarding the INTERSECT operator?

  A. The names of columns in all SELECT statements must be identical.

  B. It ignores NULL values.

  C. Reversing the order of the intersected tables alters the result.

  D. The number of columns and data types must be identical for all SELECT statements in the query.

  Correct Answer: D

  INTERSECT Returns only the rows that occur in both queries' result sets, sorting them and removing duplicates.

  The columns in the queries that make up a compound query can have different names, but the output result set will use the names of the columns in the first query.

• Question 49:

  You want to display the date for the first Monday of the next month and issue the following command:

  

  What is the outcome?

  A. In generates an error because rrrr should be replaced by rr in the format string.

  B. It executes successfully but does not return the correct result.

  C. It executes successfully and returns the correct result.

  D. In generates an error because TO_CHAR should be replaced with TO_DATE.

  E. In generates an error because fm and double quotation marks should not be used in the format string.

  Correct Answer: C

• Question 50:

  Which two statements are true regarding the GROUP BY clause in a SQL statement? (Choose two.)

  A. You can use column alias in the GROUP BY clause.

  B. Using the WHERE clause after the GROUP BY clause excludes the rows after creating groups.

  C. The GROUP BY clause is mandatory if you are using an aggregate function in the SELECT clause.

  D. Using the WHERE clause before the GROUP BY clause excludes the rows before creating groups.

  E. If the SELECT clause has an aggregate function, then those individual columns without an aggregate function in the SELECT clause should be included in the GROUP BY cause.

  Correct Answer: DE