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

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

Question No.271

View the Exhibit and examine the structure and data in the INVOICE table.

image

Which two statements are true regarding data type conversion in expressions used in queries? (Choose two.)

  1. inv_amt =#39;0255982#39;: requires explicit conversion

  2. inv_date gt; #39;01-02-2008#39;: uses implicit conversion

  3. CONCAT (inv_amt, inv_date): requires explicit conversion

  4. inv_date = #39;15-february-2008#39;: uses implicit conversion

  5. inv_no BETWEEN #39;101#39; AND #39;110#39;: uses implicit conversion

    Correct Answer: DE

    Question No.272

    Evaluate the following CREATE SEQUENCE statement:

    CREATE SEQUENCE seq1 START WITH 100

    INCREMENT BY 10

    MAXVALUE 200 CYCLE NOCACHE;

    The SEQ1 sequence has generated numbers up to the maximum limit of 200. You issue the following SQL statement:

    SELECT seq1.nextval FROM dual;

    What is displayed by the SELECT statement?

    A.

    1

    B.

    10

    C.

    100

    D.

    an error

    Correct Answer: A

    Explanation:

    But why the answer is not quot;Cquot;?

    Because you didn#39;t specify the MINVALUE for the sequence. If you check the sequence definition that you created it will have the default value of 1, which it reverts to when cycling.

    If you wanted to keep the minimum value you would need to specify it in the sequence creation. sequence Is the name of the sequence generator

    INCREMENT BY n Specifies the interval between sequence numbers, where n is an integer (If this clause is omitted, the sequence increments by 1.) START WITH n Specifies the first sequence number to be generated (If this clause is omitted, the sequence starts with 1.) MAXVALUE n Specifies the maximum value the sequence can generate NOMAXVALUE Specifies a maximum value of 10^27 for an ascending sequence and ? for a descending sequence (This is the default option.)

    MINVALUE n Specifies the minimum sequence value

    NOMINVALUE Specifies a minimum value of 1 for an ascending sequence and – 10^26) for a descending sequence (This is the default option.) CYCLE | NOCYCLE Specifies whether the sequence continues to generate values after reaching its maximum or minimum value (NOCYCLE is the default option.)

    CACHE n | NOCACHE Specifies how many values the Oracle server preallocates and keeps in memory (By default, the Oracle server caches 20 values.)

    Question No.273

    Which three statements are true about the ALTER TABLE …. DROP COLUMN …. command?

    1. A column can be dropped only if it does not contain any data.

    2. A column can be dropped only if another column exists in the table.

    3. A dropped column can be rolled back.

    4. The column in a composite PRIMARY KEY with the CASCADE option can be dropped.

    5. A parent key column in the table cannot be dropped.

Correct Answer: BDE

Question No.274

View the Exhibit; examine the structure of the PROMOTIONS table.

image

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;

Correct Answer: D

Question No.275

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

You want to generate a report that displays the average list price of product categories where the average list price is less than half the maximum in each category.

Which query would give the correct output?

  1. SELECT prod_category, avg(prod_list_price)FROM productsGROUP BY prod_categoryHAVING avg(prod_list_price) lt; ALL(SELECT max(prod_list_price)/2FROM productsGROUP BY prod_category);

  2. SELECT prod_category, avg(prod_list_price)FROM productsGROUP BY prod_categoryHAVING avg(prod_list_price) gt; ANY(SELECT max(prod_list_price)/2FROM productsGROUP BY prod_category);

  3. SELECT prod_category, avg(prod_list_price)FROM productsHAVING avg(prod_list_price) lt; ALL(SELECT max(prod_list_price)/2FROM productsGROUP BY prod_category);

  4. SELECT prod_category, avg(prod_list_price)FROM productsGROUP BY prod_categoryHAVING avg(prod_list_price) gt; ANY(SELECT max(prod_list_price)/2FROM products);

Correct Answer: A

Explanation:

Using the ANY Operator in Multiple-Row Subqueries

The ANY operator (and its synonym, the SOME operator) compares a value to each value returned by a subquery.

lt;ANY means less than the maximum.

gt;ANY means more than the minimum.

=ANY is equivalent to IN

Using the ALL Operator in Multiple-Row Subqueries

The ALL operator compares a value to every value returned by a subquery.

