Oracle 1Z0-061 Online Practice Questions and Exam Preparation

Oracle 1Z0-061 Online Questions & Answers

• Question 11:

  What does the FORCE option for creating a view do?

  A. creates a view with constraints
  B. creates a view even if the underlying parent table has constraints
  C. creates a view in another schema even if you don't have privileges
  D. creates a view regardless of whether or not the base tables exist
  D. creates a view regardless of whether or not the base tables exist

  ---

  create a view regardless of whether or not the base tables exist. Incorrect answer:

  A. the option is not valid

  B. the option is not valid

  C. the option is not valid Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 11-3

• Question 12:

  You need to display the date 11-Oct-2007 in words as `Eleventh of October, Two Thousand Seven'.

  Which SQL statement would give the required result?

  A. SELECT TO_CHAR('11-oct-2007', 'fmDdspth "of" Month, Year') FROM DUAL;
  B. SELECT TO_CHAR(TO_DATE('11-oct-2007'), 'fmDdspth of month, year') FROM DUAL;
  C. SELECT TO_CHAR(TO_DATE('11-oct-2007'), 'fmDdthsp "of" Month, Year') FROM DUAL;
  D. SELECT TO_DATE(TO_CHAR('11-oct-2007', 'fmDdspth ''of'' Month, Year')) FROM DUAL;
  C. SELECT TO_CHAR(TO_DATE('11-oct-2007'), 'fmDdthsp "of" Month, Year') FROM DUAL;

  ---

  Using the TO_CHAR Function with Dates

  TO_CHAR converts a datetime data type to a value of VARCHAR2 data type in the format specified by the format_model. A format model is a character literal that describes the format of datetime stored in a character string. For example, the

  datetime format model for the string '11-Nov-1999' is 'DD-Mon-YYYY'. You can use the TO_CHAR function to convert a date from its default format to the one that you specify.

  Guidelines

  ?The format model must be enclosed with single quotation marks and is case-sensitive. ?The format model can include any valid date format element. But be sure to separate the date value from the format model with a comma.

  ?The names of days and months in the output are automatically padded with blanks. ?To remove padded blanks or to suppress leading zeros, use the fill mode fm element.

  Elements of the Date Format Model

  --------------------------------------------------------------------- DY Three-letter abbreviation of the day of the week DAY Full name of the day of the week

  DD Numeric day of the month

  MM Two-digit value for the month

  MON Three-letter abbreviation of the month

  MONTH Full name of the month

  YYYY Full year in numbers

  YEAR Year spelled out (in English)

• Question 13:

  Examine the data in the LIST_PRICE and MIN_PRICE columns of the PRODUCTS table:

  

  Which two expressions give the same output? (Choose two.)

  A. NVL(NULLIF(list_price, min_price), 0)
  B. NVL(COALESCE(list_price, min_price), 0)
  C. NVL2(COALESCE(list_price, min_price), min_price, 0)
  D. COALESCE(NVL2(list_price, list_price, min_price), 0)
  B. NVL(COALESCE(list_price, min_price), 0)
  D. COALESCE(NVL2(list_price, list_price, min_price), 0)

  ---

  Using the COALESCE Function

  The advantage of the COALESCE function over the NVL function is that the COALESCE function can take multiple alternate values.

  If the first expression is not null, the COALESCE function returns that expression; otherwise, it does a COALESCE of the remaining expressions.

  Using the COALESCE Function

  The COALESCE function returns the first non-null expression in the list.

  Syntax

  COALESCE (expr1, expr2, .. exprn)

  In the syntax:

  expr1 returns this expression if it is not null ?expr2 returns this expression if the first expression is null and this expression is not null ?exprn returns this expression if the preceding expressions are null Note that all expressions must be of the

  same data type.

• Question 14:

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

  

  All products have a list price.

  You issue the following command to display the total price of each product after a discount of 25% and a tax of 15% are applied on it. Freight charges of $100 have to be applied to all the products.

  

  What would be the outcome if all the parentheses are removed from the above statement?

  A. It produces a syntax error.
  B. The result remains unchanged.
  C. The total price value would be lower than the correct value.
  D. The total price value would be higher than the correct value.
  B. The result remains unchanged.

• Question 15:

  View the Exhibit for the structure of the student and faculty tables.

  

  You need to display the faculty name followed by the number of students handled by the faculty at the base location. Examine the following two SQL statements:

  

  Which statement is true regarding the outcome?

  A. Only statement 1 executes successfully and gives the required result.
  B. Only statement 2 executes successfully and gives the required result.
  C. Both statements 1 and 2 execute successfully and give different results.
  D. Both statements 1 and 2 execute successfully and give the same required result.
  D. Both statements 1 and 2 execute successfully and give the same required result.

• Question 16:

  Examine the structure of the orders table:

  

  You want to find the total value of all the orders for each year and issue the following command:

  

  Which statement is true regarding the outcome?

  A. It executes successfully and gives the correct output.
  B. It gives an error because the TO_CHAR function is not valid.
  C. It executes successfully but does not give the correct output.
  D. It gives an error because the data type conversion in the SELECT list does not match the data type conversion in the GROUP BY clause.
  D. It gives an error because the data type conversion in the SELECT list does not match the data type conversion in the GROUP BY clause.

  ---

  The correct code would be:

  SELECT TO_CHAR(order_date, 'rr'), SUM(order_total) FROM orders

  GROUP BY TO_CHAR(order_date, 'rr');

• Question 17:

  Which two statements are true regarding views? (Choose two.)

  A. A simple view in which column aliases have been used cannot be updated.
  B. Rows cannot be deleted through a view if the view definition contains the DISTINCT keyword.
  C. Rows added through a view are deleted from the table automatically when the view is dropped.
  D. The OR REPLACE option is used to change the definition of an existing view without dropping and recreating it.
  E. The WITH CHECK OPTION constraint can be used in a view definition to restrict the columns displayed through the view.
  B. Rows cannot be deleted through a view if the view definition contains the DISTINCT keyword.
  D. The OR REPLACE option is used to change the definition of an existing view without dropping and recreating it.

• Question 18:

  View the Exhibit and evaluate the structure and data in the CUST_STATUS table.

  

  You issue the following SQL statement:

  

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

  A. It produces an error because the AMT_SPENT column contains a null value.
  B. It displays a bonus of 1000 for all customers whose AMT_SPENT is less than CREDIT_LIMIT.
  C. It displays a bonus of 1000 for all customers whose AMT_SPENT equals CREDIT_LIMIT, or AMT_SPENT is null.
  D. It produces an error because the TO_NUMBER function must be used to convert the result of the NULLIF function before it can be used by the NVL2 function.
  C. It displays a bonus of 1000 for all customers whose AMT_SPENT equals CREDIT_LIMIT, or AMT_SPENT is null.

  ---

  The NULLIF function tests two terms for equality. If they are equal the function returns a null, else it returns the first of the two terms tested. The NULLIF function takes two mandatory parameters of any data type. The syntax is NULLIF (ifunequal, comparison_term), where the parameters ifunequal and comparison_term are compared. If they are identical, then NULL is returned. If they differ, the ifunequal parameter is returned.

• Question 19:

  Which SQL statement accepts user input for the columns to be displayed, the table name, and WHERE condition?

  A. SELECT and1, "and2"FROM and3WHERE last_name = 'and4';
  B. SELECT and1, 'and2'FROM and3WHERE 'andlast_name = 'and4' ';
  C. SELECT and1, and2FROM and3WHERE last_name = 'and4';
  D. SELECT and1, 'and2'FROM EMPWHERE last_name = 'and4';
  C. SELECT and1, and2FROM and3WHERE last_name = 'and4';

  ---

  In a WHERE clause, date and characters values must be enclosed within single quotation marks.

  Sample of the correct syntax

  SELECT EMPLOYEE_ID, andCOLUMN_NAME FROM EMPLOYEES

  Incorrect Answers :

  A. Incorrect use of " symbol

  B. Incorrect use of ' symbol

  D. No input for table name as EMP has been use in the statement.

  Refer: Introduction to Oracle9i: SQL, Oracle University Student Guide, Producing Readable Output with iSQL*PLUS, p. 7-8

• Question 20:

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

  

  You want to display the category with the maximum number of items.

  You issue the following query:

  SQL>SELECT COUNT(*), prod_category_id

  FROM products

  GROUP BY prod_category_id

  HAVING COUNT(*) = (SELECT MAX(COUNT(*)) FROM products);

  What is the outcome?

  A. It executes successfully and gives the correct output.
  B. It executes successfully but does not give the correct output.
  C. It generates an error because the subquery does not have a GROUP BY clause.
  D. It generates an error because = is not valid and should be replaced by the IN operator.
  C. It generates an error because the subquery does not have a GROUP BY clause.