#练习一 1、建立学员表stuInfo和学员成绩表stuMarks 2、向两个表中分别录入信息 ![图片描述](https://dn-simplecloud.shiyanlou.com/courses/uid1970911-20220219-1645280475830) ![图片描述](https://dn-simplecloud.shiyanlou.com/courses/uid1970911-20220219-1645280505699) -- 1.查询两表的数据 select * from stuinfo; select * from stumarks; ![图片描述](https://dn-simplecloud.shiyanlou.com/courses/uid1970911-20220219-1645280423421) ![图片描述](https://dn-simplecloud.shiyanlou.com/courses/uid1970911-20220219-1645280447282) -- 2.查询男学员名单 select * from stuinfo where STUSEX LIKE '男'; ![图片描述](https://dn-simplecloud.shiyanlou.com/courses/uid1970911-20220219-1645280554094) -- 3.查询笔试成绩优秀的学员情况（成绩在75~100之间) select * from stumarks where writtenExam BETWEEN 75 AND 100; ![图片描述](https://dn-simplecloud.shiyanlou.com/courses/uid1970911-20220219-1645280676694) -- 4.显示学员信息，地址没有填写的显示“地址不详” select stuname,stuno,stusex,stuage,IFNULL(stuaddress,'地址不详')as "stuAddress" from stuinfo; ![图片描述](https://dn-simplecloud.shiyanlou.com/courses/uid1970911-20220219-1645281196721) -- 5.统计笔试考试平均分和机试考试平均分？空值问题 select AVG(writtenExam) 笔试平均分, AVG(labExam) 机试平均分 FROM stumarks ; ![图片描述](https://dn-simplecloud.shiyanlou.com/courses/uid1970911-20220219-1645281485723) -- 6.统计参加本次考试的学员人数 select count(stuNo) as 考试人数 from stumarks ![图片描述](https://dn-simplecloud.shiyanlou.com/courses/uid1970911-20220219-1645281634130) -- 7.查询没有通过考试的人数（笔试或机试小于60分）？空值问题 select count(stuNo) as 未通过的人数 from stumarks where labExam<60 or writtenExam<60 ![图片描述](https://dn-simplecloud.shiyanlou.com/courses/uid1970911-20220219-1645281755165) -- 8.查询学员成绩，显示学号，笔试成绩，机试成绩，平均分 select stuNO as 学号,writtenExam as 笔试,labExam as 机试,(writtenExam+labExam)/2.0 as 平均分 from stumarks ![图片描述](https://dn-simplecloud.shiyanlou.com/courses/uid1970911-20220219-1645281845879) -- 9.排名次（按平均分从高到低排序），显示名次、学号、平均分、名次 select stuNO as 学号,(writtenExam+labExam)/2.0 as 平均分 from stumarks order by 平均分 desc ![图片描述](https://dn-simplecloud.shiyanlou.com/courses/uid1970911-20220219-1645281916228) -- 10.机试成绩都提5分 -- select stuno,writtenexam,ifnull(labexam,0)+5 as"labExam" from stumarks; update stumarks set LabExam=LabExam+5 ; ![图片描述](https://dn-simplecloud.shiyanlou.com/courses/uid1970911-20220219-1645281960282) -- 11.机试成绩100分封顶（加分后超过100分的，按100分计算） select stuno,writtenexam,(case when ifnull(labexam,0)+5>100 then 100 else ifnull(labexam,0)+5 end)as "labexam" from stumarks ![图片描述](https://dn-simplecloud.shiyanlou.com/courses/uid1970911-20220219-1645283138875) -- 12.笔试不及格的提到及格，60-90分的加5分，90分以上加3分（但不能超过100） select stuno ,(case when writtenexam < 60 then 60 when writtenexam >= 60 and writtenexam <= 90 then writtenexam + 5 when writtenexam > 90 and writtenexam <=97 then writtenexam + 3 when writtenexam > 97 then 100 end) as writtenexam from stumarks; ![图片描述](https://dn-simplecloud.shiyanlou.com/courses/uid1970911-20220219-1645282910925) #练习二 --1、选择部门30中的雇员 select * from emp where deptno=30 ![图片描述](https://dn-simplecloud.shiyanlou.com/courses/uid1970911-20220220-1645349247887) --2、列出所有办事员的姓名、编号和部门 select ename,empno,dname from emp,dept where emp.deptno=dept.deptno and emp.job='CLERK' ![图片描述](https://dn-simplecloud.shiyanlou.com/courses/uid1970911-20220220-1645349296836) --3、找出佣金高于薪金的雇员 select * from emp where ifnull(emp.comm,0)>emp.sal ![图片描述](https://dn-simplecloud.shiyanlou.com/courses/uid1970911-20220220-1645349416965) --4、找出佣金高于薪金60%的雇员 select * from emp where ifnull(emp.comm,0)>emp.sal*0.6 ![图片描述](https://dn-simplecloud.shiyanlou.com/courses/uid1970911-20220220-1645349485319) --5、找出部门10中所有经理manager和部门20中的所有办事员clerk的详细资料 select * from emp where deptno=10 and job='MANAGER' or deptno=20 and job='CLERK' ![图片描述](https://dn-simplecloud.shiyanlou.com/courses/uid1970911-20220220-1645349631505) --6、找出部门10中所有经理、部门20中所有办事员，既不是经理又不是办事员但其薪金>=2000的所有雇员的详细资料 select * from emp where (deptno=10 and job='MANAGER')or (deptno=20 and job='CLERK') or (job!='MANAGER' and job!='CLERK' and emp.sal>=2000) ![图片描述](https://dn-simplecloud.shiyanlou.com/courses/uid1970911-20220220-1645349660760) --7、找出收取佣金的雇员的不同工作 select distinct job from emp where comm is not null ![图片描述](https://dn-simplecloud.shiyanlou.com/courses/uid1970911-20220220-1645349679026) --8、找出不收取佣金或收取的佣金低于100的雇员 select ename from emp where comm is null or comm<100 ![图片描述](https://dn-simplecloud.shiyanlou.com/courses/uid1970911-20220220-1645349723493) --9、找出各月最后一天受雇的所有雇员 select * from emp where hiredate=last_day(hiredate) ![图片描述](https://dn-simplecloud.shiyanlou.com/courses/uid1970911-20220220-1645349754611) --10、找出早于25年之前受雇的雇员 --11、显示只有首字母大写的所有雇员的姓名 select ename from emp where ename=initcap(ename) ![图片描述](https://dn-simplecloud.shiyanlou.com/courses/uid1970911-20220220-1645351860449) --12、显示正好为6个字符的雇员姓名 select ename from emp where length(ename)=6 ![图片描述](https://dn-simplecloud.shiyanlou.com/courses/uid1970911-20220220-1645352032835) --13、显示不带有'R'的雇员姓名 select ename from emp where ename not like '%R%' ![图片描述](https://dn-simplecloud.shiyanlou.com/courses/uid1970911-20220220-1645352178755) --14、显示所有雇员的姓名的前三个字符 Select substr(ename,1,3) from emp; --15、显示所有雇员的姓名，用a替换所有'A' Select replace(ename,'A','a') from emp; --16、显示所有雇员的姓名以及满10年服务年限的日期 select ename,add_month(hiredate,12*10) from emp; --17、显示雇员的详细资料，按姓名排序 select * from emp order by ename; --18、显示雇员姓名，根据其服务年限，将最老的雇员排在最前面 select ename,hiredate from emp order by hiredate desc; --19、显示所有雇员的姓名、工作和薪金，按工作的降序顺序排序，而工作相同时按薪金升序 select ename,job,sal from emp order by job desc,sal --20、显示所有雇员的姓名和加入公司的年份和月份，按雇员受雇日所在月排序，将最早年份的项目排在最前面 select ename,to_char(hiredate,'YYYY') year,to_char(hiredate,'MM') month from emp order by month,year --21、显示在一个月为30天的情况下所有雇员的日薪金 select ename,trunc(sal/30,2) from emp --22、找出在（任何年份的）2月受聘的所有雇员 select * from emp where to_char(hiredate,'MM')='02' --23、对于每个雇员，显示其加入公司的天数 select ename,round(sysdate-hiredate) from emp --24、显示姓名字段的任何位置，包含 "A" 的所有雇员的姓名 select ename from emp where ename like '%A%'; --25、以年、月和日显示所有雇员的服务年限 select ename,round(months_between(sysdate,hiredate)/12) 年, round(mod((months_between(sysdate,hiredate)),12)) 月, round(sysdate-add_months(hiredate,months_between(sysdate,hiredate))) 日 from emp #练习三 --1．列出至少有一个员工的所有部门 select * from dept where dept.deptno in (select distinct deptno from emp); --2．列出薪金比“SMITH”多的所有员工。 select * from emp where sal >(select sal from emp where ename='SMITH'); --3．列出所有员工的姓名及其直接上级的姓名。(自连接) select e1.ename,e2.ename from emp e1,emp e2 where e1.mgr=e2.empno; --4．列出受雇日期晚于其直接上级的所有员工。(自连接) select e1.ename,e2.ename from emp e1,emp e2 where e1.mgr=e2.empno and e1.hiredate-e2.hiredate<0; --5．列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门。(外连接) select dept.dname,emp.* from dept left outer join emp on(dept.deptno=emp.deptno); --6．列出所有“CLERK”（办事员）的姓名及其部门名称。 select ename,dname from emp,dept where emp.deptno=dept.deptno and emp.job='CLERK'; --7．列出最低薪金大于1500的各种工作。 select job from emp group by job naving min(sal)>1500; --8．列出在部门“SALES”（销售部）工作的员工的姓名，假定不知道销售部的部门编号。 select ename from emp where deptno=(select deptno from dept where dname='SALES'); --9．列出薪金高于公司平均薪金的所有员工。 select * from emp where sal > (select avg(sal) from emp); --10．列出与“SCOTT”从事相同工作的所有员工。 select ename from emp where job=(select job from emp where ename='SCOTT'); --11．列出薪金等于部门30中员工的薪金的所有员工的姓名和薪金。 select ename,sal from emp where sal in(select sal from emp where deptno=30); --12．列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金。 select ename,sal from emp where sal >(select max(sal) from emp where deptno=30); --13．列出在每个部门工作的员工数量、平均工资和平均服务期限。 select deptno,count(*),avg(sal),avg(sysdate-hiredate) from emp group by deptno; --14．列出所有员工的姓名、部门名称和工资。内连接 select ename,dname,sal from emp,dept where emp.deptno=dept.deptno; --15.列出所有部门的详细信息和部门人数。 select d.deptno, d.dname, count(e.empno) "Number of employees" from dept d, emp e where d.deptno = e.deptno group by d.deptno,d.dname; select d.,ec.cou from dept d left join (select count() as cou,deptno from emp group by deptno) ec on d.deptno=deptno; --16．列出各种工作的最低工资。 select job,min(sal) from emp group by job; --17．列出MANAGER（经理）的最低薪金。 select min(sal) from emp where job='MANAGER'; --18．列出所有员工的年工资，按年薪从低到高排序。 select sal*12+nvl(comm,0) yearsal from emp order by yearsal; --19. 查询职员表中的职员姓名、薪水、奖金，使用函数处理空值。 select ename,sal,nvl(comm,0) from emp; --20.查询部门表和职员表，列出所有的部门编码和部门名字，以及所有的职员编码和职员名字，并以部门编码和职员编码作为升序排列的标准 select dept.deptno,dname,empno,ename from dept,emp where dept.deptno=emp.deptno order by dept.deptno,emp.empno; --21.多列分组计算每个部门每个职位的平均薪水和最高薪水 select deptno,job,avg(sal),max(sal) from emp group by deptno,job; --22.查询出薪水比本部门平均薪水高的员工信息 select a.deptno from emp a, (select deptno,avg(sal) avgsal from emp group by deptno) as b where a.deptno=b.deptno and a.sal>b.avgsal; select e.* from emp e where e.sal>(select avg(p.sal) from emp p group by deptno having p.deptno=e.deptno); #练习四 --1.查询“语文”课程比“数学”课程成绩高的所有学生的学号； select a.s# from (select s#,score from sc where c#=(select c# from course where cname='语文')) a, (select s#,score from sc where c#=(select c# from course where cname='数学')) b where a.score>b.score and a.s#=b.s#; --2.查询平均分大于60的学生的学号和平均分 select s#,avg(score) from sc group by s# having avg(score)>60; --3.查询所有学生的学号，姓名，选课数，总分 select sc.s#,sname,count(*),sum(score) from sc,student where student.s#=sc.s# group by sc.s#,student.sname; select s.sname,ss.* from student s join (select count(c#),sum(score),s# from sc group by s#)ss on s.s#=ss.s#; --4.查询姓叶的老师的个数 select count(*) from teacher where tname like '叶%'; --5.查询没学过“叶平”老师课的同学的学号、姓名； select distinct s#,sname from student where s# not in (select sc.s# from sc where c#=(select c# from course where t# in (select t# from teacher where tname='叶平'))); --6.查询学过“语文”并且也学过“数学”课程的同学的学号、姓名；intersect select s.S#,s.Sname from Student s,SC c where s.S#=c.S# and c.C#=(select c# from course where cname='语文') intersect select s.S#,s.Sname from Student s,SC c where s.S#=c.S# and c.C#=(select c# from course where cname='数学'); --7.查询学过“叶平”老师所教的所有课的同学的学号、姓名； select sname,s# from (select count() cou,tname from teacher t , course c where t.t# = c.t# and t.tname='叶平' group by tname) a, (select count() cou,tname,sname,stu.s# from teacher t , course c ,sc s , student stu where t.t# = c.t# and s.c# = c.c# and stu.s# = s.s# and tname = '叶平' group by tname,sname,stu.s#) b where a.cou=b.cou ; --8.查询课程编号“2”的成绩比课程编号“1”课程低的所有同学的学号、姓名； select Student.S#,Student.Sname from Student where (select score from SC where SC.S# = Student.S# and C# = 2) < (select score from SC where SC.S# = Student.S# and C# = 1) ; --9.查询所有课程成绩小于60分的同学的学号、姓名； select s.s#,sname from student s where (select count() from sc c group by c.s# having c.s#=s.s#)=(select count() from sc c where c.s#=s.s# and c.score<60 group by c.s# ); --10.查询没有学全所有课的同学的学号、姓名； select s.s#,sname from student s where (select count() from sc c where s.s#=c.s# group by c.s# )!=(select count() from course); --11.查询至少有一门课与学号为“1”的同学所学相同的同学的学号和姓名； select distinct sc.s#,sname from sc,student where sc.s#=student.s# and c# in(select c# from sc where s#=1); --12.查询和学号为“6”同学所有课数量相等的其他同学的学号和姓名； select s.s# , s.sname from (select S#, count() cou from sc group by s#) a , (select count() cou from sc where s# = 6) b,student s where a.cou = b.cou and s.s# = a.s#; --13.查询和“1”号的同学学习的课程完全相同的其他同学学号和姓名； select s.s# , s.sname from (select S#, count() cou from sc where c# in (select C# from SC where S# = 1) and S# <>1 group by s#) a , (select count() cou from sc where s# = 1) b,student s where a.cou = b.cou and s.s# = a.s#; - --14.查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分 select c#,max(score) "最高分",min(score) "最低分" from sc group by c#; --15.按各科平均成绩从低到高和及格率的百分数从高到低顺序 select b.c#,avg(b.score),(count(*)/(select count(a.score) from sc a where a.c#=b.c#)) as "及格率" from sc b

“大数据技术开发与应用”实验报告