OCP-1Z0-051-V9.02-57/64/67题

57. The CUSTOMERS table has the followingstructure:

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  doesthe following tasks:

1. Display the first name and tax amountof the customers. Tax is 5% of their credit limit. 

2. Only those customers whose income levelhas a value should be considered.

3. Customers whose tax amount is null should notbe considered.

Which statement accomplishes all therequired tasks?

A. SELECT cust_first_name,cust_credit_limit * .05 AS TAX_AMOUNT    

FROM customers    

WHERE cust_income_level IS NOT NULLAND          

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 NULLAND          

cust_credit_limit IS NOT NULL;

C. SELECT cust_first_name,cust_credit_limit * .05 AS TAX_AMOUNT    

FROM customers    

WHERE cust_income_level <> NULLAND          

tax_amount <> NULL;

D. SELECT cust_first_name,cust_credit_limit * .05 AS TAX_AMOUNT    

FROM customers    

WHERE (cust_income_level,tax_amount) ISNOT NULL;

Answer: B

答案解析：

A,WHERE子句不能跟别名

sh@TEST0924> SELECT cust_first_name, cust_credit_limit* .05 AS TAX_AMOUNT FROM customers

  2  WHERE cust_income_level IS NOT NULL ANDTAX_AMOUNT IS NOT NULL

  3  /

WHERE cust_income_level IS NOT NULL AND TAX_AMOUNT IS NOTNULL

                                      *

ERROR at line 2:

ORA-00904: "TAX_AMOUNT": invalid identifier

 

sh@TEST0924> SELECT cust_first_name, cust_credit_limit* .05 AS TAX_AMOUNT FROM customers

  2  WHERE cust_income_level IS NOT NULL ANDcust_credit_limit * .05 IS NOT NULL；

 

 

CUST_FIRST_NAME      TAX_AMOUNT

-------------------- ----------

Abigail                     75

Abigail                    350

...

B正确

C错误，一是where子句不能用别名，二是非null不能用<>来表达

sh@TEST0924> SELECT cust_first_name, cust_credit_limit* .05 AS TAX_AMOUNT FROM customers

  2  WHERE cust_income_level <> NULL ANDtax_amount <> NULL;

WHERE cust_income_level <> NULL AND tax_amount<> NULL

                                   *

ERROR at line 2:

ORA-00904: "TAX_AMOUNT": invalid identifier

 

 

sh@TEST0924> SELECT cust_first_name, cust_credit_limit* .05 AS TAX_AMOUNT FROM customers

  2  WHERE cust_income_level <> NULL ANDcust_credit_limit<> NULL;

 

no rows selected

D错误，语法错误

64. View the E xhibit and examine the datain the PROMO_CATEGORY and PROMO_COST columns of

the PROMOTIONS table.

Evaluate the following two queries:

SQL>SELECT DISTINCTpromo_category   to_char(promo_cost)"code"    

FROM promotions    

ORDER BY code;

SQL>SELECT DISTINCTpromo_category  promo_cost "code"    

FROM promotions    

ORDER BY 1;

Which statement is true regarding theexecution of the above queries? 

A. Only the first query executes successfully.

B. Only the second query executessuccessfully.

C. Both queries execute successfully butgive different results.

D. Both queries execute successfully andgive the same result.

Answer: B

答案解析：

    order by 可以指定表达式、别名或列位置作为排序条件，别名要完全匹配。列别名并没有完全匹配，所以报错。

67. Which two statements are true regardingthe ORDER BY clause? (Choose two.)

A. It is executed first in the queryexecution.

B. It must be the last clause in theSELECT statement. 

C. It cannot be used in a SELECT statementcontainin g a HAVING clause.

D. You cannot specify a column name followedby an expression in this clause. order by可以用表达式

E. You can specify a combination ofnumeric positions and column names in this clause. 您可以指定数字的位置和在本节中列名的组合

Answer: BE

答案解析：

A错，使用ORDER BY 子句，该子句必须是SQL 语句的最后一个子句。

B对，

C错，放在最后就可以了。

D错，可以指定表达式、别名或列位置或组合作为排序条件

E对，可以指定表达式、别名或列位置或组合作为排序条件

总结：

order by子句：

    出现的位置：始终位于最后；

    后面可跟：表达式、别名（前后要完全一致，包括引号）或列位置或组合

where条件表达式：

    where子句不能跟别名，但是可以跟表达式