Oracle 1Z0-061 Online Practice Questions and Exam Preparation

Oracle 1Z0-061 Online Questions & Answers

• Question 321:

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

  Exhibit:

  

  Which two SQL statements would execute successfully? (Choose two.)

  A. UPDATE promotionsSET promo_cost = promo_cost+ 100WHERE TO_CHAR(promo_end_date,'yyyy') > '2000';
  B. SELECT promo_begin_dateFROM promotionsWHERE TO_CHAR(promo_begin_date,'mon dd yy')='jul 01 98';
  C. UPDATE promotionsSET promo_cost = promo_cost+ 100WHERE promo_end_date > TO_DATE(SUBSTR('01-JAN-2000', 8));
  D. SELECT TO_CHAR(promo_begin_date,'dd/month')FROM promotionsWHERE promo_begin_date IN (TO_DATE('JUN 01 98'), TO_DATE('JUL 01 98'));
  A. UPDATE promotionsSET promo_cost = promo_cost+ 100WHERE TO_CHAR(promo_end_date,'yyyy') > '2000';
  B. SELECT promo_begin_dateFROM promotionsWHERE TO_CHAR(promo_begin_date,'mon dd yy')='jul 01 98';

• Question 322:

  You need to display the date ll-oct-2007 in words as `Eleventh of October, Two Thousand Seven'. Which SQL statement would give the required result?

  

  A. Option A
  B. Option B
  C. Option C
  D. Option D
  C. Option C

  ---

  The '11-oct-2007' is a string not a date. We need to user the TO_DATE function and convert it to a date.

• Question 323:

  You work as a database administrator at ABC.com. You study the exhibit carefully.

  Exhibit:

  

  and examine the structure of CUSTOMRS AND SALES tables:

  Evaluate the following SQL statement:

  Exhibit:

  

  Which statement is true regarding the execution of the above UPDATE statement?

  A. It would not execute because the SELECT statement cannot be used in place of the table name
  B. It would execute and restrict modifications to only the column specified in the SELECT statement
  C. It would not execute because a sub query cannot be used in the WHERE clause of an UPDATE statement
  D. It would not execute because two tables cannot be used in a single UPDATE statement
  B. It would execute and restrict modifications to only the column specified in the SELECT statement

• Question 324:

  View the Exhibit and examine the structure of the PROMOTIONS table. Which SQL statements are valid? (Choose all that apply.)

  

  A. SELECT promo_id. DECODE(NVL(promo_cost.O).promo_cost * 0.25. 100) "Discount"FROM promotions;
  B. SELECT promo id. DECODE(promo_cost. 10000.DECODE(promo_category. 'Gl\ promo_cost * 25. NULL). NULL) "Catcost" FROM promotions;
  C. SELECT promo_id. DECODE(NULLIF(promo_cost. 10000). NULL. promo_cost*.25, *N/A') "Catcost"FROM promotions;
  D. SELECT promo_id. DECODE(promo_cost. >10000. 'High'.
  A. SELECT promo_id. DECODE(NVL(promo_cost.O).promo_cost * 0.25. 100) "Discount"FROM promotions;
  B. SELECT promo id. DECODE(promo_cost. 10000.DECODE(promo_category. 'Gl\ promo_cost * 25. NULL). NULL) "Catcost" FROM promotions;

  ---

  Note: there are some syntax issues in this question.

• Question 325:

  View the Exhibit and examine the structure of the CUSTOMERS table .Which statement would display the highest credit limit available in each income level in each city in the CUSTOMERS table?

  

  A. SELECT cust_city, cust_income_level, MAX(cust_credit_limit ) FROM customersGROUP BY cust_city, cust_income_level, cust_credit_limit;
  B. SELECT cust_city, cust_income_level, MAX(cust_credit_limit) FROM customersGROUP BY cust_city, cust_income_level;
  C. SELECT cust_city, cust_income_level, MAX(cust_credit_limit) FROM customersGROUP BY cust_credit_limit, cust_income_level, cust_city ;
  D. SELECT cust_city, cust_income_level, MAX(cust_credit_limit) FROM customersGROUP BY cust_city, cust_income_level, MAX(cust_credit_limit);
  B. SELECT cust_city, cust_income_level, MAX(cust_credit_limit) FROM customersGROUP BY cust_city, cust_income_level;

• Question 326:

  Examine the structure of the INVOICE table: Exhibit:

  

  Which two SQL statements would execute successfully? (Choose two.)

  A. SELECT inv_no, NVL2(inv_date, 'Pending', 'Incomplete')FROM invoice;
  B. SELECT inv_no, NVL2(inv_amt, inv_date, 'Not Available')FROM invoice;
  C. SELECT inv_no, NVL2(inv_date, sysdate-inv_date, sysdate)FROM invoice;
  D. SELECT inv_no, NVL2(inv_amt, inv_amt*.25, 'Not Available')FROM invoice;
  A. SELECT inv_no, NVL2(inv_date, 'Pending', 'Incomplete')FROM invoice;
  C. SELECT inv_no, NVL2(inv_date, sysdate-inv_date, sysdate)FROM invoice;

  ---

  The NVL2 Function

  The NVL2 function provides an enhancement to NVL but serves a very similar purpose. It evaluates whether a column or expression of any data type is null or not.

  5-6 The NVL function\

  If the first term is not null, the second parameter is returned, else the third parameter is returned. Recall that the NVL function is different since it returns the original term if it is not null. The NVL2 function takes three mandatory parameters. Its

  syntax is NVL2(original, ifnotnull, ifnull), where original represents the term being tested. Ifnotnull is returned if original is not null, and ifnull is returned if original is null. The data types of the ifnotnull and ifnull parameters must be compatible,

  and they cannot be of type LONG. They must either be of the same type, or it must be possible to convert ifnull to the type of the ifnotnull parameter. The data type returned by the NVL2 function is the same as that of the ifnotnull parameter.

• Question 327:

  Examine the structure of the EMPLOYEES and NEW_EMPLOYEES tables:

  

  Which MERGE statement is valid?

  A. MERGE INTO new_employees c USING employees e ON (c.employee_id =
  B. employee_id) WHEN MATCHED THEN UPDATE SET name = e.first_name ||', '||
  C. last_name WHEN NOT MATCHED THEN INSERT value S(e.employee_id, e.first_name ||', '||e.last_name);
  D. MERGE new_employees c USING employees e ON (c.employee_id = e.employee_id) WHEN EXISTS THEN UPDATE SET name = e.first_name ||', '|| e.last_name WHEN NOT MATCHED THEN INSERT valueS(e.employee_id, e.first_name ||', '||e.last_name);
  E. MERGE INTO new_employees cUSING employees e ON (c.employee_id =
  F. employee_id) WHEN EXISTS THEN UPDATE SET
  G. name = e.first_name ||', '|| e.last_name WHEN NOT MATCHED THEN INSERT value S(e.employee_id, e.first_name ||', '||e.last_name);
  H. MERGE new_employees c FROM employees e ON (c.employee_id = e.employee_id) WHEN MATCHED THEN UPDATE SET name = e.first_name ||', '|| e.last_name WHEN NOT MATCHED THEN INSERT INTO new_employees valueS (e.employee_id,
  I. first_name ||', '||e.last_name);
  A. MERGE INTO new_employees c USING employees e ON (c.employee_id =

  ---

  this is the correct MERGE statement syntax

  Incorrect answer:

  B. it should MERGE INTO table_name

  C. it should be WHEN MATCHED THEN

  D. it should MERGE INTO table_name Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 8-29

• Question 328:

  The CUSTOMERS table has these columns:

  The CUSTOMER_ID column is the primary key for the table.

  You need to determine how dispersed your customer base is.

  Which expression finds the number of different countries represented in the CUSTOMERS table?

  A. COUNT(UPPER(country_address))
  B. COUNT(DIFF(UPPER(country_address)))
  C. COUNT(UNIQUE(UPPER(country_address)))
  D. COUNT DISTINCT UPPER(country_address)
  E. COUNT(DISTINCT (UPPER(country_address)))
  E. COUNT(DISTINCT (UPPER(country_address)))

• Question 329:

  View the Exhibit and evaluate structures of the SALES, PRODUCTS, and COSTS tables.

  

  Evaluate the following SQL statements:

  

  Which statement is true regarding the above compound query?

  A. It shows products that have a cost recorded irrespective of sales
  B. It shows products that were sold and have a cost recorded
  C. It shows products that were sold but have no cost recorded
  D. It reduces an error
  C. It shows products that were sold but have no cost recorded

• Question 330:

  Examine the following query:

  

  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.
  C. It displays 5 percent of the products with the lowest amount sold.