web analytics

100% Pass Guarantee PassLeader 1Z0-051 Dumps with VCE and PDF for Free (Question 101 – Question 120)

PassLeader now are offering 100% pass ensure 1Z0-051 dumps! All 1Z0-051 exam questions have been updated with correct answers, welcome to download the newest PassLeader 1Z0-051 VCE dumps and PDF dumps: http://www.passleader.com/1z0-051.html (303 Q&As)

BTW: Download PassLeader 1Z0-051 dumps from Google Drive for free: https://drive.google.com/open?id=0B-ob6L_QjGLpWXpBUEhyRnVXVlE

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.)
passleader-1Z0-051-dumps-1011

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
Explanation:
How Are Indexes Created?
You can create two types of indexes.
Unique index: The Oracle server automatically creates this index when you define a column in a table to have a PRIMARY KEY or a UNIQUE constraint. The name of the index is the name that is given to the constraint.
Nonunique index: This is an index that a user can create. For example, you can create the FOREIGN KEY column index for a join in a query to improve the speed of retrieval. Note: You can manually create a unique index, but it is recommended that you create a unique constraint, which implicitly creates a unique index.

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

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
Explanation:
A synonym is an alias for a table (or a view). Users can execute SQL statements against the synonym, and the database will map them into statements against the object to which the synonym points.
Private synonyms are schema objects. Either they must be in your own schema, or they must be qualified with the schema name. Public synonyms exist independently of a schema. A public synonym can be referred to by any user to whom permission has been granted to see it without the need to qualify it with a schema name.
Private synonyms must be a unique name within their schema. Public synonyms can have the same name as schema objects. When executing statements that address objects without a schema qualifier, Oracle will first look for the object in the local schema, and only if it cannot be found will it look for a public synonym.

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.
passleader-1Z0-051-dumps-1061
The query generates an error. What is the reason for the error?
passleader-1Z0-051-dumps-1062

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?
passleader-1Z0-051-dumps-1071

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:
passleader-1Z0-051-dumps-1081
What would be the outcome of the above statement?
passleader-1Z0-051-dumps-1082

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.)
passleader-1Z0-051-dumps-1101

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

QUESTION 111
The CUSTOMERS table has the following structure:
passleader-1Z0-051-dumps-1111
You need to write a query that does the following tasks:
1. Display the first name and tax amount of the customers. Tax is 5% of their credit limit.
2. Only those customers whose income level has a value should be considered.
3. Customers whose tax amount is null should not be considered.
Which statement accomplishes all the required tasks?

A.    SELECT cust_first_name, cust_credit_limit * .05 AS TAX_AMOUNT FROM customers
WHERE cust_income_level IS NOT NULL AND
tax_amount IS NOT NULL;
B.    SELECT cust_first_name, cust_credit_limit * .05 AS TAX_AMOUNT FROM customers
WHERE cust_income_level IS NOT NULL AND
cust_credit_limit IS NOT NULL;
C.    SELECT cust_first_name, cust_credit_limit * .05 AS TAX_AMOUNT FROM customers
WHERE cust_income_level <> NULL AND
tax_amount <> NULL;
D.    SELECT cust_first_name, cust_credit_limit * .05 AS TAX_AMOUNT FROM customers
WHERE (cust_income_level,tax_amount) IS NOT NULL;

Answer: B

QUESTION 112
The PART_CODE column in the SPARES table contains the following list of values:
passleader-1Z0-051-dumps-1112
Which statement is true regarding the outcome of the above query?

A.    It produces an error.
B.    It displays all values.
C.    It displays only the values A%_WQ123 and AB_WQ123.
D.    It displays only the values A%_WQ123 and A%BWQ123.
E.    It displays only the values A%BWQ123 and AB_WQ123.

Answer: D
Explanation:
Combining Wildcard Characters
The % and _ symbols can be used in any combination with literal characters. The example in the slide displays the names of all employees whose last names have the letter “o” as the second character.
ESCAPE Identifier
When you need to have an exact match for the actual % and _ characters, use the ESCAPE identifier. This option specifies what the escape character is. If you want to search for strings that contain SA_, you can use the following SQL statement:
SELECT employee_id, last_name, job_id
FROM employees WHERE job_id LIKE ‘%SA\_%’ ESCAPE ‘\’;

QUESTION 113
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 ‘Software/Other ‘ category with minimum prices as either $2000 or $4000 and no unit of measure. You issue the following query:
passleader-1Z0-051-dumps-1131
Which statement is true regarding the above query?
passleader-1Z0-051-dumps-1132

A.    It executes successfully but returns no result.
B.    It executes successfully and returns the required result.
C.    It generates an error because the condition specified for PROD_UNIT_OF_MEASURE is not valid.
D.    It generates an error because the condition specified for the PROD_CATEGORY column is not valid.

Answer: A

QUESTION 114
View the Exhibit and examine the structure of CUSTOMERS table. Evaluate the following query:
passleader-1Z0-051-dumps-1141
Which statement is true regarding the above query?
passleader-1Z0-051-dumps-1142

A.    It executes successfully.
B.    It produces an error because the condition on the CUST_CITY column is not valid.
C.    It produces an error because the condition on the CUST_FIRST_NAME column is not valid.
D.    It produces an error because conditions on the CUST_CREDIT_LIMIT column are not valid.

Answer: A

QUESTION 115
View the Exhibit and examine the structure of the PROMOTIONS table. You need to generate a report of all promos from the PROMOTIONS table based on the following conditions:
1. The promo name should not begin with ‘T’ or ‘N’.
2. The promo should cost more than $20000.
3. The promo should have ended after 1st January 2001.
Which WHERE clause would give the required result?
passleader-1Z0-051-dumps-1151

