[Free] 2018(Jan) Dumps4cert Braindumps Oracle 1z0-051 Dumps with VCE and PDF 61-70

Dumps4cert.com : Latest Dumps with PDF and VCE Files
2018 Jan Oracle Official New Released 1z0-051
100% Free Download! 100% Pass Guaranteed!
http://www.Dumps4cert.com/1z0-051.html

Oracle Database: SQL Fundamentals I

Question No: 61 – (Topic 1)

Which four are types of functions available in SQL? (Choose 4)

  1. string

  2. character

  3. integer

  4. calendar

  5. numeric

  6. translation

  7. date

  8. conversion

Answer: B,E,G,H

Explanation: Explanation: SQL have character, numeric, date, conversion function. Incorrect answer:

ASQL have character, numeric, date, conversion function. CSQL have character, numeric, date, conversion function. DSQL have character, numeric, date, conversion function. FSQL have character, numeric, date, conversion function.

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

Question No: 62 – (Topic 1)

View the Exhibits and examine the structures of the PROMOTIONS and SALES tables.

Dumps4Cert 2018 PDF and VCE

Dumps4Cert 2018 PDF and VCE

Evaluate the following SQL statements:

Dumps4Cert 2018 PDF and VCE

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

  1. It gives details of product IDs that have been sold irrespective of whether they had a promo or not

  2. It gives the details of promos for which there have been no sales

  3. It gives the details of promos for which there have been sales

  4. It gives details of all promos irrespective of whether they have resulted in a sale or not

Answer: D

Question No: 63 – (Topic 1)

Dumps4Cert 2018 PDF and VCE

See the Exhibit and examine the structure and data in the INVOICE table: Exhibit:

Which two SQL statements would executes successfully? (Choose two.)

  1. SELECT MAX(inv_date),MIN(cust_id) FROM invoice;

  2. SELECT MAX(AVG(SYSDATE – inv_date)) FROM invoice;

  3. SELECT (AVG(inv_date) FROM invoice;

  4. SELECT AVG(inv_date – SYSDATE),AVG(inv_amt) FROM invoice;

Answer: A,D

Question No: 64 – (Topic 1)

Which two are true about aggregate functions? (Choose two.)

  1. You can use aggregate functions in any clause of a SELECT statement.

  2. You can use aggregate functions only in the column list of the select clause and in the WHERE clause of a SELECT statement.

  3. You can mix single row columns with aggregate functions in the column list of a SELECT statement by grouping on the single row columns.

  4. You can pass column names, expressions, constants, or functions as parameter to an aggregate function.

  5. You can use aggregate functions on a table, only by grouping the whole table as one single group.

  6. You cannot group the rows of a table by more than one column while using aggregate functions.

Answer: A,D

Question No: 65 – (Topic 1)

Which two statements are true regarding the USING and ON clauses in table joins? (Choose two.)

  1. The ON clause can be used to join tables on columns that have different names but compatible data types

  2. A maximum of one pair of columns can be joined between two tables using the ON clause

  3. Both USING and ON clause can be used for equijoins and nonequijoins

  4. The WHERE clause can be used to apply additional conditions in SELECT statement containing the ON or the USING clause

Answer: A,D Explanation:

Creating Joins with the USING Clause

If several columns have the same names but the data types do not match, use the USING clause to specify the columns for the equijoin.

Use the USING clause to match only one column when more than one column matches. The NATURAL JOIN and USING clauses are mutually exclusive

Using Table Aliases with the USING clause

When joining with the USING clause, you cannot qualify a column that is used in the USING clause itself. Furthermore, if that column is used anywhere in the SQL statement, you cannot alias it. For example, in the query mentioned in the slide, you should not alias the location_id column in the WHERE clause because the column is used in the USING

clause.

The columns that are referenced in the USING clause should not have a qualifier (table name oralias) anywhere in the SQL statement.

Creating Joins with the ON Clause

The join condition for the natural join is basically an equijoin of all columns with the same name.

Use the ON clause to specify arbitrary conditions or specify columns to join. – ANSWER C The join condition is separated from other search conditions. ANSWER D

The ON clause makes code easy to understand.

Question No: 66 – (Topic 1)

Dumps4Cert 2018 PDF and VCE

See the Exhibit and examine the structure and data in the INVOICE table: Exhibit:

Which two SQL statements would execute successfully? (Choose two.)

  1. SELECT MAX(inv_date),MIN(cust_id) FROM invoice;

  2. SELECT AVG(inv_date-SYSDATE),AVG(inv_amt) FROM invoice;

  3. SELECT MAX(AVG(SYSDATE-inv_date)) FROM invoice;

  4. SELECT AVG(inv_date)

FROM invoice;

Answer: A,B

Question No: 67 – (Topic 1)

Which is a valid CREATE TABLE statement?

  1. CREATE TABLE EMP9$# AS (empid number(2));

  2. CREATE TABLE EMP*123 AS (empid number(2));

  3. CREATE TABLE PACKAGE AS (packid number(2));

  4. CREATE TABLE 1EMP_TEST AS (empid number(2));

Answer: A

Explanation: Table names and column names must begin with a letter and be 1-30 characters long. Characters A-Z,a-z, 0-9, _, $ and # (legal characters but their use is discouraged).

Incorrect answer:

BNon alphanumeric character such as “*” is discourage in Oracle table name. DTable name must begin with a letter.

Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 9-4

Question No: 68 – (Topic 1)

The PRODUCTS table has the following structure:

Dumps4Cert 2018 PDF and VCE

Evaluate the following two SQL statements:

Dumps4Cert 2018 PDF and VCE

Which statement is true regarding the outcome?

  1. Both the statements execute and give the same result

  2. Both the statements execute and give different results

  3. Only the second SQL statement executes successfully

  4. Only the first SQL statement executes successfully

Answer: B Explanation:

Using the NVL2 Function

The NVL2 function examines the first expression. If the first expression is not null, the NVL2 function returns the second expression. If the first expression is null, the third expression is returned.

Syntax

NVL2(expr1, expr2, expr3) In the syntax:

expr1 is the source value or expression that may contain a null expr2 is the value that is returned if expr1 is not null

expr3 is the value that is returned if expr1 is null

Question No: 69 – (Topic 1)

View the Exhibit and examine the structure of the CUSTOMERS table. Exhibit:

Dumps4Cert 2018 PDF and VCE

you issue the following SQL statement on the CUSTOMERS table to display the customers who are in the same country as customers with the last name #39;king#39; and whose credit limit is less than the maximum credit limit in countries that have customers with the last name #39;king#39;.

Dumps4Cert 2018 PDF and VCE

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

  1. It produces an error and the lt; operator should be replaced by lt; ANY to get the required output

  2. It produces an error and the IN operator should be replaced by = in the WHERE clause of the main query to get the required output

  3. It executes and shows the required result

  4. It produces an error and the lt; operator should be replaced by lt; ALL to get the required output

Answer: C

Question No: 70 – (Topic 1)

Which arithmetic operations can be performed on a column by using a SQL function that is built into Oracle database? (Choose three.)

  1. addition

  2. subtraction

  3. raising to a power

  4. finding the quotient

  5. finding the lowest value

    Answer: A,C,E

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

    Dumps4cert 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.