Get Smart with oracle database sql expert 1z0 047

Your success in Oracle 1z0 047 pdf is our sole target and we develop all our 1z0 047 dumps braindumps in a way that facilitates the attainment of this target. Not only is our oracle 1z0 047 study material the best you can find, it is also the most detailed and the most updated. oracle 1z0 047 Practice Exams for Oracle oracle database sql expert 1z0 047 are written to the highest standards of technical accuracy.

♥♥ 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

Q1. Evaluate the following command: 

CREATE TABLE employees 

(employee_id NUMBER(2) PRIMARY KEY, 

last_name VARCHAR2(25) NOT NULL, 

department_id NUMBER(2), job_id VARCHAR2(8), 

salary NUMBER(10,2)); 

You issue the following command to create a view that displays the IDs and last names of the sales staff in the organization: 

CREATE OR REPLACE VIEW sales_staff_vu AS 

SELECT employee_id, last_name job_id 

FROM employees 

WHERE job_id LIKE 'SA_%' WITH CHECK OPTION; 

Which statements are true regarding the above view? (Choose all that apply.) 

A. It allows you to insert details of allnewstaff into the EMPLOYEES table. 

B. Itallowsyou todeletethedetails of the existing sales staff fromtheEMPLOYEES table. 

C. It allows you to updatethejob ids oftheexisting sales staff to any other job id in the EMPLOYEES table. 

D. It allows you to insert the IDs, last names and job ids of the sales staff from theviewif it is used in multitable INSERT statements. 

Answer: BD

Q2. OE and SCOTT are the users in the database. The ORDERS table is owned by OE. Evaluate the statements issued by the DBA in the following sequence: 

CREATE ROLE r1; 

GRANT SELECT, INSERT ON oe. orders TO r1; 

GRANT r1 TO scott; 

GRANT SELECT ON oe. orders TO scott; 

REVOKE SELECT ON oe.orders FROM scott; 

What would be the outcome after executing the statements? 

A. SCOTT would be able to query the OE.ORDERS table. 

B. SCOTT would not be able to query the OE.ORDERS table. 

C. The REVOKE statement would remove the SELECT privilege from SCOTT as well as from the role R1. 

D. The REVOKE statement would give an error because the SELECT privilege has been granted to the role R1 

Answer: A

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

Q4. 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_OBJECTS 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

Q5. Which CREATE TABLE statement is valid? 

A. CREATE TABLE ord_details 

(ord_no NUMBER(2) PRIMARY KEY, 

item_no NUMBER(3)PRIMARY KEY, 

ord_date date NOT NULL); 

B. CREATE TABLE ord_details 

(ord_no NUMBER(2) UNIQUE, NOT NULL, 

item_no NUMBER(3), 

ord_date date DEFAULT SYSDATE NOT NULL); 

C. CREATE TABLE ord_details 

(ord_no NUMBER(2) , 

item_no NUMBER(3), 

ord_date date DEFAULT NOT NULL, 

CONSTRAINT ord_uq UNIQUE (ord_no), 

CONSTRAINT ord_pk PRIMARY KEY (ord_no)); 

D. CREATE TABLE ord_details 

(ord_no NUMBER(2), 

item_no NUMBER(3), 

ord_date date DEFAULT SYSDATE NOT NULL, 

CONSTRAINT ord_pk PRIMARY KEY (ord_no, item_no)); 

Answer: D

Q6. View the Exhibit and examine the structure of the EMP table which is not partitioned and not an index-organized table. 

Evaluate the following SQL statement: 

ALTER TABLE emp DROP COLUMN first_name; 

Which two statements are true regarding the above command? (Choose two.) 

A. The FIRST_NAME column wouldbedropped provided it does notcontainany data. 

B. The FIRST_NAME column would bedropped providedat least one or more columnsremain inthe table. 

C. The FIRST_NAME column can be rolledback providedthe SET UNUSED option is added to the above SQL statement. 

D. The FIRST_NAME column can be dropped evenif itis part ofacomposite PRIMARY KEY provided the CASCADE option is used. 

Answer: BD

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

Q8. Evaluate the following statement: 

INSERT ALL 

WHEN order_total < 10000 THEN 

INTO small_orders 

WHEN order_total > 10000 AND order_total < 20000 THEN 

INTO medium_orders 

WHEN order_total > 2000000 THEN 

INTO large_orders 

SELECT order_id, order_total, customer_id 

FROM orders; 

Which statement is true regarding the evaluation of rows returned by the subquery in the INSERT statement? 

A. They areevaluatedby allthe three WHENclauses regardlessofthe resultsof the evaluation ofany other WHEN clause. 

B. They are evaluated by thefirst WHENclause. If the condition is true, then the row would be evaluated by the subsequent WHEN clauses. 

C. They are evaluated by the first WHEN clause. If the condition isfalse,thenthe row wouldbeevaluated by the subsequentWHENclauses. 

D. TheINSERT statement would give an error becausetheELSE clause is notpresent forsupport in case none of theWHENclauses are true. 

Answer: A

Q9. View the Exhibit and examine the structure of the EMP table which is not partitioned and not an index-organized table. 

Evaluate the following SQL statement: 

ALTER TABLE emp DROP COLUMN first_name; 

Which two statements are true regarding the above command? (Choose two.) 

A. The FIRST_NAME column would be dropped provided it does not contain any data. 

B. The FIRST_NAME column would be dropped provided at least one or more columns remain in the table. 

C. The FIRST_NAME column can be rolled back provided the SET UNUSED option is added to the above SQL statement. 

D. The FIRST_NAME column can be dropped even if it is part of a composite PRIMARY KEY provided the CASCADE option is used. 

Answer: BD

Q10. View the Exhibit and examine the structure of the MARKS_DETAILS and MARKStables. Which is the best method to load data from the MARKS_DETAILStable to the MARKS table? 

A. PivotingINSERT 

B. Unconditional INSERT 

C. ConditionalALLINSERT 

D. Conditional FIRST INSERT 

Answer: A