问题集2

来源：互联网发布：parsley.js 手册编辑：程序博客网时间：2024/06/06 10:46

1.下列聚集函数中哪些处理null 哪些不处理null

max() min() avg() sum() count(*) count(column)

2.找出语句中的错误

select deptno,job,sum(sal)

from emp

where sum(sal)<3000

group by deptno ,job

having sum(sal) > 2000

3.比较decode 和 case的区别

decode

只能等值比较

case

简单

只能等值比较

搜索

等值＋不等值

4.下面语句能否正确执行

select ename,sal+comm from emp where comm is not null;

select to_date(20110501,'YYYYMMDD') from dual;

select to_number(sysdatE,9999999999) from dual;

SQL> alter session set nls_date_format='YYYY-MM-DD HH24:MI:SS';

Session altered.

SQL> 执行下列语句自己总结YY和RR的区别

YY 44 85

2013 ＝＝》 2044 2085

RR 44 85

2013

1944 1985

2044 2085

SQL> select to_date('01-MAY-50','DD-MON-YY'),to_date('01-MAY-50','DD-MON-RR') from dual;

SQL> select to_date('01-MAY-11','DD-MON-YY'),to_date('01-MAY-11','DD-MON-RR') from dual;

SQL> select to_date('01-MAY-81','DD-MON-YY'),to_date('01-MAY-81','DD-MON-RR') from dual;

SQL>

6.写出下列语句的执行结果

select round(458.734,0),round(458.734,1),round(458.734,-1) from dual;

459458。7 460

select trunc(458.734,0),trunc(458.734,1),trunc(458.734,-1) from dual

458 458。7 450

7. 根据结果写出SQL语句

SQL> select deptno,rpad(lpad(ename,8,'='),length(lpad(ename,8,'='))+3,'=') "ABC" from emp where deptno=20;

DEPTNO ABC

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

20 ===SMITH===

20 ===JONES===

20 ===SCOTT===

20 ===ADAMS===

20 ====FORD===

SQL>

SQL> select ename,regexp_replace(ename,'S|T','9') abc from emp where deptno=20;

ENAME ABC

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

SMITH 9MI9H

JONES JONE9

SCOTT 9CO99

ADAMS ADAM9

FORD FORD

SQL>

8.使用scott用户执行下面语句能获取到EMP表的元数据(即创建表的完整语句格式)

select dbms_metadata.get_ddl('TABLE','EMP') from dual;

写出一个SQL生成SCOTT用户中所有表的查看元数据语句

输出结果:

select dbms_metadata.get_ddl('TABLE','EMP') from dual;

select dbms_metadata.get_ddl('TABLE','DEPT') from dual;

....

有多少张表就有多少条SQL产生

SYS@orasid> l

1 select 'select dbms_metadata.get_ddl('||

2 chr(39)||object_type||chr(39)||','

3 ||chr(39)||object_name||chr(39)

4 ||','

5 ||chr(39)||owner||chr(39)

6 ||') from dual;'

7 from dba_objects

8* where owner='SCOTT'

SYS@orasid>

9. 替代变量 & 和 &&有什么区别?

& 环境搜索变量名称

存在直接引用

否则交互输入但不保存

＆＆环境搜索变量名称

存在直接引用

否则交互输入但保存

10.如何使select '&abc' from dual;不需要交互输入

define abc='xxx'

undefine abc