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

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)

Which is an iSQL*Plus command?

A. INSERT

B. UPDATE

C. SELECT

D. DESCRIBE

E. DELETE

F. RENAME

Answer: D

Explanation: Explanation:

The only SQL*Plus command in this list: DESCRIBE. It cannot be used as SQL command.

This command returns a description of tablename, including all columns in that table, the

datatype for each column and an indication of whether the column permits storage of NULL

values.

Incorrect Answer:

A INSERT is not a SQL*PLUS command

B UPDATE is not a SQL*PLUS command

C SELECT is not a SQL*PLUS command

E DELETE is not a SQL*PLUS command

F RENAME is not a SQL*PLUS command

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

Q2. - (Topic 2)

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

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. Reference: Oracle Database Concepts 10g, Native Datatypes

Q3. - (Topic 1)

See 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: A,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')

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.

Q4. - (Topic 2)

The user Sue issues this SQL statement:

GRANT SELECT ON sue.EMP TO alice WITH GRANT OPTION;

The user Alice issues this SQL statement:

GRANT SELECT ON sue.EMP TO reena WITH GRANT OPTION;

The user Reena issues this SQL statement:

GRANT SELECT ON sue.EMP TO timber;

The user Sue issues this SQL statement:

REVOKE select on sue.EMP FROM alice;

For which users does the revoke command revoke SELECT privileges on the SUE.EMP table?

A. Alice only

B. Alice and Reena

C. Alice, Reena, and Timber

D. Sue, Alice, Reena, and Timber

Answer: C

Explanation: use the REVOKE statement to revoke privileges granted to other users. Privilege granted to others through the WITH GRANT OPTION clause are also revoked. Alice, Reena and Timber will be revoke.

Incorrect Answer: Athe correct answer should be Alice, Reena and Timber Bthe correct answer should be Alice, Reena and Timber Dthe correct answer should be Alice, Reena and Timber

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

Q5. - (Topic 2)

The DBA issues this SQL command:

CREATE USER scott IDENTIFIED by tiger;

What privileges does the user Scott have at this point?

A. no privileges

B. only the SELECT privilege

C. only the CONNECT privilege

D. all the privileges of a default user

Answer: A

Explanation:

when a user is created, by default no privilege is granted

Incorrect Answer:

BSELECT is not grant

CCONNECT is not grant

Ddefault profile is grant by default not privilege.

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

Q6. - (Topic 2)

Examine the structure of the EMPLOYEES and NEW_EMPLOYEES tables:

EMPLOYEES

EMPLOYEE_ID NUMBER Primary Key

FIRST_NAME VARCHAR2(25)

LAST_NAME VARCHAR2(25)

HIRE_DATE DATE

NEW_EMPLOYEES

EMPLOYEE_ID NUMBER Primary Key

NAME VARCHAR2(60)

Which MERGE statement is valid?

A. MERGE INTO new_employees c USING employees e ON (c.employee_id = e.employee_id) WHEN MATCHED THEN UPDATE SET c.name = e.first_name ||','|| e.last_name WHEN NOT MATCHED THEN INSERT VALUES (e.employee_id, e.first_name ||', '||e.last_name);

B. MERGE new_employees c USING employees e ON (c.employee_id = e.employee_id) WHEN EXISTS THEN UPDATE SET c.name = e.first_name ||','|| e.last_name WHEN NOT MATCHED THEN INSERT VALUES (e.employee_id, e.first_name ||', '||e.last_name);

C. MERGE INTO new_employees c USING employees e ON (c.employee_id = e.employee_id) WHEN EXISTS THEN UPDATE SET c.name = e.first_name ||','|| e.last_name WHEN NOT MATCHED THEN INSERT VALUES(e.employee_id, e.first_name ||', '||e.last_name);

D. MERGE new_employees c FROM employees e ON (c.employee_id = e.employee_id) WHEN MATCHED THEN UPDATE SET c.name = e.first_name ||','|| e.last_name WHEN NOT MATCHED THEN INSERT INTO new_employees VALUES (e.employee_id,

e.first_name ||', '||e.last_name);

