47道 oracle SQL测试题

1.查出emp表中薪水在3000以上（包括3000）的所有员工的员工号、姓名、薪水。
selectemp_id,emp_name,salary from emp where salary>=3000
select empno,ename,sal from emp where sal >=3000;

2.查询出emp表中所有员工一年的薪水，显示形式如下：
 编号为XX的员工，姓名为XX，年薪XX。（提示：使用||将多个列合并）

select '编号为'+cast(emp_id asvarchar(20))+'的员工,姓名为+emp_name+'，年薪'+salary+'。'fromemp
select'编号为'||empno||'的员工，姓名为'||ename||',年薪为'||(sal*12)||'。' fromemp;

3.查询出emp表中部门编号为20，薪水在2000以上（不包括2000）的所有员工，显示他们的员工号，姓名以及薪水，以如下列名显示：员工编号员工名字 薪水
select emp_id 员工编号,emp_name 员工姓名,salary 薪水from emp where dep_id=20 and salary>2000
select empno as "员工编号",ename as "员工名字",sal as "薪水" from emp wheredeptno = 20 and sal > 2000;

4.查询出emp表中所有的工作种类（无重复）
select disctinct job from emp
selectdistinct(job) from emp;

5.查询出所有奖金（comm）字段不为空的人员的所有信息。
select *from emp where comm is not null
select * from emp where comm is not null;

6.1查询出薪水在800到2500之间（闭区间）所有员工的信息。（注：使用两种方式实现and以及betweenand）
select * from emp wheresalary>800 and salary<2500
select * from emp where salary between 800 and 2500

select * from emp where sal >= 800 and sal<= 2500;
6.2查询出薪水在800到2500之间（闭区间）所有员工的信息。（注：使用两种方式实现and以及between and）
select * from emp where sal between 800 and 2500;

7.查询出员工号为7521，7900，7782的所有员工的信息。（注：使用两种方式实现，or以及in）
select* from emp where emp_id = 7521 or emp_id = 7900 or emp_id=7782
select * from emp where emp_id in(7521,7900,7782)

select * from emp where empno = 7521 or empno = 7900 or empno =7782;
select * from emp where empno in (7521,7900,7782);

8.查询出名字中有“A”字符，并且薪水在1000以上（不包括1000）的所有员工信息。
select* from emp where emp_name like '%A%' and salary>1000
select * from emp where ename like '%A%' and sal >1000;

9.查询出名字第三个字母是“M”的所有员工信息。

select * from zt_rsdazl wheresubstring(c86,3,1)='M'
select * from emp where enamelike '__M%';

10.将所有员工按薪水升序排序，薪水相同的按照入职时间降序排序。
select* from emp order by salary,hiredate  desc
select * from emp order by sal asc,hiredate desc;

11.将所有员工按照名字首字母升序排序，首字母相同的按照薪水降序排序。
select *from emp order by emp_namee,salary desc
select * from emp order by ename asc,sal desc;

12.显示所有员工名字的小写形式、薪水及部门编号。
selectlower(emp_namee),salary,dep_it from emp
select lower(ename),sal,deptno from emp;

13.查询出所有在30号部门的员工的员工名字、薪水和所在部门编号，其中名字按照首字母大写，其余小写的形式显示。
selectupper(left(emp_namee,1))+substring(emp_namee,2,20),salary,dep_idfrom emp where dep_id = 30
selectinitcap(ename),sal,deptno from emp where deptno = 30;

14.将所有人的名字截掉首字母后显示。
selectsubstring(emp_namee,2,20)as emp_name from emp
select substr(ename,2) from emp;

15.查询出emp表中所有名字长度在5以上（不包括5）人的名字、员工编号、薪水以及名字的长度。
selectemp_namee,emp_id,salary,len(emp_namee) from emp wherelen(emp_namee)>5
select ename,empno,sal,length(ename) from emp where length(ename)> 5;

