Cause all that matters here is passing the Oracle 1Z0-051 exam. Cause all that you need is a high score of 1Z0-051 Oracle Database: SQL Fundamentals I exam. The only one thing you need to do is downloading Pass4sure 1Z0-051 exam study guides now. We will not let you down with our money-back guarantee.

2016 Jun 1z0-047 vs 1z0-051:

Q111. - (Topic 1) 

Here is the structure and data of the CUST_TRANS table: Exhibit: 


Dates are stored in the default date format dd-mm-rr in the CUST_TRANS table. 

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

A. SELECT transdate + '10' FROM cust_trans; 

B. SELECT * FROM cust_trans WHERE transdate = '01-01-07' 

C. SELECT transamt FROM cust_trans WHERE custno > '11' 

D. SELECT * FROM cust_trans WHERE transdate='01-JANUARY-07' 

E. SELECT custno + 'A' FROM cust_trans WHERE transamt > 2000; 

Answer: A,C,D 


Q112. - (Topic 2) 

Examine the structure of the EMPLOYEES and NEW_EMPLOYEES tables: 

EMPLOYEES 

EMPLOYEE_ID NUMBER Primary Key 

FIRST_NAME VARCHAR2(25) 

LAST_NAME VARCHAR2(25) 

HIRE_DATE DATE 

NEW_EMPLOYEES 

EMPLOYEE_ID NUMBER Primary Key 

NAME VARCHAR2(60) 

Which MERGE statement is valid? 

A. MERGE INTO new_employees c USING employees e ON (c.employee_id = e.employee_id) WHEN MATCHED THEN UPDATE SET c.name = e.first_name ||','|| e.last_name WHEN NOT MATCHED THEN INSERT VALUES (e.employee_id, e.first_name ||', '||e.last_name); 

B. MERGE new_employees c USING employees e ON (c.employee_id = e.employee_id) WHEN EXISTS THEN UPDATE SET c.name = e.first_name ||','|| e.last_name WHEN NOT MATCHED THEN INSERT VALUES (e.employee_id, e.first_name ||', '||e.last_name); 

C. MERGE INTO new_employees c USING employees e ON (c.employee_id = e.employee_id) WHEN EXISTS THEN UPDATE SET c.name = e.first_name ||','|| e.last_name WHEN NOT MATCHED THEN INSERT VALUES(e.employee_id, e.first_name ||', '||e.last_name); 

D. MERGE new_employees c FROM employees e ON (c.employee_id = e.employee_id) WHEN MATCHED THEN UPDATE SET c.name = e.first_name ||','|| e.last_name WHEN NOT MATCHED THEN INSERT INTO new_employees VALUES (e.employee_id, 

e.first_name ||', '||e.last_name); 

Answer: A 

Explanation: 

The correct statement for MERGE is MERGE INTO table_name Incorrect Answer: BWrong statement with the keyword EXISTS CWrong statement with the keyword EXISTS DWrong statement on the MERGE new_employees 

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


Q113. - (Topic 1) 

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


Exhibit: 

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

A. 

UPDATE promotions SET promo_cost = promo_cost+ 100 WHERE TO_CHAR(promo_end_date, 'yyyy') > '2000' 

B. 

SELECT promo_begin_date FROM promotions WHERE TO_CHAR(promo_begin_date,'mon dd yy')='jul 01 98' 

C. 

UPDATE promotions SET promo_cost = promo_cost+ 100 WHERE promo_end_date > TO_DATE(SUBSTR('01-JAN-2000',8)); 

D. 

SELECT TO_CHAR(promo_begin_date,'dd/month') FROM promotions 

WHERE promo_begin_date IN (TO_DATE('JUN 01 98'), TO_DATE('JUL 01 98')); 

Answer: A,B 


Q114. - (Topic 2) 

You issue the following query: 

SQL> SELECT AVG(MAX(qty)) 

FROM ord_items 

GROUP BY item_no 

HAVING AVG(MAX(qty))>50; 

Which statement is true regarding the outcome of this query? 

A. It executes successfully and gives the correct output. 

B. It gives an error because the HAVING clause is not valid. 

C. It executes successfully but does not give the correct output. 

D. It gives an error because the GROUP BY expression is not valid. 

Answer: B 

Explanation: 

The general form of the SELECT statement is further enhanced by the addition of the 

HAVING clause and becomes: 

SELECT column|expression|group_function(column|expression [alias]),…} 

FROM table 

[WHERE condition(s)] 

[GROUP BY {col(s)|expr}] 

[HAVING group_condition(s)] 

[ORDER BY {col(s)|expr|numeric_pos} [ASC|DESC] [NULLS FIRST|LAST]]; 

