Proper study guides for Latest Oracle Oracle Database SQL Expert certified begins with Oracle 1z0-047 preparation products which designed to deliver the Verified 1z0-047 questions by making you pass the 1z0-047 test at your first time. Try the free 1z0-047 demo right now.

2016 Jul oca oracle database sql expert exam guide exam 1z0-047 download:

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


Q2. Which statement is true regarding external tables? 

A. The default REJECT LIMIT for external tables is UNLIMITED. 

B. The data and metadata for an external table are stored outside the database. 

C. ORACLE_LOADER and ORACLE_DATAPUMP have exactly the same functionality when used with an external table. 

D. The CREATE TABLE AS SELECT statement can be used to unload data into regular table in the database from an external table. 

Answer: D


Q3. View the Exhibit and examine the structure of the PRODUCT INFORMATION table. 


Which two queries would work? (Choose two.) 

A. SELECT product_name 

FROM product_information 

WHERE list_price = (SELECT AVG(list_price) 

FROM product_information); 


B. SELECT product_status 

FROM product_information 

GROUP BY product_status 

WHERE list_price < (SELECT AVG(list_price) 

FROM product_information); 


C. SELECT product_status 

FROM product_information 

GROUP BY product_status 

HAVING list_price > (SELECT AVG(list_price) 

FROM product_information); 


D. SELECT product_name FROM product_jnformation WHERE list_price < ANY(SELECT AVG(list_price) FROM product_jnformation GROUP BY product_status); 

Answer: AD


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

A. A simple view in which column aliases have been used cannot be updated. 

B. A subquery used in a complex view definition cannot contain group functions or joins. 

C. Rows cannot be deleted through a view if the view definition contains the DISTINCT keyword. 

D. Rows added through a view are deleted from the table automatically when the view is dropped. 

E. TheOR REPLACE option is used to change the definition of an existing view without dropping and re-creating it. 

F. The WITH CHECK OPTION constraint can be used in a view definition to restrict the columns displayed through the view. 

Answer: CE


Q5. Which three possible values can be set for the TIME_ZONE session parameter by using the ALTER SESSION command? (Choose three.) 

A. 'os' 

B. local 

C. -8:00' 

D. dbtimezone Li 

E. 'Australia' 

Answer: BCD


certifyforall.com

Renovate 1z0-047 pdf free download:

Q6. View the Exhibit and examine the structure of EMPLOYEES and JOB_IHISTORY tables. 

The EMPLOYEES table maintains the most recent information regarding salary, department, and job for all the employees. The JOB_HISTORY table maintains the record for all the job changes for the employees. You want to delete all the records from the JOB_HISTORY table that are repeated in the EMPLOYEES table. 

Which two SQL statements can you execute to accomplish the task? (Choose two.) 


A. DELETE FROM job_history j WHERE employee_id = (SELECT employee_id FROM employees e WHERE j.employee_id = e.employee_id) AND job _id = (SELECT job _id FROM employees e WHERE j.job_id = e.job_id); 

B. DELETE FROM job_history j WHERE (employee_id, job _id) = ALL (SELECT employee_id, job_id FROM employees e WHERE j.employee_id = e.employee_id and j.job_id = e.job_id) 

C. DELETE FROM job_history j WHERE employee_id = (SELECT employee_id 

FROM employees e 

WHERE j.employee_id = e.employee_id and j.job_id = e.job_id) 

D. DELETE FROM job_history j WHERE (employee_id, job _id) = (SELECT employee_id, job_id FROM employees e WHERE j.employee_id = e.employee_id and j.job_id = e.job_id) 

Answer: C,D


Q7. Which two statements are true regarding the GROUP BY clause in a SQL statement? (Choose two.) 

A. You can use column alias in the GROUP BY clause. 

B. Using the WHERE clause after the GROUP BY clause excludes the rows after creating groups. 

C. The GROUP BY clause is mandatory if you are using an aggregate function in the SELECT clause. 

D. Using the WHERE clause before the GROUP BY clause excludes the rows before creating groups. 

E. If the SELECT clause has an aggregate function, then those individual columns without an aggregate function in the SELECT clause should be included in the GROUP BY clause. 

Answer: DE


Q8. 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 SOL statement would get you the desired result? 


A. SELECT employee_id, first_name 

FROM employees 

WHERE job_id like‘MAN%' OR job_id like 'CLERK%' 

AND department_id = 30 AND salary > 3000; 


B. SELECT employee_ d, first_name 

FROM employees 

WHERE job_id like‘%MAN' OR job_id like '%CLERK' 

AND (department_id = 30 OR salary > 3000); 


C. SELECT employee_id, first_name 

FROM employees 

WHERE (job_id like‘%MAN' AND job_id like '%CLERK’) 

AND department_id = 30 OR salary > 3000; 


D. SELECT employee_id, first_name 

FROM employees 

WHERE (job_id like '%MAN' OR job_id like '%CLERK') 

AND department_id= 30 AND salary > 3000; 


Answer: D


Q9. Which statements are true regarding the usage of the WITH clause in complex correlated subqueries? (Choose all that apply.) 

A. It can be used only with the SELECT clause. 

B. The WITH clause can hold more than one query. 

C. If the query block name and the table name were the same, then the table name would take precedence. 

D. The query name in the WITH clause is visible to other query blocks in the WITH clause as well as to the main query block. 

Answer: ABD


Q10. View the Exhibit and examine the table structure of DEPARTMENTS and LOCATIONS tables. 


You want to display all the cities that have no departments and the departments that have not been allocated cities. 

Which type of join between DEPARTMENTS and LOCATIONS tables would produce this information as part of its output? 

A. NATURAL JOIN 

B. FULL OUTERJOIN 

C. LEFT OUTERJOIN 

D. RIGHT OUTER JOIN 

Answer: B