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

Question No.301

A data manipulation language statement .

1. completes a transaction on a table

2. modifies the structure and data in a table

3. modifies the data but not the structure of a table

4. modifies the structure but not the data of a table

Correct Answer: C

Explanation:

modifies the data but not the structure of a table

Incorrect answer:

1. DML does not complete a transaction

2. DDL modifies the structure and data in the table

D. DML does not modified table structure.

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

Question No.302

The STUDENT_GRADES table has these columns: STUDENT_ID NUMBER(12)

SEMESTER_END DATE GPA NUMBER(4, 3)

The registrar requested a report listing the students#39; grade point averages (GPA) sorted from highest grade point average to lowest.

Which statement produces a report that displays the student ID and GPA in the sorted order requested by the registrar?

1. SELECT student_id, gpa FROM student_grades ORDER BY gpa ASC;

2. SELECT student_id, gpa FROM student_grades SORT ORDER BY gpa ASC;

3. SELECT student_id, gpa FROM student_grades SORT ORDER BY gpa;

4. SELECT student_id, gpa FROM student_grades ORDER BY gpa;

5. SELECT student_id, gpa FROM student_grades SORT ORDER BY gpa DESC;

6. SELECT student_id, gpa FROM student_grades ORDER BY gpa DESC;

Correct Answer: F

Explanation:

Sorted by highest to lowest is DESCENDING order

Incorrect answer:

1. result in ascending order

2. wrong syntax with SORT keyword

3. wrong syntax with SORT keyword

4. default value for ORDER by is in ascending order

5. wrong syntax with SORT keyword

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

Question No.303

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

1. A simple view in which column aliases have been used cannot be updated.

2. Rows cannot be deleted through a view if the view definition contains the DISTINCT keyword.

3. Rows added through a view are deleted from the table automatically when the view is dropped.

4. The OR REPLACE option is used to change the definition of an existing view without dropping and recreating it.

5. The WITH CHECK OPTION constraint can be used in a view definition to restrict the columns displayed through the view.

Correct Answer: BD

Question No.304

Which statement is true regarding transactions? (Choose all that apply.)

1. A transaction can consist only of a set of DML and DDL statements.

2. A part or an entire transaction can be undone by using ROLLBACK command.

3. A transaction consists of a set of DML or DCL statements.

4. A part or an entire transaction can be made permanent with a COMMIT.

5. A transaction can consist of only a set of queries or DML or DDL statements.

Correct Answer: BC

Question No.305

View the Exhibit and examine the structure of CUSTOMERS and SALES tables.

Evaluate the following SQL statement:

UPDATE (SELECT prod_id, cust_id, quantity_sold, time_id FROM sales)

SET time_id = #39;22-MAR-2007#39;

