Exam Code: oracle database sql expert 1z0 047 (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
Q41. Which three statements are true regarding group functions? (Choose three.)
A. They can be used on columns or expressions.
B. They can be passed as an argument to another group function.
C. They can be used only with a SQL statement that has the GROUP BY clause.
D. They can be used on only one column in the SELECT clause of a SQL statement.
E. They can be used along with the single-row function in the SELECT clause of a SQL statement.
Answer: ABE
Q42. 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
Q43. 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
Q44. View the Exhibit and examine the details of the PRODUCT_INFORMATION table.
Evaluate the following SQL statement:
SELECT TO_CHAR(list_price ,'$9,999") FROM product_information; Which two statements would be true regarding the output for this SQL statement? (Choose two.)
A. TheLIST_PRICEcolumn having value1123.90wouldbedisplayed as $1,124.
B. The LIST_PRICEcolumn having value 1123.90 would be displayed as$1,123.
C. The LIST_PRICE columnhavingvalue 11235.90would bedisplayed as $1,123.
D. The LIST_PRICE column having value 11235.90wouldbe displayed as #######.
Answer: AD
Q45. Evaluate the following SELECT statement and view the Exhibit to examine its output:
SELECT constraint_name, constraint_type, search_condition, r_constraint_name, delete_rule, status FROM user_constraints WHERE table_name = ORDERS
Which two statements are true about the output? (Choose two.)
A. Inthe secondcolumn, indicates a check constraint.
B. TheSTATUS columnindicateswhether the tableiscurrently in use.
C. The R_CONSTRAINT_NAME column givesthealternative name for the constraint.
D. The column DELETE_RULE decides the state oftherelated rows inthechild tablewhenthe corresponding row is deleted from the parent table.
Answer: AD
Q46. View the Exhibit and examine the structure of the EMPLOYEES table.
You want to know the FIRST_NAME and SALARY for all employees who have the same manager as that of the employee with the first name 'Neena' and have salary equal to or greater than that of'Neena'.
Which SQL statement would give you the desired result?
A. SELECTfirst_name, salary FROM employees WHERE (manager_id,salary) >= ALL (SELECT manager_id, salary FROM employees WHERE first_name = 'Neena') AND first_name <> 'Neena'
B. SELECTfirst_name, salary FROM employees WHERE (manager_id, salary) >= (SELECT manager_id, salary FROM employees WHERE first_name = 'Neena') AND first_name <> 'Neena'
C. SELECT first_name, salary FROM employees WHERE (manager_id,salary) >= ANY (SELECT manager_id, salary FROM employees WHERE first_name = 'Neena' AND first_name <> 'Neena'
D. SELECT first_name, salary FROM employees WHERE (manager_id = (SELECT manager_id FROM employees WHERE first_name = 'Neena') AND salary >= (SELECT salary FROM employees WHERE first_name = 'Neena')) AND first name <> 'Neena'
Answer: D
Q47. View the Exhibit and examine the description of the CUSTOMERS table.
You want to add a constraint on the CUST_FIRST_NAME column of the CUSTOMERS table so that the value inserted in the column does not have numbers.
Which SOL statement would you use to accomplish the task?
A. ALTER TABLE CUSTOMERS ADD CONSTRAINT
cust_f_name CHECK(REGEXP_LIKE(cust_first_name,,^A-Z’)NOVALIDATE;
B. ALTER TABLE CUSTOMERS ADD CONSTRAINT
cust_f_name CHECK(REGEXP_LIKE(cust_first_name,,'^[0-9]’))NOVALIDATE;
C. ALTER TABLE CUSTOMERS ADD CONSTRAINT
cust_f_name CHECK(REGEXP_LIKE(cust_first_name,'[[: alpha:]]'))NOVALIDATE;
D. ALTER TABLE CUSTOMERS ADD CONSTRAINT
cust_f_name CHECK(REGEXP_LIKE(cust_first_name,'[[: digit: ]]’))NOVALIDATE;
Answer: C
Q48. 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
Q49. View the Exhibit and examine the description of the ORDERS table.
Evaluate the following SQL statement:
SELECT order_id, customer_id
FROM orders
WHERE order_date > 'June 30 2001'
Which statement is true regarding the execution of this SQL statement?
A. It would not execute because 'June 30 2001' in the WHERE condition is not enclosed within double quotation marks.
B. It would execute and would return ORDER_ID and CUSTOMER_ID for all records having ORDER_DATE greater than 'June 30 2001'.
C. It would not execute because 'June 30 2001' in the WHERE condition cannot be converted implicitly and needs the use of the TO_DATE conversion function for proper execution.
D. It would not execute because 'June 30 2001' in the WHERE condition cannot be converted implicitly and needs the use of the TO_CHAR conversion function for proper execution.
Answer: C
Q50. View the Exhibit and examine the description of the ORDERS table.
The orders in the ORDERS table are placed through sales representatives only. You are given the task to get the SALES_REP_ID from the ORDERS table of those sales representatives who have successfully referred more than 10 customers. Which statement would achieve this purpose?
A. SELECT sales_rep_id, COUNT(customer_id)"Total"
FROMorders"
HAVING COUNT(customer_id)>10;
B. SELECT sales_rep_id, COUNT(customer_id) "Total"
FROM orders "
WHERE COUNT(customer_id)>10
GROUP BY sales_rep_id;
C. SELECT sales_rep_id, COUNT(customer_id) "Total"
FROMorders"
GROUP BY sales_rep_id
HAVING total > 10;
D. SELECT sales_rep_id, COUNT(customer_id) "Total"
FROM orders "
GROUP BY sales_rep_id
HAVING COUNT(customer_id) > 10;
Answer: D
