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

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

You need to create a table with the following column specifications:

  1. Employee ID (numeric data type) for each employee

  2. Employee Name (character data type) that stores the employee name

  3. Hire date, which stores the date of joining the organization for each employee

  4. Status (character data type), that contains the value #39;ACTIVE#39; if no data is entered

  5. Resume (character large object [CLOB] data type), which contains the resume submitted by the employee

    Which is the correct syntax to create this table?

    1. CREATE TABLE EMP_1 (emp_id NUMBER(4), emp_name VARCHAR2(25), start_date DATE,

      e_status VARCHAR2(10) DEFAULT #39;ACTIVE#39;,

      resume CLOB(200));

    2. CREATE TABLE 1_EMP (emp_id NUMBER(4), emp_name VARCHAR2(25), start_date DATE,

      emp_status VARCHAR2(10) DEFAULT #39;ACTIVE#39;,

      resume CLOB);

    3. CREATE TABLE EMP_1 (emp_id NUMBER(4),

      emp_name VARCHAR2(25), start_date DATE,

      emp_status VARCHAR2(10) DEFAULT quot;ACTIVEquot;,

      resume CLOB);

    4. CREATE TABLE EMP_1 (emp_id NUMBER, emp_name VARCHAR2(25), start_date DATE,

emp_status VARCHAR2(10) DEFAULT #39;ACTIVE#39;,

resume CLOB);

Answer: D Explanation:

CLOB Character data (up to 4 GB)

NUMBER [(p,s)] Number having precision p and scale s (Precision is the total number of decimal digits and scale is the number of digits to the right of the decimal point; precision can range from 1 to 38, and scale can range from -84 to 127.)

Question No: 72 – (Topic 1)

Evaluate these two SQL statements:

SELECT last_name, salary, hire_date FROM EMPLOYEES ORDER BY salary DESC; SELECT last_name, salary, hire_date FROM EMPLOYEES ORDER BY 2 DESC;

What is true about them?

  1. The two statements produce identical results.

  2. The second statement returns a syntax error.

  3. There is no need to specify DESC because the results are sorted in descending order by default.

  4. The two statements can be made to produce identical results by adding a column alias for the salary column in the second SQL statement.

Answer: A

Explanation: Explanation: the two statement produce identical results as ORDER BY 2 will take the second column as sorting column.

Incorrect answer:

Bthere is no syntax error

Cresult are sorted in ascending order by default

DORDER BY 2 will take the second column as sorting column.

Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 2-22

Question No: 73 – (Topic 1)

View the Exhibit and examine the structure of the PRODUCTS table. You need to generate a report in the following format:

CATEGORIES

5MP Digital Photo Camera#39;s category is Photo Y Box#39;s category is Electronics

Envoy Ambassador#39;s category is Hardware

Which two queries would give the required output? (Choose two.)

Latesttests 2018 PDF and VCE

  1. SELECT prod_name || q#39;#39;#39;s category is #39; || prod_category CATEGORIES FROM products;

  2. SELECT prod_name || q#39;[#39;s ]#39;category is #39; || prod_category CATEGORIES FROM products;

  3. SELECT prod_name || q#39;\#39;s\#39; || #39; category is #39; || prod_category CATEGORIES FROM products;

  4. SELECT prod_name || q#39;lt;#39;s gt;#39; || #39;category is #39; || prod_category CATEGORIES FROM products;

Answer: C,D 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: 74 – (Topic 1)

Latesttests 2018 PDF and VCE

See the Exhibit and Examine the structure of SALES and PROMOTIONS tables: Exhibit:

You want to delete rows from the SALES table, where the PROMO_NAME column in the PROMOTIONS table has either blowout sale or everyday low price as values.

Which DELETE statements are valid? (Choose all that apply.)

A.

DELETE

FROM sales

WHERE promo_id = (SELECT promo_id FROM promotions

WHERE promo_name = #39;blowout sale#39;) AND promo_id = (SELECT promo_id FROM promotions

WHERE promo_name = #39;everyday low price#39;);

B.

DELETE

FROM sales

WHERE promo_id = (SELECT promo_id FROM promotions

WHERE promo_name = #39;blowout sale#39;) OR promo_id = (SELECT promo_id FROM promotions

WHERE promo_name = #39;everyday low price#39;);

C.

DELETE

FROM sales

WHERE promo_id IN (SELECT promo_id FROM promotions

WHERE promo_name = #39;blowout sale#39; OR promo_name = #39;everyday low price#39;); D.

D DELETE

FROM sales

WHERE promo_id IN (SELECT promo_id FROM promotions

WHERE promo_name IN (#39;blowout sale#39;,#39;everyday low price#39;));

Answer: B,C,D

Question No: 75 – (Topic 1)

View the Exhibit and examine the structure of the PRODUCTS table. All products have a list price.

You issue the following command to display the total price of each product after a discount of 25% and a tax of 15% are applied on it. Freight charges of S100 have to be applied to all the products.

Latesttests 2018 PDF and VCE

What would be the outcome if all the parentheses are removed from the above statement?

Latesttests 2018 PDF and VCE

  1. It produces a syntax error.

  2. The result remains unchanged.

  3. The total price value would be lower than the correct value.

  4. The total price value would be higher than the correct value.

Answer: B

Question No: 76 – (Topic 1)

Latesttests 2018 PDF and VCE

Examine the structure of the MARKS table: Exhibit:

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;

Answer: C,D

Question No: 77 – (Topic 1)

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

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

  1. SELECT NVL(cust_credit_limit,#39;Not Available#39;)*.15 quot;NEW CREDITquot; FROM customers;

  2. SELECT NVL(cust_credit_limit*.15,#39;Not Available#39;) quot;NEW CREDITquot; FROM customers;

  3. SELECT TO_CHAR(NVL(cust_credit_limit*.15,#39;Not Available#39;)) quot;NEW CREDITquot; FROM customers;

  4. SELECT NVL(TO_CHAR(cust_credit_limit*.15),#39;Not Available#39;) quot;NEW CREDITquot; FROM customers;

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

View the Exhibit and examine the data in the COSTS table.

Latesttests 2018 PDF and VCE

You need to generate a report that displays the IDs of all products in the COSTS table whose unit price is at least 25% more than the unit cost. The details should be displayed in the descending order of 25% of the unit cost. You issue the following query:

Latesttests 2018 PDF and VCE

Which statement is true regarding the above query?

  1. It executes and produces the required result.

  2. It produces an error because an expression cannot be used in the ORDER BY clause.

  3. It produces an error because the DESC option cannot be used with an expression in the ORDER BY clause.

  4. It produces an error because the expression in the ORDER BY clause should also be

specified in the SELECT clause.

Answer: A

Question No: 79 – (Topic 1)

Latesttests 2018 PDF and VCE

Evaluate the following two queries: Exhibit:

Exhibit:

Latesttests 2018 PDF and VCE

Which statement is true regarding the above two queries?

  1. Performance would improve in query 2 only if there are null values in the CUST_CREDIT_LIMIT column

  2. Performance would degrade in query 2

  3. There would be no change in performance

  4. Performance would improve in query 2

Answer: C Explanation:

Note: The IN operator is internally evaluated by the Oracle server as a set of OR conditions, such as a=value1 or a=value2 or a=value3. Therefore, using the IN operator has no performance benefits and is used only for logical simplicity.

Question No: 80 – (Topic 1)

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.

Answer: A,C,D

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

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