Exam Code: 1z0-047 (Practice Exam Latest Test Questions VCE PDF)
Exam Name: Oracle Database SQL Expert
Certification Provider: Oracle
Free Today! Guaranteed Training- Pass 1z0-047 Exam.
2021 Jun 1z0-047 Study Guide Questions:
Q161. Which three statements are true? (Choose three.)
A. Only one LONG column can be used per table.
B. ATIMESTAMP data type column stores only time values with fractional seconds.
C. The BLOB data type column is used to store binary data in an operating system file.
D. The minimum column width that can be specified for a varchar2 data type column is one.
E. The value for a CHAR data type column is blank-padded to the maximum defined column width.
Answer: ADE
Q162. Which two statements are true regarding the EXISTS operator used in the correlated subqueries? (Choose two.)
A. The outer query stops evaluating the result set of the inner query when the first value is found.
B. It is used to test whether the values retrieved by the inner query exist in the result of the outer query.
C. It is used to test whether the values retrieved by the outer query exist in the result set of the inner query.
D. The outer query continues evaluating the result set of the inner query until all the values in the result set are processed.
Answer: AC
Q163. You need to load information about new customers from the NEW_CUST table into the tables CUST and CUST_SPECIAL If a new customer has a credit limit greater than 10,000, then the details have to be inserted into CUST_SPECIAL All new customer details have to be inserted into the CUST table. Which technique should be used to load the data most efficiently?
A. external table
B. the MERGE command
C. the multi table INSERT command
D. INSERT using WITHCHECK OPTION
Answer: C
Renew 1z0-047 study guide:
Q164. The user SCOTT who is the owner of ORDERS and ORDERJTEMS tables issues the following GRANT command:
GRANT ALL ON orders, order_items TO PUBLIC;
What correction needs to be done to the above statement?
A. PUBLICshould bereplacedwithspecific usernames.
B. ALL should be replaced with a list of specific privileges.
C. WITH GRANTOPTIONshould beaddedto the statement.
D. Separate GRANT statements are required for ORDERSandORDERJTEMS tables.
Answer: D
Q165. Which view would you use to display the column names and DEFAULT values for a table?
A. DBA_TABLES
B. DBA_COLUMNS
C. USER_COLUMNS
D. USER TAB COLUMNS
Answer: D
Q166. 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,'MON DD YYYY} = 'JAN 20 2003'
C. WHERE order_date > T0_CHAR(ADD_M0NTHS(SYSDATE,6),'M0N DD YYYY’)
D. WHERE order_date IN (T0_DATE('0ct 21 2003','Mon DD YYYY’), T0_CHAR('N0V 21 2003','Mon DD YYYY’))
Answer: AB
Guaranteed 1z0-047 dump:
Q167. 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.
Which SQL statement would you execute to get the desired output?
A. 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;
B. 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) 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
Q168. View the Exhibit and examine the details of the EMPLOYEES table.
You want to generate a hierarchical report for all the employees who report to the employee whose EMPLOYEE_ID is 100.
Which SQL clauses would you require to accomplish the task? (Choose all that apply.)
A. WHERE
B. HAVING
C. GROUP BY
D. START WITH
E. CONNECT BY
Answer: ADE
Q169. View the Exhibit and examine the structure of the ORDERS table. The columns ORDER_MODE and ORDER_TOTAL have the default values 'direct' and 0 respectively.
Which two INSERT statements are valid? (Choose two.)
A. INSERT INTO orders
VALUES (1, O9-mar-2007', 'online',",1000);
B. INSERT INTO orders
(order_id ,order_date ,order_mode,
customer_id ,order_total)
VALUES(1 ,TO_DATE(NULL), 'online', 101, NULL);
C. INSERT INTO
(SELECT order_id ,order_date .customer_id
FROM orders)
VALUES (1,O9-mar-2007', 101);
D. INSERT INTO orders
VALUES (1,09-mar-2007', DEFAULT, 101, DEFAULT);
E. INSERT INTO orders
(order_id,order_date ,order_mode .order_total)
VALUES (1 ,'10-mar-2007','online',1000);
Answer: CD
Q170. Which two statements are true? (Choose two.)
A. The USER_SYNONYMSviewcan provide information about private synonyms.
B. The user SYSTEM owns all the base tables and user-accessible views of the data dictionary.
C. All the dynamic performance views prefixed with V$ are accessible to all the database users.
D. The USER_OBJECTS view can provide information about the tables and views created by the user only.
E. DICTIONARY is a view thatcontains thenamesof allthe datadictionary views that theuser can access.
Answer: AE