Our Oracle Oracle exam questions along with answers are updated regularly in keeping with the Oracle true exam. They are associated with great price for the Oracle 1Z0-051 exam preparation. Testking gives the most precise along with authentic 1Z0-051 practice questions using verified answers. They are presented in a couple of formats. One is Pdf (printable files) along with the other is free Check Engine (downloadable). Oracle 1Z0-051 contains comprehensive and logical topics with the Oracle actual test. They are quite helpful along with valuable in your case to preview along with review to the Oracle 1Z0-051 exam. Taking measures right today and purchasing our Oracle 1Z0-051 products, you will end up being on the approach to Oracle Oracle certification soon. Our Oracle Pdf study guides are presented chapter by chapter along with printable. If you need the printed version, we all will mail them to you. With the convenient portable Oracle 1Z0-051 training materials, youll get a higher score which ensure your wonderful accomplishment.

2016 Sep 1z0-047 vs 1z0-051:

Q131. - (Topic 2) 

Evaluate the SQL statement: 

SELECT LPAD (salary,10,’*’) 

FROM EMP 

WHERE EMP_ID = 1001; 

If the employee with the EMP_ID 1001 has a salary of 17000, what is displayed? 

A. 17000.00 

B. 17000***** 

C. ****170.00 

D. **17000.00 

E. an error statement 

Answer: D 


Q132. - (Topic 2) 

Which three statements are true regarding views? (Choose three.) 

A. Views can be created only from tables. 

B. Views can be created from tables or other views. 

C. Only simple views can use indexes existing on the underlying tables. 

D. Both simple and complex views can use indexes existing on the underlying tables. 

E. Complex views can be created only on multiple tables that exist in the same schema. 

F. Complex views can be created on multiple tables that exist in the same or different schemas. 

Answer: B,D,F 

Explanation: 

Creating a Sequence (continued) 

CYCLE | NOCYCLE Specifies whether the sequence continues to generate values after 

reaching its maximum or minimum value 

(NOCYCLE is the default option.) 

CACHE n | NOCACHE Specifies how many values the Oracle server preallocates and 

keeps in memory (By default, the Oracle server caches 20 values.) 


Q133. - (Topic 2) 

Examine the structure of the EMPLOYEES and NEW_EMPLOYEES tables: 


Which DELETE statement is valid? 

A. DELETE FROM employees WHERE employee_id = (SELECT employee_id FROM employees); 

B. DELETE * FROM employees WHERE employee_id = (SELECT employee_id FROM new_employees); 

C. DELETE FROM employees WHERE employee_id IN(SELECT employee_id FROM new_employees WHERE name = 'Carrey'); 

D. DELETE * FROM employees WHERE employee_id IN (SELECT employee_id FROM new_employees WHERE last_name = 'Carrey'); 

Answer: C 

Explanation: 

The correct syntax for DELETE statement 

DELETE [ FROM ] table 

[ WHERE condition ]; 

Incorrect Answers : 

A. '=' is use in the statement and sub query will return more than one row. 

Error Ora-01427: single-row sub query returns more than one row. 

B. Incorrect DELETE statement 

D. Incorrect DELETE statement 

Refer: Introduction to Oracle9i: SQL, Oracle University Student Guide, Manipulating Data, 

p. 8-19 


Q134. - (Topic 1) 

View the Exhibits and examine the structures of the PRODUCTS SALES and CUSTOMERS tables. 


You need to generate a report that gives details of the customer's last name, name of the product, and the quantity sold for all customers in Tokyo'. Which two queries give the required result? (Choose two.) 

A. 

SELECT c.cust_last_name,p.prod_name, s.quantity_sold FROM sales s JOIN products p 

USING(prod_id) 

JOIN customers c 

USING(cust_id) 

WHERE c.cust_city='Tokyo' 

B. 

SELECT c.cust_last_name, p.prod_name, s.quantity_sold 

FROM products p JOIN sales s JOIN customers c 

ON(p.prod_id=s.prod_id) 

ON(s.cust_id=c.cust_id) 

WHERE c.cust_city='Tokyo' 

C. 

SELECT c.cust_last_name, p.prod_name, s.quantity_sold 

FROM products p JOIN sales s 

ON(p.prod_id=s.prod_id) 

JOIN customers c 

ON(s.cust_id=c.cust_id) 

AND c.cust_city='Tokyo' 

D. 

SELECT c.cust_id,c.cust_last_name,p.prod_id, p.prod_name, s.quantity_sold FROM 

products p JOIN sales s 

USING(prod_id) 

JOIN customers c 

USING(cust_id) 

WHERE c.cust_city='Tokyo' 

Answer: A,C 


Q135. - (Topic 2) 

Examine the structure of the SHIPMENTS table: 


You want to generate a report that displays the PO_ID and the penalty amount to be paid if 

the 

SHIPMENT_DATE is later than one month from the PO_DATE. The penalty is $20 per day. 

Evaluate the following two queries: 


Which statement is true regarding the above commands? 

A. Both execute successfully and give correct results. 

B. Only the first query executes successfully but gives a wrong result. 

C. Only the first query executes successfully and gives the correct result. 

D. Only the second query executes successfully but gives a wrong result. 

