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

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

Question No.191

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

image

Which two SQL statements are valid? (Choose two.)

  1. SELECT promo_id, DECODE (NVL(promo_cost, 0), promo_cost,promo_cost * 0.25, 100) quot;Discountquot;FROM promotions;

  2. SELECT promo_id, DECODE (promo_cost, 10000,DECODE(promo_category, #39;G1#39;, promo_cost

    *.25, NULL),NULL) quot;Catcostquot;FROM promotions;

  3. SELECT promo_id, DECODE (NULLIF(promo_cost, 10000),NULL, promo_cost*.25, #39;N/A#39;) quot;Catcostquot;FROM promotions;

  4. SELECT promo_id, DECODE (promo_cost, gt;10000, #39;High#39;,lt;10000, #39;Low#39;) quot;Rangequot;FROM promotions;

Correct Answer: AB

Explanation:

The DECODE Function

Although its name sounds mysterious, this function is straightforward. The DECODE function implements ifthen-else conditional logic by testing its first two terms for equality and returns the third if they are equal and optionally returns another term if they are not. The DECODE function takes at least three mandatory parameters, but can take many more. The syntax of the function is DECODE(expr1, comp1, iftrue1, [comp2, iftrue2…[ compN, iftrueN]], [iffalse]).

Question No.192

Which two statements are true regarding sub queries? (Choose two.)

  1. A sub query can retrieve zero or more rows.

  2. Only two sub queries can be placed at one level.

  3. A sub query can be used only in SQL query statements.

  4. A sub query can appeal* on either side of a comparison operator.

  5. There is no limit on the number of sub query levels in the WHERE clause of a SELECT statement.

Correct Answer: AD

Question No.193

You notice a performance change in your production Oracle 12c database. You want to know which change caused this performance difference. Which method or feature should you use?

  1. Compare Period ADDM report

  2. AWR Compare Period report

  3. Active Session History (ASH) report

  4. Taking a new snapshot and comparing it with a preserved snapshot

Correct Answer: B

Explanation:

The awrddrpt.sql report is the Automated Workload Repository Compare Period Report. The awrddrpt.sql script is located in the $ORACLE_HOME/rdbms/admin directory.

Incorrect:

Not A: Compare Period ADDM

Use this report to perform a high-level comparison of one workload replay to its capture or to another replay of the same capture. Only workload replays that contain at least 5 minutes of database time can be compared using this report.

Question No.194

See the Exhibit and examine the structure of ORD table:

image

Evaluate the following SQL statements that are executed in a user session in the specified order: CREATE SEQUENCE ord_seq;

SELECT ord_seq.nextval FROM dual;

INSERT INTO ord

VALUES (ord_seq.CURRVAL, #39;25-jan-2007, 101);

UPDATE ord

SET ord_no= ord_seq.NEXTVAL WHERE cust_id =101;

What would be the outcome of the above statements?

  1. All the statements would execute successfully and the ORD_NO column would contain the value 2 for the CUST_ID 101.

  2. The CREATE SEQUENCE command would not execute because the minimum value and maximum value for the sequence have not been specified.

  3. The CREATE SEQUENCE command would not execute because the starting value of the sequence and the increment value have not been specified.

  4. All the statements would execute successfully and the ORD_NO column would have the value 20 for the CUST_ID 101 because the default CACHE value is 20.

Correct Answer: A

Question No.195

Which SQL statement displays the date March 19, 2001 in a format that appears as quot;Nineteenth of March 2001 12:00:00 AMquot;?

  1. SELECTTO_CHAR(TO_DATE(#39;19-Mar-2001#39;, `DD-Mon-YYYY#39;), `fmDdspthquot;ofquot; Month YYYY fmHH:MI:SS AM#39;) NEW_DATE. FROM dual;

  2. SELECTTO_CHAR(TO_DATE(#39;19-Mar-2001#39;, `DD-Mon-YYYY#39;), `Ddspthquot;ofquot; Month YYYY fmHH:MI:SS AM#39;) NEW_DATEFROM dual;

  3. SELECTTO_CHAR(TO_DATE(#39;19-Mar-2001#39;, `DD-Mon-YYYY#39;), `fmDdspth quot;ofquot; Month YYYYHH:MI:SS AM#39;) NEW_DATE FROM dual;

  4. SELECTTO_CHAR(TO_DATE(#39;19-Mar-2001#39;, `DD-Mon-YYYY), `fmDdspth quot;ofquot; Month YYYYfmtHH:HI:SS AM#39;) NEW_DATE FROM dual;

Correct Answer: A

Question No.196

Evaluate the following SQL statements:

image

You issue the following command to create a view that displays the IDs and last names of the sales staff in the organization.

image

Which two statements are true regarding the above view? (Choose two.)

  1. It allows you to update job IDs of the existing sales staff to any other job ID in the EMPLOYEES table

  2. It allows you to delete details of the existing sales staff from the EMPLOYEES table

  3. It allows you to insert rows into the EMPLOYEES table

  4. It allows you to insert IDs, last names, and job IDs of the sales staff from the view if it is used in multitable INSERT statements

Correct Answer: BD

Question No.197

Top N analysis requires and . (Choose two.)

  1. the use of rowid

  2. a GROUP BY clause

  3. an ORDER BY clause

  4. only an inline view

  5. an inline view and an outer query

Correct Answer: CE

Explanation:

The correct statement for Top-N Analysis SELECT [coloumn_list], ROWNUM FROM (SELECT [coloumn_list]

FROM table

ORDER BY Top-N_coloumn) WHERE ROWNUM lt;= N;

Incorrect answer:

  1. ROWID is not require

  2. GROUP BY clause is not require

D. Must have inline view and outer query.

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

Question No.198

View the Exhibit and examine the structure of the CUSTOMERS table. Evaluate the following SQL statement:

image

Which statement is true regarding the outcome of the above query?

image

  1. It executes successfully.

  2. It returns an error because the BETWEEN operator cannot be used in the HAVING clause.

  3. It returns an error because WHERE and HAVING clauses cannot be used in the same SELECT statement.

  4. It returns an error because WHERE and HAVING clauses cannot be used to apply conditions on the same column.

Correct Answer: A

Question No.199

What is true about the WITH GRANT OPTION clause?

  1. It allows a grantee DBA privileges.

  2. It is required syntax for object privileges.

  3. It allows privileges on specified columns of tables.

  4. It is used to grant an object privilege on a foreign key column.

  5. It allows the grantee to grant object privileges to other users and roles.

Correct Answer: E

Explanation:

The GRANT command with the WITH GRANT OPTION clause allows the grantee to grant object privileges to other users and roles.

Incorrect Answers

A: The WITH GRANT OPTION does not allow a grantee DBA privileges.

B: It is not required syntax for object privileges. It is optional clause of GRANT command. C: GRANT command does not allows privileges on columns of tables.

D: It is not used to grant an object privilege on a foreign key column.

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

Question No.200

Which three statements/commands would cause a transaction to end? (Choose three.)

  1. COMMIT

  2. SELECT

  3. CREATE

  4. ROLLBACK

  5. SAVEPOINT

Correct Answer: ACD

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

Leave a Reply