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

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: 121 – (Topic 1)

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

Using the PROMOTIONS table, you need to find out the names and cost of all the promos done on #39;TV#39; and #39;internet#39; that ended in the time interval 15th March #39;00 to 15th October #39;00.

Which two queries would give the required result? (Choose two.)

Latesttests 2018 PDF and VCE

  1. SELECT promo_name, promo_cost FROM promotions

    WHERE promo_category IN (#39;TV#39;, #39;internet#39;) AND

    promo_end_date BETWEEN #39;15-MAR-00#39; AND #39;15-OCT-00#39;;

  2. SELECT promo_name, promo_cost FROM promotions

    WHERE promo_category = #39;TV#39; OR promo_category =#39;internet#39; AND promo_end_date

    gt;=#39;15-MAR-00#39; OR promo_end_date lt;=#39;15-OCT-00#39;;

  3. SELECT promo_name, promo_cost FROM promotions

    WHERE (promo_category BETWEEN #39;TV#39; AND #39;internet#39;) AND (promo_end_date IN (#39;15-MAR-00#39;,#39;15-OCT-00#39;));

  4. SELECT promo_name, promo_cost FROM promotions

WHERE (promo_category = #39;TV#39; OR promo_category =#39;internet#39;) AND (promo_end_date

gt;=#39;15-MAR-00#39; AND promo_end_date lt;=#39;15-OCT-00#39;);

Answer: A,D

Question No: 122 – (Topic 1)

When does a transaction complete? (Choose all that apply.)

  1. When a PL/SQL anonymous block is executed

  2. When a DELETE statement is executed

  3. When a data definition language statement is executed

  4. When a TRUNCATE statement is executed after the pending transaction

  5. When a ROLLBACK command is executed

Answer: C,D,E

Question No: 123 – (Topic 1)

Examine the structure and data of the CUSTJTRANS table:

CUSTJRANS

Name Null? Type

CUSTNO NOT NULL CHAR(2) TRANSDATE DATE TRANSAMT NUMBER(6.2) CUSTNO TRANSDATE TRANSAMT

11 01-JAN-07 1000

22 01-FEB-07 2000

33 01-MAR-07 3000

Dates are stored in the default date format dd-mon-rr in the CUSTJTRANS table. Which three SQL statements would execute successfully? (Choose three.)

  1. SELECT transdate #39;10#39; FROM custjrans;

  2. SELECT * FROM custjrans WHERE transdate = #39;01-01-07#39;:

  3. SELECT transamt FROM custjrans WHERE custno gt; #39;11#39;:

  4. SELECT * FROM custjrans WHERE transdate=#39;01-JANUARY-07#39;:

  5. SELECT custno – #39;A#39; FROM custjrans WHERE transamt gt; 2000:

Answer: A,C,D

Question No: 124 – (Topic 1)

View the Exhibit and examine the structure of the PROMOTIONS, SALES, and CUSTOMER tables.

Latesttests 2018 PDF and VCE

You need to generate a report showing the promo name along with the customer name for all products that were sold during their promo campaign and before 30th October 2007.

You issue the following query:

Latesttests 2018 PDF and VCE

Which statement is true regarding the above query?

  1. It executes successfully and gives the required result.

  2. It executes successfully but does not give the required result.

  3. It produces an error because the join order of the tables is incorrect.

  4. It produces an error because equijoin and nonequijoin conditions cannot be used in the same SELECT statement.

Answer: B

Question No: 125 – (Topic 1)

You work as a database administrator at ABC.com. You study the exhibit carefully. Exhibit:

Latesttests 2018 PDF and VCE

Examine the structure of PRODUCTS table.

Using the PRODUCTS table, you issue the following query to generate the names, current list price and discounted list price for all those products whose list price fails below $10 after a discount of 25% is applied on it.

Exhibit:

Latesttests 2018 PDF and VCE

The query generates an error.

What is the reason of generating error?

  1. The column alias should be put in uppercase and enclosed within double quotation marks in the WHERE clause

  2. The parenthesis should be added to enclose the entire expression

  3. The column alias should be replaced with the expression in the WHERE clause

  4. The double quotation marks should be removed from the column alias

Answer: C

Explanation: Note: You cannot use column alias in the WHERE clause.

Question No: 126 – (Topic 1)

What is true regarding sub queries?

  1. The inner query always sorts the results of the outer query

  2. The outer query always sorts the results of the inner query

  3. The outer query must return a value to the outer query

  4. The inner query returns a value to the outer query

  5. The inner query must always return a value or the outer query will give an error

Answer: D

Explanation: The inner query returns a value to the outer query. If the inner query does not return a value, the outer query does not return a result

Question No: 127 – (Topic 1)

Which statement is true regarding sub queries?

  1. The LIKE operator cannot be used with single- row subqueries.

  2. The NOT IN operator is equivalent to IS NULL with single- row subqueries.

C. =ANY and =ALL operators have the same functionality in multiple- row subqueries.

D. The NOT operator can be used with IN, ANY, and ALL operators in multiple- row subqueries.

Answer: D Explanation:

Using the ANY Operator in Multiple-Row Subqueries

The ANY operator (and its synonym, the SOME operator) compares a value to each value returned by a subquery.

lt;ANY means less than the maximum.

gt;ANY means more than the minimum.

=ANY is equivalent to IN

Using the ALL Operator in Multiple-Row Subqueries

The ALL operator compares a value to every value returned by a subquery.

gt;ALL means more than the maximum and

lt;ALL means less than the minimum.

The NOT operator can be used with IN, ANY, and ALL operators.

Question No: 128 – (Topic 1)

You work as a database administrator at ABC.com. You study the exhibit carefully. Exhibit:

Latesttests 2018 PDF and VCE

You issue the following SQL statement:

Latesttests 2018 PDF and VCE

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

  1. It produces an error because the AMT_SPENT column contains a null value.

  2. It displays a bonus of 1000 for all customers whose AMT_SPENT is less than CREDIT_LIMIT.

  3. It displays a bonus of 1000 for all customers whose AMT_SPENT equals CREDIT_LIMIT, or AMT_SPENT is null.

  4. It produces an error because the TO_NUMBER function must be used to convert the result of the NULLIF function before it can be used by the NVL2 function.

Answer: C Explanation:

The NULLIF Function

The NULLIF function tests two terms for equality. If they are equal the function returns a null, else it returns the first of the two terms tested.

The NULLIF function takes two mandatory parameters of any data type. The syntax is NULLIF(ifunequal, comparison_term), where the parameters ifunequal and comparison_term are compared. If they are identical, then NULL is returned. If they differ, the ifunequal parameter is returned.

Question No: 129 – (Topic 1)

Latesttests 2018 PDF and VCE

See the Exhibit and examine the structure of ORD table: Exhibit:

Evaluate the following SQL statements that are executed in a user session in the specified order:

CREATE SEQUENCE ord_seq; SELECT ord_seq.nextval FROM dual;

INSERT INTO ord

VALUES (ord_seq.CURRVAL, ’25-jan-2007,101);

UPDATE ord

SET ord_no= ord_seq.NEXTVAL WHERE cust_id =101;

What would be the outcome of the above statements?

  1. All the statements would execute successfully and the ORD_NO column would contain the value 2 for the CUST_ID 101.

  2. The CREATE SEQUENCE command would not execute because the minimum value and maximum value for the sequence have not been specified.

  3. The CREATE SEQUENCE command would not execute because the starting value of the sequence and the increment value have not been specified.

  4. All the statements would execute successfully and the ORD_NO column would have the value 20 for the CUST_ID 101 because the default CACHE value is 20.

Answer: A

Question No: 130 – (Topic 1)

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

Answer: A,B,C,F 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:

A SELECT list A FROM clause

The following are optional clauses: 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.

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