gt;ALL means more than the maximum and

lt;ALL means less than the minimum.

The NOT operator can be used with IN, ANY, and ALL operators.

Question No.276

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

image

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;.

Correct Answer: CE

Question No.277

Which two statements are true regarding the DELETE and TRUNCATE commands? (Choose two.)

  1. DELETE can be used to remove only rows from only one table at a time.

  2. DELETE can be used to remove only rows from multiple tables at a time.

  3. DELETE can be used only on a table that is a parent of a referential integrity constraint.

  4. DELETE can be used to remove data from specific columns as well as complete rows.

  5. DELETE and TRUNCATE can be used on a table that is a parent of a referential integrity constraint having ON DELETE rule.

Correct Answer: AE

Explanation:

Transactions, consisting of INSERT, UPDATE, and DELETE (or even MERGE) commands can be made permanent (with a COMMIT) or reversed (with a ROLLBACK). A TRUNCATE command, like any other DDL command, is immediately permanent: it can never be reversed.

The Transaction Control Statements

A transaction begins implicitly with the first DML statement. There is no command to explicitly start a transaction. The transaction continues through all subsequent DML statements issued by the session. These statements can be against any number of tables:

a transaction is not restricted to one table. It terminates (barring any of the events listed in the previous section) when the session issues a COMMIT or ROLLBACK command. The SAVEPOINT command can be used to set markers that will stage the action of a ROLLBACK, but the same transaction remains in progress irrespective of the use of SAVEPOINT

Explicit Transaction Control Statements

You can control the logic of transactions by using the COMMIT, SAVEPOINT, and ROLLBACK statements.

Note:

You cannot COMMIT to a SAVEPOINT. SAVEPOINT is not ANSI-standard SQL.

image

Question No.278

Which are iSQL*Plus commands? (Choose all that apply.)

  1. INSERT

  2. UPDATE

  3. SELECT

  4. DESCRIBE

  5. DELETE

  6. RENAME

Correct Answer: D

Explanation:

The only SQL*Plus command in this list : DESCRIBE. It cannot be used as SQL command. This command returns a description of tablename, including all columns in that table, the datatype for each column and an indication of whether the column permits storage of NULL values.

Incorrect answer:

  1. INSERT is not a SQL*PLUS command

  2. UPDATE is not a SQL*PLUS command

  3. SELECT is not a SQL*PLUS command

  1. DELETE is not a SQL*PLUS command

  2. RENAME is not a SQL*PLUS command

Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 7

Question No.279

View the Exhibit and examine the data in the PROJ_TASK_DETAILS table.

image

The PROJ_TASK_DETAILS table stores information about tasks involved in a project and the relation between them.

The BASED_ON column indicates dependencies between tasks. Some tasks do not depend on the completion of any other tasks.

You need to generate a report showing all task IDs, the corresponding task ID they are dependent on, and the name of the employee in charge of the task it depends on.

Which query would give the required result?

  1. SELECT p.task_id, p.based_on, d.task_in_chargeFROM proj_task_details p JOIN proj_task_details dON (p.based_on = d.task_id);

  2. SELECT p.task_id, p.based_on, d.task_in_chargeFROM proj_task_details p LEFT OUTER JOIN proj_task_details d ON (p.based_on = d.task_id);

  3. SELECT p.task_id, p.based_on, d.task_in_chargeFROM proj_task_details p FULL OUTER JOIN proj_task_details d ON (p.based_on = d.task_id);

  4. SELECT p.task_id, p.based_on, d.task_in_chargeFROM proj_task_details p JOIN proj_task_details dON (p.task_id = d.task_id);

Correct Answer: B

Question No.280

In which two cases would you use an outer join? (Choose two.)

  1. The tables being joined have NOT NULL columns.

  2. The tables being joined have only matched data.

  3. The columns being joined have NULL values.

  4. The tables being joined have only unmatched data.

  5. The tables being joined have both matched and unmatched data.

  6. Only when the tables have a primary key/foreign key relationship.

Correct Answer: CE

Explanation:

You use an outer join to also see rows that do not meet the join condition.

Incorrect answer:

  1. meet a join condition

  2. meet a join condition

D. meet non join condition only

F. does not take into consideration of primary key and foreign key relationship Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 4-17

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