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

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

Question No.141

View the Exhibit and evaluate structures of the SALES, PRODUCTS, and COSTS tables.

image

Evaluate the following SQL statements:

image

Which statement is true regarding the above compound query?

  1. It shows products that have a cost recorded irrespective of sales

  2. It shows products that were sold and have a cost recorded

  3. It shows products that were sold but have no cost recorded

  4. It reduces an error

Correct Answer: C

Question No.142

Which two statements are true regarding the USING and ON clauses in table joins? (Choose two.)

  1. The ON clause can be used to join tables on columns that have different names but compatible data types

  2. A maximum of one pair of columns can be joined between two tables using the ON clause

  3. Both USING and ON clause can be used for equijoins and nonequijoins

  4. The WHERE clause can be used to apply additional conditions in SELECT statement containing the ON or the USING clause

Correct Answer: AD

Explanation:

Creating Joins with the USING Clause

If several columns have the same names but the data types do not match, use the USING clause to specify the columns for the equijoin.

Use the USING clause to match only one column when more than one column matches.

The NATURAL JOIN and USING clauses are mutually exclusive Using Table Aliases with the USING clause

When joining with the USING clause, you cannot qualify a column that is used in the USING clause itself. Furthermore, if that column is used anywhere in the SQL statement, you cannot alias it. For example, in the query mentioned in the slide, you should not alias the location_id column in the WHERE clause because the column is used in the USING clause.

The columns that are referenced in the USING clause should not have a qualifier (table name oralias) anywhere in the SQL statement.

Creating Joins with the ON Clause

The join condition for the natural join is basically an equijoin of all columns with the same name. Use the ON clause to specify arbitrary conditions or specify columns to join.

ANSWER C The join condition is separated from other search conditions. ANSWER D The ON clause makes code easy to understand.

Question No.143

View the Exhibit and examine the description for the CUSTOMERS table.

image

You want to update the CUST_CREDIT_LIMIT column to NULL for all the customers, where CUST_INCOME_LEVEL has NULL in the CUSTOMERS table. Which SQL statement will

