It is more faster and easier to pass the Oracle 1z0 051 latest dumps free download pdf exam by using Download Oracle Oracle Database: SQL Fundamentals I questuins and answers. Immediate access to the Far out 1z0 051 practice test Exam and find the same core area 1z0 051 dumps pdf questions with professionally verified answers, then PASS your exam with a high score now.


♥♥ 2017 NEW RECOMMEND ♥♥

Free VCE & PDF File for Oracle 1Z0-051 Real Exam (Full Version!)

★ Pass on Your First TRY ★ 100% Money Back Guarantee ★ Realistic Practice Exam Questions

Free Instant Download NEW 1Z0-051 Exam Dumps (PDF & VCE):
Available on: http://www.surepassexam.com/1Z0-051-exam-dumps.html

Q1. - (Topic 2) 

You need to produce a report for mailing labels for all customers. The mailing label must have only the customer name and address. The CUSTOMERS table has these columns: 

CUST_IDNUMBER(4)NOT NULL CUST_NAMEVARCHAR2(100)NOT NULL CUST_ADDRESSVARCHAR2(150) 

CUST_PHONEVARCHAR2(20) 

Which SELECT statement accomplishes this task? 

A. SELECT * FROM customers 

B. SELECT name, address FROM customers; 

C. SELECT id, name, address, phone FROM customers; 

D. SELECT cust_name, cust_address FROM customers; 

E. SELECT cust_id, cust_name, cust_address, cust_phone FROM customers; 

Answer:

Explanation: 

This answer provides correct list of columns for the output. 

Incorrect Answers 

A:This answer does not provide correct list of columns for the output. It is not required to 

show all columns of the table. Symbol “*” is used in the SELECT command to substitute a 

list of all columns of the table. 

B:This answer does not provide correct list of columns for the output. There are not NAME 

and ADDRESS columns in the CUSTOMERS table. 

C:This answer does not provide correct list of columns for the output. There are not ID, 

NAME, ADDRESS or PHONE columns in the CUSTOMERS table. 

E:This answer does not provide correct list of columns for the output. It is not required to 

show all columns of the table. 

OCP Introduction to Oracle 9i: SQL Exam Guide, Jason Couchman, p. 20-24 

Chapter 1: Overview of Oracle Databases 


Q2. - (Topic 2) 

View the Exhibits and examine the structures of the CUSTOMERS, SALES, and COUNTRIES tables. 

You need to generate a report that shows all country names, with corresponding customers (if any) and sales details (if any), for all customers. 

Which FROM clause gives the required result? 

A. FROM sales JOIN customers USING (cust_id) FULL OUTER JOIN countries USING (country_id); 

B. FROM sales JOIN customers USING (cust_id) RIGHT OUTER JOIN countries USING (country_id); 

C. FROM customers LEFT OUTER JOIN sales USING (cust_id) RIGHT OUTER JOIN countries USING (country_id); 

D. FROM customers LEFT OUTER JOIN sales USING (cust_id) LEFT OUTER JOIN countries USING (country_id); 

Answer:


Q3. - (Topic 2) 

You want to display the date for the first Monday of the next month and issue the following command: 

SQL>SELECT TO_CHAR(NEXT_DAY(LAST_DAY(SYSDATE),'MON'), 'dd "is the first Monday for"fmmonth rrrr') FROM DUAL; 

What is the outcome? 

A. It executes successfully and returns the correct result. 

B. It executes successfully but does not return the correct result. 

C. It generates an error because TO_CHAR should be replaced with TO_DATE. 

D. It generates an error because rrrr should be replaced by rr in the format string. 

E. It generates an error because fm and double quotation marks should not be used in the format string. 

Answer:

Explanation: 

NEXT_DAY(date, 'char'): Finds the date of the next specified day of the week ('char') following date. The value of char may be a number representing a day or a character string. 

LAST_DAY(date): Finds the date of the last day of the month that contains date The second innermost function is evaluated next. TO_CHAR('28-OCT-2009', 'fmMonth') converts the given date based on the Month format mask and returns the character string October. The fm modifier trims trailing blank spaces from the name of the month. 


Q4. - (Topic 1) 

You need to display the first names of all customers from the CUSTOMERS table that contain the character 'e' and have the character 'a' in the second last position. 

Which query would give the required output? 

A. 

