Oracle基礎查詢關聯查詢練習題

1、實用文檔1 Oracle基礎查詢綜合示例有職員表emp,表結構如表一1所示:表一1職員表emp信息字段名類型描述empnoNUMBER(4.0).ZIDenameVARCHAR2(10)員:jobVARCHAR2(9)職位mgrNUMBERS，。）員工管運者的IDhir&dateDATE入職日期salNUMBER1薪資commNUMBERM教deptnoNUMBER(2.0)員/恒的部門IDemp表中的示例數據如圖一1所示:EMPNO JEMAME kJOBMGR 一HIRE DATE-J salCOMM IDEPTNO7369SMITHCLERK79021980/W17*800.002074

2、99ALLENSALESMAN769Z1981/2/20T160000300.00307521WARDSALESMAN76931鮑1儂2-1250.0GSOO.OG307566JONESMANAGER783s1931/4/2*297500207654MAA TINSALESMAN7G931931/9/28F1250001400.00307698BLAKEMANAGER7B391931/5/1T2860.00307732CLARKMANAGER78351 騏 W9 -2450.00107733SCOTTANALYST75661987/4/19，3000.00207833KINGPRESIDENT

3、1981/11/17V5000.00107的4TURNERSALESMAN181/9/8T15000G000307876ADAMSCLERK77881987/5/231100.00207900JAMESCLERK7&S31981/12/3,r950.00307502FORDANALYST7SG61931/12/3T3000.00207934MILLERCLERK77821982/1/23 1300.00W圖一1有部門表dept,表結構如表一2所示:表一2部門表dept信息字段名類型描述deptnoNUMBER(2.O)部門DdnameVARCHAR2(14)部門名稱locVARCHAR2(13

4、)部門所在地實用文檔dept表中的示例數據如圖一2所示:DEPTNODNAMELOG10ACCOUNTINGNEW YORK20RESEARCHDALLAS30SALESCHICAGO40OPERATIONSBOSTON圖2需要完成如下查詢：1、查詢職員表中，在20和30號部門工作的員工姓名和部門號。select ename, deptno from empwhere deptno in (20,30);2、查詢職員表中，沒有管理者的員工姓名及職位，并按職位排序。select ename, job from empwhere mgr is nullorder by job;3、查詢職員表中，有

5、績效的員工姓名、薪資和績效，并按工資倒序排列。select ename, sal, comm from emporder by sal desc;4、查詢職員表中，員工姓名的第三個字母是A的員工姓名。select ename from empwhere ename like a%;5、查詢職員表中的職員名字、職位、薪資，并顯示為如圖一3所示效果：select ename|,|job|,|sal out_put from emp;實用文檔Uli OUT FUTSMITHrCLEBKr 800ATLENr SALESMflNr l00WARD,SALESMfiN,12501 JONES,MANAG

6、ER,2975MflRTIN, SALESMAN, 1250BLAKE r MflNAGERr 2350CLARK, MANAGER r 2 4 5 0SCOTT,ANALYST,3000KINGrPRESILEinr5000TURNER, SALESMMI, 1500ADAMS rCLEBKr 1100JAMES rCLEBKr 550FORDrANALYSTr 30001； MILLER,CLERKr 1300圖3提示：列之間用逗號連接，列頭顯示成OUT_PUT。6、查詢職員表中員工號、姓名、工資，以及工資提高百分之20%后的結果。select empno, ename, sal,sal*

7、1.2 from emp;7、查詢員工的姓名和工資，條件限定為：工資必須大于1200,并對查詢結果按 入職時間進行排列，早入職排在前面，晚入職排在后面。select ename, sal from empwhere sal1200order by hiredate asc;8、查詢ACCOUNT部門以外的其他部門的編號、名稱以及所在地。select deptno, dname, loc from deptwhere dname not in accounting;實用文檔Oracle分組查詢綜合示例對于如前所示的職員表emp,需要完成如下查詢：1、查詢每個部門中每個職位的最高薪水。select

8、 max(sal),deptno,job from empgroup by deptno,job;2、有SQL語句如下所示：SELECT a.ename, a.sal, a.deptno, b.maxsalFROM emp a,(SELECT deptno, max(sal) maxsalFROM empGROUP BY deptno) bWHERE a.deptno = b.deptnoAND a. sal any(e2.sal)and e1.mgr = e2.empno;實用文檔4、找出EMP中那些工資高于他們所在部門普通員工平均工資的員工。select e.ename, e.sal, e

