1Z0-047_2.pdf - Part I

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?

View the Exhibit and examine the description of the CUSTOMERS table. You want to add a constraint on the CUST_FIRST_NAME column of the CUSTOMERS table so that the value inserted in the column does not have numbers. Which SQL statement would you use to accomplish the task?

Which three tasks can be performed using regular expression support in Oracle Database 10g? (Choose three.)

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 is true regarding the above command? (Choose two.)

Evaluate the CREATE TABLE statement: CREATE TABLE products (product_id NUMBER(6) CONSTRAINT prod_id_pk PRIMARY KEY, product_name VARCHAR2(15)); Which statement is true regarding the PROD_ID_PK constraint?

Which two statements are true? (Choose two.)

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

View the Exhibit and examine the description of the EMPLOYEES table. Your company decided to give a monthly bonus of $50 to all the employees who have completed five years in the company. The following statement is written to display the LAST_NAME, DEPARTMENT_ID, and the total annual salary: SELECT last_name, department_id, salary+50*12 "Annual Compensation" FROM employees WHERE MONTHS_BETWEEN(SYSDATE, hire_date)/12 >= 5; When you execute the statement, the "Annual Compensation" is not computed correctly. What changes would you make to the query to calculate the annual compensation correctly?

Evaluate the following CREATE SEQUENCE statement: CREATE SEQUENCE seq1 START WITH 100 INCREMENT BY 10 MAXVALUE 200 CYCLE NOCACHE; The sequence SEQ1 has generated numbers up to the maximum limit of 200. You issue the following SQL statement: SELECT seq1.nextval FROM dual; What is displayed by the SELECT statement?

View the Exhibit and examine the description of the EMPLOYEES table. You want to display the EMPLOYEE_ID, FIRST_NAME, and DEPARTMENT_ID for all the employees who work in the same department and have the same manager as that of the employee having EMPLOYEE_ID 104. To accomplish the task, you execute the following SQL statement: SELECT employee_id, first_name, department_id FROM employees WHERE (manager_id, department_id) =(SELECT department_id, manager_id FROM employees WHERE employee_id = 104) AND employee_id <> 104; When you execute the statement it does not produce the desired output. What is the reason for this?

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?

View the Exhibit and examine the structure of the EMPLOYEES table. You want to retrieve hierarchical data of the employees using the top-down hierarchy. Which SQL clause would let you choose the direction to walk through the hierarchy tree?

Which two statements is true regarding the execution of the correlated subqueries? (Choose two.)

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?

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?

EMPDET is an external table containing the columns EMPNO and ENAME. Which command would work in relation to the EMPDET table?

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 MARKStable?

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?

View the Exhibit and examine PRODUCTS and ORDER_ITEMS tables. You executed the following query to display PRODUCT_NAME and the number of times the product has been ordered: SELECT p.product_name, i.item_cnt FROM (SELECT product_id, COUNT (*) item_cnt FROM order_items GROUP BY product_id) i RIGHT OUTER JOIN products p ON i.product_id = p.product_id; What would happen when the above statement is executed?

In which scenario would you use the ROLLUP operator for expression or columns within a GROUP BY clause?

View the Exhibit and examine the details of the EMPLOYEES table. Evaluate the following SQL statement: SELECT phone_number, REGEXP_REPLACE(phone_number,'([[:digit:]]{3})\.([[:digit:]]{3})\.([[:digit:]]{4})', '(\1) \2-\3') "PHONE NUMBER" FROM employees; The query was written to format the PHONE_NUMBER for the employees. Which option would be the correct format in the output?

Which statement correctly grants a system privilege?

View the Exhibit and examine the structure of the CUST table. Evaluate the following SQL statements executed in the given order: ALTER TABLE cust ADD CONSTRAINT cust_id_pk PRIMARY KEY(cust_id) DEFERRABLE INITIALLY DEFERRED; INSERT INTO cust VALUES (1,'RAJ'); --row 1 INSERT INTO cust VALUES (1,'SAM'); --row 2 COMMIT; SET CONSTRAINT cust_id_pk IMMEDIATE; INSERT INTO cust VALUES (1,'LATA'); --row 3 INSERT INTO cust VALUES (2,'KING'); --row 4 COMMIT; Which rows would be made permanent in the CUST table?

View the Exhibit and examine the structure of the ORDERS table: The ORDER_ID column has the PRIMARY KEY constraint and CUSTOMER_ID has the NOT NULL constraint. Evaluate the following statement: INSERT INTO (SELECT order_id,order_date,customer_id FROM ORDERS WHERE order_total = 1000 WITH CHECK OPTION) VALUES (13, SYSDATE, 101); What would be the outcome of the above INSERT statement?

View the Exhibit and examine the description of the EMPLOYEES table. Your company wants to give 5% bonus to all the employees on their annual salary. The SALARY column stores the monthly salary for an employee. To check the total for annual salary and bonus amount for each employee, you issued the following SQL statement: SELECT first_name, salary, salary*12+salary*12*.05 "ANNUAL SALARY + BONUS" FROM employees; Which statement is true regarding the above query?

Which statement is true regarding external tables?

View the Exhibit and examine the structure of the PRODUCT_INFORMATION table. You want to see the product names and the date of expiration of warranty for all the products, if the product is purchased today. The products that have no warranty should be displayed at the top and the products with maximum warranty period should be displayed at the bottom. Which SQL statement would you execute to fulfill this requirement?

Which two statements are true regarding the EXISTS operator used in the correlated subqueries? (Choose two.)

A non-correlated subquery can be defined as ____.

You need to create a table for a banking application with the following considerations: 1) You want a column in the table to store the duration of the credit period. 2) The data in the column should be stored in a format such that it can be easily added and subtracted with 3) date type data without using the conversion functions. 4) The maximum period of the credit provision in the application is 30 days. 5) The interest has to be calculated for the number of days an individual has taken a credit for. Which data type would you use for such a column in the table?

Which statements are true regarding the hierarchical query in Oracle Database 10g? (Choose all that apply.)

Which two statements are true regarding views? (Choose two.)

View the Exhibit and examine the details of the ORDER_ITEMS table. Evaluate the following SQL statements: Statement 1: SELECT MAX(unit_price*quantity) "Maximum Order" FROM order_items; Statement 2: SELECT MAX(unit_price*quantity) "Maximum Order" FROM order_items GROUP BY order_id; Which statements are true regarding the output of these SQL statements? (Choose all that apply.)

View the Exhibit and examine the structure of the ORDERS and ORDER_ITEMS tables. Evaluate the following SQL statement: SELECT oi.order_id, product_id, order_date FROM order_items oi JOIN orders o USING(order_id); Which statement is true regarding the execution of this SQL statement?

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?

Evaluate the following statements: CREATE TABLE digits (id NUMBER(2), description VARCHAR2(15)); INSERT INTO digits VALUES (1,'ONE'); UPDATE digits SET description ='TWO' WHERE id=1; INSERT INTO digits VALUES (2,'TWO'); COMMIT; DELETE FROM digits; SELECT description FROM digits VERSIONS BETWEEN TIMESTAMP MINVALUE AND MAXVALUE; What would be the outcome of the above query?

View the Exhibit and examine the description of the ORDERS table. Evaluate the following SQL statement: SELECT order_id, customer_id FROM orders WHERE order_date > 'June 30 2001'; Which statement is true regarding the execution of this SQL statement?

Which statements are correct regarding indexes? (Choose all that apply.)

View the Exhibit and examine the description of the PRODUCT_INFORMATION table. Which SQL statement would retrieve from the table the number of products having LIST_PRICE as NULL?

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?

View the Exhibit and examine the structure of the EMP table. You executed the following command to add a primary key to the EMP table: ALTER TABLE emp ADD CONSTRAINT emp_id_pk PRIMARY KEY (emp_id) USING INDEX emp_id_idx; Which statement is true regarding the effect of the command?

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?

Which statement best describes the GROUPING function?

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

