[Free] 2018(Jan) EnsurePass Pass4sure Oracle 1z0-051 Dumps with VCE and PDF 181-190

Ensurepass.com : Ensure you pass the IT Exams
2018 Jan Oracle Official New Released 1z0-051
100% Free Download! 100% Pass Guaranteed!
http://www.EnsurePass.com/1z0-051.html

Oracle Database: SQL Fundamentals I

Question No: 181 – (Topic 2)

The CUSTOMERS table has these columns:

Ensurepass 2018 PDF and VCE

The CUSTOMER_ID column is the primary key for the table.

You need to determine how dispersed your customer base is.

Which expression finds the number of different countries represented in the CUSTOMERS table?

  1. COUNT(UPPER(country_address))

  2. COUNT(DIFF(UPPER(country_address)))

  3. COUNT(UNIQUE(UPPER(country_address)))

  4. COUNT DISTINCT UPPER(country_address)

  5. COUNT(DISTINCT (UPPER(country_address)))

Answer: E

Question No: 182 – (Topic 2)

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

Ensurepass 2018 PDF and VCE

Which two tasks would require subqueries? (Choose two.)

  1. Display the minimum list price for each product status.

  2. Display all suppliers whose list price is less than 1000.

  3. Display the number of products whose list price is more than the average list price.

  4. Display the total number of products supplied by supplier 102 and have product status as #39;obsolete#39;.

  5. Display all products whose minimum list price is more than the average list price of products and have the status #39;orderable#39;.

Answer: C,E

Question No: 183 – (Topic 2)

Evaluate the following query:

SELECT INTERVAL #39;300#39; MONTH, INTERVAL #39;54-2#39; YEAR TO MONTH,

INTERVAL #39;11:12:10.1234567#39; HOUR TO SECOND

FROM dual;

What is the correct output of the above query?

A. 25-00 , 54-02, 00 11:12:10.123457

B. 00-300, 54-02, 00 11:12:10.123457

C. 25-00 , 00-650, 00 11:12:10.123457

D. 00-300 , 00-650, 00 11:12:10.123457

Answer: A Explanation: Datetime Data Types

You can use several datetime data types: INTERVAL YEAR TO MONTH

Stored as an interval of years and months INTERVAL DAY TO SECOND

Stored as an interval of days, hours, minutes, and seconds

Question No: 184 – (Topic 2)

Examine the structure of the EMPLOYEES and NEW_EMPLOYEES tables:

Ensurepass 2018 PDF and VCE

