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

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

Question No.31

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

image

All products have a list price.

You issue the following command to display the total price of each product after a discount of 25% and a tax of 15% are applied on it. Freight charges of $100 have to be applied to all the products.

image

What would be the outcome if all the parentheses are removed from the above statement?

  1. It produces a syntax error.

  2. The result remains unchanged.

  3. The total price value would be lower than the correct value.

  4. The total price value would be higher than the correct value.

Correct Answer: B

Question No.32

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

image

You have been asked to produce a report on the customers table showing the customers details sorted in descending order of the city and in the descending order of their income level in each city.

Which query would accomplish this task?

image

  1. Option A

  2. Option B

  3. Option C

  4. Option D

Correct Answer: A

Question No.33

Examine the structure of the orders table:

image

You want to find the total value of all the orders for each year and issue the following command:

image

Which statement is true regarding the outcome?

  1. It executes successfully and gives the correct output.

  2. It gives an error because the TO_CHAR function is not valid.

  3. It executes successfully but does not give the correct output.

  4. It gives an error because the data type conversion in the SELECT list does not match the data type conversion in the GROUP BY clause.

Correct Answer: D

Explanation:

The correct code would be:

SELECT TO_CHAR(order_date, #39;rr#39;), SUM(order_total) FROM orders

GROUP BY TO_CHAR(order_date, #39;rr#39;);

Question No.34

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

image

Using the customers table, you need to generate a report that shows the average credit limit for customers in Washington and NEW YORK.

Which SQL statement would produce the required result?

image

  1. Option A

  2. Option B

  3. Option C

  4. Option D

Correct Answer: C

Question No.35

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

image

Using the products table, you issue the following query to generate the names, current list price, and discounted list price for all those products whose list price falls below $10 after a discount of 25% is applied on it.

image

The query generates an error. What is the reason for the error?

  1. The parenthesis should be added to enclose the entire expression.

  2. The double quotation marks should be removed from the column alias.

  3. The column alias should be replaced with the expression in the where clause.

  4. The column alias should be put in uppercase and enclosed within double quotation marks in the where clause.

Correct Answer: C

Question No.36

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

image

NEW_CUSTOMERS is a new table with the columns CUST_ID, CUST_NAME and CUST_CITY that have the same data types and size as the corresponding columns in the customers table.

Evaluate the following insert statement:

image

The insert statement fails when executed. What could be the reason?

  1. The values clause cannot be used in an INSERT with a subquery.

  2. Column names in the NEW_CUSTOMERS and CUSTOMERS tables do not match.

  3. The where clause cannot be used in a subquery embedded in an INSERT statement.

  4. The total number of columns in the NEW_CUSTOMERS table does not match the total number of columns in the CUSTOMERS table.

Correct Answer: A

Explanation:

Copying Rows from Another Table

Write your INSERT statement with a subquery: Do not use the VALUES clause.

Match the number of columns in the INSERT clause to those in the subquery. Inserts all the rows returned by the subquery in the table, sales_reps.

Question No.37

Which normal form is a table in if it has no multi-valued attributes and no partial dependencies?

  1. First normal form

  2. Second normal form

  3. Third normal form

  4. Fourth normal form

Correct Answer: B

Explanation:

According to the Second Normal Form (2NF) there must be no partial dependencies on a concatenated key.

Question No.38

View the Exhibit and examine the structures of the employees and departments tables.

image

You want to update the employees table as follows:

image

Update only those employees who work in Boston or Seattle (locations 2900 and 2700).

image

Set department_id for these employees to the department_id corresponding to London (location_id 2100).

image

Set the employees#39; salary in iocation_id 2100 to 1.1 times the average salary of their department.

image

Set the employees#39; commission in iocation_id 2100 to 1.5 times the average commission of their department.

You issue the following command:

image

What is the outcome?

  1. It executes successfully and gives the correct result.

  2. It executes successfully but does not give the correct result.

  3. It generates an error because a subquery cannot have a join condition in an update statement.

  4. It generates an error because multiple columns (SALARY, COMMISSION) cannot be specified together in an update statement.

Correct Answer: B

Explanation:

Not that employees is used both in the first line (UPDATE employees) and later (FROM employees, departments). This would not cause the correct output. Instead aliases should be use. The following would be the correct query:

UPDATE employees a SET department_id = (SELECT department_id FROM departments

WHERE location_id = #39;2100#39;), (salary, commission_pct) =

(SELECT 1.1*AVG(salary), 1.5*AVG(commission_pct) FROM employees b

WHERE a.department_id = b.department_id)

WHERE department_id IN (SELECT department_id FROM departments WHERE location_id = 2900 OR location_id = 2700);

Question No.39

A sequence is created as follows: create sequence seq1 maxvalue 50;

If the current value is already 50, when you attempt to select SEQ1.NEXTVAL what will happen?

  1. The sequence will cycle and issue 0.

  2. The sequence will cycle and issue 1.

  3. The sequence will reissue 50.

  4. There will be an error.

Correct Answer: D

Explanation:

The default is NOCYCLE, and the sequence cannot advance further.

Question No.40

Which three SQL statements would display the value 1890.55 as $1, 890.55?

image

  1. Option A

  2. Option B

  3. Option C

  4. Option D

  5. Option E

Correct Answer: ADE

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