Answer: A

Explanation:

The correct statement for MERGE is MERGE INTO table_name Incorrect Answer: BWrong statement with the keyword EXISTS CWrong statement with the keyword EXISTS DWrong statement on the MERGE new_employees

Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 8-29

Q7. - (Topic 1)

Which three statements are true about multiple-row sub queries? (Choose three.)

A. They can contain a subquery within a sub query.

B. They can return multiple columns as well as rows.

C. They cannot contain a sub query within a sub query.

D. They can return only one column but multiple rows.

E. They can contain group functions and GROUP BY and HAVING clauses.

F. They can contain group functions and the GROUP BY clause, but not the HAVING clause.

Answer: A,B,E

Q8. - (Topic 1)

Which statement is true regarding the INTERSECT operator?

A. It ignores NULL values

B. The number of columns and data types must be identical for all SELECT statements in the query

C. The names of columns in all SELECT statements must be identical

D. Reversing the order of the intersected tables the result

Answer: B

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.

Q9. - (Topic 2)

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

Q10. - (Topic 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

Explanation:

Schema Object Naming Rules

Every database object has a name. In a SQL statement, you represent the name of an

object with a quoted identifier or a nonquoted identifier.

A quoted identifier begins and ends with double quotation marks ("). If you name a schema

object using a quoted identifier, then you must use the double quotation marks whenever

you refer to that object.

A nonquoted identifier is not surrounded by any punctuation.

The following list of rules applies to both quoted and nonquoted identifiers unless otherwise

indicated:

Names must be from 1 to 30 bytes long with these exceptions:

Names of databases are limited to 8 bytes.

Names of database links can be as long as 128 bytes.

If an identifier includes multiple parts separated by periods, then each attribute can be up to

30 bytes long.

Each period separator, as well as any surrounding double quotation marks, counts as one

byte. For example, suppose you identify a column like this:

"schema"."table"."column"

Nonquoted identifiers cannot be Oracle Database reserved words (ANSWER D). Quoted identifiers can be reserved words, although this is not recommended. Depending on the Oracle product you plan to use to access a database object, names might be further restricted by other product-specific reserved words. The Oracle SQL language contains other words that have special meanings. These words include datatypes, schema names, function names, the dummy system table DUAL, and keywords (the uppercase words in SQL statements, such as DIMENSION, SEGMENT, ALLOCATE, DISABLE, and so forth). These words are not reserved. However, Oracle uses them internally in specific ways. Therefore, if you use these words as names for objects and object parts, then your SQL statements may be more difficult to read and may lead to unpredictable results. In particular, do not use words beginning with SYS_ as schema object names, and do not use the names of SQL built-in functions for the names of schema objects or user-defined functions. You should use ASCII characters in database names, global database names, and database link names, because ASCII characters provide optimal compatibility across different platforms and operating systems. Nonquoted identifiers must begin with an alphabetic character (ANSWER B - begins with 9) from your database character set. Quoted identifiers can begin with any character. Nonquoted identifiers can contain only alphanumeric characters from your database character set and the underscore (_), dollar sign ($), and pound sign (#). Database links can also contain periods (.) and "at" signs (@). Oracle strongly discourages you from using $ and # in nonquoted identifiers. Quoted identifiers can contain any characters and punctuations marks as well as spaces. However, neither quoted nor nonquoted identifiers can contain double quotation marks or the null character (\0). Within a namespace, no two objects can have the same name. Nonquoted identifiers are not case sensitive. Oracle interprets them as uppercase. Quoted identifiers are case sensitive. By enclosing names in double quotation marks, you can give the following names to different objects in the same namespace: employees "employees" "Employees" "EMPLOYEES"

Note that Oracle interprets the following names the same, so they cannot be used for different objects in the same namespace: employees EMPLOYEES "EMPLOYEES" Columns in the same table or view cannot have the same name. However, columns in different tables or views can have the same name. Procedures or functions contained in the same package can have the same name, if their arguments are not of the same number and datatypes. Creating multiple procedures or functions with the same name in the same package with different arguments is called overloading the procedure or function.