java开发常见笔试题(数据库篇)

一.根据下列员工信息表和部门信息表完成以下SQL查询

(表1 emp_xiangyoulu 员工信息表)

(表2 dept_xiangyoulu)

1.查询所有员工信息?

Select * from emp_xianyoulu;

2.查看所有部门信息?

Select * from dept_xiangyoulu;

3:查看公司工资排名的第2-5名(分页查询rownum)

select * from(

selectrownumrn,ename,sal from emp_xiangyoulu order by saldesc) where rn between 2 and 5;--->错! 伪列rownum和排序order by 同时出现时,先生成伪列号,再按条件排序--->导致rn对应的序号在排序之前就定了,查出的结果和预期不符;;

SELECT ROWNUMrn,e.* FROM (

SELECT * FROM emp_xiangyoulu ORDER BY saldesc) e

WHERE rn BETWEEN 2 AND 5;--->错! "RN": 标识符无效,伪列号不能直接当成一般属性直接用;

selectrownum,e.* from(

select * from emp_xiangyoulu order by saldesc)e

where rownum between 2 and 5;--->错! 不报错,但查询结果为0条,原因同上; select * from(

selectrownum,e.* from(

select * from emp_xiangyoulu order by saldesc)e)

where rownum between 2 and 5;--->错! 不报错,但查询结果为0条,rownum没取别名,直接用有影响;

select * from(

selectrownumrn,e.* from(

select * from emp_xiangyoulu order by saldesc)e)

where rn between 2 and 5;--->结果正确!!!

3.2:查看奖金为NULL的员工

select * from emp_xiangyoulu where comm is null;(不能用=,=只能判断是否为" "空字符串)

4.数据库中出现两条一样的数据,如何只留一条?

DISTINCT关键字(distinct)去重;

在SELECT子句中使用，用来对指定的字段值去除重复行。

查看公司有哪些职位?

SELECT DISTINCT job FROM emp;

多字段去重时，不保证单一的某个字段的值没有重复，而去重原则是这些字段值的

组合没有重复行。

SELECT DISTINCT job,deptno FROM emp

4.2 删除除了ID之外,其他都一样的冗余数据(重复数据只保留一条);

delete student where (code,name) in(

selectcode,name from student group by code,name having count(name)>1) andid not in(

select min(id) from student group by code,name having count(name)>1); delete student where id not in(

select min(id) from student group by code,name);

select * from student;

5.查询最低薪水高于30号部门最低工资的部门信息?

SELECT deptno, MIN(sal) min_salFROM emp

GROUP BY deptno

HAVINGmin_sal>(SELECT MIN(sal)

FROMempWHERE deptno = 30);

//select deptno,MIN(sal) from emp group by deptno

hiving MIN(sal)>(select MIN(sal) from emp where deptno=30);

8.查询部门平均薪资大于2000的所有员工信息;

select * from emp where deptno in (

selectdeptno from emp group by deptno

hiving AVG(sal)>2000

);

9.查询每个部门每种职位的员工人数;

Select count(job),deptno,job

from emp_xiangyoulu

group by rollup(deptno,job);

10:查看从2008-08-08号到今天为止一共经历了多少天?

SELECT SYSDATE-TO_DATE('2008-08-08','YYYY-MM-DD')

FROM DUAL;

--向上取整；；

--SELECT CEIL(SYSDATE-TO_DATE('2008-08-08','YYYY-MM-DD'))

FROM DUAL;

11:将每名员工入职时间以例如:(1981年12月3日的形式显示)

select ename,to_char(hiredate, 'yyyy"年"MM"月"dd"日"')

fromemp_xiangyoulu;

12:查看SMITH的上司在哪个城市工作?(三表关联查询)

Select e.ename,e.mgr,m.empno,m.ename,d.loc

fromemp_xiangyoulue,emp_xiangyoulum,dept_xiangyoulu d

Where e.mgr=m.empno and m.deptno=d.deptno and e.ename='smith';

13:查看平均工资高于2000的那些部门名字以及所在城市?

selecte.deptno,avg(e.sal),d.dname,d.loc from

emp_xiangyoulue,dept_xiangyoulu d

wheree.deptno=d.deptno group by e.deptno,d.loc,d.dname having

avg(e.sal)>2000;

14.在NEW YORK工作的员工有多少人?

select count(e.ename),d.loc from emp_xiangyoulue,dept_xiangyoulu d wheree.deptno=d.deptno and d.loc='NEW YORK' group by d.loc;

15:查看低于自己所在部门平均工资的员工?(将查询结果作为表来关联查询) selecte.ename,e.sal,d.deptno from emp_xiangyoulu e,(

selectavg(sal) a_sal,deptno from emp_xiangyoulu group by deptno) d wheree.deptno=d.deptno and e.sal