Oracle 1Z0-061 Online Practice Questions and Exam Preparation

Oracle 1Z0-061 Online Questions & Answers

• Question 111:

  Which is an iSQL*Plus command?

  A. INSERT
  B. UPDATE
  C. SELECT
  D. DESCRIBE
  E. DELETE
  F. RENAME
  D. DESCRIBE

  ---

  The only SQL*Plus command in this list: DESCRIBE. It cannot be used as SQL command. This command returns a description of tablename, including all columns in that table, the datatype for each column and an indication of whether the column permits storage of NULL values.

  Incorrect answer:

  A . INSERT is not a SQL*PLUS command

  B . UPDATE is not a SQL*PLUS command

  C . SELECT is not a SQL*PLUS command

  E . DELETE is not a SQL*PLUS command

  F . RENAME is not a SQL*PLUS command Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 7

• Question 112:

  Which four are valid Oracle constraint types? (Choose four.)

  A. CASCADE
  B. UNIQUE
  C. NONUNIQUE
  D. CHECK
  E. PRIMARY KEY
  F. CONSTANT
  G. NOT NULL
  B. UNIQUE
  D. CHECK
  E. PRIMARY KEY
  G. NOT NULL

  ---

  Oracle constraint type is Not Null, Check, Primary Key, Foreign Key and Unique Incorrect answer:

  A. Is not Oracle constraint

  C. Is not Oracle constraint

  F. Is not Oracle constraint Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 10-3

• Question 113:

  View the Exhibit and examine the structure of the PRODUCTS tables. You want to generate a report that displays the average list price of product categories where the average list price is less than half the maximum in each category.

  Which query would give the correct output?

  A. SELECT prod_category, avg(prod_list_price)FROM productsGROUP BY prod_categoryHAVING avg(prod_list_price) < ALL(SELECT max(prod_list_price)/2FROM productsGROUP BY prod_category);
  B. SELECT prod_category, avg(prod_list_price)FROM productsGROUP BY prod_categoryHAVING avg(prod_list_price) > ANY(SELECT max(prod_list_price)/2FROM productsGROUP BY prod_category);
  C. SELECT prod_category, avg(prod_list_price)FROM productsHAVING avg(prod_list_price) < ALL(SELECT max(prod_list_price)/2FROM productsGROUP BY prod_category);
  D. SELECT prod_category, avg(prod_list_price)FROM productsGROUP BY prod_categoryHAVING avg(prod_list_price) > ANY(SELECT max(prod_list_price)/2FROM products);
  A. SELECT prod_category, avg(prod_list_price)FROM productsGROUP BY prod_categoryHAVING avg(prod_list_price) < ALL(SELECT max(prod_list_price)/2FROM productsGROUP BY prod_category);

  ---

  Using the ANY Operator in Multiple-Row Subqueries The ANY operator (and its synonym, the SOME operator) compares a value to each value returned by a subquery.

  >ANY means more than the minimum.

  =ANY is equivalent to IN

  Using the ALL Operator in Multiple-Row Subqueries The ALL operator compares a value to every value returned by a subquery.

  >ALL means more than the maximum and

  The NOT operator can be used with IN, ANY, and ALL operators.

• Question 114:

  Which SQL statement would you use to remove a view called EMP_DEPT_VU from your schema?

  A. DROP emp_dept_vu;
  B. DELETE emp_dept_vu;
  C. REMOVE emp_dept_vu;
  D. DROP VIEW emp_dept_vu;
  E. DELETE VIEW emp_dept_vu;
  F. REMOVE VIEW emp_dept_vu;
  D. DROP VIEW emp_dept_vu;

  ---

  DROP VIEW viewname; Incorrect answer:

  A. Not a valid drop view statement

  B. Not a valid drop view statement

  C. Not a valid drop view statement

  E. Not a valid drop view statement

  F. Not a valid drop view statement Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 11-20

