Oracle 1Z0-061 Online Practice Questions and Exam Preparation

Oracle 1Z0-061 Online Questions & Answers

• Question 141:

  Which two statements are true about sequences created in a single instance database? (Choose two.)

  A. CURRVAL is used to refer to the last sequence number that has been generated
  B. DELETE would remove a sequence from the database
  C. The numbers generated by a sequence can be used only for one table
  D. When the MAXVALUE limit for a sequence is reached, you can increase the MAXVALUE limit by using the ALTER SEQUENCE statement
  E. When a database instance shuts down abnormally, the sequence numbers that have been cached but not used would be available once again when the database instance is restarted
  A. CURRVAL is used to refer to the last sequence number that has been generated
  D. When the MAXVALUE limit for a sequence is reached, you can increase the MAXVALUE limit by using the ALTER SEQUENCE statement

  ---

  Gaps in the Sequence

  Although sequence generators issue sequential numbers without gaps, this action occurs independent of a commit or rollback. Therefore, if you roll back a statement containing a sequence, the number is lost.

  Another event that can cause gaps in the sequence is a system crash. If the sequence caches values in memory, those values are lost if the system crashes. Because sequences are not tied directly to tables, the same sequence can be used

  for multiple tables.

  However, if you do so, each table can contain gaps in the sequential numbers.

  Modifying a Sequence

  If you reach the MAXVALUE limit for your sequence, no additional values from the sequence are allocated and you will receive an error indicating that the sequence exceeds the MAXVALUE. To continue to use the sequence, you can modify

  it by using the ALTER SEQUENCE statement

  To remove a sequence, use the DROP statement:

  DROP SEQUENCE dept_deptid_seq;

