[Free] 2018(Jan) Dumps4cert Braindumps Oracle 1z0-051 Dumps with VCE and PDF 191-200

Dumps4cert.com : Latest Dumps with PDF and VCE Files
2018 Jan Oracle Official New Released 1z0-051
100% Free Download! 100% Pass Guaranteed!
http://www.Dumps4cert.com/1z0-051.html

Oracle Database: SQL Fundamentals I

Question No: 191 – (Topic 2)

View the Exhibits and examine the structures of the PRODUCTS and SALES tables. Which two SQL statements would give the same output? (Choose two.)

A.

SELECT prod_id FROM products INTERSECT

SELECT prod_id FROM sales;

B.

SELECT prod_id FROM products MINUS

SELECT prod_id FROM sales;

C.

SELECT DISTINCT p.prod_id FROM products p JOIN sales s ON p.prod_id=s.prod_id;

D.

SELECT DISTINCT p.prod_id FROM products p JOIN sales s ON p.prod_id lt;gt; s.prod_id;

Answer: A,C

Question No: 192 – (Topic 2)

You issue the following query:

SQLgt; SELECT AVG(MAX(qty))

FROM ord_items GROUP BY item_no

HAVING AVG(MAX(qty))gt;50;

Which statement is true regarding the outcome of this query?

  1. It executes successfully and gives the correct output.

  2. It gives an error because the HAVING clause is not valid.

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

  4. It gives an error because the GROUP BY expression is not valid.

Answer: B Explanation:

The general form of the SELECT statement is further enhanced by the addition of the HAVING clause and becomes:

SELECT column|expression|group_function(column|expression [alias]),…}

FROM table

[WHERE condition(s)] [GROUP BY {col(s)|expr}] [HAVING group_condition(s)]

[ORDER BY {col(s)|expr|numeric_pos} [ASC|DESC] [NULLS FIRST|LAST]];

An important difference between the HAVING clause and the other SELECT statement clauses is that it may only be specified if a GROUP BY clause is present. This dependency is sensible since group-level rows must exist before they can be restricted. The HAVING clause can occur before the GROUP BY clause in the SELECT statement. However, it is more common to place the HAVING clause after the GROUP BY clause. All grouping is performed and group functions are executed prior to evaluating the HAVING clause.

Question No: 193 – (Topic 2)

View the Exhibit and examine the structure of the SALES and PRODUCTS tables.

Dumps4Cert 2018 PDF and VCE

In the SALES table, PROD_ID is the foreign key referencing PROD_ID in the PRODUCTS table. You want to list each product ID and the number of times it has been sold.

Evaluate the following query:

SQLgt;SELECT p.prod_id, COUNT(s.prod_id) FROM products p sales s ON p.prod_id = s.prod_id

GROUP BY p.prod_id;

Which two JOIN options can be used in the blank in the above query to get the required output? (Choose two.)

  1. JOIN

  2. FULL OUTER JOIN

  3. LEFT OUTER JOIN

  4. RIGHT OUTER JOIN

Answer: B,C

Question No: 194 – (Topic 2)

Examine the structure of the PROMOTIONS table:

Dumps4Cert 2018 PDF and VCE

The management wants to see a report of unique promotion costs in each promotion category.

Which query would achieve the required result?

  1. SELECT DISTINCT promo_cost, promo_category FROM promotions;

  2. SELECT promo_category, DISTINCT promo_cost FROM promotions;

  3. SELECT DISTINCT promo_cost, DISTINCT promo_category FROM promotions;

  4. SELECT DISTINCT promo_category, promo_cost FROM promotions ORDER BY 1;

Answer: D

Question No: 195 – (Topic 2)

Examine the structure of the EMPLOYEES and DEPARTMENTS tables:

Dumps4Cert 2018 PDF and VCE

You want to create a report displaying employee last names, department names, and locations. Which query should you use to create an equi-join?

  1. SELECT last_name, department_name, location_id FROM employees , departments ;

  2. SELECT employees.last_name, departments.department_name, departments.location_id

    FROM employees e, departments D WHERE e.department_id =d.department_id;

  3. SELECT e.last_name, d.department_name, d.location_id FROM employees e, departments D

    WHERE manager_id =manager_id;

  4. SELECT e.last_name, d.department_name, d.location_id FROM employees e, departments D

WHERE e.department_id =d.department_id;

Answer: D Explanation:

Equijoins are also called simple joins or inner joins. Equijoin involve primary key and foreign key.

Incorrect answer: Athere is no join B invalid syntax

Cdoes not involve the join in the primary and foreign key

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

Question No: 196 – (Topic 2)

Which three statements are true regarding subqueries? (Choose three.)

  1. Subqueries can contain GROUP BY and ORDER BY clauses.

  2. Main query and subquery can get data from different tables.

  3. Main query and subquery must get data from the same tables.

  4. Subqueries can contain ORDER BY but not the GROUP BY clause.

  5. Only one column or expression can be compared between the main query and subquery.

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