accomplish the task?

  1. UPDATE customersSET cust_credit_limit = NULLWHERE CUST_INCOME_LEVEL = NULL;

  2. UPDATE customersSET cust_credit_limit = NULLWHERE cust_income_level IS NULL;

  3. UPDATE customersSET cust_credit_limit = TO_NUMBER(NULL)WHERE cust_income_level = TO_NUMBER(NULL);

  4. UPDATE customersSET cust_credit_limit = TO_NUMBER(#39; #39;, 9999)WHERE cust_income_level IS NULL;

Correct Answer: B

Question No.144

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

image

You have to generate a report that displays the promo name and start date for all promos that started after the last promo in the #39;INTERNET#39; category.

Which query would give you the required output?

  1. SELECT promo_name, promo_begin_date FROM promotionsWHERE promo_begin_date gt; ALL (SELECT MAX(promo_begin_date) FROM promotions )ANDpromo_category = #39;INTERNET#39;;

  2. SELECT promo_name, promo_begin_date FROM promotionsWHERE promo_begin_date IN (SELECT promo_begin_dateFROM promotionsWHERE promo_category=#39;INTERNET#39;);

  3. SELECT promo_name, promo_begin_date FROM promotionsWHERE promo_begin_date gt; ALL (SELECT promo_begin_dateFROM promotionsWHERE promo_category = #39;INTERNET#39;);

  4. SELECT promo_name, promo_begin_date FROM promotionsWHERE promo_begin_date gt; ANY (SELECT promo_begin_dateFROM promotionsWHERE promo_category = #39;INTERNET#39;);

Correct Answer: C

Question No.145

You created an ORDERS table with the following description:

image

You inserted some rows in the table. After some time, you want to alter the table by creating the

PRIMARY KEY constraint on the ORD_ID column.

Which statement is true in this scenario?

  1. You cannot add a primary key constraint if data exists in the column

  2. You can add the primary key constraint even if data exists, provided that there are no duplicate values

  3. The primary key constraint can be created only a the time of table creation

  4. You cannot have two constraints on one column

Correct Answer: B

Question No.146

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

image

and examine the structure of CUSTOMRS AND SALES tables: Evaluate the following SQL statement:

image

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

  1. It would not execute because the SELECT statement cannot be used in place of the table name

  2. It would execute and restrict modifications to only the column specified in the SELECT statement

  3. It would not execute because a sub query cannot be used in the WHERE clause of an UPDATE statement

  4. It would not execute because two tables cannot be used in a single UPDATE statement

Correct Answer: B

Question No.147

Examine the structure of the EMPLOYEES and NEW_EMPLOYEES tables:

image

Which MERGE statement is valid?

  1. MERGE INTO new_employees c USING employees e ON (c.employee_id = e.employee_id) WHEN MATCHED THEN UPDATE SET name = e.first_name ||#39;, #39;|| e.last_name WHEN NOT MATCHED THEN INSERT value S(e.employee_id, e.first_name ||#39;, #39;||e.last_name);

  2. MERGE new_employees c USING employees e ON (c.employee_id = e.employee_id) WHEN EXISTS THEN UPDATE SET name = e.first_name ||#39;, #39;|| e.last_name WHEN NOT MATCHED THEN INSERT valueS(e.employee_id, e.first_name ||#39;, #39;||e.last_name);

  3. MERGE INTO new_employees cUSING employees e ON (c.employee_id = e.employee_id) WHEN EXISTS THEN UPDATE SET

  4. name = e.first_name ||#39;, #39;|| e.last_name WHEN NOT MATCHED THEN INSERT value S(e.employee_id, e.first_name ||#39;, #39;||e.last_name);

  5. MERGE new_employees c FROM employees e ON (c.employee_id = e.employee_id) WHEN MATCHED THEN UPDATE SET name = e.first_name ||#39;, #39;|| e.last_name WHEN NOT MATCHED THEN INSERT INTO new_employees valueS(e.employee_id, e.first_name ||#39;, #39;||e.last_name);

Correct Answer: A

Explanation:

this is the correct MERGE statement syntax

Incorrect answer:

  1. it should MERGE INTO table_name

  2. it should be WHEN MATCHED THEN

  3. it should MERGE INTO table_name

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

Question No.148

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

image

Using the PROMOTIONS table, you need to display the names of all promos done after January 1, 2001 starting with the latest promo.

Which query would give the required result? (Choose all that apply.)

  1. . SELECT promo_name, promo_begin_dateFROM promotionsWHERE promo_begin_date gt; #39;01- JAN-01#39;ORDER BY 1 DESC;

  2. SELECT promo_name, promo_begin_date quot;START DATEquot;FROM promotionsWHERE promo_begin_date gt; #39;01-JAN-01#39;ORDER BY quot;START DATEquot; DESC;

  3. . SELECT promo_name, promo_begin_dateFROM promotionsWHERE promo_begin_date gt; #39;01- JAN-01#39;ORDER BY 2 DESC;

  4. . SELECT promo_name, promo_begin_dateFROM promotionsWHERE promo_begin_date gt; #39;01- JAN-01#39;ORDER BY promo_name DESC;

Correct Answer: BC

Question No.149

Evaluate the SQL statement: TRUNCATE TABLE DEPT;

Which three are true about the SQL statement? (Choose three.)

  1. It releases the storage space used by the table.

  2. It does not release the storage space used by the table.

  3. You can roll back the deletion of rows after the statement executes.

  4. You can NOT roll back the deletion of rows after the statement executes.

  5. An attempt to use DESCRIBE on the DEPT table after the TRUNCATE statement executes will display an error.

  6. You must be the owner of the table or have DELETE ANY TABLE system privileges to truncate the DEPT table

Correct Answer: ADF

Explanation:

A: The TRUNCATE TABLE Statement releases storage space used by the table, D: Can not rollback the deletion of rows after the statement executes,

F: You must be the owner of the table or have DELETE ANY TABLE system privilege to truncate the DEPT table.

Incorrect answer:

  1. is not true

  2. is not true

  3. is not true

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

Question No.150

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

  1. Multiple columns or expressions can be compared between the main query and sub query

  2. Main query and sub query can get data from different tables

  3. Sub queries can contain GROUP BY and ORDER BY clauses

  4. Main query and sub query must get data from the same tables

  5. Sub queries can contain ORDER BY but not the GROUP BY clause

  6. Only one column or expression can be compared between the main query and subqeury

Correct Answer: ABC

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

Leave a Reply

Your email address will not be published. Required fields are marked *

Proudly powered by WordPress