Oracle 1Z0-061 Online Practice Questions and Exam Preparation

Oracle 1Z0-061 Online Questions & Answers

• Question 171:

  Examine the structure proposed for the TRANSACTIONS table:

  

  Which two statements are true regarding the storage of data in the above table structure? (Choose two.)

  A. The TRANS_DATE column would allow storage of dates only in the dd-mon-yyyy format.
  B. The CUST_CREDIT_VALUE column would allow storage of positive and negative integers.
  C. The TRANS_VALIDITY column would allow storage of a time interval in days, hours, minutes, and seconds.
  D. The CUST_STATUS column would allow storage of data up to the maximum VARCHAR2 size of 4, 000 characters.
  B. The CUST_CREDIT_VALUE column would allow storage of positive and negative integers.
  D. The CUST_STATUS column would allow storage of data up to the maximum VARCHAR2 size of 4, 000 characters.

  ---

  B: The NUMBER datatype stores fixed and floating-point numbers. Numbers of virtually any magnitude can be stored and are guaranteed portable among different systems operating Oracle, up to 38 digits of precision.

  The following numbers can be stored in a NUMBER column:

  Positive numbers in the range 1 x 10-130 to 9.99...9 x 10125 with up to 38 significant digits Negative numbers from -1 x 10-130 to 9.99...99 x 10125 with up to 38 significant digits Zero

  Positive and negative infinity (generated only by importing from an Oracle Version 5 database)

  D: The VARCHAR2 datatype stores variable-length character strings. When you create a table with a VARCHAR2 column, you specify a maximum string length (in bytes or characters) between 1 and 4000 bytes for the VARCHAR2 column.

  An interval literal specifies a period of time, and Oracle supports two types of interval literals: YEAR_TO_MONTH and DAY TO SECOND. For DAY TO SECOND, you can specify these differences in terms in terms of days, hours, minutes,

  and seconds. DAY TO SECOND contains a leading field and may contain an optional trailing field. If trailing field is specified it must be less significant than the leading field. For example, INTERVAL MINUTE TO DAY is not valid.

  A DAY TO MINUTE interval considers an interval of days to the nearest minute. References:

• Question 172:

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

  A. Create a table view.
  B. Create a view in any schema.
  C. Create a view in your schema.
  D. Create a sequence view in any schema.
  E. Create a view that is accessible by everyone.
  F. Create a view only of it is based on tables that you created.
  C. Create a view in your schema.

  ---

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

  Examine the data in the PROMO_BEGIN_DATE column of the promotions table:

  

  You want to display the number of promotions started in 1999 and 2000. Which query gives the correct output?

  

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

• Question 174:

  Examine the structure of the PROMOS table: You want to display the list of promo names with the message 'Same Day' for promos that started and ended on the same day. Which query gives the correct output?

  

  A. SELECT promo_name, NVL(NULLIF(promo_start_date, promo_end_date), 'Same Day') FROM promos;
  B. SELECT promo_name, NVL(TRUNC(promo_end_date - promo_start_date), 'Same Day') FROM promos;
  C. SELECT promo_name, NVL2(TO_CHAR(TRUNC(promo_end_date-promo_start_date)), NULL, 'Same Day')FROM promos;
  D. SELECT promo_name, DECODE((NULLIF(promo_start_date, promo_end_date)), NULL, 'Same day') FROM promos;
  D. SELECT promo_name, DECODE((NULLIF(promo_start_date, promo_end_date)), NULL, 'Same day') FROM promos;

  ---

  The NULLIF Function The NULLIF function tests two terms for equality. If they are equal the function returns a null, else it returns the first of the two terms tested. The NULLIF function takes two mandatory parameters of any data type. The syntax is NULLIF (ifunequal, comparison_term), where the parameters ifunequal and comparison_term are compared. If they are identical, then NULL is returned. If they differ, the ifunequal parameter is returned ANSWER A - date and String incompatibl;a datatypes for NVL function The Date TRUNC Function The date TRUNC function performs a truncation operation on a date value based on a specified date precision format. The date TRUNC function takes one mandatory and one optional parameter. Its syntax is TRUNC(source date, [date precision format]). The source date parameter represents any value that can be implicitly converted into a date item. The date precision format parameter specifies the degree of truncation and is optional. If it is absent, the default degree of truncation is day. This means that any time component