9、.deptno, d.avgsalfrom emp e,(select deptno ,avg(sal) avgsalfrom empgroup by deptno)dwhere e.sald.avgsal and e.deptno= d.deptno;Oracle分組查詢綜合示例(提高題，選做)1、下列SQL語句出錯的原因是(B)。SELECT classid, AVG(MONTHS_BETWEEN(SYSDATE,entertime) FROM studentWHERE AVG( MONTHS_BETWEEN (SYSDATE, entertime)12GROUP BY classidOR

10、DER BY AVG(MONTHS_BETWEEN (SYSDATE, entertime);select短語中不能出現組函數。where短語中不能限制分組結果。order by子句中不能包含組函數。D)組函數中不能包含單行函數。2、有學員表Student,該表的結構如表一3所示：表- 3學員表Student信息實用文檔字段名類型描述idNUMBER)學一言 IDNot NullnameVARCHAR2(3O)學員姓名r Not NullsubjectVARCHAR2(3D)科目scoreNUMBER 25classidNUMBER 班級桑謗編寫SQL語句，查詢每班中每個科目的最高成績。sel

11、ect classid, subject, max (score) from student group by classid, subject;3、針對表一3所示的Student表，執(zhí)行下述SQL語句：SELECT , a.score, a.classid, b.avgscoreFROM student a,(SELECT classid, avg(score) avgscoreFROM studentGROUP BY classid) bWHERE a.classid = b.classidAND a.score b. avgscore;關于運行結果，下列描述正確的是(D)。A

12、)第一行出現錯誤B)第三行出現錯誤C)第六行出現錯誤D)語句正常執(zhí)行4 Oracle關聯查詢綜合示例實用文檔1、有職員表emp,若需列出所有薪水高于平均薪水值的員工信息，則有SQL語 句如下：SELECT ename, job FROM emp WHERE sal avg(sal);上述語句是否正確？如果有錯，寫出正確的SQL語句。select a.ename, a.job from emp a,(select avg(sal) sal from emp)bwhere a.salb.sal ;2、有學員分數表，如圖一5所示:unmefenshu張三語文81后噲75李四語文76李四造90-l-f

13、l語文S1數學。0英語90圖5編寫一條SQL語句，查詢出每門課都大于80分的學生姓名。select name count (*)from studentwhere fenshu 80group by name實用文檔having count (*)=3；select distinct Sname from S where Sname not in (select distinct Sname from S where grade=80)3、有USERLIST表如圖一6所示：TELEPHONE ( V archar (10)，十 鍵JACCOUNT (vaiehar (10)RENT (nuif

14、leiic (10, 2)4210001A AA A19, 504210002A AAA20. 501210003EBBE00- 00-1210001-cccc-25。一 00圖一6有CHAEGE表如圖一7所示:IsLEPMOSiE：FEE01FEE02FEWE421D0O111.QD13.00421DDO221.0D32.00236 口24.00421D03331.0D32.0D33.D034.00圖一7請用最少的SQL語句，產生如表一4所示的查詢結果: 表一4查詢結果實用文檔ACCOUNTUSERSRENTFEE01FEE02FEE03FEE04AAAA240.0032.0034.0036

15、.0038.00BBBB1100.0031.0032.0033.0034.00CCCC1250.000.000.000.000.00其中，數據是經過USERLIST、CHAEGE表進行合適的連接，并以ACCOUNT字段 為關鍵字分組求和得到。特別注意：電話號碼421004在USERLIST表中有一條記錄，在CHARGE表中并 沒有記錄。但是，在查詢結果中，合同CCCC具有一條記錄。4、有兩個表emp和taxgrade，其字段分別為：emp （員工）表：empname， empno， saltaxgrade （稅別）表： taxmin，taxmax， grade上述字段中，除字段empname外

16、，其他字段均為數值類型。emp表的數據如表一5所示：表一5 emp表示例數據empnameempnosalmary1450John2800jerry31250kate-200neo55750taxgrade表的數據如表一6所示:表一6 taxgrade表示例數據實用文檔taxmintaxmaxgrade0500L5001000210002000320005000-5000100005編寫SQL語句，查詢編號為1的員工的稅別。select grade from taxgradewhere taxmax(select sal from empwhere empno = 1;);5、有學員表stud

17、ent,用于記錄：學號，姓名，性別，年齡，組織部門；有課 程表course，用于記錄：課程編號，課程名稱；還有選課表sc，用于記錄：學 號，課程編號，成績。三表的結構以及關聯如圖一8所示：實用文檔圖一8完成如下要求的SQL語句：1)寫一個SQL語句，查詢選修了計算機原理的學生學號和姓名select Sno, Sname from studentwhere Sno in (select Sno from SCwhere cno = (select cno from Coursewhere cname =計算機原理);2)寫一個SQL語句，查詢周星馳同學選修了的課程名字select cname f

18、rom Coursewhere cno in (select cno from Scwhere Sno =(select Sno from Studentwhere Sname =周星馳);6、有表test，表結構如表一7所示：表一7 test表實用文檔IDNAMEAGEMAN AGER，：祈屬主管人ID)106A30104109B19104104C20111107D35109112E25120119F45NULL編寫SQL語句，查詢所有年齡比所屬主管年齡大的人的ID和NAME。select a. ID ,a. NAME from test a join test bon a.MANAGER

19、= b. IDwhere a.AGE b.AGE;7、有表city記載城市信息，如表一8所示：表一8 city表CrtyNoCrtyNameStateNoBJd驚(Null)SH上海(NullGZ廣州GDDL注LN有表state,記載省份信息，如表一9所示:表一9 state 表StateNoStsteNameGDLN遼寧SD山東NMG內蒙古欲得到如表一10所示的查詢結果:實用文檔表一10查詢結果CityNoCityNameStat&NoStateNameBUd原t Null )(Null )DL在LN遼寧GZ廣州GDSH上海(Null )(Null )請編寫相應的SQL語句。select CityNo, CityName, StateNo, StateName fromcity c full join state swhere c.StateNo = s.StateNo;5 Oracle關聯查詢綜合示例（提高題，選做）有科目表，記載學員的學習科目數據，如表一11所示:表一11 t_subject表（科目表）siibject_idsiibject_msme