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 161
Which statement is true regarding the INTERSECT operator?
A. It ignores NULL values.
B. Reversing the order of the intersected tables alters the result.
C. The names of columns in all SELECT statements must be identical.
D. The number of columns and data types must be identical for all SELECT statements in the query.
Answer: D
Explanation:
INTERSECT Returns only the rows that occur in both queries’ result sets, sorting them and removing duplicates. The columns in the queries that make up a compound query can have different names, but the output result set will use the names of the columns in the first query.
QUESTION 162
View the Exhibit; e xamine the structure of the PROMOTIONS table. Each promotion has a duration of at least seven days. Your manager has asked you to generate a report, which provides the weekly cost for each promotion done to l date. Which query would achieve the required result?
A. SELECT promo_name, promo_cost/promo_end_date-promo_begin_date/7 FROM promotions;
B. SELECT promo_name,(promo_cost/promo_end_date-promo_begin_date)/7 FROM promotions;
C. SELECT promo_name, promo_cost/(promo_end_date-promo_begin_date/7) FROM promotions;
D. SELECT promo_name, promo_cost/((promo_end_date-promo_begin_date)/7) FROM promotions;
Answer: D
QUESTION 163
You need to produce a report where each customer’s credit limit has been incremented by $1000. In the output, the customer’s last name should have the heading Name and the incremented credit limit should be labeled New Credit Limit. The column headings should have only the first letter of each word in uppercase. Which statement would accomplish this requirement?
A. SELECT cust_last_name Name, cust_credit_limit + 1000
“New Credit Limit”
FROM customers;
B. SELECT cust_last_name AS Name, cust_credit_limit + 1000
AS New Credit Limit
FROM customers;
C. SELECT cust_last_name AS “Name”, cust_credit_limit + 1000
AS “New Credit Limit”
FROM customers;
D. SELECT INITCAP(cust_last_name) “Name”, cust_credit_limit + 1000 INITCAP(“NEW CREDIT LIMIT”)
FROM customers;
Answer: C
Explanation:
A column alias:
– Renames a column heading
– Is useful with calculations
– Immediately follows the column name (There can also be the optional AS keyword between the column name and the alias.)
– Requires double quotation marks if it contains spaces or special characters, or if it is case sensitive.
QUESTION 164
View the Exhibit and examine the structure of the PRODUCTS table. All products have a list price. You issue the following command to display the total price of each product after a discount of 25% and a tax of 15% are applied on it. Freight charges of $100 have to be applied to all the products.
What would be the outcome if all the parenthese s are removed from the above statement?
A. It produces a syntax error.
B. The result remains unchanged.
C. The total price value would be lower than the correct value.
D. The total price value would be higher than the correct value.
Answer: B
QUESTION 165
Using the CUSTOMERS table, you need to generate a report that shows 50% of each credit amount in each income level. The report should NOT show any repeated credit amounts in each income level. Which query would give the required result?
A. SELECT cust_income_level, DISTINCT cust_credit_limit * 0.50 AS “50% Credit Limit” FROM customers;
B. SELECT DISTINCT cust_income_level, DISTINCT cust_credit_limit * 0.50 AS “50% Credit Limit” FROM customers;
C. SELECT DISTINCT cust_income_level ‘ ‘ cust_credit_limit * 0.50 AS “50% Credit Limit” FROM customers;
D. SELECT cust_income_level ‘ ‘ cust_credit_limit * 0.50 AS “50% Credit Limit” FROM customers;
Answer: C
Explanation:
Duplicate Rows
Unless you indicate otherwise, SQL displays the results of a query without eliminating the duplicate rows. To eliminate duplicate rows in the result, include the DISTINCT keyword in the SELECT clause immediately after the SELECT keyword. You can specify multiple columns after the DISTINCT qualifier. The DISTINCT qualifier affects all the selected columns, and the result is every distinct combination of the columns.
QUESTION 166
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'<‘s >’ || ‘category is ‘ || prod_category CATEGORIES FROM products;
Answer: CD
QUESTION 167
View the Exhibit and examine the data in the CUSTOMERS table. Evaluate the following query. The above query produces an error on execution. What is the reason for the error?
A. An alias cannot be used in an expression.
B. The a lias NAME should not be enclosed with in double quotation marks.
C. The MIDPOINT+100 expression gives an error because CUST_CREDIT_LIMIT contains NULL values.
D. The a lias MIDPOINT should be enclosed with in double quotation marks for the CUST_CREDIT_LIMIT/2 expression.
Answer: A
QUESTION 168
Evaluate the following query:
What would be the outcome of the above query?
A. It produces an error because flower braces have been used.
B. It produces an error because the data types are not matching.
C. It executes successfully and introduces an ‘s at the end of each promo_name in the output.
D. It executes successfully and displays the literal ” {‘s start date was } ” for each row in the output.
Answer: C
QUESTION 169
View the Exhibit and examine the data in the EMPLOYEES table. You want to generate a report showing the total compensation paid to each employee to date. You issue the following query:
What is the outcome?
A. It generates an error because the alias is not valid.
B. It executes successfully and gives the correct output.
C. It executes successfully but does not give the correct output.
D. It generates an error because the usage of the ROUND function in the expression is not valid.
E. It generates an error because the concatenation operator can be used to combine only two items.
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 170
Examine the structure of the PROMOTIONS table. The management wants to see a report of unique promotion costs in each promotion category. Which query would achieve the required result?
A. SELECT DISTINCT promo_cost, promo_category FROM promotions;
B. SELECT promo_category, DISTINCT promo_cost FROM promotions;
C. SELECT DISTINCT promo_cost, DISTINCT promo_category FROM promotions;
D. SELECT DISTINCT promo_category, promo_cost FROM promotions ORDER BY 1;
Answer: D
QUESTION 171
Evaluate the following query:
SELECT INTERVAL ‘300’ MONTH,
INTERVAL ’54-2′ YEAR TO MONTH,
INTERVAL ’11:12:10.1234567′ HOUR TO SECOND
FROM dual;
What is the correct output of the above query?
A. +25-00 , +54-02, +00 11:12:10.123457
B. +00-300, +54-02, +00 11:12:10.123457
C. +25-00 , +00-650, +00 11:12:10.123457
D. +00-300 , +00-650, +00 11:12:10.123457
Answer: A
Explanation:
Datetime Data Types
You can use several datetime data types:
INTERVAL YEAR TO MONTH
Stored as an interval of years and months
INTERVAL DAY TO SECOND
Stored as an interval of days, hours, minutes, and seconds
QUESTION 172
Which three statements are true regarding the data types in Oracle Database 10g/11g? (Choose three.)
A. Only one LONG column can be used per table.
B. A TIMESTAMP data type column stores only time values with fractional seconds.
C. The BLOB data type column is used to store binary data in an operating system file.
D. The minimum column width that can be specified for a VARCHAR2 data type column is one.
E. The value for a CHAR data type column is blank-padded to the maximum defined column width.
Answer: ADE
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 173
Examine the description of the EMP_DETAILS table given below. Which two statements are true regarding SQL statements that can be executed on the EMP_DETAIL table? (Choose two.)
A. An EMP_IMAGE column can be included in the GROUP BY clause.
B. An EMP_IMAGE column cannot be included in the ORDER BY clause.
C. You cannot add a new column to the table with LONG as the data type.
D. You can alter the table to include the NOT NULL constraint on the EMP_IMAGE column.
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 174
You need to create a table for a banking application. One of the columns in the table has the following requirements:
1) You want a column in the table to store the duration of the credit period.
2) The data in the column should be stored in a format such that it can be easily added and subtracted with DATE data type without using conversion functions.
3) The maximum period of the credit provision in the application is 30 days.
4) The interest has to be calculated for the number of days an individual has taken a credit for.
Which data type would you use for such a column in the table?
A. DATE
B. NUMBER
C. TIMESTAMP
D. INTERVAL DAY TO SECOND
E. INTERVAL YEAR TO MONTH
Answer: D
QUESTION 175
Examine the structure proposed for the TRANSACTIONS table. Which statements are true regarding the creation and storage of data in the above table structure? (Choose all that apply.)
A. The CUST_STATUS column would give an error.
B. The TRANS_VALIDITY column would give an error.
C. The CUST_STATUS column would store exactly one character.
D. The CUST_CREDIT_LIMIT column would not be able to store decimal values.
E. The TRANS_VALIDITY column would have a maximum size of one character.
F. The TRANS_DATE column would be able to store day, month, century, year, hour, minutes, seconds, and fractions of seconds.
Answer: BC
Explanation:
VARCHAR2(size)Variable-length character data (A maximum size must be specified: minimum size is 1; maximum size is 4,000.)
CHAR [(size)] Fixed-length character data of length size bytes (Default and minimum size is 1; maximum size is 2,000.)
NUMBER [(p,s)] Number having precision p and scale s (Precision is the total number of decimal digits and scale is the number of digits to the right of the decimal point; precision can range from 1 to 38, and scale can range from-84 to 127.)
DATE Date and time values to the nearest second between January 1, 4712 B.C., and December 31, 9999 A.D.
QUESTION 176
Examine the structure proposed for the TRANSACTIONS table. Which two statements are true regarding the storage of data in the above table structure? (Choose two.)
A. The TRANS_DATE column would allow storage of dates only in the dd-mon-yyyy format.
B. The CUST_CREDIT_VALUE column would allow storage of positive and negative integers.
C. The TRANS_VALIDITY column would allow storage of a time interval in days, hours, minutes, and seconds.
D. The CUST_STATUS column would allow storage of data up to the maximum VARCHAR2 size of 4,000 characters.
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 177
Resume (character large object [CLOB] data type), which contains the resume submitted by the employee. Which is the correct syntax to create this table?
A. CREATE TABLE EMP_1
(emp_id NUMBER(4),
emp_name VARCHAR2(25),
start_date DATE,
e_status VARCHAR2(10) DEFAULT ‘ACTIVE’,
resume CLOB(200));
B. CREATE TABLE 1_EMP
(emp_id NUMBER(4),
emp_name VARCHAR2(25),
start_date DATE,
emp_status VARCHAR2(10) DEFAULT ‘ACTIVE’,
resume CLOB);
C. CREATE TABLE EMP_1
(emp_id NUMBER(4),
emp_name VARCHAR2(25),
start_date DATE,
emp_status VARCHAR2(10) DEFAULT “ACTIVE”,
resume CLOB);
D. CREATE TABLE EMP_1
(emp_id NUMBER,
emp_name VARCHAR2(25),
start_date DATE,
emp_status VARCHAR2(10) DEFAULT ‘ACTIVE’,
resume CLOB);
Answer: D
Explanation:
CLOB Character data (up to 4 GB)
NUMBER [(p,s)] Number having precision p and scale s (Precision is the total number of decimal digits and scale is the number of digits to the right of the decimal point; precision can range from 1 to 38, and scale can range from-84 to 127.)
QUESTION 178
Examine the structure of the PROMOS table. You want to generate a report showing promo names and their duration (number of days). If the PROMO_END_DATE has not been entered, the message ‘ONGOING’ should be displayed. Which queries give the correct output? (Choose all that apply.)
A. SELECT promo_name, TO_CHAR(NVL(promo_end_date -promo_start_date,’ONGOING’)) FROM promos;
B. SELECT promo_name,COALESCE(TO_CHAR(promo_end_date – promo_start_date),’ONGOING’) FROM promos;
C. SELECT promo_name, NVL(TO_CHAR(promo_end_date -promo_start_date),’ONGOING’) FROM promos;
D. SELECT promo_name, DECODE(promo_end_date-promo_start_date,NULL,’ONGOING’,promo_end_date – promo_start_date) FROM promos;
E. SELECT promo_name, decode(coalesce(promo_end_date,promo_start_date),null,’ONGOING’, promo_end_date – promo_start_date) FROM promos;
Answer: BCD
QUESTION 179
Examine the structure of the PROMOS table. You want to display the list of promo names with the message ‘Same Day’ for promos that started and ended on the same day. Which query gives the correct output?
A. SELECT promo_name, NVL(NULLIF(promo_start_date, promo_end_date), ‘Same Day’) FROM promos;
B. SELECT promo_name, NVL(TRUNC(promo_end_date – promo_start_date), ‘Same Day’) FROM promos;
C. SELECT promo_name, NVL2(TO_CHAR(TRUNC(promo_end_date-promo_start_date)), NULL,’Same Day’) FROM promos;
D. SELECT promo_name, DECODE((NULLIF(promo_start_date, promo_end_date)), NULL,’Same day’) FROM promos;
Answer: D
Explanation:
The NULLIF Function
The NULLIF function tests two terms for equality. If they are equal the function returns a null, else it returns the first of the two terms tested.
The NULLIF function takes two mandatory parameters of any data type. The syntax is NULLIF(ifunequal, comparison_term), where the parameters ifunequal and comparison_term are compared. If they are identical, then NULL is returned. If they differ, the ifunequal parameter is returned ANSWER A – date and String incompatibl;a datatypes for NVL function The Date TRUNC Function.
The date TRUNC function performs a truncation operation on a date value based on a specified date precision format.
The date TRUNC function takes one mandatory and one optional parameter.
Its syntax is TRUNC(source date, [date precision format]). The source date parameter represents any value that can be implicitly converted into a date item. The date precision format parameter specifies the degree of truncation and is optional. If it is absent, the default degree of truncation is day. This means that any time component.
QUESTION 180
Examine the data in the LIST_PRICE and MIN_PRICE columns of the PRODUCTS table. Which two expressions give the same output? (Choose two.)
A. NVL(NULLIF(list_price, min_price), 0)
B. NVL(COALESCE(list_price, min_price), 0)
C. NVL2(COALESCE(list_price, min_price), min_price, 0)
D. COALESCE(NVL2(list_price, list_price, min_price), 0)
Answer: BD
Explanation:
Using the COALESCE Function
– The advantage of the COALESCE function over the NVL function is that the COALESCE function can take multiple alternate values.
– If the first expression is not null, the COALESCE function returns that expression; otherwise, it does a COALESCE of the remaining expressions.
Using the COALESCE Function
The COALESCE function returns the first non-null expression in the list.
Syntax
COALESCE (expr1, expr2, … exprn)
In the syntax:
– expr1 returns this expression if it is not null
– expr2 returns this expression if the first expression is null and this expression is not null
– exprn returns this expression if the preceding expressions are null
Note that all expressions must be of the same data type.
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