[Free] 2018(Jan) EnsurePass Testinsides Oracle 1z0-047 Dumps with VCE and PDF 21-30

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

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

Ensurepass 2018 PDF and VCE

Evaluate the following SOL statement: SELECT street_address

FROM locations WHERE

REGEXP_INSTR(street_address,#39;[^[: alpha:]]’) = 1;

Which statement is true regarding the output of this SOL statement?

  1. It would display all the street addresses that do not have a substring #39;alpha#39;.

  2. It would display all the street addresses where the first character is a special character.

  3. It would display all the street addresses where the first character is a letter of the alphabet.

  4. It would display all the street addresses where the first character is not a letter of the alphabet.

Answer: D

Question No: 22

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

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

  2. It produces only the grand totals for the groups specified in the GROUP BY clause.

  3. It produces higher-level subtotals, moving from right to left through the list of grouping columns specified in the GROUP BY clause.

  4. It produces higher-level subtotals, moving in all the directions through the list of grouping columns specified in the GROUP BY clause.

Answer: C

Question No: 23

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

Ensurepass 2018 PDF and VCE

You executed the following multitable INSERT statement: INSERT FIRST

WHEN credit_limit gt;= 5000 THEN

INTO cust_1 VALUES(cust_id, credit_limit, grade, gender) WHEN grade = THE

INTO cust_2 VALUES(cust_id, credit_limit, grade, gender) WHEN gender = THE

INTO cust_3 VALUES(cust_id, credit_limit, grade, gender) INTO cust_4 VALUES(cust_id, credit_limit, grade, gender) ELSE

INTO cust_5 VALUES(cust_id, credit_limit, grade, gender) SELECT * FROM cust_det;

The row will be inserted in .

  1. CUST_1 table only because CREDIT_LIMIT condition is satisfied

  2. CUST_1 and CUST_2 tables because CREDIT_LIMIT and GRADE conditions are satisfied

  3. CUST_1 ,CUST_2 and CUST_5 tables because CREDIT_LIMIT and GRADE conditions are satisfied but GENDER condition is not satisfied

  4. CUST 1, CUST 2 and CUST 4 tables because CREDIT LIMIT and GRADE conditions are satisfied for CUST 1 and CUST 2, and CUST 4 has no condition on it

Answer: A

Question No: 24

You executed the following SQL statements in the given order:

CREATE TABLE orders

(order_id NUMBER(3) PRIMARY KEY,

order_date DATE, customer_id number(3));

INSERT INTO orders VALUES (100,#39;10-mar-2007,,222);

ALTER TABLE orders MODIFY order_date NOT NULL; UPDATE orders SET customer_id=333;

DELETE FROM order;

The DELETE statement results in the following error: ERROR at line 1:

ORA-00942: table or view does not exist What would be the outcome?

  1. All the statements before the DELETE statement would be rolled back.

  2. All the statements before the DELETE statement would be implicitly committed within the session.

  3. All the statements up to the ALTER TABLE statement would be committed and the outcome of UPDATE statement would be rolled back.

  4. All the statements up to the ALTER TABLE statement would be committed and the outcome of the UPDATE statement is retained uncommitted within the session.

Answer: D

Question No: 25

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

A non-correlated subquery can be defined as .

  1. a set of sequential queries, all of which must always return a single value

  2. a set of sequential queries, all of which must return values from the same table

  3. a SELECT statement that can be embedded in a clause of another SELECT statement only

  4. a set of one or more sequential queries in which generally the result of the inner query is used as the search value in the outer query

Answer: D

Question No: 27

View the Exhibit and examine the data in ORDERS_MASTER and MONTHLY_ORDERS tables.

Ensurepass 2018 PDF and VCE

Evaluate the following MERGE statement: MERGE INTO orders_master o

USING monthly_orders m ON (o.order_id = m.order_id) WHEN MATCHED THEN

UPDATE SET o.order_total = m.order_total DELETE WHERE (m.order_total IS NULL) WHEN NOT MATCHED THEN

INSERT VALUES (m.order_id, m.order_total);

What would be the outcome of the above statement?

  1. The ORDERS_MASTER table would contain the ORDER_IDs 1 and 2.

  2. The ORDERS_MASTER table would contain the ORDER_IDs 1,2 and 3.

  3. The ORDERS_MASTER table would contain the ORDER_IDs 1,2 and 4.

  4. The ORDERS_MASTER table would contain the ORDER IDs 1,2,3 and 4.

Answer: C

Question No: 28

Which CREATE TABLE statement is valid?

  1. CREATE TABLE ord_details (ord_no NUMBER(2) PRIMARY KEY, item_no NUMBER(3) PRIMARY KEY, ord_date date NOT NULL);

  2. CREATE TABLE ord_details

    (ord_no NUMBER(2) UNIQUE, NOT NULL,

    item_no NUMBER(3),

    ord_date date DEFAULT SYSDATE NOT NULL);

  3. CREATE TABLE ord_details (ord_no NUMBER(2) ,

    item_no NUMBER(3),

    ord_date date DEFAULT NOT NULL, CONSTRAINT ord_uq UNIQUE (ord_no), CONSTRAINT ord_pk PRIMARY KEY (ord_no));

  4. CREATE TABLE ord_details (ord_no NUMBER(2),

item_no NUMBER(3),

ord_date date DEFAULT SYSDATE NOT NULL, CONSTRAINT ord_pk PRIMARY KEY (ord_no, item_no));

Answer: D

Question No: 29

Evaluate the following CREATE SEQUENCE statement:

CREATE SEQUENCE seql START WITH 100

INCREMENT BY 10

MAXVALUE 200 CYCLE NOCACHE;

The sequence SEQ1 has generated numbers up to the maximum limit of 200. You issue the following SQL statement:

SELECT seql.nextval FROM dual;

What is displayed by the SELECT statement?

  1. 1

  2. 10

C. 100

D. an error

Answer: A

Question No: 30

View the Exhibit and examine the descriptions of the EMPLOYEES and DEPARTMENTS tables.

Ensurepass 2018 PDF and VCE

The following SQL statement was executed:

SELECT e.department_id, e.job_id, d.location_id, sum(e.salary) total, GROUPING(e. department_id) GRP_DEPT,

GROUPING(e.job_id) GRPJOB,

GROUPING(d. location_id) GRP_LOC FROM employees e JOIN departments d ON e.department_id = d.department_id

Ensurepass 2018 PDF and VCE

GROUP BY ROLLUP (e.department_id, e.job_id, d.location_id); View the Exhibit2 and examine the output of the command.

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

  1. The value 1 in GRP_LOC means that the LOCATION_ID column is taken into account

    to generate the subtotal.

  2. The value 1 in GRPJOB and GRP_LOC means that JOB_ID and LOCATION_ID columns are not taken into account to generate the subtotal.

  3. The value 1 in GRPJOB and GRP_LOC means that the NULL value in JOBJD and LOCATIONJD columns are taken into account to generate the subtotal.

  4. The value 0 in GRP_DEPT, GRPJOB, and GRP_LOC means that DEPARTMENT_ID, JOB_ID, and LOCATION_ID columns are taken into account to generate the subtotal

Answer: B,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.