• Question 175:

  Which constraint can be defined only at the column level?

  A. UNIQUE
  B. NOT NULL
  C. CHECK
  D. PRIMARY KEY
  E. FOREIGN KEY
  B. NOT NULL

  ---

  the NOT NULL constraint can be specified only at the column level, not at the table level.

  Incorrect answer:

  A. UNIQUE can be define at table level

  C. CHECK can be define at table level

  D. PRIMARY KEY can be define at table level

  E. FOREIGN KEY can be define at table level

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

• Question 176:

  Examine the data in the CUST_NAME column of the customers table.

  

  You need to display customers' second names where the second name starts with "Mc" or "MC." Which query gives the required output?

  

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

• Question 177:

  Evaluate this SQL statement:

  SELECT ename, sal, 12*sal+100 FROM emp;

  The SAL column stores the monthly salary of the employee. Which change must be made to the above syntax to calculate the annual compensation as "monthly salary plus a monthly bonus of $100, multiplied by 12"?

  A. No change is required to achieve the desired results.
  B. SELECT ename, sal, 12*(sal+100) FROM emp;
  C. SELECT ename, sal, (12*sal)+100 FROM emp;
  D. SELECT ename, sal+100, *12 FROM emp;
  B. SELECT ename, sal, 12*(sal+100) FROM emp;

  ---

  to achieve the result you must add 100 to sal before multiply with 12. Select ename, sal, 12*(sal+100) from EMP;

  Incorrect answer:

  A. Multiplication and division has priority over addition and subtraction in Operator precedence.

  C. Give wrong results

  D. Wrong syntax Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 1-11

• Question 178:

  You need to produce a report where each customer's credit limit has been incremented by $1000. In the output, the customer's last name should have the heading Name and the incremented credit limit should be labeled New Credit Limit. The column headings should have only the first letter of each word in uppercase.

  Which statement would accomplish this requirement?

  

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

  ---

  A column alias:

  -Renames a column heading

  -Is useful with calculations

  -

  Immediately follows the column name (There can also be the optional AS keyword between the column name and the alias.)

  -

  Requires double quotation marks if it contains spaces or special characters, or if it is case sensitive.

• Question 179:

  View the Exhibit to examine the description for the SALES table. Which views can have all DML operations performed on it? (Choose all that apply.)

  

  A. CREATE VIEW v3AS SELECT * FROM SALESWHERE cust_id = 2034WITH CHECK OPTION;
  B. CREATE VIEW v1AS SELECT * FROM SALESWHERE time_id
  C. CREATE VIEW v2AS SELECT prod_id, cust_id, time_id FROM SALESWHERE time_id
  D. CREATE VIEW v4AS SELECT prod_id, cust_id, SUM(quantity_sold) FROM SALESWHERE time_id
  A. CREATE VIEW v3AS SELECT * FROM SALESWHERE cust_id = 2034WITH CHECK OPTION;
  B. CREATE VIEW v1AS SELECT * FROM SALESWHERE time_id

  ---

  Creating a View

  You can create a view by embedding a subquery in the CREATE VIEW statement.

  In the syntax:

  CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW view

  [(alias[, alias]...)]

  AS subquery

  [WITH CHECK OPTION [CONSTRAINT constraint]]

  [WITH READ ONLY [CONSTRAINT constraint]];

  OR REPLACE Re-creates the view if it already exists FORCE Creates the view regardless of whether or not the base tables exist NOFORCE Creates the view only if the base tables exist (This is the default.) View Is the name of the view alias Specifies names for the expressions selected by the view's query (The number of aliases must match the number of expressions selected by the view.) subquery Is a complete SELECT statement (You can use aliases for the columns in the SELECT list.) WITH CHECK OPTION Specifies that only those rows that are accessible to the view can be inserted or updated ANSWER D constraint Is the name assigned to the CHECK OPTION constraint WITH READ ONLY Ensures that no DML operations can be performed on this view Rules for Performing DML Operations on a View You cannot add data through a view if the view includes: Group functions A GROUP BY clause The DISTINCT keyword The pseudocolumn ROWNUM keyword Columns defined by expressions NOT NULL columns in the base tables that are not selected by the view ?ANSWER C

• Question 180:

  You want to display 5 percent of the rows from the sales table for products with the lowest AMOUNT_SOLD and also want to include the rows that have the same AMOUNT_SOLD even if this causes the output to exceed 5 percent of the rows.

  Which query will provide the required result?

  

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

  ---

  The FETCH statement must include WITH TIES.

  Incorrect:

  Not B: You cannot use ROWS WITH in a FETCH statement.