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

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: 171

The following are the steps for a correlated subquery, listed in random order:

  1. The WHERE clause of the outer query is evaluated.

  2. The candidate row is fetched from the table specified in the outer query.

  3. The procedure is repeated for the subsequent rows of the table, till all the rows are processed.

  4. Rows are returned by the inner query, after being evaluated with the value from the candidate row in the outer query. Identify the option that contains the steps in the correct sequence in which the Oracle server evaluates a correlated subquery.

A. 4,2,1,3

B. 4,1,2,3

C. 2,4,1,3

D. 2,1,4,3

Answer: C

Question No: 172

Evaluate the following command:

CREATE TABLE employees (employee_id NUMBER(2) PRIMARY KEY, last_name VARCHAR2(25) NOT NULL, department_id NUMBER(2), job_id VARCHAR2(8), salary NUMBER(10,2));

You issue the following command to create a view that displays the IDs and last names of the sales staff in the organization:

CREATE OR REPLACE VIEW sales_staff_vu AS SELECT employee_id, last_name job_id FROM employees WHERE job_id LIKE #39;SA_%#39; WITH CHECK OPTION;

Which statements are true regarding the above view? (Choose all that apply.)

  1. It allows you to insert details of all new staff into the EMPLOYEES table.

  2. It allows you to delete the details of the existing sales staff from the EMPLOYEES table.

  3. It allows you to update the job ids of the existing sales staff to any other job id in the EMPLOYEES table.

  4. It allows you to insert the IDs, last names and job ids of the sales staff from the view if it is used in multitable INSERT statements.

Answer: B,D

Question No: 173

View the Exhibit and examine DEPARTMENTS and the LOCATIONS tables. Evaluate the following SOL statement:

SELECT location_id, city FROM locations I

WHERE NOT EXISTS (SELECT location_id FROM departments

WHERE location_id lt;gt; I. location_id);

This statement was written to display LOCATION_ID and CITY where there are no departments located. Which statement is true regarding the execution and output of the command?

Latesttests 2018 PDF and VCE

  1. The statement would execute and would return the desired results.

  2. The statement would not execute because the = comparison operator is missing in the WHERE clause of the outer query.

  3. The statement would execute but it will return zero rows because the WHERE clause in the inner query should have the = operator instead of lt;gt;.

  4. The statement would not execute because the WHERE clause in the outer query is missing the column name for comparison with the inner query result.

Answer: C

Question No: 174

Which three tasks can be performed using regular expression support in Oracle Database 10g? (Choose three.)

  1. it can be used to concatenate two strings.

  2. it can be used to find out the total length of the string.

  3. it can be used for string manipulation and searching operations.

  4. it can be used to format the output for a column or expression having string data.

  5. it can be used to find and replace operations for a column or expression having string data.

Answer: C,D,E

Question No: 175

User OE, the owner of the ORDERS table, issues the following command:

GRANT SELECT .INSERT

ON orders TO hr

WITH GRANT OPTION;

The user HR issues the following command: GRANT SELECT

ON oe. orders TO scott;

Then, OE issues the following command:

REVOKE ALL

ON orders FROM hr;

Which statement is correct?

  1. The user SCOTT loses the privilege to select rows from OE.ORDERS.

  2. The user SCOTT retains the privilege to select rows from OE.ORDERS.

  3. The REVOKE statement generates an error because OE has to first revoke the SELECT privilege from SCOTT.

  4. The REVOKE statement generates an error because the ALL keyword cannot be used for privileges that have been granted using WITH GRANT OPTION.

Answer: A

Question No: 176

View the Exhibit and examine the data in the DEPARTMENTS tables. Evaluate the following SQL statement:

SELECT department_id quot;DEPT_IDquot;, department_name , #39;b#39; FROM departments

WHERE department id=90 UNION

SELECT department_id, department_name DEPT_NAME, #39;a#39; FROM departments

WHERE department_id=10

Which two ORDER BY clauses can be used to sort the output of the above statement? (Choose two.)

Latesttests 2018 PDF and VCE

  1. ORDER BY 3;

  2. ORDER BY#39;b#39;;

  3. ORDER BY DEPT_ID;

  4. ORDER BY DEPT NAME;

Answer: A,C

Question No: 177

Evaluate the following SQL statements that are issued in the given order:

CREATE TABLE emp

(emp_no NUMBER(2) CONSTRAINT emp_emp_no_pk PRIMARY KEY, enameVARCHAR2(15),

salary NUMBER(8,2),

mgr_no NUMBER(2) CONSTRAINT emp_mgr_fk REFERENCES emp);

ALTER TABLE emp

DISABLE CONSTRAINT emp_emp_no_pk CASCADE; ALTER TABLE emp

ENABLE CONSTRAINT emp_emp_no_pk;

What would be the status of the foreign key EMP_MGR_FK?

  1. It would be automatically enabled and deferred.

  2. It would be automatically enabled and immediate.

  3. It would remain disabled and has to be enabled manually using the ALTER TABLE command.

  4. It would remain disabled and can be enabled only by dropping the foreign key constraint and re-creating it.

Answer: A,B,D

Question No: 178

Evaluate the following statements:

CREATE TABLE digits (id NUMBER(2),

description VARCHAR2(15));

INSERT INTO digits VALUES (1,#39;ONE’);

UPDATE digits SET description =TWO#39;WHERE id=1; INSERT INTO digits VALUES (2.’TWO’);

COMMIT;

DELETE FROM digits;

SELECT description FROM digits

VERSIONS BETWEEN TIMESTAMP MINVALUE AND MAXVALUE;

What would be the outcome of the above query?

  1. It would not display any values.

  2. It would display the value TWO once.

  3. It would display the value TWO twice.

  4. It would display the values ONE, TWO, and TWO.

Answer: C

Question No: 179

View the Exhibit and examine the structure of the ORDERS table. Which UPDATE statement is valid?

Latesttests 2018 PDF and VCE

  1. UPDATE orders

    SET order_date = 12-mar-2007\ order_total IS NULL WHERE order_id = 2455;

  2. UPDATE orders

    SET order_date = 12-mar-2007#39;, order_total = NULL WHERE order_id = 2455;

  3. UPDATE orders

    SET order_date = #39;12-mar-2007#39;

    AND order_total = TO_NUMBER(NULL) WHERE order_id = 2455;

  4. UPDATE orders

SET order_date = TO_DATE(#39;12-mar-2007,l,dd-mon-yyyy#39;)l SET order_total = TO_NUMBER(NULL) WHERE order id = 2455;

Answer: B

Question No: 180

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

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.

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

  1. SELECT o.customer_id, oi.product_id, SUM(oi.unit_price*oi. quantity) quot;Total 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;Total FROM order_items 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;Total 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;Total 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

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