• Question 115:

  Examine the structure of the SHIPMENTS table:

  

  You want to generate a report that displays the PO_ID and the penalty amount to be paid if the SHIPMENT_DATE is later than one month from the PO_DATE. The penalty is $20 per day. Evaluate the following two queries:

  

  Which statement is true regarding the above commands?

  A. Both execute successfully and give correct results.
  B. Only the first query executes successfully but gives a wrong result.
  C. Only the first query executes successfully and gives the correct result.
  D. Only the second query executes successfully but gives a wrong result.
  E. Only the second query executes successfully and gives the correct result.
  C. Only the first query executes successfully and gives the correct result.

  ---

  The MONTHS_BETWEEN(date 1, date 2) function returns the number of months between two dates:

  months_between('01-FEB-2008', '01-JAN-2008') = 1 The DECODE Function

  Although its name sounds mysterious, this function is straightforward. The DECODE function implements if then-else conditional logic by testing its first two terms for equality and returns the third if they are equal and optionally returns another

  term if they are not. DECODE Function Facilitates conditional inquiries by doing the work of a CASE expression or an IF-THENELSE statement:

  DECODE(col|expression, search1, result1

  [, search2, result2, ..., ]

  [, default])

  DECODE Function

  The DECODE function decodes an expression in a way similar to the IF-THEN-ELSE logic that is used in various languages. The DECODE function decodes expression after comparing it to each search value. If the expression is the same

  as search, result is returned.

  If the default value is omitted, a null value is returned where a search value does not match any of the result values.

• Question 116:

  Examine the structure of the transactions table:

  

  You want to display the date, time, and transaction amount of transactions that where done before 12 noon. The value zero should be displayed for transactions where the transaction amount has not been entered. Which query gives the required result?

  

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

• Question 117:

  Top N analysis requires _____ and _____. (Choose two.)

  A. the use of rowid
  B. a GROUP BY clause
  C. an ORDER BY clause
  D. only an inline view
  E. an inline view and an outer query
  C. an ORDER BY clause
  E. an inline view and an outer query

  ---

  The correct statement for Top-N Analysis

  SELECT [coloumn_list], ROWNUM

  FROM (SELECT [coloumn_list]

  FROM table

  ORDER BY Top-N_coloumn)

  WHERE ROWNUM <= N;

  Incorrect answer:

  A. ROWID is not require

  B. GROUP BY clause is not require

  D. Must have inline view and outer query.

  Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 11-23

• Question 118:

  View the Exhibit and examine the description of SALES and PROMOTIONS tables.

  

  You want to delete rows from the sales table, where the PROMO_NAME column in the promotions table has either blowout sale of everyday low prices as values. Which three delete statements are valid?

  

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

• Question 119:

  Which is an iSQL*Plus command?

  A. INSERT
  B. UPDATE
  C. SELECT
  D. DESCRIBE
  E. DELETE
  F. RENAME
  D. DESCRIBE

  ---

  The only SQL*Plus command in this list : DESCRIBE. It cannot be used as SQL command. This command returns a description of table name, including all columns in that table, the datatype for each column and an indication of whether the

  column permits storage of NULL values.

  Incorrect answer:

  A . INSERT is not a SQL*PLUS command

  B . UPDATE is not a SQL*PLUS command

  C . SELECT is not a SQL*PLUS command

  E . DELETE is not a SQL*PLUS command

  F . RENAME is not a SQL*PLUS command Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 7

• Question 120:

  Consider these three statements:

  create view v1 as select department_id,department_name,last_name from departments join employees using (department_id);

  select department_name,last_name from v1 where department_id=20; select

  A. department_name,e.last_name from departments d, employees ewhere
  B. department_id=e.department_id and d.department_id=20; The first query will be quicker than the second because:
  C. The view has already done the work of joining the tables.
  D. The view uses ISO standard join syntax, which is faster than the Oracle join syntax used in the second query.
  E. The view is precompiled, so the first query requires less dynamic compilation than the second query.
  F. There is no reason for the first query to be quicker.
  D. The view uses ISO standard join syntax, which is faster than the Oracle join syntax used in the second query.