Examcollection 1Z0-051 Oracle Database: SQL Fundamentals I exercise examination could be the most suitable choice that you should get ready for the particular Oracle exam. In order to be described as a prominent Examcollection technical engineers, you can not pass up the particular Examcollection 1Z0-051 Oracle Database: SQL Fundamentals I substance provision. Learn not merely the real 1Z0-051 examination questions but also the detailed description. Passing Oracle 1Z0-051 qualification will be the initial most important thing, in addition, you can learn numerous specialized understanding and also activities that can found in the way forward for exercise perform.

2016 Jun 1Z0-051 question

Q141. - (Topic 2) 

Examine the data in the CUST_NAME column of the CUSTOMERS table. CUST_NAME 

Lex De Haan Renske Ladwig Jose Manuel Urman 

Jason Mallin 

You want to extract only those customer names that have three names and display the * symbol in place of the 

first name as follows: 

CUST NAME 

*** De Haan 

**** Manuel Urman 

Which two queries give the required output? (Choose two.) 

A. 

SELECT LPAD(SUBSTR(cust_name,INSTR(cust_name,' ')),LENGTH(cust_name),'*') 

"CUST NAME" FROM customers 

WHERE INSTR(cust_name, ' ',1,2)<>0; 

B. 

SELECT LPAD(SUBSTR(cust_name,INSTR(cust_name,' ')),LENGTH(cust_name),'*') 

"CUST NAME" FROM customers 

WHERE INSTR(cust_name, ' ',-1,2)<>0; 

C. 

SELECT LPAD(SUBSTR(cust_name,INSTR(cust_name,' ')),LENGTH(cust_name)-INSTR(cust_name,''),'*') "CUST NAME" 

FROM customers 

WHERE INSTR(cust_name, ' ',-1,-2)<>0; 

D. 

SELECT LPAD(SUBSTR(cust_name,INSTR(cust_name,' ')),LENGTH(cust_name)-INSTR(cust_name,' '),'*') "CUST NAME" 

FROM customers 

WHERE INSTR(cust_name, ' ',1,2)<>0 ; 

Answer: A,B 


Q142. - (Topic 1) 

Examine the structure of the INVOICE table: Exhibit: 


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

A. SELECT inv_no,NVL2(inv_date,'Pending','Incomplete') FROM invoice; 

B. SELECT inv_no,NVL2(inv_amt,inv_date,'Not Available') FROM invoice; 

C. SELECT inv_no,NVL2(inv_date,sysdate-inv_date,sysdate) FROM invoice; 

D. SELECT inv_no,NVL2(inv_amt,inv_amt*.25,'Not Available') FROM invoice; 

Answer: A,C 

Explanation: 

The NVL2 Function 

The NVL2 function provides an enhancement to NVL but serves a very similar purpose. It evaluates whether a column or expression of any data type is null or not. 5-6 The NVL function\ If the first term is not null, the second parameter is returned, else the third parameter is returned. Recall that the NVL function is different since it returns the original term if it is not null. The NVL2 function takes three mandatory parameters. Its syntax is NVL2(original, ifnotnull, ifnull), where original represents the term being tested. Ifnotnull is returned if original is not null, and ifnull is returned if original is null. The data types of the ifnotnull and ifnull parameters must be compatible, and they cannot be of type LONG. They must either be of the same type, or it must be possible to convert ifnull to the type of the ifnotnull parameter. The data type returned by the NVL2 function is the same as that of the ifnotnull parameter. 


Q143. - (Topic 2) 

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

A. A table name can be of any length. 

B. A table can have any number of columns. 

C. A column that has a DEFAULT value cannot store null values. 

D. A table and a view can have the same name in the same schema. 

E. A table and a synonym can have the same name in the same schema. 

F. The same table name can be used in different schemas in the same database. 

Answer: E,F 

Explanation: 

Synonyms Synonyms are database objects that enable you to call a table by another name. You can create synonyms to give an alternative name to a table. 


Q144. - (Topic 1) 

Which statements are correct regarding indexes? (Choose all that apply.) 

A. For each data manipulation language (DML) operation performed, the corresponding indexes are automatically updated. 

B. A nondeferrable PRIMARY KEY or UNIQUE KEY constraint in a table automatically creates a unique index. 

C. A FOREIGN KEY constraint on a column in a table automatically creates a non unique key 

D. When a table is dropped, the corresponding indexes are automatically dropped 

Answer: A,B,D 


Q145. - (Topic 2) 

View the Exhibit and examine the structure of CUSTOMERS and SALES tables. 


Evaluate the following SQL statement: 

UPDATE (SELECT prod_id, cust_id, quantity_sold, time_id 

FROM sales) 

