100% Guarantee of 1Z0-051 free draindumps materials and testing software for Oracle certification for customers, Real Success Guaranteed with Updated 1Z0-051 pdf dumps vce Materials. 100% PASS Oracle Database: SQL Fundamentals I exam Today!

2016 May 1Z0-051 Study Guide Questions:

Q11. - (Topic 2) 

Which SQL statements would display the value 1890.55 as $1,890.55? (Choose three.) 

A. 

SELECT TO_CHAR(1890.55,'$0G000D00') FROM DUAL; 

B. 

SELECT TO_CHAR(1890.55,'$9,999V99') FROM DUAL; 

C. 

SELECT TO_CHAR(1890.55,'$99,999D99') FROM DUAL; 

D. 

SELECT TO_CHAR(1890.55,'$99G999D00') FROM DUAL; 

E. SELECT TO_CHAR(1890.55,'$99G999D99') FROM DUAL; 

Answer: A,D,E 


Q12. - (Topic 2) 

Which statements are true regarding the WHERE and HAVING clauses in a SELECT statement? 

(Choose all that apply.) 

A. The HAVING clause can be used with aggregate functions in subqueries. 

B. The WHERE clause can be used to exclude rows after dividing them into groups. 

C. The WHERE clause can be used to exclude rows before dividing them into groups. 

D. The aggregate functions and columns used in the HAVING clause must be specified in the SELECT list of the query. 

E. The WHERE and HAVING clauses can be used in the same statement only if they are applied to different columns in the table. 

Answer: A,C 


Q13. - (Topic 1) 

Evaluate this SQL statement: 

SELECT e.emp_name, d.dept_name 

FROM employees e 

JOIN departments d 

USING (department_id) 

WHERE d.department_id NOT IN (10,40) 

ORDER BY dept_name; 

The statement fails when executed. Which change fixes the error? 

A. remove the ORDER BY clause 

B. remove the table alias prefix from the WHERE clause 

C. remove the table alias from the SELECT clause 

D. prefix the column in the USING clause with the table alias 

E. prefix the column in the ORDER BY clause with the table alias 

F. replace the condition 

”d.department_id NOT IN (10,40)” 

in the WHERE clause with 

”d.department_id <> 10 AND d.department_id <> 40” 

Answer: B 


1Z0-051  free draindumps

Up to date 1z0-051 dumps free download pdf:

Q14. - (Topic 2) 

Examine the structure proposed for the TRANSACTIONS table: 


Which two statements are true regarding the storage of data in the above table structure? (Choose two.) 

A. The TRANS_DATE column would allow storage of dates only in the dd-mon-yyyy format. 

B. The CUST_CREDIT_VALUE column would allow storage of positive and negative integers. 

C. The TRANS_VALIDITY column would allow storage of a time interval in days, hours, minutes, and seconds. 

D. The CUST_STATUS column would allow storage of data up to the maximum VARCHAR2 size of 4,000 characters. 

Answer: B,D 

Explanation: 

B: The NUMBER datatype stores fixed and floating-point numbers. Numbers of virtually 

any magnitude can be stored and are guaranteed portable among different systems 

operating Oracle, up to 38 digits of precision. 

The following numbers can be stored in a NUMBER column: 

Positive numbers in the range 1 x 10-130 to 9.99...9 x 10125 with up to 38 significant digits Negative numbers from -1 x 10-130 to 9.99...99 x 10125 with up to 38 significant digits Zero Positive and negative infinity (generated only by importing from an Oracle Version 5 database) 

D: The VARCHAR2 datatype stores variable-length character strings. When you create a table with a VARCHAR2 column, you specify a maximum string length (in bytes or characters) between 1 and 4000 bytes for the VARCHAR2 column. An interval literal specifies a period of time, and Oracle supports two types of interval literals: YEAR_TO_MONTH and DAY TO SECOND. For DAY TO SECOND, you can specify these differences in terms in terms of days, hours, minutes, and seconds. DAY TO SECOND contains a leading field and may contain an optional trailing field. If trailing field is specified it must be less significant than the leading field. For example, INTERVAL MINUTE TO DAY is not valid. 

