Examcollection.com presents the up-to-date exam training materials which will put together for your exam with guaranteed result. You can trust the Oracle Oracle exam on the Oracle exam braindumps. We guarantee you will get yourself a high passing score. You can find the Oracle 1Z0-051 exam questions and also answers are offered in a pair of formats. 1 is printable Pdf type, and the some other is downloadable Test Engine type.

2016 Dec 1z0-051 study guide pdf:

Q161. - (Topic 2) 

Evaluate the following SQL statement: 

SQL> SELECT cust_id, cust_last_name FROM customers WHERE cust_credit_limit IN (select cust_credit_limit FROM customers WHERE cust_city ='Singapore'); 

Which statement is true regarding the above query if one of the values generated by the subquery is NULL? 

A. It produces an error. 

B. It executes but returns no rows. 

C. It generates output for NULL as well as the other values produced by the subquery. 

D. It ignores the NULL value and generates output for the other values produced by the subquery. 

Answer:


Q162. - (Topic 2) 

View the Exhibit and examine the structure of the PRODUCTS tables. 

You want to generate a report that displays the average list price of product categories where the average list price is less than half the maximum in each category. 

Which query would give the correct output? 

A. 

SELECT prod_category,avg(prod_list_price) FROM products GROUP BY prod_category HAVING avg(prod_list_price) < ALL (SELECT max(prod_list_price)/2 FROM products GROUP BY prod_category); 

B. 

SELECT prod_category,avg(prod_list_price) FROM products GROUP BY prod_category HAVING avg(prod_list_price) > ANY (SELECT max(prod_list_price)/2 FROM products GROUP BY prod_category); 

C. 

SELECT prod_category,avg(prod_list_price) FROM products HAVING avg(prod_list_price) < ALL (SELECT max(prod_list_price)/2 FROM products GROUP BY prod_category); 

D. 

SELECT prod_category,avg(prod_list_price) FROM products GROUP BY prod_category HAVING avg(prod_list_price) > ANY (SELECT max(prod_list_price)/2 FROM products); 

Answer:

Explanation: 

Using the ANY Operator in Multiple-Row Subqueries 

The ANY operator (and its synonym, the SOME operator) compares a value to each value 

returned by a subquery. 

<ANY means less than the maximum. 

>ANY means more than the minimum. 

=ANY is equivalent to IN 

Using the ALL Operator in Multiple-Row Subqueries 

The ALL operator compares a value to every value returned by a subquery. 

>ALL means more than the maximum and 

<ALL means less than the minimum. 

The NOT operator can be used with IN, ANY, and ALL operators. 


Q163. - (Topic 1) 

Which three statements are true regarding the data types in Oracle Database 10g/11g? (Choose three.) 

A. The BLOB data type column is used to store binary data in an operating system file 

B. The minimum column width that can be specified for a VARCHAR2 data type column is one 

C. A TIMESTAMP data type column stores only time values with fractional seconds 

D. The value for a CHAR data type column is blank-padded to the maximum defined column width 

E. Only One LONG column can be used per table 

Answer: B,D,E 

Explanation: 

LONG Character data in the database character set, up to 2GB. All the functionality of LONG (and more) is provided by CLOB; LONGs should not be used in a modern database, and if your database has any columns of this type they should be converted to CLOB. There can only be one LONG column in a table. DVARCHAR2 Variable-length character data, from 1 byte to 4KB. The data is stored in the database character set. The VARCHAR2 data type must be qualified with a number indicating the maximum length of the column. If a value is inserted into the column that is less than this, it is not a problem: the value will only take up as much space as it needs. If the value is longer than this maximum, the INSERT will fail with an error. VARCHAR2(size) Variable-length character data (A maximum size must be specified: minimum size is 1; maximum size is 4,000.) BLOB Like CLOB, but binary data that will not undergo character set conversion by Oracle 

Net. 

BFILE A locator pointing to a file stored on the operating system of the database server. 

The size of the files is limited to 4GB. 

TIMESTAMP This is length zero if the column is empty, or up to 11 bytes, depending on 

the precision specified. 

Similar to DATE, but with precision of up to 9 decimal places for the seconds, 6 places by 

default. 


Q164. - (Topic 1) 

Which CREATE TABLE statement is valid? 

A. 

CREATE TABLE ord_details (ord_no NUMBER(2) PRIMARY KEY, item_no NUMBER(3) PRIMARY KEY, ord_date DATE NOT NULL); 

B. 

CREATE TABLE ord_details 

(ord_no NUMBER(2) UNIQUE, NOT NULL, 

item_no NUMBER(3), 

ord_date DATE DEFAULT SYSDATE NOT NULL); 

C. 

CREATE TABLE ord_details 

(ord_no NUMBER(2) , 

item_no NUMBER(3), 

ord_date DATE DEFAULT NOT NULL, 

CONSTRAINT ord_uq UNIQUE (ord_no), 

CONSTRAINT ord_pk PRIMARY KEY (ord_no)); 

D. 

CREATE TABLE ord_details 

