Oracle 1Z0-061 Online Practice Questions and Exam Preparation

Oracle 1Z0-061 Online Questions & Answers

• Question 31:

  You need to calculate the number of days from 1st January 2007 till date . Dates are stored in the default format of dd-mon-rr. Which two SQL statements would give the required output? (Choose two.)

  A. SELECT SYSDATE - '01-JAN-2007' FROM DUAL:
  B. SELECT SYSDATE - TOJDATE(X)1/JANUARY/2007") FROM DUAL:
  C. SELECT SYSDATE - TOJDATE('01-JANUARY-2007') FROM DUAL:
  D. SELECT TO_CHAR(SYSDATE. 'DD-MON-YYYY') - '01-JAN-2007' FROM DUAL:
  E. SELECT TO_DATE(SYSDATE. *DD/MONTH/YYYY') - '01/JANUARY/2007' FROM DUAL:
  B. SELECT SYSDATE - TOJDATE(X)1/JANUARY/2007") FROM DUAL:
  C. SELECT SYSDATE - TOJDATE('01-JANUARY-2007') FROM DUAL:

• Question 32:

  Examine the structure of the EMPLOYEES table:

  

  You want to generate a report that contains the department IDs and last names of employees ordered by hire date in their respective departments. Employees from one department must appear in a single row. Which query will provide the required output?

  A. SELECT department_id "Dept.",LISTAGG (last_name, `;') WITHIN GROUP (ORDER BY hire_date)" Employees"FROM employeesGROUP BY department_idORDER BY department_id;
  B. SELECT department_id "Dept.",LISTAGG (last_name, `;') WITHIN GROUP (ORDER BY hire_date)" Employees"FROM employeesGROUP BY department_idORDER BY hire_date;
  C. SELECT department_id "Dept.",LISTAGG (last_name, `;') WITHIN GROUP (ORDER BY hire_date)" Employees")FROM employeesGROUP BY department_idORDER BY hire_date;
  D. SELECT department_id "Dept.",LISTAGG (last_name, `;') WITHIN GROUP ORDER BY (hire_date)" Employees")FROM employeesGROUP BY department_idORDER BY department_id;
  C. SELECT department_id "Dept.",LISTAGG (last_name, `;') WITHIN GROUP (ORDER BY hire_date)" Employees")FROM employeesGROUP BY department_idORDER BY hire_date;

• Question 33:

  Which two statements are true regarding single row functions?

  A. MOD: returns the quotient of a division
  B. TRUNC: can be used with number and date values
  C. CONCAT: can be used to combine any number of values
  D. SYSDATE: returns the database server current date and time
  E. INSTR: can be used to find only the first occurrence of a character in a string
  F. TRIM: can be used to remove all the occurrences of a character from a string
  B. TRUNC: can be used with number and date values
  D. SYSDATE: returns the database server current date and time

  ---

  ROUND: Rounds value to a specified decimal

  TRUNC: Truncates value to a specified decimal

  MOD: Returns remainder of division

  SYSDATE is a date function that returns the current database server date and time.

  Date-Manipulation Functions

  Date functions operate on Oracle dates. All date functions return a value of the DATE data type except MONTHS_BETWEEN, which returns a numeric value. MONTHS_BETWEEN(date1, date2): Finds the number of months between date1

  and date2. The result can be positive or negative. If date1 is later than date2, the result is positive; if date1 is earlier than date2, the result is negative. The noninteger part of the result represents a portion of the month.

  ADD_MONTHS(date, n): Adds n number of calendar months to date. The value of n must be an integer and can be negative.

  NEXT_DAY(date, 'char'): Finds the date of the next specified day of the week ('char') following date. The value of char may be a number representing a day or a character string.

  LAST_DAY(date): Finds the date of the last day of the month that contains date The above list is a subset of the available date functions. ROUND and TRUNC number functions can also be used to manipulate the date values as shown

  below:

  ROUND(date[, 'fmt']): Returns date rounded to the unit that is specified by the format model fmt. If the format model fmt is omitted, date is rounded to the nearest day. TRUNC(date[, 'fmt']): Returns date with the time portion of the day

  truncated to the unit that is specified by the format model fmt. If the format model fmt is omitted, date is truncated to the nearest day.

  The CONCAT Function

  The CONCAT function joins two character literals, columns, or expressions to yield one larger character expression. Numeric and date literals are implicitly cast as characters when they occur as parameters to the CONCAT function. Numeric

  or date expressions are evaluated before being converted to strings ready to be concatenated. The CONCAT function takes two parameters. Its syntax is CONCAT(s1, s2), where s1 and s2 represent string literals, character column values, or

  expressions resulting in character values. The INSTR(source string, search item, [start position], [nth occurrence of search item]) function returns a number that represents the position in the source string, beginning from the given start

  position, where the nth occurrence of the search item begins:

  instr('http://www.domain.com', '.', 1, 2) = 18

  The TRIM function literally trims off leading or trailing (or both) character strings from a given source string:

• Question 34:

  Which two statements are true about constraints? (Choose two.)

  A. The UNIQUE constraint does not permit a null value for the column.
  B. A UNIQUE index gets created for columns with PRIMARY KEY and UNIQUE constraints.
  C. The PRIMARY KEY and FOREIGN KEY constraints create a UNIQUE index.
  D. The NOT NULL constraint ensures that null values are not permitted for the column.
  B. A UNIQUE index gets created for columns with PRIMARY KEY and UNIQUE constraints.
  D. The NOT NULL constraint ensures that null values are not permitted for the column.

  ---

  B: A unique constraint can contain null values because null values cannot be compared to anything.

  D:

  The NOT NULL constraint ensure that null value are not permitted for the column Incorrect answer:

  A.

  statement is not true

  C.

  statement is not true Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 10-9

• Question 35:

  See the structure of the PROGRAMS table:

  

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

  A. SELECT NVL(ADD_MONTHS(END_DATE, 1), SYSDATE)FROM programs;
  B. SELECT TO_DATE(NVL(SYSDATE-END_DATE, SYSDATE))FROM programs;
  C. SELECT NVL(MONTHS_BETWEEN(start_date, end_date), 'Ongoing')FROM programs;
  D. SELECT NVL(TO_CHAR(MONTHS_BETWEEN(start_date, end_date)), 'Ongoing') FROM programs;
  A. SELECT NVL(ADD_MONTHS(END_DATE, 1), SYSDATE)FROM programs;
  D. SELECT NVL(TO_CHAR(MONTHS_BETWEEN(start_date, end_date)), 'Ongoing') FROM programs;

  ---

  NVL Function

  Converts a null value to an actual value:

  Data types that can be used are date, character, and number.

  Data types must match:

  NVL(commission_pct, 0)

  NVL(hire_date, '01-JAN-97')

  NVL(job_id, 'No Job Yet')

  MONTHS_BETWEEN(date1, date2): Finds the number of months between date1 and date2

  The result can be positive or negative. If date1 is later than date2, the result is positive; if date1 is earlier than date2, the result is negative. The noninteger part of the result represents a portion of the month.

  MONTHS_BETWEEN returns a numeric value. - answer C NVL has different datatypes - numeric and strings, which is not possible!

  The data types of the original and if null parameters must always be compatible. They must either be of the same type, or it must be possible to implicitly convert if null to the type of the original parameter. The NVL function returns a value with

  the same data type as the original parameter.

• Question 36:

  Which three are true? (Choose three.)

  A. A MERGE statement is used to merge the data of one table with data from another.
  B. A MERGE statement replaces the data of one table with that of another.
  C. A MERGE statement can be used to insert new rows into a table.
  D. A MERGE statement can be used to update existing rows in a table.
  A. A MERGE statement is used to merge the data of one table with data from another.
  C. A MERGE statement can be used to insert new rows into a table.
  D. A MERGE statement can be used to update existing rows in a table.

  ---

  The MERGE Statement allows you to conditionally insert or update data in a table. If the rows are present in the target table which match the join condition, they are updated if the rows are not present they are inserted into the target table

• Question 37:

  Evaluate the following SQL statements:

  Exhibit:

  

  Which is the correct output of the above query?

  A. +00-300, +54-02, +00 11:12:10.123457
  B. +00-300, +00-650, +00 11:12:10.123457
  C. +25-00, +54-02, +00 11:12:10.123457
  D. +25-00, +00-650, +00 11:12:10.123457
  C. +25-00, +54-02, +00 11:12:10.123457

• Question 38:

  Which statements are correct regarding indexes? (Choose all that apply.)

  A. For each data manipulation language (DML) operation performed, the corresponding indexes are automatically updated.
  B. A nondeferrable PRIMARY KEY or UNIQUE KEY constraint in a table automatically creates a unique index.
  C. A FOREIGN KEY constraint on a column in a table automatically creates a non unique key
  D. When a table is dropped, the corresponding indexes are automatically dropped
  A. For each data manipulation language (DML) operation performed, the corresponding indexes are automatically updated.
  B. A nondeferrable PRIMARY KEY or UNIQUE KEY constraint in a table automatically creates a unique index.
  D. When a table is dropped, the corresponding indexes are automatically dropped

• Question 39:

  Which statement is true regarding sub queries?

  A. The LIKE operator cannot be used with single- row subqueries.
  B. The NOT IN operator is equivalent to IS NULL with single- row subqueries.
  C. =ANY and =ALL operators have the same functionality in multiple- row subqueries.
  D. The NOT operator can be used with IN, ANY, and ALL operators in multiple- row subqueries.
  D. The NOT operator can be used with IN, ANY, and ALL operators in multiple- row subqueries.

  ---

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

  Which view should a user query to display the columns associated with the constraints on a table owned by the user?

  A. USER_CONSTRAINTS
  B. USER_OBJECTS
  C. ALL_CONSTRAINTS
  D. USER_CONS_COLUMNS
  E. USER_COLUMNS
  D. USER_CONS_COLUMNS

  ---

  view the columns associated with the constraint names in the USER_CONS_COLUMNS view. Incorrect answer:

  A. table to view all constraints definition and names B. show all object name belong to user

  C. does not display column associated

  E. no such view

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