[Free] 2018(Jan) EnsurePass Dumpsleader Oracle 1z0-047 Dumps with VCE and PDF 161-170

Ensurepass.com : Ensure you pass the IT Exams
2018 Jan Oracle Official New Released 1z0-047
100% Free Download! 100% Pass Guaranteed!
http://www.EnsurePass.com/1z0-047.html

Oracle Database SQL Expert

Question No: 161

Evaluate the following ALTER TABLE statement:

ALTER TABLE orders SET UNUSED order_date;

Which statement is true?

  1. The DESCRIBE command would still display the ORDER_DATE column.

  2. ROLLBACK can be used to get back the ORDER_DATE column in the ORDERS table.

  3. The ORDER_DATE column should be empty for the ALTER TABLE command to execute successfully.

  4. After executing the ALTER TABLE command, you can add a new column called ORDER_DATE to the ORDERS table.

Answer: D

Question No: 162

View the Exhibit and examine the description of the ORDERS table.

You need to display CUSTOMER_ID for all customers who have placed orders more than three times in the last six months. You issued the following SQL statement:

SELECT customer_id,COUNT(order_id) FROM orders

WHERE COUNT(order_id)gt;3 AND

order_date BETWEEN ADD_MONTHS(SYSDATE,-6) AND SYSDATE

GROUP BY customer_id;

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

  1. It would execute successfully and provide the desired result.

  2. It would not execute because the WHERE clause cannot have an aggregate function.

  3. It would not execute because the ORDER_ID column is not included in the GROUP BY clause.

  4. It would not execute because the GROUP BY clause should be placed before the WHERE clause.

Answer: B

Question No: 163

View the Exhibit and examine the structure of the EMP table which is not partitioned and not an index-organized table.

Evaluate the following SQL statement:

ALTER TABLE emp DROP COLUMN first_name;

Which two statements are true regarding the above command? (Choose two.)

Ensurepass 2018 PDF and VCE

  1. The FIRST_NAME column would be dropped provided it does not contain any data.

  2. The FIRST_NAME column would be dropped provided at least one or more columns remain in the table.

  3. The FIRST_NAME column can be rolled back provided the SET UNUSED option is added to the above SQL statement.

  4. The FIRST_NAME column can be dropped even if it is part of a composite PRIMARY KEY provided the CASCADE option is used.

Answer: B,D

Question No: 164

Which two statements are true regarding the GROUP BY clause in a SQL statement? (Choose two.)

  1. You can use column alias in the GROUP BY clause.

  2. Using the WHERE clause after the GROUP BY clause excludes the rows after creating groups.

  3. The GROUP BY clause is mandatory if you are using an aggregate function in the SELECT clause.

  4. Using the WHERE clause before the GROUP BY clause excludes the rows before

    creating groups.

  5. If the SELECT clause has an aggregate function, then those individual columns without an aggregate function in the SELECT clause should be included in the GROUP BY clause.

Answer: D,E

Question No: 165

In which scenario would you use the ROLLUP operator for expression or columns within a GROUP BY clause?

  1. to find the groups forming the subtotal in a row

  2. to create group-wise grand totals for the groups specified within a GROUP BY clause

  3. to create a grouping for expressions or columns specified within a GROUP BY clause in one direction, from right to left for calculating the subtotals

  4. to create a grouping for expressions or columns specified within a GROUP BY clause in all possible directions, which is cross-tabular report for calculating the subtotals

Answer: C

Question No: 166

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

There are some products listed in the PRODUCT_INFORMATION table that have no value in the LIST_PRICE column. You issued the following SQL statement to find out the PRODUCT_NAME for these products:

SELECT product_name, list_price

FROM product_information WHERE list_price = NULL;

Ensurepass 2018 PDF and VCE

The query returns no rows. What changes would you make in the statement to get the desired result?

  1. Change the WHERE clause to WHERE list_price = 0

  2. Change the WHERE clause to WHERE list_price = #39;#39;.

  3. Change the WHERE clause to WHERE list_price IS NULL.

  4. In the WHERE clause, enclose NULL within single quotation marks.

  5. In the WHERE clause, enclose NULL within double quotation marks.

Answer: C

Question No: 167

View the Exhibit and examine the ORDERS table.

The ORDERS table contains data and all orders have been assigned a customer ID. Which statement would add a NOT NULL constraint to the CUSTOMER_ID column?

Ensurepass 2018 PDF and VCE

  1. ALTER TABLE orders

    ADD CONSTRAINT orders_cust_id_nn NOT NULL (customer_id);

  2. ALTER TABLE orders

    MODIFY customer_id CONSTRAINT orders_cust_id_nn NOT NULL;

  3. ALTER TABLE orders

    MODIFY CONSTRAINT orders_cust_id_nn NOT NULL (customer_id);

  4. ALTER TABLE orders

ADD customer_id NUMBER(6)CONSTRAINT orders_cust_id_nn NOT NULL;

Answer: B

Question No: 168

View the Exhibit and examine the structure of ORDERS and CUSTOMERS tables. Evaluate the following UPDATE statement:

UPDATE

(SELECT order_date, order_total, customer_id FROM orders)

SET order_date = 22-mar-2007#39; WHERE customer_id = (SELECT customer_id

FROM customers

WHERE cust_last_name = #39;Roberts#39;AND Credit_limit = 600);

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

Ensurepass 2018 PDF and VCE

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

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

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

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

Answer: B

Question No: 169

Given below are the SQL statements executed in a user session:

CREATE TABLE product (pcode NUMBER(2), pnameVARCHAR2(10));

INSERT INTO product VALUES(1, ‘pen’); INSERT INTO product VALUES (2,#39;pencil); SAVEPOINT a;

UPDATE product SET pcode = 10 WHERE pcode = 1;

SAVEPOINT b;

DELETE FROM product WHERE pcode = 2; COMMIT;

DELETE FROM product WHERE pcode=10; ROLLBACK TO SAVEPOINT a;

Which statement describes the consequences?

  1. No SQL statement would be rolled back.

  2. Both the DELETE statements would be rolled back.

  3. Only the second DELETE statement would be rolled back.

  4. Both the DELETE statements and the UPDATE statement would be rolled back.

Answer: A

Question No: 170

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

You need to display the ORDER_ID of the order that has the highest total value among all the orders in the ORDER_ITEMS table.

Which query would produce the desired output? ORDER ITEMS

Ensurepass 2018 PDF and VCE

  1. SELECT order_id FROM order_items

    WHERE(unit_price*quantity) = MAX(unit_price*quantity) GROUP BY order_id;

  2. SELECT order_id FROM order_items

    WHERE(unit_price*quantity) = (SELECT MAX(unit_price*quantity) FROM order_items) GROUP BY order_id;

  3. SELECT order_id FROM order_items

    WHERE (unit_price*quantity) = (SELECT MAX(unit_price*quantity) FROM order_items GROUP BY order_id);

  4. SELECT order_id FROM order_items GROUP BY order_id

HAVING SUM(unit_price*quantity) =(SELECT MAX(SUM(unit_price*quantity)j FROM order_items GROUP BY order_id);

Answer: D

100% Ensurepass Free Download!
Download Free Demo:1z0-047 Demo PDF
100% Ensurepass Free Guaranteed!
1z0-047 Dumps

EnsurePass ExamCollection Testking
Lowest Price Guarantee Yes No No
Up-to-Dated Yes No No
Real Questions Yes No No
Explanation Yes No No
PDF VCE Yes No No
Free VCE Simulator Yes No No
Instant Download Yes No No

Leave a Reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.