java開發(fā)常見筆試題(數(shù)據(jù)庫篇)

1、一. 根據(jù)下列員工信息表和部門信息表完成以下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;-錯! 偽列rownum和

2、排序order by 同時出現(xiàn)時,先生成偽列號,再按條件排序-導致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 between 2 and 5;-錯! 不

3、報錯,但查詢結果為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 between 2 and 5;-結果正確!3.2:查看獎金為

4、NULL的員工select * from emp_xiangyoulu where comm is null;(不能用=,=只能判斷是否為 空字符串)4.數(shù)據(jù)庫中出現(xiàn)兩條一樣的數(shù)據(jù),如何只留一條?DISTINCT關鍵字(distinct)去重;在SELECT子句中使用，用來對指定的字段值去除重復行。查看公司有哪些職位?SELECT DISTINCT job FROM emp;多字段去重時，不保證單一的某個字段的值沒有重復，而去重原則是這些字段值的組合沒有重復行。SELECT DISTINCT job,deptno FROM emp4.2 刪除除了ID之外,其他都一樣的冗余數(shù)據(jù)(重復數(shù)據(jù)只保留一

5、條);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 student group by code,name);select * from student; 5.查詢最

6、低薪水高于30號部門最低工資的部門信息?SELECT deptno, MIN(sal) min_sal FROM empGROUP BY deptnoHAVING min_sal (SELECT MIN(sal) FROM emp WHERE 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 i

7、n ( select deptno from emp group by deptno hiving AVG(sal)2000);9.查詢每個部門每種職位的員工人數(shù);Select count(job),deptno,job from emp_xiangyoulugroup by rollup(deptno,job);10:查看從2008-08-08號到今天為止一共經(jīng)歷了多少天?SELECT SYSDATE-TO_DATE(2008-08-08,YYYY-MM-DD)FROM DUAL;-向上取整 ；-SELECT CEIL(SYSDATE-TO_DATE(2008-08-08,YYYY-MM-D

8、D) FROM DUAL;11:將每名員工入職時間以例如:(1981年12月3日的形式顯示)select ename,to_char(hiredate, yyyy年MM月dd日) from emp_xiangyoulu;12:查看SMITH的上司在哪個城市工作?(三表關聯(lián)查詢)Select e.ename,e.mgr,m.empno,m.ename,d.loc from emp_xiangyoulu e,emp_xiangyoulu m,dept_xiangyoulu dWhere e.mgr=m.empno and m.deptno=d.deptno and e.ename=smith;13

9、:查看平均工資高于2000的那些部門名字以及所在城市?select e.deptno,avg(e.sal),d.dname,d.loc from emp_xiangyoulu e,dept_xiangyoulu dwhere e.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_xiangyoulu e,dept_xiangyoulu d where e.deptno=d.deptno and d.loc=NEW YORK group by d.loc;15:查看低于自己所在部門平均工資的員工?(將查詢結果作為表來關聯(lián)查詢 )select e.ename,e.