[Free] 2019(Nov) EnsurePass Oracle 1z0-061 Dumps with VCE and PDF 211-220

Get Full Version of the Exam
http://www.EnsurePass.com/1z0-061.html

Question No.211

Evaluate the following SQL statements:

image

Which is the correct output of the above query? A. 00-300, 54-02, 00 11:12:10.123457

B. 00-300, 00-650, 00 11:12:10.123457

C. 25-00, 54-02, 00 11:12:10.123457

D. 25-00, 00-650, 00 11:12:10.123457

Correct Answer: C

Question No.212

Which two statements are true regarding the ORDER BY clause? (Choose two.)

  1. It is executed first in the query execution.

  2. It must be the last clause in the SELECT statement.

  3. It cannot be used in a SELECT statement containing a HAVING clause.

  4. You cannot specify a column name followed by an expression in this clause.

  5. You can specify a combination of numeric positions and column names in this clause.

Correct Answer: BE

Question No.213

The PART_CODE column in the SPARES table contains the following list of values:

image

Which statement is true regarding the outcome of the above query?

  1. It produces an error.

  2. It displays all values.

  3. It displays only the values A%_WQ123 and AB_WQ123 .

  4. It displays only the values A%_WQ123 and A%BWQ123 .

  5. It displays only the values A%BWQ123 and AB_WQ123.

Correct Answer: D

Explanation:

Combining Wildcard Characters

The % and _ symbols can be used in any combination with literal characters. The example in the slide displays the names of all employees whose last names have the letter quot;oquot; as the second character.

ESCAPE Identifier

When you need to have an exact match for the actual % and _ characters, use the ESCAPE identifier. This option specifies what the escape character is. If you want to search for strings that contain SA_, you can use the following SQL statement:

SELECT employee_id, last_name, job_id

FROM employees WHERE job_id LIKE #39;%SA\_%#39; ESCAPE #39;\#39;;

Question No.214

Evaluate the following SQL statement:

SQLgt; SELECT cust_id, cust_last_name quot;Last Namequot; FROM customers

WHERE country_id = 10 UNION

SELECT cust_id CUST_NO, cust_last_name FROM customers

WHERE country_id = 30;

Which ORDER BY clause are valid for the above query? (Choose all that apply.)

  1. ORDER BY 2, 1

  2. ORDER BY CUST_NO

  3. ORDER BY 2, cust_id

  4. ORDER BY quot;CUST_NOquot;

  5. ORDER BY quot;Last Namequot;

Correct Answer: AC

Explanation:

Using the ORDER BY Clause in Set Operations

The ORDER BY clause can appear only once at the end of the compound query. Component queries cannot have individual ORDER BY clauses.

The ORDER BY clause recognizes only the columns of the first SELECT query.

By default, the first column of the first SELECT query is used to sort the output in an ascending order.

Question No.215

Which SQL statement would you use to remove a view called EMP_DEPT_VU you#39;re your schema?

  1. DROP emp_dept_vu;

  2. DELETE emp_dept_vu;

  3. REMOVE emp_dept_vu;

  4. DROP VIEW emp_dept_vu;

  5. DELETE VIEW emp_dept_vu;

  6. REMOVE VIEW emp_dept_vu;

Correct Answer: D

Explanation:

DROP VIEW viewname;

Incorrect answer:

  1. Not a valid drop view statement

  2. Not a valid drop view statement

  3. Not a valid drop view statement

  1. Not a valid drop view statement

  2. Not a valid drop view statement

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

Question No.216

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?

  1. CREATE PUBLIC SYNONYM EDL_VU. ON emp_dept_loc_vu;

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

  3. CREATE PUBLIC SYNONYM EDL_VU. FOR emp_dept_loc_vu;

  4. CREATE SYNONYM EDL_VU. ON emp_dept_loc_vu. FOR EACH USER;

  5. CREATE SYNONYM EDL_VU. FOR EACH USER. ON emp_dept_loc_vu;

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

Correct Answer: C

Explanation:

The general syntax to create a synonym is:

CREATE [PUBLIC] SYNONYM synonym FOR object;

Question No.217

Which substitution variable would you use if you want to reuse the variable without prompting the user each time?

  1. amp;

  2. ACCEPT

  3. PROMPT

  4. amp;amp;

Correct Answer: D

Explanation:

To reuse the variable without prompting the user each time you can use amp;amp; substitution variable.

