QUESTION 101
View the Exhibit and examine the structure of ORD and ORD_ITEMS tables.
The ORD_NO column is PRIMARY KEY in the ORD table and the ORD_NO and ITEM_NO columns are composite PRIMARY KEY in the ORD_ITEMS table.
Which two CREATE INDEX statements are valid? (Choose two.)

A.    CREATE INDEX ord_idx1
ON ord(ord_no);
B.    CREATE INDEX ord_idx2
ON ord_items(ord_no);
C.    CREATE INDEX ord_idx3
ON ord_items(item_no);
D.    CREATE INDEX ord_idx4
ON ord,ord_items(ord_no, ord_date,qty);

Answer: BC

QUESTION 102
Which two statements are true regarding indexes? (Choose two.)

A.    They can be created on tables and clusters.
B.    They can be created on tables and simple views.
C.    You can create only one index by using the same columns.
D.    You can create more than one index by using the same columns if you specify distinctly different
combinations of the columns.

Answer: AD

QUESTION 103
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;

A.    CREATE SYNONYM ord FOR orders; This command is issued by OE.
B.    CREATE PUBLIC SYNONYM ord FOR orders; This command is issued by OE.
C.    CREATE SYNONYM ord FOR oe.orders; This command is issued by the database administrator.
D.    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):

QUESTION 104
SLS is a private synonym for the SH.SALES table.
The user SH issues the following command:
DROP SYNONYM sls;
Which statement is true regarding the above SQL statement?

A.    Only the synonym would be dropped.
B.    The synonym would be dropped and the corresponding table would become invalid.
C.    The synonym would be dropped and the packages referring to the synonym would be dropped.
D.    The synonym would be dropped and any PUBLIC synonym with the same name becomes invalid.

Answer: A

QUESTION 105
Which statement is true regarding synonyms?

A.    Synonyms can be created only for a table.
B.    Synonyms are used to reference only those tables that are owned by another user.
C.    A public synonym and a private synonym can exist with the same name for the same table.
D.    The DROP SYNONYM statement removes the synonym, and the table on which the synonym has
been created becomes invalid.

Answer: C

QUESTION 106
View the Exhibit and examine the structure of the PRODUCTS table.
Using the PRODUCTS table, you issue the following query to generate the names, current list price, and discounted list price for all those products whose list price falls below $10 after a discount of 25% is applied on it.
SQL>SELECT prod_name, prod_list_price,
prod_list_price – (prod_list_price * .25) “DISCOUNTED_PRICE”
FROM products
WHERE discounted_price < 10;
The query generates an error.
What is the reason for the error?

A.    The parenthesis should be added to enclose the entire expression.
B.    The double quotation marks should be removed from the column alias.
C.    The column alias should be replaced with the expression in the WHERE clause.
D.    The column alias should be put in uppercase and enclosed with in double quotation marks in the
WHERE clause.

Answer: C
Explanation:
You cannot use column alias in the WHERE clause.

QUESTION 107
View the Exhibit and examine the data in the PROMOTIONS table.
PROMO_BEGIN_DATE is stored in the default date format, dd-mon-rr.
You need to produce a report that provides the name, cost, and start date of all promos in the POST category that were launched before January 1, 2000.
Which SQL statement would you use?

A.    SELECT promo_name, promo_cost, promo_begin_date
FROM promotions
WHERE promo_category = ‘post’ AND promo_begin_date < ’01-01-00′;
B.    SELECT promo_name, promo_cost, promo_begin_date
FROM promotions
WHERE promo_cost LIKE ‘post%’ AND promo_begin_date < ’01-01-2000′;
C.    SELECT promo_name, promo_cost, promo_begin_date
FROM promotions
WHERE promo_category LIKE ‘P%’ AND promo_begin_date < ‘1-JANUARY-00’;
D.    SELECT promo_name, promo_cost, promo_begin_date
FROM promotions
WHERE promo_category LIKE ‘%post%’ AND promo_begin_date < ‘1-JAN-00’;

Answer: D

QUESTION 108
View the Exhibit and examine the structure of the CUSTOMERS table.
Evaluate the query statement:
SQL> SELECT cust_last_name, cust_city, cust_credit_limit
FROM customers
WHERE cust_last_name BETWEEN ‘A’ AND ‘C’ AND cust_credit_limit BETWEEN
1000 AND 3000;
What would be the outcome of the above statement?

A.    It executes successfully.
B.    It produces an error because the condition on CUST_LAST_NAME is invalid.
C.    It executes successfully only if the CUST_CREDIT_LIMIT column does not contain any null values.
D.    It produces an error because the AND operator cannot be used to combine multiple BETWEEN clauses.

Answer: A

QUESTION 109
Evaluate the following two queries:
SQL> SELECT cust_last_name, cust_city
FROM customers
WHERE cust_credit_limit IN (1000, 2000, 3000);
SQL> SELECT cust_last_name, cust_city
FROM customers
WHERE cust_credit_limit = 1000 OR cust_credit_limit = 2000 OR
cust_credit_limit = 3000;
Which statement is true regarding the above two queries?

A.    Performance would improve in query 2.
B.    Performance would degrade in query 2.
C.    There would be no change in performance.
D.    Performance would improve in query 2 only if there are null values in the CUST_CREDIT_LIMIT column.

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 110
View the Exhibit and examine the structure of the PROMOTIONS table.
Using the PROMOTIONS table, you need to find out the names and cost of all the promos done on ‘TV’ and ‘internet’ that ended in the time interval 15th March ’00 to 15th October ’00.
Which two queries would give the required result? (Choose two.)

A.    SELECT promo_name, promo_cost
FROM promotions
WHERE promo_category IN (‘TV’, ‘internet’) AND
promo_end_date BETWEEN ’15-MAR-00′ AND ’15-OCT-00′;
B.    SELECT promo_name, promo_cost
FROM promotions
WHERE promo_category = ‘TV’ OR promo_category =’internet’ AND promo_end_date >=’15-MAR-00′
OR promo_end_date <=’15-OCT-00′;
C.    SELECT promo_name, promo_cost
FROM promotions
WHERE (promo_category BETWEEN ‘TV’ AND ‘internet’) AND
(promo_end_date IN (’15-MAR-00′,’15-OCT-00′));
D.    SELECT promo_name, promo_cost
FROM promotions
WHERE (promo_category = ‘TV’ OR promo_category =’internet’) AND (promo_end_date >=’15-MAR-00′
AND promo_end_date <=’15-OCT-00′);

Answer: AD

If you want to pass Oracle 1Z0-051 exam successfully, donot missing to read latest lead2pass Oracle 1Z0-051 exam questions.
If you can master all lead2pass questions you will able to pass 100% guaranteed.

http://www.lead2pass.com/1z0-051.html