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

Get Full Version of the Exam
http://www.EnsurePass.com/1z0-061.html

Question No.151

View the Exhibit and examine the structure of the PROMOTIONS table:

image

Using the PROMOTIONS table, you need to find out the average cost for all promos in the range

$0-2000 and $2000-5000 in category A. You issue the following SQL statements:

image

What would be the outcome?

  1. It generates an error because multiple conditions cannot be specified for the WHEN clause

  2. It executes successfully and gives the required result

  3. It generates an error because CASE cannot be used with group functions

  4. It generates an error because NULL cannot be specified as a return value

Correct Answer: B Explanation: CASE Expression

Facilitates conditional inquiries by doing the work of an IF-THEN-ELSE statement:

CASE expr WHEN comparison_expr1 THEN return_expr1 [WHEN comparison_expr2 THEN return_expr2

WHEN comparison_exprn THEN return_exprn ELSE else_expr]

END

Question No.152

Examine the structure of the EMPLOYEES and NEW_EMPLOYEES tables:

image

Which DELETE statement is valid?

  1. DELETE FROM employeesWHERE employee_id = (SELECT employee_id FROM employees);

  2. DELETE * FROM employeesWHERE employee_id=(SELECT employee_id FROM new_employees);

  3. DELETE FROM employeesWHERE employee_id IN (SELECT employee_id . FROM new_employees . WHERE name = `Carrey#39;);

  4. DELETE * FROM employeesWHERE employee_id IN (SELECT employee_id . FROM new_employees . WHERE name = `Carrey#39;);

Correct Answer: C

Question No.153

What is true about sequences?

  1. The start value of the sequence is always 1.

  2. A sequence always increments by 1.

  3. The minimum value of an ascending sequence defaults to 1.

  4. The maximum value of descending sequence defaults to 1.

Correct Answer: C

Question No.154

The PRODUCTS table has the following structure:

image

Evaluate the following two SQL statements:

image

Which statement is true regarding the outcome?

  1. Both the statements execute and give the same results.

  2. Both the statements execute and give different results.

  3. Only the second SQL statement executes successfully.

  4. Only the first SQL statement executes successfully.

Correct Answer: B

Explanation:

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 No.155

You work as a database administrator at ABC.com. You study the exhibit carefully.

image

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

  1. UPDATE promotionsSET promo_cost = promo_cost 100WHERE TO_CHAR(promo_end_date, #39;yyyy#39;) gt; #39;2000#39;;

  2. SELECT promo_begin_dateFROM promotionsWHERE TO_CHAR(promo_begin_date, #39;mon dd yy#39;)=#39;jul 01 98#39;;

  3. UPDATE promotionsSET promo_cost = promo_cost 100WHERE promo_end_date gt; TO_DATE(SUBSTR(#39;01-JAN-2000#39;, 8));

  4. SELECT TO_CHAR(promo_begin_date, #39;dd/month#39;)FROM promotionsWHERE promo_begin_date IN (TO_DATE(#39;JUN 01 98#39;), TO_DATE(#39;JUL 01 98#39;));

Correct Answer: AB

Question No.156

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

  1. USER_CONSTRAINTS

  2. USER_OBJECTS

  3. ALL_CONSTRAINTS

  4. USER_CONS_COLUMNS

  5. USER_COLUMNS

Correct Answer: D

Explanation:

view the columns associated with the constraint names in the USER_CONS_COLUMNS view.

Incorrect answer:

  1. table to view all constraints definition and names

  2. show all object name belong to user

  3. does not display column associated

E. no such view

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

Question No.157

The COMMISSION column shows the monthly commission earned by the employee.

image

Which two tasks would require sub queries or joins in order to be performed in a single step? (Choose two.)

  1. listing the employees who earn the same amount of commission as employee 3

  2. finding the total commission earned by the employees in department 10

  3. finding the number of employees who earn a commission that is higher than the average commission of the company

  4. listing the departments whose average commission is more that 600

  5. listing the employees who do not earn commission and who are working for department 20 in descending order of the employee ID

  6. listing the employees whose annual commission is more than 6000

Correct Answer: AC

Question No.158

Which statement is true regarding the INTERSECT operator?

  1. It ignores NULL values

  2. The number of columns and data types must be identical for all SELECT statements in the query

  3. The names of columns in all SELECT statements must be identical

  4. Reversing the order of the intersected tables the result

Correct Answer: B

Explanation:

INTERSECT Returns only the rows that occur in both queries#39; result sets, sorting them and removing duplicates.

The columns in the queries that make up a compound query can have different names, but the output result set will use the names of the columns in the first query.

Question No.159

Which one is a system privilege?

  1. SELECT

  2. DELETE

  3. EXECUTE

  4. ALTER TABLE

  5. CREATE TABLE

Correct Answer: E

Question No.160

You work as a database administrator at ABC.com. You study the exhibit carefully.

image

Evaluate the following query:

image

The above query produces an error on execution. What is the reason for the error?

  1. An alias cannot be used in an expression

  2. The alias MIDPOINT should be enclosed within double quotation marks for the CUST_CREDIT_LIMIT/2 expression

  3. The MIDPOINT 100 expression gives an error because CUST_CREDIT_LIMIT contains NULL values

  4. The alias NAME should not be enclosed within double quotation marks

Correct Answer: A

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

You must be logged in to post a comment.

Proudly powered by WordPress   Premium Style Theme by www.gopiplus.com