QUESTION 111
The CUSTOMERS table has the following structure:
Name Null Type
————– ———– ———– ———-
CUST_ID NOT NULL NUMBER
CUST_FIRST_NAME NOT NULL VARCHAR2(20)
CUST_LAST_NAME NOT NULL VARCHAR2(30)
CUST_INCOME_LEVEL VARCHAR2(30)
CUST_CREDIT_LIMIT NUMBER
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:
PART_CODE
———-
A%_WQ123
A%BWQ123
AB_WQ123
Evaluate the following query:
SQL> SELECT part_code
FROM spares
WHERE part_code LIKE ‘%\%_WQ12%’ ESCAPE ‘\’;
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
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:
SQL>SELECT prod_name, prod_category, prod_min_price
FROM products
WHERE prod_category LIKE ‘%Other%’ AND (prod_min_price = 2000 OR
prod_min_price = 4000) AND prod_unit_of_measure <> ”;
Which statement is true regarding the above query?
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:
SQL>SELECT cust_id, cust_city
FROM customers
WHERE cust_first_name NOT LIKE ‘A_%g_%’ AND
cust_credit_limit BETWEEN 5000 AND 15000 AND
cust_credit_limit NOT IN (7000, 11000) AND
cust_city NOT BETWEEN ‘A’ AND ‘B’;
Which statement is true regarding the above query?
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?
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:
SQL> SELECT cust_last_name, cust_credit_limit FROM customers
WHERE (UPPER(cust_last_name) LIKE ‘A%’ OR
UPPER(cust_last_name) LIKE ‘B%’ OR UPPER(cust_last_name) LIKE ‘C%’)
AND cust_credit_limit < 10000;
SQL>SELECT cust_last_name, cust_credit_limit FROM customers
WHERE UPPER(cust_last_name) BETWEEN ‘A’ AND ‘C’
AND cust_credit_limit < 10000;
Which statement is true regarding the execution of the above queries?
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.)
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
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?
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?
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
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:
SQL>SELECT prod_id
FROM costs
WHERE unit_price >= unit_cost * 1.25
ORDER BY unit_cost * 0.25 DESC;
Which statement is true regarding the above query?
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
If you want to pass Oracle 1Z0-051 exam successfully, donot missing to read latest lead2pass Oracle 1Z0-051 practice tests.
If you can master all lead2pass questions you will able to pass 100% guaranteed.