PassLeader now are offering 100% pass ensure 1Z0-051 dumps! All 1Z0-051 exam questions have been updated with correct answers, welcome to download the newest PassLeader 1Z0-051 VCE dumps and PDF dumps: http://www.passleader.com/1z0-051.html (303 Q&As)
BTW: Download PassLeader 1Z0-051 dumps from Google Drive for free: https://drive.google.com/open?id=0B-ob6L_QjGLpWXpBUEhyRnVXVlE
QUESTION 121
Which two statements are true regarding the ORDER BY clause? (Choose two.)
A. It is executed first in the query execution.
B. It must be the last clause in the SELECT statement.
C. It cannot be used in a SELECT statement containin g a HAVING clause.
D. You cannot specify a column name followed by an expression in this clause.
E. You can specify a combination of numeric positions and column names in this clause.
Answer: BE
QUESTION 122
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 123
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? (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;
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.
QUESTION 124
View the Exhibit and examine the structure of the PRODUCTS table. You want to display only those product names with their list prices where the list price is at least double the minimum price. The report should start with the product name having the maximum list price satisfying this condition. Evaluate the following SQL statement:
SQL>SELECT prod_name,prod_list_price
FROM products
WHERE prod_list_price >= 2 * prod_min_price
Which ORDER BY clauses can be added to the above SQL statement to get the correct output? (Choose all that apply.)
A. ORDER BY prod_list_price DESC, prod_name;
B. ORDER BY (2*prod_min_price)DESC, prod_name;
C. ORDER BY prod_name, (2*prod_min_price)DESC;
D. ORDER BY prod_name DESC, prod_list_price DESC;
E. ORDER BY prod_list_price DESC, prod_name DESC;
Answer: AE
Explanation:
Using the ORDER BY Clause
The order of rows that are returned in a query result is undefined. The ORDER BY clause can be used to sort the rows. However, if you use the ORDER BY clause, it must be the last clause of the SQL statement. Further, you can specify an expression, an alias, or a column position as the sort condition.
Syntax
SELECT expr
FROM table
[WHERE condition(s)]
[ORDER BY {column, expr, numeric_position} [ASC|DESC]];
In the syntax:
ORDER BY specifies the order in which the retrieved rows are displayed
ASC orders the rows in ascending order (This is the default order.)
DESC orders the rows in descending order
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.
QUESTION 125
Which arithmetic operations can be performed on a column by using a SQL function that is built into Oracle database ? (Choose three .)
A. addition
B. subtraction
C. raising to a power
D. finding the quotient
E. finding the lowest value
Answer: ACE
QUESTION 126
Which tasks can be performed using SQL functions built into Oracle Database ? (Choose three.)
A. displaying a date in a nondefault format
B. finding the number of characters in an expression
C. substituting a character string in a text expression with a specified string
D. combining more than two columns or expressions into a single column in the output
Answer: ABC
QUESTION 127
Which tasks can be performed using SQL functions that are built into Oracle database ? (Choose three.)
A. finding the remainder of a division
B. adding a number to a date for a resultant date value
C. comparing two expressions to check whether they are equal
D. checking whether a specified character exists in a given string
E. removing trailing, leading, and embedded characters from a character string
Answer: ACD
QUESTION 128
Which statements are true regarding single row functions? (Choose all that apply.)
A. MOD : returns the quotient of a division
B. TRUNC : can be used with NUMBER and DATE values
C. CONCAT : can be used to combine any number of values
D. SYSDATE : returns the database server current date and time
E. INSTR : can be used to find only the first occurrence of a character in a string
F. TRIM : can be used to remove all the occurrences of a character from a string
Answer: BD
QUESTION 129
The following data exists in the PRODUCTS table:
You issue the following query:
SQL> SELECT RPAD(( ROUND(prod_list_price)), 10,’*’)
FROM products
WHERE prod_id = 123456;
What would be the outcome?
A. 152526 ****
B. **152525.99
C. 152525** **
D. an error message
Answer: A
Explanation:
The LPAD(string, length after padding, padding string) and RPAD(string, length after padding, padding string) functions add a padding string of characters to the left or right of a string until it reaches the specified length after padding.
QUESTION 130
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. SELECT cust_first_name
FROM customers
WHERE INSTR(cust_first_name, ‘e’)<>0 AND
SUBSTR(cust_first_name, -2, 1)=’a’;
B. SELECT cust_first_name
FROM customers
WHERE INSTR(cust_first_name, ‘e’)<>” AND
SUBSTR(cust_first_name, -2, 1)=’a’;
C. SELECT cust_first_name
FROM customers
WHERE INSTR(cust_first_name, ‘e’)IS NOT NULL AND
SUBSTR(cust_first_name, 1,-2)=’a’;
D. SELECT cust_first_name
FROM customers
WHERE INSTR(cust_first_name, ‘e’)<>0 AND
SUBSTR(cust_first_name, LENGTH(cust_first_name),-2)=’a’;
Answer: A
QUESTION 131
In the CUSTOMERS table, the CUST_CITY column contains the value ‘Paris’ for the CUST_FIRST_NAME ‘ABIGAIL’. Evaluate the following query:
What would be the outcome?
A. Abigail PA
B. Abigail Pa
C. Abigail IS
D. an error message
Answer: B
QUESTION 132
Evaluate the following query:
What would be the outcome?
A. 16
B. 100
C. 160
D. 200
E. 150
Answer: C
Explanation:
Function Purpose
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.)
TRUNC(column|expression, n) Truncates the column, expression, or value to n decimal places or, if n is omitted, n defaults to zero.
QUESTION 133
View the Exhibit and examine the structure of the CUSTOMERS table. In the CUSTOMERS table, the CUST_LAST_NAME column contains the values ‘Anderson’ and ‘Ausson’. You issue the following query:
SQL> SELECT LOWER(REPLACE(TRIM(‘son’ FROM cust_last_name),’An’,’O’))
FROM CUSTOMERS
WHERE LOWER(cust_last_name) LIKE ‘a%n’;
What would be the outcome?
A. ‘Oder’ and ‘Aus’
B. an error because the TRIM function specified is not valid
C. an error because the LOWER function specified is not valid
D. an error because the REPLACE function specified is not valid
Answer: B
QUESTION 134
You are currently located in Singapore and have connected to a remote database in Chicago. You issue the following command:
PROMOTIONS is the public synonym for the public database link for the PROMOTIONS table. What is the outcome?
A. an error because the ROUND function specified is invalid
B. an error because the WHERE condition specified is invalid
C. number of days since the promo started based on the current Chicago date and time
D. number of days since the promo started based on the current Singapore date and time
Answer: C
QUESTION 135
Which two statements are true regarding working with dates? (Choose two.)
A. The default internal storage of dates is in the numeric format.
B. The default internal storage of dates is in the character format.
C. The RR date format automatically calculates the century from the SYSDATE function and does not allow the user to enter the century.
D. The RR date format automatically calculates the century from the SYSDATE function but allows the user to enter the century if required.
Answer: AD
QUESTION 136
Examine the data in the CUST_NAME column of the CUSTOMERS table.
You need to display customers’ second names where the second name starts with “Mc” or “MC.”. Which query gives the required output?
A. SELECT SUBSTR(cust_name, INSTR(cust_name,’ ‘)+1)
FROM customers
WHERE INITCAP(SUBSTR(cust_name, INSTR(cust_name,’ ‘)+1))=’Mc’;
B. SELECT SUBSTR(cust_name, INSTR(cust_name,’ ‘)+1)
FROM customers
WHERE INITCAP(SUBSTR(cust_name, INSTR(cust_name,’ ‘)+1)) LIKE ‘Mc%’;
C. SELECT SUBSTR(cust_name, INSTR(cust_name,’ ‘)+1)
FROM customers
WHERE SUBSTR(cust_name, INSTR(cust_name,’ ‘)+1) LIKE INITCAP(‘MC%’);
D. SELECT SUBSTR(cust_name, INSTR(cust_name,’ ‘)+1)
FROM customers
WHERE INITCAP(SUBSTR(cust_name, INSTR(cust_name,’ ‘)+1)) = INITCAP(‘MC%’);
Answer: B
QUESTION 137
Examine the data in the CUST_NAME column of the CUSTOMERS table.
You want to extract only those customer names that have three names and display the * symbol in place of the first name as follows:
Which two queries give the required output? (Choose two.)
A. SELECT LPAD(SUBSTR(cust_name,INSTR(cust_name,’ ‘)),LENGTH(cust_name),’*’) “CUST NAME”
FROM customers
WHERE INSTR(cust_name, ‘ ‘,1,2)<>0;
B. SELECT LPAD(SUBSTR(cust_name,INSTR(cust_name,’ ‘)),LENGTH(cust_name),’*’) “CUST NAME”
FROM customers
WHERE INSTR(cust_name, ‘ ‘,-1,2)<>0;
C. SELECT LPAD(SUBSTR(cust_name,INSTR(cust_name,’ ‘)),LENGTH(cust_name)- INSTR(cust_name,”),
‘*’) “CUST NAME”
FROM customers
WHERE INSTR(cust_name, ‘ ‘,-1,-2)<>0;
D. SELECT LPAD(SUBSTR(cust_name,INSTR(cust_name,’ ‘)),LENGTH(cust_name)- INSTR(cust_name,’ ‘),
‘*’) “CUST NAME”
FROM customers
WHERE INSTR(cust_name, ‘ ‘,1,2)<>0 ;
Answer: AB
QUESTION 138
View the Exhibit and examine the structure of the EMPLOYEES table. Examine the data in the ENAME and HIREDATE columns of the EMPLOYEES table:
You issue the following query:
SQL>SELECT CONCAT(SUBSTR(INITCAP(ename),1,3), REPLACE(hiredate,’-‘)) “USERID”
FROM employees;
What is the outcome?
A. It executes successfully and gives the correct output.
B. It executes successfully but does not give the correct output.
C. It generates an error because the REPLACE function is not valid.
D. It generates an error because the SUBSTR function cannot be nested in the CONCAT function.
Answer: A
Explanation:
REPLACE(text, search_string,replacement_string)
Searches a text expression for a character string and, if found, replaces it with a specified replacement string
The REPLACE Function
The REPLACE function replaces all occurrences of a search item in a source string with a replacement term and returns the modified source string. If the length of the replacement term is different from that of the search item, then the lengths of the returned and source strings will be different. If the search string is not found, the source string is returned unchanged. Numeric and date literals and expressions are evaluated before being implicitly cast as characters when they occur as parameters to the REPLACE function. The REPLACE function takes three parameters, with the first two being mandatory. Its syntax is REPLACE (source string, search item, [replacement term]).
If the replacement term parameter is omitted, each occurrence of the search item is removed from the source string. In other words, the search item is replaced by an empty string.
The following queries illustrate the REPLACE function with numeric and date expressions:
Query 1: select replace(10000-3,’9′,’85’) from dual
Query 2: select replace(sysdate, ‘DEC’,’NOV’) from dual
QUESTION 139
View the Exhibit and examine the structure and data in the INVOICE table. Which statements are true regarding data type conversion in expressions used in queries? (Choose all that apply.)
A. inv_amt =’0255982′ : requires explicit conversion
B. inv_date > ’01-02-2008′ : uses implicit conversion
C. CONCAT(inv_amt,inv_date) : requires explicit conversion
D. inv_date = ’15-february-2008′ : uses implicit conversion
E. inv_no BETWEEN ‘101’ AND ‘110’ : uses implicit conversion
Answer: DE
Explanation:
In some cases, the Oracle server receives data of one data type where it expects data of a different data type.
When this happens, the Oracle server can automatically convert the data to the expected data type. This data type conversion can be done implicitly by the Oracle server or explicitly by the user.
Explicit data type conversions are performed by using the conversion functions. Conversion functions convert a value from one data type to another. Generally, the form of the function names follows the convention data type TO data type. The first data type is the input data type and the second data type is the output.
Note: Although implicit data type conversion is available, it is recommended that you do the explicit data type conversion to ensure the reliability of your SQL statements.
QUESTION 140
Examine the structure and data of the CUST_TRANS table:
Dates are stored in the default date format dd-mon-rr in the CUST_TRANS table. Which SQL statements would execute successfully? (Choose three.)
A. SELECT transdate + ’10’ FROM cust_trans;
B. SELECT * FROM cust_trans WHERE transdate = ’01-01-07′;
C. SELECT transamt FROM cust_trans WHERE custno > ’11’;
D. SELECT * FROM cust_trans WHERE transdate=’01-JANUARY-07′;
E. SELECT custno + ‘A’ FROM cust_trans WHERE transamt > 2000;
Answer: ACD
PassLeader now are offering 100% pass ensure 1Z0-051 dumps! All 1Z0-051 exam questions have been updated with correct answers, welcome to download the newest PassLeader 1Z0-051 VCE dumps and PDF dumps: http://www.passleader.com/1z0-051.html (303 Q&As)
BTW: Download PassLeader 1Z0-051 dumps from Google Drive for free: https://drive.google.com/open?id=0B-ob6L_QjGLpWXpBUEhyRnVXVlE