1Z0-051(V9.02) 解析6-10

6.Examine the structure of the SHIPMENTS table:

name           Null       Type

PO_ID          NOT NULL    NUMBER(3)

PO_DATE        NOT NULL    DATE

SHIPMENT_DATENOT NULL    DATE

SHIPMENT_MODE             VARCHAR2(30)

SHIPMENT_COST             NUMBER(8,2)

You wantto generate a report that displays the PO_ID and the penalty amount to be paidif the

SHIPMENT_DATEis later than one month from the PO_DATE. The penalty is $20 per day.

Evaluatethe following two queries:

SQL>SELECT po_id, CASE

WHENMONTHS_BETWEEN (shipment_date,po_date)>1 THEN

TO_CHAR((shipment_date- po_date) * 20) ELSE 'No Penalty' END PENALTY

FROMshipments;

SQL>SELECTpo_id, DECODE

(MONTHS_BETWEEN(po_date,shipment_date)>1,

TO_CHAR((shipment_date- po_date) * 20), 'No Penalty') PENALTY  

FROMshipments;

Whichstatement is true regarding the above commands?

A. Bothexecute successfully and give correct results.

B. Onlythe first query executes successfully but gives a wrong result.

C. Onlythe first query executes successfully and gives the  correct result.

D. Onlythe second query executes successfully but gives a wrong result.

E. Onlythe second query executes successfully and gives the correct result.

Answer: C

 

原题翻译：

检查SHIPMENTS表结构如下：
Name               Null             Type
PO_ID              NOT NULL       NUMBER(3)
PO_DATE            NOT NULL       DATE
SHIPMENT_DATE      NOT NULL       DATE
SHIPMENT_MODE                     VARCHAR2(30)
SHIPMENT_COST                     NUMBER(8,2)
要获取一个报表，报表内容显示PO_ID和支付的罚款总金额，SHIPMENT_DATE与PO_DATE进行比较，SHIPMENT_DATE如果比PO_DATE晚一个月，则每天罚款$20。
下面有两条查询语句：
上面的命令哪句话是正确的？
A.两个查询都成功，并且结果都正确。
B.只有第一个查询成功，但是结果是错误的。
C.只有第一个查询成功，并且结果是正确的。
D.只有第二个查询成功，但是结果是错误的。
E.只有第二个查询成功，并且结果是正确的。

 

 

相关理论：

DECODE 与CASE WHEN的比较

1. DECODE 只能用做相等判断,但是可以配合sign函数进行大于，小于，等于的判断

 CASE when可用于=,>=,<,<=,<>,is null,is not null等的判断;
2. DECODE 使用其来比较简洁，CASE 虽然复杂但更为灵活;
3.在decode中，null和null是相等的，但在case when中，只能用is null来判断

selectcase

         when3 >2then

          '>'

         else

          '<='

       end ren

  from dual;

selectdecode(sign(4 -2), 1,'>', '<=')from dual;

 

或

selectdecode(sign(4 -2), 1,'>',-1,'<',0,'=')from dual;

 

实验：

CREATETABLE SHIPMENTS

(

PO_ID NUMBER(3)NOTNULL,

PO_DATE DATENOTNULL,

SHIPMENT_DATE DATE NOTNULL,

SHIPMENT_MODE VARCHAR2(30),

SHIPMENT_COST NUMBER(8,2)

);

 

SQL> SELECT po_id, CASE

  2  WHEN MONTHS_BETWEEN (shipment_date,po_date)>1 THEN

  3  TO_CHAR((shipment_date - po_date) * 20) ELSE 'No Penalty' END PENALTY

  4  FROM shipments;

PO_ID PENALTY

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

--以上可以执行

 

SQL> SELECT po_id, DECODE

  2  (MONTHS_BETWEEN (po_date,shipment_date)>1,

  3  TO_CHAR((shipment_date - po_date) * 20), 'No Penalty') PENALTY

  4  FROM shipments;

SELECT po_id, DECODE

(MONTHS_BETWEEN (po_date,shipment_date)>1,

TO_CHAR((shipment_date - po_date) * 20), 'No Penalty') PENALTY

FROM shipments

ORA-00907: missing right parenthesis

--以上语法有误,修改后可执行，

--注：sign()函数根据参数1的值是0、正数还是负数，分别返回0、1、-1

SQL> SELECT po_id,

  2         DECODE(SIGN(MONTHS_BETWEEN(shipment_date, po_date) - 1),

  3                1,

  4                TO_CHAR((shipment_date - po_date) * 20),

  5                -1,

  6                'No Penalty') PENALTY

  7    FROM shipments;

PO_ID PENALTY

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

 

 

7. Whichtwo statements are true regarding the USING and ON clauses in table joins?(Choose two.)

A. BothUSING and ON clauses can be used for equijoins and nonequijoins.

B. Amaximum of one pair of columns can be joined between two tables using the ONclause.

C. The ONclause can be used to join tables on columns that have different names butcompatible data types.

D. TheWHERE clause can be used to apply additional conditions in SELECT statementscontaining the ON or the USING clause.

Answer:CD

 

题目翻译

关于多表连接的USING和ON子句，下面哪两个选项是正确的？（选择两个）
A. USING和ON子句都可以用于等值连接和非等值连接。
B. 使用ON子句最大只能使用两个列连接两个表。
C. ON子句关联表的列名可以不同，但是数据类型要兼容。
D. 在包含ON或USING子句的SELECT语句中，WHERE子句可以做为补充的条件。

 

