1z0-047 | Up to the immediate present 1z0-047 Exam Study Guides With New Update Exam Questions
Q1. View the Exhibit and examine the structure of the ORDERS table.
NEW_IDRDERS is a new table with the columns ORD_ID, ORD_DATE, CUST_ID, and ORD_TOTAL that have the same data types and size as the corresponding columns in the ORDERS table.
Evaluate the following INSERT statement:
INSERT INTO new_orders (ord_id, ord_date, cust_id, ord_total) VALUES
(SELECT order_id.order_date.customer_id.order_total FROM orders WHERE order_date > ‘31-dec-1999’);
Why would the INSERT statement fail?
A. because column names in NEWORDERS and ORDERS tables do not match
B. because the VALUES clause cannot be used in an INSERT with a subquery
C. because the WHERE clause cannot be used in a subquery embedded in an INSERT statement
D. because the total number of columns in the NEW ORDERS table does not match the total number of columns in the ORDERS table
Answer: B
Q2. View the Exhibit and examine the structure of the CUST table.
Evaluate the following SQL statements executed in the given order:
ALTER TABLE cust
ADD CONSTRAINT cust_id_pk PRIMARY KEY(cust_id) DEFERRABLE INITIALLY DEFERRED;
INSERT INTO cust VALUES (1 /RAJ1); --row 1
INSERT INTO cust VALUES (1 ,'SAM); --row 2
COMMIT;
SET CONSTRAINT cust_id_pk IMMEDIATE;
INSERT INTO cust VALUES (1 /LATA1); --row 3
INSERT INTO cust VALUES (2 .KING1); --row 4
COMMIT;
Which rows would be made permanent in the CUST table?
A. row4only
B. rows 2 and 4
C. rows 3 and 4
D. rows1and 4
Answer: C
Q3. Which two statements are true regarding constraints? (Choose two.)
A. A foreign key cannot contain NULL values.
B. A column with the UNIQUE constraint can contain NULL.
C. A constraint is enforced only for the INSERT operation on a table.
D. A constraint can be disabled even if the constraint column contains data.
E. All the constraints can be defined at the column level as well as the table level
Answer: BD
Q4. Which two statements are true regarding the execution of the correlated subqueries? (Choose two.)
A. The nested query executes after the outer query returns the row.
B. The nested query executes first and then the outer query executes.
C. The outer query executes only once for the result returned by the inner query.
D. Each row returned by the outer query is evaluated for the results returned by the inner query.
Answer: AD
Q5. Evaluate the following expression using meta character for regular expression:
'[^Ale|ax.r$]'
Which two matches would be returned by this expression? (Choose two.)
A. Alex
B. Alax
C. Alxer
D. Alaxendar
E. Alexender
Answer: DE
Q6. View the Exhibit and examine the structure of the LOCATIONS and DEPARTMENTS tables.
Which SET operator should be used in the blank space in the following SQL statement to display the cities that have departments located in them?
SELECT location_id, city
FROM locations
SELECT location_id, city
FROM locations JOIN departments
USING(location_id);
A. UNION
B. MINUS
C. INTERSECT
D. UNIONALL
Answer: C
Q7. Evaluate the following expression using meta character for regular expression:
'[AAle|ax.r$]'
Which two matches would be returned by this expression? (Choose two.)
A. Alex
B. Alax
C. Alxer
D. Alaxendar
E. Alexender
Answer: DE
Q8. View the Exhibit and examine the structure for the ORDERS and ORDER_ITEMS tables.
You want to display ORDER_ID, PRODUCT_ID, and TOTAL (UNIT_PRICE multiplied by QUANTITY) for all the orders placed in the last seven days.
Which query would you execute?
A. SELECT orde_id, product_id, unit_price*quantity "TOTAL"
FROM order_items oi JOIN orders o
ON (o.order_id=oi. order_id)
WHERE o.order_date>=SYSDATE-7;
B. SELECT o.order_id,oi.product_id, oi.unit_price*oi.quantity "TOTAL"
FROM order_items oi JOIN orders o
USING (order_id)
WHERE o.order_date>=SYSDATE-7;
C. SELECT o.order_id, oi.product_id, oi.unit_price*oi.quantity "TOTAL"
FROM order_items oi JOIN orders o
WHERE o.order_date>=SYSDATE-7 ON (o.order_id=oi. order_id);
D. SELECT o.order_id, oi.product_id, oi.unit_price*oi.quantity "TOTAL"
FROM orde_items oi JOIN orders o
ON (o.order_id=oi. order_id)
WHERE o. order date>=SYSDATE-7;
Answer: D
Q9. 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
Q10. Given below is the list of meta character syntaxes and their descriptions in random order: Meta character syntax Description 1)^ a) Matches character not in the list 2) [^…] b) Matches character when it occurs at the beginning of a line 3) | c) Treats the subsequent meta character as a literal 4) d) Matches one of the characters such as the OR operator
Identify the option that correctly matches the meta character syntaxes with their descriptions.
A. 1-b, 2-a, 3-d,4-c
B. 1-a, 2-b, 3-d,4-c
C. 1-d, 2-b, 3-a,4-c
D. 1-b,2-c,3-d,2-a
Answer: A