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 1
Which is the valid CREATE TABLE statement?
A. CREATE TABLE emp9$# (emp_no NUMBER (4));
B. CREATE TABLE 9emp$# (emp_no NUMBER(4));
C. CREATE TABLE emp*123 (emp_no NUMBER(4));
D. CREATE TABLE emp9$# (emp_no NUMBER(4), date DATE);
Answer: A
QUESTION 2
Which two statements are true regarding tables? (Choose two.)
A. A table name can be of any length.
B. A table can have any number of columns.
C. A column that has a DEFAULT value cannot store null values.
D. A table and a view can have the same name in the same schema.
E. A table and a synonym can have the same name in the same schema.
F. The same table name can be used in different schemas in the same database.
Answer: EF
Explanation:
Synonyms
Synonyms are database objects that enable you to call a table by another name. You can create synonyms to give an alternative name to a table.
QUESTION 3
Which two statements are true regarding constraints? (Choose two.)
A. A foreign key cannot contain NULL values.
B. A column with the UNIQUE constraint can contain NULL values.
C. A constraint is enforced only for the INSERT operation on a table.
D. A constraint can be disabled even if the constraint column contains data.
E. All constraints can be defined at the column level as well as the table level.
Answer: BD
Explanation:
Including Constraints
– Constraints enforce rules at the table level.
– Constraints prevent the deletion of a table if there are dependencies.
The following constraint types are valid:
– NOT NULL
– UNIQUE
– PRIMARY KEY
– FOREIGN KEY
– CHECK
QUESTION 4
Which two statements are true regarding constraints? (Choose two.)
A. A foreign key cannot contain NULL values.
B. The column with a UNIQUE constraint can store NULLS .
C. A constraint is enforced only for an INSERT operation on a table.
D. You can have more than one column in a table as part of a primary key.
Answer: BD
QUESTION 5
Evaluate the following CREATE TABLE commands:
The above command fails when executed. What could be the reason?
A. SYSDATE cannot be used with the CHECK constraint.
B. The BETWEEN clause cannot be used for the CHECK constraint.
C. The CHECK constraint cannot be placed on columns having the DATE data type.
D. ORD_NO and ITEM_NO cannot be used as a composite primary key because ORD_NO is also the FOREIGN KEY.
Answer: A
Explanation:
CHECK Constraint
The CHECK constraint defines a condition that each row must satisfy. The condition can use the same constructs as the query conditions, with the following exceptions:
References to the CURRVAL, NEXTVAL, LEVEL, and ROWNUM pseudocolumns Calls to SYSDATE, UID, USER, and USERENV functions
Queries that refer to other values in other rows
A single column can have multiple CHECK constraints that refer to the column in its definition. There is no limit to the number of CHECK constraints that you can define on a column. CHECK constraints can be defined at the column level or table level.
CREATE TABLE employees
(…
salary NUMBER(8,2) CONSTRAINT emp_salary_min
CHECK (salary > 0),
QUESTION 6
Evaluate the following SQL commands:
The command to create a table fails. Identify the reason for the SQL statement failure? (Choose all that apply.)
A. You cannot use SYSDATE in the condition of a CHECK constraint.
B. You cannot use the BETWEEN clause in the condition of a CHECK constraint.
C. You cannot use the NEXTVAL sequence value as a DEFAULT value for a column.
D. You cannot use ORD_NO and ITEM_NO columns as a composite primary key because ORD_NO is also the FOREIGN KEY.
Answer: AC
Explanation:
CHECK Constraint
The CHECK constraint defines a condition that each row must satisfy. The condition can use the same constructs as the query conditions, with the following exceptions:
References to the CURRVAL, NEXTVAL, LEVEL, and ROWNUM pseudocolumns
Calls to SYSDATE, UID, USER, and USERENV functions
Queries that refer to other values in other rows
A single column can have multiple CHECK constraints that refer to the column in its definition.
There is no limit to the number of CHECK constraints that you can define on a column.
CHECK constraints can be defined at the column level or table level.
CREATE TABLE employees
(…
salary NUMBER(8,2) CONSTRAINT emp_salary_min
CHECK (salary > 0),
QUESTION 7
Examine the structure and data in the PRICE_LIST table:
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?
A. SELECT TO_CHAR(prod_price* .25,’$99,999.99′)
FROM PRICE_LIST;
B. SELECT TO_CHAR(TO_NUMBER(prod_price)* .25,’$99,999.00′)
FROM PRICE_LIST;
C. SELECT TO_CHAR(TO_NUMBER(prod_price,’$99,999.99′)* .25,’$99,999.00′)
FROM PRICE_LIST;
D. SELECT TO_NUMBER(TO_NUMBER(prod_price,’$99,999.99′)* .25,’$99,999.00′)
FROM PRICE_LIST;
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’$99,999.00′) to convert the number back to char.
Incorrect:
Not C: Use the formatting’$99,999.00′ 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 8
View the Exhibit and examine the structure of the PROMOTIONS table. Which two SQL statements would execute successfully? (Choose two.)
A. UPDATE promotions
SET promo_cost = promo_cost+ 100
WHERE TO_CHAR(promo_end_date, ‘yyyy’) > ‘2000’;
B. SELECT promo_begin_date
FROM promotions
WHERE TO_CHAR(promo_begin_date,’mon dd yy’)=’jul 01 98′;
C. UPDATE promotions
SET promo_cost = promo_cost+ 100
WHERE promo_end_date > TO_DATE(SUBSTR(’01-JAN-2000′,8));
D. SELECT TO_CHAR(promo_begin_date,’dd/month’)
FROM promotions
WHERE promo_begin_date IN (TO_DATE(‘JUN 01 98’), TO_DATE(‘JUL 01 98’));
Answer: AB
QUESTION 9
View the Exhibit and examine the data in the PROMO_NAME and PROMO_END_DATE columns of the PROMOTIONS table, and the required output format. Which two queries give the correct result? (Choose two.)
A. SELECT promo_name, TO_CHAR(promo_end_date,’Day’) ‘, ‘
TO_CHAR(promo_end_date,’Month’) ‘ ‘
TO_CHAR(promo_end_date,’DD, YYYY’) AS last_day
FROM promotions;
B. SELECT promo_name,TO_CHAR (promo_end_date,’fxDay’) ‘, ‘
TO_CHAR(promo_end_date,’fxMonth’) ‘ ‘
TO_CHAR(promo_end_date,’fxDD, YYYY’) AS last_day
FROM promotions;
C. SELECT promo_name, TRIM(TO_CHAR(promo_end_date,’Day’)) ‘, ‘
TRIM(TO_CHAR(promo_end_date,’Month’)) ‘ ‘
TRIM(TO_CHAR(promo_end_date,’DD, YYYY’)) AS last_day
FROM promotions;
D. SELECTpromo_name,TO_CHAR(promo_end_date,’fmDay’)’,’
TO_CHAR(promo_end_date,’fmMonth’) ‘ ‘
TO_CHAR(promo_end_date,’fmDD, YYYY’) AS last_day
FROM promotions;
Answer: CD
QUESTION 10
View the Exhibit and examine the structure of the CUSTOMERS table. Using the CUSTOMERS table, y ou need to generate a report that shows an increase in the credit limit by 15% for all customers. Customers whose credit limit has not been entered should have the message ” Not Available” displayed. Which SQL statement would produce the required result?
A. SELECT NVL(cust_credit_limit,’Not Available’)*.15 “NEW CREDIT” FROM customers;
B. SELECT NVL(cust_credit_limit*.15,’Not Available’) “NEW CREDIT” FROM customers;
C. SELECT TO_CHAR(NVL(cust_credit_limit*.15,’Not Available’)) “NEW CREDIT” FROM customers;
D. SELECT NVL(TO_CHAR(cust_credit_limit*.15),’Not Available’) “NEW CREDIT” FROM customers;
Answer: D
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’)
QUESTION 11
Examine 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;
Answer: AD
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 12
The PRODUCTS table has the following structure:
Evaluate the following two SQL statements:
Which statement is true regarding the outcome?
A. Both the statements execute and give different results.
B. Both the statements execute and give the same result.
C. Only the first SQL statement executes successfully.
D. Only the second SQL statement executes successfully.
Answer: A
Explanation:
Using the NVL2 Function
The NVL2 function examines the first expression. If the first expression is not null, the NVL2 function returns the second expression. If the first expression is null, the third expression is returned.
Syntax
NVL2(expr1, expr2, expr3)
In the syntax:
expr1 is the source value or expression that may contain a null expr2 is the value that is returned if expr1 is not null expr3 is the value that is returned if expr1 is null
QUESTION 13
Examine the structure of the INVOICE table. Which two SQL statements would execute successfully? (Choose two.)
A. SELECT inv_no,NVL2(inv_date,’Pending’,’Incomplete’)
FROM invoice;
B. SELECT inv_no,NVL2(inv_amt,inv_date,’Not Available’)
FROM invoice;
C. SELECT inv_no,NVL2(inv_date,sysdate-inv_date,sysdate)
FROM invoice;
D. SELECT inv_no,NVL2(inv_amt,inv_amt*.25,’Not Available’)
FROM invoice;
Answer: AC
Explanation:
The NVL2 Function
The NVL2 function provides an enhancement to NVL but serves a very similar purpose. It evaluates whether a column or expression of any data type is null or not.
5-6 The NVL function\
If the first term is not null, the second parameter is returned, else the third parameter is returned. Recall that the NVL function is different since it returns the original term if it is not null. The NVL2 function takes three mandatory parameters. Its syntax is NVL2(original, ifnotnull, ifnull), where original represents the term being tested. Ifnotnull is returned if original is not null, and ifnull is returned if original is null. The data types of the ifnotnull and ifnull parameters must be compatible, and they cannot be of type LONG.
They must either be of the same type, or it must be possible to convert ifnull to the type of the ifnotnull parameter. The data type returned by the NVL2 function is the same as that of the ifnotnull parameter.
QUESTION 14
View the Exhibit and evaluate the structure and data in the CUST_STATUS table. You issue the following SQL statement:
Which statement is true regarding the execution of the above query?
A. It produces an error because the AMT_SPENT column contains a null value.
B. It displays a bonus of 1000 for all customers whose AMT_SPENT is less than CREDIT_LIMIT.
C. It displays a bonus of 1000 for all customers whose AMT_SPENT equals CREDIT_LIMIT, or AMT_SPENT is null .
D. It produces an error because the TO_NUMBER function must be used to convert the result of the NULLIF function before it can be used by the NVL2 function.
Answer: C
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.
QUESTION 15
Which statement is true regarding the COALESCE function?
A. It can have a maximum of five expressions in a list.
B. It returns the highest NOT NULL value in the list for all rows.
C. It requires that all expressions in the list must be of the same data type.
D. It requires that at least one of the expressions in the list must have a NOT NULL value.
Answer: C
Explanation:
The COALESCE Function
The COALESCE function returns the first nonnull value from its parameter list. If all its parameters are null, then null is returned.
The COALESCE function takes two mandatory parameters and any number of optional parameters. The syntax is COALESCE(expr1, expr2,…,exprn), where expr1 is returned if it is not null, else expr2 if it is not null, and so on. COALESCE is a general form of the NVL function, as the following two equations illustrate:
COALESCE(expr1,expr2) = NVL(expr1,expr2)
COALESCE(expr1,expr2,expr3) = NVL(expr1,NVL(expr2,expr3))
The data type COALESCE returns if a not null value is found is the same as that of the first not null parameter.
To avoid an “ORA-00932: inconsistent data types” error, all not null parameters must have data types compatible with the first not null parameter.
QUESTION 16
View the Exhibit and examine the structure of the PROMOTIONS table. Using the PROMOTIONS table, you need to find out the average cost for all promos in the ranges $0-2000 and $2000-5000 in category A. You issue the following SQL statement:
What would be the outcome?
A. It executes successfully and gives the required result.
B. It generates an error because NULL cannot be specified as a return value.
C. It generates an error because CASE cannot be used with group functions.
D. It generates an error because multiple conditions cannot be specified for the WHEN clause.
Answer: A
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 17
View the Exhibit and examine the structure of the PROMOTIONS table. Which SQL statements are valid? (Choose all that apply.)
A. SELECT promo_id, DECODE(NVL(promo_cost,0), promo_cost,
promo_cost * 0.25, 100) “Discount”
FROM promotions;
B. SELECT promo_id, DECODE(promo_cost, 10000,
DECODE(promo_category, ‘G1’, promo_cost *.25, NULL),
NULL) “Catcost”
FROM promotions;
C. SELECT promo_id, DECODE(NULLIF(promo_cost, 10000),
NULL, promo_cost*.25, ‘N/A’) “Catcost”
FROM promotions;
D. SELECT promo_id, DECODE(promo_cost, >10000, ‘High’,
<10000, ‘Low’) “Range”
FROM promotions;
Answer: AB
Explanation:
The DECODE Function
Although its name sounds mysterious, this function is straightforward. The DECODE function implements ifthen-else conditional logic by testing its first two terms for equality and returns the third if they are equal and optionally returns another term if they are not. The DECODE function takes at least three mandatory parameters, but can take many more. The syntax of the function is DECODE(expr1,comp1, iftrue1, [comp2,iftrue2…[ compN,iftrueN]], [iffalse]).
QUESTION 18
Examine the data in the PROMO_BEGIN_DATE column of the PROMOTIONS table:
You want to display the number of promotions started in 1999 and 2000. Which query gives the correct output?
A. SELECT SUM(DECODE(SUBSTR(promo_begin_date,8),’00’,1,0)) “2000”, SUM(DECODE(SUBSTR
(promo_begin_date,8),’99’,1,0)) “1999”
FROM promotions;
B. SELECT SUM(CASE TO_CHAR(promo_begin_date,’yyyy’) WHEN ’99’ THEN 1 ELSE 0 END) “1999”,
SUM(CASE TO_CHAR(promo_begin_date,’yyyy’) WHEN ’00’ THEN 1 ELSE 0 END) “2000”
FROM promotions;
C. SELECT COUNT(CASE TO_CHAR(promo_begin_date,’yyyy’) WHEN ’99’ THEN 1 ELSE 0 END) “1999”,
COUNT(CASE TO_CHAR(promo_begin_date,’yyyy’) WHEN ’00’ THEN 1 ELSE 0 END) “2000”
FROM promotions;
D. SELECT COUNT(DECODE(SUBSTR(TO_CHAR(promo_begin_date,’yyyy’), 8), ‘1999’, 1, 0)) “1999”, COUNT(DECODE(SUBSTR(TO_CHAR(promo_begin_date,’yyyy’), 8),’2000′, 1,
0)) “2000”
FROM promotions;
Answer: A
QUESTION 19
Examine the structure of the TRANSACTIONS table:
You want to display the date, time, and transaction amount of transactions that where done before 12 noon. The value zero should be displayed for transactions where the transaction amount has not been entered. Which query gives the required result?
A. SELECT TO_CHAR(trans_date,’dd-mon-yyyy hh24:mi:ss’),
TO_CHAR(trans_amt,’$99999999D99′)
FROM transactions
WHERE TO_NUMBER(TO_DATE(trans_date,’hh24′)) < 12 AND COALESCE(trans_amt,NULL)<>NULL;
B. SELECT TO_CHAR(trans_date,’dd-mon-yyyy hh24:mi:ss’),
NVL(TO_CHAR(trans_amt,’$99999999D99′),0)
FROM transactions
WHERE TO_CHAR(trans_date,’hh24′) < 12;
C. SELECT TO_CHAR(trans_date,’dd-mon-yyyy hh24:mi:ss’),
COALESCE(TO_NUMBER(trans_amt,’$99999999.99′),0)
FROM transactions
WHERE TO_DATE(trans_date,’hh24′) < 12;
D. SELECT TO_DATE (trans_date,’dd-mon-yyyy hh24:mi:ss’),
NVL2(trans_amt,TO_NUMBER(trans_amt,’$99999999.99′), 0)
FROM transactions
WHERE TO_DATE(trans_date,’hh24′) < 12;
Answer: B
QUESTION 20
Examine the structure of the TRANSACTIONS table:
You want to display the transaction date and specify whether it is a weekday or weekend. Evaluate the following two queries:
Which statement is true regarding the above queries?
A. Both give wrong results.
B. Both give the correct result.
C. Only the first query gives the correct result.
D. Only the second query gives the correct result.
Answer: C
Explanation:
Range Conditions Using the BETWEEN Operator
Use the BETWEEN operator to display rows based on a range of values:
SELECT last_name, salary
FROM employees
WHERE salary BETWEEN 2500 AND 3500;
Range Conditions Using the BETWEEN Operator
You can display rows based on a range of values using the BETWEEN operator. The range that you specify contains a lower limit and an upper limit. The SELECT statement in the slide returns rows from the EMPLOYEES table for any employee whose salary is between $2,500 and $3,500. Values that are specified with the BETWEEN operator are inclusive. However, you must specify the lower limit first.
You can also use the BETWEEN operator on character values:
SELECT last_name
FROM employees
WHERE last_name BETWEEN ‘King’ AND ‘Smith’;
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