Oracle certification will be the worlds the majority of well-respected international certification. So 1Z0-051 is very necessary for those people who are stressed to enter the particular IT business. But its very hard for those who are hectic working or studying to pass through the particular Oracle Database: SQL Fundamentals I test. Examcollection Oracle 1Z0-051 study manuals will allow you to saving a large amount of time,power and also source inside the 1Z0-051 test. You can handle this 1Z0-051 test efficiently simply by the 1Z0-051 pdf file and also test engine. Were able to keep the product promptly and enable visitors to understand Oracle understanding effortlessly. You can even download the particular Oracle 1Z0-051 pdf file test version totally free.

2016 Jun exam 1z0-051 pdf:

Q51. - (Topic 1) 

You issue the following command to drop the PRODUCTS table: 

SQL>DROP TABLE products; 

What is the implication of this command? (Choose all that apply.) 

A. All data in the table are deleted but the table structure will remain 

B. All data along with the table structure is deleted 

C. All views and synonyms will remain but they are invalidated 

D. The pending transaction in the session is committed 

E. All indexes on the table will remain but they are invalidated 

Answer: B,C,D 


Q52. - (Topic 1) 

Which arithmetic operations can be performed on a column by using a SQL function that is built into Oracle database? (Choose three.) 

A. addition 

B. subtraction 

C. raising to a power 

D. finding the quotient 

E. finding the lowest value 

Answer: A,C,E 


Q53. - (Topic 2) 

The EMPLOYEES table contains these columns: 

EMPLOYEE_ID NUMBER(4) 

ENAME VARCHAR2 (25) 

JOB_ID VARCHAR2(10) 

Which SQL statement will return the ENAME, length of the ENAME, and the numeric position of the letter "a" in the ENAME column, for those employees whose ENAME ends with a the letter "n"? 

A. SELECT ENAME, LENGTH(ENAME), INSTR(ENAME, 'a') FROM EMPLOYEES WHERE SUBSTR(ENAME, -1, 1) = 'n' 

B. SELECT ENAME, LENGTH(ENAME), INSTR(ENAME, ,-1,1) FROM EMPLOYEES WHERE SUBSTR(ENAME, -1, 1) = 'n' 

C. SELECT ENAME, LENGTH(ENAME), SUBSTR(ENAME, -1,1) FROM EMPLOYEES WHERE INSTR(ENAME, 1, 1) = 'n' 

D. SELECT ENAME, LENGTH(ENAME), SUBSTR(ENAME, -1,1) FROM EMPLOYEES WHERE INSTR(ENAME, -1, 1) = 'n' 

Answer: A 

Explanation: 

INSTR is a character function return the numeric position of a named string. 

INSTR(NAMED,’a’) 

Incorrect Answer: 

BDid not return a numeric position for ‘a’. 

CDid not return a numeric position for ‘a’. 

DDid not return a numeric position for ‘a’. 

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


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


Q55. - (Topic 2) 

Which constraint can be defined only at the column level? 

A. UNIQUE 

B. NOT NULL 

C. CHECK 

D. PRIMARY KEY 

E. FOREIGN KEY 

Answer: B 

Explanation: 

the NOT NULL constraint can be specified only at the column level, not at the table level. 

Incorrect Answer: AUNIQUE can be define at table level CCHECK can be define at table level DPRIMARY KEY can be define at table level EFOREIGN KEY can be define at table level 

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

New Questions 


certifyforall.com

Renovate exam 1z0-051:

Q56. - (Topic 1) 

View the Exhibit and examine the description for the CUSTOMERS table. 


You want to update the CUST_CREDIT_LIMIT column to NULL for all the customers, where 

CUST_INCOME_LEVEL has NULL in the CUSTOMERS table. Which SQL statement will accomplish the task? 

A. 

UPDATE customers SET cust_credit_limit = NULL WHERE CUST_INCOME_LEVEL = NULL; 

B. 

UPDATE customers SET cust_credit_limit = NULL WHERE cust_income_level IS NULL; 

C. 

UPDATE customers 

SET cust_credit_limit = TO_NUMBER(NULL) 

