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

Get Full Version of the Exam

Question No.21

There is a simple view SCOTT.DEPT_VIEW on the table SCOTT.DEPT. This insert fails with an error:

SQLgt; insert into dept_view values(#39;SUPPORT#39;,#39;OXFORD#39;); insert into dept_view values(#39;SUPPORT#39;,#39;OXFORD#39;)


ERROR at line 1:

ORA-01400: cannot insert NULL into (quot;SCOTTquot;.quot;DEPTquot;.quot;DEPTNOquot;)

What might be the problem?

  1. The INSERT violates a constraint on the detail table.

  2. The INSERT violates a constraint on the view.

  3. The view was created as WITH READ ONLY.

  4. The view was created as WITH CHECK OPTION.

Correct Answer: A


B is incorrect because constraints are enforced on detail tables, not on views. C and D are incorrect because the error message would be different.

Question No.22

In the customers table, the CUST_CITY column contains the value #39;Paris#39; for the CUST_FIRST_NAME #39;Abigail#39;.

Evaluate the following query:


What would be the outcome?

  1. Abigail PA

  2. Abigail Pa

  3. Abigail IS

  4. An error message

Correct Answer: B

Question No.23

Examine the structure of the customers table:


CUSTNO is the primary key in the table. You want to find out if any customers#39; details have been entered more than once using different CUSTNO, by listing all the duplicate names.

Which two methods can you use to get the required result?

  1. Self-join

  2. Subquery

  3. Full outer-join with self-join

  4. Left outer-join with self-join

  5. Right outer-join with self-join

Correct Answer: AB

Question No.24

Examine the structure of the sales table:


Evaluate the following create table statement:


Which two statements are true about the creation of the SALES1 table?

  1. The SALES1 table is created with no rows but only a structure.

  2. The SALES1 table would have primary key and unique constraints on the specified columns.

  3. The SALES1 table would not be created because of the invalid where clause.

  4. The SALES1 table would have not null and unique constraints on the specified columns.

  5. The SALES1 table would not be created because column-specified names in the select and create table clauses do not match,

Correct Answer: AD


A: the WHERE clause 1=2 is false so no rows will be inserted into the new table.

D: The NOT NULL constraints will be included in the new table. There are no UNIQUE constraints.

Question No.25

Evaluate the following query:


What would be the outcome of the above query?

  1. It produces an error because flower braces have been used.

  2. It produces an error because the data types are not matching.

  3. It executes successfully and introduces an #39;s at the end of each PROMO_NAME in the output.

  4. It executes successfully and displays the literal quot;{#39;s start date was \} * for each row in the output.

Correct Answer: C


So, how are words that contain single quotation marks dealt with? There are essentially two mechanisms available. The most popular of these is to add an additional single quotation mark next to each naturally occurring single quotation mark in the character string.

Oracle offers a neat way to deal with this type of character literal in the form of the alternative quote (q) operator. Notice that the problem is that Oracle chose the single quote characters as the special pair of symbols that enclose or wrap any other character literal. These character- enclosing symbols could have been anything other than single quotation marks.

Bearing this in mind, consider the alternative quote (q) operator. The q operator enables you to choose from a set of possible pairs of wrapping symbols for character literals as alternatives to the single quote symbols. The options are any single-byte or multibyte character or the four brackets: (round brackets), {curly braces}, [squarebrackets], or lt;angle bracketsgt;. Using the q operator, the character delimiter can effectively be changed from a single quotation mark to any other character

The syntax of the alternative quote operator is as follows:

q#39;delimiter#39;character literal which may include the single quotes delimiter#39; where delimiter can be any character or bracket.

Alternative Quote (q) Operator

Specify your own quotation mark delimiter. Select any delimiter.

Increase readability and usability.

SELECT department_name || q#39;[ Department#39;s Manager Id: ]#39; || manager_id AS quot;Department and Managerquot;

FROM departments;

Alternative Quote (q) Operator

Many SQL statements use character literals in expressions or conditions. If the literal itself contains a single quotation mark, you can use the quote (q) operator and select your own quotation mark delimiter.

You can choose any convenient delimiter, single-byte or multi byte, or any of the following character pairs: [ ], { }, ( ), or lt; gt;.

In the example shown, the string contains a single quotation mark, which is normally interpreted as a delimiter of a character string. By using the q operator, however, brackets [] are used as the quotation mark delimiters. The string between the brackets delimiters is interpreted as a literal character string.

Question No.26

View the Exhibit and examine the data in the employees table:


You want to display all the employee names and their corresponding manager names. Evaluate the following query:


Which join option can be used in the blank in the above query to get the required output?





Correct Answer: C

Question No.27

You need to create a table with the following column specifications:

  1. Employee ID (numeric data type) for each employee

  2. Employee Name (character data type) that stores the employee name

  3. Hire date, which stores the date of joining the organization for each employee

  4. Status (character data type), that contains the value #39;active1 if no data is entered

  5. Resume (character large object [CLOB] data type), which contains the resume submitted by the employee

Which is the correct syntax to create this table?


  1. Option A

  2. Option B

  3. Option C

  4. Option D

Correct Answer: D


CLOB Character data (up to 4 GB)

NUMBER [(p, s)] Number having precision p and scale s (Precision is the total number of decimal digits and scale is the number of digits to the right of the decimal point; precision can range from 1 to 38, and scale can range from ?4 to 127.)

Question No.28

You need to generate a list of all customer last names with their credit limits from the customers table.

Those customers who do not have a credit limit should appear last in the list. Which two queries would achieve the required result? (Choose two.)


  1. Option A

  2. Option B

  3. Option C

  4. Option D

Correct Answer: BC


If the ORDER BY clause is not used, the sort order is undefined, and the Oracle server may not fetch rows in the same order for the same query twice. Use the ORDER BY clause to display the rows in a specific order.


Use the keywords NULLS FIRST or NULLS LAST to specify whether returned rows containing null values should appear first or last in the ordering sequence.

ANSWER C Sorting

The default sort order is ascending:

Numeric values are displayed with the lowest values first (for example, 1 to 999).

Date values are displayed with the earliest value first (for example, 01-JAN-92 before 01- JAN- 95).

Character values are displayed in the alphabetical order (for example, quot;Aquot; first and quot;Zquot; last). Null values are displayed last for ascending sequences and first for descending sequences. ANSWER B

You can also sort by a column that is not in the SELECT list.

Question No.29

Examine the create table statements for the stores and sales tables.

SQLgt; CREATE TABLE stores (store_id NUMBER(4)CONSTRAINT store_id_pk PRIMARY KEY, store_name VARCHAR2(12, store_address VARCHAR2(20, start_date DATE);

SQLgt; CREATE TABLE sales (sales_id NUMBER(4)CONSTRAINT sales_id_pk PRIMARY KEY, item_id NUMBER(4,quantity NUMBER(10), sales_date DATE, store_id NUMBER(4, CONSTRAINT store_id_fk FOREIGN KEY(store_id)REFERENCES stores(store_id));

You executed the following statement: SQLgt; DELETE from stores

WHERE store_id=900;

The statement fails due to the integrity constraint error:

ORA-02292: integrity constraint (HR.STORE_ID_FK) violated

Which three options ensure that the statement will execute successfully? (Choose three.)

  1. Disable the primary key in the STORES table.

  2. Use CASCADE keyword with DELETE statement.

  3. DELETE the rows with STORE_ID = 900 from the SALES table and then delete rows from STORES table.

  4. Disable the FOREIGN KEY in SALES table and then delete the rows.

  5. Create the foreign key in the SALES table on SALES_ID column with on DELETE CASCADE option.

Correct Answer: ACD

Question No.30

View the Exhibits and examine the structures of the products and sales tables.


Which two SQL statements would give the same output?


  1. Option A

  2. Option B

  3. Option C

  4. Option D

Correct Answer: AC


INTERSECT and JOIN works on comparable columns.

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