[Free] 2018(Jan) EnsurePass Dumpsleader Oracle 1z0-051 Dumps with VCE and PDF 81-90

Ensurepass.com : Ensure you pass the IT Exams
2018 Jan Oracle Official New Released 1z0-051
100% Free Download! 100% Pass Guaranteed!
http://www.EnsurePass.com/1z0-051.html

Oracle Database: SQL Fundamentals I

Question No: 81 – (Topic 1)

What is true about sequences?

  1. The start value of the sequence is always 1.

  2. A sequence always increments by 1.

  3. The minimum value of an ascending sequence defaults to 1.

  4. The maximum value of descending sequence defaults to 1.

Answer: C

Question No: 82 – (Topic 1)

Which three statements are true about multiple-row sub queries? (Choose three.)

  1. They can contain a subquery within a sub query.

  2. They can return multiple columns as well as rows.

  3. They cannot contain a sub query within a sub query.

  4. They can return only one column but multiple rows.

  5. They can contain group functions and GROUP BY and HAVING clauses.

  6. They can contain group functions and the GROUP BY clause, but not the HAVING clause.

Answer: A,B,E

Question No: 83 – (Topic 1)

Ensurepass 2018 PDF and VCE

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

Using the PROMOTIONS table, you need to display the names of all promos done after January 1, 2001 starting with the latest promo.

Which query would give the required result? (Choose all that apply.)

  1. SELECT promo_name,promo_begin_date FROM promotions

    WHERE promo_begin_date gt; #39;01-JAN-01#39; ORDER BY 1 DESC;

  2. SELECT promo_name,promo_begin_date quot;START DATEquot; FROM promotions

    WHERE promo_begin_date gt; #39;01-JAN-01#39; ORDER BY quot;START DATEquot; DESC;

  3. SELECT promo_name,promo_begin_date FROM promotions

    WHERE promo_begin_date gt; #39;01-JAN-01#39; ORDER BY 2 DESC;

  4. SELECT promo_name,promo_begin_date FROM promotions

WHERE promo_begin_date gt; #39;01-JAN-01#39; ORDER BY promo_name DESC;

Answer: B,C

Question No: 84 – (Topic 1)

View the Exhibit to examine the description for the SALES table. Which views can have all DML operations performed on it? (Choose all that apply.)

Ensurepass 2018 PDF and VCE

  1. CREATE VIEW v3

    AS SELECT * FROM SALES

    WHERE cust_id = 2034 WITH CHECK OPTION;

  2. CREATE VIEW v1

    AS SELECT * FROM SALES

    WHERE time_id lt;= SYSDATE – 2*365 WITH CHECK OPTION;

  3. CREATE VIEW v2

    AS SELECT prod_id, cust_id, time_id FROM SALES WHERE time_id lt;= SYSDATE – 2*365

    WITH CHECK OPTION;

  4. CREATE VIEW v4

AS SELECT prod_id, cust_id, SUM(quantity_sold) FROM SALES WHERE time_id lt;= SYSDATE – 2*365

GROUP BY prod_id, cust_id WITH CHECK OPTION;

Answer: A,B Explanation: Creating a View

You can create a view by embedding a subquery in the CREATE VIEW statement. In the syntax:

CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW view

[(alias[, alias]…)]

AS subquery

[WITH CHECK OPTION [CONSTRAINT constraint]] [WITH READ ONLY [CONSTRAINT constraint]];

OR REPLACE Re-creates the view if it already exists

FORCE Creates the view regardless of whether or not the base tables exist NOFORCE Creates the view only if the base tables exist (This is the default.)

View Is the name of the view alias Specifies names for the expressions selected by the view’s query (The number of aliases must match the number of expressions selected by the view.)

subquery Is a complete SELECT statement (You can use aliases for the columns in the SELECT list.)

WITH CHECK OPTION Specifies that only those rows that are accessible to the view can be inserted or updated ANSWER D constraint Is the name assigned to the CHECK OPTION constraint

WITH READ ONLY Ensures that no DML operations can be performed on this view Rules for Performing DML Operations on a View

You cannot add data through a view if the view includes: Group functions

A GROUP BY clause The DISTINCT keyword

The pseudocolumn ROWNUM keyword Columns defined by expressions

NOT NULL columns in the base tables that are not selected by the view – ANSWER C

Question No: 85 – (Topic 1)

