Oracle 1z0-047 Free Practice Questions

Q1. View the Exhibit and examine the data in EMPLOYEES and 

DEPARTMENTS tables. In the EMPLOYEES table EMPLOYEE_ID is the PRIMARY KEY and DEPARTMENT_ID is the FOREIGN KEY. In the DEPARTMENTS table DEPARTMENT_ID is the PRIMARY KEY. 

Evaluate the following UPDATE statement: 

UPDATE employees a 

SET department_jd = 

(SELECT department_id 

FROM departments 

WHERE location_id = ‘2100’), 

(salary, commission_pct) = 

(SELECT 1.1*AVG(salary), 1.5*AVG(commission_pct) 

FROM employees b 

WHERE a. department_jd = b. department_id) 

WHERE first_name|| '||last_name = 'Amit Banda' 

What would be the outcome of the above statement? 

A. Itwould execute successfullyandupdate therelevant data. 

B. It would not execute successfully because there isno LOCATION_ID2100 in theDEPARTMENTStable. 

C. It wouldnotexecute successfully because the condition specified with the concatenation operator is not valid. 

D. Itwould not execute successfully becausemultiplecolumns (SALARY,COMMISSION_PCT)cannot be used in an UPDATE statement. 

View Answer

Q2. Which two statements best describe the benefits of using the WITH clause? (Choose two.) 

A. It enables users to store the results of a query permanently. 

B. It enables users to store the query block permanently in the memory and use it to create complex queries. 

C. It enables users to reuse the same query block in a SELECT statement, if it occurs more than once in a complex query. 

D. It can improve the performance of a large query by storing the result of a query block having the WITH clause in the user's temporary tablespace. 

View Answer

Q3. Which statement best describes the GROUPING function? 

A. It is used to set the order for the groups to be used for calculating the grand totals and subtotals. 

B. It is used to form various groups to calculate total and subtotals created using ROLLUP and CUBE operators. 

C. It is used to identify if the NULL value in an expression is a stored NULL value or created by ROLLUP or CUBE. 

D. It is used to specify the concatenated group expressions to be used for calculating the grand totals and subtotals. 

View Answer

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

View Answer

Q5. 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? 

A. Change the SELECT clause to SELECT last_name, department_id, salary*12+50 "Annual Compensation". 

B. Change the SELECT clause to SELECT last_name, department_id, salary+(50*12) "Annual Compensation". 

C. Change the SELECT clause to SELECT last_name, department_id, (salary +50)*12 "Annual Compensation". 

D. Change the SELECT clause to SELECT last_name, department_id, (salary*12)+50 "Annual Compensation". 

View Answer

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

View Answer

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

NEW_IDRDERS is a new table with the columns ORD_ID, ORD_DATE, CUST_ID, and ORD_TOTAL that have the same data types and size as the corresponding columns in the ORDERS table. 

Evaluate the following INSERT statement: 

INSERT INTO new_orders (ord_id, ord_date, cust_id, ord_total) VALUES

(SELECT order_id.order_date.customer_id.order_total FROM orders WHERE order_date > ‘31-dec-1999’); 

Why would the INSERT statement fail? 

A. because column names in NEWORDERS and ORDERS tables do not match 

B. because the VALUES clause cannot be used in an INSERT with a subquery 

C. because the WHERE clause cannot be used in a subquery embedded in an INSERT statement 

D. because the total number of columns in the NEW ORDERS table does not match the total number of columns in the ORDERS table 

View Answer

Q8. 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; 

View Answer

Q9. 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 /RAJ1); --row 1 

INSERT INTO cust VALUES (1 ,'SAM); --row 2 

COMMIT; 

SET CONSTRAINT cust_id_pk IMMEDIATE; 

INSERT INTO cust VALUES (1 /LATA1); --row 3 

INSERT INTO cust VALUES (2 .KING1); --row 4 

COMMIT; 

Which rows would be made permanent in the CUST table? 

A. row4only 

B. rows 2 and 4 

C. rows 3 and 4 

D. rows1and 4 

View Answer

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

View Answer

Q11. 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? 

A. WHERE 

B. HAVING 

C. GROUP BY 

D. START WITH 

E. CONNECT BY PRIOR 

View Answer

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

View Answer

Q13. Which two statements are true regarding multiple-row subqueries? (Choose two.) 

A. They can containgroupfunctions. 

B. They always contain a subquery within a subquery. 

C. They use the < ALL operator to imply less than the maximum. 

D. They can be used to retrieve multiple rows from a single table only. 

E. Theyshouldnot be used withthe NOTIN operator inthemainquery if NULLislikelytobea part ofthe result of thesubquery. 

View Answer

Q14. View the Exhibit and examine the structure of the EMPLOYEES table. 

You want to display all employees and their managers having 100 as the MANAGER_ID. You want the output in two columns: the first column would have the LAST_NAME of the managers and the second column would have LAST_NAME of the employees. 

Which SQL statement would you execute? 

A. SELECT m.last_name "Manager", e.last_name "Employee" 

FROM employees m JOIN employees e 

ON m.employee_id = e.manager_id 

WHERE m.manager_id=100; 

B. SELECT m.last_name "Manager", e.last_name "Employee" 

FROM employees m JOIN employees e 

ON m.employee_id = e.manager_id 

WHERE e.manager_id=100; 

C. SELECT m.last_name "Manager", e.last_name "Employee" 

FROM employees m JOIN employees e 

ON e.employee_id = m.manager_id WHERE m.manager_id=100; 

D. SELECT m.last_name "Manager", e.last_name "Employee" 

FROM employees m JOIN employees e 

WHERE m.employee_id = e.manager_id AND e.manager_id=100; 

View Answer

Q15. 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? 

A. Itwould becreated only if a unique index is manually created first. 

B. Itwould becreated andwould use an automatically created unique index. 

C. It would be createdandwould use an automaticallycreatednonunique index. 

D. Itwouldbecreated and remainsinadisabledstatebecauseno indexis specified in the command. 

View Answer