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

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

View the Exhibits and examine the structures of the PRODUCTS SALES and CUSTOMERS tables.

Latesttests 2018 PDF and VCE

You need to generate a report that gives details of the customer#39;s last name, name of the product, and the quantity sold for all customers in Tokyo#39;. Which two queries give the required result? (Choose two.)

A.

SELECT c.cust_last_name,p.prod_name, s.quantity_sold FROM sales s JOIN products p

USING(prod_id) JOIN customers c USING(cust_id)

WHERE c.cust_city=#39;Tokyo#39;;

B.

SELECT c.cust_last_name, p.prod_name, s.quantity_sold FROM products p JOIN sales s JOIN customers c ON(p.prod_id=s.prod_id)

ON(s.cust_id=c.cust_id) WHERE c.cust_city=#39;Tokyo#39;; C.

SELECT c.cust_last_name, p.prod_name, s.quantity_sold FROM products p JOIN sales s

ON(p.prod_id=s.prod_id) JOIN customers c ON(s.cust_id=c.cust_id) AND c.cust_city=#39;Tokyo#39;; D.

SELECT c.cust_id,c.cust_last_name,p.prod_id, p.prod_name, s.quantity_sold FROM products p JOIN sales s

USING(prod_id) JOIN customers c USING(cust_id)

WHERE c.cust_city=#39;Tokyo#39;;

Answer: A,C

Question No: 102 – (Topic 1)

For which action can you use the TO_DATE function?

  1. Convert any date literal to a date

  2. Convert any numeric literal to a date

  3. Convert any character literal to a date

  4. Convert any date to a character literal

  5. Format ’10-JAN-99′ to ‘January 10 1999’

Answer: C

Question No: 103 – (Topic 1)

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

Latesttests 2018 PDF and VCE

You need to display all promo categories that do not have #39;discount#39; in their subcategory. Which two SQL statements give the required result? (Choose two.)

A.

SELECT promo_category FROM promotions MINUS

SELECT promo_category FROM promotions

WHERE promo_subcategory = #39;discount#39;;

B.

SELECT promo_category FROM promotions INTERSECT

SELECT promo_category FROM promotions

WHERE promo_subcategory = #39;discount#39;;

C.

SELECT promo_category FROM promotions MINUS

SELECT promo_category FROM promotions

WHERE promo_subcategory lt;gt; #39;discount#39;;

D.

SELECT promo_category

FROM promotions INTERSECT

SELECT promo_category FROM promotions

WHERE promo_subcategory lt;gt; #39;discount#39;;

Answer: A,D

Question No: 104 – (Topic 1)

Which statement is true regarding the COALESCE function?

  1. It can have a maximum of five expressions in a list.

  2. It returns the highest NOT NULL value in the list for all rows.

  3. It requires that all expressions in the list must be of the same data type.

  4. It requires that at least one of the expressions in the list must have a NOT NULL value.

Answer: C Explanation:

The COALESCE Function

The COALESCE function returns the first nonnull value from its parameter list. If all its parameters are null, then null is returned.

The COALESCE function takes two mandatory parameters and any number of optional parameters. The syntax is COALESCE(expr1, expr2,…,exprn), where expr1 is returned if it is not null, else expr2 if it is not null, and so on. COALESCE is a general form of the NVL function, as the following two equations illustrate:

COALESCE(expr1,expr2) = NVL(expr1,expr2) COALESCE(expr1,expr2,expr3) = NVL(expr1,NVL(expr2,expr3))

The data type COALESCE returns if a not null value is found is the same as that of the first not null parameter.

To avoid an “ORA-00932: inconsistent data types” error, all not null parameters must have data types compatible with the first not null parameter.

Question No: 105 – (Topic 1)

Examine these statements:

CREATE ROLE registrar;

GRANT UPDATE ON student_grades TO registrar; GRANT registrar to user1, user2, user3;

What does this set of SQL statements do?

  1. The set of statements contains an error and does not work.

  2. It creates a role called REGISTRAR, adds the MODIFY privilege on the STUDENT_GRADES object to the role, and gives the REGISTRAR role to three users.

  3. It creates a role called REGISTRAR, adds the UPDATE privilege on the STUDENT_GRADES object to the role, and gives the REGISTRAR role to three users.

  4. It creates a role called REGISTRAR, adds the UPDATE privilege on the STUDENT_GRADES object to the role, and creates three users with the role.

  5. It creates a role called REGISTRAR, adds the UPDATE privilege on three users, and gives the REGISTRAR role to the STUDENT_GRADES object.

  6. It creates a role called STUDENT_GRADES, adds the UPDATE privilege on three users, and gives the UPDATE role to the registrar.

