The Secret of Oracle 1z0-047 exam

It is more faster and easier to pass the Oracle 1z0 047 dumps exam by using Vivid Oracle Oracle Database SQL Expert questuins and answers. Immediate access to the Up to the minute oracle 1z0 047 Exam and find the same core area 1z0 047 dumps questions with professionally verified answers, then PASS your exam with a high score now.


2024 Oracle Official New Released 1z0-047 ♥♥
https://www.certleader.com/1z0-047-dumps.html


Q1. View the Exhibit and examine the structure of the ORDERS table. The columns ORDER_MODE and ORDER_TOTAL have the default values 'direct' and 0 respectively. 

Which two INSERT statements are valid? (Choose two.) 

A. INSERT INTO orders 

VALUES (1, O9-mar-2007', 'online',",1000); 

B. INSERT INTO orders 

(order_id ,order_date ,order_mode, 

customer_id ,order_total) 

VALUES(1 ,TO_DATE(NULL), 'online', 101, NULL); 

C. INSERT INTO 

(SELECT order_id ,order_date .customer_id 

FROM orders) 

VALUES (1,O9-mar-2007', 101); 

D. INSERT INTO orders 

VALUES (1,09-mar-2007', DEFAULT, 101, DEFAULT); 

E. INSERT INTO orders 

(order_id,order_date ,order_mode .order_total) 

VALUES (1 ,'10-mar-2007','online',1000); 

Answer: CD

Q2. The details of the order ID, order date, order total, and customer ID are obtained from the ORDERS table. If the order value is more than 30000, the details have to be added to the LARGEjDRDERS table. The order ID, order date, and order total should be added to the ORDERJHISTORY table, and order ID and customer ID should be added to the CUSTJHISTORY table. Which multitable INSERT statement would you use? 

A. Pivoting INSERT 

B. Unconditional INSERT 

C. ConditionalALLINSERT 

D. Conditional FIRST INSERT 

Answer: C

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

Q4. View the Exhibit and examine the description of the ORDERS table. 

You need to display CUSTOMER_ID for all customers who have placed orders more than three times in the last six months. You issued the following SQL statement: 

SELECT customer_id,COUNT(order_id) FROM orders WHERE COUNT(order_id)>3 AND order_date BETWEEN ADD_MONTHS(SYSDATE,-6) AND SYSDATE GROUP BY customer_id; 

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

A. It would execute successfully and provide the desired result. 

B. It would not execute because the WHERE clause cannot have an aggregate function. 

C. It would not execute because the ORDER_ID column is not included in the GROUP BY clause. 

D. It would not execute because the GROUP BY clause should be placed before the WHERE clause. 

Answer: B

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

You want to display the EMPLOYE_ID, FIRST_NAME, and DEPARTMEN_ID for all the employees who work in the same department and have the same manager as that of the employee having EMPLOYE_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? 

A. The WHERE clause condition in the main query is using the = comparison operator, instead of EXISTS. 

B. TheWHERE clause condition in themainquery is usingthe= comparison operator,insteadof theINoperator. 

C. The WHERE clause condition in themainquery is using the=comparison operator,insteadof the =ANYoperator. 

D. The columns in the WHERE clause condition of the main query andthecolumns selected inthesubquery should be in the same order. 

Answer: D

Q6. Given below are the SQL statements executed in a user session: 

CREATE TABLE product 

(pcode NUMBER(2), 

pnameVARCHAR2(10)); 

INSERT INTO product VALUES(1, ‘pen’); 

INSERT INTO product VALUES (2,'penci’); 

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? 

A. No SQL statementwouldberolledback. 

B. Both the DELETE statements would be rolled back. 

C. Only the second DELETE statementwould be rolledback. 

D. Both theDELETE statementsand the UPDATE statement would berolledback. 

Answer: A

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

Q8. View the Exhibit and examine the data in ORDERS_MASTER and MONTHLYjDRDERS tables. 

Evaluate the following MERGE statement: 

MERGE INTO orders_master o USING monthly_orders m 

ON (o.order_id = m.order_id) 

WHEN MATCHED THEN 

UPDATE SET o.order_total = m.order_total 

DELETE WHERE (m.order_total IS NULL) 

WHEN NOT MATCHED THEN 

INSERT VALUES (m.order_id, m.order_total); 

What would be the outcome of the above statement? 

A. The ORDERS_MASTER table would contain the ORDERJDs1and 2. 

B. TheORDERS_MASTERtablewould containtheORDERJDs 1,2and3. 

C. TheORDERS_MASTERtable would containtheORDERJDs 1,2 and 4. 

D. The ORDERSMASTER table would containtheORDER IDs 1,2,3 and4. 

Answer: C

Q9. View the Exhibit and examine the structure of ORDERS and CUSTOMERS tables. 

Which INSERT statement should be used to add a row into the ORDERS table for the customer whose CUST LAST NAME is Roberts and CREDIT LIMIT is 600? 

A. INSERT INTO orders VALUES (1,'10-mar-2007', 'direct', (SELECT customer_id FROM customers WHERE cust_last_name='Roberts' AND credit_limit=600), 1000); 

B. INSERT INTO orders (order_id,order_date,order_mode, (SELECT customer_id FROM customers WHERE cust_last_name='Roberts' AND credit_limit=600) .order_total) 

VALUES(1 ,'10-mar-2007', 'direct', &&customer_id, 1000); 

C. INSERT INTO orders (order_id.order_date.order_mode, 

(SELECT customer_id 

FROM customers 

WHERE cust_last_name='Roberts' AND 

credit _limit=600) .order_total) 

VALUES(1 ,'IO-mar-2007', 'direct', &customer_id, 1000); 

D. INSERT INTO(SELECT o.order_id, o.order_date.o.orde_mode.c.customer_id, 

o.order_totalFROM orders o, customers c 

WHERE o.customer_id = c.customer_id 

AND c.cust_last_name='Roberts'ANDc. Credit_limit=600) 

VALUES (1,'10-mar-2007', 'direct',(SELECT customer_id 

FROM customers 

WHERE cust_last_name='Roberts' AND 

Credit_limit=600), 1000); 

Answer: A

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

Answer: C