[Free] 2019(Nov) EnsurePass Oracle 1z0-061 Dumps with VCE and PDF 11-20

Get Full Version of the Exam

Question No.11

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


You need to display customers#39; second names where the second name starts with quot;Mcquot; or quot;MC.quot; Which query gives the required output?


  1. Option A

  2. Option B

  3. Option C

  4. Option D

Correct Answer: B

Question No.12

Which statement adds a column called salary to the employees table having 100 rows, which cannot contain null?


  1. Option A

  2. Option B

  3. Option C

  4. Option D

Correct Answer: C

Question No.13

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 d.department_name, e.last_name from departments d, employees e where d.department_id=e.department_id and d.department_id=20;

The first query will be quicker than the second because:

  1. The view has already done the work of joining the tables.

  2. The view uses ISO standard join syntax, which is faster than the Oracle join syntax used in the second query.

  3. The view is precompiled, so the first query requires less dynamic compilation than the second query.

  4. There is no reason for the first query to be quicker.

Correct Answer: D

Question No.14

You have created an index with this statement:

create index ename_i on employees(last_name,first_name);

How can you adjust the index to include the employees#39; birthdays, which is a date type column called DOB?


  2. You can#39;t do this because of the data type mismatch.

  3. You must drop the index and re-create it.

  4. This can only be done if the column DOB is NULL in all existing rows.

Correct Answer: C

Question No.15

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

You need to generate a report of all promos from the promotions table based on the following conditions:

  1. The promo name should not begin with #39;T#39; or #39;N#39;.

  2. The promo should cost more than $20000.

  3. The promo should have ended after 1st January 2001. Which where clause would give the required result?


  1. WHERE promo_name NOT LIKE #39;T%#39; AND promo_name NOT LIKE #39;N%#39; AND promo_cost gt; 20000 AND promo_end_date gt; `1-JAN-01#39;

  2. WHERE promo_name NOT LIKE #39;T%#39; OR promo_name NOT LIKE #39;N%#39; AND promo_cost gt;20000 AND promo_end_date gt; `1-JAN-01#39;

  3. WHERE (promo_name NOT LIKE #39;T%#39; AND promo_name NOT LIKE #39;N%#39;)OR promo_cost

    gt;20000 OR promo_end_date gt; `1-JAN-01#39;

  4. WHERE (promo_name NOT LIKE #39;%T%#39; OR promo_name NOT LIKE #39;%N%#39;) AND(promo_cost

gt;20000 AND promo_end_date gt; `1-JAN-01#39;)

Correct Answer: A


We must use the AND operator, not the OR operator, as all the Boolean statements must be true to meet the conditions.

Question No.16

You want to create a table employees in which the values of columns EMPLOYEES_ID and LOGIN_ID must be unique and not null. Which two SQL statements would create the required



  1. Option A

  2. Option B

  3. Option C

  4. Option D

  5. Option E

  6. Option F

Correct Answer: AF

Question No.17

Which two statements are true regarding single row functions?

  1. MOD: returns the quotient of a division

  2. TRUNC: can be used with number and date values

  3. CONCAT: can be used to combine any number of values

  4. SYSDATE: returns the database server current date and time

  5. INSTR: can be used to find only the first occurrence of a character in a string

  6. TRIM: can be used to remove all the occurrences of a character from a string

Correct Answer: BD


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, #39;char#39;): Finds the date of the next specified day of the week (#39;char#39;) 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[, #39;fmt#39;]): 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[, #39;fmt#39;]): 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(#39;http://www.domain.com#39;, #39;.#39;, 1, 2) = 18

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

Question No.18

Evaluate the following SQL commands:


The command to create a table fails. Identify the two reasons for the SQL statement failure?

  1. You cannot use SYSDATE in the condition of a check constraint.

  2. You cannot use the BETWEEN clause in the condition of a CHECK constraint.

  3. You cannot use the NEXTVAL sequence value as a DEFAULT value for a column.

  4. You cannot use ORD_NO and ITEM_NO columns as a composite primary key because ORD_NO is also the FOREIGN KEY.

Correct Answer: AC Explanation: CHECK Constraint

The CHECK constraint defines a condition that each row must satisfy. The condition can use the same constructs as the query conditions, with the following exceptions:

References to the CURRVAL, NEXTVAL, LEVEL, and ROWNUM pseudocolumns Calls to SYSDATE, UID, USER, and USERENV functions Queries that refer to other values in other rows A single column can have multiple CHECK constraints that refer to the column in its definition.

There is no limit to the number of CHECK constraints that you can define on a column. CHECK constraints can be defined at the column level or table level.

CREATE TABLE employees (…

Salary NUMBER(8, 2) CONSTRAINT emp_salary_min CHECK (salary gt; 0),

Question No.19

Which three tasks can be performed using SQL functions built into Oracle Database?

  1. Displaying a date in a nondefault format

  2. Finding the number of characters in an expression

  3. Substituting a character string in a text expression with a specified string

  4. Combining more than two columns or expressions into a single column in the output

Correct Answer: ABC

Question No.20

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


Using the customers table, you need to generate a report that shows an increase in the credit limit by 15% for all customers. Customers whose credit limit has not been entered should have the message quot;Not Availablequot; displayed.

Which SQL statement would produce the required result?


  1. Option A

  2. Option B

  3. Option C

  4. Option D

Correct Answer: D


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, #39;01-JAN-97#39;) NVL(job_id, #39;No Job Yet#39;)

Get Full Version of the Exam
1z0-061 Dumps
1z0-061 VCE and PDF