Incorrect Answers

A: This substitution variable will prompt the user each time.

B: ACCEPT is command, not substitution variable. It used to define more accurate or specific prompt or when you want more output to display as the values are defined.

C: PROMPT is part of the ACCEPT command, it is not a variable.

OCP Introduction to Oracle 9i: SQL Exam Guide, Jason Couchman, p. 165-173 Chapter 4: Sub queries

Question No.218

View the Exhibit and examine the structure of ORDERS and CUSTOMERS tables. There is only one customer with the cus_last_name column having value Roberts. Which INSERT statement should be used to add a row into the ORDERS table for the customer whose CUST_LAST_NAME is Roberts and CREDIT_LIMIT is 600?

image

  1. INSERT INTO orders VALUES (l.#39;10-mar-2007\ #39;direct#39;. (SELECT customeridFROM customersWHERE cust_last_iiame=#39;Roberts#39; ANDcredit_limit=600). 1000);

  2. . INSERT INTO orders (order_id.order_date.order_mode. (SELECT customer idFROM customersWHERE cust_last_iiame=#39;Roberts#39; ANDredit_limit=600).order_total)VALUES(L#39;10-mar-2007#39;. #39;direct#39;, amp;amp;customer_id, 1000):

  3. INSERT INTO(SELECT o.order_id. o.order_date.o.order_modex.customer_id. o.ordertotalFROM orders o. customers cWHERE o.customer_id = c.customeridAND c.cust_la$t_name-RoberTs#39; ANDc.credit_liinit=600)VALUES (L#39;10-mar-2007\ #39;direct#39;.( SELECT customer_idFROM customersWHERE cust_last_iiame=#39;Roberts#39; ANDcredit_limit=600). 1000);

  4. INSERT INTO orders (order_id.order_date.order_mode.(SELECT customer_idFROM customersWHERE cust_last_iiame=#39;Roberts#39; ANDcredit_limit=600).order_total)VALUES(l.#39;10-mar-2007\ #39;direct#39;. amp;customer_id. 1000):

Correct Answer: A

Question No.219

Which two statements complete a transaction? (Choose two)

  1. DELETE employees;

  2. DESCRIBE employees;

  3. ROLLBACK TO SAVEPOINT C;

  4. GRANT SELECT ON employees TO SCOTT;

  5. ALTER TABLE employeesSET UNUSED COLUMN sal;

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

Correct Answer: DE

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

Question No.220

Examine the structure of the EMPLOYEES table: EMPLOYEE_ID NUMBER NOT NULL, Primary Key EMP_NAME VARCHAR2(30)

JOB_ID NUMBER\ SAL NUMBER

MGR_ID NUMBER . References EMPLOYEE_ID column

DEPARTMENT_ID NUMBER . Foreign key to DEPARTMENT_ID column of the. . DEPARTMENTS table

You created a sequence called EMP_ID_SEQ in order to populate sequential values for the EMPLOYEE_ID column of the EMPLOYEES table.

Which two statements regarding the EMP_ID_SEQ sequence are true? (Choose two.)

  1. You cannot use the EMP_ID_SEQ sequence to populate the JOB_ID column.

  2. The EMP_ID_SEQ sequence is invalidated when you modify the EMPLOYEE_ID column.

  3. The EMP_ID_SEQ sequence is not affected by modifications to the EMPLOYEES table.

  4. Any other column of NUMBER data type in your schema can use the EMP_ID_SEQ sequence.

  5. The EMP_ID_SEQ sequence is dropped automatically when you drop the EMPLOYEES table.

  6. The EMP_ID_SEQ sequence is dropped automatically when you drop the EMPLOYEE_ID column.

Correct Answer: CD

Explanation:

the EMP_ID_SEQ sequence is not affected by modification to the EMPLOYEES table. Any other column of NUMBER data type in your schema can use the EMP_ID_SEQ sequence.

Incorrect answer:

  1. EMP_ID_SEQ sequence can be use to populate JOB_ID

  2. EMP_ID_SEQ sequence will not be invalidate when column in EMPLOYEE_ID is modify.

  1. EMP_ID_SEQ sequence will be dropped automatically when you drop the EMPLOYEES table.

  2. EMP_ID_SEQ sequence will be dropped automatically when you drop the EMPLOYEE_ID column.

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

Get Full Version of the Exam
1z0-061 Dumps
1z0-061 VCE and PDF

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.