10 07 2014

QUESTION 1
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 2
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 3
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 4
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: BD

QUESTION 5
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_id =
               (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_id = b.department_id)
     WHERE first_name||’ ‘||last_name = ‘Amit Banda’;
What would be the outcome of the above statement?

A.    It would execute successfully and update the relevant data.
B.    It would not execute successfully because there is no LOCATION_ID 2100 in the DEPARTMENTS
table.
C.    It would not execute successfully because the condition specified with the concatenation operator
is not valid.
D.    It would not execute successfully because multiple columns (SALARY,COMMISSION_PCT)cannot
be used in an UPDATE statement.

Answer: A

QUESTION 6
View the Exhibit and examine the description of the ORDERS table.
Your manager asked you to get the SALES_REP_ID and the total numbers of orders placed by each of the sales representatives. Which statement would provide the desired result?
 

A.    SELECT sales_rep_id, COUNT(order_id) total_orders
FROM orders
GROUP BY sales_rep_id;
B.    SELECT sales_rep_id, COUNT(order_id) total_orders
FROM orders
GROUP BY sales_rep_id, total_orders;
C.    SELECT sales_rep_id, COUNT(order_id) total_orders
FROM orders;
D.    SELECT sales_rep_id, COUNT(order_id) total_orders
FROM orders
WHERE sales_rep_id IS NOT NULL;

Answer: A

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

Answer: CD

QUESTION 8
Evaluate the following SQL statement:
SELECT 2 col1,ycol2
FROM dual
UNION
SELECT 1 ,’x’
FROM dual
UNION
SELECT 3 .NULL
FROM dual
ORDER BY 2;
Which statement is true regarding the output of the SQL statement?

A.    It would execute and the order of the values in the first column would be 3,2,1.
B.    It would execute and the order of the values in the first column would be 1,2,3.
C.    It would not execute because the column alias name has not been used in the ORDER BY clause.
D.    It would not execute because the number 2 in the ORDER BY clause would conflict with the value 2
in the first SELECT statement.

Answer: B

QUESTION 9
Which statement correctly differentiates a system privilege from an object privilege?

A.    System privileges can be granted only by the DBA whereas object privileges can be granted by DBAs
or the owner of the object.
B.    System privileges give the rights to only create user schemas whereas object privileges give rights to
manipulate objects in a schema.
C.    Users require system privileges to gain access to the database whereas they require object privileges
to create objects in the database.
D.    A system privilege is the right to perform specific activities in a database whereas an object privilege is
a right to perform activities on a specific object in the database.

Answer: D

QUESTION 10
Evaluate the following SQL statement:
CREATE INDEX upper_name_idx
ON product_information(UPPER(product_name));
Which query would use the UPPER_NAME_IDX index?
 

A.    SELECT UPPER(product_name)
FROM product_information
WHERE product_jd = 2254;
B.    SELECT UPPER(product_name)
FROM product_jnformation;
C.    SELECT product_id
FROM product_jnformation
WHERE UPPER(product_name) IN (‘LASERPRO’, ‘Cable);
D.    SELECT product_jd, UPPER(product_name)
FROM product_information
WHERE UPPER(product_name)=’LASERPRO’ OR list_price > 1000;

Answer: C
Passing your Oracle 1Z0-047 Exam by using the latest 1Z0-047 Exam Demo Full Version: http://www.braindump2go.com/1z0-047.html

1Z0-047 Exam Demo, 1Z0-047 Exam Dumps, 1Z0-047 Exam Questions, 1Z0-047 Practice Exam, Oracle 1Z0-047 Exam

2014 Latest Oracle 1Z0-822 Exam Demo Free Download! 2014 Latest Oracle 1Z0-144 Exam Demo Free Download!