[PDF and VCE] Free Share 1Z0-051 PDF Exam Preparation Materials with CertBus Real Exam Questions

100% candidates have passed the 9i DBA 1Z0-051 exam by the help of CertBus pass guaranteed 9i DBA 1Z0-051 preparation materials. The CertBus Oracle PDF and VCEs are the latest and cover every knowledge points of 9i DBA 1Z0-051 Oracle Database: SQL Fundamentals I certifications. You can try the Q and As for an undeniable success in 1Z0-051 exam.

We CertBus has our own expert team. They selected and published the latest 1Z0-051 preparation materials from Oracle Official Exam-Center: http://www.certgod.com/1Z0-051.html

QUESTION NO:69

See the Exhibit and examine the structure of the PROMOTIONS table:

Exhibit:

Using the PROMOTIONS table, you need to find out the average cost for all promos in the range $0-2000

and $2000-5000 in category A.

You issue the following SQL statements:

Exhibit:

What would be the outcome?

A. It generates an error because multiple conditions cannot be specified for the WHEN clause

B. It executes successfully and gives the required result

C. It generates an error because CASE cannot be used with group functions

D. It generates an error because NULL cannot be specified as a return value

Correct Answer: B

Explanation

Explanation/Reference:

Explanation:

CASE Expression

Facilitates conditional inquiries by doing the work of an

IF-THEN-ELSE statement:

CASE expr WHEN comparison_expr1 THEN return_expr1

[WHEN comparison_expr2 THEN return_expr2

WHEN comparison_exprn THEN return_exprn

ELSE else_expr]

END

QUESTION NO:137

You need to generate a list of all customer last names with their credit limits from the CUSTOMERS table.

Those customers who do not have a credit limit should appear last in the list. Winch two queries would

achieve the required result? (Choose two.)

A. SELECT cust_last_name. cust_credit_limit FROM customers

ORDER BY cust_credit_limit DESC:

B. SELECT cust_last_name. cust_credit_limit FROM customers

ORDER BY cust_credit_limit:

C. SELECT cust_last_name. cust_credit_limit FROM customers

ORDER BY cust_credit_limit NULLS LAST:

D. SELECT cust_last_name. cust_credit_limit FROM customers

ORDER BY cust_last_name. cust_credit_limit NULLS LAST:

Correct Answer: BC

Explanation

Explanation/Reference:

Explanation:

If the ORDER BY clause is not used, the sort order is undefined, and the Oracle server may not fetch rows

in the same order for the same query twice. Use the ORDER BY clause to display the rows in a specific

order.

Note: Use the keywords NULLS FIRST or NULLS LAST to specify whether returned rows containing null

values should appear first or last in the ordering sequence. ANSWER C Sorting

The default sort order is ascending:

QUESTION NO:41

See the structure of the PROGRAMS table:

Which two SQL statements would execute successfully? (Choose two.)

A. SELECT NVL(ADD_MONTHS(END_DATE,1),SYSDATE)

FROM programs;

B. SELECT TO_DATE(NVL(SYSDATE-END_DATE,SYSDATE))

FROM programs;

C. SELECT NVL(MONTHS_BETWEEN(start_date,end_date),’Ongoing’)

FROM programs;

D. SELECT NVL(TO_CHAR(MONTHS_BETWEEN(start_date,end_date)),’Ongoing’) FROM programs;

Correct Answer: AD

Explanation

Explanation/Reference:

Explanation:

NVL Function

Converts a null value to an actual value:

Data types that can be used are date, character, and number.

Data types must match:

NVL(commission_pct,0)

NVL(hire_date,’01-JAN-97′)

NVL(job_id,’No Job Yet’)

MONTHS_BETWEEN(date1, date2): Finds the number of months between date1 and date2 . The result

can be positive or negative. If date1 is later than date2, the result is positive; if date1 is earlier than date2,

the result is negative. The noninteger part of the result represents a portion of the month.

MONTHS_BETWEEN returns a numeric value. – answer C NVL has different datatypes – numeric and

