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

根据下列员工信息表和部门信息表完成以下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) eWHERE 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 commis null;(不能用=,=只能判断是否为 空字符串)?4.数据库中出现两条一样的数据,如何只留一条?DISTINCT关键字(distinct)去重;在SELECT子句中使用，用来对指定的字段值去除重复行。查看公司有哪些职位?SELECT DISTINCT job FROM emp;多字段去重时，不保证单一的某个字段的值没有重复，而去重原则是这些字段值的组合没有重复行。SELECT DISTINCT job,deptno FROM emp4.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 empGROUP BY deptnoHAVINGmin_sal (SELECT MIN(sal) FROMempWHERE deptno = 30);//select deptno,MIN(sal) from emp group by deptnohiving MIN(sal)(select MIN(sal) from emp where deptno=30); ?8.查询部门平均薪资大于2000的所有员工信息;select * from emp where deptno in (selectdeptno from emp group by deptnohiving AVG(sal)2000);9.查询每个部门每种职位的员工人数;Select count(job),deptno,jobfrom emp_xiangyoulugroup by rollup(deptno,job);10:查看从2008-08-08号到今天为止一共经历了多少天?SELECT SYSDATE-TO_DATE(2