1z0-047 | Renew Oracle 1z0-047 pdf
Q21. EMPDET is an external table containing the columns EMPNO and ENAME. Which command would work in relation to the EMPDET table?
A. UPDATE empdet SET ename ='Amit' WHERE empno = 1234;
B. DELETE FROM empdet WHERE ename LIKE 'J%'
C. CREATE VIEWempvu AS SELECT* FROMempdept;
D. CREATEINDEX empdet_dx ON empdet(empno);
Answer: C
Q22. View the Exhibit and examine the description of the ORDERS table.
Which two WHERE clause conditions demonstrate the correct usage of conversion functions? (Choose two.)
A. WHERE order_date> TO_DATE('JUL 10 2006','MON DD YYYY)
B. WHERE TO_CHAR(order_date,'MONDDYYYY) = 'JAN 20 2003'
C. WHEREorder_date > TO_CHAR(ADD_MONTHS(SYSDATE,6),'MONDDYYYY")
D. WHERE order_dateIN(TO_DATE('Oct 21 2003','Mon DD YYYY"),TO_CHAR('NOV21 2003','Mon DD YYYY"))
Answer: AB
Q23. 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
Q24. Which mandatory clause has to be added to the following statement to successfully create an external table called EMPDET?
CREATE TABLE empdet
(empno CHAR(2), ename CHAR(5), deptno NUMBER(4))
ORGANIZATION EXTERNAL
(LOCATION ('emp.daf));
A. TYPE
B. REJECTLIMIT
C. DEFAULT DIRECTORY
D. ACCESS PARAMETERS
Answer: C
Q25. Which mandatory clause has to be added to the following statement to successfully create an external table called EMPDET?
CREATE TABLE empdet
(empno CHAR(2), ename CHAR(5), deptno NUMBER(4))
ORGANIZATION EXTERNAL
(LOCATION ('emp.daf));
A. TYPE
B. REJECTLIMIT
C. DEFAULT DIRECTORY
D. ACCESS PARAMETERS
Answer: C
Q26. 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 superaggregate combinations, if the n columns and expressions are specified in the GROUP BY clause.
Answer: C
Q27. 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
Q28. View the Exhibit and examine the structure of the ORDER_ITEMS and ORDERS tables.
You are asked to retrieve the ORDER_ID, PRODUCT_ID, and total price (UNIT_PRICE multiplied
by QUANTITY), where the total price is greater than 50,000.
You executed the following SQL statement:
SELECT order_id, product_id, unit_price*quantity "Total Price" FROM order_items WHERE
unit_price*quantity > 50000 NATURAL JOIN orders; Which statement is true regarding the execution of the statement?
A. The statement would execute and provide the desired result.
B. The statement would not execute because the ON keyword is missing in the NATURAL JOIN clause.
C. The statement would not execute because the WHERE clause is before the NATURAL JOIN clause.
D. The statement would not execute because the USING keyword is missing in the NATURAL JOIN clause.
Answer: C
Q29. View the Exhibit and examine the structure of the ORD table.
Evaluate the following SQL statements that are executed in a user session in the specified order: CREATE SEQUENCE ord_seq;
SELECT ord_seq.nextval FROM dual;
INSERT INTO ord
VALUES (ord_seq.CURRVAL, 25-jan-2007',101);
UPDATE ord
SET ord_no= ord_seq.NEXTVAL
WHERE cust_id =101;
What would be the outcome of the above statements?
A. All the statements would execute successfullyandthe ORD_NO column would containthevalue 2 for the CUSTJD101.
B. TheCREATE SEQUENCE command would not execute because the minimum value and maximum value for the sequence have not been specified.
C. The CREATE SEQUENCE command wouldnotexecute because the starting value of the sequence and the incrementvaluehave not been specified.
D. Allthe statementswould execute successfully and the ORD_NO column wouldhave the value 20 for the CUST_ID101becausethedefault CACHE value is 20.
Answer: A
Q30. 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.
A. SELECT o.customer_Id, oi.productj_id, SUM(oi.unit_price*oi. quantity) "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) <= 6;
B. SELECT o.customer_id, oi.product_id, SUM(oi.unit_price*oi. quantity) "Total"
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) <= 6;
C. SELECT o.customer_id, oi.product_id, SUM(oi.unit_price*oi.quantity) "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) >= 6;
D. SELECT o.customer_id, oi.product_id, SUM(oi.unit_price*oi.quantity) "Total"
FROM order_items oi JOIN orders o
ON oi.order_id=o.order_id
WHERE MONTHS_BETWEEN(order_date, SYSDATE) <= 6
GROUP BY ROLLUP (o.customer_id, oi.product_id);
Answer: D