Answer: C

Explanation: the statement will create a role call REGISTRAR, grant UPDATE on student_grades to registrar, grant the role to user1,user2 and user3.

Incorrect answer:

Athe statement does not contain error Bthere is no MODIFY privilege

Dstatement does not create 3 users with the role Eprivilege is grant to role then grant to user Fprivilege is grant to role then grant to user

Question No: 106 – (Topic 1)

View the Exhibit and examine the structure of ORDERS and CUSTOMERS tables. There is only one customer with the cus_last_name column having value Roberts. Which INSERT statement should be used to add a row into the ORDERS table for the customer whose CUST_LAST_NAME is Roberts and CREDIT_LIMIT is 600?

Latesttests 2018 PDF and VCE

  1. INSERT INTO orders VALUES (l.#39;10-mar-2007\ #39;direct#39;. (SELECT customerid FROM customers

    WHERE cust_last_iiame=#39;Roberts#39; AND credit_limit=600). 1000);

  2. INSERT INTO orders (order_id.order_date.order_mode. (SELECT customer id FROM customers

    WHERE cust_last_iiame=#39;Roberts#39; AND redit_limit=600).order_total)

    VALUES(L#39;10-mar-2007#39;. #39;direct#39;, amp;amp;customer_id, 1000):

  3. INSERT INTO(SELECT o.order_id. o.order_date.o.order_modex.customer_id. o.ordertotal

    FROM orders o. customers c

    WHERE o.customer_id = c.customerid

    AND c.cust_la$t_name-RoberTs#39; ANDc.credit_liinit=600) VALUES (L#39;10-mar-2007\ #39;direct#39;.( SELECT customer_id FROM customers

    WHERE cust_last_iiame=#39;Roberts#39; AND credit_limit=600). 1000);

  4. INSERT INTO orders (order_id.order_date.order_mode. (SELECT customer_id

FROM customers

WHERE cust_last_iiame=#39;Roberts#39; AND credit_limit=600).order_total)

VALUES(l.#39;10-mar-2007\ #39;direct#39;. amp;customer_id. 1000):

Answer: A

Question No: 107 – (Topic 1)

You work as a database administrator at ABC.com. You study the exhibit carefully and examine the structure of CUSTOMRS AND SALES tables.

Latesttests 2018 PDF and VCE

Latesttests 2018 PDF and VCE

Evaluate the following SQL statement: Exhibit:

Which statement is true regarding the execution of the above UPDATE statement?

  1. It would execute and restrict modifications to only the column specified in the SELECT statement

  2. It would not execute because two tables cannot be used in a single UPDATE statement

  3. It would not execute because a sub query cannot be used in the WHERE clause of an UPDATE statement

  4. It would not execute because the SELECT statement cannot be used in place of the table name

Answer: A

Question No: 108 – (Topic 1)

Which statement is true regarding the INTERSECT operator?

  1. It ignores NULL values

  2. The number of columns and data types must be identical for all SELECT statements in the query

  3. The names of columns in all SELECT statements must be identical

  4. Reversing the order of the intersected tables the result

Answer: B Explanation:

INTERSECT Returns only the rows that occur in both queries’ result sets, sorting them and removing duplicates.

The columns in the queries that make up a compound query can have different names, but the output result set will use the names of the columns in the first query.

Question No: 109 – (Topic 1)

Examine the structure and data in the PRIC E_LIST table: Name Null? Type

—— ——- ———- PROD_D NOT NULL NUMBER(3) PROD_PRICE VARCHAR2(10) PROD_ID PROD PRICE

—— ————

100 $234.55

101 $6,509.75

102 $1,234

in the same format as the PROD_PRICE. Which SQL statement would give the required result?

  1. SELECT TO_CHAR(prod_price* .25.#39;$99.999.99#39;) FROM PRICEJLIST:

  2. SELECT TO_CHAR(TO_NUMBER(prod_price)* .25.#39;$99.999.00#39;) FROM PRICE_LIST;

  3. SELECT TO_CRAR(TO_NUMBER(prod_price.#39;S99.999.99#39;)* .25.#39;$99.999.00#39;) FROM PRICE_LIST:

D. SELECT TO_NUMBER(TO_NUMBER(prod_price.,$99.999.99#39;)* .25/$99.999.00#39;) FROM PRICE_LIST:

Answer: C

Question No: 110 – (Topic 1)

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.

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

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