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表，执行下述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)语句正常执行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