Oracle 1Z0-061 Online Practice Questions and Exam Preparation

Oracle 1Z0-061 Online Questions & Answers

• Question 51:

  Evaluate the following CREATE SEQUENCE statement:

  CREATE SEQUENCE seq1

  START WITH 100

  INCREMENT BY 10 MAXVALUE 200

  CYCLE

  NOCACHE;

  The SEQ1 sequence has generated numbers up to the maximum limit of 200. You issue the following SQL statement:

  SELECT seq1.nextval FROM dual;

  What is displayed by the SELECT statement?

  A. 1
  B. 10
  C. 100
  D. an error
  A. 1

  ---

  But why the answer is not "C" ?

  Because you didn't specify the MINVALUE for the sequence. If you check the sequence definition that you created it will have the default value of 1, which it reverts to when cycling.

  If you wanted to keep the minimum value you would need to specify it in the sequence creation.

  sequence Is the name of the sequence generator

  INCREMENT BY n Specifies the interval between sequence numbers, where n is an integer (If this clause is omitted, the sequence increments by 1.) START WITH n Specifies the first sequence number to be generated (If this clause is

  omitted, the sequence starts with 1.)

  MAXVALUE n Specifies the maximum value the sequence can generate NOMAXVALUE Specifies a maximum value of 10^27 for an ascending sequence and ? for a descending sequence (This is the default option.) MINVALUE n Specifies

  the minimum sequence value

  NOMINVALUE Specifies a minimum value of 1 for an ascending sequence and ?10^26) for a descending sequence (This is the default option.) 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.)

• Question 52:

  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?

  A. Column definitions cannot be altered to add DEFAULT values.
  B. A change to the DEFAULT value affects only subsequent insertions to the table.
  C. Column definitions cannot be altered at add DEFAULT values for columns with a NUMBER data type.
  D. All the rows that have a NULL value for the SALARY column will be updated with the value 5000.
  B. A change to the DEFAULT value affects only subsequent insertions to the table.

  ---

  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 53:

  View the Exhibits and examine the structures of the COSTS and PROMOTIONS tables.

  Exhibit 1.

  

  Exhibit 2.

  

  Evaluate the following SQL statement:

  

  What would be the outcome of the above SQL statement?

  A. It displays prod IDs in the promo with the lowest cost.
  B. It displays prod IDs in the promos with the lowest cost in the same time interval.
  C. It displays prod IDs in the promos with the highest cost in the same time interval.
  D. It displays prod IDs in the promos with cost less than the highest cost in the same time interval.
  D. It displays prod IDs in the promos with cost less than the highest cost in the same time interval.

• Question 54:

  Evaluate the following SQL commands:

  

  The command to create a table fails. Identify the two reasons for the SQL statement failure?

  A. You cannot use SYSDATE in the condition of a check constraint.
  B. You cannot use the BETWEEN clause in the condition of a CHECK constraint.
  C. You cannot use the NEXTVAL sequence value as a DEFAULT value for a column.
  D. You cannot use ORD_NO and ITEM_NO columns as a composite primary key because ORD_NO is also the FOREIGN KEY.
  A. You cannot use SYSDATE in the condition of a check constraint.
  C. You cannot use the NEXTVAL sequence value as a DEFAULT value for a column.

  ---

  CHECK Constraint

  The CHECK constraint defines a condition that each row must satisfy. The condition can use the same constructs as the query conditions, with the following exceptions:

  References to the CURRVAL, NEXTVAL, LEVEL, and ROWNUM pseudocolumns Calls to SYSDATE, UID, USER, and USERENV functions Queries that refer to other values in other rows A single column can have multiple CHECK

  constraints that refer to the column in its definition.

  There is no limit to the number of CHECK constraints that you can define on a column. CHECK constraints can be defined at the column level or table level.

  CREATE TABLE employees

  (...

  Salary NUMBER(8, 2) CONSTRAINT emp_salary_min

  CHECK (salary > 0),

• Question 55:

  Sales data of a company is stored in two tables, SALES1 and SALES2, with some data being duplicated across the tables. You want to display the results the results from the SALES1 table, which are not present in the SALES2 table.

  

  Which set operator generates the required output?

  A. UNION
  B. INTERSECT
  C. PLUS
  D. MINUS
  E. SUBSTRACT
  D. MINUS

• Question 56:

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

  A. A table can have only one primary key and one foreign key.
  B. A table can have only one primary key but multiple foreign keys.
  C. Only the primary key can be defined at the column and table levels.
  D. The foreign key and parent table primary key must have the same name.
  E. Both primary key and foreign key constraints can be defined at both column and table levels.
  B. A table can have only one primary key but multiple foreign keys.
  E. Both primary key and foreign key constraints can be defined at both column and table levels.

• Question 57:

  Which are iSQL*Plus commands? (Choose all that apply.)

  A. INSERT
  B. UPDATE
  C. SELECT
  D. DESCRIBE
  E. DELETE
  F. RENAME
  D. DESCRIBE

  ---

  The only SQL*Plus command in this list : DESCRIBE. It cannot be used as SQL command. This command returns a description of tablename, including all columns in that table, the datatype for each column and an indication of whether the column permits storage of NULL values.

  Incorrect answer:

  A. INSERT is not a SQL*PLUS command

  B. UPDATE is not a SQL*PLUS command

  C. SELECT is not a SQL*PLUS command

  E. DELETE is not a SQL*PLUS command

  F. RENAME is not a SQL*PLUS command

  Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 7

• Question 58:

  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?

  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

  ---

  In some cases, the Oracle server receives data of one data type where it expects data of a different data type.

  When this happens, the Oracle server can automatically convert the data to the expected data type. This data type conversion can be done implicitly by the Oracle server or explicitly by the user.

  Explicit data type conversions are performed by using the conversion functions. Conversion functions convert a value from one data type to another. Generally, the form of the function names follows the convention data type TO data type.

  The first data type is the input data type and the second data type is the output.

  Note: Although implicit data type conversion is available, it is recommended that you do the explicit data type conversion to ensure the reliability of your SQL statements.

• Question 59:

  Which describes the default behavior when you create a table?

  A. The table is accessible to all users.
  B. Tables are created in the public schema.
  C. Tables are created in your schema.
  D. Tables are created in the DBA schema.
  E. You must specify the schema when the table is created.
  C. Tables are created in your schema.

  ---

  Sorted by highest to lowest is DESCENDING order

  Incorrect answer:

  A. grant the table privilege to PUBLIC

  B. login as sysoper

  D. login as DBA or sysdba

  E. no such option is allow.

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

• Question 60:

  The PRODUCTS table has these columns:

  PRODUCT_ID NUMBER(4) PRODUCT_NAME VARCHAR2(45)

  PRICE NUMBER(8, 2)

  Evaluate this SQL statement:

  SELECT *

  FROM PRODUCTS

  ORDER BY price, product_name;

  What is true about the SQL statement?

  A. The results are not sorted.
  B. The results are sorted numerically.
  C. The results are sorted alphabetically.
  D. The results are sorted numerically and then alphabetically.
  D. The results are sorted numerically and then alphabetically.

  ---

  the result is sort by price which is numeric and follow by product_name which is alphabetically.

  Incorrect answer:

  A. the results are sorted

  B. the results are sorted with alphabetically as well C. the results are sorted with numerically as well

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