A.    WHERE promo_name NOT LIKE ‘T%’ OR promo_name NOT LIKE ‘N%’ AND promo_cost > 20000
AND promo_end_date > ‘1-JAN-01’
B.    WHERE (promo_name NOT LIKE ‘T%’ AND promo_name NOT LIKE ‘N%’)OR promo_cost > 20000
OR promo_end_date > ‘1-JAN-01’
C.    WHERE promo_name NOT LIKE ‘T%’ AND promo_name NOT LIKE ‘N%’ AND promo_cost > 20000
AND promo_end_date > ‘1-JAN-01’
D.    WHERE (promo_name NOT LIKE ‘%T%’ OR promo_name NOT LIKE ‘%N%’) AND(promo_cost > 20000
AND promo_end_date > ‘1-JAN-01’)

Answer: C

QUESTION 116
View the E xhibit and examine the structure of the CUSTOMERS table. You want to generate a report showing the last names and credit limits of all customers whose last names start with A, B, or C, and credit limit is below 10, 000. Evaluate the following two queries:
passleader-1Z0-051-dumps-1161
Which statement is true regarding the execution of the above queries?
passleader-1Z0-051-dumps-1162

A.    Only the first query gives the correct result.
B.    Only the second query gives the correct result.
C.    Both execute successfully and give the same result.
D.    Both execute successfully but do not give the required result.

Answer: A

QUESTION 117
View the Exhibit and examine the structure of the PRODUCTS table. You want to display only those product names with their list prices where the list price is at least double the minimum price. The report should start with the product name having the maximum list price satisfying this condition. Evaluate the following SQL statement:
SQL>SELECT prod_name,prod_list_price
FROM products
WHERE prod_list_price >= 2 * prod_min_price
Which ORDER BY clauses can be added to the above SQL statement to get the correct output? (Choose all that apply.)
passleader-1Z0-051-dumps-1171

A.    ORDER BY prod_list_price DESC, prod_name;
B.    ORDER BY (2*prod_min_price)DESC, prod_name;
C.    ORDER BY prod_name, (2*prod_min_price)DESC;
D.    ORDER BY prod_name DESC, prod_list_price DESC;
E.    ORDER BY prod_list_price DESC, prod_name DESC;

Answer: AE
Explanation:
Using the ORDER BY Clause
The order of rows that are returned in a query result is undefined. The ORDER BY clause can be used to sort the rows. However, if you use the ORDER BY clause, it must be the last clause of the SQL statement. Further, you can specify an expression, an alias, or a column position as the sort condition.
Syntax
SELECT expr
FROM table
[WHERE condition(s)]
[ORDER BY {column, expr, numeric_position} [ASC|DESC]];
In the syntax:
ORDER BY specifies the order in which the retrieved rows are displayed
ASC orders the rows in ascending order (This is the default order.)
DESC orders the rows in descending order
If the ORDER BY clause is not used, the sort order is undefined, and the Oracle server may not fetch rows in the same order for the same query twice. Use the ORDER BY clause to display the rows in a specific order.
Note: Use the keywords NULLS FIRST or NULLS LAST to specify whether returned rows containing null values should appear first or last in the ordering sequence.

QUESTION 118
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?
passleader-1Z0-051-dumps-1181

A.    SELECT cust_city, cust_income_level, cust_last_name
FROM customers
ORDER BY cust_city desc, cust_income_level DESC ;
B.    SELECT cust_city, cust_income_level, cust_last_name
FROM customers
ORDER BY cust_income_level desc, cust_city DESC;
C.    SELECT cust_city, cust_income_level, cust_last_name
FROM customers
ORDER BY (cust_city, cust_income_level) DESC;
D.    SELECT cust_city, cust_income_level, cust_last_name
FROM customers
ORDER BY cust_city, cust_income_level DESC;

Answer: A

QUESTION 119
View the Exhibit and examine the data in the PROMO_CATEGORY and PROMO_COST columns of the PROMOTIONS table. Evaluate the following two queries:
SQL>SELECT DISTINCT promo_category to_char(promo_cost)”code”
FROM promotions
ORDER BY code;
SQL>SELECT DISTINCT promo_category promo_cost “code”
FROM promotions
ORDER BY 1;
Which statement is true regarding the execution of the above queries?
passleader-1Z0-051-dumps-1191

A.    Only the first query executes successfully.
B.    Only the second query executes successfully.
C.    Both queries execute successfully but give different results.
D.    Both queries execute successfully and give the same result.

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

QUESTION 120
View the Exhibit and examine the data in the COSTS table. 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:
passleader-1Z0-051-dumps-1201
Which statement is true regarding the above query?
passleader-1Z0-051-dumps-1202

A.    It executes and produces the required result.
B.    It produces an error because an expression cannot be used in the ORDER BY clause.
C.    It produces an error because the DESC option cannot be used with an expression in the ORDER BY clause.
D.    It produces an error because the expression in the ORDER BY clause should also be specified in the SELECT clause.

Answer: A


PassLeader now are offering 100% pass ensure 1Z0-051 dumps! All 1Z0-051 exam questions have been updated with correct answers, welcome to download the newest PassLeader 1Z0-051 VCE dumps and PDF dumps: http://www.passleader.com/1z0-051.html (303 Q&As)

BTW: Download PassLeader 1Z0-051 dumps from Google Drive for free: https://drive.google.com/open?id=0B-ob6L_QjGLpWXpBUEhyRnVXVlE