Oracle中的null测试题
create table TABLE1(
ID VARCHAR2(10) not null,
GRZHYE NUMBER(10,2),
GMSFHM VARCHAR2(18),
RYLB varchar2(10),
CARDNO VARCHAR2(20));
comment on column TABLE1.ID is '个人编号';
comment on column TABLE1.GRZHYE is '个人账户余额';
comment on column TABLE1.GMSFHM is '公民身份号码';
comment on column TABLE1.RYLB is '人员类别';
comment on column TABLE1.CARDNO is '卡号';
alter table TABLE1 add constraint PK_TABLE1 primary key (ID);
create index IDX_TABLE1_GMSFHM on TABLE1 (GMSFHM) tablespace YB;
create index idx_table1_cardno on TABLE1 (cardno);
表中的数据如下：
id, grzhye, gmsfhm, rylb, cardno
1, 100, 123456770707771, 01, 1401000001
2, null, 123456770707772, null, null
3, 200, 123456770707773, 03, 1401000003
1. select count(*) from table1 where 1=2; 结果为( )
A. null B. 0 C. 1 D. 会报错
2. select sum(grzhye) from table1 where 1=2; 结果为( )
A. null B. 0 C. 1 D. 会报错
3. select sum(grzhye) from table1; 结果为( )
A. null B. 0 C. 300 D. 会报错
4. select count(*) from (select sum(grzhye) from table1 where 1=2); 结果为( )
A. 0 B. 1 C. null D. 会报错
5. select avg(grzhye) from table1; 结果为( )
A. null B. 0 C. null D. 150 E. 100
6. 执行以下语句会 ( )
alter table TABLE1 add constraint udx_table1_cardno unique (CARDNO);
A. 成功 B. 报错
7. select * from table1 where cardno is null; 如果优化方式按规则，是否会用到idx_table1_cardno索引( )
A.会 B.不会
8. select * from table1 where cardno =’123’; 如何优化方式按规则，是否会用到idx_table1_cardno索引( )
A.会 B.不会
9. select min(grzhye) from table1; 结果是( )
A. null B. 100 C. 报错
10. select id||cardno from table1 where id = ‘2’; 结果会是：( )
A. null B. 2 C. 报错
11. Select 100 + null from dual; 结果是( )
A. null B. 100 C. 报错
12. Select 100 * null from dual; 结果是( )
A. null B. 100 C. 0 D. 报错
13. Select 100 / null from dual; 结果是( )
A. null B. 100 C. 0 D. 报错
14. Select null/0 from dual; 结果是( )
A. null B. 0 C. 报错
15. select rylb,sum(grzhye)/count(rylb) from table1 group by rylb;
会查到( )条记录
A. 0条 B. 2条 C. 3条 D. 报错
16. select 100/sum(grzhye) from table1 where id='2'; 结果是： ( )
A. null B. 0 C. 100 D. 报错
17. update table1 set cardno = null where id='2';
update table1 set cardno = ‘’ where id='2';
以上两句，( )
A. 效果是相同的 B. 只有第一句成功 C. 只有第二句成功
18. select * from table1 where cardno=''; 会查到几条记录 ( )
A. 0 B. 1 C. 报错
19. select * from table1 where cardno is null; 会查到几条记录 ( )
A. 0 B. 1 C. 报错
20. select count(cardno) from table1; 会查到几条记录 ( )
A. 0 B. 2 C.3 D. 报错
更多Oracle相关信息见 专题页面