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

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

View the Exhibit and examine the description for the CUSTOMERS table.

Latesttests 2018 PDF and VCE

You want to update the CUST_INCOME_LEVEL and CUST_CREDIT_LIMIT columns for the customer with the CUST_ID 2360. You want the value for the CUST_INCOME_LEVEL to have the same value as that of the customer with the CUST_ID 2560 and the CUST_CREDIT_LIMIT to have the same value as that of the customer with CUST_ID

2566.

Which UPDATE statement will accomplish the task?

A.

UPDATE customers

SET cust_income_level = (SELECT cust_income_level FROM customers

WHERE cust_id = 2560),

cust_credit_limit = (SELECT cust_credit_limit FROM customers

WHERE cust_id = 2566) WHERE cust_id=2360; B.

UPDATE customers

SET (cust_income_level,cust_credit_limit) = (SELECT cust_income_level, cust_credit_limit

FROM customers

WHERE cust_id=2560 OR cust_id=2566) WHERE cust_id=2360;

C.

UPDATE customers

SET (cust_income_level,cust_credit_limit) = (SELECT cust_income_level, cust_credit_limit

FROM customers

WHERE cust_id IN(2560, 2566)

WHERE cust_id=2360;

D.

UPDATE customers

SET (cust_income_level,cust_credit_limit) = (SELECT cust_income_level, cust_credit_limit

FROM customers

WHERE cust_id=2560 AND cust_id=2566) WHERE cust_id=2360;

Answer: A Explanation:

Updating Two Columns with a Subquery

You can update multiple columns in the SET clause of an UPDATE statement by writing multiple subqueries. The syntax is as follows:

UPDATE table SET column = (SELECT column FROM table

WHERE condition) [ ,

column = (SELECT column FROM table

WHERE condition)] [WHERE condition ] ;

Question No: 52 – (Topic 1)

Which statement is true regarding the UNION operator?

  1. The number of columns selected in all SELECT statements need to be the same

  2. Names of all columns must be identical across all SELECT statements

  3. By default, the output is not sorted

  4. NULL values are not ignored during duplicate checking

Answer: A Explanation:

The SQL UNION query allows you to combine the result sets of two or more SQL SELECT statements. It removes duplicate rows between the various SELECT statements.

Each SQL SELECT statement within the UNION query must have the same number of fields in the result sets with similar data types.

Question No: 53 – (Topic 1)

View the Exhibit and examine the structure of the CUSTOMERS table. Evaluate the following SQL statement:

Latesttests 2018 PDF and VCE

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

Latesttests 2018 PDF and VCE

  1. It executes successfully.

  2. It returns an error because the BETWEEN operator cannot be used in the HAVING clause.

  3. It returns an error because WHERE and HAVING clauses cannot be used in the same SELECT statement.

  4. It returns an error because WHERE and HAVING clauses cannot be used to apply conditions on the same column.

Answer: A

Question No: 54 – (Topic 1)

Examine the structure of the STUDENTS table:

Latesttests 2018 PDF and VCE

You need to create a report of the 10 students who achieved the highest ranking in the course INT SQL and who completed the course in the year 1999.

Which SQL statement accomplishes this task?

  1. SELECT student_ id, marks, ROWNUM quot;Rankquot; FROM students

    WHERE ROWNUM lt;= 10

    AND finish_date BETWEEN #39;01-JAN-99#39; AND #39;31-DEC-99

    AND course_id = #39;INT_SQL#39; ORDER BY marks DESC;

  2. SELECT student_id, marks, ROWID quot;Rankquot; FROM students

    WHERE ROWID lt;= 10

    AND finish_date BETWEEN #39;01-JAN-99#39; AND #39;31-DEC-99#39;

    AND course_id = #39;INT_SQL#39; ORDER BY marks;

  3. SELECT student_id, marks, ROWNUM quot;Rankquot; FROM (SELECT student_id, marks

    FROM students

    WHERE ROWNUM lt;= 10

    AND finish_date BETWEEN #39;01-JAN-99#39; AND #39;31-DEC- 99#39;

    AND course_id = #39;INT_SQL#39; ORDER BY marks DESC);

  4. SELECT student_id, marks, ROWNUM quot;Rank” FROM (SELECT student_id, marks

    FROM students

    WHERE (finish_date BETWEEN ’01-JAN-99 AND ’31-DEC-99′

    AND course_id = ‘INT_SQL’ ORDER BY marks DESC) WHERE ROWNUM lt;= 10 ;

  5. SELECTstudent id, marks, ROWNUM “Rank” FROM(SELECT student_id, marks

FROM students

ORDER BY marks) WHEREROWNUM lt;= 10

ANDfinish date BETWEEN ’01-JAN-99′ AND ’31-DEC-99′

ANDcourse_id = ‘INT_SQL’;

Answer: D

Question No: 55 – (Topic 1)

View the Exhibit and examine the structure of the CUSTOMERS and CUST_HISTORY tables.

Latesttests 2018 PDF and VCE

The CUSTOMERS table contains the current location of all currently active customers. The CUST_HISTORY table stores historical details relating to any changes in the location of all current as well as previous customers who are no longer active with the company.

You need to find those customers who have never changed their address. Which SET operator would you use to get the required output?

  1. INTERSECT

  2. UNION ALL

  3. MINUS

  4. UNION

Answer: C

Question No: 56 – (Topic 1)

View the Exhibit and evaluate structures of the SALES, PRODUCTS, and COSTS tables.

Latesttests 2018 PDF and VCE

Evaluate the following SQL statements:

Latesttests 2018 PDF and VCE

Which statement is true regarding the above compound query?

  1. It shows products that have a cost recorded irrespective of sales

  2. It shows products that were sold and have a cost recorded

  3. It shows products that were sold but have no cost recorded

  4. It reduces an error

Answer: C

Question No: 57 – (Topic 1)

Which one is a system privilege?

  1. SELECT

  2. DELETE

  3. EXECUTE

  4. ALTER TABLE

  5. CREATE TABLE

Answer: E

Question No: 58 – (Topic 1)

You need to display the date 11-Oct-2007 in words as ‘Eleventh of October, Two Thousand Seven’. Which SQL statement would give the required result?

  1. SELECT TO_CHAR(#39;11-oct-2007#39;, #39;fmDdspth quot;ofquot; Month, Year#39;) FROM DUAL;

  2. SELECT TO_CHAR(TO_DATE(#39;11-oct-2007#39;), #39;fmDdspth of month, year#39;) FROM DUAL;

  3. SELECT TO_CHAR(TO_DATE(#39;11-oct-2007#39;), #39;fmDdthsp quot;ofquot; Month, Year#39;) FROM DUAL;

  4. SELECT TO_DATE(TO_CHAR(#39;11-oct-2007#39;,#39;fmDdspth #39;#39;of#39;#39; Month, Year#39;)) FROM DUAL;

Answer: C Explanation:

Using the TO_CHAR Function with Dates

TO_CHAR converts a datetime data type to a value of VARCHAR2 data type in the format specified by the format_model. A format model is a character literal that describes the format of datetime stored in a character string. For example, the datetime format model for the string #39;11-Nov-1999#39; is #39;DD-Mon-YYYY#39;. You can use the

TO_CHAR function to convert a date from its default format to the one that you specify. Guidelines

  • The format model must be enclosed with single quotation marks and is case-sensitive.

  • The format model can include any valid date format element. But be sure to separate the date value from the format model with a comma.

  • The names of days and months in the output are automatically padded with blanks.

  • To remove padded blanks or to suppress leading zeros, use the fill mode fm element.

    Elements of the Date Format Model

    ———————————–

    DY Three-letter abbreviation of the day of the week DAY Full name of the day of the week

    DD Numeric day of the month

    MM Two-digit value for the month

    MON Three-letter abbreviation of the month MONTH Full name of the month

    YYYY Full year in numbers

    YEAR Year spelled out (in English)

    Question No: 59 – (Topic 1)

    See the structure of the PROGRAMS table:

    Latesttests 2018 PDF and VCE

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

    1. SELECT NVL(ADD_MONTHS(END_DATE,1),SYSDATE) FROM programs;

    2. SELECT TO_DATE(NVL(SYSDATE-END_DATE,SYSDATE)) FROM programs;

    3. SELECT NVL(MONTHS_BETWEEN(start_date,end_date),#39;Ongoing#39;) FROM programs;

    4. SELECT NVL(TO_CHAR(MONTHS_BETWEEN(start_date,end_date)),#39;Ongoing#39;) FROM programs;

    Answer: A,D Explanation: NVL Function

    Converts a null value to an actual value:

    Data types that can be used are date, character, and number. Data types must match:

    • NVL(commission_pct,0)

    • NVL(hire_date,#39;01-JAN-97#39;)

    • NVL(job_id,#39;No Job Yet#39;)

    MONTHS_BETWEEN(date1, date2): Finds the number of months between date1 and date2

    The result can be positive or negative. If date1 is later than date2, the result is positive; if date1 is earlier than date2, the result is negative. The noninteger part of the result represents a portion of the month.

    MONTHS_BETWEEN returns a numeric value. – answer C NVL has different datatypes – numeric and strings, which is not possible!

    The data types of the original and if null parameters must always be compatible. They must either be of the same type, or it must be possible to implicitly convert if null to the type of the original parameter. The NVL function returns a value with the same data type as the original parameter.

    Question No: 60 – (Topic 1)

    Exhibit contains the structure of PRODUCTS table:

    Latesttests 2018 PDF and VCE

    Evaluate the following query:

    Latesttests 2018 PDF and VCE

    What would be the outcome of executing the above SQL statement?

    1. It produces an error

    2. It shows the names of products whose list price is the second highest in the table.

    3. It shown the names of all products whose list price is less than the maximum list price

    4. It shows the names of all products in the table

    Answer: B

    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