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

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 2)

Which two statements about creating constraints are true? (Choose two)

A. Constraint names must start with SYS_C.

B. All constraints must be defines at the column level.

C. Constraints can be created after the table is created.

D. Constraints can be created at the same time the table is created.

E. Information about constraints is found in the VIEW_CONSTRAINTS dictionary view.

Answer: C,D

Explanation:

Constraints can be created after the table is created. Use ALTER TABLE command for

that.

Constraints can be created at the same time the table is created (CREATE TABLE

command).

Incorrect Answers

A:There is no requirements in Oracle that constraint names must start with SYS_C. Oracle

can use prefix “SYS” to build indexes for UNIQUE and NOT NULL constraints, but it is not

required for user to follow this naming rule.

B:Not all constraints must be defines at the column level. Only NOT NULL constraint must

be.

E:There is no VIEW_CONSTRAINTS dictionary view in Oracle.

OCP Introduction to Oracle 9i: SQL Exam Guide, Jason Couchman, p. 227-232

Chapter 5: Creating Oracle Database Objects

Q2. - (Topic 1)

The SQL statements executed in a user session as follows: Exhibit:

Which two statements describe the consequence of issuing the ROLLBACK TO SAVE POINT a command in the session? (Choose two.)

A. Both the DELETE statements and the UPDATE statement are rolled back

B. The rollback generates an error

C. Only the DELETE statements are rolled back

D. Only the seconds DELETE statement is rolled back

E. No SQL statements are rolled back

Answer: B,E

Q3. - (Topic 2)

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

Which statements are true regarding data type conversion in expressions used in queries? (Choose all that apply.)

A. inv_amt ='0255982' : requires explicit conversion

B. inv_date > '01-02-2008' : uses implicit conversion

C. CONCAT(inv_amt,inv_date) : requires explicit conversion

D. inv_date = '15-february-2008' : uses implicit conversion

E. inv_no BETWEEN '101' AND '110' : uses implicit conversion

Answer: D,E

Explanation:

In some cases, the Oracle server receives data of one data type where it expects data of a different data type. When this happens, the Oracle server can automatically convert the data to the expected data type. This data type conversion can be done implicitly by the Oracle server or explicitly by the user. Explicit data type conversions are performed by using the conversion functions. Conversion functions convert a value from one data type to another. Generally, the form of the function names follows the convention data type TO data type. The first data type is the input data type and the second data type is the output. Note: Although implicit data type conversion is available, it is recommended that you do the explicit data type conversion to ensure the reliability of your SQL statements.

Q4. - (Topic 1)

Which object privileges can be granted on a view?

A. none

B. DELETE, INSERT,SELECT

C. ALTER, DELETE, INSERT, SELECT

D. DELETE, INSERT, SELECT, UPDATE

Answer: D

Explanation: Object privilege on VIEW is DELETE, INSERT, REFERENCES, SELECT and UPDATE.

Incorrect Answer: AObject privilege on VIEW is DELETE, INSERT, REFERENCES, SELECT and UPDATE BObject privilege on VIEW is DELETE, INSERT, REFERENCES, SELECT and UPDATE CObject privilege on VIEW is DELETE, INSERT, REFERENCES, SELECT and UPDATE

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

Q5. - (Topic 2)

Which SQL statement would you use to remove a view called EMP_DEPT_VU from your schema?

A. DROP emp_dept_vu;

B. DELETE emp_dept_vu;

C. REMOVE emp_dept_vu;

D. DROP VIEW emp_dept_vu;

E. DELETE VIEW emp_dept_vu;

F. REMOVE VIEW emp_dept_vu;

Answer: D

Explanation:

DROP VIEW viewname;

Incorrect Answer: ANot a valid drop view statement BNot a valid drop view statement CNot a valid drop view statement ENot a valid drop view statement FNot a valid drop view statement

Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 11-20

Q6. - (Topic 2)

Which SQL statement returns a numeric value?

A. SELECT ADD_MONTHS(MAX(hire_Date), 6) FROM EMP;

B. SELECT ROUND(hire_date) FROM EMP;

C. SELECT sysdate-hire_date FROM EMP;

D. SELECT TO_NUMBER(hire_date + 7) FROM EMP;

Answer: C

Explanation:

DATE value subtract DATE value will return numeric value.

Incorrect Answer: Adoes not return numeric value Bdoes not return numeric value Ddoes not return numeric value

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

Q7. - (Topic 1)

See the Exhibit and examine the structure of the SALES, CUSTOMERS, PRODUCTS and ITEMS tables:

The PROD_ID column is the foreign key in the SALES table, which references the PRODUCTS table. Similarly, the CUST_ID and TIME_ID columns are also foreign keys in the SALES table referencing the CUSTOMERS and TIMES tables, respectively.

Evaluate the following the CREATE TABLE command:

Exhibit:

Which statement is true regarding the above command?

A. The NEW_SALES table would not get created because the column names in the CREATE TABLE command and the SELECT clause do not match

B. The NEW_SALES table would get created and all the NOT NULL constraints defined on the specified columns would be passed to the new table

C. The NEW_SALES table would not get created because the DEFAULT value cannot be specified in the column definition

D. The NEW_SALES table would get created and all the FOREIGN KEY constraints defined on the specified columns would be passed to the new table

Answer: B

Explanation:

Creating a Table Using a Subquery

Create a table and insert rows by combining the CREATE

TABLE statement and the AS subquery option.

CREATE TABLE table

[(column, column...)]

AS subquery;

Match the number of specified columns to the number of subquery columns.

Define columns with column names and default values.

Guidelines

The table is created with the specified column names, and the rows retrieved by the

SELECT statement are inserted into the table.

The column definition can contain only the column name and default value.

If column specifications are given, the number of columns must equal the number of

columns in the subquery SELECT list.

If no column specifications are given, the column names of the table are the same as the

column names in the subquery.

The column data type definitions and the NOT NULL constraint are passed to the new

table. Note that only the explicit NOT NULL constraint will be inherited. The PRIMARY KEY

column will not pass the NOT NULL feature to the new column. Any other constraint rules

are not passed to the new table. However, you can add constraints in the column definition.

Q8. - (Topic 2)

You are granted the CREATE VIEW privilege. What does this allow you to do?

A. Create a table view.

B. Create a view in any schema.

C. Create a view in your schema.

D. Create a sequence view in any schema.

E. Create a view that is accessible by everyone.

F. Create a view only of it is based on tables that you created.

Answer: C

Explanation:

You can create a view in your own schema only if you are granted the CREATE VIEW

privilege.

Incorrect Answers

A:You can create a view in your own schema only.

B:You can create a view in your own schema only, not in any schema.

D:There is no sequence view in Oracle.

E:You cannot create a view that is accessible by everyone. You will need specially grant

SELECT privileges on this view for everyone.

F:You can create a view in your own schema, but not only for tables in your schema. You

can use object from other users schemas if you have privileges to retrieve data from them.

OCP Introduction to Oracle 9i: SQL Exam Guide, Jason Couchman, p. 292-301

Chapter 7: Creating Other Database Objects in Oracle

Q9. - (Topic 1)

See the Exhibit and Examine the structure of the CUSTOMERS table:

Using the CUSTOMERS table, you 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')

Q10. - (Topic 1)

Evaluate the following SQL statements:

DELETE FROM sales;

There are no other uncommitted transactions on the SALES table.

Which statement is true about the DELETE statement?

A. It removes all the rows as well as the structure of the table

B. It removes all the rows in the table and deleted rows cannot be rolled back

C. It removes all the rows in the table and deleted rows can be rolled back

D. It would not remove the rows if the table has a primary key

Answer: C