1z0-047 | Realistic Oracle 1z0-047 exam

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

Q32. User OE, the owner of the ORDERS table, issues the following command: 

GRANT SELECT,INSERT 

ON orders 

TO hr 

WITH GRANT OPTION; 

The user HR issues the following command: GRANT SELECT ON oe. orders TO scott; 

Then, OE issues the following command: 

REVOKE ALL ON orders FROM hr; 

Which statement is correct? 

A. The user SCOTT loses the privilege to select rows fromOE.ORDERS. 

B. The user SCOTT retains the privilege to select rows from OE.ORDERS. 

C. The REVOKE statement generates an error because OE has to first revoke the SELECT privilege from SCOTT. 

D. The REVOKE statement generates an error because the ALL keyword cannot be usedforprivilegesthathave been granted using WITH GRANT OPTION. 

Answer: A

Q33. View the Exhibit and examine the data in the EMPLOYEES tables. 

Evaluate the following SQL statement: SELECT employee_id, department_id FROM employees WHERE department_id= 50 ORDER BY department_id UNION SELECT employee_id, department_id FROM employees WHERE department_id= 90 UNION SELECT employee_id, department_id FROM employees WHERE department_id= 10; What would be the outcome of the above SQL statement? 

A. The statementwouldexecute successfullyanddisplay all the rows intheascending order of DEPARTMENT_ID. 

B. Thestatement would execute successfullybutitwillignoretheORDERBYclause and display the rows in random order. 

C. The statementwouldnot execute because the positional notationinstead of thecolumn name shouldbeusedwiththe ORDER BY clause. 

D. The statement would not execute because the ORDER BY clause should appear only at the end of the SQL statement, that is, in the last SELECT statement. 

Answer: D

Q34. SCOTT is a user in the database. 

Evaluate the commands issued by the DBA: 

1 - CREATE ROLE mgr; 

2 - GRANT CREATE TABLE, SELECT 

ON oe. orders 

TO mgr; 

3 - GRANT mgr, create table TO SCOTT; 

Which statement is true regarding the execution of the above commands? 

A. Statement 1 would not execute because the WITH GRANT option is missing. 

B. Statement 1 would not execute because the IDENTIFIED BY <password> clause is missing. 

C. Statement 3 would not execute because role and system privileges cannot be granted together in a single GRANT statement. 

D. Statement 2 would not execute because system privileges and object privileges cannot be granted together in a single GRANT command. 

Answer: D

Q35. You need to create a table with the following column specifications: 

1. Employee ID (numeric data type) for each employee 

2. Employee Name, (character data type) which stores the employee name 

3. Hire date, to store the date when the employee joined the organization 

4. Status (character data type). It should contain the value if no data is entered. 

5. Resume (character large object [CLOB] data type), which would contain the resume submitted by the employee 

Which is the correct syntax to create this table? 

A. CREATETABLEEMP_1 

(emp_id NUMBER(4), 

emp_name VARCHAR2(25), 

start_dateDATE, 

e_status VARCHAR2(10) DEFAULTACTIVE', 

resume CLOB(200)); 

B. CREATETABLE1_EMP 

(emp_idNUMBER(4), 

emp_nameVARCHAR2(25), 

start_dateDATE, 

emp_status VARCHAR2(10) DEFAULT ACTIVE', 

resume CLOB); 

C. CREATETABLE1_EMP 

(emp_id NUMBER(4), 

emp_name VARCHAR2(25),start_date DATE, 

emp_status VARCHAR2(10) DEFAULT "ACTIVE", 

resume CLOB); 

D. CREATE TABLEEMP_1 

(emp_id NUMBER, emp_nameVARCHAR2(25), 

start_dateDATE, 

emp_statusVARCHAR2(10) DEFAULTACTIVE', 

resume CLOB); 

Answer: D

Q36. View the Exhibit and examine the structure of ORD and ORD_ITEMS tables. 

In the ORD table, the PRIMARY KEY is ORD_NO and in the ORD_ITEMS tables the composite PRIMARY KEY is (ORD_NO, ITEM_NO). 

Which two CREATE INDEX statements are valid? (Choose two.) 

A. CREATE INDEX ord_idx ON ord(ord_no); 

B. CREATE INDEX ord_idx ON ord_items(ord_no); 

C. CREATE INDEX ord_idx ON ord_items(item_no); 

D. CREATE INDEX ord_idx ON ord,ord_items(ord_no, ord_date,qty); 

Answer: BC

Q37. View the Exhibit and examine the description of the EMPLOYEES table. 

Evaluate the following SQL statement: 

SELECT first_name, employee_id, NEXr_DAY(ADD_MONTHS(hire_date, 6), 1) "Review" FROM employees; 

The query was written to retrieve the FIRST_NAME, EMPLOYEE_ID, and review date for employees. 

The review date is the first Monday after the completion of six months of the hiring. The NLS_TERRITORY parameter is set to AMERICA in the session. 

Which statement is true regarding this query? 

A. The querywouldexecutetogive the desired output. 

B. Thequerywouldnot execute becausedatefunctions cannot be nested. 

C. Thequerywould executebuttheoutputwouldgive reviewdates that are Sundays. 

D. Thequery would not execute becausetheNEXT_DAY function acceptsastring as argument. 

Answer: C

Q38. You executed the following SQL statements in the given order: 

CREATE TABLE orders 

(order_id NUMBER(3) PRIMARY KEY, 

order_date DATE, 

customer_id number(3)); 

INSERT INTO orders VALUES (100,'10-mar-2007,,222); 

ALTER TABLE orders MODIFY order_date NOT NULL; 

UPDATE orders SET customer_id=333; 

DELETE FROM order; 

The DELETE statement results in the following error: 

ERROR at line 1: 

ORA-00942: table or view does not exist 

What would be the outcome? 

A. All the statements before the DELETEstatementwouldberolled back. 

B. Allthestatements before theDELETEstatement wouldbeimplicitly committedwithin thesession. 

C. Allthe statements up to theALTER TABLEstatement would be committedandthe outcomeof UPDATEstatementwouldberolled back. 

D. All the statements up to theALTERTABLEstatementwould be committed and the outcome oftheUPDATE statement is retained uncommittedwithin thesession. 

Answer: D

Q39. Evaluate the following CREATE TABLE commands: 

CREATE TABLE orders 

(ord_no NUMBER(2) CONSTRAINT ord_pk PRIMARY KEY, 

ord_date DATE, 

cust_id NUMBER(4)); 

CREATE TABLE ord_items 

(ord_no NUMBER(2), 

item_no NUMBER(3), 

qty NUMBER(3) CHECK (qty BETWEEN 100 AND 200), 

expiry_date date CHECK (expiry_date > SYSDATE), 

CONSTRAINT it_pk PRIMARY KEY (ord_no,item_no), 

CONSTRAINT ord_fk FOREIGN KEY(ord_no) REFERENCES orders(ord_no)); 

Why would the ORD_ITEMS table not get created? 

A. SYSDATE cannot be used with the CHECK constraint. 

B. TheCHECK constraint cannot be usedtwiceforthesame table. 

C. TheBETWEENclause cannot be used for the CHECK constraint. 

D. ORD_NO and ITEM_NO cannotbeused asacomposite primarykeybecause ORD_NO is also the FOREIGN KEY. 

Answer: A

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