[Free] 2019(Nov) EnsurePass Oracle 1z0-061 Dumps with VCE and PDF 121-130

Get Full Version of the Exam
http://www.EnsurePass.com/1z0-061.html

Question No.121

The ORDERS TABLE belongs to the user OE. OE has granted the SELECT privilege on the ORDERS table to the user HR.

Which statement would create a synonym ORD so that HR can execute the following query successfully?

SELECT * FROM ord;

  1. CREATE SYNONYM ord FOR orders; This command is issued by OE.

  2. CREATE PUBLIC SYNONYM ord FOR orders; This command is issued by OE.

  3. CREATE SYNONYM ord FOR oe.orders; This command is issued by the database administrator.

  4. CREATE PUBLIC SYNONYM ord FOR oe.orders; This command is issued by the database administrator.

Correct Answer: D

Explanation:

Creating a Synonym for an Object

To refer to a table that is owned by another user, you need to prefix the table name with the name of the user who created it, followed by a period. Creating a synonym eliminates the need to qualify the object name with the schema and provides you with an alternative name for a table, view, sequence, procedure, or other objects. This method can be especially useful with lengthy object names, such as views.

In the syntax:

PUBLIC Creates a synonym that is accessible to all users synonym Is the name of the synonym to be created object Identifies the object for which the synonym is created Guidelines

The object cannot be contained in a package.

A private synonym name must be distinct from all other objects that are owned by the same user. If you try to execute the following command (alternative B, issued by OE):

Question No.122

Examine the structure of the MARKS table:

image

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

  1. SELECT student_name, subject1 FROM marks

    WHERE subject1 gt; AVG(subject1);

  2. SELECT student_name, SUM(subject1) FROM marks

    WHERE student_name LIKE #39;R%#39;;

  3. SELECT SUM(subject1 subject2 subject3) FROM marks

    WHERE student_name IS NULL;

  4. SELECT SUM(DISTINCT NVL(subject1, 0)), MAX(subject1) FROM marks

WHERE subject1 gt; subject2;

Correct Answer: CD

Question No.123

See the structure of the PROGRAMS table:

image

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;

Correct Answer: AD

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

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

Correct Answer: CD

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

A SELECT statement can be used to perform these three functions: Which set of keywords describes these capabilities?

  1. difference, projection, join

  2. selection, projection, join

  3. selection, intersection, join

  4. intersection, projection, join

  5. difference, projection, product

Correct Answer: B

Explanation:

Choose rows from a table is SELECTION, Choose column from a table is PROJECTION

Bring together data in different table by creating a link between them is JOIN.

Incorrect answer:

A. answer should have SELECTION, PROJECTION and JOIN.

  1. answer should have SELECTION, PROJECTION and JOIN.

  2. answer should have SELECTION, PROJECTION and JOIN.

  3. answer should have SELECTION, PROJECTION and JOIN.

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

Question No.126

Evaluate the following SQL statements:

image

The above command fails when executed. What could be the reason?

  1. The BETWEEN clause cannot be used for the CHECK constraint

  2. SYSDATE cannot be used with the CHECK constraint

  3. ORD_NO and ITEM_NO cannot be used as a composite primary key because ORD_NO is also the FOREIGN KEY

  4. The CHECK constraint cannot be placed on columns having the DATE data type

Correct Answer: B Explanation: CHECK Constraint

The CHECK constraint defines a condition that each row must satisfy. The condition can use the same constructs as the query conditions, with the following exceptions:

References to the CURRVAL, NEXTVAL, LEVEL, and ROWNUM pseudocolumns Calls to SYSDATE, UID, USER, and USERENV functions Queries that refer to other values in other rows A single column can have multiple CHECK constraints that refer to the column in its definition.

There is no limit to the number of CHECK constraints that you can define on a column. CHECK constraints can be defined at the column level or table level.

CREATE TABLE employees (…

salary NUMBER(8, 2) CONSTRAINT emp_salary_min CHECK (salary gt; 0),

Question No.127

Which statement correctly describes SQL and /SQL*Plus?

  1. Both SQL and /SQL*plus allow manipulation of values in the database.

  2. /SQL*Plus recognizes SQL statements and sends them to the server; SQL is the Oracle proprietary interface for executing SQL statements.

  3. /SQL*Plus is a language for communicating with the Oracle server to access data; SQL recognizes SQL statements and sends them to the server.

  4. SQL manipulates data and table definitions in the database; /SQL*Plus does not allow manipulation of values in the database.

Correct Answer: A

Question No.128

You need to display the date 11-Oct-2007 in words as `Eleventh of October, Two Thousand Seven#39;. 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;

Correct 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.129

Examine the structure of the INVOICE table:

image

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

  1. SELECT inv_no, NVL2(inv_date, #39;Pending#39;, #39;Incomplete#39;)FROM invoice;

  2. SELECT inv_no, NVL2(inv_amt, inv_date, #39;Not Available#39;)FROM invoice;

  3. SELECT inv_no, NVL2(inv_date, sysdate-inv_date, sysdate)FROM invoice;

  4. SELECT inv_no, NVL2(inv_amt, inv_amt*.25, #39;Not Available#39;)FROM invoice;

Correct Answer: AC

Explanation:

The NVL2 Function

The NVL2 function provides an enhancement to NVL but serves a very similar purpose. It evaluates whether a column or expression of any data type is null or not.

5-6 The NVL function\

If the first term is not null, the second parameter is returned, else the third parameter is returned. Recall that the NVL function is different since it returns the original term if it is not null. The NVL2 function takes three mandatory parameters. Its syntax is NVL2(original, ifnotnull, ifnull), where original represents the term being tested. Ifnotnull is returned if original is not null, and ifnull is returned if original is null. The data types of the ifnotnull and ifnull parameters must be compatible, and they cannot be of type LONG. They must either be of the same type, or it must be possible to convert ifnull to the type of the ifnotnull parameter. The data type returned by the NVL2 function is the same as that of the ifnotnull parameter.

Question No.130

Which three are true? (Choose three.)

  1. A MERGE statement is used to merge the data of one table with data from another.

  2. A MERGE statement replaces the data of one table with that of another.

  3. A MERGE statement can be used to insert new rows into a table.

  4. A MERGE statement can be used to update existing rows in a table.

Correct Answer: ACD

Explanation:

The MERGE Statement allows you to conditionally insert or update data in a table. If the rows are present in the target table which match the join condition, they are updated if the rows are not present they are inserted into the target table

Get Full Version of the Exam
1z0-061 Dumps
1z0-061 VCE and PDF

Leave a Reply

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

Proudly powered by WordPress