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

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

Question No.321

Examine the structure and data in the PRICE_LIST table: Name . Null . Type

PROD_ID . NOT NULL . NUMBER(3) PROD_PRICE . VARCHAR2(10) PROD_ID PROD_PRICE

– 100 $234.55

101 $6, 509.75

102 $1, 234

You plan to give a discount of 25% on the product price and need to display the discount amount 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 PRICE_LIST;

  2. SELECT TO_CHAR(TO_NUMBER(prod_price)* .25, #39;$99, 999.00#39;)FROM PRICE_LIST; C. SELECT TO_CHAR(TO_NUMBER(prod_price, #39;$99, 999.99#39;)* .25, #39;$99, 999.00#39;) FROM

PRICE_LIST;

D. SELECT TO_NUMBER(TO_NUMBER(prod_price, #39;$99, 999.99#39;)* .25, #39;$99, 999.00#39;) FROM PRICE_LIST;

Correct Answer: B

Explanation:

Use TO_NUMBER on the prod_price column to convert from char to number to be able to multiply it with 0.25. Then use the TO_CHAR function (with formatting#39;$99, 999.00#39;) to convert the number back to char.

Incorrect:

Not C: Use the formatting#39;$99, 999.00#39; with the TO_CHAR function, not with the TO_NUMBER function.

Note:

Using the TO_CHAR Function

The TO_CHAR function returns an item of data type VARCHAR2. When applied to items of type NUMBER, several formatting options are available. The syntax is as follows: TO_CHAR(number1, [format], [nls_parameter]),

The number1 parameter is mandatory and must be a value that either is or can be implicitly converted into a number. The optional format parameter may be used to specify numeric formatting information like width, currency symbol, the position of a decimal point, and group (or thousands) separators and must be enclosed in single

Syntax of Explicit Data Type Conversion Functions

TO_NUMBER(char1, [format mask], [nls_parameters]) = num1 TO_CHAR(num1, [format mask], [nls_parameters]) = char1 TO_DATE(char1, [format mask], [nls_parameters]) = date1 TO_CHAR(date1, [format mask], [nls_parameters]) = char1

Question No.322

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

image

You want to generate a report showing the last names and credit limits of all customers whose last names start with A, B, or C, and credit limit 10,000.

Evaluate the following two queries:

image

Which statement is true regarding the execution of the above queries?

  1. Only the first query gives the correct result.

  2. Only the second query gives the correct result.

  3. Both execute successfully and give the same result.

  4. Both execute successfully but do not give the required result.

Correct Answer: A

Question No.323

View the Exhibit and examine the structure of the SALES and PRODUCTS tables.

image

In the SALES table, PROD_ID is the foreign key referencing PROD_ID in the PRODUCTS table. You want to list each product ID and the number of times it has been sold.

Evaluate the following query:

SQLgt;SELECT p.prod_id, COUNT(s.prod_id) FROM products p sales s ON p.prod_id = s.prod_id

GROUP BY p.prod_id;

Which two JOIN options can be used in the blank in the above query to get the required output? (Choose two.)

  1. JOIN

  2. FULL OUTER JOIN

  3. LEFT OUTER JOIN

  4. RIGHT OUTER JOIN

Correct Answer: BC

Question No.324

Examine the data in the CUSTOMERS table:

image

You want to list all cities that have more than one customer along with the customer details. Evaluate the following query:

SQLgt;SELECT c1.custname, c1.city

FROM Customers c1 Customers c2 ON (c1.city=c2.city AND c1.custnamelt;gt;c2.custname);

Which two JOIN options can be used in the blank in the above query to give the correct output? (Choose two.)

  1. JOIN

  2. NATURAL JOIN

  3. LEFT OUTER JOIN

  4. FULL OUTER JOIN

  5. RIGHT OUTER JOIN

Correct Answer: AE

Question No.325

Which three SQL statements would display the value 1890.55 as $1, 890.55? (Choose three.)

  1. SELECT TO_CHAR(1890.55, #39;$0G000D00#39;)FROM DUAL;

  2. SELECT TO_CHAR(1890.55, #39;$9, 999V99#39;)FROM DUAL;

  3. SELECT TO_CHAR(1890.55, #39;$99, 999D99#39;)FROM DUAL;

  4. SELECT TO_CHAR(1890.55, #39;$99G999D00#39;)FROM DUAL;

  5. SELECT TO_CHAR(1890.55, #39;$99G999D99#39;)FROM DUAL;

Correct Answer: ADE

Question No.326

Examine the structure of the PROMOTIONS table:

image

The management wants to see a report of unique promotion costs in each promotion category. Which query would achieve the required result?

  1. SELECT DISTINCT promo_cost, promo_category FROM promotions;

  2. SELECT promo_category, DISTINCT promo_cost FROM promotions;

  3. SELECT DISTINCT promo_cost, DISTINCT promo_category FROM promotions;

  4. SELECT DISTINCT promo_category, promo_cost FROM promotions ORDER BY 1;

Correct Answer: D

Question No.327

View the Exhibit and examine the data in the EMPLOYEES table.

image

You want to generate a report showing the total compensation paid to each employee to date. You issue the following query:

image

What is the outcome?

  1. It generates an error because the alias is not valid.

  2. It executes successfully and gives the correct output.

  3. It executes successfully but does not give the correct output.

  4. It generates an error because the usage of the ROUND function in the expression is not valid.

  5. It generates an error because the concatenation operator can be used to combine only two items.

Correct Answer: C

Explanation:

ROUND(column|expression, n) Rounds the column, expression, or value to n decimal places or, if n is omitted, no decimal places (If n is negative, numbers to the left of decimal point are rounded.)

Question No.328

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

  1. They can be created on tables and clusters.

  2. They can be created on tables and simple views.

  3. You can create only one index by using the same columns.

  4. You can create more than one index by using the same columns if you specify distinctly different combinations of the columns.

Correct Answer: AD

Question No.329

Examine the structure proposed for the TRANSACTIONS table:

image

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

  1. The TRANS_DATE column would allow storage of dates only in the dd-mon-yyyy format.

  2. The CUST_CREDIT_VALUE column would allow storage of positive and negative integers.

  3. The TRANS_VALIDITY column would allow storage of a time interval in days, hours, minutes, and seconds.

  4. The CUST_STATUS column would allow storage of data up to the maximum VARCHAR2 size of 4, 000 characters.

Correct Answer: BD

Explanation:

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.

Question No.330

The user Sue issues this SQL statement:

GRANT SELECT ON sue.EMP TO alice WITH GRANT OPTION;

The user Alice issues this SQL statement:

GRANT SELECT ON sue.EMP TO reena WITH GRANT OPTION;

The user Reena issues this SQL statement: GRANT SELECT ON sue.EMP TO timber; The user Sue issues this SQL statement: REVOKE select on sue.EMP FROM alice;

For which users does the revoke command revoke SELECT privileges on the SUE.EMP table?

  1. Alice only

  2. Alice and Reena

  3. Alice, Reena, and Timber

  4. Sue, Alice, Reena, and Timber

Correct Answer: C

Explanation:

use the REVOKE statement to revoke privileges granted to other users. Privilege granted to others through the WITH GRANT OPTION clause are also revoked.

Alice, Reena and Timber will be revoke.

Incorrect answer:

  1. the correct answer should be Alice, Reena and Timber

  2. the correct answer should be Alice, Reena and Timber

D. the correct answer should be Alice, Reena and Timber

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

>

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