All About Certified 1z0-047 examcollection

Q1. View the Exhibit and examine the structure of the ORDERS table. 

The ORDER_ID column is the PRIMARY KEY in the ORDERS table. Evaluate the following 

CREATE TABLE command: 

CREATE TABLE new_orders(ord_id, ord_date DEFAULT SYSDATE, cus_id) 

AS 

SELECT order_id.order_date.customer_id 

FROM orders; 

Which statement is true regarding the above command? 

A. The NEW_ORDERS table would not get created because the DEFAULT value can not be specified in the column definition. 

B. The NEW_ORDERS table would get created and only the NOT NULL constraint definedon the specified columns would bepassed to the new table. 

C. The NEW_ORDERS table would not get created because the column names in the CREATE TABLE command and the SELECT clause do not match. 

D. The NEW_ORDERS table would get created and all the constraints defined on the specified columns in the ORDERS table would be passed to the new table. 

View Answer

Q2. Evaluate the following SQL statements in the given order: 

DROP TABLE dept; 

CREATE TABLE dept 

(deptno NUMBER(3) PRIMARY KEY, 

deptname VARCHAR2(10)); 

DROP TABLE dept; 

FLASHBACK TABLE dept TO BEFORE DROP; 

Which statement is true regarding the above FLASHBACK operation? 

A. It recovers only the firstDEPTtable. 

B. Itrecovers onlythesecondDEPTtable. 

C. It does not recoveranyof the tables becauseFLASHBACKis not possible in this case. 

D. Itrecovers both the tables but the names would be changed to the ones assigned intheRECYCLEBIN. 

View Answer

Q3. Evaluate the SQL statements: 

CREATE TABLE new_order 

(orderno NUMBER(4), 

booking_date TIMESTAMP WITH LOCAL TIME ZONE); 

The database is located in San Francisco where the time zone is -8:00. 

The user is located in New York where the time zone is -5:00. 

A New York user inserts the following record: 

INSERT INTO new_order 

VALUES(1, TIMESTAMP ?007-05-10 6:00:00 -5:00?); 

Which statement is true? 

A. When theNewYorkuserselects the row, booking_date is displayed as 007-05-10 3.00.00.000000' 

B. When the New York user selectstherow, booking_date is displayed as 2007-05-10 6.00.00.000000 -5:00'. 

C. WhentheSan Francisco user selectstherow, booking_date is displayed as 007-05-103.00.00.000000' 

D. When the San Francisco user selectstherow, booking_dateis displayed 

as 007-05-103.00.00.000000-8:00' 

View Answer

Q4. Evaluate the following ALTER TABLE statement: 

ALTER TABLE orders 

SET UNUSED order_date; 

Which statement is true? 

A. The DESCRIBE command would still display the ORDER_DATE column. 

B. ROLLBACK can be used to get back the ORDER_DATE column in the ORDERS table. 

C. The ORDER_DATE column should be empty for the ALTER TABLE command to execute successfully. 

D. After executing the ALTER TABLE command, you can add a new column called ORDER_DATE to the ORDERS table. 

View Answer

Q5. 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. SELECT first_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, salaryFROM 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' 

View Answer

Q6. View the Exhibit and examine the data in the CUST_DET table. 

You executed the following multitable INSERT statement: 

INSERT FIRST 

WHEN credit_limit >= 5000 THEN 

INTO cust_1 VALUES(cust_id, credit_limit, grade, gender) 

WHEN grade = THE 

INTO cust_2 VALUES(cust_id, credit_limit, grade, gender) 

WHEN gender = THE 

INTO cust_3 VALUES(cust_id, credit_limit, grade, gender) 

INTO cust_4 VALUES(cust_id, credit_limit, grade, gender) 

ELSE 

INTO cust_5 VALUES(cust_id, credit_limit, grade, gender) 

SELECT * FROM cust_det; 

The row will be inserted in________. 

A. CUST_1 table only because CREDIT_LIMIT condition is satisfied 

B. CUST_1and CUST_2 tables because CREDIT_LIMIT and GRADE conditions are satisfied 

C. CUST_1,CUST_2 and CUST_5 tables because CREDIT_LIMIT and GRADE conditions are satisfied but GENDER condition is not satisfied 

D. CUST 1, CUST 2 and CUST 4 tables because CREDIT LIMIT and GRADE conditions are satisfied for CUST 1 and CUST 2, and CUST 4 has no condition on it 

View Answer

Q7. Which three statements are true regarding the WHERE and HAVING clauses in a SQL statement? (Choose three.) 

A. The HAVING clause conditions can have aggregate functions. 

B. The HAVING clause conditions can use aliases for the columns. 

C. WHERE and HAVING clauses cannot be used together in a SQL statement. 

D. The WHERE clause is used to exclude rows before the grouping of data. 

E. The HAVING clause is used to exclude one or more aggregated results after grouping data. 

View Answer

Q8. View the Exhibit and examine the structure of the ORDERS table. The ORDER_ID column is the 

PRIMARY KEY in the ORDERS table. 

Evaluate the following CREATE TABLE command: 

CREATE TABLE new_orders(ord_id, ord_date DEFAULT SYSDATE, cus_id) 

AS 

SELECT order_id.order_date,customer_id 

FROM orders; 

Which statement is true regarding the above command? 

A. The NEW_IDRDERS table would not get created because the DEFAULT value can not be specified in the column definition. 

B. The NEW_IDRDERS table would get created and only the NOTNULL constraint defined on the specified columns would be passed to the new table. 

C. The NEW_IDRDERS table would not get created because the column names in the CREATE TABLE commandand the SELECT clause do not match. 

D. The NEW_IDRDERS table would get created and all the constraints defined on the specified columns in the ORDERS table would be passed to the new table. 

View Answer

Q9. View the Exhibit and examine the structure of the ORD table. 

Evaluate the following SQL statements that are executed in a user session in the specified order: CREATE SEQUENCE ord_seq; 

SELECT ord_seq.nextval FROM dual; 

INSERT INTO ord 

VALUES (ord_seq.CURRVAL, 25-jan-2007',101); 

UPDATE ord 

SET ord_no= ord_seq.NEXTVAL 

WHERE cust_id =101; 

What would be the outcome of the above statements? 

A. All the statements would execute successfullyandthe ORD_NO column would containthevalue 2 for the CUSTJD101. 

B. TheCREATE SEQUENCE command would not execute because the minimum value and maximum value for the sequence have not been specified. 

C. The CREATE SEQUENCE command wouldnotexecute because the starting value of the sequence and the incrementvaluehave not been specified. 

D. Allthe statementswould execute successfully and the ORD_NO column wouldhave the value 20 for the CUST_ID101becausethedefault CACHE value is 20. 

View Answer

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

View Answer