Oracle 1Z0-071 Online Practice Questions and Exam Preparation

Oracle 1Z0-071 Online Questions & Answers

• Question 271:

  Which two are true? (Choose two.)

  A. CONCAT joins two character strings together.
  B. CONCAT joins two or more character strings together.
  C. FLOOR returns the largest positive integer less than or equal to a specified number.
  D. INSTR finds the offset within a character string, starting from position 0.
  E. INSTR finds the offset within a string of a single character only.
  F. FLOOR returns the largest integer less than or equal to a specified number.
  A. CONCAT joins two character strings together.
  F. FLOOR returns the largest integer less than or equal to a specified number.

• Question 272:

  Which three queries execute successfully?

  A. SELECT 1 - SYSDATE - DATE '2019-01-01' FROM DUAL;
  B. SELECT SYSDATE - DATE '2019-01-01' - 1 FROM DUAL;
  C. SELECT SYSDATE / DATE '2019-01-01' - 1 FROM DUAL;
  D. SELECT SYSDATE - 1 - DATE '2019-01-01' FROM DUAL;
  E. SELECT (SYSDATE - DATE '2019-01-01') / 1 FROM DUAL;
  F. SELECT 1 / SYSDATE - DATE '2019-01-01' FROM DUAL;
  B. SELECT SYSDATE - DATE '2019-01-01' - 1 FROM DUAL;
  D. SELECT SYSDATE - 1 - DATE '2019-01-01' FROM DUAL;
  E. SELECT (SYSDATE - DATE '2019-01-01') / 1 FROM DUAL;

• Question 273:

  Examine the description of the PROMOTIONS TABLE:

  

  You want to display the unique promotion costs in each promotion category.

  Which two queries can be used?

  A. SELECT DISTINCT promo_category || ' has ' || promo_cost AS COSTS FROM promotions ORDER BY 1;
  B. SELECT DISTINCT promo_cost || ' in ' || DISTINCT promo_category FROM promotions ORDER BY 1;
  C. SELECT DISTINCT promo_category, promo_cost FROM promotions ORDER BY 1;
  D. SELECT promo_category DISTINCT promo_cost, FROM promotions ORDER BY 2;
  E. SELECT promo_cost, promo_category FROM promotions ORDER BY 1;
  A. SELECT DISTINCT promo_category || ' has ' || promo_cost AS COSTS FROM promotions ORDER BY 1;
  C. SELECT DISTINCT promo_category, promo_cost FROM promotions ORDER BY 1;

• Question 274:

  View the Exhibit and examine the description of the tables. You execute this SQL statement:

  

  Which three statements are true? (Choose three.)

  A. The statement will execute successfully and a new row will be inserted into the SALES table.
  B. A product can have a different unit price at different times.
  C. The statement will fail if a row already exists in the SALES table for product 23.
  D. The statement will fail because a subquery may not be contained in a VALUES clause.
  E. A customer can exist in many countries.
  F. The SALES table has five foreign keys.
  A. The statement will execute successfully and a new row will be inserted into the SALES table.
  B. A product can have a different unit price at different times.
  F. The SALES table has five foreign keys.

• Question 275:

  Which statement is true about TRUNCATE and DELETE?

  A. For tables with multiple indexes and triggers, DELETE is faster than TRUNCATE.
  B. You can never TRUNCATE a table if foreign key constraints would be violated.
  C. You can DELETE rows from a table with referential integrity constraints.
  D. For large tables, DELETE is faster than TRUNCATE.
  B. You can never TRUNCATE a table if foreign key constraints would be violated.

• Question 276:

  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
  C. 160

  ---

  Explanation/Reference:

  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 277:

  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;
  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

• Question 278:

  Which two statements are true about the SET VERIFY ON command?

  A. It displays values for variables created by the DEFINE command.
  B. It can be used in SQL Developer and SQL*Plus.
  C. It can be used only in SQL*plus.
  D. It displays values for variables prefixed with andand.
  E. It displays values for variables used only in the WHERE clause of a query.
  B. It can be used in SQL Developer and SQL*Plus.
  D. It displays values for variables prefixed with andand.

• Question 279:

  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
  B. SELECT DISTINCT cust_income_level || ` ' || cust_credit_limit * 0.50 AS "50% Credit Limit" FROM customers.

• Question 280:

  Which three statements are true about inner and outer joins? (Choose three.)

  A. A full outer join returns matched and unmatched rows.
  B. A full outer join must use Oracle syntax.
  C. Outer joins can be used when there are multiple join conditions on two tables.
  D. Outer joins can only be used between two tables per query.
  E. An inner join returns matched rows.
  F. A left or right outer join returns only unmatched rows.
  A. A full outer join returns matched and unmatched rows.
  C. Outer joins can be used when there are multiple join conditions on two tables.
  E. An inner join returns matched rows.