Oracle 1Z0-061 Online Practice Questions and Exam Preparation

Oracle 1Z0-061 Online Questions & Answers

• Question 101:

  Which three arithmetic operations can be performed on a column by using a SQL function that is built into Oracle database? (Choose three.)

  A. addition
  B. subtraction
  C. raising to a power
  D. finding the quotient
  E. finding the lowest value
  A. addition
  C. raising to a power
  E. finding the lowest value

• Question 102:

  View the Exhibit and examine the structure of ORD and ORD_ITEMS tables.

  The ORD_NO column is PRIMARY KEY in the ORD table and the ORD_NO and ITEM_NO columns are composite PRIMARY KEY in the ORD_ITEMS table.

  Which two CREATE INDEX statements are valid? (Choose two.)

  

  A. CREATE INDEX ord_idx1ON ord(ord_no);
  B. CREATE INDEX ord_idx2ON ord_items(ord_no);
  C. CREATE INDEX ord_idx3ON ord_items(item_no);
  D. CREATE INDEX ord_idx4ON ord, ord_items(ord_no, ord_date, qty);
  B. CREATE INDEX ord_idx2ON ord_items(ord_no);
  C. CREATE INDEX ord_idx3ON ord_items(item_no);

  ---

  How Are Indexes Created?

  You can create two types of indexes.

  Unique index: The Oracle server automatically creates this index when you define a column in a table to have a PRIMARY KEY or a UNIQUE constraint. The name of the index is the name that is given to the constraint.

  Nonunique index: This is an index that a user can create. For example, you can create the FOREIGN KEY column index for a join in a query to improve the speed of retrieval. Note: You can manually create a unique index, but it is

  recommended that you create a unique constraint, which implicitly creates a unique index.

• Question 103:

  Which four are types of functions available in SQL? (Choose 4)

  A. string
  B. character
  C. integer
  D. calendar
  E. numeric
  F. translation
  G. date
  H. conversion
  B. character
  E. numeric
  G. date
  H. conversion

  ---

  SQL have character, numeric, date, conversion function. Incorrect answer:

  A. SQL have character, numeric, date, conversion function. C. SQL have character, numeric, date, conversion function. D. SQL have character, numeric, date, conversion function. F. QL have character, numeric, date, conversion function.

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

• Question 104:

  You want to display the date for the first Monday of the next month and issue the following command:

  

  What is the outcome?

  A. It executes successfully and returns the correct result.
  B. It executes successfully but does not return the correct result.
  C. It generates an error because TO_CHAR should be replaced with TO_DATE.
  D. It generates an error because rrrr should be replaced by rr in the format string.
  E. It generates an error because fm and double quotation marks should not be used in the format string.
  A. It executes successfully and returns the correct result.

• Question 105:

  The PRODUCTS table has the following structure:

  

  Evaluate the following two SQL statements:

  

  Which statement is true regarding the outcome?

  A. Both the statements execute and give the same results.
  B. Both the statements execute and give different results.
  C. Only the second SQL statement executes successfully.
  D. Only the first SQL statement executes successfully.
  B. Both the statements execute and give different results.

  ---

  Using the NVL2 Function

  The NVL2 function examines the first expression. If the first expression is not null, the NVL2 function returns the second expression. If the first expression is null, the third expression is returned.

  Syntax

  NVL2(expr1, expr2, expr3)

  In the syntax:

  Expr1 is the source value or expression that may contain a null Expr2 is the value that is returned if expr1 is not null Expr3 is the value that is returned if expr1 is null

• Question 106:

  You need to create a table with the following column specifications:

  1.

  Employee ID (numeric data type) for each employee

  2.

  Employee Name (character data type) that stores the employee name

  3.

  Hire date, which stores the date of joining the organization for each employee

  4.

  Status (character data type), that contains the value 'active1 if no data is entered

  5.

  Resume (character large object [CLOB] data type), which contains the resume submitted by the employee Which is the correct syntax to create this table?

  

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

  ---

  CLOB Character data (up to 4 GB)

  NUMBER [(p, s)] Number having precision p and scale s (Precision is the total number of decimal digits and scale is the number of digits to the right of the decimal point; precision can range from 1 to 38, and scale can range from ?4 to 127.)

