Welcome to Dumpsbase.com

1Z0-047 Dumps

Only $41.76, Automatic 28% OFF. More on Promotion page

$ 68
(241 Customer Reviews)
Exam Name

Oracle Database SQL Expert

Updated
2018-10-13
Q&A
260

1Z0-047 Exam Dumps Introduction

Want to pass Oracle Database SQL Expert 1Z0-047 exam in a short time? Which material is the best 1Z0-047preparation guides? How can I get them easily? Dumpsbase Oracle Oracle 1Z0-047 exam dumps are your big helper to achieve them. 

1Z0-047 Dumps Questions Format

Dumpsbase provides Oracle 1Z0-047 exam dumps questions in two formats. One is PDF version, you can print out to read all the questions and answers, which is easy and convenient for you to read and study. The other is Software version, you can test all Oracle 1Z0-047 exam dumps questions in a real exam environment.

After-sale Service of 1Z0-047 Dumps

After you purchase Dumpsbase Oracle Oracle exam dumps, you can get the file via email in 10 minutes in our working time, 12 hours in off-work time. If you fail Oracle 1Z0-047 exam, you only need to scan and send the score report to us via email, and then we will arrange a full payment refund for you in one working day. 

Question No : 1

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 ORDERJDs 1 and 2.
B. The ORDERS_MASTER table would contain the ORDERJDs 1, 2 and 3.
C. The ORDERS_MASTER table would contain the ORDERJDs 1, 2 and 4.
D. The ORDERS MASTER table would contain the ORDER IDs 1, 2, 3 and 4.
Answer: C

Question No : 2

EMPDET is an external table containing the columns EMPNO and ENAME.
Which command would work in relation to the EMPDET table?
A. UPDATE empdet
SET ename = 'Amit'
WHERE empno = 1234;
B. DELETE FROM empdet
WHERE ename LIKE 'J%';
C. CREATE VIEW empvu
AS
SELECT* FROM empdept;
D. CREATE INDEX empdet_dx
ON empdet (empno);
Answer: C

Question No : 3

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



You have to display ORDER_ID, ORDER_DATE, and CUSTOMER_ID for all those orders that were placed after the last order placed by the customer whose CUSTOMER_ID is 101
Which query would give you the desired output?
A. SELECT order_id, order_date FROM orders
WHERE order_date > ALL (SELECT MAX(order_date)
FROM orders) AND
Customer_id = 101;
B. SELECT order_id, order_date FROM orders
WHERE order_date > ANY (SELECT order_date
FROM orders
WHERE customer_id = 101);
C. SELECT order_id, order_date FROM orders
WHERE order_date > ALL (SELECT order_date
FROM orders
WHERE customer_id = 101);
D. SELECT order_id, order_date FROM orders
WHERE order_date IN (SELECT order_date
FROM orders
WHERE customer id = 101);
Answer: C

Question No : 4

Which three statements are true regarding single-row functions? (Choose three.)
A. They can accept only one argument.
B. They can be nested up to only two levels.
C. They can return multiple values of more than one data type.
D. They can be used in SELECT, WHERE, and ORDER BY clauses.
E. They can modify the data type of the argument that is referenced.
F. They can accept a column name, expression, variable name, or a user-supplied constant as arguments.
Answer: D,E,F

Question No : 5

Which two statements are true regarding the execution of the correlated subqueries? (Choose two.)
A. The nested query executes after the outer query returns the row.
B. The nested query executes first and then the outer query executes.
C. The outer query executes only once for the result returned by the inner query.
D. Each row returned by the outer query is evaluated for the results returned by the inner query.
Answer: A,D

Question No : 6

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?
A. It would not display any values.
B. It would display the value TWO once.
C. It would display the value TWO twice.
D. It would display the values ONE, TWO, and TWO.
Answer: C

Question No : 7

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 doe s not exist
What would be the outcome?
A. All the statements before the DELETE statement would be rolled back.
B. All the statements before the DELETE statement would be implicitly committed within the session.
C. All the statements up to the ALTER TABLE statement would be committed and the outcome of UPDATE statement would be rolled back.
D. All the statements up to the ALTER TABLE statement would be committed and the outcome of the UPDATE statement is retained uncommitted within the session.
Answer: D

Question No : 8

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