View the Exhibit and examine the structure of the CUSTOMERS table. CUSTOMER_VU is a view based on CUSTOMERS_BR1 table which has the same structure as CUSTOMERS table. CUSTOMERS needs to be updated to reflect the latest information about the customers. What is the error in the following MERGE statement? MERGE INTO customers c USING customer_vu cv ON (c.customer_id = cv.customer_id) WHEN MATCHED THEN UPDATE SET c.customer_id = cv.customer_id, c.cust_name = cv.cust_name, c.cust_email = cv.cust_email, c.income_level = cv.income_level WHEN NOT MATCHED THEN INSERT VALUES(cv.customer_id,cv.cust_name,cv.cust_email,cv,income_level) WHERE cv.income_level >100000;

Which two statements are true regarding operators used with subqueries? (Choose two.)

Given below are the SQL statements executed in a user session: CREATE TABLE product (pcode NUMBER(2), pname VARCHAR2(10)); INSERT INTO product VALUES(1, 'pen'); INSERT INTO product VALUES (2,'pencil'); SAVEPOINT a; UPDATE product SET pcode = 10 WHERE pcode = 1; SAVEPOINT b; DELETE FROM product WHERE pcode = 2; COMMIT; DELETE FROM product WHERE pcode=10; ROLLBACK TO SAVEPOINT a; Which statement describes the consequences?

Evaluate the following CREATE TABLE command: CREATE TABLE order_item (order_id NUMBER(3), item_id NUMBER(2), qty NUMBER(4), CONSTRAINT ord_itm_id_pk PRIMARY KEY (order_id,item_id) USING INDEX (CREATE INDEX ord_itm_idx ON order_item(order_id,item_id))); Which statement is true regarding the above SQL statement?

View the Exhibit and examine the description of EMPLOYEES and DEPARTMENTS tables. You want to display the EMPLOYEE_ID, LAST_NAME, and SALARY for the employees who get the maximum salary in their respective departments. The following SQL statement was written: WITH SELECT employee_id, last_name, salary FROM employees WHERE (department_id, salary) = ANY (SELECT * FROM dept_max) dept_max as ( SELECT d.department_id, max(salary) FROM departments d JOIN employees j ON (d.department_id = j.department_id) GROUP BY d.department_id); Which statement is true regarding the execution and the output of this statement?

View the Exhibit and examine the data in the DEPARTMENTS tables. Evaluate the following SQL statement: SELECT department_id "DEPT_ID", department_name , 'b' FROM departments WHERE department_id=90 UNION SELECT department_id, department_name DEPT_NAME, 'a' FROM departments WHERE department_id=10 Which two ORDER BY clauses can be used to sort the output of the above statement? (Choose two.)

View the Exhibit and examine the description of the EMPLOYEES table. You want to know the EMPLOYEE_ID and FIRST_NAME of all the records in the EMPLOYEES table wherein the JOB_ID column has ST_CLERK or ST_MAN values, the DEPARTMENT_ID column has value 30, and the SALARY column has a value greater than 3,000. Which SQL statement would get you the desired result?

View the Exhibit and examine the structure of the ORDERS table. The ORDERS table belongs to the user OE. HR is another user in the database. Evaluate the commands issued by users OE and HR in the following order: Statement 1 by user OE: GRANT SELECT, UPDATE(customer_id, order_total) ON orders TO hr; Statement 1 by user HR: SELECT * FROM oe.orders; Statement 2 by user HR: UPDATE oe.orders SET order_total= 10000; Which statement is true regarding the above commands?

View the Exhibit and examine the structure of the ORDER_ITEMS table. You need to display the ORDER_ID of the order that has the highest total value among all the orders in the ORDER_ITEMS table. Which query would produce the desired output?

Which two statements are true about sequences created in a single instance database? (Choose two.)

View the Exhibit and examine the structure of the EMPLOYEES and DEPARTMENTS tables. Which SET operator would you use in the blank space in the following SQL statement to list the departments where all the employees have managers? SELECT department_id FROM departments ____ SELECT department_id FROM employees WHERE manager_id IS NULL;

Which mandatory clause has to be added to the following statement to successfully create an external table called EMPDET? CREATE TABLE empdet (empno CHAR(2), ename CHAR(5), deptno NUMBER(4)) ORGANIZATION EXTERNAL (LOCATION ('emp.dat'));