[Free] 2018(Jan) EnsurePass Pass4sure Oracle 1z0-051 Dumps with VCE and PDF 21-30

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

Which two statements are true regarding working with dates? (Choose two.)

  1. The default internal storage of dates is in the numeric format

  2. The RR date format automatically calculates the century from the SYSDATE function but allows the user to enter the century if required

  3. The default internal storage of dates is in the character format

  4. The RR date format automatically calculates the century from the SYSDATE function and does not allow the user to enter the century

Answer: A,B Explanation: Working with Dates

The Oracle Database stores dates in an internal numeric format, representing the century, year, month, day, hours, minutes, and seconds.

The default display and input format for any date is DD-MON-RR. RR Date Format

The RR date format is similar to the YY element, but you can use it to specify different centuries. Use the RR date format element instead of YY so that the century of the return value varies according to the specified two digit year and the last two digits of the current year. The table in the slide summarizes the behavior of the RR element.

Ensurepass 2018 PDF and VCE

untitled

Note the values shown in the last two rows of the above table. As we approach the middle of the century, then the RR behavior is probably not what you want.

This data is stored internally as follows:

CENTURY YEAR MONTH DAY HOUR MINUTE SECOND 19 87 06 17 17 10 43

Question No: 22 – (Topic 1)

Examine the structure of the EMPLOYEES table:

Ensurepass 2018 PDF and VCE

You want to create a SQL script file that contains an INSERT statement. When the script is

run, the INSERT statement should insert a row with the specified values into the EMPLOYEES table. The INSERT statement should pass values to the table columns as specified below:

Ensurepass 2018 PDF and VCE