WHERE cust_income_level = TO_NUMBER(NULL); 

D. 

UPDATE customers 

SET cust_credit_limit = TO_NUMBER(' ',9999) 

WHERE cust_income_level IS NULL; 

Answer: B 


Q57. - (Topic 1) 

Which two statements are true regarding the COUNT function? (Choose two.) 

A. COUNT(*) returns the number of rows including duplicate rows and rows containing NULL value in any of the columns 

B. COUNT(cust_id) returns the number of rows including rows with duplicate customer IDs and NULL value in the CUST_ID column 

C. COUNT(DISTINCT inv_amt) returns the number of rows excluding rows containing duplicates and NULL values in the INV_AMT column 

D. A SELECT statement using COUNT function with a DISTINCT keyword cannot have a WHERE clause 

E. The COUNT function can be used only for CHAR, VARCHAR2 and NUMBER data types 

Answer: A,C 

Explanation: 

Using the COUNT Function 

The COUNT function has three formats: 

COUNT(*) 

COUNT(expr) 

COUNT(DISTINCT expr) 

COUNT(*) returns the number of rows in a table that satisfy the criteria of the SELECT 

statement, including duplicate rows and rows containing null values in any of the columns. 

If a WHERE clause is included in the SELECT statement, COUNT(*) returns the number of 

rows that satisfy the condition in the WHERE clause. 

In contrast, 

COUNT(expr) returns the number of non-null values that are in the column identified by 

expr. 

COUNT(DISTINCT expr) returns the number of unique, non-null values that are in the 

column identified by expr. 


Q58. - (Topic 1) 

You need to extract details of those products in the SALES table where the PROD_ID column contains the string '_D123'. Which WHERE clause could be used in the 

SELECT statement to get the required output? 

A. WHERE prod_id LIKE '%_D123%' ESCAPE '_' 

B. WHERE prod_id LIKE '%\_D123%' ESCAPE '\' 

C. WHERE prod_id LIKE '%_D123%' ESCAPE '%_' 

D. WHERE prod_id LIKE '%\_D123%' ESCAPE '\_' 

Answer: B 

Explanation: 

A naturally occurring underscore character may be escaped (or treated as a regular nonspecial symbol) using the ESCAPE identifier in conjunction with an ESCAPE character. The second example in Figure 3-12 shows the SQL statement that retrieves the JOBS table records with JOB_ID values equal to SA_MAN and SA_REP and which conforms to the original requirement: select job_id from jobs where job_id like 'SA\_%' escape '\' 


Q59. - (Topic 2) 

What are two reasons to create synonyms? (Choose two.) 

A. You have too many tables. 

B. Your tables names are too long. 

C. Your tables have difficult names. 

D. You want to work on your own tables. 

E. You want to use another schema's tables. 

F. You have too many columns in your tables. 

Answer: B,C 

Explanation: 

Create a synonyms when the names of the tables are too long or the table names are difficult. 


Q60. - (Topic 2) 

You need to produce a report where each customer's credit limit has been incremented by $1000. 

In the output, the customer's last name should have the heading Name and the incremented credit limit should be labeled New Credit Limit. 

The column headings should have only the first letter of each word in uppercase . 

Which statement would accomplish this requirement? 

A. SELECT cust_last_name Name, cust_credit_limit + 1000 

"New Credit Limit" 

FROM customers; 

B. SELECT cust_last_name AS Name, cust_credit_limit + 1000 

AS New Credit Limit 

FROM customers; 

C. SELECT cust_last_name AS "Name", cust_credit_limit + 1000 

AS "New Credit Limit" 

FROM customers; 

D. SELECT INITCAP(cust_last_name) "Name", cust_credit_limit + 1000 INITCAP("NEW 

CREDIT LIMIT") 

FROM customers; 

Answer: C 

Explanation: 

A column alias: 

-Renames a column heading 

-Is useful with calculations 

-Immediately follows the column name (There can also be the optional AS keyword between the column name and the alias.) 

-Requires double quotation marks if it contains spaces or special characters, or if it is case sensitive.