D. COUNT(DISTINCT inv_amt) returns the number of rows excluding rows containing duplicates and NULL values in the INV_AMT column
E. A SELECT statement using the COUNT function with a DISTINCT keyword cannot have a WHERE clause
Answer: B, D 20.
Which two statements are true regarding single row functions? (Choose two.) A. They accept only a single argument
B. They can be nested only to two levels
C. Arguments can only be column values or constants
D. They always return a single result row for every row of a queried table E. They can return a data type value different from the one that is referenced
Answer: D, E 21.
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:
16
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 22.
View the Exhibit and examine the structure of the CUSTOMERS table Which statement would display the highest credit limit available in each income level
in each city in the CUSTOMERS table?
17
A. SELECT cust_city, cust_income_level, MAX(cust_credit_limit) FROM customers
GROUP BY cust_city, cust_income_level, cust_credit_limit; B. SELECT cust_city, cust_income_level, MAX(cust_credit_limit)
FROM customers
GROUP BY cust_city, cust_income_level;
C. SELECT cust_city, cust_income_level, MAX(cust_credit_limit) FROM customers
GROUP BY cust_credit_limit, cust_income_level, cust_city; D. SELECT cust_city, cust_income_level, MAX(cust_credit_limit)
FROM customers
GROUP BY cust_city, cust_income_level, MAX(cust_credit_limit); Answer: B 23.
Which two statements are true regarding subqueries? (Choose two.) A. A subquery can retrieve zero or more rows
B. Only two subqueries can be placed atone level C. A subquery can be used only in SQL query statements
D. A subquery can appear on either side of a comparison operator E. There is no limit on the number of subquery levels in the WHERE clause of a SELECT statement
Answer: A, D 24.
18
View the Exhibit and examine the structure of the PROMOTIONS table. Evaluate the following SQL statement:
The above query generates an error on execution.
Which clause in the above SQL statement causes the error? A. WHERE B. SELECT C. GROUP BY D. ORDER BY Answer: C 25.
You need to create a table for a banking application One of the columns in the table has the following requirements:
1) You want a column in the table to store the duration of the credit period 2) The data in the column should be stored in a format such that it can be easily added
and subtracted with DATE data type without using conversion functions 3) The maximum period of the credit provision in the application is 30days 4) The interest has to be calculated for the number of days an individual has taken a
19
credit for.
Which data type would you use for such a column in the table? A. DATE
B. NUMBER C. TIMESTAMP
D. INTERVAL DAY TO SECOND E. INTERVAL YEAR TO MONTH Answer: D 26.
View the Exhibit to examine the description for the SALES table Which views can have all DML operations performed on it? (Choose all that apply.)
A. CREATE VIEW v3 AS SELECT * FROM SALES WHERE cust_id = 2034 WITH CHECK OPTION; B. CREATE VIEW vl AS SELECT * FROM SALES
WHERE time_id <= SYSDATE - 2*365 WITH CHECK OPTION; C. CREATE VIEW v2
20
最新Oracle_OCP11g考试题库