subquery.

Answer: A,B,F Explanation:

SUBQUERIES can be used in the SELECT list and in the FROM, WHERE, and HAVING clauses of a query.

A subquery can have any of the usual clauses for selection and projection. The following are required clauses:

A SELECT list A FROM clause

The following are optional clauses: WHERE

GROUP BY HAVING

The subquery (or subqueries) within a statement must be executed before the parent query that calls it, in order that the results of the subquery can be passed to the parent.

Question No: 197 – (Topic 2)

Which best describes an inline view?

  1. a schema object

  2. a sub query that can contain an ORDER BY clause

  3. another name for a view that contains group functions

  4. a sub query that is part of the FROM clause of another query

Answer: D Explanation:

a sub query that is part of the FROM clause of another query

Incorrect answer:

Ais not a schema object

Bsub query can contain GROUP BY clause as well. Cdoes not necessary contains group functions

Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 11-21

Question No: 198 – (Topic 2)

You want to display the date for the first Monday of the next month and issue the following command:

SQLgt;SELECT TO_CHAR(NEXT_DAY(LAST_DAY(SYSDATE),#39;MON#39;), #39;dd quot;is the first

Monday forquot;fmmonth rrrr#39;) FROM DUAL; What is the outcome?

  1. It executes successfully and returns the correct result.

  2. It executes successfully but does not return the correct result.

  3. It generates an error because TO_CHAR should be replaced with TO_DATE.

  4. It generates an error because rrrr should be replaced by rr in the format string.

  5. It generates an error because fm and double quotation marks should not be used in the format string.

Answer: A Explanation:

  • NEXT_DAY(date, #39;char#39;): Finds the date of the next specified day of the week (#39;char#39;) following date. The value of char may be a number representing a day or a character string.

  • LAST_DAY(date): Finds the date of the last day of the month that contains date

  • The second innermost function is evaluated next. TO_CHAR(#39;28-OCT-2009#39;, #39;fmMonth#39;) converts the given date based on the Month format mask and returns the character string October. The fm modifier trims trailing blank spaces from the name of the month.

    Question No: 199 – (Topic 2)

    View the Exhibit and examine the structure of the PRODUCTS tables.

    You want to generate a report that displays the average list price of product categories where the average list price is less than half the maximum in each category.

    Which query would give the correct output?

    A.

    SELECT prod_category,avg(prod_list_price) FROM products

    GROUP BY prod_category HAVING avg(prod_list_price) lt; ALL (SELECT max(prod_list_price)/2 FROM products

    GROUP BY prod_category);

    B.

    SELECT prod_category,avg(prod_list_price) FROM products

    GROUP BY prod_category

    HAVING avg(prod_list_price) gt; ANY (SELECT max(prod_list_price)/2 FROM products

    GROUP BY prod_category);

    C.

    SELECT prod_category,avg(prod_list_price) FROM products

    HAVING avg(prod_list_price) lt; ALL (SELECT max(prod_list_price)/2 FROM products

    GROUP BY prod_category);

    D.

    SELECT prod_category,avg(prod_list_price) FROM products

    GROUP BY prod_category

    HAVING avg(prod_list_price) gt; ANY (SELECT max(prod_list_price)/2 FROM products);

    Answer: A Explanation:

    Using the ANY Operator in Multiple-Row Subqueries

    The ANY operator (and its synonym, the SOME operator) compares a value to each value returned by a subquery.

    lt;ANY means less than the maximum.

    gt;ANY means more than the minimum.

    =ANY is equivalent to IN

    Using the ALL Operator in Multiple-Row Subqueries

    The ALL operator compares a value to every value returned by a subquery.

    gt;ALL means more than the maximum and

    lt;ALL means less than the minimum.

    The NOT operator can be used with IN, ANY, and ALL operators.

    Question No: 200 – (Topic 2)

    Examine the data in the ORD_ITEMS table:

    ORD_NO ITEM_NO QTY

    ———————- 1 111 10

    1 222 20

    1 333 30

    2 333 30

    2 444 40

    3 111 40

    Evaluate the following query:

    SQLgt;SELECT item_no, AVG(qty) FROM ord_items

    HAVING AVG(qty) gt; MIN(qty) * 2

    GROUP BY item_no;

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

    1. It gives an error because the HAVING clause should be specified after the GROUP BY

      clause.

    2. It gives an error because all the aggregate functions used in the HAVING clause must be specified in the SELECT list.

    3. It displays the item nos with their average quantity where the average quantity is more than double the minimum quantity of that item in the table.

    4. It displays the item nos with their average quantity where the average quantity is more than double the overall minimum quantity of all the items in the table.

    Answer: C

    100% Dumps4cert Free Download!
    Download Free Demo:1z0-051 Demo PDF
    100% Dumps4cert Free Guaranteed!
    1z0-051 Dumps

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