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

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

Question No.281

View the exhibit and examine the description for the SALES and CHANNELS tables.

image

You issued the following SQL statement to insert a row in the SALES table:

INSERT INTO sales VALUES

(23, 2300, SYSDATE, (SELECT channel_id

FROM channels

WHERE channel_desc=#39;Direct Sales#39;), 12, 1, 500);

Which statement is true regarding the execution of the above statement?

  1. The statement will execute and the new row will be inserted in the SALES table.

  2. The statement will fail because subquery cannot be used in the VALUES clause.

  3. The statement will fail because the VALUES clause is not required with subquery.

  4. The statement will fail because subquery in the VALUES clause is not enclosed with in single quotation marks.

Correct Answer: A

Question No.282

You want to write a query that accepts user input for columns and conditions every time it is executed.

The user needs to be promoted only once for the table input. Which query provides the required result?

  1. SELECT amp;col1, col2FROM amp;amp;tableWHERE amp;condition = amp;cond;

  2. SELECT amp;col1, col2FROM quot;amp;amp;tablequot;WHERE amp;condition = amp;cond;

  3. SELECT amp;col1, col2FROM amp;amp;tableWHERE amp;condition = amp;amp;cond;

  4. SELECT amp;col1, `col2#39;FROM amp;tableWHERE amp;amp;condition = `amp;cond#39;;

Correct Answer: A

Question No.283

Which constraint can be defined only at the column level?

  1. UNIQUE

  2. NOT NULL

  3. CHECK

  4. PRIMARY KEY

  5. FOREIGN KEY

Correct Answer: B

Explanation:

The NOT NULL constraint can be specified only at the column level, not at the table level.

Incorrect answer:

A. UNIQUE can be define at table level

  1. CHECK can be define at table level

  2. PRIMARY KEY can be define at table level

  3. FOREIGN KEY can be define at table level

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

Question No.284

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

image

In the CUSTOMERS table, the CUST_LAST_NAME column contains the values #39;Anderson#39; and #39;Ausson#39;.

You issue the following query:

image

What would be the outcome?

  1. #39;Oder#39; and #39;Aus#39;

  2. an error because the TRIM function specified is not valid

  3. an error because the LOWER function specified is not valid

  4. an error because the REPLACE function specified is not valid

Correct Answer: B

Question No.285

Examine the structure of the SHIPMENTS table:

image

You want to generate a report that displays the PO_ID and the penalty amount to be paid if the SHIPMENT_DATE is later than one month from the PO_DATE. The penalty is $20 per day.

Evaluate the following two queries:

image

Which statement is true regarding the above commands?

  1. Both execute successfully and give correct results.

  2. Only the first query executes successfully but gives a wrong result.

  3. Only the first query executes successfully and gives the correct result.

  4. Only the second query executes successfully but gives a wrong result.

  5. Only the second query executes successfully and gives the correct result.

Correct Answer: C

Explanation:

The MONTHS_BETWEEN(date 1, date 2) function returns the number of months between two dates:

months_between(#39;01-FEB-2008#39;, #39;01-JAN-2008#39;) = 1 The DECODE Function

Although its name sounds mysterious, this function is straightforward. The DECODE function implements if then-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. DECODE Function Facilitates conditional inquiries by doing the work of a CASE expression or an IF-THENELSE statement:

DECODE(col|expression, search1, result1 [, search2, result2, …, ]

[, default]) DECODE Function

The DECODE function decodes an expression in a way similar to the IF-THEN-ELSE logic that is used in various languages. The DECODE function decodes expression after comparing it to each search value. If the expression is the same as search, result is returned.

If the default value is omitted, a null value is returned where a search value does not match any

of the result values.

Question No.286

View the Exhibit and examine the data in the PROMO_CATEGORY and PROMO_COST columns of the PROMOTIONS table.

image

Evaluate the following two queries:

SQLgt;SELECT DISTINCT promo_category || to_char(promo_cost)quot;codequot; FROM promotions

ORDER BY code;

SQLgt;SELECT DISTINCT promo_category || promo_cost quot;codequot; FROM promotions

ORDER BY 1;

Which statement is true regarding the execution of the above queries?

  1. Only the first query executes successfully.

  2. Only the second query executes successfully.

  3. Both queries execute successfully but give different results.

  4. Both queries execute successfully and give the same result.

Correct Answer: B

Explanation:

You cannot use column alias in the WHERE clause.

Question No.287

Examine the structure of the EMPLOYEES table: EMPLOYEE_ID NUMBER Primary Key

FIRST_NAME VARCHAR2(25) LAST_NAME VARCHAR2(25)

Which three statements insert a row into the table? (Choose three.)

  1. INSERT INTO employees VALUES ( NULL, #39;John#39;, #39;Smith#39;);

  2. INSERT INTO employees( first_name, last_name) VALUES( #39;John#39;, #39;Smith#39;);

  3. INSERT INTO employees VALUES ( 1000, #39;John#39;, NULL);

  4. INSERT INTO employees (first_name, last_name, employee_id) VALUES ( 1000, #39;John#39;, #39;Smith#39;);

  5. INSERT INTO employees (employee_id) VALUES (1000);

  6. INSERT INTO employees (employee_id, first_name, last_name) VALUES ( 1000, #39;John#39;, #39; #39;);

Correct Answer: CEF

Explanation:

EMPLOYEE_ID is a primary key.

Incorrect answer:

  1. EMPLOYEE_ID cannot be null

  2. EMPLOYEE_ID cannot be null

D. mismatch of field_name with datatype

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

Question No.288

Which statements are true regarding the WHERE and HAVING clauses in a SELECT statement? (Choose two.)

  1. The HAVING clause can be used with aggregate functions in subqueries.

  2. The WHERE clause can be used to exclude rows after dividing them into groups.

  3. The WHERE clause can be used to exclude rows before dividing them into groups.

  4. The aggregate functions and columns used in the HAVING clause must be specified in the SELECT list of the query.

  5. The WHERE and HAVING clauses can be used in the same statement only if they are applied to different columns in the table.

Correct Answer: AC

Question No.289

What is true about updates through a view?

  1. You cannot update a view with group functions.

  2. When you update a view group functions are automatically computed.

  3. When you update a view only the constraints on the underlying table will be in effect.

  4. When you update a view the constraints on the views always override the constraints on the underlying tables.

Correct Answer: A

Question No.290

SLS is a private synonym for the SH.SALES table. The user SH issues the following command:

DROP SYNONYM sls;

Which statement is true regarding the above SQL statement?

  1. Only the synonym would be dropped.

  2. The synonym would be dropped and the corresponding table would become invalid.

  3. The synonym would be dropped and the packages referring to the synonym would be dropped.

  4. The synonym would be dropped and any PUBLIC synonym with the same name becomes invalid.

Correct Answer: A

Explanation:

A synonym is an alias for a table (or a view). Users can execute SQL statements against the synonym, and the database will map them into statements against the object to which the synonym points.

Private synonyms are schema objects. Either they must be in your own schema, or they must be qualified with the schema name. Public synonyms exist independently of a schema. A public synonym can be referred to by any user to whom permission has been granted to see it without the need to qualify it with a schema name.

Private synonyms must be a unique name within their schema. Public synonyms can have the same name as schema objects. When executing statements that address objects without a schema qualifier, Oracle will first look for the object in the local schema, and only if it cannot be found will it look for a public synonym.

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