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

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)

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.

Q2. - (Topic 2)

The EMPLOYEES table contains these columns:

EMPLOYEE_ID NUMBER(4)

LAST_NAME VARCHAR2 (25)

JOB_ID VARCHAR2(10)

You want to search for strings that contain 'SA_' in the JOB_ID column. Which SQL statement do you use?

A. SELECT employee_id, last_name, job_id FROM employees WHERE job_id LIKE '%SA\_' ESCAPE '\'

B. SELECT employee_id, last_name, job_id FROM employees WHERE job_id LIKE '%SA_'

C. SELECT employee_id, last_name, job_id FROM employees WHERE job_id LIKE '%SA_' ESCAPE "\";

D. SELECT employee_id, last_name, job_id FROM employees WHERE job_id = '%SA_'

Answer: A

Explanation: ESCAPE identifier to search for the _ symbol

Incorrect Answer: BESCAPE identifier must be use Cwrong syntax Dwrong syntax

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

Q3. - (Topic 1)

View the Exhibit and examine the description for the CUSTOMERS table.

You want to update the CUST_INCOME_LEVEL and CUST_CREDIT_LIMIT columns for the customer with the CUST_ID 2360. You want the value for the CUST_INCOME_LEVEL to have the same value as that of the customer with the CUST_ID 2560 and the CUST_CREDIT_LIMIT to have the same value as that of the customer with CUST_ID 2566.

Which UPDATE statement will accomplish the task?

UPDATE customers SET cust_income_level = (SELECT cust_income_level FROM customers WHERE cust_id = 2560), cust_credit_limit = (SELECT cust_credit_limit FROM customers WHERE cust_id = 2566) WHERE cust_id=2360;

UPDATE customers SET (cust_income_level,cust_credit_limit) = (SELECT cust_income_level, cust_credit_limit FROM customers WHERE cust_id=2560 OR cust_id=2566) WHERE cust_id=2360;

