[Free] 2019(Nov) EnsurePass Oracle 1z0-061 Dumps with VCE and PDF 181-190

Get Full Version of the Exam
http://www.EnsurePass.com/1z0-061.html

Question No.181

Which is a valid CREATE TABLE statement?

  1. CREATE TABLE EMP9$# AS (empid number(2));

  2. CREATE TABLE EMP*123 AS (empid number(2));

  3. CREATE TABLE PACKAGE AS (packid number(2));

  4. CREATE TABLE 1EMP_TEST AS (empid number(2));

Correct Answer: A

Explanation:

Table names and column names must begin with a letter and be 1-30 characters long. Characters A-Z, a-z, 0-9, _, $ and # (legal characters but their use is discouraged).

Incorrect answer:

B. Non alphanumeric character such as quot;*quot; is discourage in Oracle table name.

D. Table name must begin with a letter.

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

Question No.182

Which statement describes the ROWID data type?

  1. Binary data up to 4 gigabytes.

  2. Character data up to 4 gigabytes.

  3. Raw binary data of variable length up to 2 gigabytes.

  4. Binary data stored in an external file, up to 4 gigabytes.

  5. A hexadecimal string representing the unique address of a row in its table.

Correct Answer: E

Explanation:

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 No.183

Which SQL statement accepts user input for the columns to be displayed, the table name, and WHERE condition?

  1. SELECT amp;1, quot;amp;2quot;FROM amp;3WHERE last_name = #39;amp;4#39;;

  2. SELECT amp;1, #39;amp;2#39;FROM amp;3WHERE #39;amp;last_name = #39;amp;4#39; #39;;

  3. SELECT amp;1, amp;2FROM amp;3WHERE last_name = #39;amp;4#39;;

  4. SELECT amp;1, #39;amp;2#39;FROM EMPWHERE last_name = #39;amp;4#39;;

Correct Answer: C

Explanation:

In a WHERE clause, date and characters values must be enclosed within single quotation marks. Sample of the correct syntax

SELECT EMPLOYEE_ID, amp;COLUMN_NAME FROM EMPLOYEES

Incorrect Answers :

  1. Incorrect use of quot; symbol

  2. Incorrect use of #39; symbol

D. No input for table name as EMP has been use in the statement.

Refer: Introduction to Oracle9i: SQL, Oracle University Student Guide, Producing Readable Output with iSQL*PLUS, p. 7-8

Question No.184

The EMPLOYEES table contains these columns: EMPLOYEE_ID NUMBER(4)

ENAME VARCHAR2 (25) JOB_ID VARCHAR2(10)

