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

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

Question No.1

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

Question No.2

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

image

You need to generate a report that displays the IDs of all products in the costs table whose unit price is at least 25% more than the unit cost. The details should be displayed in the descending order of 25% of the unit cost.

You issue the following query:

image

Which statement is true regarding the above query?

  1. It executes and produces the required result.

  2. It produces an error because an expression cannot be used in the order by clause.

  3. It produces an error because the DESC option cannot be used with an expression in the order by clause.

  4. It produces an error because the expression in the ORDER by clause should also be specified in the SELECT clause.

Correct Answer: A

Question No.3

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

image

The following query is written to retrieve all those product IDs from the SALES table that have more than 55000 sold and have been ordered more than 10 times.

image

Which statement is true regarding this SQL statement?

  1. It executes successfully and generates the required result.

  2. It produces an error because count(*) should be specified in the SELECT clause also.

  3. It produces an error because count{*) should be only in the HAVING clause and not in the WHERE clause.

  4. It executes successfully but produces no result because COUNT (prod_id) should be used instead of COUNT (*).

Correct Answer: C

Explanation:

Restricting Group Results with the HAVING Clause

You use the HAVING clause to specify the groups that are to be displayed, thus further restricting the groups on the basis of aggregate information.

In the syntax, group_condition restricts the groups of rows returned to those groups for which the specified condition is true.

The Oracle server performs the following steps when you use the HAVING clause:

Rows are grouped.

The group function is applied to the group.

The groups that match the criteria in the HAVING clause are displayed.

The HAVING clause can precede the GROUP BY clause, but it is recommended that you place the GROUP BY clause first because it is more logical. Groups are formed and group functions are calculated before the HAVING clause is applied to the groups in the SELECT list.

Note:

The WHERE clause restricts rows, whereas the HAVING clause restricts groups.

Question No.4

You want to display 5 percent of the rows from the sales table for products with the lowest AMOUNT_SOLD and also want to include the rows that have the same AMOUNT_SOLD even if this causes the output to exceed 5 percent of the rows. Which query will provide the required result?

image

  1. Option A

  2. Option B

  3. Option C

  4. Option D

Correct Answer: D

Explanation:

The FETCH statement must include WITH TIES.

Incorrect:

Not B: You cannot use ROWS WITH in a FETCH statement.

Question No.5

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

image

Evaluate the following query:

image

What would be the outcome of executing the above SQL statement?

  1. It produces an error.

  2. It shows the names of all products in the table.

  3. It shows the names of products whose list price is the second highest in the table.

  4. It shows the names of all products whose list price is less than the maximum list price.

Correct Answer: C

Question No.6

Using the customers table, you need to generate a report that shows 50% of each credit amount in each income level. The report should NOT show any repeated credit amounts in each income level. Which query would give the required result?

image

  1. Option A

  2. Option B

  3. Option C

  4. Option D

Correct Answer: C Explanation: Duplicate Rows

Unless you indicate otherwise, SQL displays the results of a query without eliminating the duplicate rows.

To eliminate duplicate rows in the result, include the DISTINCT keyword in the SELECT clause immediately after the SELECT keyword.

You can specify multiple columns after the DISTINCT qualifier. The DISTINCT qualifier affects all the selected columns, and the result is every distinct combination of the columns.

Question No.7

Examine the structure of the employees table.

image

You want to display the maximum and minimum salaries of employees hired 1 year ago. Which two statements would get the correct output?

image

  1. Option A

  2. Option B

  3. Option C

  4. Option D

Correct Answer: AC Explanation: Incorrect:

Not B, not D: You cannot use a GROUP BY clause in a WHERE statement.

Question No.8

You want to display the date for the first Monday of the next month and issue the following command:

image

What is the outcome?

  1. It executes successfully and returns the correct result.

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

  3. It generates an error because TO_CHAR should be replaced with TO_DATE.

  4. It generates an error because rrrr should be replaced by rr in the format string.

  5. It generates an error because fm and double quotation marks should not be used in the format string.

Correct Answer: A

Question No.9

You create a sequence as follows: create sequence seq1 start with 1;

After selecting from it a few times, you want to reinitialize it to reissue the numbers already generated.

How can you do this?

  1. You must drop and re-create the sequence.

  2. You can#39;t. Under no circumstances can numbers from a sequence be reissued once they have been used.

  3. Use the command ALTER SEQUENCE SEQ1 START WITH 1; to reset the next value to 1.

  4. Use the command ALTER SEQUENCE SEQ1 CYCLE; to reset the sequence to its starting value.

Correct Answer: A

Explanation:

It is not possible to change the next value of a sequence, so you must re-create it.

Question No.10

You execute the following commands:

image

For which substitution variables are you prompted for the input?

  1. None, because no input required

  2. Both the substitution variables #39;hiredate#39; and #39;mgr_id\

  3. Only #39;hiredate#39;

  4. Only #39;mgr_id#39;

Correct Answer: D

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