Which DELETE statement is valid?

  1. DELETE FROM employees

    WHERE employee_id = (SELECT employee_id FROM employees);

  2. DELETE * FROM employees

    WHERE employee_id = (SELECT employee_id FROM new_employees);

  3. DELETE FROM employees

    WHERE employee_id IN(SELECT employee_id FROM new_employees

    WHERE name = #39;Carrey#39;);

  4. DELETE * FROM employees

WHERE employee_id IN (SELECT employee_id FROM new_employees

WHERE last_name = #39;Carrey#39;);

Answer: C Explanation: Explanation:

The correct syntax for DELETE statement

DELETE [ FROM ] table

[ WHERE condition ]; Incorrect Answers :

  1. #39;=#39; is use in the statement and sub query will return more than one row. Error Ora-01427: single-row sub query returns more than one row.

  2. Incorrect DELETE statement

D. Incorrect DELETE statement

Refer: Introduction to Oracle9i: SQL, Oracle University Student Guide, Manipulating Data, p. 8-19

Question No: 185 – (Topic 2)

What are two reasons to create synonyms? (Choose two.)

  1. You have too many tables.

  2. Your tables names are too long.

  3. Your tables have difficult names.

  4. You want to work on your own tables.

  5. You want to use another schema#39;s tables.

  6. You have too many columns in your tables.

Answer: B,C

Explanation:

Create a synonyms when the names of the tables are too long or the table names are difficult.

Question No: 186 – (Topic 2)

Which statements are true regarding single row functions? (Choose all that apply.)

  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

Answer: B,D Explanation:

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: 187 – (Topic 2)

View the Exhibit and examine the structures of the EMPLOYEES and DEPARTMENTS tables.

You want to update the EMPLOYEES table as follows:4 ? 4;

-Update only those employees who work in Boston or Seattle (locations 2900 and 2700).

-Set department_id for these employees to the department_id corresponding to London (location_id 2100).

-Set the employees#39; salary in location_id 2100 to 1.1 times the average salary of their department.

-Set the employees#39; commission in location_id 2100 to 1.5 times the average commission of their department.

You issue the following command:

SQLgt;UPDATE employees SET department_id = (SELECT department_id FROM departments WHERE location_id = 2100), (salary, commission) =

(SELECT 1.1*AVG(salary), 1.5*AVG(commission) FROM employees, departments

WHERE departments.location_id IN(2900,2700,2100))

WHERE department_id IN (SELECT department_id FROM departments WHERE location_id = 2900 OR location_id = 2700)

What is the outcome?

  1. It executes successfully and gives the correct result.

  2. It executes successfully but does not give the correct result.

  3. It generates an error because a subquery cannot have a join condition in an UPDATE statement.

  4. It generates an error because multiple columns (SALARY, COMMISION) cannot be specified together in an

UPDATE statement.

Answer: B

Question No: 188 – (Topic 2)

The DBA issues this SQL command: CREATE USER Scott

IDENTIFIED by tiger;

What privileges does the user Scott have at this point?

  1. No privileges.

  2. Only the SELECT privilege.

  3. Only the CONNECT privilege.

  4. All the privileges of a default user.

Answer: A Explanation:

There are no privileges for the user Scott at this point. They are not added themselves to the user immediately after creation. The DBA needs to grant all privileges explicitly.

Incorrect Answers

B:There are no privileges for the user Scott at this point. SELECT privilege needs to be added to the user Scott.

C:There are no privileges for the user Scott at this point. CONNECT privilege needs to be added to the user Scott.

D:There is no default user in Oracle.

OCP Introduction to Oracle 9i: SQL Exam Guide, Jason Couchman, p. 348-351 Chapter 8: User Access in Oracle

Question No: 189 – (Topic 2)

What is true of using group functions on columns that contain NULL values?

  1. Group functions on columns ignore NULL values.

  2. Group functions on columns returning dates include NULL values.

  3. Group functions on columns returning numbers include NULL values.

  4. Group functions on columns cannot be accurately used on columns that contain NULL values.

  5. Group functions on columns include NULL values in calculations if you use the keyword INC_NULLS.

Answer: A

Explanation: group functions on column ignore NULL values

Incorrect answer:

Bgroup functions on column ignore NULL values Cgroup functions on column ignore NULL values

DNVL function can be use for column with NULL values Eno such INC_NULLS keyword

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

Question No: 190 – (Topic 2)

View the Exhibit; e xamine the structure of the PROMOTIONS table.

Ensurepass 2018 PDF and VCE

Each promotion has a duration of at least seven days.

Your manager has asked you to generate a report, which provides the weekly cost for each promotion done to l date.

Which query would achieve the required result?

  1. SELECT promo_name, promo_cost/promo_end_date-promo_begin_date/7 FROM promotions;

  2. SELECT promo_name,(promo_cost/promo_end_date-promo_begin_date)/7 FROM promotions;

  3. SELECT promo_name, promo_cost/(promo_end_date-promo_begin_date/7) FROM

    promotions;

  4. SELECT promo_name, promo_cost/((promo_end_date-promo_begin_date)/7) FROM promotions;

Answer: D

100% Ensurepass Free Download!
Download Free Demo:1z0-051 Demo PDF
100% Ensurepass Free Guaranteed!
1z0-051 Dumps

EnsurePass ExamCollection Testking
Lowest Price Guarantee Yes No No
Up-to-Dated Yes No No
Real Questions Yes No No
Explanation Yes No No
PDF VCE Yes No No
Free VCE Simulator Yes No No
Instant Download Yes No No

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.