[Free] 2018(Jan) Latesttests Dumpsleader Oracle 1z0-051 Dumps with VCE and PDF Download 131-140

Latesttests 2018 Jan Oracle Official New Released 1z0-051
100% Free Download! 100% Pass Guaranteed!
http://www.Latesttests.com/1z0-051.html

Oracle Database: SQL Fundamentals I

Question No: 131 – (Topic 1)

Evaluate the following SQL statement:

Latesttests 2018 PDF and VCE

Which statement is true regarding the outcome of the above query?

  1. It produces an error because the ORDER BY clause should appear only at the end of a compound query-that is, with the last SELECT statement

  2. It executes successfully and displays rows in the descending order of PROMO_CATEGORY

  3. It executes successfully but ignores the ORDER BY clause because it is not located at the end of the compound statement

  4. It produces an error because positional notation cannot be used in the ORDER BY clause with SET operators

Answer: A Explanation:

Using the ORDER BY Clause in Set Operations

The ORDER BY clause can appear only once at the end of the compound query. Component queries cannot have individual ORDER BY clauses.

The ORDER BY clause recognizes only the columns of the first SELECT query.

By default, the first column of the first SELECT query is used to sort the output in an ascending order.

Question No: 132 – (Topic 1)

Which statements are correct regarding indexes? (Choose all that apply.)

  1. For each data manipulation language (DML) operation performed, the corresponding

    indexes are automatically updated.

  2. A nondeferrable PRIMARY KEY or UNIQUE KEY constraint in a table automatically creates a unique index.

  3. A FOREIGN KEY constraint on a column in a table automatically creates a non unique key

  4. When a table is dropped, the corresponding indexes are automatically dropped

    Answer: A,B,D

    Question No: 133 – (Topic 1)

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

    You need to generate a report of all promos from the PROMOTIONS table based on the following conditions:

    1. The promo name should not begin with #39;T#39; or #39;N#39;.

    2. The promo should cost more than $20000.

    3. The promo should have ended after 1st January 2001. Which WHERE clause would give the required result?

      Latesttests 2018 PDF and VCE

      A.

      WHERE promo_name NOT LIKE #39;T%#39; OR promo_name NOT LIKE #39;N%#39; AND promo_cost

      gt; 20000 AND promo_end_date gt; #39;1-JAN-01#39;

      B.

      WHERE (promo_name NOT LIKE #39;T%#39; AND promo_name NOT LIKE #39;N%#39;)OR promo_cost

      gt; 20000 OR promo_end_date gt; #39;1-JAN-01#39;

      C.

      WHERE promo_name NOT LIKE #39;T%#39; AND promo_name NOT LIKE #39;N%#39; AND promo_cost gt; 20000 AND promo_end_date gt; #39;1-JAN-01#39;

      D.

      WHERE (promo_name NOT LIKE #39;%T%#39; OR promo_name NOT LIKE #39;%N%#39;) AND(promo_cost gt; 20000 AND promo_end_date gt; #39;1-JAN-01#39;)

      Answer: C

      Question No: 134 – (Topic 1)

      View the Exhibit and examine the structure of ORD and ORD_ITEMS tables.

      The ORD_NO column is PRIMARY KEY in the ORD table and the ORD_NO and ITEM_NO columns are composite PRIMARY KEY in the ORD_ITEMS table.

      Which two CREATE INDEX statements are valid? (Choose two.)

      Latesttests 2018 PDF and VCE

      1. CREATE INDEX ord_idx1 ON ord(ord_no);

      2. CREATE INDEX ord_idx2

        ON ord_items(ord_no);

      3. CREATE INDEX ord_idx3 ON ord_items(item_no);

      4. CREATE INDEX ord_idx4

ON ord,ord_items(ord_no, ord_date,qty);

Answer: B,C Explanation:

How Are Indexes Created?

You can create two types of indexes.

Unique index: The Oracle server automatically creates this index when you define a column in a table to have a PRIMARY KEY or a UNIQUE constraint. The name of the index is the name that is given to the constraint.

Nonunique index: This is an index that a user can create. For example, you can create the FOREIGN KEY column index for a join in a query to improve the speed of retrieval. Note: You can manually create a unique index, but it is recommended that you create a unique constraint, which implicitly creates a unique index.

Question No: 135 – (Topic 1)

You need to display the date ll-oct-2007 in words as quot;Eleventh of October, Two Thousand Seven#39;. Which SQL statement would give the required result?

  1. SELECT TO_CHAR(#39;ll-oct-2007#39;. #39;miDdspth quot;of Month. Year#39;) FROM DUAL:

  2. SELECT TO_CHAR(TO_DATE(#39;ll-oct-2007#39;X #39;miDdspth of month, year#39;) FROM DUAL;

  3. SELECT TO_CHAR(TO_DATE(#39;ll-oct-2007#39;), #39;miDdthsp quot;of* Month. Year#39;) FROM DUAL;

  4. SELECT TO_DATE(TO_CHAR(#39;ll-oct-20077fiiiDdspth quot;ofquot; Month. Year#39;)) FROM DUAL:

Answer: A

Question No: 136 – (Topic 1)

Evaluate the following SQL commands:

Latesttests 2018 PDF and VCE

The command to create a table fails. Identify the reason for the SQL statement failure? (Choose all that apply.)

  1. You cannot use SYSDATE in the condition of a CHECK constraint.

  2. You cannot use the BETWEEN clause in the condition of a CHECK constraint.

  3. You cannot use the NEXTVAL sequence value as a DEFAULT value for a column.

  4. You cannot use ORD_NO and ITEM_NO columns as a composite primary key because ORD NO is also the FOREIGN KEY.

Answer: A,C Explanation: CHECK Constraint

The CHECK constraint defines a condition that each row must satisfy. The condition can use the same constructs as the query conditions, with the following exceptions: References to the CURRVAL, NEXTVAL, LEVEL, and ROWNUM pseudocolumns

Calls to SYSDATE, UID, USER, and USERENV functions Queries that refer to other values in other rows

A single column can have multiple CHECK constraints that refer to the column in its definition.

There is no limit to the number of CHECK constraints that you can define on a column. CHECK constraints can be defined at the column level or table level.

CREATE TABLE employees (…

salary NUMBER(8,2) CONSTRAINT emp_salary_min CHECK (salary gt; 0),

Question No: 137 – (Topic 1)

You need to perform these tasks:

->Create and assign a MANAGER role to Blake and Clark

->Grant CREATE TABLE and CREATE VIEW privileges to Blake and Clark

Which set of SQL statements achieves the desired results?

  1. CREATE ROLE manager; GRANT create table, create view TO manager;

    GRANT manager TO BLAKE,CLARK;

  2. CREATE ROLE manager; GRANT create table, create voew TO manager;

    GRANT manager ROLE TO BLAKE,CLARK;

  3. GRANT manager ROLE TO BLAKE,CLARK; GRANT create table, create voew

TO BLAKE CLARK;

***MISSING***

Answer: A

Explanation: Result of commands:

Latesttests 2018 PDF and VCE

Question No: 138 – (Topic 1)

Examine the structure of the EMPLOYEES table:

Latesttests 2018 PDF and VCE

Which UPDATE statement is valid?

A.

UPDATE employees SET first_name = ‘John’ SET last_name = ‘Smith’

WHERE employee_id = 180;

B.

UPDATE employees SET first_name = ‘John’, SET last_name = ‘Smoth’

WHERE employee_id = 180;

C.

UPDATE employee SET first_name = ‘John’

AND last_name = ‘Smith’ WHERE employee_id = 180; D.

UPDATE employee

SET first_name = ‘John’, last_name = ‘Smith’ WHERE employee_id = 180;

Answer: D

Question No: 139 – (Topic 1)

Latesttests 2018 PDF and VCE

The SQL statements executed in a user session as follows: Exhibit:

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

Answer: B,E

Question No: 140 – (Topic 1)

Which view should a user query to display the columns associated with the constraints on a table owned by the user?

  1. USER_CONSTRAINTS

  2. USER_OBJECTS

  3. ALL_CONSTRAINTS

  4. USER_CONS_COLUMNS

  5. USER_COLUMNS

Answer: D

Explanation: view the columns associated with the constraint names in the USER_CONS_COLUMNS view.

Incorrect answer:

Atable to view all constraints definition and names Bshow all object name belong to user

Cdoes not display column associated Eno such view

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

100% Free Download!
Download Free Demo:1z0-051 Demo PDF
100% Pass Guaranteed!
Download 2018 Latesttests 1z0-051 Full Exam PDF and VCE

Latesttests ExamCollection Testking
Lowest Price Guarantee Yes No No
Up-to-Dated Yes No No
Real Questions Yes No No
Explanation Yes No No
PDF VCE Yes No No
Free VCE Simulator Yes No No
Instant Download Yes No No

2018 Latesttests IT Certification PDF and VCE