Oracle 1Z0-061 Online Practice Questions and Exam Preparation

Oracle 1Z0-061 Online Questions & Answers

• Question 161:

  View the Exhibit and examine the data in the PROMOTIONS table.

  

  You need to display all promo categories that do not have 'discount' in their subcategory. Which two SQL statements give the required result? (Choose two.)

  A. SELECT promo_categoryFROM promotionsMINUSSELECT promo_categoryFROM promotionsWHERE promo_subcategory = 'discount';
  B. SELECT promo_categoryFROM promotionsINTERSECTSELECT promo_categoryFROM promotionsWHERE promo_subcategory = 'discount';
  C. SELECT promo_categoryFROM promotionsMINUSSELECT promo_categoryFROM promotionsWHERE promo_subcategory 'discount';
  D. SELECT promo_categoryFROM promotionsINTERSECTSELECT promo_categoryFROM promotionsWHERE promo_subcategory 'discount';
  A. SELECT promo_categoryFROM promotionsMINUSSELECT promo_categoryFROM promotionsWHERE promo_subcategory = 'discount';
  D. SELECT promo_categoryFROM promotionsINTERSECTSELECT promo_categoryFROM promotionsWHERE promo_subcategory 'discount';

• Question 162:

  Examine this statement:

  SELECT student_id, gpa FROM student_grades WHERE gpa > andandvalue;

  You run the statement once, and when prompted you enter a value of 2.0. A report is produced.

  What happens when you run the statement a second time?

  A. An error is returned.
  B. You are prompted to enter a new value.
  C. A report is produced that matches the first report produced.
  D. You are asked whether you want a new value or if you want to run the report based on the previous value.
  C. A report is produced that matches the first report produced.

  ---

  Use the double-ampersand if you want to reuse the variable value without prompting the user each time. Incorrect answer:

  A. is not an error

  B. andand will not prompt user for second time

  D. andand will not ask the user for new value Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 7-13

• Question 163:

  Which statement describes the ROWID data type?

  A. Binary data up to 4 gigabytes.
  B. Character data up to 4 gigabytes.
  C. Raw binary data of variable length up to 2 gigabytes.
  D. Binary data stored in an external file, up to 4 gigabytes.
  E. A hexadecimal string representing the unique address of a row in its table.
  E. A hexadecimal string representing the unique address of a row in its table.

  ---

  The ROWID datatype stores information related to the disk location of table rows. They also uniquely identify the rows in your table. The ROWID datatype is stored as a hexadecimal string.

  Incorrect Answers

  A:. It is not a binary data. The ROWID datatype is a hexadecimal string.

  B:. It is not a character data. The ROWID datatype is a hexadecimal string.

  C:. It is not a raw binary data. The ROWID datatype is a hexadecimal string.

  D:. It is not binary data stored in an external file. The ROWID datatype is a hexadecimal string.

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

• Question 164:

  This statement will fail:

  create unique bitmap index on employees(department_id,hire_date);

  Why?

  A. Bitmap indexes cannot be unique.
  B. The two columns are of different data types.
  C. A bitmap index can be on only one column.
  D. There is already a B*Tree index on DEPARTMENT_ID.
  A. Bitmap indexes cannot be unique.

• Question 165:

  Evaluate the SQL statement:

  TRUNCATE TABLE DEPT;

  Which three are true about the SQL statement? (Choose three.)

  A. It releases the storage space used by the table.
  B. It does not release the storage space used by the table.
  C. You can roll back the deletion of rows after the statement executes.
  D. You can NOT roll back the deletion of rows after the statement executes.
  E. An attempt to use DESCRIBE on the DEPT table after the TRUNCATE statement executes will display an error.
  F. You must be the owner of the table or have DELETE ANY TABLE system privileges to truncate the DEPT table
  A. It releases the storage space used by the table.
  D. You can NOT roll back the deletion of rows after the statement executes.
  F. You must be the owner of the table or have DELETE ANY TABLE system privileges to truncate the DEPT table

  ---

  A: The TRUNCATE TABLE Statement releases storage space used by the table,

  D: Can not rollback the deletion of rows after the statement executes,

  F: You must be the owner of the table or have DELETE ANY TABLE system privilege to truncate the DEPT table.

  Incorrect answer:

  C. is not true

  D. is not true

  E. is not true

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

