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

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

Which statement is true regarding synonyms?

  1. Synonyms can be created only for a table

  2. Synonyms are used to reference only those tables that are owned by another user

  3. The DROP SYNONYM statement removes the synonym and the table on which the synonym has been created becomes invalid

  4. A public synonym and a private synonym can exist with the same name for the same table

Answer: D

Question No: 42 – (Topic 1)

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

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?

Latesttests 2018 PDF and VCE

  1. SELECT cust_city, cust_income_level, cust_last_name FROM customers

    ORDER BY cust_city desc, cust_income_level DESC;

  2. SELECT cust_city, cust_income_level, cust_last_name FROM customers

    ORDER BY cust_income_level desc, cust_city DESC;

  3. SELECT cust_city, cust_income_level, cust_last_name FROM customers

    ORDER BY (cust_city, cust_income_level) DESC;

  4. SELECT cust_city, cust_income_level, cust_last_name FROM customers

ORDER BY cust_city, cust_income_level DESC;

Answer: A

Question No: 43 – (Topic 1)

Examine the structure of the EMPLOYEES and NEW_EMPLOYEES tables:

Latesttests 2018 PDF and VCE

Which MERGE statement is valid?

A.

MERGE INTO new_employees c USING employees e

ON (c.employee_id = e.employee_id) WHEN MATCHED THEN

UPDATE SET

B. name = e.first_name ||#39;,#39;|| e.last_name WHEN NOT MATCHED THEN

INSERT

value