• Question 142:

  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 =
  B. employee_id) WHEN MATCHED THEN UPDATE SET c.name = e.first_name ||', '||
  C. last_name WHEN NOT MATCHED THEN INSERT VALUES (e.employee_id,
  D. first_name ||', '||e.last_name);
  E. 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);
  F. MERGE INTO new_employees c USING employees e ON (c.employee_id =
  G. employee_id) WHEN EXISTS THEN UPDATE SET c.name = e.first_name ||', '||
  H. last_name WHEN NOT MATCHED THEN INSERT VALUES(e.employee_id,
  I. first_name ||', '||e.last_name);
  J. 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,
  A. MERGE INTO new_employees c USING employees e ON (c.employee_id =

  ---

  The correct statement for MERGE is MERGE INTO table_name Incorrect answer:

  B. Wrong statement with the keyword EXISTS

  C. Wrong statement with the keyword EXISTS

  D. Wrong statement on the MERGE new_employees Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 8-29

• Question 143:

  Examine the structure of the MARKS table:

  Exhibit:

  

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

  A. SELECT student_name, subject1FROM marksWHERE subject1 > AVG(subject1);
  B. SELECT student_name, SUM(subject1)FROM marksWHERE student_name LIKE 'R%';
  C. SELECT SUM(subject1+subject2+subject3)FROM marksWHERE student_name IS NULL;
  D. SELECT SUM(DISTINCT NVL(subject1, 0)), MAX(subject1)FROM marksWHERE subject1 > subject2;
  C. SELECT SUM(subject1+subject2+subject3)FROM marksWHERE student_name IS NULL;
  D. SELECT SUM(DISTINCT NVL(subject1, 0)), MAX(subject1)FROM marksWHERE subject1 > subject2;

• Question 144:

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

  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?

  A. DELETEFROM productsWHERE prod_id = (SELECT prod_idFROM salesWHERE time_id - 3*365 = SYSDATE );
  B. DELETEFROM productsWHERE prod_id = (SELECT prod_idFROM salesWHERE SYSDATE >= time_id - 3*365 );
  C. DELETEFROM productsWHERE prod_id IN (SELECT prod_idFROM salesWHERE SYSDATE - 3*365 >= time_id);
  D. DELETEFROM productsWHERE prod_id IN (SELECT prod_idFROM salesWHERE time_id >= SYSDATE - 3*365 );
  C. DELETEFROM productsWHERE prod_id IN (SELECT prod_idFROM salesWHERE SYSDATE - 3*365 >= time_id);

• Question 145:

  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.
  B. It gives an error because the HAVING clause is not valid.

  ---

  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.

• Question 146:

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

  

  Using the products table, you issue the following query to generate the names, current list price, and discounted list price for all those products whose list price falls below $10 after a discount of 25% is applied on it.

  

  The query generates an error. What is the reason for the error?

  A. The parenthesis should be added to enclose the entire expression.
  B. The double quotation marks should be removed from the column alias.
  C. The column alias should be replaced with the expression in the where clause.
  D. The column alias should be put in uppercase and enclosed within double quotation marks in the where clause.
  C. The column alias should be replaced with the expression in the where clause.

• Question 147:

  View the Exhibit and examine the structure and data in the INVOICE table.

  

  Which two statements are true regarding data type conversion in expressions used in queries? (Choose two.)

  A. inv_amt ='0255982': requires explicit conversion
  B. inv_date > '01-02-2008': uses implicit conversion
  C. CONCAT (inv_amt, inv_date): requires explicit conversion
  D. inv_date = '15-february-2008': uses implicit conversion
  E. inv_no BETWEEN '101' AND '110': uses implicit conversion
  D. inv_date = '15-february-2008': uses implicit conversion
  E. inv_no BETWEEN '101' AND '110': uses implicit conversion

• Question 148:

  Which four are attributes of single row functions? (Choose four.)

  A. cannot be nested
  B. manipulate data items
  C. act on each row returned
  D. return one result per row
  E. accept only one argument and return only one value
  F. accept arguments which can be a column or an expression
  B. manipulate data items
  C. act on each row returned
  D. return one result per row
  F. accept arguments which can be a column or an expression

  ---

  Manipulate data items, act on each row returned, return one result per row, and accept arguments that can be a column or expression.

  Incorrect answer:

  A. is not single row attributes

  E. functions can accept more than one argument, e.g NVL2 Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 3-5

• Question 149:

  Which three statements are true about the ALTER TABLE .... DROP COLUMN .... command?

  A. A column can be dropped only if it does not contain any data.
  B. A column can be dropped only if another column exists in the table.
  C. A dropped column can be rolled back.
  D. The column in a composite PRIMARY KEY with the CASCADE option can be dropped.
  E. A parent key column in the table cannot be dropped.
  B. A column can be dropped only if another column exists in the table.
  D. The column in a composite PRIMARY KEY with the CASCADE option can be dropped.
  E. A parent key column in the table cannot be dropped.

• Question 150:

  Examine the structure proposed for the transactions table:

  

  Which two statements are true regarding the creation and storage of data in the above table structure?

  A. The CUST_STATUS column would give an error.
  B. The TRANS_VALIDITY column would give an error.
  C. The CUST_STATUS column would store exactly one character.
  D. The CUST_CREDIT_LIMIT column would not be able to store decimal values.
  E. The TRANS_VALIDITY column would have a maximum size of one character.
  F. The TRANS_DATE column would be able to store day, month, century, year, hour, minutes, seconds, and fractions of seconds
  B. The TRANS_VALIDITY column would give an error.
  C. The CUST_STATUS column would store exactly one character.

  ---

  VARCHAR2(size)Variable-length character data (A maximum size must be specified:

  minimum size is 1; maximum size is 4, 000.)

  CHAR [(size)] Fixed-length character data of length size bytes (Default and minimum size is 1; maximum size is 2, 000.)

  NUMBER [(p, s)] Number having precision p and scale s (Precision is the total number of decimal digits and scale is the number of digits to the right of the decimal point; precision can range from 1 to 38, and scale can range from ?4 to 127.)

  DATE Date and time values to the nearest second between January 1, 4712 B.C., and December 31, 9999 A.D.