Which INSERT statement meets the above requirements?

  1. INSERT INTO employees

    VALUES (emp_id_seq.NEXTVAL, #39;amp;ename#39;, #39;amp;jobid#39;, 2000, NULL, amp;did);

  2. INSERT INTO employees

    VALUES (emp_id_seq.NEXTVAL, #39;amp;ename#39;, #39;amp;jobid#39;, 2000, NULL, amp;did IN (20,50));

  3. INSERT INTO (SELECT * FROM employees

    WHERE department_id IN (20,50))

    VALUES (emp_id_seq.NEXTVAL, #39;amp;ename#39;, #39;amp;jobid#39;, 2000, NULL, amp;did);

  4. INSERT INTO (SELECT * FROM employees

    WHERE department_id IN (20,50) WITH CHECK OPTION)

    VALUES (emp_id_seq.NEXTVAL, #39;amp;ename#39;, #39;amp;jobid#39;, 2000, NULL, amp;did);

  5. INSERT INTO (SELECT * FROM employees

WHERE (department_id = 20 AND department_id = 50)

WITH CHECK OPTION )

VALUES (emp_id_seq.NEXTVAL, #39;amp;ename#39;, #39;amp;jobid#39;, 2000, NULL, amp;did);

Answer: D

Question No: 23 – (Topic 1)

You need to display the first names of all customers from the CUSTOMERS table that contain the character #39;e#39; and have the character #39;a#39; in the second last position.

Which query would give the required output?

A.

SELECT cust_first_name FROM customers

WHERE INSTR(cust_first_name, #39;e#39;)lt;gt;0 AND SUBSTR(cust_first_name, -2, 1)=#39;a#39;;

B.

SELECT cust_first_name FROM customers

WHERE INSTR(cust_first_name, #39;e#39;)lt;gt;#39;#39; AND SUBSTR(cust_first_name, -2, 1)=#39;a#39;;

C.

SELECT cust_first_name FROM customers

WHERE INSTR(cust_first_name, #39;e#39;)IS NOT NULL AND SUBSTR(cust_first_name, 1,-2)=#39;a#39;;

D.

SELECT cust_first_name FROM customers

WHERE INSTR(cust_first_name, #39;e#39;)lt;gt;0 AND SUBSTR(cust_first_name, LENGTH(cust_first_name),-2)=#39;a#39;;

Answer: A Explanation:

The SUBSTR(string, start position, number of characters) function accepts three parameters and returns a string consisting of the number of characters extracted from the source string, beginning at the specified start position:

substr(#39;http://www.domain.com#39;,12,6) = domain

The position at which the first character of the returned string begins. When position is 0 (zero), then it is treated as 1.

When position is positive, then the function counts from the beginning of string to find the first character.

When position is negative, then the function counts backward from the end of string. substring_length

The length of the returned string. SUBSTR calculates lengths using characters as defined by the input character set. SUBSTRB uses bytes instead of characters. SUBSTRC uses Unicode complete characters.

SUBSTR2 uses UCS2 code points. SUBSTR4 uses UCS4 code points.

When you do not specify a value for this argument, then the function

The INSTR(source string, search item, [start position],[nth occurrence of search item]) function returns a number that represents the position in the source string, beginning from the given start position, where the nth occurrence of the search item begins: instr(#39;http://www.domain.com#39;,#39;.#39;,1,2) = 18

Question No: 24 – (Topic 1)

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

The following query is written to retrieve all those product IDs from the SALES table that have more than 55000 sold and have been ordered more than 10 times.

Ensurepass 2018 PDF and VCE

Which statement is true regarding this SQL statement?

Ensurepass 2018 PDF and VCE

  1. It executes successfully and generates the required result.

  2. It produces an error because COUNT(*) should be specified in the SELECT clause also.

  3. It produces an error because COUNT(*) should be only in the HAVING clause and not in the WHERE clause.

  4. It executes successfully but produces no result because COUNT(prod_id) should be

    used instead of COUNT(*).

    Answer: C Explanation:

    Restricting Group Results with the HAVING Clause

    You use the HAVING clause to specify the groups that are to be displayed, thus further restricting the groups on the basis of aggregate information.

    In the syntax, group_condition restricts the groups of rows returned to those groups for which the specified condition is true.

    The Oracle server performs the following steps when you use the HAVING clause:

    1. Rows are grouped.

    2. The group function is applied to the group.

    3. The groups that match the criteria in the HAVING clause are displayed.

      The HAVING clause can precede the GROUP BY clause, but it is recommended that you place the GROUP BY clause first because it is more logical. Groups are formed and group functions are calculated before the HAVING clause is applied to the groups in the SELECT list.

      Note: The WHERE clause restricts rows, whereas the HAVING clause restricts groups.

      Question No: 25 – (Topic 1)

      See the Exhibit and examine the structure of the SALES, CUSTOMERS, PRODUCTS and ITEMS tables:

      Ensurepass 2018 PDF and VCE

      The PROD_ID column is the foreign key in the SALES table, which references the PRODUCTS table. Similarly, the CUST_ID and TIME_ID columns are also foreign keys in the SALES table referencing the CUSTOMERS and TIMES tables, respectively.

      Ensurepass 2018 PDF and VCE

      Evaluate the following the CREATE TABLE command: Exhibit:

      Which statement is true regarding the above command?

      1. The NEW_SALES table would not get created because the column names in the CREATE TABLE command and the SELECT clause do not match

      2. The NEW_SALES table would get created and all the NOT NULL constraints defined on the specified columns would be passed to the new table

      3. The NEW_SALES table would not get created because the DEFAULT value cannot be specified in the column definition

      4. The NEW_SALES table would get created and all the FOREIGN KEY constraints defined on the specified columns would be passed to the new table

Answer: B Explanation:

Creating a Table Using a Subquery

Create a table and insert rows by combining the CREATE TABLE statement and the AS subquery option.

CREATE TABLE table [(column, column…)] AS subquery;

Match the number of specified columns to the number of subquery columns. Define columns with column names and default values.

Guidelines

The table is created with the specified column names, and the rows retrieved by the SELECT statement are inserted into the table.

The column definition can contain only the column name and default value.

If column specifications are given, the number of columns must equal the number of columns in the subquery SELECT list.

If no column specifications are given, the column names of the table are the same as the column names in the subquery.

The column data type definitions and the NOT NULL constraint are passed to the new table. Note that only the explicit NOT NULL constraint will be inherited. The PRIMARY KEY column will not pass the NOT NULL feature to the new column. Any other constraint rules are not passed to the new table. However, you can add constraints in the column definition.

Question No: 26 – (Topic 1)

You need to generate a list of all customer last names with their credit limits from the CUSTOMERS table. Those customers who do not have a credit limit should appear last in the list. Winch two queries would achieve the required result? (Choose two.)

  1. SELECT cust_last_name. cust_credit_limit FROM customers ORDER BY cust_credit_limit DESC:

  2. SELECT cust_last_name. cust_credit_limit FROM customers ORDER BY cust_credit_limit:

  3. SELECT cust_last_name. cust_credit_limit FROM customers ORDER BY cust_credit_limit NULLS LAST:

  4. SELECT cust_last_name. cust_credit_limit FROM customers ORDER BY cust_last_name. cust_credit_limit NULLS LAST:

Answer: B,C Explanation:

If the ORDER BY clause is not used, the sort order is undefined, and the Oracle server may not fetch rows in the same order for the same query twice. Use the ORDER BY clause to display the rows in a specific order.

Note: Use the keywords NULLS FIRST or NULLS LAST to specify whether returned rows containing null values should appear first or last in the ordering sequence. ANSWER C Sorting

The default sort order is ascending:

  • Numeric values are displayed with the lowest values first (for example, 1 to 999).

  • Date values are displayed with the earliest value first (for example, 01-JAN-92 before 01- JAN-95).

  • Character values are displayed in the alphabetical order (for example, “A” first and “Z” last).

  • Null values are displayed last for ascending sequences and first for descending sequences.

    • ANSWER B

  • You can also sort by a column that is not in the SELECT list.

    Question No: 27 – (Topic 1)

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

    Ensurepass 2018 PDF and VCE

    and examine the structure of CUSTOMRS AND SALES tables: Evaluate the following SQL statement:

    Exhibit:

    Ensurepass 2018 PDF and VCE

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

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

    2. It would execute and restrict modifications to only the column specified in the SELECT 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 two tables cannot be used in a single UPDATE statement

    Answer: B

    Question No: 28 – (Topic 1)

    You are currently located in Singapore and have connected to a remote database in Chicago.

    Ensurepass 2018 PDF and VCE

    You issue the following command: Exhibit:

    PROMOTIONS is the public synonym for the public database link for the PROMOTIONS table.

    What is the outcome?

    1. Number of days since the promo started based on the current Singapore data and time.

    2. An error because the ROUND function specified is invalid

    3. An error because the WHERE condition specified is invalid

    4. Number of days since the promo started based on the current Chicago data and time

    Answer: D

    Question No: 29 – (Topic 1)

    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

    Answer: D

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

    Incorrect answer:

    AObject privilege on VIEW is DELETE, INSERT, REFERENCES, SELECT and UPDATE BObject privilege on VIEW is DELETE, INSERT, REFERENCES, SELECT and UPDATE CObject privilege on VIEW is DELETE, INSERT, REFERENCES, SELECT and UPDATE

    Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 13-12

    Question No: 30 – (Topic 1)

    View the Exhibit and examine the structure of the CUSTOMERS table .Which statement would display the highest credit limit available in each income level in each city in the CUSTOMERS table?

    Ensurepass 2018 PDF and VCE

    1. SELECT cust_city, cust_income_level, MAX(cust_credit_limit ) FROM customers GROUP BY cust_city, cust_income_level, cust_credit_limit;

    2. SELECT cust_city, cust_income_level, MAX(cust_credit_limit) FROM customers

      GROUP BY cust_city, cust_income_level;

    3. SELECT cust_city, cust_income_level, MAX(cust_credit_limit) FROM customers GROUP BY cust_credit_limit, cust_income_level, cust_city ;

    4. SELECT cust_city, cust_income_level, MAX(cust_credit_limit) FROM customers GROUP BY cust_city, cust_income_level, MAX(cust_credit_limit);

    Answer: B

    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

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