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

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

Evaluate the following SQL query;

Ensurepass 2018 PDF and VCE

What would be the outcome?

A. 200

B. 16 C. 160 D. 150 E. 100

Answer: C Explanation: Function Purpose

ROUND(column|expression, n) Rounds the column, expression, or value to n decimal places or, if n is omitted, no decimal places (If n is negative, numbers to the left of decimal point are rounded.)

TRUNC(column|expression, n) Truncates the column, expression, or value to n decimal places or, if n is omitted, n defaults to zero

Question No: 152 – (Topic 1)

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

Ensurepass 2018 PDF and VCE

Using the CUSTOMERS table, you need to generate a report that shown the average credit limit for customers in WASHINGTON and NEW YORK.

Which SQL statement would produce the required result?

A.

SELECT cust_city, AVG(cust_credit_limit) FROM customers

WHERE cust_city IN (#39;WASHINGTON#39;,#39;NEW YORK#39;)

GROUP BY cust_credit_limit, cust_city;

B.

SELECT cust_city, AVG(cust_credit_limit) FROM customers

WHERE cust_city IN (#39;WASHINGTON#39;,#39;NEW YORK#39;)

GROUP BY cust_city,cust_credit_limit;

C.

SELECT cust_city, AVG(cust_credit_limit) FROM customers

WHERE cust_city IN (#39;WASHINGTON#39;,#39;NEW YORK#39;)

GROUP BY cust_city;

D.

SELECT cust_city, AVG(NVL(cust_credit_limit,0)) FROM customers

WHERE cust_city IN (#39;WASHINGTON#39;,#39;NEW YORK#39;);

Answer: C Explanation:

Creating Groups of Data: GROUP BY Clause Syntax

You can use the GROUP BY clause to divide the rows in a table into groups. You can then use the group functions to return summary information for each group.

In the syntax:

group_by_expression Specifies the columns whose values determine the basis for grouping rows

Guidelines

  • If you include a group function in a SELECT clause, you cannot select individual results as well, unless the individual column appears in the GROUP BY clause. You receive an error message if you fail to include the column list in the GROUP BY clause.

  • Using a WHERE clause, you can exclude rows before dividing them into groups.

  • You must include the columns in the GROUP BY clause.

  • You cannot use a column alias in the GROUP BY clause.

    Question No: 153 – (Topic 1)

    You are currently located in Singapore and have connected to a remote database in Chicago. You issue the following command:

    Exhibit:

    Ensurepass 2018 PDF and VCE

    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 Chicago data and time

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

    3. An error because the WHERE condition specified is invalid

    4. An error because the ROUND function specified is invalid

    Answer: A

    Question No: 154 – (Topic 1)

    Evaluate the SQL statement: TRUNCATE TABLE DEPT;

    Which three are true about the SQL statement? (Choose three.)

    1. It releases the storage space used by the table.

    2. It does not release the storage space used by the table.

    3. You can roll back the deletion of rows after the statement executes.

    4. You can NOT roll back the deletion of rows after the statement executes.

    5. An attempt to use DESCRIBE on the DEPT table after the TRUNCATE statement executes will display an error.

    6. You must be the owner of the table or have DELETE ANY TABLE system privileges to truncate the DEPT table

    Answer: A,D,F Explanation:

    A: The TRUNCATE TABLE Statement releases storage space used by the table, D: Can not rollback the deletion of rows after the statement executes,

    F: You must be the owner of the table or have DELETE ANY TABLE system privilege to truncate the DEPT table.

    Incorrect answer: Cis not true

    Dis not true Eis not true

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

    Question No: 155 – (Topic 1)

    You want to create an ORD_DETAIL table to store details for an order placed having the following business requirement:

    1. The order ID will be unique and cannot have null values.

    2. The order date cannot have null values and the default should be the current date.

    3. The order amount should not be less than 50.

    4. The order status will have values either shipped or not shipped.

    5. The order payment mode should be cheque, credit card, or cash on delivery (COD). Which is the valid DDL statement for creating the ORD_DETAIL table?

    A.

    CREATE TABLE ord_details

    (ord_id NUMBER(2) CONSTRAINT ord_id_nn NOT NULL, ord_date DATE DEFAULT SYSDATE NOT NULL,

    ord_amount NUMBER(5, 2) CONSTRAINT ord_amount_min CHECK (ord_amount gt; 50),

    ord_status VARCHAR2(15) CONSTRAINT ord_status_chk CHECK (ord_status IN (#39;Shipped#39;, #39;Not Shipped#39;)),

    ord_pay_mode VARCHAR2(15) CONSTRAINT ord_pay_chk CHECK (ord_pay_mode IN (#39;Cheque#39;, #39;Credit Card#39;,

    #39;Cash On Delivery#39;)));

    B.

    CREATE TABLE ord_details

    (ord_id NUMBER(2) CONSTRAINT ord_id_uk UNIQUE NOT NULL, ord_date DATE DEFAULT SYSDATE NOT NULL,

    ord_amount NUMBER(5, 2) CONSTRAINT ord_amount_min CHECK (ord_amount gt; 50),

    ord_status VARCHAR2(15) CONSTRAINT ord_status_chk CHECK (ord_status IN (#39;Shipped#39;, #39;Not Shipped#39;)), ord_pay_mode VARCHAR2(15) CONSTRAINT ord_pay_chk CHECK (ord_pay_mode IN (#39;Cheque#39;, #39;Credit Card#39;,

    #39;Cash On Delivery#39;)));

    C.

    CREATE TABLE ord_details

    (ord_id NUMBER(2) CONSTRAINT ord_id_pk PRIMARY KEY, ord_date DATE DEFAULT SYSDATE NOT NULL,

    ord_amount NUMBER(5, 2) CONSTRAINT ord_amount_min CHECK (ord_amount gt;= 50),

    ord_status VARCHAR2(15) CONSTRAINT ord_status_chk CHECK (ord_status IN (#39;Shipped#39;, #39;Not Shipped#39;)), ord_pay_mode VARCHAR2(15) CONSTRAINT ord_pay_chk CHECK (ord_pay_mode IN (#39;Cheque#39;, #39;Credit Card#39;,

    #39;Cash On Delivery#39;)));

    D.

    CREATE TABLE ord_details (ord_id NUMBER(2),

    ord_date DATE NOT NULL DEFAULT SYSDATE,

    ord_amount NUMBER(5, 2) CONSTRAINT ord_amount_min CHECK (ord_amount gt;= 50),

    ord_status VARCHAR2(15) CONSTRAINT ord_status_chk CHECK (ord_status IN (#39;Shipped#39;, #39;Not Shipped#39;)), ord_pay_mode VARCHAR2(15) CONSTRAINT ord_pay_chk CHECK (ord_pay_mode IN (#39;Cheque#39;, #39;Credit Card#39;,

    #39;Cash On Delivery#39;)));

    Answer: C

    Question No: 156 – (Topic 1)

    You need to calculate the number of days from 1st January 2007 till date . Dates are stored in the default format of dd-mon-rr. Which two SQL statements would give the required

    output? (Choose two.)

    1. SELECT SYSDATE – #39;01-JAN-2007#39; FROM DUAL:

    2. SELECT SYSDATE – TOJDATE(X)1/JANUARY/2007quot;) FROM DUAL:

    3. SELECT SYSDATE – TOJDATE(#39;01-JANUARY-2007#39;) FROM DUAL:

    4. SELECT TO_CHAR(SYSDATE. #39;DD-MON-YYYY#39;) – #39;01-JAN-2007#39; FROM DUAL:

    5. SELECT TO_DATE(SYSDATE. *DD/MONTH/YYYY#39;) – #39;01/JANUARY/2007#39; FROM DUAL:

    Answer: B,C

    Topic 2, Volume BUse these to reinforce the exam concepts.

    Question No: 157 – (Topic 2)

    Examine the data in the CUST_NAME column of the CUSTOMERS table. CUST_NAME

    ————

    Renske Ladwig Jason Mallin Samuel McCain Allan MCEwen Irene Mikkilineni Julia Nayer

    You need to display customers#39; second names where the second name starts with quot;Mcquot; or quot;MC.quot;

    Which query gives the required output?

    1. SELECT SUBSTR(cust_name, INSTR(cust_name,#39; #39;) 1) FROM customers

      WHERE INITCAP(SUBSTR(cust_name, INSTR(cust_name,#39; #39;) 1))=#39;Mc#39;;

    2. SELECT SUBSTR(cust_name, INSTR(cust_name,#39; #39;) 1) FROM customers

      WHERE INITCAP(SUBSTR(cust_name, INSTR(cust_name,#39; #39;) 1)) LIKE #39;Mc%#39;;

    3. SELECT SUBSTR(cust_name, INSTR(cust_name,#39; #39;) 1) FROM customers

      WHERE SUBSTR(cust_name, INSTR(cust_name,#39; #39;) 1) LIKE INITCAP(#39;MC%#39;);

    4. SELECT SUBSTR(cust_name, INSTR(cust_name,#39; #39;) 1) FROM customers

    WHERE INITCAP(SUBSTR(cust_name, INSTR(cust_name,#39; #39;) 1)) = INITCAP(#39;MC%#39;);

    Answer: B

    Question No: 158 – (Topic 2)

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

    1. They can be created on tables and clusters.

    2. They can be created on tables and simple views.

    3. You can create only one index by using the same columns.

    4. You can create more than one index by using the same columns if you specify distinctly different combinations of the columns.

    Answer: A,D

    Question No: 159 – (Topic 2)

    Examine the structure and data in the PRICE_LIST table:

    Name Null Type

    ———–

    PROD_ID NOT NULL NUMBER(3) PROD_PRICE VARCHAR2(10) PROD_ID PROD_PRICE

    ———– 100 $234.55

    101 $6,509.75

    102 $1,234

    You plan to give a discount of 25% on the product price and need to display the discount amount in the same format as the PROD_PRICE.

    Which SQL statement would give the required result?

    1. SELECT TO_CHAR(prod_price* .25,#39;$99,999.99#39;) FROM PRICE_LIST;

    2. SELECT TO_CHAR(TO_NUMBER(prod_price)* .25,#39;$99,999.00#39;) FROM PRICE_LIST;

    C. SELECT TO_CHAR(TO_NUMBER(prod_price,#39;$99,999.99#39;)* .25,#39;$99,999.00#39;) FROM PRICE_LIST;

    D. SELECT TO_NUMBER(TO_NUMBER(prod_price,#39;$99,999.99#39;)* .25,#39;$99,999.00#39;) FROM PRICE_LIST;

    Answer: B

    Explanation: Use TO_NUMBER on the prod_price column to convert from char to number to be able to multiply it with 0.25. Then use the TO_CHAR function (with formatting#39;$99,999.00#39;) to convert the number back to char.

    Incorrect:

    Not C: Use the formatting#39;$99,999.00#39; with the TO_CHAR function, not with the TO_NUMBER function.

    Note:

    • Using the TO_CHAR Function

      The TO_CHAR function returns an item of data type VARCHAR2. When applied to items of type NUMBER, several formatting options are available. The syntax is as follows: TO_CHAR(number1, [format], [nls_parameter]),

      The number1 parameter is mandatory and must be a value that either is or can be implicitly converted into a number. The optional format parameter may be used to specify numeric formatting information like width, currency symbol, the position of a decimal point, and group (or thousands) separators and must be enclosed in single

    • Syntax of Explicit Data Type Conversion Functions

    TO_NUMBER(char1, [format mask], [nls_parameters]) = num1 TO_CHAR(num1, [format mask], [nls_parameters]) = char1 TO_DATE(char1, [format mask], [nls_parameters]) = date1 TO_CHAR(date1, [format mask], [nls_parameters]) = char1

    Question No: 160 – (Topic 2)

    Examine the structure of the ORDERS table:

    Ensurepass 2018 PDF and VCE

    You want to find the total value of all the orders for each year and issue the following command:

    SQLgt;SELECT TO_CHAR(order_date,#39;rr#39;), SUM(order_total) FROM orders

    GROUP BY TO_CHAR(order_date,#39;yyyy#39;); Which statement is true regarding the outcome?

    1. It executes successfully and gives the correct output.

    2. It gives an error because the TO_CHAR function is not valid.

    3. It executes successfully but does not give the correct output.

    4. It gives an error because the data type conversion in the SELECT list does not match the data type conversion in the GROUP BY clause.

    Answer: D

    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.