Evaluate the following SQL statement:
SELECT employee_id, department_id
FROM employees
WHERE department_id= 50 ORDER BY department_id
UNION
SELECT employee_id, department_id
FROM employees
WHERE department_id= 90
UNION
SELECT employee_id, department_id
FROM employees
WHERE department_id= 10;
What would be the outcome of the above SQL statement?
A. The statement would execute successfully and display all the rows in the ascending order of DEPARTMENT_ID.
B. The statement would execute successfully but it will ignore the ORDER BY clause and display the rows in random order.
C. The statement would not execute because the positional notation instead of the column name should be used with the ORDER BY clause.
D. The statement would not execute because the ORDER BY clause should appear only at the end of the SQL statement, that is, in the last SELECT statement.
Answer: D

Question No : 9

Which three tasks can be performed using regular expression support in Oracle Database 10g? (Choose three.)
A. it can be used to concatenate two strings.
B. it can be used to find out the total length of the string.
C. it can be used for string manipulation and searching operations.
D. it can be used to format the output for a column or expression having string data.
E. it can be used to find and replace operations for a column or expression having string data.
Answer: C,D,E

Question No : 10

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?
A. external table
B. the MERGE command
C. the multitable INSERT command
D. INSERT using WITH CHECK OPTION
Answer: C

Question No : 11

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.
Answer: C

Question No : 12

Evaluate the following command:
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 all new staff into the EMPLOYEES table.
B. It allows you to delete the details of the existing sales staff from the EMPLOYEES table.
C. It allows you to update the job ids of the existing 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 the view if it is used in multitable INSERT statements.
Answer: B,D

Question No : 13

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. It would be created only if a unique index is manually created first.
B. It would be created and would use an automatically created unique index.
C. It would be created and would use an automatically created nonunique index.
D. It would be created and remains in a disabled state because no index is specified in the command.
Answer: B

Question No : 14

Which two statements are true regarding roles? (Choose two.)
A. A role can be granted to itself.
B. A role can be granted to PUBLIC.
C. A user can be granted only one role at any point of time.
D. The REVOKE command can be used to remove privileges but not roles from other users.
E. Roles are named groups of related privileges that can be granted to users or other roles.
Answer: B,E

Question No : 15

View the Exhibit and examine the data in the PRODUCT INFORMATION table.



Which two tasks would require subqueries? (Choose two.)
A. displaying the minimum list price for each product status
B. displaying all supplier IDs whose average list price is more than 500
C. displaying the number of products whose list prices are more than the average list price
D. displaying all the products whose minimum list prices are more than the average list price of products having the product status orderable
E. displaying the total number of products supplied by supplier 102071 and having product status OBSOLETE
Answer: C,D

Question No : 16

Evaluate the following statement:
INSERT ALL
WHEN order_total < 10000 THEN
INTO small_orders
WHEN order_total > 10000 AND order_total < 20000 THEN
INTO medium_orders
WHEN order_total > 2000000 THEN
INTO large_orders
SELECT order_id, order_total, customer_id
FROM orders;
Which statement is true regarding the evaluation of rows returned by the subquery in the INSERT statement?
A. They are evaluated by all the three WHEN clauses regardless of the results of the evaluation of any other WHEN clause.
B. They are evaluated by the first WHEN clause. If the condition is true, then the row would be evaluated by the subsequent WHEN clauses.
C. They are evaluated by the first WHEN clause. If the condition is false, then the row would be evaluated by the subsequent WHEN clauses.
D. The INSERT statement would give an error because the ELSE clause is not present for support in case none of the WHEN clauses are true.
Answer: A

Question No : 17

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?
A. xxx-xxx-xxxx
B. (xxx) xxxxxxx
C. (xxx) xxx-xxxx
D. xxx-(xxx)-xxxx
Answer: C

Question No : 18

A non-correlated subquery can be defined as________ .
A. a set of sequential queries, all of which must always return a single value
B. a set of sequential queries, all of which must return values from the same table
C. a SELECT statement that can be embedded in a clause of another SELECT statement only
D. a set of one or more sequential queries in which generally the result of the inner query is used as the search value in the outer query
Answer: D

Question No : 19

View the Exhibit and examine the data in ORDERS_MASTER and MONTHLY_ORDERS 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 ORDER_IDs 1 and 2.
B. The ORDERS_MASTER table would contain the ORDER_IDs 1, 2 and 3.
C. The ORDERS_MASTER table would contain the ORDER_IDs 1, 2 and 4.
D. The ORDERS_MASTER table would contain the ORDER IDs 1, 2, 3 and 4.
Answer: C

Question No : 20

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.)
A. ORDER BY 3;
B. ORDER BY 'b';
C. ORDER BY DEPT_ID;
D. ORDER BY DEPT NAME;
Answer: A,C
Some similar or invalid comments have been hidden.

Leave your Review

Your Rating