Oracle基础练习题及答案（多表查询1）（共5篇）

Oracle基础练习题及答案（多表查询1）（共5篇）第一篇：Oracle基础练习题及答案(多表查询1)利用scott用户自带的四张表完成如下作业：1.列出至少有一个员工的所有部门selectb.deptno,b.dnamefromempa,deptbwherea.deptno=b.deptnogroupbyb.deptno,b.dnamehavingcount(*)>=1;2.列出薪金比SMITH高的所有员工select*fromempwheresal>(selectsalfromempwhereename='SMITH');3.列出所有员工的姓名及其直接上级领导的姓名selecta.ename,b.ename“leader”fromempa,empbwherea.mgr=b.empno;4.列出受雇日期早于其直接上级的所有员工的编号，姓名，部门名称selecta.empno,a.ename,a.hiredate,c.dnamefromempa,empb,deptcwherea.mgr=b.empnoanda.deptno=c.deptnoanda.hiredate5.列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门selectb.dname,a.*fromempa,deptbwherea.deptno(+)=b.deptno;6.列出所有CLERK(办事员)的姓名，及其部门名称，部门人数selectaa.ename,aa.job,bb.dname,(selectcount(a.deptno)fromempa,deptbwherea.deptno=b.deptnoandb.dname=bb.dnamegroupbya.deptno)fromempaa,deptbbwhereaa.deptno(+)=bb.deptnoandaa.job='CLERK';7.列出最低薪金大于1500的各种工作及从事此工作的全部雇员人数selecta.job,min(sal),count(ename)fromempa,deptbwherea.deptno=b.deptnohavingmin(sal)>1500groupbya.job;8.列出在部门SALES(销售部)工作的员工的姓名，假定不知道销售部的部门编号。selectenamefromempa,deptbwherea.deptno=b.deptnoanddname='SALES';9.列出薪金高于公司平均薪金的所有员工，所在部门，上级领导，公司的等级工资selecta.ename,dname,aa.ename“Leader”,gradefromempa,deptb,empaa,salgradeswherea.deptno=b.deptnoanda.mgr=aa.empnoanda.sal>(selectavg(sal)fromemp)anda.salbetweenlosalandhisal;10.列出与SCOTT从事相同工作的所有员工的编号，姓名，职位及其部门名称selectempno,ename,job,dnamefromempa,deptbwherea.deptno=b.deptnoandjob=(selectjobfromempwhereename='SCOTT');11.列出薪金等于部门30中员工的薪金的所有员工的姓名和薪金。selectename,salfromempwheresal=any(selectsalfromempwheredeptno=30);12.列出薪金高于在部门30工作的所有员工的薪金的员工姓名、薪金和部门名称selectename,sal,dnamefromempa,deptbwherea.deptno=b.deptnoandsal>all(selectsalfromempwheredeptno=30);13.列出在每个部门工作的员工数量、平均工资和平均服务期限selectdname,count(ename),avg(sal),avg(sysdate-a.hiredate)fromempa,deptbwherea.deptno(+)=b.deptnogroupbya.deptno,dname;14.列出所有员工的姓名、部门名称和工资selectename,dname,salfromempa,deptbwherea.deptno=b.deptno;15.列出所有部门的详细信息和部门人数selectb.deptno,dname,loc,count(ename)fromempa,deptbwherea.deptno(+)=b.deptnogroupbyb.deptno,dname,loc;16.列出各种工作的最低工资及从事此工作的雇员姓名selectb.ename,c“MINSAL”,a.jobfrom(selectjob,min(sal)cfromempgroupbyjob)a,empbwherea.job=b.jobandc=b.sal;17.列出各个部门的MANAGER(经理)的最低薪金selectdeptno,min(sal)fromempwherejob='MANAGER'groupbydeptno;18.列出员工的年工资，按年薪从低到高排序selectename,(sal+nvl(comm,0))*12afromemporderbya;19.查出某个员工的上级主管，并要求出这些主管中的薪水超过3000selecta.ename,b.ename,b.sal“bosssal”fromempa,empbwherea.mgr=b.empnoandb.sal>=3000;第二篇：Oracle基础练习题及答案(子查询)子查询1.查询和scott相同部门的员工姓名ename和雇用日期hiredateselectename,hiredatefromempwheredeptno=(selectb.deptnofromempa,deptbwherea.deptno=b.deptnoandename='SCOTT');2.查询工资比公司平均工资高的所有员工的员工号empno，姓名ename和工资sal。selectempno,ename,salfromempwheresal>(selectavg(sal)fromemp);3.查询和姓名中包含字母u的员工在相同部门的员工的员工号empno和姓名enameselectempno,enamefromempa,deptbwherea.deptno(+)=b.deptnoanda.deptno=(selectdeptnofromempwhereenamelike'%U%');4.查询在部门的loc为newYork的部门工作的员工的员工姓名ename，部门名称dname和岗位名称jobselectename,dname,jobfromempa,deptbwherea.deptno=b.deptnoandloc='NEWYORK';查询管理者是king的员工姓名ename和工资salselecta.ename,a.salfromempa,empbwherea.mgr=b.empnoandb.ename='KING';第三篇：SQL语句练习题(包含有多表查询)-答案SQL语句练习题1、请从表EMP中查找工种是职员CLERK或经理MANAGER的雇员姓名、工资。selectename,salfromempwherejob='CLERK'orjob='MANAGER';2、请在EMP表中查找部门号在10－30之间的雇员的姓名、部门号、工资、工作。selectename,deptno,sal,jobfromempwheredeptnobetween10and30;3、请从表EMP中查找姓名以J开头所有雇员的姓名、工资、职位。selectename,sal,jobfromempwhereenamelike'J%';4、请从表EMP中查找工资低于2000的雇员的姓名、工作、工资，并按工资降序排列。selectename,job,salfromempwheresal<=2000orderbysaldesc;5、请从表中查询工作是CLERK的所有人的姓名、工资、部门号、部门名称以及部门地址的信息。selectename,sal,emp.deptno,dname,locfromemp,deptwhereemp.deptno=dept.deptnoandjob=’CLERK’;6、查询表EMP中所有的工资大于等于2000的雇员姓名和他的经理的名字。selecta.ename,b.enamefromempa,empbwherea.mgr=b.empno(+)anda.sal>=2000;7、查询所有雇员的姓名、SAL与COMM之和。selectename,sal+nvl(comm,0)“sal-and-comm”fromemp;8、查询所有81年7月1日以前来的员工姓名、工资、所属部门的名字selectename,sal,dnamefromemp,deptwhereemp.deptno=dept.deptnohiredate<=to_date(‘1981-07-01’,’yyyy-mm-dd’);9、查询列出来公司就职时间超过24年的员工名单selectenamefromempwherehiredate<=add_months(sysdate,-288);10、查询于81年来公司所有员工的总收入（SAL和COMM）selectsum(sal+nvl(comm,0))fromempwhereto_char(hiredate,’yyyy’)=’1981’;11、查询显示每个雇员加入公司的准确时间，按××××年××月××日时分秒显示。selectename,to_char(hiredate,'yyyy-mm-ddhh24:mi:ss')fromemp;12、查询列出各部门的部门名和部门经理名字selectdname,enamefromemp,deptwhereemp.deptno=dept.deptnoandjob=’MANAGER’;and第四篇：黑马程序员c语言教程：多表查询笛卡尔积部门表笛卡尔积产生结果:行数两个表相乘列数:行数相加原因条件等值连接select****fromtab1,tab2wheretab1.a=tab2.aselectcount(e.ename)fromempe,deptd3*wheree.deptno=d.deptnoSQL>/COUNT(E.ENAME)--------------多表查询关键点:等值连接eg:查询员工信息,员工号,姓名,月薪,部门名称selecte.empno,e.ename,e.sal,d.dnamefromempe,deptd3*wheree.deptno=d.deptnoSQL>/EMPNOENAMESALDNAME--------------------------------------------7369SMITH800RESEARCH7499ALLEN1600SALES7521WARD1250SALES7566JONES2975RESEARCH7654MARTIN1250SALES7698BLAKE2850SALES7782CLARK2450ACCOUNTING7788SCOTT3000RESEARCH7839KING5000ACCOUNTING7844TURNER1500SALES7876ADAMS1100RESEARCH7900JAMES950SALES7902FORD3000RESEARCH7934MILLER1300ACCOUNTING已选择14行。不等值连接:eg:查询员工信息,员工号,姓名,月薪,薪水级别selecte.empno,e.ename,e.sal,s.gradefromempe,salgradeswheree.sal>=s.losalande.sal<=s.hisalselecte.empno,e.ename,e.sal,s.gradefromempe,salgradeswheree.salbetweens.losalands.hisal外连接eg:按部门统计员工人数:部门号部门名称各部门人数分析1:102030====>分组分析2:因为各部门人数是在员工表中..多表查询步骤1selectd.deptno,d.dname,count(e.empno)fromdeptd,empewhered.deptno=e.deptnogroupbyd.deptno,d.dnameDEPTNODNAMECOUNT(E.EMPNO)-------------------------------------ACCOUNTING3RESEARCH5SALES6步骤2外连接....分析:为什么40号部门没有统计出来原因:因员工表里面没有40号部门的员工(现象)whered.deptno=e.deptno(sql)问题的本质40=====>期望:在连接条件不成立的条件下,也要把部门编号40给显示出来....外连接...selectd.deptno,d.dname,count(e.empno)fromdeptd,empewhered.deptno=e.deptno(+)groupbyd.deptno,d.dname左外连接:(+)写在=号的右边自连接:--查询员工信息,老板信息显示:****的老板是****自连接:把一张表看成两张表,自连接--员工表的老板是老板表的员工selecte.ename,b.enamefromempe,empbwheree.mgr=b.empnoselecte.ename||'的老板是'||b.enamefromempe,empbwheree.mgr=b.empno====>需求把员工表的每一条记录都显示出来selecte.ename||'的老板是'||b.enamefromempe,empbwheree.mgr=b.empno(+)selecte.ename||'的老板是'||nvl(b.ename,'他自己')fromempe,empbwheree.mgr=b.empno(+)SMITH的老板是FORDMPNOENAMEJOBMGRHIREDATESALCOMMDEPTNO----------------------------------------------------------------------------7369SMITHCLERK790217-12月-80800207499ALLENSALESMAN769820-2月-811600300307521WARDSALESMAN769822-2月-811250500307566JONESMANAGER783902-4月-812975207654MARTINSALESMAN769828-9月-8112501400307698BLAKEMANAGER783901-5月-812850307782CLARKMANAGER783909-6月-812450107788SCOTTANALYST756619-4月-873000207839KINGPRESIDENT17-11月-815000107844TURNERSALESMAN769808-9月-8115000307876ADAMSCLERK778823-5月-871100207900JAMESCLERK769803-12月-81950307902FORDANALYST756603-12月-813000207934MILLERCLERK778223-1月-8213001014行。第五篇：查询练习题《网络数据库》讲稿查询练习题简单的单表查询例1：检索出1983年6月1日之后出生的学生的姓名、性别和民族，并按降序排列。SELECT姓名,性别,民族FROM学生WHERE出生日期>'1983-6-1'ORDERBY出生日期DESC多表查询在FROM后跟随多个表。例1：查找出已修学分达到20分的学生的姓名、出生日期、班级名称、已修学分。SELECT姓名,出生日期,班级名称,已修学分FROM学生,班级WHERE学生.班级编号=班级.班级编号AND已修学分>=20解释：这里的“学生.班级编号=班级.班级编号”是连接条件。如果在FROM短语中有两个表，那么这两个表必须具备可建立联系的字段，否则无法查询。如果FROM短语中的多个表中有同名字段，当使用这些字段名时，需要在字段名左边加上表名和圆点，作为字段名的前缀。练习题01：查找出已修学分低于20分的学生的姓名、性别和班主任。练习题02：查找出学生“王林”的班主任。练习题03：查找出班主任“刘成河”班的全部男生的信息。例2：查找出课程“中国历史”考试及格的全部学生的学号、姓名。SELECT学生.学号,姓名FROM学生,课程,成绩WHERE分数>=60AND课程名称='中国历史'AND学生.学号=成绩.学号AND成绩.课程编号=课程.课程编号练习题04：查找出课程“中国历史”考试及格的全部学生的学号、姓名、班级名称和分数。练习题05：查找出全部同学的所有考试的姓名、课程名称、分数，查找结果的格式如下：嵌套查询P121-126例1：查找出取得过95分及其以上考试成绩的学生的学号和姓名。SELECT学号,姓名FROM学生WHERE学号IN(SELECT学号FROM成绩WHERE分数>=95)解释：该命令中的IN相当于集合运算的包含运算符∈。括号外的查询称为外层查询；括号内的查询称为内层查询(也称为“子查询”)。内层查询的结果被当做是一个集合。P104例2：查找出“计算机应用”课程考试取得95分及其以上考试成绩的学生的学号和姓名。SELECT学号,姓名FROM学生WHERE学号IN(SELECT学号FROM成绩WHERE分数>=95AND课程编号IN(SELECT课程编号FROM课程WHERE课程名称='计算机应用'))例3：查找出所有学生的考试都及格的班级名称。答案一：SELECT班级名称FROM班级WHERE班级编号NOTIN(SELECT班级编号FROM学生WHERE学号IN(SELECT学号FROM成绩WHERE分数<60))答案二：select班级名称from班级wherenotexists(select*from学生whereexists(select*from成绩where分数<60and班级.班级编号=学生.班级编号and成绩.学号=学生.学号))答案三：select班级名称from班级wherenotexists(select*from成绩,学生where班级.班级编号=学生.班级编号and学生.学号=成绩.学号and分数<60)练习题01：查找出班主任“刘成河”班的全部男生的信息。练习题02：查找出考试全及格的课程名称。练习题03：查找出所有学生的考试都及格的班级，并排除那些没有学生的班级。练习题04：使用多表查询实现例1。练习题05：使用多表查询实现例2。练习题06：查找出“数学专业”班全体同学的姓名和“数学”课程考试分数。三个特殊运算符一、谓词EXISTSP122EXISTS判断子查询中是否为空集。例1：查找出那些还没有学生的班级名称。SELECT班级名称FROM班级WHERENOTEXISTS(SELECT*FROM学生WHERE学生.班级编号=班级.班级编号)例2：检索出每个学生都选修了的课程(并且考试都及格)。SELECT课程编号,课程名称FROM课程WHERENOTEXISTS(SELECT学号FROM学生WHERENOTEXISTS(SELECT*FROM成绩WHERE课程.课程编号=成绩.课程编号AND学生.学号=成绩.学号AND分数>=60))练习题01：写出可实现例1要求的其他命令。练习题02：写出可实现例2要求的其他命令。二、范围运算符BETWEEN…AND…P103该范围是一个闭区间，意思是在…和…之间，包括两个端点的值。例3：查找出1983年6月1日至1984年6月1日之间出生的学生的信息。SELECT*FROM学生WHERE出生日期BETWEEN'1983-6-1'AND'1984-6-1'练习题03：查找出已修学分在15和20之间的学生的信息。练习题04：查找出1983年6月1日至1984年6月1日之间出生的少数民族学生的信息。练习题05：查找出已修学分在15和20之间、且“自然辨证法”课程的考试分数达到80分的学生的姓名、性别、所在班级。三、字符串匹配运算符LIKEP105例4：查找出姓名中包含有“林”的学生的信息。SELECT*FROM学生WHERE姓名LIKE'%林%'例5：查找出姓名中第二个字是“林”的学生的信息。SELECT*FROM学生WHERE姓名LIKE'_林%'练习题06：查找出湖北出生的学生的信息。练习题07：查找出武汉出生的学生的信息。练习题08：查找出班主任“郭大壮”的班中出生地是武汉的学生的姓名、班级。练习题09：查找出至少参加了辛有余老师所任课程中一门课程考试的男生的姓名。至少写出两条不同的命令。聚合函数及其分组查询为了完成后续题目，请首先计算出每位同学的已修学分UPDATE学生SET已修学分=(SELECTSUM(学分)FROM成绩,课程WHERE学生.学号=成绩.学号AND分数>=60AND成绩.课程编号=课程.课程编号)一、常用统计(聚合)函数P109表4-3例1：计算出全部学生的平均已修学分。SELECTAVG(已修学分)FROM学生例2：计算出“软件工程”班全体同学的平均已修学分。SELECTAVG(已修学分)FROM学生WHERE班级编号IN(SELECT班级编号FROM班级WHERE班级名称='软件工程')练习题01：计算出“软件工程”和“计算机应用”两个班全体同学的平均已修学分。注意：当子查询的结果只有一个数据时，可以用等号；为多个数据时，需用IN。练习题02：计算出李建同学各门课程考试总分。例3：查找出“数学专业”班“数学”课程考试的最高分数。SELECTMAX(分数)FROM学生,成绩WHERE学生.学号=成绩.学号AND班级编号=(SELECT班级编号FROM班级WHERE班级名称='数学专业')AND课程编号=(SELECT课程编号FROM课程WHERE课程名称='数学')另一个答案SELECTMAX(分数)FROM成绩WHERE学号IN(SELECT学号FROM学生WHERE班级编号=(SELECT班级编号FROM班级WHERE班级名称='数学专业'))AND课程编号=(SELECT课程编号FROM课程WHERE课程名称='数学')例4：查找出“数学专业”班“数学”课程考试最高得分的同学姓名和分数。SELECT姓名,分数FROM学生,成绩WHERE学生.学号=成绩.学号AND班级编号=(SELECT班级编号FROM班级WHERE班级名称='数学专业')AND课程编号=(SELECT课程编号FROM课程WHERE课程名称='数学')AND分数=(SELECTMAX(分数)FROM学生,成绩WHERE学生.学号=成绩.学号AND班级编号=(SELECT班级编号FROM班级WHERE班级名称='数学专业')AND课程编号=(SELECT课程编号FROM课程WHERE课程名称='数学'))附：TOP…PERCENTP98二、GROUPBY短语GROUPBY后面跟随的是分组关键字段，按照关键字段的值，将记录分成若干个组，每一组转变为查询结果中的一行。例5：计算出各班学生的平均已修学分。SELECT班级编号,AVG(已修学分)FROM学生GROUPBY班级编号练习题04：计算出各门课程考试的平均分数。练习题05：计算出各位学生的考试总分。练习题06：计算出辛有余老师所任课程中每门课程的课程编号和学生平均考试分数。三、别名及其应用P113-114,P99例6：查找出已修学分低于本班平均已修学分的学生信息。SELECT姓名,班级编号,已修学分FROM学生WHERE已修学分说明：在FROM短语中为表取别名也可省略AS，例如FROM学生XS。例7：查找和计算出各位学生的姓名和考试总分。SELECT姓名,总分FROM学生,(SELECT学号,SUM(分数)AS总分FROM成绩GROUPBY学号)ASZFWHERE学生.学号=ZF.学号说明：在FROM短语中可用子查询的结果作为表，此时，必须为该子查询取别名。在SELECT短语中可为查询结果列取别名，其格式有三种，见P99。练习题07：查找和计算出各门课程的名称及其考试的平均分数。练习题08：查找和计算出各门课程的名称及其考试达到90分的人数。四、HAVING短语HAVING后面跟随的是筛选条件，作用是筛选满足条件的组，即在分组之后过滤数据，条件中经常包含统计(聚合)函数。例8：查找出讲授多门课程的教师姓名及其授课门数。SELECT任课教师,COUNT(*)AS授课门数FROM课程GROUPBY任课教师HAVINGCOUNT(*)>1说明：HAVING短语通常与GROUPBY短语配合使用，否则无使用价值。HAVING短语中使用的统计函数应该是SELECT短语中使用的函数。练习题10：查找出考试成绩优秀人数多于3人的课程编号及其学生人数(假定考试分数达到90分为优秀)。练习题11：查找出考试成绩优秀人数多于3人的课程名称及其学生人数(假定考试分数达到90分为优秀)。练习题12：查找出考试成绩优秀人次多于5人次的班级名称及其人次(假定考试分数达到90分为优秀)。练习题13：查找出考试分数标准差小于10、且最高分与最低分之差小于20的各班(班级名称)各门课程(课程名称)的考试人数，考试分数的最高分、最低分、平均分、标准差、方差。常用函数、连接一、常用函数P159二、链接P114用于多表查询，实现表之间的链接。例6：在多表查询中，下面的命令可查找出已修学分达到17分的学生的姓名、出生日期和班级：SELECT姓名,出生日期,班级名称FROM学生,班级WHERE学生.班级编号=班级.班级编号AND已修学分>=17用INNERJOIN…ON…，可将该命令改写为：SELECT姓名,出生日期,班级名称FROM学生INNERJOIN班级ON学生.班级编号=班级.班级编号WHERE已修学分>=17或SELECT姓名,出生日期,班级名称FROM学生INNERJOIN班级ON学生.班级编号=班级.班级编号AND已修学分>=17解释：这里的“学生.班级编号=班级.班级编号”是连接条件。如果在FROM短语中有两个表，那么这两个表必须具备可建立联系的字段，否则无法查询。如果FROM短语中的多个表中有同名字段，当使用这些字段名时，需要在字段名左边加上