strings, which is not possible!

The data types of the original and if null parameters must always be compatible. They must either be of the

same type, or it must be possible to implicitly convert if null to the type of the original parameter. The NVL

function returns a value with the same data type as the original parameter.

QUESTION NO:101

Which statement is true regarding the default behavior of the ORDER BY clause?

A. In a character sort, the values are case-sensitive

B. NULL values are not considered at all by the sort operation

C. Only those columns that are specified in the SELECT list can be used in the ORDER BY clause

D. Numeric values are displayed from the maximum to the minimum value if they have decimal positions

Correct Answer: A

Explanation

Explanation/Reference:

Explanation:

Character Strings and Dates

Character strings and date values are enclosed with single quotation marks. Character values are case-

sensitive and date values are format-sensitive.

The default date display format is DD-MON-RR.

QUESTION NO:67

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

You need to display all promo categories that do not have ‘discount’ in their subcategory. Which two SQL

statements give the required result? (Choose two.)

A. SELECT promo_category

FROM promotions

MINUS

SELECT promo_category

FROM promotions

WHERE promo_subcategory = ‘discount’;

B. SELECT promo_category

FROM promotions

INTERSECT

SELECT promo_category

FROM promotions

WHERE promo_subcategory = ‘discount’;

C. SELECT promo_category

FROM promotions

MINUS

SELECT promo_category

FROM promotions

WHERE promo_subcategory ‘discount’;

D. SELECT promo_category

FROM promotions

INTERSECT

SELECT promo_category

FROM promotions

WHERE promo_subcategory ‘discount’;

Correct Answer: AD

Explanation

Explanation/Reference:

QUESTION NO:92

Evaluate the following SQL statements:

Exhibit:

You issue the following command to create a view that displays the IDs and last names of the sales staff in

the organization.

Exhibit:

Which two statements are true regarding the above view? (Choose two.)

A. It allows you to update job IDs of the existing sales staff to any other job ID in the EMPLOYEES table

B. It allows you to delete details of the existing sales staff from the EMPLOYEES table

C. It allows you to insert rows into the EMPLOYEES table

D. It allows you to insert IDs, last names, and job IDs of the sales staff from the view if it is used in

multitable INSERT statements

Correct Answer: BD

Explanation

Explanation/Reference:

QUESTION NO:128

What is true regarding sub queries?

A. The inner query always sorts the results of the outer query

B. The outer query always sorts the results of the inner query

C. The outer query must return a value to the outer query

D. The inner query returns a value to the outer query

E. The inner query must always return a value or the outer query will give an error

Correct Answer: D

Explanation

Explanation/Reference:

Explanation: The inner query returns a value to the outer query. If the inner query does not return a value,

the outer query does not return a result

QUESTION NO:20

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

A. A constraint can be disabled even if the constraint column contains data

B. A constraint is enforced only for the INSERT operation on a table

C. A foreign key cannot contain NULL values

D. All constraints can be defined at the column level as well as the table level

E. A columns with the UNIQUE constraint can contain NULL values

Correct Answer: AE

Explanation

Explanation/Reference:

QUESTION NO:118

View the Exhibit and examine the structure of the PRODUCTS table. You need to generate a report in the

following format:

CATEGORIES

5MP Digital Photo Camera’s category is Photo

Y Box’s category is Electronics

Envoy Ambassador’s category is Hardware

Which two queries would give the required output? (Choose two.)

A. SELECT prod_name || q”’s category is ‘ || prod_category CATEGORIES FROM products;

B. SELECT prod_name || q'[‘s ]’category is ‘ || prod_category CATEGORIES FROM products;

C. SELECT prod_name || q’\’s\’ || ‘ category is ‘ || prod_category CATEGORIES FROM products;

D. SELECT prod_name || q” || ‘category is ‘ || prod_category CATEGORIES FROM products;

Correct Answer: CD

Explanation

Explanation/Reference:

Explanation:

So, how are words that contain single quotation marks dealt with? There are essentially two mechanisms