题目解析

A选项，不正确，USING子句只能用于等值连接, ON子句可用于等值和非等值连接，
B选项，不正确，ON子句可以连接多个列。
C选项，正确，ON子句中关联表的列名可以不同，也可以相同，但USING子句只能用在关联表的列名相同的情况下。
D选项，正确，不管是ON，还是USING子句，都要以在后面再添加where子句进行补充筛选。

 

相关知识点：

SELECT table1.column, table2.column

FROM table1

JOIN table2 USING(join_column1, join_column2...);

 

SELECT table1.column, table2.column

FROM table1

JOIN table2 ON(table1.column_name = table2.column_name);

 

纯自然联接和JOIN ...USING子句依赖具有相同列名的联接列.JOIN ... ON子句允许显式指定联接列，不管它们的列名是什么.这是联接子句最灵活、最常用的形式。同等联接被完全限定为table1.column1 = table2.column2

并可以在ON 关键字之后的括号内指定。

 

The pure natural join and the JOIN . . . USING clauses depend on join columns with identical column names. The JOIN . . . ON clause allows the explicit specification of join columns, regardless of their column names. This is the most flexible and widely used form of the join clauses. The equijoin columns are fully qualified astable1.column1 = table2.column2and are optionally specified in brackets after the ON keyword.

 

 

Query 1:

 select d.department_name from departments djoin locations l on (l.LOCATION_ID=d.LOCATION_ID)  where d.department_name like 'P%';

 

Query 2:

 select d.department_name from departments djoin locations l on

(l.LOCATION_ID=d.LOCATION_ID and d.department_name like 'P%');

 

8. View the Exhibit and examine the structureof the CUSTOMERS table.

Which two tasks would require subqueries orjoins to be executed in a single statement? (Choose two.)

A. listing of customers who do not have acredit limit and were born before 1980

B. finding the number of customers, in eachcity, whose marital status is 'married'

C. finding the average credit limit of malecustomers residing in 'Tokyo' or 'Sydney'

D. listing of those customers whose creditlimit is the same as the credit limit of customers residing in the city 'Tokyo'

E. finding the number of customers, in eachcity, whose credit limit is more than the average credit limit of all thecustomers

Answer: DE

 

题目翻译

下面是CUSTOMERS表的结构：
以下哪两个需求需要在一条语句中执行子查询或表关联查询？（选择两个）
A. 列出没有信贷限额并且1980年以前出生的客户。
B. 找出每个城市的已婚客户的数量。
C. 找出住在'Tokyo'或者 'Sydney'两个城市的男性客户的平均信贷限额。
D. 列出与住在'Tokyo'的客户的信贷限额相等的客户。
E. 找出每个城市信贷限额大于所有客户的平均信贷限额的客户数量。

 

题目解析

A选项，只需要在where条件中判断有没有信贷限额和出生日期小于1980的人，两个条件and连接就行了，不需要子查询或多表关联。

SELECT 客户 from 表名 where 信贷限额 is null and 出生日期>1980;
B选项，只需要在where条件过滤是否结婚就可以了，也不需要使用子查询或关联查询。

SELECT 城市名，COUNT(*) FROM 表名 where 客户婚否=‘结婚’ group by 城市名;
C选项，用where过滤两个城市，并且性别是男的客户，然后求平均信贷额，也不需要用子查询或关联查询。

SELECT 城市名， AVG(信贷限额) from 表名 where 性别=‘男’ and 城市 in（'Tokyo'， 'Sydney'） group by 城市名
D选项，首先要用子查询查出Tokyo这个客户的信贷限额，然后才能求出哪个用户与他的相等。
E选项，首先要用子查询查出客户的平均信贷额，然后才能求出哪些用户的信贷额大于平均信贷额。

 

 

9. Which statement is true regarding theINTERSECT operator?

A. It ignores NULL values.

B. Reversing the order of the intersectedtables alters the result.

C. The names of columns in all SELECTstatements must be identical.

D. The number of columns and data types mustbe identical for all SELECT statements in the query.

Answer: D

 

题目翻译

关于INTERSECT操作符，哪句话正确？
A. 它忽略空值
B. 交换交集表的前后顺序可以改变交集结果。
C. 所有SELECT查询语句中的列的名字必须相同。
D. 所有SELECT查询语句，列的数量和数据类型必须相同。

 

题目解析

A不正确，INTERSECT不会忽略空值，
B不正确，表的顺序改变，不影响结果，结果还是一样的。
C不正确，列的名字不需要相同，只需要列的数量和数据类型一致就行了。
D正确。

 

10. View the Exhibit; examine the structureof the PROMOTIONS table.

Each promotion has a duration of at leastseven days .

Your manager has asked you to generate areport, which provides the weekly cost for each promotion done to l date.

Which query would achieve the requiredresult?

A. SELECT promo_name, promo_cost/promo_end_date-promo_begin_date/7FROM promotions;

B. SELECTpromo_name,(promo_cost/promo_end_date-promo_begin_date)/7 FROM promotions;

C. SELECT promo_name,promo_cost/(promo_end_date-promo_begin_date/7) FROM promotions;

D. SELECT promo_name, promo_cost/((promo_end_date-promo_begin_date)/7)FROM promotions;

Answer: D

 

题目翻译

查看PROMOTIONS表结构。
每个促销活动的促销期至少持续七天。
你的经理让你去生成一个报表，该报表提供促销期间每周的成本。
哪个查询能得到所需的结果？

 

题目解析

AB C语法错误