[Free] 2018(Jan) EnsurePass Testking Oracle 1z0-061 Dumps with VCE and PDF 41-50

Ensurepass.com : Ensure you pass the IT Exams
2018 Jan Oracle Official New Released 1z0-061
100% Free Download! 100% Pass Guaranteed!
http://www.EnsurePass.com/1z0-061.html

Oracle Database 12c: SQL Fundamentals

Question No: 41

View the Exhibit and examine the structure of the product, component, and PDT_COMP tables.

In product table, PDTNO is the primary key.

In component table, COMPNO is the primary key.

In PDT_COMP table, lt;PDTNO, COMPNO) is the primary key, PDTNO is the foreign key referencing PDTNO in product table and COMPNO is the foreign key referencing the COMPNO in component table.

You want to generate a report listing the product names and their corresponding component names, if the component names and product names exist.

Evaluate the following query:

SQLgt;SELECT pdtno, pdtname, compno, compname FROM product pdt_comp

USING (pdtno) component USING (compno) WHERE compname IS NOT NULL;

Which combination of joins used in the blanks in the above query gives the correct output?

Ensurepass 2018 PDF and VCE

  1. JOIN; JOIN

  2. FULL OUTER JOIN; FULL OUTER JOIN

  3. RIGHT OUTER JOIN; LEFT OUTER JOIN

  4. LEFT OUTER JOIN; RIGHT OUTER JOIN

Answer: C

Question No: 42

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

Ensurepass 2018 PDF and VCE

NEW_CUSTOMERS is a new table with the columns CUST_ID, CUST_NAME and CUST_CITY that have the same data types and size as the corresponding columns in the customers table.

Evaluate the following insert statement:

Ensurepass 2018 PDF and VCE

The insert statement fails when executed. What could be the reason?

  1. The values clause cannot be used in an INSERT with a subquery.

  2. Column names in the NEW_CUSTOMERS and CUSTOMERS tables do not match.

  3. The where clause cannot be used in a subquery embedded in an INSERT statement.

  4. The total number of columns in the NEW_CUSTOMERS table does not match the total number of columns in the CUSTOMERS table.

Answer: A Explanation:

Copying Rows from Another Table

Write your INSERT statement with a subquery: Do not use the VALUES clause.

Match the number of columns in the INSERT clause to those in the subquery. Inserts all the rows returned by the subquery in the table, sales_reps.

Question No: 43

In which three situations does a transaction complete?

  1. When a DELETE statement is executed

  2. When a ROLLBACK command is executed

  3. When a PL/SQL anonymous block is executed

  4. When a data definition language (DDL) statement is executed

  5. When a TRUNCATE statement is executed after the pending transaction

Answer: B,D,E

Question No: 44

View 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 shows an increase in the credit limit by 15% for all customers. Customers whose credit limit has not been entered should have the message quot;Not Availablequot; displayed.

Which SQL statement would produce the required result?

Ensurepass 2018 PDF and VCE

  1. Option A

  2. Option B

  3. Option C

  4. Option D

Answer: 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;)

Question No: 45

Which two statements are true regarding constraints?

  1. A foreign key cannot contain null values.

  2. A column with the unique constraint can contain null values.

  3. A constraint is enforced only for the insert operation on a table.

  4. A constraint can be disabled even if the constraint column contains data.

  5. All constraints can be defined at the column level as well as the table level.

Answer: B,D

Question No: 46

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

Ensurepass 2018 PDF and VCE

You have been asked to produce a report on the customers table showing the customers details sorted in descending order of the city and in the descending order of their income level in each city.

Which query would accomplish this task?

Ensurepass 2018 PDF and VCE

  1. Option A

  2. Option B

  3. Option C

  4. Option D

Answer: A

Question No: 47

Examine the data in the ename and hiredate columns of the employees table:

Ensurepass 2018 PDF and VCE

Ensurepass 2018 PDF and VCE

You want to generate a list of user IDs as follows:

Ensurepass 2018 PDF and VCE

You issue the following query:

Ensurepass 2018 PDF and VCE

What is the outcome?

  1. It executes successfully and gives the correct output.

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

  3. It generates an error because the REPLACE function is not valid.

  4. It generates an error because the SUBSTR function cannot be nested in the CONCAT function.

Answer: A Explanation:

REPLACE (text, search_string, replacement_string)

Searches a text expression for a character string and, if found, replaces it with a specified replacement string

The REPLACE Function

The REPLACE function replaces all occurrences of a search item in a source string with a replacement term and returns the modified source string. If the length of the replacement term is different from that of the search item, then the lengths of the returned and source strings will be different. If the search string is not found, the source string is returned unchanged. Numeric and date literals and expressions are evaluated before being implicitly cast as characters when they occur as parameters to the REPLACE function.

The REPLACE function takes three parameters, with the first two being mandatory. Its syntax is REPLACE (source string, search item, [replacement term]).

If the replacement term parameter is omitted, each occurrence of the search item is removed from the source string. In other words, the search item is replaced by an empty string. .

The following queries illustrate the REPLACE function with numeric and date expressions: Query 1: select replace(10000-3, #39;9#39;, #39;85#39;) from dual

Query 2: select replace(sysdate, #39;DEC#39;, #39;NOV#39;) from dual

Question No: 48

Examine the data in the PROMO_BEGIN_DATE column of the promotions table:

Ensurepass 2018 PDF and VCE

You want to display the number of promotions started in 1999 and 2000. Which query gives the correct output?

Ensurepass 2018 PDF and VCE

  1. Option A

  2. Option B

  3. Option C

  4. Option D

    Answer: A

    Question No: 49

    The customers table has the following structure:

    Ensurepass 2018 PDF and VCE

    You need to write a query that does the following tasks:

    1. Display the first name and tax amount of the customers. Tax is 5% of their credit limit.

    2. Only those customers whose income level has a value should be considered.

    3. Customers whose tax amount is null should not be considered. Which statement accomplishes all the required tasks?

      Ensurepass 2018 PDF and VCE

      1. Option A

      2. Option B

      3. Option C

      4. Option D

Answer: B

Question No: 50

Evaluate the following SQL statement:

Ensurepass 2018 PDF and VCE

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

  1. It executes successfully and displays rows in the descending order of PROMO_CATEGORY.

  2. It produces an error because positional notation cannot be used in the order by clause with set operators.

  3. It executes successfully but ignores the order by clause because it is not located at the end of the compound statement.

  4. It produces an error because the order by clause should appear only at the end of a compound query-that is, with the last select statement.

Answer: D

100% Ensurepass Free Download!
Download Free Demo:1z0-061 Demo PDF
100% Ensurepass Free Guaranteed!
1z0-061 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

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.