Free 1Z0-071 Exam Braindumps

Evaluate the following SQL statement:

SQL> select cust_id, cust_last_name "Last name"
FROM customers
WHERE country_id = 10
UNION
SELECT cust_id CUST_NO, cust_last_name
FROM customers
WHERE country_id = 30

Identify three ORDER BY clauses either one of which can complete the query.

  1. ORDER BY "Last name"
  2. ORDER BY 2, cust_id
  3. ORDER BY CUST_NO
  4. ORDER BY 2, 1
  5. ORDER BY "CUST_NO"

Answer(s): A,B,D

Explanation:

Using the ORDER BY Clause in Set Operations
-The ORDER BY clause can appear only once at the end of the compound query.
-Component queries cannot have individual ORDER BY clauses.
-The ORDER BY clause recognizes only the columns of the first SELECT query.
-By default, the first column of the first SELECT query is used to sort the output in an ascending order.



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


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

  1. display all products whose PROD_MIN_PRICE is more than the average PROD_LIST_PRICE of all products, and whose status is orderable
  2. display the total number of products supplied by supplier 102 and have product status as ‘OBSOLETE’
  3. display the number of products whose PROD_LIST_PRICE is more than the average PROD_LIST_PRICE.
  4. display suppliers whose PROD_LIST_PRICE is less than 1000
  5. display the minimum PROD_LIST_PRICE for each product status

Answer(s): A,C



In which three situations does a new transaction always start?

  1. when issuing a TRUNCATE statement after a SELECT statement was issued in the same session
  2. when issuing a CREATE INDEX statement after a CREATE TABLE statement completed successfully in the same session
  3. when issuing a CREATE TABLE statement after a SELECT statement was issued in the same session
  4. when issuing the first Data Manipulation Language (DML) statement after a COMMIT or ROLLBACK statement was issued in the same session
  5. when issuing a DML statement after a DML statement failed in the same session
  6. when issuing a SELECT FOR UPDATE statement after a CREATE TABLE AS SELECT statement was issued in the same session

Answer(s): D,E,F


Reference:

https://docs.oracle.com/cd/B19306_01/server.102/b14220/transact.htm



Examine this description of the PRODUCTS table:


You successfully execute this command:

Which two statements execute without errors?





Answer(s): B



Which three statements are true about multiple row subqueries?

  1. They can contain GROUP BY clauses.
  2. They can return multiple columns.
  3. Two or more values are always returned from the subquery.
  4. They can contain HAVING clauses.
  5. They cannot contain a subquery.

Answer(s): A,B,C


Reference:

https://www.w3resource.com/sql/subqueries/multiplee-row-column-subqueries.php