[Free] 2018(Jan) Latesttests Dumpsleader Oracle 1z0-047 Dumps with VCE and PDF Download 141-150

Latesttests 2018 Jan Oracle Official New Released 1z0-047
100% Free Download! 100% Pass Guaranteed!
http://www.Latesttests.com/1z0-047.html

Oracle Database SQL Expert

Question No: 141

Which statement is true regarding the CUBE operator in the GROUP BY clause of a SQL statement?

  1. It produces only aggregates for the groups specified in the GROUP BY clause.

  2. It finds all the NULL values in the superaggregates for the groups specified in the GROUP BY clause.

  3. It produces 2 n possible superaggregate combinations, if the n columns and expressions are specified in the GROUP BY clause.

  4. It produces n 1 possible superaggregate combinations, if the n columns and expressions are specified in the GROUP BY clause.

Answer: C

Question No: 142

Which statement is true regarding the SESSION_PRIVS dictionary view?

  1. It contains the current object privileges available in the user session.

  2. It contains the current system privileges available in the user session.

  3. It contains the object privileges granted to other users by the current user session.

  4. It contains the system privileges granted to other users by the current user session.

Answer: B

Question No: 143

View the Exhibit and examine the descriptions of ORDER_ITEMS and ORDERS tables.

Latesttests 2018 PDF and VCE

You want to display the CUSTOMER_ID, PRODUCT_ID, and total (UNIT_PRICE multiplied by QUANTITY) for the order placed. You also want to display the subtotals for a CUSTOMER_ID as well as for a PRODUCT ID for the last six months.

  1. SELECT o.customer_Id, oi.productj_id, SUM(oi.unit_price*oi. quantity) quot;Totalquot; FROM order_items oi JOIN orders o

    ON oi.order_id=o.order_id

    GROUP BY ROLLUP (o.customer_id.oi.product_id)

    WHERE MONTHS_BETWEEN(order_date, SYSDATE) lt;= 6;

  2. SELECT o.customer_id, oi.product_id, SUM(oi.unit_price*oi. quantity) quot;Totalquot; FROM orderjtems oi JOIN orders o

    ON oi.order_id=o.order_id

    GROUP BY ROLLUP (o.customer_id.oi.product_id)

    HAVING MONTHS_BETWEEN(order_date, SYSDATE) lt;= 6;

  3. SELECT o.customer_id, oi.product_id, SUM(oi.unit_price*oi.quantity) quot;Totalquot; FROM order_items oi JOIN orders o

    ON oi.order_id=o.order_id

    GROUP BY ROLLUP (o.customer_id, oi.product_id)

    WHERE MONTHS_BETWEEN(order_date, SYSDATE) gt;= 6;

  4. SELECT o.customer_id, oi.product_id, SUM(oi.unit_price*oi.quantity) quot;Totalquot; FROM order_items oi JOIN orders o

ON oi.order_id=o.order_id

WHERE MONTHS_BETWEEN(order_date, SYSDATE) lt;= 6

GROUP BY ROLLUP (o.customer_id, oi.product_id);

Answer: D

Question No: 144

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

Your company wants to give 5% bonus to all the employees on their annual salary. The SALARY column stores the monthly salary for an employee. To check the total for annual salary and bonus amount for each employee, you issued the following SQL statement:

SELECTfirst_name, salary, salary*!2 salary*12*.05 quot;ANNUAL SALARY BONUSquot; FROM employees;

Which statement is true regarding the above query?

Latesttests 2018 PDF and VCE

  1. It would execute and give you the desired output.

  2. It would not execute because the AS keyword is missing between the column name and the alias.

  3. It would not execute because double quotation marks are used instead of single quotation marks for assigning alias for the third column.

  4. It would execute but the result for the third column would be inaccurate because the parentheses for overriding the precedence of the operator are missing.

Answer: A

Question No: 145

View the Exhibit and examine the structure of the CUSTOMERS table. CUSTOMER_VU is a view based on CUSTOMERS_BR1 table which has the same

structure as CUSTOMERS table. CUSTOMERS needs to be updated to reflect the latest information about the customers.

What is the error in the following MERGE statement? MERGE INTO customers c

USING customer_vu cv

ON (c.customer_id = cv.customer_id) WHEN MATCHED THEN

UPDATE SET

c.customer_id = cv.customer_id, c.cust_name = cv.cust_name, c.cust_email = cv.cust_email,

  1. income_level = cv. Income_level WHEN NOT MATCHED THEN

    INSERT VALUESfcv.customer_id.cv.cus_name.cv.cus_email.cv.income_level) WHERE cv. Income_level gt;100000;

    Latesttests 2018 PDF and VCE

    1. The CUSTOMERJD column cannot be updated.

    2. The INTO clause is misplaced in the command.

    3. The WHERE clause cannot be used with INSERT.

    4. CUSTOMER VU cannot be used as a data source

Answer: A

Question No: 146

View the Exhibit and examine the descriptions for ORDERS and ORDER_ITEMS tables. Evaluate the following SQL statement:

SELECT o.customer_id, oi.product_id, SUM(oi.unit_price*oi. quantity) quot;Order Amountquot; FROM order_items oi JOIN orders o

ON oi.order_id = o.order_id

GROUP BY CUBE (o.customer_id, oi.product_id);

Which three statements are true regarding the output of this SQL statement? (Choose three.)

Latesttests 2018 PDF and VCE

  1. t would return the subtotals for the Order Amount of every CUSTOMER_ID.

  2. t would return the subtotals for the Order Amount for every PRODUCT_ID.

  3. t would return the subtotals for the Order Amount of every PRODUCT_ID and CUSTOMER_ID as one group.

  4. t would return the subtotals for the Order Amount of every CUSTOMER_ID and PRODUCT_ID as one group.

  5. t would return only the grand total for the Order Amount of every CUSTOMER_ID and PRODUCT_ID as one group.

Answer: A,B,D

Question No: 147

View the Exhibit and examine the structure of EMPLOYEES and JOB_IHISTORY tables.

The EMPLOYEES table maintains the most recent information regarding salary, department, and job for all the employees. The JOB_HISTORY table maintains the record for all the job changes for the employees. You want to delete all the records from the JOB_HISTORY table that are repeated in the EMPLOYEES table.

Which two SQL statements can you execute to accomplish the task? (Choose two.)

Latesttests 2018 PDF and VCE

  1. DELETE

    FROM job_history j WHERE employee_id = (SELECT employee_id FROM employees e

    WHERE j.employee_id = e.employee_id) AND job _id = (SELECT job _id

    FROM employees e WHERE j.job_id = e.job_id);

  2. DELETE

    FROM job_history j

    WHERE (employee_id, job _id) = ALL (SELECT employee_id, job_id

    FROM employees e

    WHERE j.employee_id = e.employee_id and j.job_id = e.job_id)

  3. DELETE

    FROM job_history j WHERE employee_id = (SELECT employee_id

    FROM employees e

    WHERE j.employee_id = e.employee_id and j.job_id = e.job_id)

  4. DELETE

FROM job_history j

WHERE (employee_id, job _id) = (SELECT employee_id, job_id FROM employees e

WHERE j.employee_id = e.employee_id and j.job_id = e.job_id)

Answer: C,D

Question No: 148

View the Exhibit and examine the descriptions of the DEPT and LOCATIONS tables.

You want to update the CITY column of the DEPT table for all the rows with the corresponding value in the CITY column of the LOCATIONS table for each department.

Which SOL statement would you execute to accomplish the task?

Latesttests 2018 PDF and VCE

  1. UPDATE deptd

    SET city = ANY (SELECT city FROM locations I);

  2. UPDATE deptd

    SET city = (SELECT city FROM locations I)

    WHERE d.location_id = l.location_id;

  3. UPDATE dept d

    SET city = (SELECT city

    FROM locations I

    WHERE d.location_id = l.location_id);

  4. UPDATE dept d

SET city = ALL (SELECT city FROM locations I

WHERE d.location_id = l.location_id);

Answer: C

Question No: 149

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

You want to see the product names and the date of expiration of warranty for all the products, if the product is purchased today. The products that have no warranty should be displayed at the top and the products with maximum warranty period should be displayed at the bottom.

Which SQL statement would you execute to fulfill this requirement?

Latesttests 2018 PDF and VCE

  1. SELECT product_name, category_id, SYSDATE warranty_period AS quot;Warranty expire datequot; FROM product_information ORDER BY SYSDATE-warranty_period;

  2. SELECT product_name, category_id, SYSDATE warranty_period AS quot;Warranty expire datequot; FROM product_information ORDER BY SYSDATE warranty_period;

  3. SELECT product_name, category_id, SYSDATE warranty_period AS quot;Warranty expire

    datequot; FROM product_information ORDER BY SYSDATE;

  4. SELECT product_name, category_id, SYSDATE warranty_period quot;Warranty expire datequot; FROM product_information WHERE warranty_period gt;SYSDATE;

Answer: B

Question No: 150

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

You want to calculate the total remuneration for each employee. Total remuneration is the sum of the annual salary and the percentage commission earned for a year. Only a few employees earn commission.

Which SOL statement would you execute to get the desired output?

Latesttests 2018 PDF and VCE

  1. SELECTfirst_name, salary, salary*12 salary*commission_pct quot;Totalquot; FROM EMPLOYEES;

  2. SELECTfirst_name, salary, salary*12 NVL((salary*commission_pct), 0) quot;Totalquot; FROM EMPLOYEES;

  3. SELECTfirst_name, salary, salary*12 NVL(salary, O)*commission_pct quot;Totalquot; FROM EMPLOYEES;

  4. SELECTfirst_name, salary, salary*12 (salary*NVL2(commission_pct, salary,salary commission_pct))quot;Totalquot;

FROM EMPLOYEES;

Answer: B

100% Free Download!
Download Free Demo:1z0-047 Demo PDF
100% Pass Guaranteed!
Download 2018 Latesttests 1z0-047 Full Exam PDF and VCE

Latesttests 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

2018 Latesttests IT Certification PDF and VCE