♥♥ 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.exam.com/1Z0-051-dumps.html


We provide real 1z0 051 dumps pdf exam questions and answers braindumps in two formats. Download PDF & Practice Tests. Pass Oracle 1z0 051 dumps pdf Exam quickly & easily. The 1z0 051 dumps PDF type is available for reading and printing. You can print more and practice many times. With the help of our Oracle 1z0 051 dumps pdf dumps pdf and vce product and material, you can easily pass the 1z0 051 pdf exam.

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

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 


Q52. - (Topic 2) 

Which four are attributes of single row functions? (Choose four.) 

A. cannot be nested 

B. manipulate data items 

C. act on each row returned 

D. return one result per row 

E. accept only one argument and return only one value 

F. accept arguments which can be a column or an expression 

Answer: B,C,D,F 

Explanation: 

manipulate data items, act on each row returned, return one result per row, and accept arguments that can be a column or expression. 

Incorrect Answer: Ais not single row attributes Efunctions can accept more than one argument, e.g NVL2 

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


Q53. - (Topic 2) 

Which substitution variable would you use if you want to reuse the variable without prompting the user each time? 

A. & 

B. ACCEPT 

C. PROMPT 

D. && 

Answer:

Explanation: 

To reuse the variable without prompting the user each time you can use && substitution 

variable. 

Incorrect Answers 

A:This substitution variable will prompt the user each time. 

B:ACCEPT is command, not substitution variable. It used to define more accurate or 

specific prompt or when you want more output to display as the values are defined. 

C:PROMPT is part of the ACCEPT command, it is not a variable. 

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

Chapter 4: Sub queries 


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


Q55. - (Topic 1) 

What is true about sequences? 

A. The start value of the sequence is always 1. 

B. A sequence always increments by 1. 

C. The minimum value of an ascending sequence defaults to 1. 

D. The maximum value of descending sequence defaults to 1. 

Answer:


Q56. - (Topic 2) 

Evaluate the following CREATE SEQUENCE statement: 

CREATE SEQUENCE seq1 

START WITH 100 

INCREMENT BY 10 

MAXVALUE 200 

CYCLE 

NOCACHE; 

The SEQ1 sequence has generated numbers up to the maximum limit of 200. You issue the following SQL statement: 

SELECT seq1.nextval FROM dual; 

What is displayed by the SELECT statement? 

A. 1 

B. 10 

C. 100 

D. an error 

Answer:

Explanation: 

But why the answer is not "C" ? Because you didn't specify the MINVALUE for the sequence. If you check the sequence definition that you created it will have the default value of 1, which it reverts to when cycling. If you wanted to keep the minimum value you would need to specify it in the sequence creation. sequence Is the name of the sequence generator INCREMENT BY n Specifies the interval between sequence numbers, where n is an integer (If this clause is omitted, the sequence increments by 1.) START WITH n Specifies the first sequence number to be generated (If this clause is omitted, the sequence starts with 1.) MAXVALUE n Specifies the maximum value the sequence can generate NOMAXVALUE Specifies a maximum value of 10^27 for an ascending sequence and –1 for a descending sequence (This is the default option.) MINVALUE n Specifies the minimum sequence value NOMINVALUE Specifies a minimum value of 1 for an ascending sequence and –(10^26) for a descending sequence (This is the default option.) 

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


Q57. - (Topic 1) 

Which three statements/commands would cause a transaction to end? (Choose three.) 

A. COMMIT 

B. SELECT 

C. CREATE 

D. ROLLBACK 

E. SAVEPOINT 

Answer: A,C,D 


Q58. - (Topic 1) 

The PART_CODE column in the SPARES table contains the following list of values: 

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

A. It produces an error. 

B. It displays all values. 

C. It displays only the values A%_WQ123 and AB_WQ123 . 

D. It displays only the values A%_WQ123 and A%BWQ123 . 

E. It displays only the values A%BWQ123 and AB_WQ123. 

Answer:

Explanation: 

Combining Wildcard Characters 

The % and _ symbols can be used in any combination with literal characters. The example in the slide displays the names of all employees whose last names have the letter “o” as the second character. 

ESCAPE Identifier 

When you need to have an exact match for the actual % and _ characters, use the ESCAPE identifier. This option specifies what the escape character is. If you want to search for strings that contain SA_, you can use the following SQL statement: SELECT employee_id, last_name, job_id FROM employees WHERE job_id LIKE '%SA\\_%' ESCAPE '\\' 


Q59. - (Topic 2) 

Which two statements are true about WHERE and HAVING clauses? (Choose two) 

A. A WHERE clause can be used to restrict both rows and groups. 

B. A WHERE clause can be used to restrict rows only. 

C. A HAVING clause can be used to restrict both rows and groups. 

D. A HAVING clause can be used to restrict groups only. 

E. A WHERE clause CANNOT be used in a query of the query uses a HAVING clause. 

F. A HAVING clause CANNOT be used in sub queries. 

Answer: B,D Explanation: 

B: WHERE clause cannot be use to restrict groups 

WHERE clause cannot be use when there is group functions. 

D: A HAVING clause can only e used to restrict GROUPS. 

Note: HAVING clause to specify which groups are to be displayed and thus further restrict the groups on the basis of aggregate information. The Oracle server performs the following steps when you use the Having clause 

1. 

rows are grouped 

2. 

the group function is applied to the group 

3. 

the group that match the criteria in the Having clause are displayed. 

Incorrect Answers : 

A. Where clause cannot be use to restrict groups 

C. A HAVING clause can only e used to restrict GROUPS. 

E. WHERE clause cannot be use when there is group function, instead HAVING is to be use. 

F. There is no constraint to use HAVING clause in a sub queries. 

Refer: Introduction to Oracle9i: SQL, Oracle University Student Guide, Aggregating Data using Group Functions, p. 5-20 


Q60. - (Topic 1) 

Examine the structure of the STUDENTS table: 

You need to create a report of the 10 students who achieved the highest ranking in the course INT SQL and who completed the course in the year 1999. 

Which SQL statement accomplishes this task? 

A. SELECT student_ id, marks, ROWNUM "Rank" 

FROM students 

WHERE ROWNUM <= 10 

AND finish_date BETWEEN '01-JAN-99' AND '31-DEC-99 

AND course_id = 'INT_SQL' 

ORDER BY marks DESC; 

B. SELECT student_id, marks, ROWID "Rank" 

FROM students 

WHERE ROWID <= 10 

AND finish_date BETWEEN '01-JAN-99' AND '31-DEC-99' 

AND course_id = 'INT_SQL' 

ORDER BY marks; 

C. SELECT student_id, marks, ROWNUM "Rank" 

FROM (SELECT student_id, marks 

FROM students 

WHERE ROWNUM <= 10 

AND finish_date BETWEEN '01-JAN-99' AND '31-DEC-

99' 

AND course_id = 'INT_SQL' 

ORDER BY marks DESC); 

D. SELECT student_id, marks, ROWNUM "Rank” 

FROM (SELECT student_id, marks 

FROM students 

WHERE (finish_date BETWEEN ’01-JAN-99 AND ’31-DEC-99’ 

AND course_id = ‘INT_SQL’ 

ORDER BY marks DESC) 

WHERE ROWNUM <= 10 ; 

E. SELECTstudent id, marks, ROWNUM “Rank” 

FROM(SELECT student_id, marks 

FROM students 

ORDER BY marks) 

WHEREROWNUM <= 10 

ANDfinish date BETWEEN ’01-JAN-99’ AND ’31-DEC-99’ 

ANDcourse_id = ‘INT_SQL’; 

Answer: