Validated of 1z0-047 exam question materials and cram for Oracle certification for IT engineers, Real Success Guaranteed with Updated 1z0-047 pdf dumps vce Materials. 100% PASS Oracle Database SQL Expert exam Today!

2016 Dec 1z0-047 syllabus:

Q141. Which CREATE TABLE statement is valid? 

A. CREATE TABLE ord_details 

(ord_no NUMBER(2) PRIMARY KEY, 

item_no NUMBER(3)PRIMARY KEY, 

ord_date date NOT NULL); 

B. CREATE TABLE ord_details 

(ord_no NUMBER(2) UNIQUE, NOT NULL, 

item_no NUMBER(3), 

ord_date date DEFAULT SYSDATE NOT NULL); 

C. 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)); 

D. 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


Q142. Which statement is true regarding external tables? 

A. The default REJECT LIMIT for external tables is UNLIMITED. 

B. The data and metadata for an external table are stored outside the database. 

C. ORACLE_LOADER and ORACLE_DATAPUMP have exactly the same functionality when used with an external table. 

D. The CREATE TABLE AS SELECT statement can be used to unload data into regular table in the database from an external table. 

Answer: D


Q143. Which three statements indicate the end of a transaction? (Choose three.) 

A. after a COMMIT is issued 

B. aftera ROLLBACK is issued 

C. afteraSAVEPOINTis issued 

D. afteraSELECT statement is issued 

E. aftera CREATE statementis issued 

Answer: ABE


Q144. 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) "Order Amount" 

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.) 

A. t wouldreturn thesubtotals for theOrder Amount of every CUSTOMER_ID. 

B. twould returnthesubtotals fortheOrderAmountfor every PRODUCT_ID. 

C. twould return the subtotals fortheOrder Amount of every PRODUCT_IDandCUSTOMER_ID asonegroup. 

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

E. t wouldreturnonly thegrandtotal for theOrderAmount ofeveryCUSTOMER_ID and PRODUCT_ID as onegroup. 

Answer: ABD


Q145. Evaluate the following statements: 

CREATE TABLE digits 

(id NUMBER(2), 

description VARCHAR2(15)); 

INSERT INTO digits VALUES (1,'ONE’); 

UPDATE digits SET description =TWO'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? 

A. Itwouldnot display any values. 

B. It would displaythevalue TWO once. 

C. Itwould display the valueTWOtwice. 

D. Itwould display the values ONE, TWO, andTWO. 

Answer: C


Most up-to-date 1z0-047 syllabus:

Q146. Evaluate the following CREATE TABLE commands: 

CREATE TABLE orders 

(ord_no NUMBER(2) CONSTRAINT ord_pk PRIMARY KEY, 

ord_date DATE, 

cust_id NUMBER(4)); 

CREATE TABLE ord_items 

(ord_no NUMBER(2), 

item_no NUMBER(3), 

qty NUMBER(3) CHECK (qty BETWEEN 100 AND 200), 

expiry_date date CHECK (expiry_date > SYSDATE), 

CONSTRAINT it_pk PRIMARY KEY (ord_no,item_no), 

CONSTRAINT ord_fk FOREIGN KEY(ord_no) REFERENCES orders(ord_no)); 

Why would the ORD_ITEMS table not get created? 

A. SYSDATE cannot be used with the CHECK constraint. 

B. TheCHECK constraint cannot be usedtwiceforthesame table. 

C. TheBETWEENclause cannot be used for the CHECK constraint. 

D. ORD_NO and ITEM_NO cannotbeused asacomposite primarykeybecause ORD_NO is also the FOREIGN KEY. 

Answer: A


Q147. Which two statements are true regarding multiple-row subqueries? (Choose two.) 

A. They can containgroupfunctions. 

B. They always contain a subquery within a subquery. 

C. They use the < ALL operator to imply less than the maximum. 

D. They can be used to retrieve multiple rows from a single table only. 

E. Theyshouldnot be used withthe NOTIN operator inthemainquery if NULLislikelytobea part ofthe result of thesubquery. 

Answer: AE


Q148. Which statement best describes the GROUPING function? 

A. It is used to set the order for the groups to be used for calculating the grand totals and subtotals. 

B. It is used to form various groups to calculate total and subtotals created using ROLLUP and CUBE operators. 

C. It is used to identify if the NULL value in an expression is a stored NULL value or created by ROLLUP or CUBE. 

D. It is used to specify the concatenated group expressions to be used for calculating the grand totals and subtotals. 

Answer: C


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

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

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

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

D. It produces n+1 possible superaggregatecombinations, if the n columns and expressions are specified in the GROUP BY clause. 

Answer: C


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

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

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

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

D. Using the WHERE clause before the GROUP BY clause excludes the rows before creating groups. 

E. 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: DE