SELECT cust_first_name FROM customers WHERE INSTR(cust_first_name, 'e')<>0 AND SUBSTR(cust_first_name, -2, 1)='a' 

B. 

SELECT cust_first_name FROM customers WHERE INSTR(cust_first_name, 'e')<>'' AND SUBSTR(cust_first_name, -2, 1)='a' 

C. 

SELECT cust_first_name FROM customers WHERE INSTR(cust_first_name, 'e')IS NOT NULL AND SUBSTR(cust_first_name, 1,-2)='a' 

D. 

SELECT cust_first_name FROM customers WHERE INSTR(cust_first_name, 'e')<>0 AND SUBSTR(cust_first_name, LENGTH(cust_first_name),-2)='a' 

Answer:

Explanation: 

The SUBSTR(string, start position, number of characters) function accepts three 

parameters and returns a string consisting of the number of characters extracted from the 

source string, beginning at the specified start position: 

substr('http://www.domain.com',12,6) = domain 

The position at which the first character of the returned string begins. 

When position is 0 (zero), then it is treated as 1. 

When position is positive, then the function counts from the beginning of string to find the 

first character. 

When position is negative, then the function counts backward from the end of string. 

substring_length 

The length of the returned string. SUBSTR calculates lengths using characters as defined 

by the input character set. SUBSTRB uses bytes instead of characters. SUBSTRC uses 

Unicode complete characters. 

SUBSTR2 uses UCS2 code points. SUBSTR4 uses UCS4 code points. 

When you do not specify a value for this argument, then the function 

The INSTR(source string, search item, [start position],[nth occurrence of search item]) 

function returns a number that represents the position in the source string, beginning from 

the given start position, where the nth occurrence of the search item begins: 

instr('http://www.domain.com','.',1,2) = 18 


Q5. - (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:

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 


Q6. - (Topic 2) 

The CUSTOMERS table has these columns: 

The CUSTOMER_ID column is the primary key for the table. 

You need to determine how dispersed your customer base is. 

Which expression finds the number of different countries represented in the CUSTOMERS table? 

A. COUNT(UPPER(country_address)) 

B. COUNT(DIFF(UPPER(country_address))) 

C. COUNT(UNIQUE(UPPER(country_address))) 

D. COUNT DISTINCT UPPER(country_address) 

E. COUNT(DISTINCT (UPPER(country_address))) 

Answer:


Q7. - (Topic 2) 

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

A. A foreign key cannot contain NULL values. 

B. A column with the UNIQUE constraint can contain NULL values. 

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

D. A constraint can be disabled even if the constraint column contains data. 

E. All constraints can be defined at the column level as well as the table level. 

Answer: B,D 

Explanation: 

Including Constraints 

Constraints enforce rules at the table level. 

Constraints prevent the deletion of a table if there are dependencies. 

The following constraint types are valid: 

– 

NOT NULL 

– 

UNIQUE 

– 

PRIMARY KEY 

– 

FOREIGN KEY 

– 

CHECK 


Q8. - (Topic 2) 

In which two cases would you use an outer join? (Choose two.) 

A. The tables being joined have NOT NULL columns. 

B. The tables being joined have only matched data. 

C. The columns being joined have NULL values. 

D. The tables being joined have only unmatched data. 

E. The tables being joined have both matched and unmatched data. 

F. Only when the tables have a primary key/foreign key relationship. 

Answer: C,E 

Explanation: 

You use an outer join to also see rows that do not meet the join condition. 

Incorrect Answer: Ameet a join condition Bmeet a join condition Dmeet non join condition only Fdoes not take into consideration of primary key and foreign key relationship 

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


Q9. - (Topic 1) 

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

A. 

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

B. 

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

C. 

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

D. 

SELECT TO_CHAR(1890.55,'$99G999D99') 

FROM DUAL; 

E. 

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

Answer: A,C,D 


Q10. - (Topic 1) 

View the Exhibit and examine the structure of the CUSTOMERS table. Evaluate the following SQL statement: 

Which statement is true regarding the outcome of the above query? 

A. It executes successfully. 

B. It returns an error because the BETWEEN operator cannot be used in the HAVING clause. 

C. It returns an error because WHERE and HAVING clauses cannot be used in the same SELECT statement. 

D. It returns an error because WHERE and HAVING clauses cannot be used to apply conditions on the same column. 

Answer: