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

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

Question No.111

Exhibit contains the structure of PRODUCTS table:

image

Evaluate the following query:

image

What would be the outcome of executing the above SQL statement?

  1. It produces an error

  2. It shows the names of products whose list price is the second highest in the table.

  3. It shown the names of all products whose list price is less than the maximum list price

  4. It shows the names of all products in the table

Correct Answer: B

Question No.112

View the Exhibit and examine the data in the PROMOTIONS table.

image

You need to display all promo categories that do not have #39;discount#39; in their subcategory. Which two SQL statements give the required result? (Choose two.)

  1. SELECT promo_categoryFROM promotionsMINUSSELECT promo_categoryFROM promotionsWHERE promo_subcategory = #39;discount#39;;

  2. SELECT promo_categoryFROM promotionsINTERSECTSELECT promo_categoryFROM promotionsWHERE promo_subcategory = #39;discount#39;;

  3. SELECT promo_categoryFROM promotionsMINUSSELECT promo_categoryFROM promotionsWHERE promo_subcategory lt;gt; #39;discount#39;;

  4. SELECT promo_categoryFROM promotionsINTERSECTSELECT promo_categoryFROM promotionsWHERE promo_subcategory lt;gt; #39;discount#39;;

Correct Answer: AD

Question No.113

Examine the structure of the EMPLOYEES table:

image

Which UPDATE statement is valid?

  1. UPDATE employeesSET first_name = `John#39;SET last_name = `Smith#39;WHERE employee_id = 180;

  2. UPDATE employeesSET first_name = `John#39;,SET last_name = `Smoth#39;WHERE employee_id = 180;

  3. UPDATE employeeSET first_name = `John#39;AND last_name = `Smith#39;WHERE employee_id = 180;

  4. UPDATE employeeSET first_name = `John#39;, last_name = `Smith#39;WHERE employee_id = 180;

Correct Answer: D

Question No.114

The STUDENT_GRADES table has these columns:

image

Which statement finds students who have a grade point average (GPA) greater than 3.0 for the calendar year 2001?

  1. SELECT student_id, gpaFROM student_gradesWHERE semester_end BETWEEN #39;01- JAN- 2001#39; AND #39;31-DEC-2001#39;OR gpa gt; 3.;

  2. SELECT student_id, gpaFROM student_gradesWHERE semester_end BETWEEN #39;01- JAN- 2001#39; AND #39;31-DEC-2001#39;AND gpa gt 3.0;

  3. SELECT student_id, gpaFROM student_gradesWHERE semester_end BETWEEN #39;01- JAN- 2001#39; AND #39;31-DEC-2001#39;AND gpa gt; 3.0;

  4. SELECT student_id, gpaFROM student_gradesWHERE semester_end BETWEEN #39;01- JAN- 2001#39; AND #39;31-DEC-2001#39;OR gpa gt; 3.0;

  5. SELECT student_id, gpaFROM student_gradesWHERE semester_end gt; #39;01-JAN-2001#39; OR semester_end lt; #39;31-DEC-2001#39;AND gpa gt;= 3.0;

Correct Answer: C

Question No.115

Evaluate the following two queries:

image

image

Which statement is true regarding the above two queries?

  1. Performance would improve in query 2 only if there are null values in the CUST_CREDIT_LIMIT column.

  2. Performance would degrade in query 2.

  3. There would be no change in performance.

  4. Performance would improve in query 2.

Correct Answer: C

Explanation:

The IN operator is internally evaluated by the Oracle server as a set of OR conditions, such as a=value1 or a=value2 or a=value3. Therefore, using the IN operator has no performance benefits and is used only for logical simplicity.

Question No.116

Which two statements are true regarding single row functions? (Choose two.)

  1. They can be nested only to two levels

  2. They always return a single result row for every row of a queried table

  3. Arguments can only be column values or constant

  4. They can return a data type value different from the one that is referenced

  5. They accept only a single argument

Correct Answer: BD

Explanation:

A function is a program written to optionally accept input parameters, perform an operation, or return a single value. A function returns only one value per execution. Three important components form the basis of defining a function. The first is the input parameter list. It specifies zero or more arguments that may be passed to a function as input for processing. These arguments or parameters may be of differing data types, and some are mandatory while others may be optional. The second component is the data type of its resultant value. Upon execution, only one value is returned by the function. The third encapsulates the details of the processing performed by the function and contains the program code that optionally manipulates the input parameters, performs calculations and operations, and generates a return value.

Question No.117

You need to design a student registration database that contains several tables storing academic information.

The STUDENTS table stores information about a student. The STUDENT_GRADES table stores information about the student#39;s grades. Both of the tables have a column named STUDENT_ID. The STUDENT_ID column in the STUDENTS table is a primary key.

You need to create a foreign key on the STUDENT_ID column of the STUDENT_GRADES table that points to the STUDENT_ID column of the STUDENTS table. Which statement creates the foreign key?

  1. CREATE TABLE student_grades (student_id NUMBER(12), semester_end DATE, gpa

    NUMBER(4, 3), CONSTRAINT student_id_fk REFERENCES (student_id) FOREIGN KEY students(student_id));

  2. CREATE TABLE student_grades(student_id NUMBER(12), semester_end DATE, gpa NUMBER(4, 3), student_id_fk FOREIGN KEY (student_id) REFERENCES students(student_id));

  3. CREATE TABLE student_grades(student_id NUMBER(12), semester_end DATE, gpa NUMBER(4, 3), CONSTRAINT FOREIGN KEY (student_id) REFERENCES students(student_id));

  4. CREATE TABLE student_grades(student_id NUMBER(12), semester_end DATE, gpa NUMBER(4, 3), CONSTRAINT student_id_fk FOREIGN KEY (student_id) REFERENCES students(student_id));

Correct Answer: D

Explanation:

CONSTRAINT name FOREIGN KEY (column_name) REFERENCES table_name (column_name);

Incorrect answer:

  1. invalid syntax

  2. invalid syntax

  3. invalid syntax

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

Question No.118

You work as a database administrator at ABC.com. You study the exhibit carefully and examine the structure of CUSTOMRS AND SALES tables.

image

Evaluate the following SQL statement:

image

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

  1. It would execute and restrict modifications to only the column specified in the SELECT statement

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

  3. It would not execute because a sub query cannot be used in the WHERE clause of an UPDATE statement

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

Correct Answer: A

Question No.119

Where can sub queries be used? (Choose all that apply)

  1. field names in the SELECT statement

  2. the FROM clause in the SELECT statement

  3. the HAVING clause in the SELECT statement

  4. the GROUP BY clause in the SELECT statement

  5. the WHERE clause in only the SELECT statement

  6. the WHERE clause in SELECT as well as all DML statements

Correct Answer: ABCF

Explanation:

SUBQUERIES can be used in the SELECT list and in the FROM, WHERE, and HAVING clauses of a query.

A subquery can have any of the usual clauses for selection and projection. The following are required clauses:

image

image

A SELECT list A FROM clause

The following are optional clauses:

image

image

image

WHERE GROUP BY HAVING

The subquery (or subqueries) within a statement must be executed before the parent query that calls it, in order that the results of the subquery can be passed to the parent.

Question No.120

Which object privileges can be granted on a view?

  1. none

  2. DELETE, INSERT, SELECT

  3. ALTER, DELETE, INSERT, SELECT

  4. DELETE, INSERT, SELECT, UPDATE

Correct Answer: D

Explanation:

Object privilege on VIEW is DELETE, INSERT, REFERENCES, SELECT and UPDATE.

Incorrect answer:

  1. Object privilege on VIEW is DELETE, INSERT, REFERENCES, SELECT and UPDATE

  2. Object privilege on VIEW is DELETE, INSERT, REFERENCES, SELECT and UPDATE

  3. Object privilege on VIEW is DELETE, INSERT, REFERENCES, SELECT and UPDATE Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 13-12

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

Leave a Reply