WHERE cust_id = (SELECT cust_id FROM customers

WHERE cust_last_name = #39;Roberts#39; AND credit_limit = 600);

Which statement is true regarding the execution of the above UPDATE statement?

1. It would not execute because two tables cannot be used in a single UPDATE statement.

2. It would not execute because the SELECT statement cannot be used in place of the table name.

3. It would execute and restrict modifications to only the columns specified in the SELECT statement.

4. It would not execute because a subquery cannot be used in the WHERE clause of an UPDATE statement.

Correct Answer: C

Explanation:

One UPDATE statement can change rows in only one table, but it can change any number of rows in that table.

Question No.306

Which four are attributes of single row functions? (Choose four.)

1. cannot be nested

2. manipulate data items

3. act on each row returned

4. return one result per row

5. accept only one argument and return only one value

6. accept arguments which can be a column or an expression

Correct Answer: BCDF

Explanation:

Manipulate data items, act on each row returned, return one result per row, and accept arguments that can be a column or expression.

Incorrect answer:

A. is not single row attributes

E. functions can accept more than one argument, e.g NVL2

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

Question No.307

Examine the structure of the TRANSACTIONS table: Name Null Type

TRANS_ID NOT NULL NUMBER (3) CUST_NAME VARCHAR2(30) TRANS_DATE DATE

TRANS_AMT NUMBER(10, 2)

You want to display the transaction date and specify whether it is a weekday or weekend. Evaluate the following two queries:

Which statement is true regarding the above queries?

1. Both give wrong results.

2. Both give the correct result.

3. Only the first query gives the correct result.

4. Only the second query gives the correct result.

Correct Answer: C

Explanation:

Range Conditions Using the BETWEEN Operator

Use the BETWEEN operator to display rows based on a range of values: SELECT last_name, salary

FROM employees

WHERE salary BETWEEN 2500 AND 3500;

Range Conditions Using the BETWEEN Operator

You can display rows based on a range of values using the BETWEEN operator. The range that you specify contains a lower limit and an upper limit. The SELECT statement in the slide returns rows from the EMPLOYEES table for any employee whose salary is between $2, 500 and $3,

500. Values that are specified with the BETWEEN operator are inclusive. However, you must specify the lower limit first.

You can also use the BETWEEN operator on character values: SELECT last_name

FROM employees

WHERE last_name BETWEEN #39;King#39; AND #39;Smith#39;;

Question No.308

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

Which two tasks would require subqueries or joins to be executed in a single statement? (Choose two.)

1. listing of customers who do not have a credit limit and were born before 1980

2. finding the number of customers, in each city, whose marital status is #39;married#39;

3. finding the average credit limit of male customers residing in #39;Tokyo#39; or #39;Sydney#39;

4. listing of those customers whose credit limit is the same as the credit limit of customers residing in the city #39;Tokyo#39;

5. finding the number of customers, in each city, whose credit limit is more than the average credit limit of all the customers

Correct Answer: DE

Explanation:

Describe the Types of Problems That the Subqueries Can Solve There are many situations where you will need the result of one query as the input for another.

Use of a Subquery Result Set for Comparison Purposes Which employees have a salary that is less than the average salary? This could be answered by two statements, or by a single statement with a subquery. The following example uses two statements:

select avg(salary) from employees;

select last_name from employees where salary lt; result_of_previous_query ; Alternatively, this example uses one statement with a subquery:

select last_name from employees where salary lt; (select avg(salary)from employees); In this example, the subquery is used to substitute a value into the WHERE clause of the parent query: it is returning a single value, used for comparison with the rows retrieved by the parent query.

The subquery could return a set of rows. For example, you could use the following to find all departments that do actually have one or more employees assigned to them:

select department_name from departments where department_id in (select distinct(department_id) from employees);

Question No.309

Examine the structure of the PROMOS table:

You want to generate a report showing promo names and their duration (number of days).

If the PROMO_END_DATE has not been entered, the message #39;ONGOING#39; should be displayed. Which queries give the correct output? (Choose all that apply.)

1. SELECT promo_name, TO_CHAR(NVL(promo_end_date -promo_start_date, #39;ONGOING#39;)) FROM promos;

2. SELECT promo_name, COALESCE(TO_CHAR(promo_end_date – promo_start_date), #39;ONGOING#39;) FROM promos;

3. SELECT promo_name, NVL(TO_CHAR(promo_end_date -promo_start_date), #39;ONGOING#39;)

   FROM promos;

4. SELECT promo_name, DECODE(promo_end_date-promo_start_date, NULL, #39;ONGOING#39;, promo_end_date – promo_start_date) FROM promos;

5. SELECT promo_name, ecode(coalesce(promo_end_date, promo_start_date), null, #39;ONGOING#39;, promo_end_date – promo_start_date)FROM promos;

Correct Answer: BCD

Question No.310

You need to modify the STUDENTS table to add a primary key on the STUDENT_ID column. The table is currently empty. Which statement accomplishes this task?

1. ALTER TABLE students ADD PRIMARY KEY student_id;

2. ALTER TABLE students ADD CONSTRAINT PRIMARY KEY (student_id);

3. ALTER TABLE students ADD CONSTRAINT stud_id_pk PRIMARY KEY student_id;

4. ALTER TABLE students ADD CONSTRAINT stud_id_pk PRIMARY KEY (student_id);

5. ALTER TABLE students MODIFY CONSTRAINT stud_id_pk PRIMARY KEY (student_id);

Correct Answer: D

Explanation:

ALTER TABLE table_name

ADD [CONSTRAINT constraint] type (coloumn);

Incorrect answer: A.wrong syntax B.wrong syntax C.wrong syntax

E.no such MODIFY keyword

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

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