• Question 107:

  Which statement is true regarding the COALESCE function?

  A. It can have a maximum of five expressions in a list.
  B. It returns the highest NOT NULL value in the list for all rows.
  C. It requires that all expressions in the list must be of the same data type.
  D. It requires that at least one of the expressions in the list must have a NOT NULL value.
  C. It requires that all expressions in the list must be of the same data type.

  ---

  The COALESCE Function

  The COALESCE function returns the first nonnull value from its parameter list. If all its parameters are null, then null is returned.

  The COALESCE function takes two mandatory parameters and any number of optional parameters. The syntax is COALESCE(expr1, expr2, ..., exprn), where expr1 is returned if it is not null, else expr2 if it is not null, and so on. COALESCE

  is a general form of the NVL function, as the following two equations illustrate:

  COALESCE(expr1, expr2) = NVL(expr1, expr2)

  COALESCE(expr1, expr2, expr3) = NVL(expr1, NVL(expr2, expr3)) The data type COALESCE returns if a not null value is found is the same as that of the first not null parameter.

  To avoid an "ORA-00932: inconsistent data types" error, all not null parameters must have data types compatible with the first not null parameter.

• Question 108:

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

  

  Using the customers table, you need to generate a report that shows the average credit limit for customers in Washington and NEW YORK. Which SQL statement would produce the required result?

  

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

• Question 109:

  Examine the data in the CUST_NAME column of the CUSTOMERS table. CUST_NAME

  Lex De Haan Renske Ladwig Jose Manuel Urman Jason Mallin You want to extract only those customer names that have three names and display the * symbol in place of the first name as follows: CUST NAME

  *** De Haan **** Manuel Urman Which two queries give the required output? (Choose two.)

  A. SELECT LPAD(SUBSTR(cust_name, INSTR(cust_name, ' ')), LENGTH(cust_name), '*') "CUST NAME" FROM customersWHERE INSTR(cust_name, ' ', 1, 2)0;
  B. SELECT LPAD(SUBSTR(cust_name, INSTR(cust_name, ' ')), LENGTH(cust_name), '*') "CUST NAME" FROM customersWHERE INSTR(cust_name, ' ', -1, 2)0;
  C. SELECT LPAD(SUBSTR(cust_name, INSTR(cust_name, ' ')), LENGTH(cust_name)- INSTR(cust_name, ''), '*') "CUST NAME"FROM customersWHERE INSTR(cust_name, ' ', - 1, -2)0;
  D. SELECT LPAD(SUBSTR(cust_name, INSTR(cust_name, ' ')), LENGTH(cust_name)- INSTR(cust_name, ' '), '*') "CUST NAME"FROM customersWHERE INSTR(cust_name, ' ', 1, 2)0 ;
  A. SELECT LPAD(SUBSTR(cust_name, INSTR(cust_name, ' ')), LENGTH(cust_name), '*') "CUST NAME" FROM customersWHERE INSTR(cust_name, ' ', 1, 2)0;
  B. SELECT LPAD(SUBSTR(cust_name, INSTR(cust_name, ' ')), LENGTH(cust_name), '*') "CUST NAME" FROM customersWHERE INSTR(cust_name, ' ', -1, 2)0;

• Question 110:

  View the Exhibit and examine the structure of CUSTOMERS and SALES tables.

  

  Evaluate the following SQL statement:

  UPDATE (SELECT prod_id, cust_id, quantity_sold, time_id

  FROM sales)

  SET time_id = '22-MAR-2007'

  WHERE cust_id = (SELECT cust_id

  FROM customers

  WHERE cust_last_name = 'Roberts' AND

  credit_limit = 600);

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

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

  ---

  One UPDATE statement can change rows in only one table, but it can change any number of rows in that table.