available. The most popular of these is to add an additional single quotation mark next to each naturally

occurring single quotation mark in the character string Oracle offers a neat way to deal with this type of

character literal in the form of the alternative quote (q) operator. Notice that the problem is that Oracle

chose the single quote characters as the special pair of symbols that enclose or wrap any other character

literal. These character- enclosing symbols could have been anything other than single quotation marks.

Bearing this in mind, consider the alternative quote (q) operator. The q operator enables you to choose

from a set of possible pairs of wrapping symbols for character literals as alternatives to the single quote

symbols. The options are any single-byte or multibyte character or the four brackets: (round brackets),

{curly braces},

[squarebrackets], or . Using the q operator, the character delimiter can effectively be

changed from a single quotation mark to any other character The syntax of the alternative quote operator

is as follows:

q’delimiter’character literal which may include the single quotes delimiter’ where delimiter can be any

character or bracket.

Alternative Quote (q) Operator

Specify your own quotation mark delimiter.

Select any delimiter.

Increase readability and usability.

SELECT department_name || q'[ Department’s Manager Id: ]’

|| manager_id

AS “Department and Manager”

FROM departments;

Alternative Quote (q) Operator

Many SQL statements use character literals in expressions or conditions. If the literal itself contains a

single quotation mark, you can use the quote (q) operator and select your own quotation mark delimiter.

You can choose any convenient delimiter, single-byte or multibyte, or any of the following character pairs:

[ ], { }, ( ), or .

In the example shown, the string contains a single quotation mark, which is normally interpreted as a

delimiter of a character string. By using the q operator, however, brackets [] are used as the quotation

mark delimiters. The string between the brackets delimiters is interpreted as a literal character string.

QUESTION NO:57

Examine the structure of the MARKS table:

Exhibit:

Which two statements would execute successfully? (Choose two.)

A. SELECT student_name,subject1

FROM marks

WHERE subject1 > AVG(subject1);

B. SELECT student_name,SUM(subject1)

FROM marks

WHERE student_name LIKE ‘R%’;

C. SELECT SUM(subject1 subject2 subject3)

FROM marks

WHERE student_name IS NULL;

D. SELECT SUM(DISTINCT NVL(subject1,0)), MAX(subject1)

FROM marks

WHERE subject1 > subject2;

Correct Answer: CD

Explanation

Explanation/Reference:

CertBus exam braindumps are pass guaranteed. We guarantee your pass for the 1Z0-051 exam successfully with our Oracle materials. CertBus Oracle Database: SQL Fundamentals I exam PDF and VCE are the latest and most accurate. We have the best Oracle in our team to make sure CertBus Oracle Database: SQL Fundamentals I exam questions and answers are the most valid. CertBus exam Oracle Database: SQL Fundamentals I exam dumps will help you to be the Oracle specialist, clear your 1Z0-051 exam and get the final success.

1Z0-051 Latest questions and answers on Google Drive(100% Free Download): https://drive.google.com/file/d/0B_3QX8HGRR1mRUNvZTBmX0hLbDQ/view?usp=sharing

1Z0-051 Oracle exam dumps (100% Pass Guaranteed) from CertBus: http://www.certgod.com/1Z0-051.html [100% Exam Pass Guaranteed]

Why select/choose CertBus?

Millions of interested professionals can touch the destination of success in exams by certgod.com. products which would be available, affordable, updated and of really best quality to overcome the difficulties of any course outlines. Questions and Answers material is updated in highly outclass manner on regular basis and material is released periodically and is available in testing centers with whom we are maintaining our relationship to get latest material.

Brand	Certbus	Testking	Pass4sure	Actualtests	Others
Price	$45.99	$124.99	$125.99	$189	$69.99-99.99
Up-to-Date Dumps
Free 365 Days Update
Real Questions
Printable PDF
Test Engine
One Time Purchase
Instant Download
Unlimited Install
100% Pass Guarantee
100% Money Back
Secure Payment
Privacy Protection