[2021-New] Oracle 1Z0-051 Dumps With Update Exam Questions (51-60)

1Z0-051 Dumps

1Z0-051 Exam Questions - Online Test

1Z0-051 Premium VCE File

Learn More 100% Pass Guarantee - Dumps Verified - Instant Download
150 Lectures, 20 Hours

Q1. - (Topic 1)

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 the same result

B. Both the statements execute and give different results

C. Only the second SQL statement executes successfully

D. Only the first SQL statement executes successfully

Answer: B

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

Q2. - (Topic 1)

The ORDERS TABLE belongs to the user OE. OE has granted the SELECT privilege on the ORDERS table to the user HR.

Which statement would create a synonym ORD so that HR can execute the following query successfully?

SELECT * FROM ord;

A. CREATE SYNONYM ord FOR orders; This command is issued by OE.

B. CREATE PUBLIC SYNONYM ord FOR orders; This command is issued by OE.

C. CREATE SYNONYM ord FOR oe.orders; This command is issued by the database administrator.

D. CREATE PUBLIC SYNONYM ord FOR oe.orders; This command is issued by the database administrator.

Answer: D

Explanation:

Creating a Synonym for an Object To refer to a table that is owned by another user, you need to prefix the table name with the name of the user who created it, followed by a period. Creating a synonym eliminates the need to qualify the object name with the schema and provides you with an alternative name for a table, view, sequence, procedure, or other objects. This method can be especially useful with lengthy object names, such as views. In the syntax: PUBLIC Creates a synonym that is accessible to all users synonym Is the name of the synonym to be created object Identifies the object for which the synonym is created Guidelines The object cannot be contained in a package. A private synonym name must be distinct from all other objects that are owned by the same user. If you try to execute the following command (alternative B, issued by OE):

Q3. - (Topic 2)

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: B,C

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.

Q4. - (Topic 2)

View the Exhibit and examine the structure and data in the INVOICE table.

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

A. SELECT AVG(inv_date ) FROM invoice;

B. SELECT MAX(inv_date),MIN(cust_id) FROM invoice;

C. SELECT MAX(AVG(SYSDATE - inv_date)) FROM invoice;

D. SELECT AVG( inv_date - SYSDATE), AVG(inv_amt) FROM invoice;

Answer: B,D

Explanation:

Using the AVG and SUM Functions You can use the AVG, SUM, MIN, and MAX functions against the columns that can store numeric data. The example in the slide displays the average, highest, lowest, and sum of monthly salaries for all sales representatives Using the MIN and MAX Functions You can use the MAX and MIN functions for numeric, character, and date data types. The example in the slide displays the most junior and most senior employees.

Q5. - (Topic 2)

Examine the structure of the TRANSACTIONS table:

Name Null Type

TRANS_ID NOT NULL NUMBER(3)

CUST_NAME VARCHAR2(30)

TRANS_DATE TIMESTAMP

TRANS_AMT NUMBER(10,2)

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?

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;

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;

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;

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

Q6. - (Topic 2)

Examine the structure of the SHIPMENTS table:

You want to generate a report that displays the PO_ID and the penalty amount to be paid if

the

SHIPMENT_DATE is later than one month from the PO_DATE. The penalty is $20 per day.

Evaluate the following two queries:

Which statement is true regarding the above commands?

A. Both execute successfully and give correct results.

B. Only the first query executes successfully but gives a wrong result.

C. Only the first query executes successfully and gives the correct result.

D. Only the second query executes successfully but gives a wrong result.

E. Only the second query executes successfully and gives the correct result.

Answer: C

Explanation:

The MONTHS_BETWEEN(date 1, date 2) function returns the number of months between two dates: months_between('01-FEB-2008','01-JAN-2008') = 1 The DECODE Function Although its name sounds mysterious, this function is straightforward. The DECODE function implements if then-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. DECODE Function Facilitates conditional inquiries by doing the work of a CASE expression or an IF-THENELSE statement: DECODE(col|expression, search1, result1 [, search2, result2,...,] [, default]) DECODE Function The DECODE function decodes an expression in a way similar to the IF-THEN-ELSE logic that is used in various languages. The DECODE function decodes expression after comparing it to each search value. If the expression is the same as search, result is returned.

If the default value is omitted, a null value is returned where a search value does not match any of the result values.

Q7. - (Topic 1)

Examine the structure of the INVOICE table: Exhibit:

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: A,C

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.

Q8. - (Topic 1)

See the Exhibits and examine the structures of PRODUCTS, SALES and CUSTOMERS table:

You issue the following query:

Which statement is true regarding the outcome of this query?

A. It produces an error because the NATURAL join can be used only with two tables

B. It produces an error because a column used in the NATURAL join cannot have a qualifier

C. It produces an error because all columns used in the NATURAL join should have a qualifier

D. It executes successfully

Answer: B

Explanation:

Creating Joins with the USING Clause

Natural joins use all columns with matching names and data types to join the tables. The USING clause can be used to specify only those columns that should be used for an equijoin.

The Natural JOIN USING Clause

The format of the syntax for the natural JOIN USING clause is as follows: SELECT table1.column, table2.column FROM table1 JOIN table2 USING (join_column1, join_column2…); While the pure natural join contains the NATURAL keyword in its syntax, the JOIN…USING syntax does not. An error is raised if the keywords NATURAL and USING occur in the same join clause. The JOIN…USING clause allows one or more equijoin columns to be explicitly specified in brackets after the USING keyword. This avoids the shortcomings associated with the pure natural join. Many situations demand that tables be joined only on certain columns, and this format caters to this requirement.

Q9. - (Topic 2)

Examine this statement:

SELECT student_id, gpa FROM student_grades WHERE gpa > &&value;

You run the statement once, and when prompted you enter a value of 2.0. A report is produced. What happens when you run the statement a second time?

A. An error is returned.

B. You are prompted to enter a new value.

C. A report is produced that matches the first report produced.

D. You are asked whether you want a new value or if you want to run the report based on the previous value.

Answer: C

Explanation:

use the double-ampersand if you want to reuse the variable value without prompting the user each time.

Incorrect Answer: Ais not an error

B&& will not prompt user for second time D&& will not ask the user for new value

Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 7-13

Q10. - (Topic 1)

Evaluate these two SQL statements:

SELECT last_name, salary, hire_date FROM EMPLOYEES ORDER BY salary DESC;

SELECT last_name, salary, hire_date FROM EMPLOYEES ORDER BY 2 DESC;

What is true about them?

A. The two statements produce identical results.

B. The second statement returns a syntax error.

C. There is no need to specify DESC because the results are sorted in descending order by default.

D. The two statements can be made to produce identical results by adding a column alias for the salary column in the second SQL statement.

Answer: A

Explanation: Explanation: the two statement produce identical results as ORDER BY 2 will take the second column as sorting column.

Incorrect Answer: Bthere is no syntax error Cresult are sorted in ascending order by default DORDER BY 2 will take the second column as sorting column. Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 2-22