The particular Oracle 1Z0-051 official certifications will offer the actual authorized owner a distinct appeal. The particular 1Z0-051 authorized skilled provides testified to your value of this particular official certifications. The particular 1Z0-051 official certifications usually are useful field established credentials that your verified volume of awareness and capability with the authorized owner. Just about every advanced level in this 1Z0-051 Oracle gives the revolutionary standard regarding benchmark knowledge and capability with the authorized owner. It will help the actual choice to achieve the great chances and great pay back on the That group.

2016 Sep oracle 1z0-051 dumps free download:

Q101. - (Topic 1) 

Which object privileges can be granted on a view? 

A. none 

B. DELETE, INSERT,SELECT 

C. ALTER, DELETE, INSERT, SELECT 

D. DELETE, INSERT, SELECT, UPDATE 

Answer: D 

Explanation: Object privilege on VIEW is DELETE, INSERT, REFERENCES, SELECT and UPDATE. 

Incorrect Answer: AObject privilege on VIEW is DELETE, INSERT, REFERENCES, SELECT and UPDATE BObject privilege on VIEW is DELETE, INSERT, REFERENCES, SELECT and UPDATE CObject privilege on VIEW is DELETE, INSERT, REFERENCES, SELECT and UPDATE 

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


Q102. - (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: A 

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 


Q103. - (Topic 2) 

View the Exhibit and examine the data in the PROMOTIONS table. 

PROMO_BEGIN_DATE is stored in the default date format, dd-mon-rr. 

You need to produce a report that provides the name, cost, and start date of all promos in the POST category that were launched before January 1, 2000. 

Which SQL statement would you use? 

A. SELECT promo_name, promo_cost, promo_begin_date FROM promotions WHERE promo_category = 'post' AND promo_begin_date < '01-01-00' 

B. SELECT promo_name, promo_cost, promo_begin_date FROM promotions WHERE promo_cost LIKE 'post%' AND promo_begin_date < '01-01-2000' 

C. SELECT promo_name, promo_cost, promo_begin_date FROM promotions WHERE promo_category LIKE 'P%' AND promo_begin_date < '1-JANUARY-00' 

D. SELECT promo_name, promo_cost, promo_begin_date FROM promotions WHERE promo_category LIKE '%post%' AND promo_begin_date < '1-JAN-00' 

Answer: D 


Q104. - (Topic 1) 

Which is the valid CREATE [TABLE statement? 

A. CREATE TABLE emp9$# (emp_no NUMBER(4)); 

B. CREATE TABLE 9emp$# (emp_no NUMBER(4)); 

C. CREATE TABLE emp*123 (emp_no NUMBER(4)); 

D. CREATE TABLE emp9$# (emp_no NUMBER(4). date DATE); 

Answer: A 

Explanation: 

Schema Object Naming Rules 

Every database object has a name. In a SQL statement, you represent the name of an 

object with a quoted identifier or a nonquoted identifier. 

A quoted identifier begins and ends with double quotation marks ("). If you name a schema 

object using a quoted identifier, then you must use the double quotation marks whenever 

you refer to that object. 

A nonquoted identifier is not surrounded by any punctuation. 

The following list of rules applies to both quoted and nonquoted identifiers unless otherwise 

indicated: 

Names must be from 1 to 30 bytes long with these exceptions: 

Names of databases are limited to 8 bytes. 

Names of database links can be as long as 128 bytes. 

If an identifier includes multiple parts separated by periods, then each attribute can be up to 

30 bytes long. 

Each period separator, as well as any surrounding double quotation marks, counts as one 

byte. For example, suppose you identify a column like this: 

"schema"."table"."column" 

Nonquoted identifiers cannot be Oracle Database reserved words (ANSWER D). Quoted identifiers can be reserved words, although this is not recommended. Depending on the Oracle product you plan to use to access a database object, names might be further restricted by other product-specific reserved words. The Oracle SQL language contains other words that have special meanings. These words include datatypes, schema names, function names, the dummy system table DUAL, and keywords (the uppercase words in SQL statements, such as DIMENSION, SEGMENT, ALLOCATE, DISABLE, and so forth). These words are not reserved. However, Oracle uses them internally in specific ways. Therefore, if you use these words as names for objects and object parts, then your SQL statements may be more difficult to read and may lead to unpredictable results. In particular, do not use words beginning with SYS_ as schema object names, and do not use the names of SQL built-in functions for the names of schema objects or user-defined functions. You should use ASCII characters in database names, global database names, and database link names, because ASCII characters provide optimal compatibility across different platforms and operating systems. Nonquoted identifiers must begin with an alphabetic character (ANSWER B - begins with 9) from your database character set. Quoted identifiers can begin with any character. Nonquoted identifiers can contain only alphanumeric characters from your database character set and the underscore (_), dollar sign ($), and pound sign (#). Database links can also contain periods (.) and "at" signs (@). Oracle strongly discourages you from using $ and # in nonquoted identifiers. Quoted identifiers can contain any characters and punctuations marks as well as spaces. However, neither quoted nor nonquoted identifiers can contain double quotation marks or the null character (\0). Within a namespace, no two objects can have the same name. Nonquoted identifiers are not case sensitive. Oracle interprets them as uppercase. Quoted identifiers are case sensitive. By enclosing names in double quotation marks, you can give the following names to different objects in the same namespace: employees "employees" "Employees" "EMPLOYEES" 

Note that Oracle interprets the following names the same, so they cannot be used for different objects in the same namespace: employees EMPLOYEES "EMPLOYEES" Columns in the same table or view cannot have the same name. However, columns in different tables or views can have the same name. Procedures or functions contained in the same package can have the same name, if their arguments are not of the same number and datatypes. Creating multiple procedures or functions with the same name in the same package with different arguments is called overloading the procedure or function. 


Q105. - (Topic 1) 

View the Exhibit and evaluate structures of the SALES, PRODUCTS, and COSTS tables. 


Evaluate the following SQL statements: 


Which statement is true regarding the above compound query? 

A. It shows products that have a cost recorded irrespective of sales 

B. It shows products that were sold and have a cost recorded 

C. It shows products that were sold but have no cost recorded 

D. It reduces an error 

Answer: C 


certifyforall.com

Refresh oracle exam 1z0-051:

Q106. - (Topic 2) 

EMPLOYEES and DEPARTMENTS data: EMPLOYEES 


DEPARTMENTS 


On the EMPLOYEES table, EMPLOYEE_ID is the primary key. MGR_ID is the ID managers and refers to the EMPLOYEE_ID. 

On the DEPARTMENTS table DEPARTMENT_ID is the primary key. 

Evaluate this UPDATE statement. 

UPDATE employees SET mgr_id = (SELECT mgr_id FROMemployees WHERE dept_id= (SELECT department_id FROM departments WHERE department_name = 'Administration')), Salary = (SELECT salary 

FROM employees 

WHERE emp_name = 'Smith') 

WHERE job_id = 'IT_ADMIN' 

What happens when the statement is executed? 

A. The statement executes successfully, leaves the manager ID as the existing value, and changes the salary to 4000 for the employees with ID 103 and 105. 

B. The statement executes successfully, changes the manager ID to NULL, and changes the salary to 4000 for the employees with ID 103 and 105. 

C. The statement executes successfully, changes the manager ID to NULL, and changes the salary to 3000 for the employees with ID 103 and 105. 

D. The statement fails because there is more than one row matching the employee name Smith. 

E. The statement fails because there is more than one row matching the IT_ADMIN job ID in the EMPLOYEES table. 

F. The statement fails because there is no 'Administration' department in the DEPARTMENTS table. 

Answer: D 

Explanation: 

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

Employees table has 2 row matching the employee name Smith. 

The update statement will fail. 

Incorrect Answers : 

A. The Update statement will fail no update was done. 

B. The update statement will fail no update was done. 

C. The update statement will fail no update was done. 

E. The update statement will fail but not due to job_it='IT_ADMIN' 

F. The update statement will fail but not due to department_id='Administration' 

Refer: Introduction to Oracle9i: SQL, Oracle University Student Guide, Sub queries, p. 6-12 


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


Q108. - (Topic 1) 

Evaluate the following SQL query; 


What would be the outcome? 

A. 200 

B. 16 

C. 160 

D. 150 

E. 100 

Answer: C 

Explanation: 

Function Purpose ROUND(column|expression, n) Rounds the column, expression, or value to n decimal places or, if n is omitted, no decimal places (If n is negative, numbers to the left of decimal point are rounded.) TRUNC(column|expression, n) Truncates the column, expression, or value to n decimal places or, if n is omitted, n defaults to zero 


Q109. - (Topic 1) 

View the Exhibit and examine the structure of the CUSTOMERS table .Which statement would display the highest credit limit available in each income level in each city in the CUSTOMERS table? 


A. SELECT cust_city, cust_income_level, MAX(cust_credit_limit ) FROM customers GROUP BY cust_city, cust_income_level, cust_credit_limit; 

B. SELECT cust_city, cust_income_level, MAX(cust_credit_limit) FROM customers GROUP BY cust_city, cust_income_level; 

C. SELECT cust_city, cust_income_level, MAX(cust_credit_limit) FROM customers GROUP BY cust_credit_limit, cust_income_level, cust_city ; 

D. SELECT cust_city, cust_income_level, MAX(cust_credit_limit) FROM customers GROUP BY cust_city, cust_income_level, MAX(cust_credit_limit); 

Answer: B 


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