• Question 166:

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

  Exhibit:

  

  Using the PROMOTIONS table, you need to find out the average cost for all promos in the range $0-2000 and $2000-5000 in category A. You issue the following SQL statements:

  

  What would be the outcome?

  A. It generates an error because multiple conditions cannot be specified for the WHEN clause
  B. It executes successfully and gives the required result
  C. It generates an error because CASE cannot be used with group functions
  D. It generates an error because NULL cannot be specified as a return value
  B. It executes successfully and gives the required result

  ---

  CASE Expression Facilitates conditional inquiries by doing the work of an IF-THEN-ELSE statement: CASE expr WHEN comparison_expr1 THEN return_expr1 [WHEN comparison_expr2 THEN return_expr2 WHEN comparison_exprn THEN return_exprn ELSE else_expr] END

• Question 167:

  Which two partitioned table maintenance operations support asynchronous Global Index Maintenance in Oracle database 12c?

  A. ALTER TABLE SPLIT PARTITION
  B. ALTER TABLE MERGE PARTITION
  C. ALTER TABLE TRUNCATE PARTITION
  D. ALTER TABLE ADD PARTITION
  E. ALTER TABLE DROP PARTITION
  F. ALTER TABLE MOVE PARTITION
  C. ALTER TABLE TRUNCATE PARTITION
  E. ALTER TABLE DROP PARTITION

  ---

  Asynchronous Global Index Maintenance for DROP and TRUNCATE PARTITION This feature enables global index maintenance to be delayed and decoupled from a DROP and TRUNCATE partition without making a global index unusable. Enhancements include faster DROP and TRUNCATE partition operations and the ability to delay index maintenance to off-peak time.

• Question 168:

  Which two statements are true regarding the DELETE and TRUNCATE commands? (Choose two.)

  A. DELETE can be used to remove only rows from only one table at a time.
  B. DELETE can be used to remove only rows from multiple tables at a time.
  C. DELETE can be used only on a table that is a parent of a referential integrity constraint.
  D. DELETE can be used to remove data from specific columns as well as complete rows.
  E. DELETE and TRUNCATE can be used on a table that is a parent of a referential integrity constraint having ON DELETE rule.
  A. DELETE can be used to remove only rows from only one table at a time.
  E. DELETE and TRUNCATE can be used on a table that is a parent of a referential integrity constraint having ON DELETE rule.

  ---

  Transactions, consisting of INSERT, UPDATE, and DELETE (or even MERGE) commands can be made permanent (with a COMMIT) or reversed (with a ROLLBACK). A TRUNCATE command, like any other DDL command, is immediately permanent: it can never be reversed. The Transaction Control Statements A transaction begins implicitly with the first DML statement. There is no command to explicitly start a transaction. The transaction continues through all subsequent DML statements issued by the session. These statements can be against any number of tables: a transaction is not restricted to one table. It terminates (barring any of the events listed in the previous section) when the session issues a COMMIT or ROLLBACK command. The SAVEPOINT command can be used to set markers that will stage the action of a ROLLBACK, but the same transaction remains in progress irrespective of the use of SAVEPOINT Explicit Transaction Control Statements You can control the logic of transactions by using the COMMIT, SAVEPOINT, and ROLLBACK statements. Note: You cannot COMMIT to a SAVEPOINT. SAVEPOINT is not ANSI-standard SQL.

  

• Question 169:

  View the Exhibits and examine products and sales tables.

  

  You issue the following query to display product name and the number of times the product has been sold:

  

  What happens when the above statement is executed?

  A. The statement executes successfully and produces the required output.
  B. The statement produces an error because item_cnt cannot be displayed in the outer query.
  C. The statement produces an error because a subquery in the from clause and outer-joins cannot be used together.
  D. The statement produces an error because the group by clause cannot be used in a subquery in the from clause.
  A. The statement executes successfully and produces the required output.

• Question 170:

  View the Exhibit and examine the structure of the PROMOSTIONS table:

  Exhibit:

  

  Which two SQL statements are valid? (Choose two.)

  A. SELECT promo_id, DECODE (NVL(promo_cost, 0), promo_cost,promo_cost * 0.25, 100) "Discount"FROM promotions;
  B. SELECT promo_id, DECODE (promo_cost, 10000,DECODE(promo_category, 'G1', 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',
  A. SELECT promo_id, DECODE (NVL(promo_cost, 0), promo_cost,promo_cost * 0.25, 100) "Discount"FROM promotions;
  B. SELECT promo_id, DECODE (promo_cost, 10000,DECODE(promo_category, 'G1', promo_cost *.25, NULL),NULL) "Catcost"FROM promotions;

  ---

  The DECODE Function Although its name sounds mysterious, this function is straightforward. The DECODE function implements ifthen-else conditional logic by testing its first two terms for equality and returns the third if they are equal and optionally returns another term if they are not. The DECODE function takes at least three mandatory parameters, but can take many more. The syntax of the function is DECODE(expr1, comp1, iftrue1, [comp2, iftrue2...[ compN, iftrueN]], [iffalse]).