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

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

Question No.231

You need to extract details of those products in the SALES table where the PROD_ID column contains the string #39;_D123#39;. Which WHERE clause could be used in the SELECT statement to get the required output?

  1. WHERE prod_id LIKE #39;%_D123%#39; ESCAPE #39;_#39;

  2. WHERE prod_id LIKE #39;%\_D123%#39; ESCAPE #39;\#39;

  3. WHERE prod_id LIKE #39;%_D123%#39; ESCAPE #39;%_#39;

  4. WHERE prod_id LIKE #39;%\_D123%#39; ESCAPE #39;\_#39;

Correct Answer: B

Explanation:

A naturally occurring underscore character may be escaped (or treated as a regular nonspecial symbol) using the ESCAPE identifier in conjunction with an ESCAPE character. The second example in Figure 3-12 shows the SQL statement that retrieves the JOBS table records with JOB_ID values equal to SA_MAN and SA_REP and which conforms to the original requirement: select job_id from jobs

where job_id like #39;SA\_%#39; escape #39;\#39;;

Question No.232

Examine the structure of the EMP_DEPT_VU view:

image

Which SQL statement produces an error?

  1. SELECT *FROM emp_dept_vu;

  2. SELECT department_id, SUM(salary) FROM emp_dept_vu GROUP BY department_id;

  3. SELECT department_id, job_id, AVG(salary) FROM emp_dept_vu GROUP BY department_id, job_id;

  4. SELECT job_id, SUM(salary) FROM emp_dept_vu WHERE department_id IN (10, 20) GROUP BY job_id HAVING SUM(salary) gt; 20000;

  5. None of the statements produce an error; all are valid.

Correct Answer: E

Explanation:

None of the statements produce an error.

Incorrect answer:

  1. Statement will not cause error

  2. Statement will not cause error

  3. Statement will not cause error

  4. Statement will not cause error

Question No.233

Which two partitioned table maintenance operations support asynchronous Global Index Maintenance in Oracle database 12c?

  1. ALTER TABLE SPLIT PARTITION

  2. ALTER TABLE MERGE PARTITION

  3. ALTER TABLE TRUNCATE PARTITION

  4. ALTER TABLE ADD PARTITION

  5. ALTER TABLE DROP PARTITION

  6. ALTER TABLE MOVE PARTITION

Correct Answer: CE

Explanation:

Asynchronous Global Index Maintenance for DROP and TRUNCATE PARTITION This feature enables global index maintenance to be delayed and decoupled from a DROP and TRUNCATE partition without making a global index unusable. Enhancements include faster DROP and TRUNCATE partition operations and the ability to delay index maintenance to off-peak time.

Question No.234

