java開(kāi)發(fā)常見(jiàn)筆試習(xí)題(數(shù)據(jù)庫(kù)篇)

1、一. 根據(jù)下列員工信息表和部門(mén)信息表完成以下SQL查詢(表1 emp_xiangyoulu 員工信息表)(表2 dept_xiangyoulu)1.查詢所有員工信息Select * from emp_xianyoulu;2.查看所有部門(mén)信息Select * from dept_xiangyoulu;3:查看公司工資排名的第2-5名(分頁(yè)查詢r(jià)ownum )select * from( select rownum rn,ename,sal from emp_xiangyoulu order by sal desc) where rn between 2 and 5;->錯(cuò)! 偽列rownu

2、m和排序order by 同時(shí)出現(xiàn)時(shí),先生成偽列號(hào),再按條件排序->導(dǎo)致rn對(duì)應(yīng)的序號(hào)在排序之前就定了,查出的結(jié)果和預(yù)期不符;SELECT ROWNUM rn,e.* FROM ( SELECT * FROM emp_xiangyoulu ORDER BY sal desc) eWHERE rn BETWEEN 2 AND 5;->錯(cuò)! "RN": 標(biāo)識(shí)符無(wú)效,偽列號(hào)不能直接當(dāng)成一般屬性直接用;select rownum,e.* from( select * from emp_xiangyoulu order by sal desc)e where rownum

3、 between 2 and 5;->錯(cuò)! 不報(bào)錯(cuò),但查詢結(jié)果為0條,原因同上;select * from( select rownum,e.* from( select * from emp_xiangyoulu order by sal desc)e)where rownum between 2 and 5;->錯(cuò)! 不報(bào)錯(cuò),但查詢結(jié)果為0條,rownum沒(méi)取別名,直接用有影響;select * from( select rownum rn,e.* from( select * from emp_xiangyoulu order by sal desc)e)where rn b

4、etween 2 and 5;->結(jié)果正確!:查看獎(jiǎng)金為NULL的員工select * from emp_xiangyoulu where comm is null;(不能用=,=只能判斷是否為" "空字符串) 4.數(shù)據(jù)庫(kù)中出現(xiàn)兩條一樣的數(shù)據(jù),如何只留一條DISTINCT關(guān)鍵字(distinct)去重;在SELECT子句中使用，用來(lái)對(duì)指定的字段值去除重復(fù)行。查看公司有哪些職位SELECT DISTINCT job FROM emp;多字段去重時(shí)，不保證單一的某個(gè)字段的值沒(méi)有重復(fù)，而去重原則是這些字段值的組合沒(méi)有重復(fù)行。SELECT DISTINCT job,

5、deptno FROM emp 刪除除了ID之外,其他都一樣的冗余數(shù)據(jù)(重復(fù)數(shù)據(jù)只保留一條);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號(hào)部門(mén)最低工資的部門(mén)信息SELECT deptno, MIN(sal) min_sal FROM empGROUP BY deptnoHAVING min_sal >(SELECT MIN(sal) FROM emp WHERE deptno = 30);詢部門(mén)平均薪資大于2000的所有員工信息; select * from emp where deptno in ( select deptno from emp group by deptno h

7、iving AVG(sal)>2000);9.查詢每個(gè)部門(mén)每種職位的員工人數(shù);Select count(job),deptno,job from emp_xiangyoulugroup by rollup(deptno,job);10:查看從2008-08-08號(hào)到今天為止一共經(jīng)歷了多少天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:將每名員工入職時(shí)間以例如:(1981年12月3日的形式顯示)select ename,to_char(hiredate, 'yyyy"年"MM"月"dd"日"') from emp_xiangyoulu;12:查看SMITH的上司在哪個(gè)城市工作(三表關(guān)聯(lián)查詢)Select , from emp_xiangyoulu e,emp_xiangyoulu m,dept_xiangyoulu dWhere = and = and ='smith' 13:查看平均工資高于2000的那些部門(mén)名字以及所在城市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:查看低于自己所在部門(mén)平均工