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

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)

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

Explanation:

So, how are words that contain single quotation marks dealt with? There are essentially two mechanisms available. The most popular of these is to add an additional single quotation mark next to each naturally occurring single quotation mark in the character string Oracle offers a neat way to deal with this type of character literal in the form of the alternative quote (q) operator. Notice that the problem is that Oracle chose the single quote characters as the special pair of symbols that enclose or wrap any other character literal. These character-enclosing symbols could have been anything other than single quotation marks. Bearing this in mind, consider the alternative quote (q) operator. The q operator enables you to choose from a set of possible pairs of wrapping symbols for character literals as alternatives to the single quote symbols. The options are any single-byte or multibyte character or the four brackets: (round brackets), {curly braces}, [squarebrackets], or <angle brackets>. Using the q operator, the character delimiter can effectively be changed from a single quotation mark to any other character The syntax of the alternative quote operator is as follows: q'delimiter'character literal which may include the single quotes delimiter' where delimiter can be any character or bracket.

Alternative Quote (q) Operator

Specify your own quotation mark delimiter.

Select any delimiter.

Increase readability and usability.

SELECT department_name || q'[ Department's Manager Id: ]'

|| manager_id

AS "Department and Manager"

FROM departments;

Alternative Quote (q) Operator

Many SQL statements use character literals in expressions or conditions. If the literal itself

contains a single quotation mark, you can use the quote (q) operator and select your own

quotation mark delimiter.

You can choose any convenient delimiter, single-byte or multibyte, or any of the following

character pairs: [ ], { }, ( ), or < >. In the example shown, the string contains a single quotation mark, which is normally interpreted as a delimiter of a character string. By using the q operator, however, brackets [] are used as the quotation mark delimiters. The string between the brackets delimiters is interpreted as a literal character string.

Q2. - (Topic 1)

Which three tasks can be performed using SQL functions built into Oracle Database? (Choose three.)

A. Combining more than two columns or expressions into a single column in the output

B. Displaying a date in a nondefault format

C. Substituting a character string in a text expression with a specified string

D. Finding the number of characters in an expression

Answer: B,C,D

Q3. - (Topic 1)

You issue the following command to drop the PRODUCTS table:

SQL>DROP TABLE products;

What is the implication of this command? (Choose all that apply.)

A. All data in the table are deleted but the table structure will remain

B. All data along with the table structure is deleted

C. All views and synonyms will remain but they are invalidated

D. The pending transaction in the session is committed

E. All indexes on the table will remain but they are invalidated

Answer: B,C,D

Q4. - (Topic 2)

Which SQL statements would display the value 1890.55 as $1,890.55? (Choose three.)

SELECT TO_CHAR(1890.55,'$0G000D00') FROM DUAL;

SELECT TO_CHAR(1890.55,'$9,999V99') FROM DUAL;

SELECT TO_CHAR(1890.55,'$99,999D99') FROM DUAL;

SELECT TO_CHAR(1890.55,'$99G999D00') FROM DUAL;

E. SELECT TO_CHAR(1890.55,'$99G999D99') FROM DUAL;

Answer: A,D,E

Q5. - (Topic 2)

In which two cases would you use an outer join? (Choose two.)

A. The tables being joined have NOT NULL columns.

B. The tables being joined have only matched data.

C. The columns being joined have NULL values.

D. The tables being joined have only unmatched data.

E. The tables being joined have both matched and unmatched data.

F. Only when the tables have a primary key/foreign key relationship.

Answer: C,E

Explanation:

You use an outer join to also see rows that do not meet the join condition.

Incorrect Answer: Ameet a join condition Bmeet a join condition Dmeet non join condition only Fdoes not take into consideration of primary key and foreign key relationship

Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 4-17

Q6. - (Topic 1)

Which are /SQL*Plus commands? (Choose all that apply.)

A. INSERT

B. UPDATE C. SELECT

D. DESCRIBE

E. DELETE

F. RENAME

Answer: D

Explanation:

Describe is a valid iSQL*Plus/ SQL*Plus command.

INSERT, UPDATE & DELETE are SQL DML Statements. A SELECT is an ANSI Standard

SQL Statement not an iSQL*Plus Statement.

RENAME is a DDL Statement.

Q7. - (Topic 2)

The STUDENT_GRADES table has these columns:

STUDENT_ID NUMBER(12)

SEMESTER_END DATE

GPA NUMBER(4,3)

The registrar requested a report listing the students' grade point averages (GPA) sorted from highest grade point average to lowest.

Which statement produces a report that displays the student ID and GPA in the sorted order requested by the registrar?

A. SELECT student_id, gpa FROM student_grades ORDER BY gpa ASC;

B. SELECT student_id, gpa FROM student_grades SORT ORDER BY gpa ASC;

C. SELECT student_id, gpa FROM student_grades SORT ORDER BY gpa;

D. SELECT student_id, gpa FROM student_grades ORDER BY gpa;

E. SELECT student_id, gpa FROM student_grades SORT ORDER BY gpa DESC;

F. SELECT student_id, gpa FROM student_grades ORDER BY gpa DESC;

Answer: F

Explanation:

sorted by highest to lowest is DESCENDING order

Incorrect Answer: Aresult in ascending order Bwrong syntax with SORT keyword Cwrong syntax with SORT keyword Ddefault value for ORDER by is in ascending order Ewrong syntax with SORT keyword

Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 2-22

Q8. - (Topic 1)

See the Exhibit and examine the structure and data in the INVOICE table: Exhibit:

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

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

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

C. SELECT (AVG(inv_date) FROM invoice;

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

Answer: A,D

Q9. - (Topic 1)

You need to create a table for a banking application. One of the columns in the table has the following requirements:

You want a column in the table to store the duration of the credit period

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

The maximum period of the credit provision in the application is 30 days

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. INTERVAL YEAR TO MONTH

B. NUMBER

C. TIMESTAMP

D. DATE

E. INTERVAL DAY TO SECOND

Answer: E

Q10. - (Topic 1)

Which two statements are true regarding sub queries? (Choose two.)

A. A sub query can retrieve zero or more rows.

B. Only two sub queries can be placed at one level.

C. A sub query can be used only in SQL query statements.

D. A sub query can appeal* on either side of a comparison operator.

E. There is no limit on the number of sub query levels in the WHERE clause of a SELECT statement.

Answer: A,D