You want to create an ORD_DETAIL table to store details for an order placed having the following business requirement:

  1. The order ID will be unique and cannot have null values.

  2. The order date cannot have null values and the default should be the current date.

  3. The order amount should not be less than 50.

  4. The order status will have values either shipped or not shipped.

  5. The order payment mode should be cheque, credit card, or cash on delivery (COD). Which is the valid DDL statement for creating the ORD_DETAIL table?

  1. CREATE TABLE ord_details(ord_id NUMBER(2) CONSTRAINT ord_id_nn NOT NULL,ord_date DATE DEFAULT SYSDATE NOT NULL,ord_amount NUMBER(5, 2) CONSTRAINT ord_amount_minCHECK (ord_amount gt; 50),ord_status VARCHAR2(15) CONSTRAINT ord_status_chkCHECK (ord_status IN (#39;Shipped#39;, #39;Not Shipped#39;)),ord_pay_mode VARCHAR2(15) CONSTRAINT ord_pay_chkCHECK (ord_pay_mode IN (#39;Cheque#39;, #39;Credit Card#39;,#39;Cash On Delivery#39;)));

  2. CREATE TABLE ord_details(ord_id NUMBER(2) CONSTRAINT ord_id_uk UNIQUE NOT NULL,ord_date DATE DEFAULT SYSDATE NOT NULL,ord_amount NUMBER(5, 2) CONSTRAINT ord_amount_minCHECK (ord_amount gt; 50),ord_status VARCHAR2(15) CONSTRAINT ord_status_chkCHECK (ord_status IN (#39;Shipped#39;, #39;Not Shipped#39;)),ord_pay_mode VARCHAR2(15) CONSTRAINT ord_pay_chkCHECK (ord_pay_mode IN (#39;Cheque#39;, #39;Credit Card#39;,#39;Cash On Delivery#39;)));

  3. CREATE TABLE ord_details(ord_id NUMBER(2) CONSTRAINT ord_id_pk PRIMARY KEY,ord_date DATE DEFAULT SYSDATE NOT NULL,ord_amount NUMBER(5, 2) CONSTRAINT ord_amount_minCHECK (ord_amount gt;= 50),ord_status VARCHAR2(15) CONSTRAINT ord_status_chkCHECK (ord_status IN (#39;Shipped#39;, #39;Not Shipped#39;)),ord_pay_mode VARCHAR2(15) CONSTRAINT ord_pay_chkCHECK (ord_pay_mode IN (#39;Cheque#39;, #39;Credit Card#39;,#39;Cash On Delivery#39;)));

  4. CREATE TABLE ord_details(ord_id NUMBER(2),ord_date DATE NOT NULL DEFAULT SYSDATE,ord_amount NUMBER(5, 2) CONSTRAINT ord_amount_minCHECK (ord_amount gt;= 50),ord_status VARCHAR2(15) CONSTRAINT ord_status_chkCHECK (ord_status IN (#39;Shipped#39;, #39;Not Shipped#39;)),ord_pay_mode VARCHAR2(15) CONSTRAINT ord_pay_chkCHECK (ord_pay_mode IN (#39;Cheque#39;, #39;Credit Card#39;,#39;Cash On Delivery#39;)));

Correct Answer: C

Question No.235

Evaluate the SQL statement:

SELECT ROUND(45.953, -1), TRUNC(45.936, 2)

FROM dual;

Which values are displayed?

A. 46 and 45

B. 46 and 45.93

C. 50 and 45.93

D. 50 and 45.9

E. 45 and 45.93

F. 45.95 and 45.93

Correct Answer: C

Explanation:

ROUND (45.953, -1) will round value to 1 decimal places to the left. TRUNC (45.936, 2) will truncate value to 2 decimal The answer will be 50 and 45.93

Question No.236

You need to produce a report for mailing labels for all customers. The mailing label must have only the customer name and address. The CUSTOMERS table has these columns:

CUST_ID. NUMBER(4). NOT NULL CUST_NAME. VARCHAR2(100). NOT NULL CUST_ADDRESS. VARCHAR2(150) CUST_PHONE. VARCHAR2(20)

Which SELECT statement accomplishes this task?

  1. SELECT * FROM customers

  2. SELECT name, address FROM customers;

  3. SELECT id, name, address, phone FROM customers;

  4. SELECT cust_name, cust_address FROM customers;

  5. SELECT cust_id, cust_name, cust_address, cust_phone FROM customers;

Correct Answer: D

Explanation:

This answer provides correct list of columns for the output.

Incorrect Answers

A: This answer does not provide correct list of columns for the output. It is not required to show all columns of the table. Symbol quot;*quot; is used in the SELECT command to substitute a list of all columns of the table.

B: This answer does not provide correct list of columns for the output. There are not NAME and ADDRESS columns in the CUSTOMERS table.

C: This answer does not provide correct list of columns for the output. There are not ID, NAME, ADDRESS or PHONE columns in the CUSTOMERS table.

E: This answer does not provide correct list of columns for the output. It is not required to show all columns of the table.

OCP Introduction to Oracle 9i: SQL Exam Guide, Jason Couchman, p. 20-24 Chapter 1: Overview of Oracle Databases

Question No.237

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

  1. The UNIQUE constraint does not permit a null value for the column.

  2. A UNIQUE index gets created for columns with PRIMARY KEY and UNIQUE constraints.

  3. The PRIMARY KEY and FOREIGN KEY constraints create a UNIQUE index.

  4. The NOT NULL constraint ensures that null values are not permitted for the column.

Correct Answer: BD

Explanation:

B: A unique constraint can contain null values because null values cannot be compared to anything.

D: The NOT NULL constraint ensure that null value are not permitted for the column

Incorrect answer:

A. statement is not true

C. statement is not true

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

Question No.238

View the Exhibit and examine the structure of the PROMOTIONS, SALES, and CUSTOMER tables.

You need to generate a report showing the promo name along with the customer name for all products that were sold during their promo campaign and before 30th October 2007.

You issue the following query:

image

Which statement is true regarding the above query?

  1. It executes successfully and gives the required result.

  2. It executes successfully but does not give the required result.

  3. It produces an error because the join order of the tables is incorrect.

  4. It produces an error because equijoin and nonequijoin conditions cannot be used in the same SELECT statement.

Correct Answer: B

Question No.239

The SQL statements executed in a user session as follows:

image

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

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

  2. The rollback generates an error

  3. Only the DELETE statements are rolled back

  4. Only the seconds DELETE statement is rolled back

  5. No SQL statements are rolled back

Correct Answer: BE

Question No.240

View the Exhibit and examine the structure of the PROMOTIONS table. Using the PROMOTIONS table, you need to display the names of all promos done after January 1, 2001, starting with the latest promo. Which query would give the required result? (Choose all that apply.)

image

  1. . SELECT promo_name, promo_begin_date FROM promotionsWHERE promo_begiii_date gt; #39;01- JAN-01#39; ORDER BY 2 DESC;

  2. . SELECT promo_name. promo_begiii_date FROM promotionsWHERE promo_begin_date gt; #39;01- JAN-01#39; ORDER BY promo_name DESC:

  3. . SELECT promo_name. promo_begin_date FROM promotionsWHERE promo_begin_date gt; #39;01- JAN-01#39; ORDER BY 1DESC:

  4. . SELECT promo_name, promo_begin_date quot;START DATEquot; FROM promotionsWHERE promo_begin_date gt; #39;01-JAN-01#39; ORDER BY quot;START DATEquot; DESC;

Correct Answer: AD

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