Oracle Oracle Certifications 1Z0-071 Questions & Answers

• Question 271:

  Which three statements are true about multiple row subqueries?

  A. They can contain HAVING clauses.

  B. Two or more values are always returned from the subquery.

  C. They cannot contain subquery.

  D. They can return multiple columns.

  E. They can contain GROUP BY clauses.

  Correct Answer: ADE

• Question 272:

  Examine the description of the PRODUCTS table:

  

  Which three queries use valid expressions?

  A. SELECT produet_id, unit_pricer, 5 "Discount",unit_price+surcharge-discount FROM products;

  B. SELECT product_id, (unit_price * 0.15 / (4.75 + 552.25)) FROM products;

  C. SELECT ptoduct_id, (expiry_date-delivery_date) * 2 FROM products;

  D. SPLECT product_id, expiry_date * 2 FROM products;

  E. SELEGT product_id, unit_price, unit_price + surcharge FROM products;

  F. SELECT product_id,unit_price || "Discount", unit_price + surcharge-discount FROM products;

  Correct Answer: BCE

• Question 273:

  Examine the description of the PRODUCT_ DETAILS table:

  

  Which two statements are true?

  A. EXPIRY_DATE contains the SYSDATE by default if no date is assigned to it.

  B. PRODUCT_PRICE can be used in an arithmetic expression even if it has no value stored in it.

  C. PRODUCT_NAME cannot contain duplicate values.

  D. EXPIRY_DATE cannot be used in arithmetic expressions.

  E. PRODUCT_PRICE contains the value zero by default if no value is assigned to it.

  F. PRODUCT_ID can be assigned the PRIMARY KEY constraint.

  Correct Answer: BF

• Question 274:

  Evaluate the following SQL statement SQL>SELECT promo_id, prom _category FROM promotions WHERE promo_category='Internet' ORDER BY promo_id UNION SELECT promo_id, promo_category FROM Pomotions WHERE promo_category = `TV' UNION SELECT promoid, promocategory FROM promotions WHERE promo category='Radio' Which statement is true regarding the outcome of the above query?

  A. It executes successfully and displays rows in the descend ignore of PROMO CATEGORY.

  B. It produces an error because positional, notation cannot be used in the ORDER BY clause with SBT operators.

  C. It executes successfully but ignores the ORDER BY clause because it is not located at the end of the compound statement.

  D. It produces an error because the ORDER BY clause should appear only at the end of a compound query-that is, with the last SELECT statement.

  Correct Answer: D

• Question 275:

  Which two statements are true about selecting related rows from two tables based on entity relationship diagram (ERD)?

  A. Relating data from a table with data from the same table is implemented with a self join.

  B. An inner join relates rows within the same table.

  C. Rows from unrelated tables cannot be joined.

  D. Implementing a relationship between two tables might require joining additional tables.

  E. Every relationship between the two tables must be implemented in a Join condition.

  Correct Answer: AD

• Question 276:

  Examine the description or the CUSTOMERS table:

  

  For Customers whose income level has a value, you want to display the first name and due amount as 5% of their credit limit. Customers whose due amount is null should not be displayed.

  Which query should be used?

  A. SELECT cust_first_name, cust_credit_limit * .05 AS due_amount FROM customers WHERE cust_income_level IS NOT NULL AND cust_credit_limit IS NOT NULL

  B. SELECT cust_first_name, cust_credit_limit * .05 AS due_amount FROM customers WHERE cust_income_level <> NULL AND due_amount <> NULL;

  C. SELECT cust_first_name, cust_credit_limit * .05 AS due_amount FROM customers WHERE cust_income_level != NULL AND cust_credit_level != NULL;

  D. SELECT cust_first_name, cust_credit_limit * .05 AS due_amount FROM customers WHERE cust_income_level != NULL AND due_amount != NULL;

  E. SELECT cust_first_name, cust_credit_limit * .05 AS due_amount FROM customers WHERE cust_income_level IS NOT NULL AND due_amount IS NOT NULL;

  Correct Answer: A

• Question 277:

  Examine the description of the transactions table:

  

  Which two SQL statements execute successfully?

  A. SELECT customer_id AS "CUSTOMER_ID", transaction_date AS "DATE", amount + 100 DUES FROM transactions;

  B. SELECT customer_id AS `CUSTOMER_ID', transaction_date AS DATE, amount + 100 `DUES' FROM transactions;

  C. SELECT customer_id AS "CUSTOMER_ID", transaction_date AS DATE, amount + 100 "DUES" FROM transactions;

  D. SELECT customer_id AS CUSTOMER_ID, transaction_date AS TRANS_DATE, amount + 100 "DUES AMOUNT" FROM transactions;

  E. SELECT customer_id CUSTID, transaction_date TRANS_DATE, amount + 100 DUES FROM transactions;

  Correct Answer: AD

• Question 278:

  Examine the data in the PRODUCTS table:

  

  Examine these queries:

  

  Which queries generate the same output?

  A. 1 and 3

  B. 1, 2 and 3

  C. 2 and 3

  D. 1 and 2

  Correct Answer: A

• Question 279:

  Examine the description of the CUSTOMERS table:

  

  Which two statements will do an implicit conversion? (Choose two.)

  A. SELECT * FROM customers WHERE customer_id - '0001';

  B. SELECT * FROM customers WHERE customer_id - 0001;

  C. SELECT * FROM customers WHERE insert_date - DATE '2019-01-01';

  D. SELECT * FROM customers WHERE insert_date - '01-JAN-19';

  E. SELECT * FROM customers WHERE TO_CHAR(customer_id) - '0001';

  Correct Answer: AD

• Question 280:

  You execute this query:

  SELECT TO CHAR (NEXT_DAY(LAST_DAY(SYSDATE),'MON' ),' dd"Monday for" fmMonth rrr')

  FROM DUAL;

  What is the result?

  A. It executes successfully but does not return any result.

  B. It returns the date for the first Monday of the next month.

  C. It generates an error.

  D. It returns the date for the last Monday of the current month.

  Correct Answer: B