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;
5.
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;
459
458。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