[Free] 2019(Nov) EnsurePass Oracle 1z0-061 Dumps with VCE and PDF 201-210

Get Full Version of the Exam
http://www.EnsurePass.com/1z0-061.html

Question No.201

The EMPLOYEES table has these columns: LAST NAME. VARCHAR2(35)

SALARY. NUMBER(8, 2) HIRE_DATE. DATE

Management wants to add a default value to the SALARY column. You plan to alter the table by using this SQL statement:

ALTER TABLE EMPLOYEES MODIFY (SALARY DEFAULT 5000);

What is true about your ALTER statement?

  1. Column definitions cannot be altered to add DEFAULT values.

  2. A change to the DEFAULT value affects only subsequent insertions to the table.

  3. Column definitions cannot be altered at add DEFAULT values for columns with a NUMBER data type.

  4. All the rows that have a NULL value for the SALARY column will be updated with the value 5000.

Correct Answer: B

Explanation:

A change to the DEFAULT value affects only subsequent insertions to the table. Existing rows will not be affected.

Incorrect Answers

A: Column definitions can be altered to add DEFAULT values.

C: Column definitions can be altered to add DEFAULT values. It works for columns with a NUMBER data type also.

D: A change to the DEFAULT value affects only subsequent insertions to the table. Existing rows will not be affected.

OCP Introduction to Oracle 9i: SQL Exam Guide, Jason Couchman, p. 219-224 Chapter 5: Creating Oracle Database Objects

Question No.202

Which statement adds a constraint that ensures the CUSTOMER_NAME column of the CUSTOMERS table holds a value?

  1. ALTER TABLE customers ADD CONSTRAINT cust_name_nn CHECK customer_name IS NOT NULL;

  2. ALTER TABLE customers MODIFY CONSTRAINT cust_name_nn CHECK customer_name IS NOT NULL;

  3. ALTER TABLE customers MODIFY customer_name CONSTRAINT cust_name_nn NOT NULL;

  4. ALTER TABLE customers MODIFY customer_name CONSTRAINT cust_name_nn IS NOT NULL;

  5. ALTER TABLE customers MODIFY name CONSTRAINT cust_name_nn NOT NULL;

  6. ALTER TABLE customers ADD CONSTRAINT cust_name_nn CHECK customer_name NOT NULL;

Correct Answer: C

Question No.203

Which three statements are true about multiple-row sub queries? (Choose three.)

  1. They can contain a subquery within a sub query.

  2. They can return multiple columns as well as rows.

  3. They cannot contain a subquery within a subquery.

  4. They can return only one column but multiple rows.

  5. They can contain group functions and GROUP BY and HAVING clauses.

  6. They can contain group functions and the GROUP BY clause, but not the HAVING clause.

Correct Answer: ABE

Question No.204

For which action can you use the TO_DATE function?

  1. Convert any date literal to a date

  2. Convert any numeric literal to a date

  3. Convert any character literal to a date

  4. Convert any date to a character literal

  5. Format #39;10-JAN-99#39; to `January 10 1999#39;

Correct Answer: C

Question No.205

View the Exhibit and examine the description for the PRODUCTS and SALES table.

image

PROD_ID is a primary key in the PRODUCTS table and foreign key in the SALES table. You want to remove all the rows from the PRODUCTS table for which no sale was done for the last three years. Which is the valid DELETE statement?

  1. DELETEFROM productsWHERE prod_id = (SELECT prod_idFROM salesWHERE time_id – 3*365 = SYSDATE );

  2. DELETEFROM productsWHERE prod_id = (SELECT prod_idFROM salesWHERE SYSDATE gt;= time_id – 3*365 );

  3. DELETEFROM productsWHERE prod_id IN (SELECT prod_idFROM salesWHERE SYSDATE – 3*365 gt;= time_id);

  4. DELETEFROM productsWHERE prod_id IN (SELECT prod_idFROM salesWHERE time_id gt;= SYSDATE – 3*365 );

Correct Answer: C

Question No.206

View the Exhibit and examine the structure of ORD and ORD_ITEMS tables.

The ORD_NO column is PRIMARY KEY in the ORD table and the ORD_NO and ITEM_NO columns are composite PRIMARY KEY in the ORD_ITEMS table.

Which two CREATE INDEX statements are valid? (Choose two.)

image

  1. CREATE INDEX ord_idx1ON ord(ord_no);

  2. CREATE INDEX ord_idx2ON ord_items(ord_no);

  3. CREATE INDEX ord_idx3ON ord_items(item_no);

  4. CREATE INDEX ord_idx4ON ord, ord_items(ord_no, ord_date, qty);

Correct Answer: BC

Explanation:

How Are Indexes Created?

You can create two types of indexes.

Unique index: The Oracle server automatically creates this index when you define a column in a table to have a PRIMARY KEY or a UNIQUE constraint. The name of the index is the name that is given to the constraint.

Nonunique index: This is an index that a user can create. For example, you can create the FOREIGN KEY column index for a join in a query to improve the speed of retrieval. Note: You can manually create a unique index, but it is recommended that you create a unique constraint, which implicitly creates a unique index.

Question No.207

The CUSTOMERS table has these columns:

The CUSTOMER_ID column is the primary key for the table. You need to determine how dispersed your customer base is.

Which expression finds the number of different countries represented in the CUSTOMERS table?

  1. COUNT(UPPER(country_address))

  2. COUNT(DIFF(UPPER(country_address)))

  3. COUNT(UNIQUE(UPPER(country_address)))

  4. COUNT DISTINCT UPPER(country_address)

  5. COUNT(DISTINCT (UPPER(country_address)))

Correct Answer: E

Question No.208

You are granted the CREATE VIEW privilege. What does this allow you to do?

  1. Create a table view.

  2. Create a view in any schema.

  3. Create a view in your schema.

  4. Create a sequence view in any schema.

  5. Create a view that is accessible by everyone.

  6. Create a view only of it is based on tables that you created.

Correct Answer: C

Explanation:

You can create a view in your own schema only if you are granted the CREATE VIEW privilege.

Incorrect Answers

A: You can create a view in your own schema only.

B: You can create a view in your own schema only, not in any schema. D: There is no sequence view in Oracle.

E: You cannot create a view that is accessible by everyone. You will need specially grant SELECT privileges on this view for everyone.

F: You can create a view in your own schema, but not only for tables in your schema. You can use object from other users schemas if you have privileges to retrieve data from them.

OCP Introduction to Oracle 9i: SQL Exam Guide, Jason Couchman, p. 292-301 Chapter 7: Creating Other Database Objects in Oracle

Question No.209

View the Exhibit and examine the structure of the PROMOTIONS table. Evaluate the following SQL statement:

image

The above query generates an error on execution.

Which clause in the above SQL statement causes the error?

image

  1. WHERE

  2. SELECT

  3. GROUP BY

  4. ORDER BY

Correct Answer: C

Question No.210

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

image

Examine the following two SQL statements:

image

Which statement is true regarding the above two SQL statements?

  1. statement 1 gives an error, statement 2 executes successfully

  2. statement 2 gives an error, statement 1 executes successfully

  3. statement 1 and statement 2 execute successfully and give the same output

  4. statement 1 and statement 2 execute successfully and give a different output

Correct Answer: D

Get Full Version of the Exam
1z0-061 Dumps
1z0-061 VCE and PDF

Leave a Reply

Your email address will not be published. Required fields are marked *

Proudly powered by WordPress