(ord_no NUMBER(2), 

item_no NUMBER(3), 

ord_date DATE DEFAULT SYSDATE NOT NULL, 

CONSTRAINT ord_pk PRIMARY KEY (ord_no, item_no)); 

Answer:

Explanation: 

PRIMARY KEY Constraint 

A PRIMARY KEY constraint creates a primary key for the table. Only one primary key can be created for each table. The PRIMARY KEY constraint is a column or a set of columns that uniquely identifies each row in a table. This constraint enforces the uniqueness of the column or column combination and ensures that no column that is part of the primary key can contain a null value. Note: Because uniqueness is part of the primary key constraint definition, the Oracle server enforces the uniqueness by implicitly creating a unique index on the primary key column or columns. 


Q165. - (Topic 1) 

The STUDENT_GRADES table has these columns: 

Which statement finds students who have a grade point average (GPA) greater than 3.0 for the calendar year 2001? 

A. SELECT student_id, gpa 

FROM student_grades 

WHERE semester_end BETWEEN ’01-JAN-2001’ AND ’31-DEC-2001’ 

OR gpa > 3.; 

B. SELECT student_id, gpa 

FROM student_grades 

WHERE semester_end BETWEEN ’01-JAN-2001’ AND ’31-DEC-2001’ 

AND gpa gt 3.0; 

C. SELECT student_id, gpa 

FROM student_grades 

WHERE semester_end BETWEEN ’01-JAN-2001’ AND ’31-DEC-2001’ 

AND gpa > 3.0; 

D. SELECT student_id, gpa 

FROM student_grades 

WHERE semester_end BETWEEN ’01-JAN-2001’ AND ’31-DEC-2001’ 

OR gpa > 3.0; 

E. SELECT student_id, gpa 

FROM student_grades 

WHERE semester_end > ’01-JAN-2001’ OR semester_end < ’31-DEC-2001’ 

AND gpa >= 3.0; 

Answer:


Refresh oracle 1z0-051 dumps free:

Q166. - (Topic 2) 

Top N analysis requires _____ and _____. (Choose two.) 

A. the use of rowid 

B. a GROUP BY clause 

C. an ORDER BY clause 

D. only an inline view 

E. an inline view and an outer query 

Answer: C,E 

Explanation: 

The correct statement for Top-N Analysis SELECT [coloumn_list], ROWNUM FROM (SELECT [coloumn_list] 

FROM table 

ORDER BY Top-N_coloumn) 

WHERE ROWNUM <= N; 

Incorrect Answer: 

AROWID is not require 

BGROUP BY clause is not require 

DMust have inline view and outer query. 

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


Q167. - (Topic 1) 

Which statement is true regarding the UNION operator? 

A. The number of columns selected in all SELECT statements need to be the same 

B. Names of all columns must be identical across all SELECT statements 

C. By default, the output is not sorted 

D. NULL values are not ignored during duplicate checking 

Answer:

Explanation: 

The SQL UNION query allows you to combine the result sets of two or more SQL SELECT statements. It removes duplicate rows between the various SELECT statements. Each SQL SELECT statement within the UNION query must have the same number of fields in the result sets with similar data types. 


Q168. - (Topic 2) 

Examine the structure of the EMP_DEPT_VU view: 

Which SQL statement produces an error? 

A. SELECT * 

FROM emp_dept_vu; 

B. SELECT department_id, SUM(salary) 

FROM emp_dept_vu 

GROUP BY department_id; 

C. SELECT department_id, job_id, AVG(salary) 

FROM emp_dept_vu 

GROUP BY department_id, job_id; 

D. SELECT job_id, SUM(salary) 

FROM emp_dept_vu 

WHERE department_id IN (10,20) 

GROUP BY job_id 

HAVING SUM(salary) > 20000; 

E. None of the statements produce an error; all are valid. 

Answer:

Explanation: Explanation: None of the statements produce an error. Incorrect Answer: AStatement will not cause error BStatement will not cause error CStatement will not cause error DStatement will not cause error 


Q169. - (Topic 1) 

Examine the structure of the MARKS table: 

Exhibit: 

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

A. SELECT student_name,subject1 

FROM marks 

WHERE subject1 > AVG(subject1); 

B. SELECT student_name,SUM(subject1) 

FROM marks 

WHERE student_name LIKE 'R%' 

C. SELECT SUM(subject1+subject2+subject3) 

FROM marks 

WHERE student_name IS NULL; 

D. SELECT SUM(DISTINCT NVL(subject1,0)), MAX(subject1) 

FROM marks 

WHERE subject1 > subject2; 

Answer: C,D 


Q170. - (Topic 1) 

Which two are true about aggregate functions? (Choose two.) 

A. You can use aggregate functions in any clause of a SELECT statement. 

B. You can use aggregate functions only in the column list of the select clause and in the WHERE clause of a SELECT statement. 

C. You can mix single row columns with aggregate functions in the column list of a SELECT statement by grouping on the single row columns. 

D. You can pass column names, expressions, constants, or functions as parameter to an aggregate function. 

E. You can use aggregate functions on a table, only by grouping the whole table as one single group. 

F. You cannot group the rows of a table by more than one column while using aggregate functions. 

Answer: A,D