Exam Code: 1z0 047 pdf (Practice Exam Latest Test Questions VCE PDF)
Exam Name: Oracle Database SQL Expert
Certification Provider: Oracle
Free Today! Guaranteed Training- Pass oracle database sql expert 1z0 047 Exam.
♥♥ 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
Q141. View the Exhibit and examine PRODUCTS and ORDER_ITEMS tables.
You executed the following query to display PRODUCT_NAME and the number of times the product has been ordered:
SELECT p.product_name, i.item_cnt
FROM (SELECT product_id, COUNT (*) item_cnt
FROM order_items
GROUP BY product_id) i RIGHT OUTER JOIN products p
ON i.product_id = p.product_id;
What would happen when the above statement is executed?
A. Thestatement would execute successfullytoproducetherequired output.
B. Thestatement wouldnotexecute because inlineviewsandouterjoins cannot be usedtogether.
C. The statementwouldnot execute because the ITEM_CNT alias cannotbedisplayedintheouter query.
D. The statement wouldnot execute because the GROUP BYclausecannot be used intheinline view.
Answer: A
Q142. Which two statements are true regarding operators used with subqueries? (Choose two.
A. The NOT IN operator is equivalent to IS NULL
B. The <ANY operator means less than the maximum.
C. =ANY and =ALL operators have the same functionality.
D. The IN operator cannot be used in single-row subqueries.
E. TheNOT operator can be used with IN, ANY and ALL operators.
Answer: BE
Q143. View the Exhibit and examine the structure of the EMPLOYEES table.
You want to display all employees and their managers having 100 as the MANAGER_ID. You want the output in two columns: the first column would have the LAST_NAME of the managers and the second column would have LAST_NAME of the employees.
Which SQL statement would you execute?
A. SELECT m.last_name "Manager", e.last_name "Employee"
FROM employees m JOIN employees e
ON m.employee_id = e.manager_id
WHERE m.manager_id=100;
B. SELECT m.last_name "Manager", e.last_name "Employee"
FROM employees m JOIN employees e
ON m.employee_id = e.manager_id
WHERE e.manager_id=100;
C. SELECT m.last_name "Manager", e.last_name "Employee"
FROM employees m JOIN employees e
ON e.employee_id = m.manager_id WHERE m.manager_id=100;
D. SELECT m.last_name "Manager", e.last_name "Employee"
FROM employees m JOIN employees e
WHERE m.employee_id = e.manager_id AND e.manager_id=100;
Answer: B
Q144. Which two statements best describe the benefits of using the WITH clause? (Choose two.)
A. It enables users to store the results of a query permanently.
B. It enables users to store the query block permanently in the memory and use it to create complex queries.
C. It enables users to reuse the same query block in a SELECT statement, if it occurs more than once in a complex query.
D. It can improve the performance of a large query by storing the result of a query block having the WITH clause in the user's temporary tablespace.
Answer: CD
Q145. Evaluate the following SQL statement:
SELECT 2 col1,ycol2
FROM dual
UNION
SELECT 1 ,'x'
FROM dual
UNION
SELECT 3 .NULL
FROM dual
ORDER BY 2;
Which statement is true regarding the output of the SQL statement?
A. It would execute and the order of the values in the first column would be 3,2,1.
B. It would execute and the order of the values in the first column would be 1,2,3.
C. It would not execute because the column alias name has not been used in the ORDER BY clause.
D. It would not execute because the number 2 in the ORDER BY clause would conflict with the value 2 in the first SELECT statement.
Answer: B
Q146. View the Exhibit button and examine the structures of ORDERS and ORDER_ITEMS tables.
In the ORDERS table, ORDER_ID is the PRIMARY KEY and in the ORDER_ITEMS table, ORDER_ID and LINE_ITEM_ID form the composite primary key.
Which view can have all the DML operations performed on it?
A. CREATE VIEWV1
ASSELECTorder_id, product_id
FROMorder_items;
B. CREATE VIEW V4(or_no, or_date, cust_id)
ASSELECTorder_id, order_date, customer_id
FROM orders
WHEREorder_date < 30-mar-2007'
WITH CHECK OPTION;
C. CREATEVIEW V3
ASSELECT o.order_id,o.customer_id, i.product_id
FROM orders o, order_items i
WHERE o.order_id=i.order_id;
D. CREATE VIEWV2
AS SELECT order_id, line_item_id, unit_price*quantity total
FROM order items;
Answer: B
Q147. Evaluate the following SQL statement:
CREATE INDEX upper_name_idx
ON product_information(UPPER(product_name));
Which query would use the UPPER_NAME_IDX index?
A. SELECT UPPER(product_name)
FROM product_information
WHERE product_jd = 2254;
B. SELECT UPPER(product_name)
FROM product_jnformation;
C. SELECT product_id
FROM product_jnformation
WHERE UPPER(product_name) IN ('LASERPRO', 'Cable);
D. SELECT product_jd, UPPER(product_name)
FROM product_information
WHERE UPPER(product_name)='LASERPRO' OR list_price > 1000;
Answer: C
Q148. 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
Q149. View the Exhibit and examine the structure of the EMP table.
You executed the following command to add a primary key to the EMP table:
ALTER TABLE emp
ADD CONSTRAINT emp_id_pk PRIMARY KEY (emp_id)
USING INDEX emp_id_idx;
Which statement is true regarding the effect of the command?
A. The PRIMARY KEY is created alongwitha new index.
B. The PRIMARY KEY is created anditwould use an existinguniqueindex.
C. The PRIMARY KEYwould becreated in a disabled state because it is usinganexisting index.
D. The statementproduces an error because the USING clause is permitted only intheCREATE TABLE command.
Answer: B
Q150. Which three tasks can be performed using regular expression support in Oracle Database 10g? (Choose three.)
A. it can be used to concatenate two strings.
B. it can be used to find out the total length of the string.
C. it can be used for string manipulation and searching operations.
D. it can be used to format the output for a column or expression having string data.
E. it can be used to find and replace operations for a column or expression having string data.
Answer: CDE