Which SQL statement will return the ENAME, length of the ENAME, and the numeric position of the letter quot;aquot; in the ENAME column, for those employees whose ENAME ends with a the letter quot;nquot;?

  1. SELECT ENAME, LENGTH(ENAME), INSTR(ENAME, #39;a#39;) FROM EMPLOYEES WHERE SUBSTR(ENAME, -1, 1) = #39;n#39;;

  2. SELECT ENAME, LENGTH(ENAME), INSTR(ENAME, , -1, 1) FROM EMPLOYEES WHERE SUBSTR(ENAME, -1, 1) = #39;n#39;;

  3. SELECT ENAME, LENGTH(ENAME), SUBSTR(ENAME, -1, 1) FROM EMPLOYEES WHERE INSTR(ENAME, 1, 1) = #39;n#39;;

  4. SELECT ENAME, LENGTH(ENAME), SUBSTR(ENAME, -1, 1) FROM EMPLOYEES WHERE INSTR(ENAME, -1, 1) = #39;n#39;;

Correct Answer: A

Explanation:

INSTR is a character function return the numeric position of a named string. INSTR(NAMED, #39;a#39;)

Incorrect answer:

  1. Did not return a numeric position for `a#39;.

  2. Did not return a numeric position for `a#39;.

  3. Did not return a numeric position for `a#39;.

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

Question No.185

Which statement is true regarding the COALESCE function?

  1. It can have a maximum of five expressions in a list.

  2. It returns the highest NOT NULL value in the list for all rows.

  3. It requires that all expressions in the list must be of the same data type.

  4. It requires that at least one of the expressions in the list must have a NOT NULL value.

Correct Answer: C

Explanation:

The COALESCE Function

The COALESCE function returns the first nonnull value from its parameter list. If all its parameters are null, then null is returned.

The COALESCE function takes two mandatory parameters and any number of optional parameters. The syntax is COALESCE(expr1, expr2, …, exprn), where expr1 is returned if it is not null, else expr2 if it is not null, and so on. COALESCE is a general form of the NVL function, as the following two equations illustrate:

COALESCE(expr1, expr2) = NVL(expr1, expr2)

COALESCE(expr1, expr2, expr3) = NVL(expr1, NVL(expr2, expr3)) The data type COALESCE returns if a not null value is found is the same as that of the first not null parameter.

To avoid an quot;ORA-00932: inconsistent data typesquot; error, all not null parameters must have data types compatible with the first not null parameter.

Question No.186

Which is an iSQL*Plus command?

  1. INSERT

  2. UPDATE

  3. SELECT

  4. DESCRIBE

  5. DELETE

  6. RENAME

Correct Answer: D

Explanation:

The only SQL*Plus command in this list : DESCRIBE. It cannot be used as SQL command. This command returns a description of table name, 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:

  1. INSERT is not a SQL*PLUS command

  2. UPDATE is not a SQL*PLUS command

  3. SELECT is not a SQL*PLUS command

  1. DELETE is not a SQL*PLUS command

  2. RENAME is not a SQL*PLUS command

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

Question No.187

Examine the structure and data in the PRIC E_LIST table: Name Null? Type

– – – PROD_D NOT NULL NUMBER(3) PROD_PRICE VARCHAR2(10) PROD_ID PROD PRICE

100 $234.55

101 $6, 509.75

102 $1, 234

in the same format as the PROD_PRICE.

Which SQL statement would give the required result?

  1. SELECT TO_CHAR(prod_price* .25.#39;$99.999.99#39;) FROM PRICEJLIST:

  2. . SELECT TO_CHAR(TO_NUMBER(prod_price)* .25.#39;$99.999.00#39;) FROM PRICE_LIST;

  3. SELECT TO_CRAR(TO_NUMBER(prod_price.#39;S99.999.99#39;)* .25.#39;$99.999.00#39;) FROM PRICE_LIST:

  4. SELECT TO_NUMBER(TO_NUMBER(prod_price., $99.999.99#39;)* .25/$99.999.00#39;) FROM PRICE_LIST:

Correct Answer: C

Question No.188

Which is the valid CREATE [TABLE statement?

  1. CREATE TABLE emp9$# (emp_no NUMBER(4));

  2. CREATE TABLE 9emp$# (emp_no NUMBER(4));

  3. CREATE TABLE emp*123 (emp_no NUMBER(4));

  4. CREATE TABLE emp9$# (emp_no NUMBER(4). date DATE);

Correct Answer: A

Explanation:

Schema Object Naming Rules

Every database object has a name. In a SQL statement, you represent the name of an object with a quoted identifier or a nonquoted identifier. A quoted identifier begins and ends with double quotation marks (quot;). If you name a schema object using a quoted identifier, then you must use the double quotation marks whenever you refer to that object.

A nonquoted identifier is not surrounded by any punctuation. The following list of rules applies to both quoted and nonquoted identifiers unless otherwise indicated:

Names must be from 1 to 30 bytes long with these exceptions: Names of databases are limited to 8 bytes.

Names of database links can be as long as 128 bytes. If an identifier includes multiple parts separated by periods, then each attribute can be up to 30 bytes long.

Each period separator, as well as any surrounding double quotation marks, counts as one byte. For example, suppose you identify a column like this:

quot;schemaquot;.quot;tablequot;.quot;columnquot;

Nonquoted identifiers cannot be Oracle Database reserved words (ANSWER D). Quoted identifiers can be reserved words, although this is not recommended. Depending on the Oracle product you plan to use to access a database object, names might be further restricted by other product-specific reserved words. The Oracle SQL language contains other words that have special meanings. These words include datatypes, schema names, function names, the dummy system table DUAL, and keywords (the uppercase words in SQL statements, such as DIMENSION, SEGMENT, ALLOCATE, DISABLE, and so forth). These words are not reserved. However, Oracle uses them internally in specific ways. Therefore, if you use these words as names for objects and object parts, then your SQL statements may be more difficult to read and may lead to unpredictable results.

In particular, do not use words beginning with SYS_ as schema object names, and do not use the

names of SQL built-in functions for the names of schema objects or user-defined functions.

You should use ASCII characters in database names, global database names, and database link names, because ASCII characters provide optimal compatibility across different platforms and operating systems.

Nonquoted identifiers must begin with an alphabetic character (ANSWER B – begins with 9) from your database character set. Quoted identifiers can begin with any character. Nonquoted identifiers can contain only alphanumeric characters from your database character set and the underscore (_), dollar sign ($), and pound sign (#). Database links can also contain periods (.) and quot;atquot; signs (@). Oracle strongly discourages you from using $ and # in nonquoted identifiers. Quoted identifiers can contain any characters and punctuations marks as well as spaces.

However, neither quoted nor nonquoted identifiers can contain double quotation marks or the null character (\0).

Within a namespace, no two objects can have the same name. Nonquoted identifiers are not case sensitive. Oracle interprets them as uppercase. Quoted identifiers are case sensitive. By enclosing names in double quotation marks, you can give the following names to different objects in the same namespace:

employees quot;employeesquot; quot;Employeesquot; quot;EMPLOYEESquot;

Note that Oracle interprets the following names the same, so they cannot be used for different objects in the same namespace:

employees EMPLOYEES quot;EMPLOYEESquot;

Columns in the same table or view cannot have the same name. However, columns in different tables or views can have the same name.

Procedures or functions contained in the same package can have the same name, if their arguments are not of the same number and datatypes. Creating multiple procedures or functions with the same name in the same package with different arguments is called overloading the procedure or function.

Question No.189

Examine the description of the EMPLOYEES table: EMP_ID NUMBER(4) NOT NULL

LAST_NAME VARCHAR2(30) NOT NULL FIRST_NAME VARCHAR2(30)

DEPT_ID NUMBER(2)

Which statement produces the number of different departments that have employees with last name Smith?

  1. SELECT COUNT(*) FROM employees WHERE last_name=#39;Smith#39;;

  2. SELECT COUNT (dept_id) FROM employees WHERE last_name=#39;Smith#39;;

  3. SELECT DISTINCT(COUNT(dept_id)) FROM employees WHERE last_name=#39;Smith#39;;

  4. SELECT COUNT(DISTINCT dept_id) FROM employees WHERE last_name=#39;Smith#39;;

  5. SELECT UNIQUE(dept_id) FROM employees WHERE last_name=#39;Smith#39;;

Correct Answer: D

Question No.190

View the Exhibit and examine the structure of the PROMOTIONS table. Which SQL statements are valid? (Choose all that apply.)

image

  1. SELECT promo_id. DECODE(NVL(promo_cost.O).promo_cost * 0.25. 100) quot;Discountquot;FROM promotions;

  2. SELECT promo id. DECODE(promo_cost. 10000.DECODE(promo_category. #39;Gl\ promo_cost *

    25. NULL). NULL) quot;Catcostquot; FROM promotions;

  3. SELECT promo_id. DECODE(NULLIF(promo_cost. 10000). NULL. promo_cost*.25, *N/A#39;) quot;Catcostquot;FROM promotions;

  4. SELECT promo_id. DECODE(promo_cost. gt;10000. #39;High#39;. lt;10000. #39;Low#39;) quot;Rangequot;FROM promotions;

Correct Answer: AB

Get Full Version of the Exam
1z0-061 Dumps
1z0-061 VCE and PDF