oracle全集實戰(zhàn)課程2011版語句-練習(xí)題包含答案

練習(xí)一、簡單查選擇部門30中的所有員select*fromempwhere列出所有業(yè)務(wù)員(CLERK)的，和部門selectempno fromempwhere此查詢應(yīng)用了別個限定條件，需要注意的是Oracle是區(qū)分大小寫的，所以要將業(yè)務(wù)員大寫為“CLERK”才能查詢出數(shù)據(jù)，或者使用upper()函數(shù)。selectempno,ename,deptno部門fromempwhere找出傭金高于工資的雇員信select*fromempwhere找出傭金高于工資的60%的雇員select*fromempwhereselect*fromempwhere(deptno=10andjob='MANAGER')or(deptno=20andjob='CLERK');andor，并且使用()將多個限定條件包找出部門1020中所有業(yè)務(wù)員，既不是經(jīng)理又不是業(yè)務(wù)員但是大于或2000的所有雇員信select*fromempwhere(deptno=10andjob='MANAGER')or(deptno=20andjob='CLERK')or(sal>=2000andjobnot找出領(lǐng)取傭金的雇員的不同工作selectdistinctjobfromempwherecommisnot這個需求包含的信息有，工作會重復(fù)復(fù)，所以需要使用關(guān)鍵字distinct消除重復(fù)的記錄，能夠領(lǐng)取傭金說明commisnotnull找出不能領(lǐng)取傭金或者領(lǐng)取的傭金低于100select*fromempwherecommisnullor找出每個月中倒數(shù)第三天雇傭的雇員信select*fromempwherelast_day(hiredate)-分析下，Oracle為提供了一個叫做last_day()的函數(shù)，它的功能是查詢出指定日期的最后一天對應(yīng)的日期但是這里是要查詢倒數(shù)第三天如何處理？2找出早于12年前雇傭的雇員信息這個需求轉(zhuǎn)變下，知道Oraclemonths_between()，它的作用是查詢出給定的日期的值是月份數(shù)，所以，這么考select*fromempwhere以首字母大寫的方式顯示所有selectinitcname)from顯示雇員長度正好是5的雇員姓selectenamefromempwhere顯示雇員中不含“R”的雇selectenamefromempwhereenamenotlike'%R%';此查詢使用了like語句糊查詢，當(dāng)like后指定的關(guān)鍵字為“%%”時，表示查詢據(jù)顯示所有雇員的前三個字selectsubstr(ename,0,3)fromemp;selectsubstr(ename,1,3)from此處應(yīng)該強調(diào)的是，截取點的指定中，01顯示所有雇員的，并且用“a”替換所有的selectename,replace(ename,'A','a')from顯示工齡為10或以上的雇員和雇傭日selectename,hiredatefromempwhere顯示雇員的詳細(xì)信息，并按照排select*fromemporderby顯示雇員的和雇傭時間，根據(jù)其工齡，將最老的雇員排在最前selectename,hiredatefromemporderbyselectename,job,salfromemporderbyjob顯示所有雇員的雇傭的年份月份按照雇傭日期所在月排序如果月份相同則將最早的年份的雇員排在最to_char()selectename,to_char(hiredate,'mm')month,to_char(hiredate,'yyyy')yearfromemporderby顯示在一個月為30天的情況下所有雇員的日薪，忽略余數(shù)。忽略余數(shù)使用round()函數(shù)完成selectename,round(sal/30)from找出在每年2月份雇傭的所有雇員select*fromempwhereto_char()對于每個雇員，顯示其到今天為止的總天selectename,round(sysdate-hiredate)from顯示雇員中包含“A”的所有雇selectenamefromempwhereenamelike以年月日的方式顯示所有雇員的工齡第一步：求出每個雇員的雇傭年數(shù)：被雇傭的總月數(shù)/12=SELECTENAME,HIREDATE,TRUNC(MONTHS_BWTEEN(SYSDATE,HIREDATE)/12)YEARFROMtrunc()selectename,hiredate，trunc(months_between(sysdate,hiredate)/12)year,trunc(mod(months_between(sysdate,hiredate),12))monthsfromemp;現(xiàn)在已經(jīng)知道當(dāng)前的時間使用SYSDATE而雇傭的日期使用HIREDATE取出，HIREDATE和SYSDATE之間的差距太大了，所以肯定會有誤差，那么HIREDATESYSDATE30天的范圍之內(nèi)。HIREDATE+與今天相距的月數(shù)=SYSDATE30天。SELECTTRUNC(MONTHS_BETWEEN(SYSDATE,HIREDATE)/12)YEAR,EN(SYSDATE,HIREDATE)))FROM將所有的雇員按照小寫字母返SELECTLOWER(ENAME)FROMEMP將每一個雇員的的開頭首字母大寫返SELECTINITCNAME)FROMEMP查詢 長度是5的所有員SELECT*FROMEMPWHERE使用字母“_”替換掉中的所有字母SELECTENAME,REPLACE(ENAME,'A','_')FROMEMP求出本月的最后一天日SELECTLAST_DAY(SYSDATE)FROM求出下一個星期一和星期六的SELECTSYSDATE,NEXT_DAY(SYSDATE,'星期一'),NEXT_DAY(SYSDATE,'星期六FROMDUAL;求出今天的6月后的SELECTADD_MONTHS(SYSDATE,6)FROM10部門員工工資增加UPDATEEMPSETSAL=SAL*1.05WHERE刪除所有在1987年雇傭的雇DELETEFROMEMPCSWHERE增加一個員工員工888，，SMITH,工作清潔工，雇傭日1990-05-06INSERTINTOEMPCS(EMPNO,ENAME,MGR,JOB,HIREDATE)VALUES(888,'','7902','清潔工把剛增加（35題）的員工工作改為保安,工資1500，部門30UPDATEEMPCSSETSAL=1500,DEPTNO=30,JOB='保安WHERE要求顯示所有雇員的及的后三個字符SELECTENAME,SUBSTR(ENAME,-3,3)FROMEMP;從字符串'oracleor'的第3'or'出現(xiàn)的位置。SELECTINSTR('oracleor','or',3)FROM二、復(fù)雜查①確定所需要的數(shù)據(jù)表：.emp.dept.emp②確定已知的關(guān)聯(lián)字段：emp.deptno=dept.deptno;selectdeptno,count(empno)fromempgroupbyhaving將之前的emp表和dept表兩個表進行連接，采用多字段分組方式查詢：selectd.deptno,d.dname,count(e.empno)fromempe,deptdwheree.deptno=d.deptno(+)groupbyd.deptno,d.dnamehavingcount(empno)>=1;第三步：依然需要繼fromempe,deptwheree.deptno=d.deptno(+)groupbyd.deptno,d.dnamehavingcount(empno)>=1;列出薪金比“SMITH”或“ALLEN”多的所有員工、、部門名稱、其第一步：找出“SMITH”或“ALLEN”的selectsalfromempwhereenamein第二步：以上查詢返回的是多行單列的記錄，安裝子查詢的要求在where子句中寫selecte.deptno,e.enamefromempewheree.sal>all(selectsalfromempwhereenamein第三步：查詢出部門名稱，引入部門表，同時增加消除積的條selecte.deptno,e.ename,d.dnamefromempe,deptdwheree.sal>all(selectsalfromempwhereenameinandselecte.deptno,e.ename,d.dname,m.enamefromempe,deptd,empmwheree.sal>all(selectsalfromempwhereenameinande.deptno=d.deptnoand題目中用的是或面答案中是>all應(yīng)該用selecte.empno,e.ename,e.sal,dname,m.enamefromempe,deptd,empmwheree.deptno(+)=d.deptnoande.mgr=m.empno(+)ande.sal>any(selectsalfromempwhereenamein.emp表：員工的、selecte.empno,e.ename,m.empno,m.ename, fromempe,empwheree.mgr=m.empno(+)orderby edesc;列出雇傭日期早于其直接的所有雇員的、、部門名稱、部門位置、部門人數(shù).emp表：雇員的、.emp表：求出的工作日期.dept.emp第一步：列出受雇日期早于其直接的所有員工、——自身關(guān)empSELECTE.EMPNO,E.ENAMEFROMEMPE,EMPMWHEREE.MGR=M.EMPNO(+)AND第二步：加入部門信息，繼續(xù)引入部門SELECTE.EMPNO,E.ENAME,D.DNAME,D.LOCFROMEMPE,EMPM,DEPTDWHEREE.MGR=M.EMPNO(+)ANDE.HIREDATE<M.HIREDATEANDE.DEPTNO=D.DEPTNO;額外提問，此時的積：emp表的14條*emp表的14條*dept表的4條SELECTFROMEMPE,EMPM,DEPTD,(SELECTDEPTNODNO,COUNT(EMPNO)COUNTFROMEMPGROUPBYDEPTNO)WHEREE.MGR=M.EMPNO(+)ANDE.HIREDATE<M.HIREDATEANDE.DEPTNO=D.DEPTNOANDE.DEPTNO=TEMP.DNO;過子查詢在FROM子句之后進行統(tǒng)計列出部門名稱和這些部門的員工信息（數(shù)量、平均工資）,.dept.empFROMEMPE,DEPTDWHEREE.DEPTNO(+)=D.DEPTNOGROUPBY列出所有”CLERK”(辦事員)的及部門名稱,部門的人數(shù),工資等級.emp表：找到辦事員的.dept.emp.empdept.empsalgrade表：emp.salbetweensalgrade.losalandsalgrade.hisal；SELECTENAMEFROMEMPWHERESELECTE.ENAME,D.DNAMEFROMEMPE,DEPTDWHEREAND第三步：部門人數(shù)需要額外統(tǒng)計，但是本程序查詢里面已經(jīng)不可能繼續(xù)使用SELECTE.ENAME,D.DNAME,FROMEMPE,DEPTD,(SELECTDEPTNODNO,COUNT(DEPTNO)COUNTFROMEMPGROUPBYDEPTNO)TEMPWHEREJOB='CLERK'ANDE.DEPTNO=D.DEPTNOANDSELECTE.ENAME,D.DNAME,FROMEMPE,DEPTD,(SELECTDEPTNODNO,COUNT(DEPTNO)COUNTFROMEMPGROUPBYDEPTNO)TEMP,SALGRADESWHEREJOB='CLERK'ANDE.DEPTNO=D.DEPTNOANDANDE.SALBETWEENLOSALAND列出最低薪金大于1500的各種工作及此從事此工作的全部雇員人數(shù)及所在部.emp1500emp表統(tǒng)計出，以及可以求.dept.emp.empdeptSELECTE.JOBJOB,COUNT(E.EMPNO)FROMEMPEGROUPBYHAVING第二步：要查詢出雇員所在的部門信息，但以上的查詢能跟dept.empjob.要引入的deptempdeptnoSELECTTEMP.JOB,TEMP.COUNT,D.DNAME,E.ENAMEFROMDEPTD,(SELECTE.JOB,COUNT(E.EMPNO)COUNTFROMEMPEGROUPBYHAVINGMIN(E.SAL)>1500)TEMP,EMPEWHEREE.DEPTNO=D.DEPTNOANDE.JOB=TEMP.JOB;FROMDEPTD,(SELECTE.JOB,COUNT(E.EMPNO)COUNTFROMEMPEGROUPBYHAVINGMIN(E.SAL)>1500)TEMP,EMPE,BYDEPTNO)SELWHEREE.DEPTNO=D.DEPTNOANDE.JOB=TEMP.JOBANDE.DEPTNO=SEL.DNO;稱，假定不知道銷售部的部門。.emp表：員工、基本工資、雇傭日期.empdeptSELECTE.ENAME,E.SAL,E.HIREDATE,D.DNAMEFROMEMPE,DEPTDWHEREE.DEPTNO=D.DEPTNOANDD.DNAME='SALES';列出薪金高于公司平均薪金的所有員工,所在部門,,公司的工資等級.emp.emp.dept.emp表：信息.salgrade雇員和工資等級：emp.salbetweensalgrade.losalandsalgradehisal；SELECTAVG(SAL)FROM第二步：where子句之中，作為一個查詢的條件，求出滿足此SELECTE.ENAMEFROMEMPEWHEREE.SAL>(SELECTAVG(SAL)FROMEMP第三步：找到部門名SELECTE.ENAME，D.DNAMEFROMEMPE,DEPTDWHEREE.SAL>(SELECTAVG(SAL)FROMEMP)ANDE.DEPTNO=D.DEPTNO;第四步：找到的信SELECTE.ENAME，D.DNAME,M.ENAMEFROMEMPE,DEPTD，EMPMWHEREE.SAL>(SELECTAVG(SAL)FROMEMP)ANDE.DEPTNO=D.DEPTNOAND第五步：找到工資等SELECTE.ENAME，D.DNAME,M.ENAME,S.GRADEFROMEMPE,DEPTD，EMPM，SALGRADESWHEREE.SAL>(SELECTAVG(SAL)FROMEMP)ANDE.DEPTNO=D.DEPTNOANDE.MGR=M.EMPNO(+)ANDE.SALBETWEENS.LOSALAND10.列出與”SCOTT”從事相同工作的所有員工及部門的名稱,部門人數(shù).empSCOTT.emp.dept.dept第一步：SCOTT的工作SELECTJOBFROMEMPWHERE第二步：WHERE子中出現(xiàn)，以這個SELECTE.ENAMEFROMEMPEWHEREE.JOB=(SELECTJOBFROMEMPWHEREENAME='SCOTT')ANDE.ENAME<>'SCOTT';第三步：找到部門名SELECTE.ENAME,D.DNAMEFROMEMPE,DEPTDWHERESELECTJOBFROMEMPWHEREENAME='SCOTT')ANDE.DEPTNO=D.DEPTNOANDSELECTE.ENAMEFROMEMPE,DEPTD,(SELECTDEPTNODNO,COUNT(EMPNO)COUNTFROMEMPGROUPBYDEPTNO)TEMPWHERESELECTJOBFROMEMPWHEREENAME='SCOTT')ANDE.DEPTNO=D.DEPTNOANDE.ENAME<>'SCOTT'ANDE.DEPTNO=TEMP.DNO;列出公司各個工資等級雇員的數(shù)量、平均工.emp.salgrade②確定已知的關(guān)聯(lián)字段：emp.salbetweensalgrade.losalandsalgradeSELECTS.GRADE,S.LOSAL,S.HISAL,FROMEMPE,SALGRADEWHEREE.SALBETWEENS.LOSALANDS.HISALGROUPBYS.GRADE,S.LOSAL,S.HISAL;列出薪金高于在部門30工作的所有員工的薪金的員工和薪金，部門名.emp30.emp表：最終顯示的雇員.deptSELECTSALFROMEMPWHERE第二步：高于30部門，使用ALL操作符，引入emp表，查詢和薪SELECTE.ENAME,E.SALFROMEMPEWHERESELECTSALFROMEMPWHERE第三步：找到部門名SELECTE.ENAME,E.SAL,D.DNAMEFROMEMPE,DEPTDWHERESELECTSALFROMEMPWHEREDEPTNO='30')ANDE.DEPTNO=D.DEPTNO;列出在每個部門工作的員工數(shù)量，平均工資和平均服務(wù)期.dept直接將deptemp表關(guān)聯(lián)，使用多字段分組即可，但是對于服務(wù)年限需要一個計算SELECTAVG(MONTHS_BETWEEN(SYSDATE,E.HIREDATE)/12)FROMEMPE,DEPTDWHEREE.DEPTNO(+)=GROUPBY列出所有員工的,部門名稱和工SELECTE.ENAME,D.DNAME,E.SALFROMEMPE,DEPTDWHERE列出所有部門的詳細(xì)信息和部門人SELECTD.DEPTNO,D.DNAME,D.LOC,COUNT(E.EMPNO)FROMEMPE,DEPTDWHEREE.DEPTNO(+)=GROUPBYD.DEPTNO,D.DNAME,SELECTEMPGROUPBYDEPTNO)TEMPWHERE列出各種工作的最低工資及從事此工作的雇.emp.emp表：查找雇員第一步：按照職位統(tǒng)計各個職位的SELECTJOB,MIN(SAL)FROMEMPGROUPBYJOBSELECTE.ENAME,TEMP.JOB,FROMEMPE,(SELECTJOB,MIN(SAL)SALFROMEMPGROUPBYJOB)TEMPWHEREE.JOB=TEMP.JOBANDE.SAL=列出各個部門的MANAGER(經(jīng)理)的最低薪金、、部門名稱、部門人數(shù).emp表：找到經(jīng)理的薪金、.dept.emp第一步：找到所有部門經(jīng)理的最低SELECTDEPTNO,MIN(SAL)FROMEMPWHEREJOB='MANAGER'GROUPBY第二步：找到，但是以上的子查詢，不能再出現(xiàn)其他的字SELECTFROMEMPE,(SELECTDEPTNODNO,MIN(SAL)SALFROMEMPWHEREJOB='MANAGER'GROUPBYDEPTNO)TEMPWHEREE.DEPTNO=TEMP.DNOANDE.SAL=TEMP.SALAND第三步：加入部門的SELECTEMPWHEREJOB='MANAGER'GROUPBYDEPTNO)TEMPWHEREE.DEPTNO=TEMP.DNOANDE.SAL=TEMP.SALAND第四步：統(tǒng)計部門人SELECTEMPWHEREJOB='MANAGER'GROUPBYDEPTNO)TEMP,(SELECTDEPTNODNO,COUNT(EMP