Which two statements are true regarding sub queries? (Choose two.)

  1. A sub query can retrieve zero or more rows.

  2. Only two sub queries can be placed at one level.

  3. A sub query can be used only in SQL query statements.

  4. A sub query can appeal* on either side of a comparison operator.

  5. There is no limit on the number of sub query levels in the WHERE clause of a SELECT statement.

Answer: A,D

Question No: 86 – (Topic 1)

The following data exists in the PRODUCTS table:

PROD_ID PROD_LIST_PRICE

———————– 123456 152525.99

You issue the following query:

SQLgt; SELECT RPAD(( ROUND(prod_list_price)), 10,#39;*#39;) FROM products

WHERE prod_id = 123456; What would be the outcome?

A. 152526**** B. **152525.99 C. 152525**

D. an error message

Answer: A Explanation:

The LPAD(string, length after padding, padding string) and RPAD(string, length after padding, padding string) functions add a padding string of characters to the left or right of a string until it reaches the specified length after padding.

Question No: 87 – (Topic 1)

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

  1. A sub query that defines a view cannot include the GROUP BY clause

  2. A view is created with the sub query having the DISTINCT keyword can be updated

  3. A Data Manipulation Language (DML) operation can be performed on a view that is created with the sub query having all the NOT NULL columns of a table

  4. A view that is created with the sub query having the pseudo column ROWNUM keyword cannot be updated

Answer: C,D Explanation:

Rules for Performing DML Operations on a View

You cannot add data through a view if the view includes: Group functions

A GROUP BY clause The DISTINCT keyword

The pseudocolumn ROWNUM keyword Columns defined by expressions

NOT NULL columns in the base tables that are not selected by the view

Question No: 88 – (Topic 1)

View the Exhibit and examine the structure of the PROMOTIONS table. Which SQL statements are valid? (Choose all that apply.)

Ensurepass 2018 PDF and VCE

  1. SELECT promo_id. DECODE(NVL(promo_cost.O).promo_cost * 0.25. 100) quot;Discountquot; FROM promotions;

  2. SELECT promo id. DECODE(promo_cost. 10000.

    DECODE(promo_category. #39;Gl\ promo_cost * 25. NULL). NULL) quot;Catcostquot; FROM promotions;

  3. SELECT promo_id. DECODE(NULLIF(promo_cost. 10000). NULL. promo_cost*.25,

    *N/A#39;) quot;Catcostquot; FROM promotions;

  4. SELECT promo_id. DECODE(promo_cost. gt;10000. #39;High#39;. lt;10000. #39;Low#39;) quot;Rangequot;FROM promotions;

Answer: A,B Explanation:

Note: there are some syntax issues in this question.

Question No: 89 – (Topic 1)

View the Exhibit for the structure of the STUDENT and FACULTY tables.

Ensurepass 2018 PDF and VCE

You need to display the faculty name followed by the number of students handled by the faculty at the base location. Examine the following two SQL statements:

Ensurepass 2018 PDF and VCE

Which statement is true regarding the outcome?

  1. Only statement 1 executes successfully and gives the required result.

  2. Only statement 2 executes successfully and gives the required result.

  3. Both statements 1 and 2 execute successfully and give different results.

  4. Both statements 1 and 2 execute successfully and give the same required result.

Answer: D

Question No: 90 – (Topic 1)

You need to extract details of those products in the SALES table where the PROD_ID column contains the string #39;_D123#39;. Which WHERE clause could be used in the

SELECT statement to get the required output?

  1. WHERE prod_id LIKE #39;%_D123%#39; ESCAPE #39;_#39;

  2. WHERE prod_id LIKE #39;%\_D123%#39; ESCAPE #39;\#39;

  3. WHERE prod_id LIKE #39;%_D123%#39; ESCAPE #39;%_#39;

  4. WHERE prod_id LIKE #39;%\_D123%#39; ESCAPE #39;\_#39;

Answer: B Explanation:

A naturally occurring underscore character may be escaped (or treated as a regular nonspecial symbol) using the ESCAPE identifier in conjunction with an ESCAPE character. The second example in Figure 3-12 shows the SQL statement that retrieves the JOBS table records with JOB_ID values equal to SA_MAN and SA_REP and which conforms to the original requirement:

select job_id from jobs

where job_id like #39;SA\_%#39; escape #39;\#39;;

100% Ensurepass Free Download!
Download Free Demo:1z0-051 Demo PDF
100% Ensurepass Free Guaranteed!
1z0-051 Dumps

EnsurePass 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

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.