A DAY TO MINUTE interval considers an interval of days to the nearest minute. Reference: Oracle Database Concepts 10g, Native Datatypes 


Q15. - (Topic 1) 

See the exhibit and examine the structure of the CUSTOMERS and GRADES tables: 


You need to display names and grades of customers who have the highest credit limit. 

Which two SQL statements would accomplish the task? (Choose two.) 

A. 

SELECT custname, grade 

FROM customers, grades 

WHERE (SELECT MAX(cust_credit_limit) 

FROM customers) BETWEEN startval and endval; 

B. 

SELECT custname, grade FROM customers, grades WHERE (SELECT MAX(cust_credit_limit) FROM customers) BETWEEN startval and endval AND cust_credit_limit BETWEEN startval AND endval; 

C. 

SELECT custname, grade 

FROM customers, grades 

WHERE cust_credit_limit = (SELECT MAX(cust_credit_limit) 

FROM customers) 

AND cust_credit_limit BETWEEN startval AND endval; 

D. 

SELECT custname, grade 

FROM customers , grades 

WHERE cust_credit_limit IN (SELECT MAX(cust_credit_limit) 

FROM customers) 

AND MAX(cust_credit_limit) BETWEEN startval AND endval; 

Answer: B,C 


Q16. - (Topic 2) 

The PRODUCTS table has these columns: 

PRODUCT_ID NUMBER(4) 

PRODUCT_NAME VARCHAR2(45) 

PRICE NUMBER(8,2) 

Evaluate this SQL statement: 

SELECT * 

FROM PRODUCTS 

ORDER BY price, product_name; 

What is true about the SQL statement? 

A. The results are not sorted. 

B. The results are sorted numerically. 

C. The results are sorted alphabetically. 

D. The results are sorted numerically and then alphabetically. 

Answer: D 

Explanation: 

the result is sort by price which is numeric and follow by product_name which is alphabetically. 

Incorrect Answer: Athe results are sorted Bthe results are sorted with alphabetically as well Cthe results are sorted with numerically as well 

Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 2-2 


2passeasy.com

Certified 1z0-051 latest dumps free download:

Q17. - (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. 


Q18. - (Topic 1) 

See the structure of the PROGRAMS table: 


Which two SQL statements would execute successfully? (Choose two.) 

A. SELECT NVL(ADD_MONTHS(END_DATE,1),SYSDATE) FROM programs; 

B. SELECT TO_DATE(NVL(SYSDATE-END_DATE,SYSDATE)) FROM programs; 

C. SELECT NVL(MONTHS_BETWEEN(start_date,end_date),'Ongoing') FROM programs; 

D. SELECT NVL(TO_CHAR(MONTHS_BETWEEN(start_date,end_date)),'Ongoing') FROM programs; 

Answer: A,D 

Explanation: 

NVL Function 

Converts a null value to an actual value: 

Data types that can be used are date, character, and number. 

Data types must match: 

– 

NVL(commission_pct,0) 

– 

NVL(hire_date,'01-JAN-97') 

– 

NVL(job_id,'No Job Yet') 

MONTHS_BETWEEN(date1, date2): Finds the number of months between date1 and date2 The result can be positive or negative. If date1 is later than date2, the result is positive; if date1 is earlier than date2, the result is negative. The noninteger part of the result represents a portion of the month. MONTHS_BETWEEN returns a numeric value. - answer C NVL has different datatypes -numeric and strings, which is not possible! 

The data types of the original and if null parameters must always be compatible. They must either be of the same type, or it must be possible to implicitly convert if null to the type of the original parameter. The NVL function returns a value with the same data type as the original parameter. 


Q19. - (Topic 1) 

What is true regarding sub queries? 

A. The inner query always sorts the results of the outer query 

B. The outer query always sorts the results of the inner query 

C. The outer query must return a value to the outer query 

D. The inner query returns a value to the outer query 

E. The inner query must always return a value or the outer query will give an error 

Answer: D 

Explanation: The inner query returns a value to the outer query. If the inner query does not return a value, the outer query does not return a result 


Q20. - (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