UPDATE customers SET (cust_income_level,cust_credit_limit) = (SELECT cust_income_level, cust_credit_limit FROM customers WHERE cust_id IN(2560, 2566) WHERE cust_id=2360;

UPDATE customers SET (cust_income_level,cust_credit_limit) = (SELECT cust_income_level, cust_credit_limit FROM customers WHERE cust_id=2560 AND cust_id=2566) WHERE cust_id=2360;

Answer: A

Explanation:

Updating Two Columns with a Subquery

You can update multiple columns in the SET clause of an UPDATE statement by writing

multiple subqueries. The syntax is as follows:

UPDATE table

SET column =

(SELECT column

FROM table

WHERE condition)

[ ,

column =

(SELECT column

FROM table

WHERE condition)]

[WHERE condition ] ;

Q4. - (Topic 2)

Which two statements complete a transaction? (Choose two)

A. DELETE employees;

B. DESCRIBE employees;

C. ROLLBACK TO SAVEPOINT C;

D. GRANT SELECT ON employees TO SCOTT;

E. ALTER TABLE employeesSET UNUSED COLUMN sal;

F. Select MAX(sal)FROM employeesWHERE department_id = 20;

Answer: D,E

Explanation:

D: GRANT is a DML operation which will cause an implicit commit

E: It is important to understand that an implicit COMMIT occurs on the database when a user exits SQL*Plus or issues a data-definition language (DDL) command such as a CREATE TABLE statement, used to create a database object, or an ALTER TABLE statement, used to alter a database object.

Incorrect Answers A:The DELETE command is data-manipulation language (DML) command and it does not complete a transaction. B:The DESCRIBE command is internal SQL*Plus command and it has nothing to do with completion a transaction.

C: ROLLBACK is not used to commit or complete a transaction, it is used to undo a transaction F:SELECT command is used to retrieve data. It does not complete a transaction.

OCP Introduction to Oracle 9i: SQL Exam Guide, Jason Couchman, p. 281-282 Chapter 3: Advanced Data Selection in Oracle

Q5. - (Topic 1)

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

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

Q6. - (Topic 1)

View the Exhibit and examine the structure of the PROMOTIONS table. Evaluate the following SQL statement:

The above query generates an error on execution.

Which clause in the above SQL statement causes the error?

A. WHERE

B. SELECT

C. GROUP BY

D. ORDER BY

Answer: C

Q7. - (Topic 1)

You work as a database administrator at ABC.com. You study the exhibit carefully.

Exhibit:

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.

Q8. - (Topic 1)

User Mary has a view called EMP_DEPT_LOC_VU that was created based on the EMPLOYEES, DEPARTMENTS, and LOCATIONS tables. She has the privilege to create a public synonym, and would like to create a synonym for this view that can be used by all users of the database.

Which SQL statement can Mary use to accomplish that task?

A. CREATE PUBLIC SYNONYM EDL_VU ON emp_dept_loc_vu;

B. CREATE PUBLIC SYNONYM EDL:VU FOR mary (emp_dept_loc_vu);

C. CREATE PUBLIC SYNONYM EDL_VU FOR emp_dept_loc_vu;

D. CREATE SYNONYM EDL_VU ON emp_dept_loc_vu FOR EACH USER;

E. CREATE SYNONYM EDL_VU FOR EACH USER ON emp_dept_loc_vu;

F. CREATE PUBLIC SYNONYM EDL_VU ON emp_dept_loc_vu FOR ALL USERS;

Answer: C

Explanation:

The general syntax to create a synonym is:

CREATE [PUBLIC] SYNONYM synonym FOR object;

Q9. - (Topic 1)

A SELECT statement can be used to perform these three functions:

Choose rows from a table.

Choose columns from a table

Bring together data that is stored in different tables by creating a link between

them.

Which set of keywords describes these capabilities?

A. difference, projection, join

B. selection, projection, join

C. selection, intersection, join

D. intersection, projection, join

E. difference, projection, product

Answer: B

Explanation: Explanation: choose rows from a table is SELECTION,

Choose column from a table is PROJECTION

Bring together data in different table by creating a link between them is JOIN.

Incorrect Answer:

Aanswer should have SELECTION, PROJECTION and JOIN.

Canswer should have SELECTION, PROJECTION and JOIN.

Danswer should have SELECTION, PROJECTION and JOIN.

Eanswer should have SELECTION, PROJECTION and JOIN.

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

Q10. - (Topic 2)

Which two statements are true regarding the DELETE and TRUNCATE commands? (Choose two.)

A. DELETE can be used to remove only rows from only one table at a time.

B. DELETE can be used to remove only rows from multiple tables at a time.

C. DELETE can be used only on a table that is a parent of a referential integrity constraint.

D. DELETE can be used to remove data from specific columns as well as complete rows.

E. DELETE and TRUNCATE can be used on a table that is a parent of a referential integrity constraint having ON DELETE rule.

Answer: A,E

Explanation:

Transactions, consisting of INSERT, UPDATE, and DELETE (or even MERGE) commands can be made permanent (with a COMMIT) or reversed (with a ROLLBACK). A TRUNCATE command, like any other DDL command, is immediately permanent: it can never be reversed. The Transaction Control Statements A transaction begins implicitly with the first DML statement. There is no command to explicitly start a transaction. The transaction continues through all subsequent DML statements issued by the session. These statements can be against any number of tables: a transaction is not restricted to one table. It terminates (barring any of the events listed in the previous section) when the session issues a COMMIT or ROLLBACK command. The SAVEPOINT command can be used to set markers that will stage the action of a ROLLBACK, but the same transaction remains in progress irrespective of the use of SAVEPOINT Explicit Transaction Control Statements You can control the logic of transactions by using the COMMIT, SAVEPOINT, and ROLLBACK statements. Note: You cannot COMMIT to a SAVEPOINT. SAVEPOINT is not ANSI-standard SQL.

untitled