Free 1Z0-071 Exam Braindumps

Which two statements are true regarding the SQL GROUP BY clause?

1. A. You can use a column alias in the GROUP BY clause.
2. B. Using the WHERE clause after the GROUP BY clause excludes rows after creating groups.
3. C. The GROUP BY clause is mandatory if you are using an aggregating function in the SELECT clause.
4. D. Using the WHERE clause before the GROUP BY clause excludes rows before creating groups.
5. E. If the SELECT clause has an aggregating function, then columns without an aggregating function in the SELECT clause should be included in the GROUP BY clause.

Correct Answer: DE

A subquery is called a single-row subquery when .

1. A. There is only one subquery in the outer query and the inner query returns one or more values
2. B. The inner query returns a single value to the outer query.
3. C. The inner query uses an aggregating function and returns one or more values.
4. D. The inner query returns one or more values and the outer query returns a single value.

Correct Answer: B

You must write a query that prompts users for column names and conditions every time it is executed. (Choose the best answer.)
The user must be prompted only once for the table name. Which statement achieves those objectives?

1. A. SELECT &col1, '&col2'FROM &tableWHERE &&condition = '&cond';
2. B. SELECT &col1, &col2 FROM "&table"WHERE &condition =&cond;
3. C. SELECT &col1, &col2 FROM &&tableWHERE &condition = &cond;
4. D. SELECT &col1, &col2 FROM &&tableWHERE &condition = &&cond

Correct Answer: C

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

Evaluate the following SQL statement:

Which statement is true regarding the outcome of the above query?

1. A. It produces an error because subqueries cannot be used with the CASE expression.
2. B. It shows COST_REMARK for all the promos in the promo category ‘TV’.
3. C. It shows COST_REMARK for all the promos in the table.
4. D. It produces an error because the subquery gives an error.

Correct Answer: C

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

Which two tasks would require subqueries? (Choose two.)

1. A. displaying all the products whose minimum list prices are more than average list price of products having the status orderable
2. B. displaying the total number of products supplied by supplier 102071 and having product status OBSOLETE
3. C. displaying the number of products whose list prices are more than the average list price
4. D. displaying all supplier IDs whose average list price is more than 500
5. E. displaying the minimum list price for each product status

Correct Answer: AC