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

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

Question No.101

Examine the structure of the EMPLOYEES table:

image

Which INSERT statement is valid?

  1. INSERT INTO employees (employee_id, first_name, last_name, hire_date)VALUES ( 1000,

    `John#39;, `Smith#39;, `01/01/01#39;);

  2. INSERT INTO employees(employee_id, first_name, last_name, hire_date)VALUES ( 1000, `John#39;,

    `Smith#39;, #39;01 January 01#39;);

  3. INSERT INTO employees(employee_id, first_name, last_name, Hire_date)VALUES ( 1000,

    `John#39;, `Smith#39;, To_date(`01/01/01#39;));

  4. INSERT INTO employees(employee_id, first_name, last_name, hire_date)VALUES ( 1000, `John#39;,

`Smith#39;, 01-Jan-01);

Correct Answer: D

Explanation:

It is the only statement that has a valid date; all other will result in an error. Answer A is incorrect, syntax error, invalid date format

Question No.102

Evaluate these two SQL statements:

SELECT last_name, salary, hire_date FROM EMPLOYEES ORDER BY salary DESC; SELECT last_name, salary, hire_date FROM EMPLOYEES ORDER BY 2 DESC; What is true about them?

  1. The two statements produce identical results.

  2. The second statement returns a syntax error.

  3. There is no need to specify DESC because the results are sorted in descending order by default.

  4. The two statements can be made to produce identical results by adding a column alias for the salary column in the second SQL statement.

Correct Answer: A

Explanation:

the two statement produce identical results as ORDER BY 2 will take the second column as sorting column.

Incorrect answer:

  1. there is no syntax error

  2. result are sorted in ascending order by default

  3. ORDER BY 2 will take the second column as sorting column. Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 2-22

Question No.103

View the Exhibit and examine the structure of the CUSTOMERS and GRADES tables:

image

You need to display names and grades of customers who have the highest credit limit. Which two SQL statements would accomplish the task? (Choose two.)

  1. SELECT custname, gradeFROM customers, gradesWHERE (SELECT MAX(cust_credit_limit)FROM customers) BETWEEN startval and endval;

  2. SELECT custname, gradeFROM customers, gradesWHERE (SELECT MAX(cust_credit_limit)FROM customers) BETWEEN startval and endvalAND cust_credit_limit BETWEEN startval AND endval;

  3. SELECT custname, gradeFROM customers, gradesWHERE cust_credit_limit = (SELECT MAX(cust_credit_limit)FROM customers)AND cust_credit_limit BETWEEN startval AND endval;

  4. SELECT custname, gradeFROM customers , gradesWHERE cust_credit_limit IN (SELECT MAX(cust_credit_limit)FROM customers)AND MAX(cust_credit_limit) BETWEEN startval AND endval;

Correct Answer: BC

Question No.104

Which three statements are true regarding the data types in Oracle Database 10g/11g? (Choose three.)

  1. The BLOB data type column is used to store binary data in an operating system file

  2. The minimum column width that can be specified for a VARCHAR2 data type column is one

  3. A TIMESTAMP data type column stores only time values with fractional seconds

  4. The value for a CHAR data type column is blank-padded to the maximum defined column width

  5. Only One LONG column can be used per table

Correct Answer: BDE

Explanation:

LONG Character data in the database character set, up to 2GB. All the functionality of LONG (and more) is provided by CLOB; LONGs should not be used in a modern database, and if your database has any columns of this type they should be converted to CLOB.

There can only be one LONG column in a table.

DVARCHAR2 Variable-length character data, from 1 byte to 4KB. The data is stored in the database character set. The VARCHAR2 data type must be qualified with a number indicating the maximum length of the column.

If a value is inserted into the column that is less than this, it is not a problem: the value will only take up as much space as it needs. If the value is longer than this maximum, the INSERT will fail with an error. VARCHAR2(size)

Variable-length character data (A maximum size must be specified: minimum size is 1; maximum size is 4, 000.)

BLOB Like CLOB, but binary data that will not undergo character set conversion by Oracle Net. BFILE A locator pointing to a file stored on the operating system of the database server.

The size of the files is limited to 4GB.

TIMESTAMP This is length zero if the column is empty, or up to 11 bytes, depending on the precision specified.

Similar to DATE, but with precision of up to 9 decimal places for the seconds, 6 places by default.

Question No.105

You are currently located in Singapore and have connected to a remote database in Chicago. You issue the following command:

image

PROMOTIONS is the public synonym for the public database link for the PROMOTIONS table. What is the outcome?

  1. Number of days since the promo started based on the current Singapore data and time.

  2. An error because the ROUND function specified is invalid

  3. An error because the WHERE condition specified is invalid

  4. Number of days since the promo started based on the current Chicago data and time

Correct Answer: D

Question No.106

Which four are types of functions available in SQL? (Choose 4)

  1. string

  2. character

  3. integer

  4. calendar

  5. numeric

  6. translation

  7. date

  8. conversion

Correct Answer: BEGH

Explanation:

SQL have character, numeric, date, conversion function.

Incorrect answer:

A. SQL have character, numeric, date, conversion function.

  1. SQL have character, numeric, date, conversion function.

  2. SQL have character, numeric, date, conversion function.

F. QL have character, numeric, date, conversion function.

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

Question No.107

Which two statements about sub queries are true? (Choose two.)

  1. A sub query should retrieve only one row.

  2. A sub query can retrieve zero or more rows.

  3. A sub query can be used only in SQL query statements.

  4. Sub queries CANNOT be nested by more than two levels.

  5. A sub query CANNOT be used in an SQL query statement that uses group functions.

  6. When a sub query is used with an inequality comparison operator in the outer SQL statement, the column list in the SELECT clause of the sub query should contain only one column.

Correct Answer: BF

Explanation:

sub query can retrieve zero or more rows, sub query is used with an inequality comparison operator in the outer SQL statement, and the column list in the SELECT clause of the sub query should contain only one column.

Incorrect answer:

A. sub query can retrieve zero or more rows

  1. sub query is not SQL query statement

  2. sub query can be nested

  3. group function can be use with sub query

Question No.108

Examine the description of the EMP_DETAILS table given below:

image

Which two statements are true regarding SQL statements that can be executed on the EMP_DETAIL table? (Choose two.)

  1. An EMP_IMAGE column can be included in the GROUP BY clause

  2. You cannot add a new column to the table with LONG as the data type

  3. An EMP_IMAGE column cannot be included in the ORDER BY clause

  4. You can alter the table to include the NOT NULL constraint on the EMP_IMAGE column

Correct Answer: BC

Explanation:

LONG Character data in the database character set, up to 2GB. All the functionality of LONG (and more) is provided by CLOB; LONGs should not be used in a modern database, and if your database has any columns of this type they should be converted to CLOB.

There can only be one LONG column in a table. Guidelines

A LONG column is not copied when a table is created using a subquery. A LONG column cannot be included in a GROUP BY or an ORDER BY clause.

Only one LONG column can be used per table.

No constraints can be defined on a LONG column.

You might want to use a CLOB column rather than a LONG column.

Question No.109

View the Exhibit and examine the description for the CUSTOMERS table.

image

You want to update the CUST_INCOME_LEVEL and CUST_CREDIT_LIMIT columns for the customer with the CUST_ID 2360. You want the value for the CUST_INCOME_LEVEL to have the same value as that of the customer with the CUST_ID 2560 and the CUST_CREDIT_LIMIT to have the same value as that of the customer with CUST_ID 2566.

Which UPDATE statement will accomplish the task?

  1. UPDATE customersSET cust_income_level = (SELECT cust_income_levelFROM customersWHERE cust_id = 2560),cust_credit_limit = (SELECT cust_credit_limitFROM customersWHERE cust_id = 2566)WHERE cust_id=2360;

  2. UPDATE customersSET (cust_income_level, cust_credit_limit) = (SELECTcust_income_level, cust_credit_limitFROM customersWHERE cust_id=2560 OR cust_id=2566)WHERE cust_id=2360;

  3. UPDATE customersSET (cust_income_level, cust_credit_limit) = (SELECTcust_income_level, cust_credit_limitFROM customersWHERE cust_id IN(2560, 2566)WHERE cust_id=2360;

  4. UPDATE customersSET (cust_income_level, cust_credit_limit) = (SELECTcust_income_level, cust_credit_limitFROM customersWHERE cust_id=2560 AND cust_id=2566)WHERE cust_id=2360;

Correct Answer: A

Explanation:

Updating Two Columns with a Subquery

You can update multiple columns in the SET clause of an UPDATE statement by writing multiple subqueries. The syntax is as follows:

UPDATE table SET column = (SELECT column FROM table WHERE condition) [ ,

column = (SELECT column FROM table

WHERE condition)] [WHERE condition ] ;

Question No.110

Here is the structure and data of the CUST_TRANS table:

image

Dates are stored in the default date format dd-mm-rr in the CUST_TRANS table. Which three SQL statements would execute successfully? (Choose three.)

  1. SELECT transdate #39;10#39; FROM cust_trans;

  2. SELECT * FROM cust_trans WHERE transdate = #39;01-01-07#39;;

  3. SELECT transamt FROM cust_trans WHERE custno gt; #39;11#39;;

  4. SELECT * FROM cust_trans WHERE transdate=#39;01-JANUARY-07#39;;

  5. SELECT custno #39;A#39; FROM cust_trans WHERE transamt gt; 2000;

Correct Answer: ACD

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

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.