16.查询出emp表中不在10号部门所有人的名字、薪水和部门编号，将名字中所有的字符‘E’替换成‘%’显示。
selectreplace(emp_namee,'E','%'),salary,dep_id from emp wheredep_id<>10
selectreplace(ename,'E','%') as ename,sal,deptno from emp where deptno !=10;

17.查询出所有薪水在1500以上（不包括1500）的所有人的名字、薪水、入职时间，使用四舍五入的方式将所有薪水保留一位小数。
select emp_namee,cast(salary as decimal(18,1),hiredatefrom emp where salary >1500
select emp_namee,round(salary,1),hiredatefrom emp where salary>1500
selectename,round(sal,1),hiredate from emp where sal >1500;

18.查询出所有薪水在1800以下（包括1800）的所有人的名字、薪水、入职时间，使截取的方式将所有薪水小数截掉。
select emp_namee,salary,cast(salary as decimal(18,0))fromemp where salary <=1800
select ename,trunc(sal),hiredate from emp where sal<= 1800;

19.查询出最早工作的那个人的名字、入职时间和薪水。
select emp_namee,hiredate,salary from emp wherehiredate=(select min(hiredate)from emp)
selectename,hiredate,sal from emp where hiredate = (select min(hiredate)from emp);

20.查询出最晚工作的那个人的名字、入职时间。
selectemp_namee,hiredate,salary from emp where hiredate=(selectmax(hiredate)from emp)
select ename,hiredate from emp where hiredate = (selectmax(hiredate) from emp);

21.计算出最早入职的员工和最晚入职的员工的入职年份只之差。
select datediff(year,min(hiredate),max(hiredate))fromemp
selectmax(TO_CHAR(hiredate,'yyyy'))-min(TO_CHAR(hiredate,'yyyy')) fromemp;

22.显示所有员工的名字、薪水、奖金，如果没有奖金，暂时显示100.
selectemp_name,salary,isnull(comm,100) from emp
selectename,sal,NVL(comm,100) from emp;

23.使用nullif函数求出所有名字长度为6的员工的名字和部门编号。
selectemp_namee,dep_id from emp where nullif(len(emp_namee),6)isnull
select ename,deptno from emp wherenullif(length(ename),6) is null;

24.显示出薪水最高人的职位。
select job from emp wheresalary=(select max(salary) from emp)
select job from emp where sal = (select max(sal) from emp);

25.查出emp表中所有部门的最高薪水和最低薪水，部门编号为10的部门不显示。
selectmax(salary),min(salary),dep_id from emp wheredep_id<>10 group by dep_id
select max(sal),min(sal),deptno from emp where deptno != 10 groupby deptno;

26.查询出所有薪水在'ALLEN'之上的所有人员信息。
select *from emp where sal > (select sal from emp whereename = 'ALLEN');

27.查询出员工名字、薪水、入职年份、部门编号和部门名称。
selecta.emp_namee,a.salary,a.hiredate,a.dep_id,b.dep_name from emp ainner join dep b on a.dep_id = b.dep_id
selecte.ename,e.sal,e.hiredate,e.deptno,d.dname from emp e,dept d wheree.deptno = d.deptno;

28.查询出所有员工的名字以及其上司的名字。
select a.emp_name,b.emp_name from emp a,emp b wherea.manger=b.emp_id
select e1.ename,e2.ename from empe1,emp e2 where e1.mgr = e2.empno;

29.查出所有员工的个人信息和部门信息（多表连接），暂时没有部门的也要查出来。
selecta.*,b.* from emp a left join dep b on a.dep_id =b.dep_id
select * from emp e,dept d where e.deptno =d.deptno;

30.查询所有员工的信息和其薪水对应的级别。（salgrade：薪水分级表）
selecta.*,b.grade from emp a inner join salgrade b on a.grade_v=b.grade_id
select e.*,s.grade from emp e,salgrade s where e.sal betweens.losal and s.hisal;

31.在emp表中新添加一个员工：ename:ZHANGSAN empno:1234job:manager
                           hiredate:2009-01-23
insert inemp(emp_namee,emp_id,job,hiredate)values('ZHANGSAN',1234,'2009-01-23')
Insert Into emp(empno,ename,hiredate)Values(1234,'ZHANGSAN',to_date('2009-01-23','yyyy-mm-dd'));

32.将新添加的员工工作改为：ANALYST,薪水改为3000，入职时间加一年。
updateemp set job ='ANALYST',salary='3000',hiredate+365 whereemp_id='1234'

33.将新添加的员工的奖金改为和编号为7499一样高。（7499若没有奖金先为7499添加奖金100）
updateemp set comm=isnull((select comm from emp where emp_id='7499'),100) where emp_id='1234'
34.更新表中数据如下：Update emp Set deptno=55 Where empno=7499
  能否更新成功，为什么？
可以。

35.将新添加的员工删除。
delete from emp whereemp_id ='1234'

36.删除10号部门薪水最高的员工。
delete from emp whereemp_id =(select emp_id from emp where salary=(selectmax(salary)from emp where dep_id = 10))
delete from emp where empno in (select empno from emp where sal=(select max(sal) from emp where deptno = 10));

37.将薪水最高的员工的薪水降30%。
update emp setsalary=0.7*salary where salary=(select max(salary)from emp)
update emp set sal = 0.7 * sal where sal = (select max(sal) fromemp);

38.创建一个测试表，表名：test，其中有如下字段：id：Number类型4位，name：varchar2类型20位，birthday：Date类型，默认值为2008年08月08号。
create table test(id numeric(4),
name varchar(20),
birthday date default'2008-08-08')

39.删除测试表中的birthday字段。
alter table testdrop column name
Alter Table test Drop Column birthday。

40.什么是数据库中的事务？
数据库事务(Database Transaction)，是指作为单个逻辑工作单元执行的一系列操作。
 事务处理可以确保除非事务性单元内的所有操作都成功完成，否则不会永久更新面向数据的资源。
通过将一组相关操作组合为一个要么全部成功要么全部失败的单元，可以简化错误恢复并使应用程序更加可靠。
一个逻辑工作单元要成为事务，必须满足所谓的ACID(原子性、一致性、隔离性和持久性)属性。

41.char和varchar2的区别？
  char---定长字符串
   varchar2----变长字符串

  char(20)   abcd---------20
   varchar(20)abcd--------4

42.NUMBER(N)和number(m,n)的区别？
  NUMBER(N):表示整数位不能大于n个,小数位可以任意
  number(m,n):表示总长度为m位,其中小数位为n,整数为为m-n

43.delete和truncate的作用以及区别？
两种方式
A.delete删除
B.truncate清空表的内容

区别：
delete：从数据库的缓存区清除该数据
truncate：把数据删除了，然后清空所占用的空间

delete可以撤销
truncate不能撤销
truncate===delete+commit

TRUNCATE 语法
TRUNCATE TABLE EMP;

44.TRUNCATE和drop区别？
drop：删除表的定义，整个对象删掉，删除的是对象的本身，全部
truncate：删除表的内容，只是删除数据，表的结构会保留

45.什么是主键，主键的作用是什么？可以举例阐述
一般情况下,我们在创建表的同时,都会为表指定一个主键,用来唯一标识一条记录,以便在程序中实现修改,删除等业务逻辑操作时,根据该主键标识来准确定位到要操作的记录

46.什么是外键，外键的作用是什么？可以举例阐述
外来的键值,如EMP表的deptno字段引用了dept表中的deptno字段
我们认为:emp表中的deptno是一个外键,emp.deptno不能任意指定,只能从dept.deptno的值中任意选一个

47.什么是联合主键？
把多个字段合起来作为一个主键,要求多个字段每一项都不能为null,并且合起来的值不能重复.