Oracle 1Z0-061 Online Practice Questions and Exam Preparation

Oracle 1Z0-061 Online Questions & Answers

• Question 201:

  You created an ORDERS table with the following description:

  Exhibit:

  

  You inserted some rows in the table. After some time, you want to alter the table by creating the PRIMARY KEY constraint on the ORD_ID column. Which statement is true in this scenario?

  A. You cannot add a primary key constraint if data exists in the column
  B. You can add the primary key constraint even if data exists, provided that there are no duplicate values
  C. The primary key constraint can be created only a the time of table creation
  D. You cannot have two constraints on one column
  B. You can add the primary key constraint even if data exists, provided that there are no duplicate values

• Question 202:

  Examine the description of the EMP_DETAILS table given below:

  

  Which two statements are true regarding SQL statements that can be executed on the EMP_DETAIL table? (Choose two.)

  A. An EMP_IMAGE column can be included in the GROUP BY clause
  B. You cannot add a new column to the table with LONG as the data type
  C. An EMP_IMAGE column cannot be included in the ORDER BY clause
  D. You can alter the table to include the NOT NULL constraint on the EMP_IMAGE column
  B. You cannot add a new column to the table with LONG as the data type
  C. An EMP_IMAGE column cannot be included in the ORDER BY clause

  ---

  LONG Character data in the database character set, up to 2GB. All the functionality of LONG (and more) is provided by CLOB; LONGs should not be used in a modern database, and if your database has any columns of this type they should

  be converted to CLOB.

  There can only be one LONG column in a table.

  Guidelines

  A LONG column is not copied when a table is created using a subquery. A LONG column cannot be included in a GROUP BY or an ORDER BY clause.

  Only one LONG column can be used per table.

  No constraints can be defined on a LONG column.

  You might want to use a CLOB column rather than a LONG column.

• Question 203:

  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 promotionsSET promo_cost = promo_cost+ 100WHERE TO_CHAR(promo_end_date, 'yyyy') > '2000';
  B. SELECT promo_begin_dateFROM promotionsWHERE TO_CHAR(promo_begin_date, 'mon dd yy')='jul 01 98';
  C. UPDATE promotionsSET promo_cost = promo_cost+ 100WHERE promo_end_date > TO_DATE(SUBSTR('01-JAN-2000', 8));
  D. SELECT TO_CHAR(promo_begin_date, 'dd/month')FROM promotionsWHERE promo_begin_date IN (TO_DATE('JUN 01 98'), TO_DATE('JUL 01 98'));
  A. UPDATE promotionsSET promo_cost = promo_cost+ 100WHERE TO_CHAR(promo_end_date, 'yyyy') > '2000';
  B. SELECT promo_begin_dateFROM promotionsWHERE TO_CHAR(promo_begin_date, 'mon dd yy')='jul 01 98';

• Question 204:

  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.
  B. Only the second query executes successfully.

• Question 205:

  What is true about the WITH GRANT OPTION clause?

  A. It allows a grantee DBA privileges.
  B. It is required syntax for object privileges.
  C. It allows privileges on specified columns of tables.
  D. It is used to grant an object privilege on a foreign key column.
  E. It allows the grantee to grant object privileges to other users and roles.
  E. It allows the grantee to grant object privileges to other users and roles.

  ---

  The GRANT command with the WITH GRANT OPTION clause allows the grantee to grant object privileges to other users and roles.

  Incorrect Answers

  A:. The WITH GRANT OPTION does not allow a grantee DBA privileges.

  B:. It is not required syntax for object privileges. It is optional clause of GRANT command.

  C:. GRANT command does not allows privileges on columns of tables.

  D:. It is not used to grant an object privilege on a foreign key column.

  OCP Introduction to Oracle 9i: SQL Exam Guide, Jason Couchman, p. 356-365 Chapter 8: User Access in Oracle

• Question 206:

  View the Exhibit and examine the structure of the PROMOTIONS, SALES, and CUSTOMER tables.

  You need to generate a report showing the promo name along with the customer name for all products that were sold during their promo campaign and before 30th October 2007.

  You issue the following query:

  

  Which statement is true regarding the above query?

  A. It executes successfully and gives the required result.
  B. It executes successfully but does not give the required result.
  C. It produces an error because the join order of the tables is incorrect.
  D. It produces an error because equijoin and nonequijoin conditions cannot be used in the same SELECT statement.
  B. It executes successfully but does not give the required result.

• Question 207:

  View the Exhibit and examine the data in the PROMO_NAME and PROMO_END_DATE columns of the promotions table, and the required output format.

  

  Which two queries give the correct result?

  

  A. Option A
  B. Option B
  C. Option C
  D. Option D
  C. Option C
  D. Option D

• Question 208:

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

  A. Savepoints are effective only for COMMIT.
  B. Savepoints may be used to ROLLBACK.
  C. Savepoints can be used for only DML statements.
  D. Savepoints are effective for both COMMIT and ROLLBACK.
  E. Savepoints can be used for both DML and DDL statements.
  B. Savepoints may be used to ROLLBACK.
  C. Savepoints can be used for only DML statements.

• Question 209:

  Which object privileges can be granted on a view?

  A. none
  B. DELETE, INSERT, SELECT
  C. ALTER, DELETE, INSERT, SELECT
  D. DELETE, INSERT, SELECT, UPDATE
  D. DELETE, INSERT, SELECT, UPDATE

  ---

  Object privilege on VIEW is DELETE, INSERT, REFERENCES, SELECT and UPDATE. Incorrect answer:

  A. Object privilege on VIEW is DELETE, INSERT, REFERENCES, SELECT and UPDATE B. Object privilege on VIEW is DELETE, INSERT, REFERENCES, SELECT and UPDATE C. Object privilege on VIEW is DELETE, INSERT, REFERENCES, SELECT and UPDATE

  Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 13-12

• Question 210:

  You issue the following command to drop the products table:

  SQL> DROP TABLE products;

  Which three statements are true about the implication of this command? (Choose three.)

  A. All data along with the table structure is deleted.
  B. A pending transaction in the session is committed.
  C. All indexes on the table remain but they are invalidated.
  D. All views and synonyms remain but they are invalidated.
  E. All data in the table is deleted but the table structure remains.
  A. All data along with the table structure is deleted.
  B. A pending transaction in the session is committed.
  D. All views and synonyms remain but they are invalidated.

  ---

  A: The DROP TABLE statement moves a table or object table to the recycle bin.

  B: If a user issues a DDL (CREATE, ALTER, or DROP) or DCL (GRANT or REVOKE) command, the transaction in progress (if any) will

  Incorrect:

  Not C: Dropping a table invalidates dependent objects, such as indexes and constraints.