S(e.employee_id, e.first_name ||#39;, #39;||e.last_name);

C.

MERGE new_employees c USING employees e

ON (c.employee_id = e.employee_id) WHEN EXISTS THEN

UPDATE SET

D. name = e.first_name ||#39;,#39;|| e.last_name WHEN NOT MATCHED THEN INSERT valueS(e.employee_id, e.first_name ||#39;, #39;||e.last_name);

E.

MERGE INTO new_employees cUSING employees e ON (c.employee_id = e.employee_id)

WHEN EXISTS THEN UPDATE SET

F. name = e.first_name ||#39;,#39;|| e.last_name WHEN NOT MATCHED THEN

INSERT

value

S(e.employee_id, e.first_name ||#39;, #39;||e.last_name);

G.

MERGE new_employees c

FROM employees e ON (c.employee_id = e.employee_id) WHEN MATCHED THEN

UPDATE SET

H. name = e.first_name ||#39;,#39;|| e.last_name WHEN NOT MATCHED THEN

INSERT INTO

new_employees valueS(e.employee_id, e.first_name ||#39;, #39;||e.last_name);

Answer: A

Explanation: Explanation: this is the correct MERGE statement syntax

Incorrect answer:

Bit should MERGE INTO table_name Cit should be WHEN MATCHED THEN Dit should MERGE INTO table_name

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

Question No: 44 – (Topic 1)

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

Latesttests 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:

Latesttests 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. The total number of columns in the NEW_CUSTOMERS table does not match the total number of columns in the CUSTOMERS table

  3. The WHERE clause cannot be used in a sub query embedded in an INSERT statement

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

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

Evaluate the following query:

Latesttests 2018 PDF and VCE

What would be the outcome of the above query?

  1. It produces an error because flower braces have been used.

  2. It produces an error because the data types are not matching.

  3. It executes successfully and introduces an #39;s at the end of each promo_name in the

    output.

  4. It executes successfully and displays the literalquot; {#39;s start date was} quot; for each row in the output.

Answer: C Explanation:

So, how are words that contain single quotation marks dealt with? There are essentially two mechanisms available. The most popular of these is to add an additional single quotation mark next to each naturally occurring single quotation mark in the character string

Oracle offers a neat way to deal with this type of character literal in the form of the alternative quote (q) operator. Notice that the problem is that Oracle chose the single quote characters as the special pair of symbols that enclose or wrap any other character literal.

These character-enclosing symbols could have been anything other than single quotation marks.

Bearing this in mind, consider the alternative quote (q) operator. The q operator enables you to choose from a set of possible pairs of wrapping symbols for character literals as alternatives to the single quote symbols. The options are any single-byte or multibyte character or the four brackets: (round brackets), {curly braces}, [squarebrackets], or lt;angle bracketsgt;. Using the q operator, the character delimiter can effectively be changed from a single quotation mark to any other character

The syntax of the alternative quote operator is as follows:

q#39;delimiter#39;character literal which may include the single quotes delimiter#39; where delimiter can be any character or bracket.

Alternative Quote (q) Operator

Specify your own quotation mark delimiter. Select any delimiter.

Increase readability and usability.

SELECT department_name || q#39;[ Department#39;s Manager Id: ]#39;

|| manager_id

AS quot;Department and Managerquot; FROM departments;

Alternative Quote (q) Operator

Many SQL statements use character literals in expressions or conditions. If the literal itself contains a single quotation mark, you can use the quote (q) operator and select your own quotation mark delimiter.

You can choose any convenient delimiter, single-byte or multibyte, or any of the following character pairs: [ ], { }, ( ), or lt; gt;.

In the example shown, the string contains a single quotation mark, which is normally

interpreted as a delimiter of a character string. By using the q operator, however, brackets [] are used as the quotation mark delimiters. The string between the brackets delimiters is interpreted as a literal character string.

Question No: 46 – (Topic 1)

Which three SQL statements would display the value 1890.55 as $1,890.55? (Choose three.)

A.

SELECT TO_CHAR(1890.55,#39;$99G999D00#39;) FROM DUAL;

B.

SELECT TO_CHAR(1890.55,#39;$9,999V99#39;) FROM DUAL;

C.

SELECT TO_CHAR(1890.55,#39;$0G000D00#39;) FROM DUAL;

D.

SELECT TO_CHAR(1890.55,#39;$99G999D99#39;) FROM DUAL;

E.

SELECT TO_CHAR(1890.55,#39;$9,999D99#39;) FROM DUAL;

Answer: A,C,D

Question No: 47 – (Topic 1)

Which three statements are true regarding sub queries? (Choose three.)

  1. Multiple columns or expressions can be compared between the main query and sub query

  2. Sub queries can contain GROUP BY and ORDER BY clauses

  3. Only one column or expression can be compared between the main query and subqeury

  4. Main query and sub query can get data from different tables

  5. Main query and sub query must get data from the same tables

  6. Sub queries can contain ORDER BY but not the GROUP BY clause

Answer: A,B,D

Question No: 48 – (Topic 1)

Evaluate this SQL statement:

SELECT e.emp_name, d.dept_name FROM employees e

JOIN departments d USING (department_id)

WHERE d.department_id NOT IN (10,40) ORDER BY dept_name;

The statement fails when executed. Which change fixes the error?

  1. remove the ORDER BY clause

  2. remove the table alias prefix from the WHERE clause

  3. remove the table alias from the SELECT clause

  4. prefix the column in the USING clause with the table alias

  5. prefix the column in the ORDER BY clause with the table alias

  6. replace the condition “d.department_id NOT IN (10,40)” in the WHERE clause with

“d.department_id lt;gt; 10 AND d.department_id lt;gt; 40”

Answer: B

Question No: 49 – (Topic 1)

Which two statements are true regarding the COUNT function? (Choose two.)

  1. COUNT(*) returns the number of rows including duplicate rows and rows containing NULL value in any of the columns

  2. COUNT(cust_id) returns the number of rows including rows with duplicate customer IDs and NULL value in the CUST_ID column

  3. COUNT(DISTINCT inv_amt) returns the number of rows excluding rows containing duplicates and NULL values in the INV_AMT column

  4. A SELECT statement using COUNT function with a DISTINCT keyword cannot have a WHERE clause

  5. The COUNT function can be used only for CHAR, VARCHAR2 and NUMBER data types

Answer: A,C Explanation:

Using the COUNT Function

The COUNT function has three formats: COUNT(*)

COUNT(expr) COUNT(DISTINCT expr)

COUNT(*) returns the number of rows in a table that satisfy the criteria of the SELECT statement, including duplicate rows and rows containing null values in any of the columns. If a WHERE clause is included in the SELECT statement, COUNT(*) returns the number of rows that satisfy the condition in the WHERE clause.

In contrast,

COUNT(expr) returns the number of non-null values that are in the column identified by expr.

COUNT(DISTINCT expr) returns the number of unique, non-null values that are in the column identified by expr.

Question No: 50 – (Topic 1)

View the Exhibit and examine the data in the PRODUCTS table. You need to display product names from the PRODUCTS table that belong to the #39;Software/Other1 category with minimum prices as either $2000 or $4000 and no unit of measure. You issue thej following query:

Latesttests 2018 PDF and VCE

Which statement is true regarding the above query?

Latesttests 2018 PDF and VCE

  1. It executes successfully but returns no result.

  2. It executes successfully and returns the required result.

  3. It generates an error because the condition specified for PROD_UNIT_OF_MEASURE is not valid.

  4. It generates an error because the condition specified for the PROD_CATEGORY column is not valid.

Answer: A

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