SET time_id = '22-MAR-2007' 

WHERE cust_id = (SELECT cust_id 

FROM customers 

WHERE cust_last_name = 'Roberts' AND 

credit_limit = 600); 

Which statement is true regarding the execution of the above UPDATE statement? 

A. It would not execute because two tables cannot be used in a single UPDATE statement. 

B. It would not execute because the SELECT statement cannot be used in place of the table name. 

C. It would execute and restrict modifications to only the columns specified in the SELECT statement. 

D. It would not execute because a subquery cannot be used in the WHERE clause of an UPDATE statement. 

Answer: C 

Explanation: 

One UPDATE statement can change rows in only one table, but it can change any number of rows in that table. 


certifyforall.com

Renovate 1z0-051 dumps pdf free download:

Q146. - (Topic 1) 

View the Exhibit and examine the structure of the PROMOTIONS table. 

Using the PROMOTIONS table, you need to find out the names and cost of all the promos done on 'TV' and 'internet' that ended in the time interval 15th March '00 to 15th October '00. 

Which two queries would give the required result? (Choose two.) 


A. SELECT promo_name, promo_cost FROM promotions WHERE promo_category IN ('TV', 'internet') AND promo_end_date BETWEEN '15-MAR-00' AND '15-OCT-00' 

B. SELECT promo_name, promo_cost FROM promotions WHERE promo_category = 'TV' OR promo_category ='internet' AND promo_end_date >='15-MAR-00' OR promo_end_date <='15-OCT-00' 

C. SELECT promo_name, promo_cost FROM promotions WHERE (promo_category BETWEEN 'TV' AND 'internet') AND (promo_end_date IN ('15-MAR-00','15-OCT-00')); 

D. SELECT promo_name, promo_cost FROM promotions WHERE (promo_category = 'TV' OR promo_category ='internet') AND (promo_end_date >='15-MAR-00' AND promo_end_date <='15-OCT-00'); 

Answer: A,D 


Q147. - (Topic 1) 

You work as a database administrator at ABC.com. You study the exhibit carefully. Exhibit: 


Evaluate the following query: Exhibit: 


The above query produces an error on execution. What is the reason for the error? 

A. An alias cannot be used in an expression 

B. The alias MIDPOINT should be enclosed within double quotation marks for the CUST_CREDIT_LIMIT/2 expression 

C. The MIDPOINT +100 expression gives an error because CUST_CREDIT_LIMIT contains NULL values 

D. The alias NAME should not be enclosed within double quotation marks 

Answer: A 


Q148. - (Topic 2) 

Which two statements complete a transaction? (Choose two) 

A. DELETE employees; 

B. DESCRIBE employees; 

C. ROLLBACK TO SAVEPOINT C; 

D. GRANT SELECT ON employees TO SCOTT; 

E. ALTER TABLE employeesSET UNUSED COLUMN sal; 

F. Select MAX(sal)FROM employeesWHERE department_id = 20; 

Answer: D,E 

Explanation: 

D: GRANT is a DML operation which will cause an implicit commit 

E: It is important to understand that an implicit COMMIT occurs on the database when a user exits SQL*Plus or issues a data-definition language (DDL) command such as a CREATE TABLE statement, used to create a database object, or an ALTER TABLE statement, used to alter a database object. 

Incorrect Answers A:The DELETE command is data-manipulation language (DML) command and it does not complete a transaction. B:The DESCRIBE command is internal SQL*Plus command and it has nothing to do with completion a transaction. 

C: ROLLBACK is not used to commit or complete a transaction, it is used to undo a transaction F:SELECT command is used to retrieve data. It does not complete a transaction. 

OCP Introduction to Oracle 9i: SQL Exam Guide, Jason Couchman, p. 281-282 Chapter 3: Advanced Data Selection in Oracle 


Q149. - (Topic 2) 

What does the FORCE option for creating a view do? 

A. creates a view with constraints 

B. creates a view even if the underlying parent table has constraints 

C. creates a view in another schema even if you don't have privileges 

D. creates a view regardless of whether or not the base tables exist 

Answer: D 

Explanation: 

create a view regardless of whether or not the base tables exist. 

Incorrect Answer: Athe option is not valid Bthe option is not valid Cthe option is not valid 

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


Q150. - (Topic 1) 

Which statement is true regarding sub queries? 

A. The LIKE operator cannot be used with single- row subqueries. 

B. The NOT IN operator is equivalent to IS NULL with single- row subqueries. 

C. =ANY and =ALL operators have the same functionality in multiple- row subqueries. 

D. The NOT operator can be used with IN, ANY, and ALL operators in multiple- row subqueries. 

Answer: D 

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.