7 03 2014

QUESTION 41
Evaluate the following SQL statement:

Which statement is true regarding the above query if one of the values generated by the subquery is null?

A.    It produces an error.
B.    It executes but returns no rows.
C.    It generates output for null as well as the other values produced by the subquery.
D.    It ignores the null value and generates output for the other values produced by the subquery.

Answer: C

QUESTION 42
Examine the structure of the employees table:

There is a parent/child relationship between EMPLOYEE_ID and MANAGER_ID.
You want to display the name, joining date, and manager for all the employees. Newly hired employees are yet to be assigned a department or a manager. For them, ‘No Manager1 should be displayed in the manager column.
Which SQL query gets the required output?

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

Answer: D
Explanation:
http://ivrainbow65.blogspot.com/

QUESTION 43
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.

Answer: A
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 44
View the Exhibits and examine the structures of the products and sales tables.

Which two SQL statements would give the same output?

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

Answer: AC

QUESTION 45
The customers table has the following structure:

You need to write a query that does the following tasks:
1. Display the first name and tax amount of the customers. Tax is 5% of their credit limit.
2. Only those customers whose income level has a value should be considered.
3. Customers whose tax amount is null should not be considered.
Which statement accomplishes all the required tasks?

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

Answer: B

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

You have been asked to produce a report on the customers table showing the customers details sorted in descending order of the city and in the descending order of their income level in each city.
Which query would accomplish this task?

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

Answer: A

QUESTION 47
View the Exhibit and examine the data in the products table.

You need to display product names from the products table that belong to the ‘software/other’ category with minimum prices as either S2000 or S4000 and no unit of measure.
You issue the following query:

Which statement is true regarding the above query?

A.    It executes successfully but returns no result.
B.    It executes successfully and returns the required result.
C.    It generates an error because the condition specified for PROD_UNIT_OF_MEASURE is not valid.
D.    It generates an error because the condition specified for the prod category column is not valid.

Answer: A

QUESTION 48
You need to display the first names of all customers from the customers table that contain the character ‘e’ and have the character ‘a’ in the second last position.
Which query would give the required output?

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

Answer: A
Explanation:
The SUBSTR(string, start position, number of characters) function accepts three parameters and returns a string consisting of the number of characters extracted from the source string, beginning at the specified start position:
substr(‘http://www.domain.com’, 12, 6) = domain
The position at which the first character of the returned string begins. When position is 0 (zero), then it is treated as 1. When position is positive, then the function counts from the beginning of string to find the first character.
When position is negative, then the function counts backward from the end of string.
substring_length
The length of the returned string. SUBSTR calculates lengths using characters as defined
by the input character set. SUBSTRB uses bytes instead of characters. SUBSTRC uses Unicode complete characters.
SUBSTR2 uses UCS2 code points. SUBSTR4 uses UCS4 code points. When you do not specify a value for this argument, then the function
The INSTR(source string, search item, [start position], [nth occurrence of search item]) function returns a number that represents the position in the source string, beginning from the given start position, where the nth occurrence of the search item begins:
instr(‘http://www.domain.com’, ‘.’, 1, 2) = 18

QUESTION 49
Examine the structure of the customers table:

CUSTNO is the primary key in the table. You want to find out if any customers’ details have been entered more than once using different CUSTNO, by listing all the duplicate names.
Which two methods can you use to get the required result?

A.    Self-join
B.    Subquery
C.    Full outer-join with self-join
D.    Left outer-join with self-join
E.    Right outer-join with self-join

Answer: AB

QUESTION 50
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.
Which two queries would achieve the required result?

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

Answer: BC
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:
Numeric values are displayed with the lowest values first (for example, 1 to 999). ?Date values are displayed with the earliest value first (for example, 01-JAN-92 before 01- JAN-95).
Character values are displayed in the alphabetical order (for example, "A" first and "Z" last).
Null values are displayed last for ascending sequences and first for descending sequences.
– ANSWER B
You can also sort by a column that is not in the SELECT list.

…go to http://www.lead2pass.com/1z0-061.html to download the full version Q&As.

1z0-061 Dumps, 1z0-061 Exam Questions, 1z0-061 PDF, 1z0-061 Practice Exams, 1z0-061 Practice Tests, 1z0-061 VCE, Free 1z0-061

2014 Latest 100% Pass Guaranteed Oracle 1Z0-061 Practice Tests (31-40) 2014 Latest 100% Pass Guaranteed Oracle 1Z0-061 Practice Tests (51-60)