An important difference between the HAVING clause and the other SELECT statement 

clauses is that it may only be specified if a GROUP BY clause is present. This dependency 

is sensible since group-level rows must exist before they can be restricted. The HAVING 

clause can occur before the GROUP BY clause in the SELECT statement. However, it is 

more common to place the HAVING clause after the GROUP BY clause. All grouping is 

performed and group functions are executed prior to evaluating the HAVING clause. 


Q115. - (Topic 1) 

View the Exhibit and examine the structure of the PROMOTIONS table. Which SQL statements are valid? (Choose all that apply.) 


A. SELECT promo_id. DECODE(NVL(promo_cost.O).promo_cost * 0.25. 100) "Discount" 

FROM promotions; 

B. SELECT promo id. DECODE(promo_cost. 10000. 

DECODE(promo_category. 'Gl\ promo_cost * 25. NULL). NULL) "Catcost" FROM 

promotions; 

C. SELECT promo_id. DECODE(NULLIF(promo_cost. 10000). NULL. promo_cost*.25, 

*N/A') "Catcost" 

FROM promotions; 

D. SELECT promo_id. DECODE(promo_cost. >10000. 'High'. <10000. 'Low') 

"Range"FROM promotions; 

Answer: A,B 

Explanation: 

Note: there are some syntax issues in this question. 


certifyforall.com

Far out 1z0-051 syllabus:

Q116. - (Topic 1) 

The ORDERS TABLE belongs to the user OE. OE has granted the SELECT privilege on the ORDERS table to the user HR. 

Which statement would create a synonym ORD so that HR can execute the following query successfully? 

SELECT * FROM ord; 

A. CREATE SYNONYM ord FOR orders; This command is issued by OE. 

B. CREATE PUBLIC SYNONYM ord FOR orders; This command is issued by OE. 

C. CREATE SYNONYM ord FOR oe.orders; This command is issued by the database administrator. 

D. CREATE PUBLIC SYNONYM ord FOR oe.orders; This command is issued by the database administrator. 

Answer: D 

Explanation: 

Creating a Synonym for an Object To refer to a table that is owned by another user, you need to prefix the table name with the name of the user who created it, followed by a period. Creating a synonym eliminates the need to qualify the object name with the schema and provides you with an alternative name for a table, view, sequence, procedure, or other objects. This method can be especially useful with lengthy object names, such as views. In the syntax: PUBLIC Creates a synonym that is accessible to all users synonym Is the name of the synonym to be created object Identifies the object for which the synonym is created Guidelines The object cannot be contained in a package. A private synonym name must be distinct from all other objects that are owned by the same user. If you try to execute the following command (alternative B, issued by OE): 


Q117. - (Topic 2) 

Examine the following SQL commands: 


Which statement is true regarding the execution of the above SQL commands? 

A. Both commands execute successfully. 

B. The first CREATE TABLE command generates an error because the NULL constraint is not valid. 

C. The second CREATE TABLE command generates an error because the CHECK constraint is not valid. 

D. The first CREATE TABLE command generates an error because CHECK and PRIMARY KEY constraints cannot be used for the same column. 

E. The first CREATE TABLE command generates an error because the column PROD_ID cannot be used in the PRIMARY KEY and FOREIGN KEY constraints. 

Answer: B 

Explanation: 

Defining Constraints The slide gives the syntax for defining constraints when creating a table. You can create 

constraints at either the column level or table level. Constraints defined at the column level 

are included when the column is defined. Table-level constraints are defined at the end of 

the table definition and must refer to the column or columns on which the constraint 

pertains in a set of parentheses. It is mainly the syntax that differentiates the two; 

otherwise, functionally, a columnlevel constraint is the same as a table-level constraint. 

NOT NULL constraints must be defined at the column level. 

Constraints that apply to more than one column must be defined at the table level. 


Q118. - (Topic 2) 

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


Evaluate the following SQL statement: 

SQL>SELECT promo_name,CASE 

WHEN promo_cost >=(SELECT AVG(promo_cost) 

FROM promotions 

WHERE promo_category='TV') 

then 'HIGH' 

else 'LOW' 

END COST_REMARK 

FROM promotions; 

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

A. It shows COST_REMARK for all the promos in the table. 

B. It produces an error because the subquery gives an error. 

C. It shows COST_REMARK for all the promos in the promo category 'TV'. 

D. It produces an error because subqueries cannot be used with the CASE expression. 

Answer: A 


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

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


Q120. - (Topic 1) 

Which one is a system privilege? 

A. SELECT 

B. DELETE 

C. EXECUTE 

D. ALTER TABLE 

E. CREATE TABLE 

Answer: E