Oracle Oracle Certifications 1Z0-071 Questions & Answers

• Question 21:

  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

  Correct Answer: ACE

• Question 22:

  View the Exhibit and examine the description for the SALES and CHANNELS tables. (Choose the best answer.)

  

  You issued this SQL statement:

  

  Which statement is true regarding the result?

  A. The statement will fail because the subquery in the VALUES clause is not enclosed within single quotation marks.

  B. The statement will fail because a subquery cannot be used in a VALUES clause.

  C. The statement will execute and a new row will be inserted in the SALES table.

  D. The statement will fail because the VALUES clause is not required with the subquery.

  Correct Answer: C

• Question 23:

  View the Exhibit and examine the structure in the EMPLOYEES tables.

  

  Evaluate the following SQL statement:

  SELECT employee_id, department_id FROM employees WHERE department_id= 50 ORDER BY department_id UNION SELECT employee_id, department_id FROM employees WHERE department_id=90 UNION SELECT employee_id, department_id FROM employees WHERE department_id=10;

  What would be the outcome of the above SQL statement?

  A. The statement would not execute because the positional notation instead of the column name should be used with the ORDER BY clause.

  B. The statement would execute successfully and display all the rows in the ascending order of DEPARTMENT_ID.

  C. The statement would execute successfully but it will ignore the ORDER BY clause and display the rows in random order.

  D. The statement would not execute because the ORDER BY clause should appear only at the end of the SQL statement, that is, in the last SELECT statement.

  Correct Answer: D

• Question 24:

  Examine these SQL statements that are executed in the given order:

  

  What will be the status of the foreign key EMP_MGR_FK?

  A. It will be enabled and immediate.

  B. It will be enabled and deferred.

  C. It will remain disabled and can be re-enabled manually.

  D. It will remain disabled and can be enabled only by dropping the foreign key constraint and re-creating it.

  Correct Answer: C

• Question 25:

  You execute the SQL statement:

  

  What is the outcome?

  A. It fails because the NOT NULL and DEFAULT options cannot be combined for the same column.

  B. It succeeds and CITY can contain only `SEATTLE' or null for all rows.

  C. It fails because the condition for the CNAMES constraint is not valid.

  D. It succeeds and an index is created for CITIZEN_ID.

  Correct Answer: A

• Question 26:

  Which two statements are true regarding the SQL GROUP BY clause?

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

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

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

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

  E. If the SELECT clause has an aggregating function, then columns without an aggregating function in the SELECT clause should be included in the GROUP BY clause.

  Correct Answer: DE

• Question 27:

  You issue this command Which succeeds: SQL> DROP TABLE products;

  Which three statements are true?

  A. All existing views and synonyms that refer to the table are invalidated but retained.

  B. Any uncommitted transaction in the session is committed.

  C. Table data and the table structure are deleted.

  D. All the table's indexes if any exist, are invalidated but retained.

  E. Table data is deleted but the table structure is retained.

  Correct Answer: BCD

• Question 28:

  View the Exhibit and examine the structure of the ORDER_ITEMS table.

  

  Examine the following SQL statement: SELECT order_id, product_id, unit_price FROM order_items WHERE unit_price = (SELECT MAX(unit_price) FROM order_items GROUP BY order_id);

  You want to display the PRODUCT_ID of the product that has the highest UNIT_PRICE per ORDER_ID. What correction should be made in the above SQL statement to achieve this?

  A. Remove the GROUP BY clause from the subquery and place it in the main query

  B. Replace = with the >ANY operator

  C. Replace = with the >ALL operator

  D. Replace = with the IN operator

  Correct Answer: D

• Question 29:

  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 clause is not permitted in the CREATE TABLE command.

  Correct Answer: A

• Question 30:

  Using the CUSTOMERS table, you need to generate a report that shows 50% of each credit amount in each income level. The report should NOT show any repeated credit amounts in each income level.

  Which query would give the required result?

  A. SELECT cust_income_level || ` ' || cust_credit_limit * 0.50 AS "50% Credit Limit" FROM customers.

  B. SELECT DISTINCT cust_income_level || ` ' || cust_credit_limit * 0.50 AS "50% Credit Limit" FROM customers.

  C. SELECT DISTINCT cust_income_level, DISTINCT cust_credit_limit * 0.50 AS "50% Credit Limit" FROM customers.

  D. SELECT cust_income_level, DISTINCT cust_credit_limit * 0.50 AS "50% Credit Limit" FROM customers

  Correct Answer: B