E. Only the second query executes successfully and gives the correct result. 

Answer: C 

Explanation: 

The MONTHS_BETWEEN(date 1, date 2) function returns the number of months between two dates: months_between('01-FEB-2008','01-JAN-2008') = 1 The DECODE Function Although its name sounds mysterious, this function is straightforward. The DECODE function implements if then-else conditional logic by testing its first two terms for equality and returns the third if they are equal and optionally returns another term if they are not. DECODE Function Facilitates conditional inquiries by doing the work of a CASE expression or an IF-THENELSE statement: DECODE(col|expression, search1, result1 [, search2, result2,...,] [, default]) DECODE Function The DECODE function decodes an expression in a way similar to the IF-THEN-ELSE logic that is used in various languages. The DECODE function decodes expression after comparing it to each search value. If the expression is the same as search, result is returned. 

If the default value is omitted, a null value is returned where a search value does not match any of the result values. 


certifyforall.com

Most recent free dumps for 1z0-051:

Q136. - (Topic 2) 

View the Exhibit and examine the data in the EMPLOYEES table: 

You want to display all the employee names and their corresponding manager names. 

Evaluate the following query: 

SQL> SELECT e.employee_name "EMP NAME", m.employee_name "MGR NAME" 

FROM employees e ______________ employees m 

ON e.manager_id = m.employee_id; 

Which JOIN option can be used in the blank in the above query to get the required output? 

Exhibit: 

A. only inner JOIN 

B. only FULL OUTER JOIN 

C. only LEFT OUTER JOIN 

D. only RIGHT OUTER JOIN 

Answer: C 


Q137. - (Topic 1) 

Which are /SQL*Plus commands? (Choose all that apply.) 

A. INSERT 

B. UPDATE C. SELECT 

D. DESCRIBE 

E. DELETE 

F. RENAME 

Answer: D 

Explanation: 

Describe is a valid iSQL*Plus/ SQL*Plus command. 

INSERT, UPDATE & DELETE are SQL DML Statements. A SELECT is an ANSI Standard 

SQL Statement not an iSQL*Plus Statement. 

RENAME is a DDL Statement. 


Q138. - (Topic 2) 

Examine the structure of the TRANSACTIONS table: 

Name Null Type 

TRANS_ID NOT NULL NUMBER(3) 

CUST_NAME VARCHAR2(30) 

TRANS_DATE TIMESTAMP 

TRANS_AMT NUMBER(10,2) 

You want to display the date, time, and transaction amount of transactions that where done before 12 noon. 

The value zero should be displayed for transactions where the transaction amount has not been entered. 

Which query gives the required result? 

A. 

SELECT TO_CHAR(trans_date,'dd-mon-yyyy hh24:mi:ss'), 

TO_CHAR(trans_amt,'$99999999D99') 

FROM transactions 

WHERE TO_NUMBER(TO_DATE(trans_date,'hh24')) < 12 AND 

COALESCE(trans_amt,NULL)<>NULL; 

B. 

SELECT TO_CHAR(trans_date,'dd-mon-yyyy hh24:mi:ss'), 

NVL(TO_CHAR(trans_amt,'$99999999D99'),0) 

FROM transactions 

WHERE TO_CHAR(trans_date,'hh24') < 12; 

C. 

SELECT TO_CHAR(trans_date,'dd-mon-yyyy hh24:mi:ss'), 

COALESCE(TO_NUMBER(trans_amt,'$99999999.99'),0) 

FROM transactions 

WHERE TO_DATE(trans_date,'hh24') < 12; 

D. 

SELECT TO_DATE (trans_date,'dd-mon-yyyy hh24:mi:ss'), 

NVL2(trans_amt,TO_NUMBER(trans_amt,'$99999999.99'), 0) 

FROM transactions 

WHERE TO_DATE(trans_date,'hh24') < 12; 

Answer: B 


Q139. - (Topic 2) 

View the Exhibit and examine the structure of the PRODUCTS, SALES, and SALE_SUMMARY tables. 

SALE_VW is a view created using the following command: 

SQL>CREATE VIEW sale_vw AS 

SELECT prod_id, SUM(quantity_sold) QTY_SOLD 

FROM sales GROUP BY prod_id; 

You issue the following command to add a row to the SALE_SUMMARY table: 

SQL>INSERT INTO sale_summary 

SELECT prod_id, prod_name, qty_sold FROM sale_vw JOIN products 

USING (prod_id) WHERE prod_id = 16; 

What is the outcome? 

A. It executes successfully. 

B. It gives an error because a complex view cannot be used to add data into the SALE_SUMMARY table. 

C. It gives an error because the column names in the subquery and the SALE_SUMMARY table do not match. 

D. It gives an error because the number of columns to be inserted does not match with the number of columns in the SALE_SUMMARY table. 

Answer: D 


Q140. - (Topic 2) 

Which two statements are true regarding constraints? (Choose two.) 

A. A foreign key cannot contain NULL values. 

B. The column with a UNIQUE constraint can store NULLS . 

C. A constraint is enforced only for an INSERT operation on a table. 

D. You can have more than one column in a table as part of a primary key. 

Answer: B,D