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

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

Question No.251

Examine the data in the CUST_NAME column of the CUSTOMERS table.

CUST_NAME

Lex De Haan Renske Ladwig Jose Manuel Urman Jason Mallin

You want to extract only those customer names that have three names and display the * symbol in place of the first name as follows:

CUST NAME

*** De Haan

**** Manuel Urman

Which two queries give the required output? (Choose two.)

  1. SELECT LPAD(SUBSTR(cust_name, INSTR(cust_name, #39; #39;)), LENGTH(cust_name), #39;*#39;) quot;CUST NAMEquot; FROM customersWHERE INSTR(cust_name, #39; #39;, 1, 2)lt;gt;0;

  2. SELECT LPAD(SUBSTR(cust_name, INSTR(cust_name, #39; #39;)), LENGTH(cust_name), #39;*#39;) quot;CUST NAMEquot; FROM customersWHERE INSTR(cust_name, #39; #39;, -1, 2)lt;gt;0;

  3. SELECT LPAD(SUBSTR(cust_name, INSTR(cust_name, #39; #39;)), LENGTH(cust_name)- INSTR(cust_name, #39;#39;), #39;*#39;) quot;CUST NAMEquot;FROM customersWHERE INSTR(cust_name, #39; #39;, – 1, – 2)lt;gt;0;

  4. SELECT LPAD(SUBSTR(cust_name, INSTR(cust_name, #39; #39;)), LENGTH(cust_name)- INSTR(cust_name, #39; #39;), #39;*#39;) quot;CUST NAMEquot;FROM customersWHERE INSTR(cust_name, #39; #39;, 1, 2)lt;gt;0 ;

Correct Answer: AB

Question No.252

Examine the following Entity Relationship Model diagram:

image

Which three statements are true? (Choose three.)

  1. SKILL is a multi-valued attribute.

  2. YEARS_EMPLOYED is a derived attribute.

  3. YEARS_EMPLOYED is a key attribute.

  4. SKILL is a composite attribute.

  5. EMP_ID is a key attribute.

  6. EMPLOYEE is a weak entity.

Correct Answer: ABE

Question No.253

Which statements are true regarding the FOR UPDATE clause in a SELECT statement? (Choose all that apply.)

  1. It locks only the columns specified in the SELECT list.

  2. It locks the rows that satisfy the condition in the SELECT statement.

  3. It can be used only in SELECT statements that are based on a single table.

  4. It can be used in SELECT statements that are based on a single or multiple tables.

  5. After it is enforced by a SELECT statement, no other query can access the same rows until a COMMIT or ROLLBACK is issued.

Correct Answer: BD

Explanation:

FOR UPDATE Clause in a SELECT Statement

Locks the rows in the EMPLOYEES table where job_id is SA_REP. Lock is released only when you issue a ROLLBACK or a COMMIT. If the SELECT statement attempts to lock a row that is locked by another user, the database waits until the row is available, and then returns the results of the SELECT statement.

FOR UPDATE Clause in a SELECT Statement

When you issue a SELECT statement against the database to query some records, no locks are placed on the selected rows. In general, this is required because the number of records locked at any given time is (by default) kept to the absolute minimum: only those records that have been changed but not yet committed are locked. Even then, others will be able to read those records

as they appeared before the change (the quot;before imagequot; of the data). There are times, however, when you may want to lock a set of records even before you change them in your program.

Oracle offers the FOR UPDATE clause of the SELECT statement to perform this locking. When you issue a SELECT…FOR UPDATE statement, the relational database management system (RDBMS) automatically obtains exclusive row-level locks on all the rows identified by the SELECT statement, thereby holding the records quot;for your changes only.quot; No one else will be able to change any of these records until you perform a ROLLBACK or a COMMIT.

You can append the optional keyword NOWAIT to the FOR UPDATE clause to tell the Oracle server not to wait if the table has been locked by another user. In this case, control will be returned immediately to your program or to your SQL Developer environment so that you can perform other work, or simply wait for a period of time before trying again. Without the NOWAIT clause, your process will block until the table is available, when the locks are released by the other user through the issue of a COMMIT or a ROLLBACK command.

Question No.254

View the Exhibit and examine the structure of the PRODUCTS table.

You want to display only those product names with their list prices where the list price is at least double the minimum price.

The report should start with the product name having the maximum list price satisfying this condition.

Evaluate the following SQL statement: SQLgt;SELECT prod_name, prod_list_price FROM products

WHERE prod_list_price gt;= 2 * prod_min_price

Which ORDER BY clauses can be added to the above SQL statement to get the correct output? (Choose all that apply.)

image

  1. ORDER BY prod_list_price DESC, prod_name;

  2. ORDER BY (2*prod_min_price)DESC, prod_name;

  3. ORDER BY prod_name, (2*prod_min_price)DESC;

  4. ORDER BY prod_name DESC, prod_list_price DESC;

  5. ORDER BY prod_list_price DESC, prod_name DESC;

Correct Answer: AE

Explanation:

Using the ORDER BY Clause

The order of rows that are returned in a query result is undefined. The ORDER BY clause can be used to sort the rows. However, if you use the ORDER BY clause, it must be the last clause of

the SQL statement. Further, you can specify an expression, an alias, or a column position as the sort condition.

Syntax SELECT expr FROM table

[WHERE condition(s)]

[ORDER BY {column, expr, numeric_position} [ASC|DESC]]; In the syntax:

ORDER BY specifies the order in which the retrieved rows are displayed ASC orders the rows in ascending order (This is the default order.) DESC orders the rows in descending order

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.

Note:

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.

Question No.255

Evaluate the SQL statement: SELECT LPAD (salary, 10, #39;*#39;) FROM EMP

WHERE EMP_ID = 1001;

If the employee with the EMP_ID 1001 has a salary of 17000, what is displayed? A. 17000.00

B. 17000***** C. ****170.00 D. **17000.00

E. an error statement

Correct Answer: D

Question No.256

Which SQL statement returns a numeric value?

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

  2. SELECT ROUND(hire_date) FROM EMP;

  3. SELECT sysdate-hire_date FROM EMP;

  4. SELECT TO_NUMBER(hire_date 7) FROM EMP;

Correct Answer: C

Explanation:

DATE value subtract DATE value will return numeric value.

Incorrect answer:

  1. does not return numeric value

  2. does not return numeric value

D. does not return numeric value

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

Question No.257

Examine the description of the CUSTOMERS table:

image

The CUSTOMER_ID column is the primary key for the table.

Which statement returns the city address and the number of customers in the cities Los Angeles or San Francisco?

  1. SELECT city_address, COUNT(*)FROM. customersWHERE city_address IN ( `Los Angeles#39;,

    `San Fransisco#39;);

  2. SELECT city_address, COUNT (*)FROM. customersWHERE city address IN ( `Los Angeles#39;,

    `San Fransisco#39;)GROUP BY city_address;

  3. SELECT city_address, COUNT(customer_id)FROM. customersWHERE city_address IN ( `Los Angeles#39;, `San Fransisco#39;)GROUP BY. city_address, customer_id;

  4. SELECT city_address, COUNT (customer_id)FROM . customersGROUP BY city_address IN (

`Los Angeles#39;, `San Fransisco#39;);

Correct Answer: B

Explanation:

Not C: The customer ID in the GROUP BY clause is wrong

Question No.258

What does the FORCE option for creating a view do?

  1. creates a view with constraints

  2. creates a view even if the underlying parent table has constraints

  3. creates a view in another schema even if you don#39;t have privileges

  4. creates a view regardless of whether or not the base tables exist

Correct Answer: D

Explanation:

create a view regardless of whether or not the base tables exist.

Incorrect answer:

  1. the option is not valid

  2. the option is not valid

  3. the option is not valid

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

Question No.259

Which three statements are true regarding views? (Choose three.)

  1. Views can be created only from tables.

  2. Views can be created from tables or other views.

  3. Only simple views can use indexes existing on the underlying tables.

  4. Both simple and complex views can use indexes existing on the underlying tables.

  5. Complex views can be created only on multiple tables that exist in the same schema.

  6. Complex views can be created on multiple tables that exist in the same or different schemas.

Correct Answer: BDF

Explanation:

Creating a Sequence (continued)

CYCLE | NOCYCLE Specifies whether the sequence continues to generate values after reaching its maximum or minimum value

(NOCYCLE is the default option.)

CACHE n | NOCACHE Specifies how many values the Oracle server preallocates and keeps in memory (By default, the Oracle server caches 20 values.)

Question No.260

You need to create a table named ORDERS that contain four columns:

  1. AN ORDER_ID column of number data type

  2. A CUSTOMER_ID column of number data type

  3. AN ORDER_STATUS column that contains a character data type

  4. A DATE_ORDERED column to contain the date the order was placed

When a row is inserted into the table, if no value is provided when the order was placed, today#39;s date should be used instead.

Which statement accomplishes this?

  1. CREATE TABLE orders (order_id NUMBER (10), customer_id NUMBER (8), order_status VARCHAR2 (10), date_ordered DATE = SYSDATE);

  2. CREATE TABLE orders (order_id NUMBER (10), customer_id NUMBER (8), order_status VARCHAR2 (10), date_ordered DATE DEFAULT SYSDATE);

  3. CREATE OR REPLACE TABLE orders (order_id NUMBER (10), customer_id NUMBER (8), order_status VARCHAR2 (10), date_ordered DATE DEFAULT SYSDATE);

  4. CREATE OR REPLACE TABLE orders (order_id NUMBER (10), customer_id NUMBER (8), order_status VARCHAR2 (10), date_ordered DATE = SYSDATE);

  5. CREATE TABLE orders (order_id NUMBER (10), customer_id NUMBER (8), order_status NUMBER (10), date_ordered DATE = SYSDATE);

  6. CREATE TABLE orders (order_id NUMBER (10), customer_id NUMBER (8), order_status NUMBER (10), date_ordered DATE DEFAULT SYSDATE);

Correct Answer: B

Explanation:

Requirement that Order_Status should be a character data type

Not E: Order_status must be a character data type. There is also a syntax error.

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