Act now and download your Oracle oracle database sql expert 1z0 047 test today! Do not waste time for the worthless Oracle oracle database sql expert 1z0 047 tutorials. Download Abreast of the times Oracle Oracle Database SQL Expert exam with real questions and answers and begin to learn Oracle oracle database sql expert 1z0 047 with a classic professional.
♥♥ 2021 NEW RECOMMEND ♥♥
Free VCE & PDF File for Oracle 1z0-047 Real Exam (Full Version!)
★ Pass on Your First TRY ★ 100% Money Back Guarantee ★ Realistic Practice Exam Questions
Free Instant Download NEW 1z0-047 Exam Dumps (PDF & VCE):
Available on:
http://www.surepassexam.com/1z0-047-exam-dumps.html
Q11. View the Exhibit and examine the structure of ORDER_ITEMS and ORDERS tables.
You need to remove from the ORDER_ITEMS table those rows that have an order status of 0 or 1 in the ORDERS table.
Which DELETE statements are valid? (Choose all that apply.)
A. DELETE FROM order_items WHERE order_id IN (SELECT order_id FROM orders WHERE order_status in (0,1));
B. DELETE * FROM order_items WHERE order_id IN (SELECT order_id FROM orders WHERE order_status IN (0,1));
C. DELETE FROM order_items i WHERE order_id = (SELECT order_id FROM orders o WHERE i. order_id = o. order_id AND order_status IN (0,1));
D. DELETE FROM (SELECT* FROM order_items i.orders o WHERE i.order_id = o.order_id AND order_status IN (0,1));
Answer: ACD
Q12. Given below are the SQL statements executed in a user session:
CREATE TABLE product
(pcode NUMBER(2),
pnameVARCHAR2(10));
INSERT INTO product VALUES(1, ‘pen’);
INSERT INTO product VALUES (2,'pencil);
SAVEPOINT a;
UPDATE product SET pcode = 10 WHERE pcode = 1;
SAVEPOINT b;
DELETE FROM product WHERE pcode = 2;
COMMIT;
DELETE FROM product WHERE pcode=10;
ROLLBACK TO SAVEPOINT a;
Which statement describes the consequences?
A. No SQL statementwouldberolledback.
B. Both the DELETE statements would be rolled back.
C. Only the second DELETE statementwould be rolledback.
D. Both theDELETE statementsand the UPDATE statement would berolledback.
Answer: A
Q13. 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
Q14. View the Exhibit and examine the structure of the EMPLOYEES table.
Evaluate the following SQL statement:
SELECT employee_id, last_name, job_id, manager_id FROM employees START WITH employee_id = 101 CONNECT BY PRIOR employee_id=manager_id;
Which statement is true regarding the output for this command?
A. It would return a hierarchical output starting with the employee whose EMPLOYEE_ID is 101, followed by his or her peers.
B. It would return a hierarchical output starting with the employee whose EMPLOYEE_ID is 101, followed by the employee to whom he or she reports.
C. It would return a hierarchical output starting with the employee whose EMPLOYEE_ID is 101, followed by employees below him or her in the hierarchy.
D. It would return a hierarchical output starting with the employee whose EMPLOYEE_ID is101, followed by employees up to one level below him or her in the hierarchy.
Answer: C
Q15. 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
Q16. Evaluate the CREATE TABLE statement:
CREATE TABLE products
(product_id NUMBER(6) CONSTRAINT prod_id_pk PRIMARY KEY,
product_name VARCHAR2(15));
Which statement is true regarding the PROD_ID_PK constraint?
A. Itwould becreated only if a unique index is manually created first.
B. Itwould becreated andwould use an automatically created unique index.
C. It would be createdandwould use an automaticallycreatednonunique index.
D. Itwouldbecreated and remainsinadisabledstatebecauseno indexis specified in the command.
Answer: B
Q17. Evaluate the following CREATE TABLE command:
CREATE TABLE order_item (order_id NUMBER(3),
Item_id NUMBER(2),
qty NUMBER(4),
CONSTRAINT ord_itm_id_pk
PRIMARY KEY (order_id jtem_id)
USING INDEX
(CREATE INDEX ord_itm_idx
ON order_item(order_id item_id)));
Which statement is true regarding the above SOL statement?
A. Itwould execute successfullyandonly ORD_ITM_IDX index would be created.
B. It would give an error because the USING INDEX clause cannot be used onacomposite primarykey.
C. It wouldexecutesuccessfully and two indexes ORD_ITM_IDXand ORD_ITM_ID_PKwould becreated.
D. Itwould give an error becausetheUSING INDEX clause isnotpermitted in the CREATETABLEcommand.
Answer: A
Q18. View the Exhibit and examine the description of the PRODUCT_INFORMATION table.
SELECT product_name, list_price, min_price, list_price - min_price Difference FROM product_information
Which options when used with the above SQL statement can produce the sorted output in ascending order of the price difference between LIST_PRICE and MIN_PRICE? (Choose all that apply.)
A. ORDERBY4
B. ORDER BY MIN_PRICE
C. ORDER BY DIFFERENCE
D. ORDER BY LIST_PRICE
E. ORDERBYLIST_PRICE–MIN_PRICE
Answer: ACE
Q19. 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
Q20. 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