It is impossible to pass Oracle oracle database sql expert 1z0 047 exam without any help in the short term. Come to Pass4sure soon and find the most advanced, correct and guaranteed Oracle oracle 1z0 047 practice questions. You will get a surprising result by our Regenerate Oracle Database SQL Expert practice guides.

Q11. View the Exhibit and examine the data in ORDERS and ORDER_ITEMS tables. 

You need to create a view that displays the ORDER ID, ORDER_DATE, and the total number of items in each order. 

Which CREATE VIEW statement would create the view successfully? 

A. CREATE OR REPLACE VIEW ord_vu (order_id,order_date) 

AS SELECT o.order_id, o.order_date, COUNT(i.line_item_id) 

"NO OF ITEMS" 

FROM orders o JOIN order_items i 

ON (o.order_id = i.order_id) 

GROUP BY o.order_id,o.order_date; 

B. CREATE OR REPLACE VIEW ord_vu 

AS SELECT o.order_id, o.order_date, COUNT(i.line_item_id) 

"NO OF ITEMS" 

FROM orders o JOIN order_items i 

ON (o.order_id = i.order_id) 

GROUP BY o.order_id,o.order_date; 

C. CREATE OR REPLACE VIEW ord_vu 

AS SELECT o.order_id, o.order_date, COUNT(i.line_item_id) 

FROM orders o JOIN order_items i ON (o.order_id = i.order_id) 

GROUP BY o.order_id,o.order_date; 

D. CREATE OR REPLACE VIEW ord_vu 

AS SELECT o.order_id, o.order_date, COUNT(i.line_item_id)ll’ NO OF ITEMS' 

FROM orders o JOIN order_items i 

ON (o.order_id = i.order_id) 

GROUP BY o.order_id,o.order_date 

WITH CHECK OPTION; 

Answer: B


Q12. Which statements are true? (Choose all that apply.) 

A. The data dictionary is created and maintained by the database administrator. 

B. The data dictionary views can consist of joins of dictionary base tables and user-defined tables. 

C. The usernames of all the users including the database administrators are stored in the data dictionary. 

D. The USER_CONS_COLUMNS view should be queried to find the names of the columns to which a constraint applies. 

E. Both USER_ODBJECTS and CAT views provide the same information about all the objects that are owned by the user. 

F. Views with the same name but different prefixes, such as DBA, ALL and USER, use the same base tables from the data dictionary 

Answer: CDF


Q13. Evaluate the following SQL statement: 

ALTER TABLE hr.emp SET UNUSED (mgr_id); 

Which statement is true regarding the effect of the above SQL statement? 

A. Any synonym existing on the EMP table would have to be re-created. 

B. Any constraints defined on the MGR_ID column would be removed by the above command. 

C. Any views created on the EMP table that include the MGR_ID column would have to be dropped and re-created. 

D. Any index created on the MGR_ID column would continue to exist until the DROP UNUSED COLUMNS command is executed. 

Answer: B


Q14. View the Exhibit and examine the structure of ORDERS and ORDER_ITEMS tables. 

ORDER__ID is the primary key in the ORDERS table. It is also the foreign key in the ORDER_ITEMS table wherein it is created with the ON DELETE CASCADE option. 

Which DELETE statement would execute successfully? 

A. DELETEorder_id 

FROMorders 

WHEREorder_total< 1000; 

B. DELETEorders 

WHERE order_total < 1000; 

C. DELETE 

FROM orders 

WHERE (SELECTorder_id 

FROM order_items); 

D. DELETE orders o, order_itemsi 

WHEREo.order id = i.order id; 

Answer: B


Q15. Which statement is true regarding the SESSION_PRIVS dictionary view? 

A. It contains the current object privileges available in the user session. 

B. It contains the current system privileges available in the user session. 

C. It contains the object privileges granted to other users by the current user session. 

D. It contains the system privileges granted to other users by the current user session. 

Answer: B


Q16. 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


Q17. Evaluate the following statement: 

CREATE TABLE bonuses(employee_id NUMBER, bonus NUMBER DEFAULT 100); 

The details of all employees who have made sales need to be inserted into the BONUSES table. You can obtain the list of employees who have made sales based on the SALES_REP_ID column of the ORDERS table. 

The human resources manager now decides that employees with a salary of $8,000 or less should receive a bonus. Those who have not made sales get a bonus of 1% of their salary. Those who have made sales get a bonus of 1 % of their salary and also a salary increase of 1 %. The salary of each employee can be obtained from the EMPLOYEES table. 

Which option should be used to perform this task most efficiently? 

A. MERGE 

B. Unconditional INSERT 

C. ConditionalALLINSERT 

D. Conditional FIRST INSERT 

Answer: A


Q18. The user SCOTT who is the owner of ORDERS and ORDER_ITEMS 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 ORDERSandORDER_ITEMS tables. 

Answer: D


Q19. View the Exhibit and examine the data in ORDERS_MASTER and MONTHLYjDRDERS tables. 

Evaluate the following MERGE statement: 

MERGE INTO orders_master o USING monthly_orders m 

ON (o.order_id = m.order_id) 

WHEN MATCHED THEN 

UPDATE SET o.order_total = m.order_total 

DELETE WHERE (m.order_total IS NULL) 

WHEN NOT MATCHED THEN 

INSERT VALUES (m.order_id, m.order_total); 

What would be the outcome of the above statement? 

A. The ORDERS_MASTER table would contain the ORDERJDs1and 2. 

B. TheORDERS_MASTERtablewould containtheORDERJDs 1,2and3. 

C. TheORDERS_MASTERtable would containtheORDERJDs 1,2 and 4. 

D. The ORDERSMASTER table would containtheORDER IDs 1,2,3 and4. 

Answer: C


Q20. Evaluate the following SQL statements that are issued in the given order: 

CREATE TABLE emp 

(emp_no NUMBER(2) CONSTRAINT emp_emp_no_pk PRIMARY KEY, 

enameVARCHAR2(15), 

salary NUMBER(8,2), 

mgr_no NUMBER(2) CONSTRAINT emp_mgr_fk REFERENCES emp); 

ALTER TABLE emp 

DISABLE CONSTRAINT emp_emp_no_pk CASCADE; 

ALTER TABLE emp 

ENABLE CONSTRAINT emp_emp_no_pk; 

What would be the status of the foreign key EMP_MGR_FK? 

A. It wouldbeautomatically enabled and deferred. 

B. It wouldbeautomaticallyenabledand immediate. 

C. Itwouldremaindisabled and has to beenabled manuallyusingthe ALTER TABLEcommand. 

D. Itwould remain disabled and can be enabled only by droppingtheforeign key constraint andre-creating it. 

Answer: ABD