Verified of 1Z0-051 torrent materials and training tools for Oracle certification for IT professionals, Real Success Guaranteed with Updated 1Z0-051 pdf dumps vce Materials. 100% PASS Oracle Database: SQL Fundamentals I exam Today!

2016 May 1Z0-051 Study Guide Questions:

Q71. - (Topic 1) 

Which two statements are true about constraints? (Choose two.) 

A. The UNIQUE constraint does not permit a null value for the column. 

B. A UNIQUE index gets created for columns with PRIMARY KEY and UNIQUE constraints. 

C. The PRIMARY KEY and FOREIGN KEY constraints create a UNIQUE index. 

D. The NOT NULL constraint ensures that null values are not permitted for the column. 

Answer: B,D 

Explanation: 

B: A unique constraint can contain null values because null values cannot be compared to anything. 

D: The NOT NULL constraint ensure that null value are not permitted for the column 

Incorrect Answer: Astatement is not true Cstatement is not true 

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


Q72. - (Topic 2) 

Examine the SQL statement that creates ORDERS table: 

CREATE TABLE orders (SER_NO NUMBER UNIQUE, ORDER_ID NUMBER, ORDER_DATE DATE NOT NULL, STATUS VARCHAR2(10) CHECK (status IN ('CREDIT', 'CASH')), PROD_ID NUMBER REFERENCES PRODUCTS(PRODUCT_ID), ORD_TOTAL NUMBER, PRIMARY KEY (order_id, order_date)); 

For which columns would an index be automatically created when you execute the above SQL statement? (Choose two.) 

A. SER_NO 

B. ORDER_ID 

C. STATUS 

D. PROD_ID 

E. ORD_TOTAL 

F. composite index on ORDER_ID and ORDER_DATE 

Answer: A,F 

Explanation: Index exist for UNIQUE and PRIMARY KEY constraints 

Incorrect Answer: BORDER_ID is neither UNIQUE nor PRIMARY KEY CSTATUS is neither UNIQUE nor PRIMARY KEY DPROD_ID is neither UNIQUE nor PRIMARY KEY EORD_TOTAL is neither UNIQUE nor PRIMARY KEY 

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


Q73. - (Topic 2) 

Which two statements about creating constraints are true? (Choose two) 

A. Constraint names must start with SYS_C. 

B. All constraints must be defines at the column level. 

C. Constraints can be created after the table is created. 

D. Constraints can be created at the same time the table is created. 

E. Information about constraints is found in the VIEW_CONSTRAINTS dictionary view. 

Answer: C,D 

Explanation: 

Constraints can be created after the table is created. Use ALTER TABLE command for 

that. 

Constraints can be created at the same time the table is created (CREATE TABLE 

command). 

Incorrect Answers 

A:There is no requirements in Oracle that constraint names must start with SYS_C. Oracle 

can use prefix “SYS” to build indexes for UNIQUE and NOT NULL constraints, but it is not 

required for user to follow this naming rule. 

B:Not all constraints must be defines at the column level. Only NOT NULL constraint must 

be. 

E:There is no VIEW_CONSTRAINTS dictionary view in Oracle. 

OCP Introduction to Oracle 9i: SQL Exam Guide, Jason Couchman, p. 227-232 

Chapter 5: Creating Oracle Database Objects 


2passeasy.com

Update 1z0-051 exam dumps:

Q74. - (Topic 1) 

Which three statements are true regarding sub queries? (Choose three.) 

A. Multiple columns or expressions can be compared between the main query and sub query 

B. Main query and sub query can get data from different tables 

C. Sub queries can contain GROUP BY and ORDER BY clauses 

D. Main query and sub query must get data from the same tables 

E. Sub queries can contain ORDER BY but not the GROUP BY clause 

F. Only one column or expression can be compared between the main query and subqeury 

Answer: A,B,C 


Q75. - (Topic 1) 

View the Exhibit and examine the data in the PRODUCTS table. You need to display product names from the PRODUCTS table that belong to the 'Software/Other1 category with minimum prices as either $2000 or $4000 and no unit of measure. You issue thej following query: 


Which statement is true regarding the above query? 


A. It executes successfully but returns no result. 

B. It executes successfully and returns the required result. 

C. It generates an error because the condition specified for PROD_UNIT_OF_MEASURE is not valid. 

D. It generates an error because the condition specified for the PROD_CATEGORY column is not valid. 

Answer: A 


Q76. - (Topic 2) 

View the Exhibit and examine the data in the PROMO_CATEGORY and PROMO_COST columns of the PROMOTIONS table. 

Evaluate the following two queries: 

SQL>SELECT DISTINCT promo_category to_char(promo_cost)"code" 

FROM promotions 

ORDER BY code; 

SQL>SELECT DISTINCT promo_category promo_cost "code" 

FROM promotions 

ORDER BY 1; 

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


A. Only the first query executes successfully. 

B. Only the second query executes successfully. 

C. Both queries execute successfully but give different results. 

D. Both queries execute successfully and give the same result. 

Answer: B 

Explanation: 

Note: You cannot use column alias in the WHERE clause. 


2passeasy.com

Real 1z0-051 dumps free:

Q77. - (Topic 1) 

When does a transaction complete? (Choose all that apply.) 

A. When a PL/SQL anonymous block is executed B. When a DELETE statement is executed 

C. When a data definition language statement is executed 

D. When a TRUNCATE statement is executed after the pending transaction 

E. When a ROLLBACK command is executed 

Answer: C,D,E 


Q78. - (Topic 1) 

View the Exhibit and examine the structure of the PROMOTIONS table. Using the PROMOTIONS table, you need to display the names of all promos done after January 1, 2001, starting with the latest promo. Which query would give the required result? (Choose all that apply.) 


A. SELECT promo_name, promo_begin_date FROM promotions 

WHERE promo_begiii_date > '01-JAN-01' ORDER BY 2 DESC; 

B. SELECT promo_name. promo_begiii_date FROM promotions 

WHERE promo_begin_date > '01-JAN-01' ORDER BY promo_name DESC: 

C. SELECT promo_name. promo_begin_date FROM promotions 

WHERE promo_begin_date > '01-JAN-01' ORDER BY 1DESC: 

D. SELECT promo_name, promo_begin_date "START DATE" FROM promotions 

WHERE promo_begin_date > '01-JAN-01' ORDER BY "START DATE" DESC; 

Answer: A,D 


Q79. - (Topic 1) 

Which three SQL statements would display the value 1890.55 as $1,890.55? (Choose three.) 

A. 

SELECT TO_CHAR(1890.55,'$99G999D00') FROM DUAL; 

B. 

SELECT TO_CHAR(1890.55,'$9,999V99') FROM DUAL; 

C. 

SELECT TO_CHAR(1890.55,'$0G000D00') FROM DUAL; 

D. 

SELECT TO_CHAR(1890.55,'$99G999D99') 

FROM DUAL; 

E. 

SELECT TO_CHAR(1890.55,'$9,999D99') FROM DUAL; 

Answer: A,C,D 


Q80. - (Topic 2) 

Which three statements are true regarding views? (Choose three.) 

A. Views can be created only from tables. 

B. Views can be created from tables or other views. 

C. Only simple views can use indexes existing on the underlying tables. 

D. Both simple and complex views can use indexes existing on the underlying tables. 

E. Complex views can be created only on multiple tables that exist in the same schema. 

F. Complex views can be created on multiple tables that exist in the same or different schemas. 

Answer: B,D,F 

Explanation: 

Creating a Sequence (continued) 

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