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

1、一. 根据下列员工信息表和部门信息表完成以下SQL查询(表1 emp_xiangyoulu 员工信息表)(表2 dept_xiangyoulu)1.查询所有员工信息Select * from emp_xianyoulu;2.查看所有部门信息Select * from dept_xiangyoulu;3:查看公司工资排名的第2-5名(分页查询rownum )select * from( select rownum rn,ename,sal from emp_xiangyoulu order by sal desc) where rn between 2 and 5;->错! 伪列rownu

2、m和排序order by 同时出现时,先生成伪列号,再按条件排序->导致rn对应的序号在排序之前就定了,查出的结果和预期不符;SELECT ROWNUM rn,e.* FROM ( SELECT * FROM emp_xiangyoulu ORDER BY sal desc) eWHERE rn BETWEEN 2 AND 5;->错! "RN": 标识符无效,伪列号不能直接当成一般属性直接用;select rownum,e.* from( select * from emp_xiangyoulu order by sal desc)e where rownum

3、 between 2 and 5;->错! 不报错,但查询结果为0条,原因同上;select * from( select rownum,e.* from( select * from emp_xiangyoulu order by sal desc)e)where rownum between 2 and 5;->错! 不报错,但查询结果为0条,rownum没取别名,直接用有影响;select * from( select rownum rn,e.* from( select * from emp_xiangyoulu order by sal desc)e)where rn b

4、etween 2 and 5;->结果正确!:查看奖金为NULL的员工select * from emp_xiangyoulu where comm is null;(不能用=,=只能判断是否为" "空字符串) 4.数据库中出现两条一样的数据,如何只留一条DISTINCT关键字(distinct)去重;在SELECT子句中使用，用来对指定的字段值去除重复行。查看公司有哪些职位SELECT DISTINCT job FROM emp;多字段去重时，不保证单一的某个字段的值没有重复，而去重原则是这些字段值的组合没有重复行。SELECT DISTINCT job,

5、deptno FROM emp 删除除了ID之外,其他都一样的冗余数据(重复数据只保留一条);delete student where (code,name) in( select code,name from student group by code,name having count(name)>1)and id 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 stud

6、ent group by code,name);select * from student;   5.查询最低薪水高于30号部门最低工资的部门信息SELECT deptno, MIN(sal) min_sal FROM empGROUP BY deptnoHAVING min_sal >(SELECT MIN(sal) FROM emp WHERE deptno = 30);询部门平均薪资大于2000的所有员工信息; select * from emp where deptno in ( select deptno from emp group by deptno h

7、iving AVG(sal)>2000);9.查询每个部门每种职位的员工人数;Select count(job),deptno,job from emp_xiangyoulugroup 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'

8、) FROM DUAL;11:将每名员工入职时间以例如:(1981年12月3日的形式显示)select ename,to_char(hiredate, 'yyyy"年"MM"月"dd"日"') from emp_xiangyoulu;12:查看SMITH的上司在哪个城市工作(三表关联查询)Select , from emp_xiangyoulu e,emp_xiangyoulu m,dept_xiangyoulu dWhere = and = and ='smith' 13:查看平均工资高于2000的那些部门名字以及所在城市select ,avg, from emp_xiangyoulu e,dept_xiangyoulu dwhere = group by , having avg>2000; 14.在NEW YORK工作的员工有多少人select count, from emp_xiangyoulu e,dept_xiangyoulu d where = and ='NEW YORK' group by ; 15:查看低于自己所在部门平均工