作业一： --1.查询两表的数据-- select * from STUINFO t; select * from STUMARKS; --2.查询男学员名单-- select * from stuinfo where stusex='男'; --3.查询笔试成绩优秀的学员情况（成绩在75~100之间)-- select * from stumarks where writtenexam between 75 and 100; --4.显示学员信息，地址没有填写的显示“地址不详”-- select stuname,stuno,stusex,stuage,nvl(stuaddress,'地址不详')as "stuAddress" from stuinfo; --5.统计笔试考试平均分和机试考试平均分-- ？空值问题 select avg(writtenexam) as "avgWrittenExam",avg(nvl(labexam,0))as "avgLabExam" from stumarks; --6.统计参加本次考试的学员人数 select count(stuName) as "学员人数" from stuinfo; --7.查询没有通过考试的人数（笔试或机试小于60分）-- ？空值问题 select count(stuno) as "不及格人数" from stumarks where nvl(writtenexam,0) < 60 or nvl(labexam,0) < 60; --8.查询学员成绩，显示学号，笔试成绩，机试成绩，平均分-- select stuno,writtenexam,nvl(labexam,0), (writtenexam+nvl(labexam,0))/2 as "平均分" from stumarks; --9.排名次（按平均分从高到低排序），显示名次、学号、平均分-- ?名次 select row_number()over(order by (writtenexam+nvl(labexam,0))/2 desc) as "名次", stuno, (writtenexam+nvl(labexam,0))/2 as "平均分" from stumarks order by nvl(labexam,0)/2 DESC; --10.机试成绩都提5分-- select stuno,writtenexam,nvl(labexam,0)+5 as"labExam" from stumarks; --11.机试成绩100分封顶（加分后超过100分的，按100分计算）-- select stuno,writtenexam,(case when nvl(labexam,0)+5>100 then 100 else nvl(labexam,0)+5 end)as "labExam" from stumarks; /*数据是char类型时 select stuno,writtenexam, (case when nvl(labexam,0)+5>100 then '100+' else cast(nvl(labexam,0)+5 as varchar(6))end)as "labExam" from stumarks; */ --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+3>100 then 100 else writtenexam+3 end)as "labExam" from stumarks; 作业二： select * from EMP t;--empno ename job mgr hiredate sal comm佣金 deptno --1、选择部门30中的雇员 select * from emp where deptno=30; --2、列出所有办事员的姓名、编号和部门 select ename,empno,deptno from emp where job='CLERK'; --3、找出佣金高于薪金的雇员 select * from emp where comm>sal; --4、找出佣金高于薪金60%的雇员 select * from emp where comm>sal*0.6; --5、找出部门10中所有经理manager和部门20中的所有办事员clerk的详细资料 select * from emp where job ='CLERK' and deptno =20 or job ='MANAGER' and deptno=10; --6、找出部门10中所有经理、部门20中所有办事员，既不是经理又不是办事员但其薪金>=2000的所有雇员的详细资料 select * from EMP where (job ='CLERK' and deptno =20) or (job ='MANAGER' and deptno =10) or (job !='CLERK' and job !='MANAGER' and sal>2000); --7、找出收取佣金的雇员的不同工作 select * from emp; select distinct job from emp where comm is not null; --8、找出不收取佣金或收取的佣金低于100的雇员 select * from emp where comm is null or comm<100; --9、找出各月最后一天受雇的所有雇员 --select hiredate, last_day(hiredate) from emp; select * from emp where hiredate=last_day(hiredate); --10、找出早于25年之前受雇的雇员 --select months_between(hiredate,sysdate)/12 from emp; select * from emp where months_between(sysdate,hiredate)/12>=25; --11、显示只有首字母大写的所有雇员的姓名 --select * from emp where UPPER( SUBSTR(ename,1,1))=SUBSTR(ename,1,1) and LOWER(SUBSTR(ename,2,LENGTH(ename)-1))=SUBSTR(ename,2,LENGTH(ename)-1); --select UPPER( SUBSTR(ename,1,1))||LOWER(SUBSTR(ename,2,LENGTH(ename)-1)) as"姓名" from emp; select ename from emp where ename=initcap(ename); --12、显示正好为6个字符的雇员姓名 select ename from emp where length(ename)=6; --13、显示不带有'R'的雇员姓名 select ename from emp where ename not like '%R%'; --14、显示所有雇员的姓名的前三个字符 select substr(ename,1,3)as"姓名" from emp; --15、显示所有雇员的姓名，用a替换所有'A' select replace(ename,'A','a')as"姓名" from emp; --16、显示所有雇员的姓名以及满10年服务年限的日期 select ename ,add_months(hiredate,12*10)as"满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') as "年份", to_char(hiredate,'MM')as "月份" from emp order by "月份","年份"; --21、显示在一个月为30天的情况下所有雇员的日薪金 select trunc(sal/30,2) as daily_sal from emp; --22、找出在（任何年份的）2月受聘的所有雇员 select * from emp where to_char(hiredate,'MM')='02'; --23、对于每个雇员，显示其加入公司的天数 select round(sysdate-hiredate) as "加入天数" from emp; --24、显示姓名字段的任何位置，包含 "A" 的所有雇员的姓名 select ename from emp where ename like'%A%'; --25、以年、月和日显示所有雇员的服务年限 round(mod(months_between(sysdate,hiredate)/12)*12) select hiredate，round(months_between(sysdate,hiredate)/12) ||'年'|| round(mod(months_between(sysdate,hiredate),12))||'月'|| round(sysdate-add_months(hiredate,months_between(sysdate,hiredate)))||'日' as"服务年限"from emp; 作业三：使用scott/tiger用户下的emp表和dept表完成下列练习 --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 p.ename ,p.mgr,pm.ename from emp p ,emp pm where pm.empno=p.mgr; --4．列出受雇日期晚于其直接上级的所有员工。(自连接) select * from emp p where p.hiredate-(select hiredate from emp where empno=p.mgr)>0; --5．列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门。(外连接) select d.dname,e.* from dept d left join emp e on d.deptno=e.deptno; --6．列出所有“CLERK”（办事员）的姓名及其部门名称。 select e.ename,d.dname from emp e , dept d where d.deptno=e.deptno and e.job='CLERK'; --7．列出最低薪金大于1500的各种工作。 select job from emp group by job having min(sal)>1500; --8．列出在部门“SALES”（销售部）工作的员工的姓名，假定不知道销售部的部门编号。 select ename from emp where deptno=(select deptno from dept where dname='SALES'); --9．列出薪金高于公司平均薪金的所有员工。 select e.* from emp e where sal>(select avg(sal) from emp); --10．列出与“SCOTT”从事相同工作的所有员工。 select e.* from emp e where job=(select job from emp where ename='SCOTT'); --11．列出薪金等于部门30中员工的薪金的所有员工的姓名和薪金。 select e.ename,e.sal from emp e where sal in(select sal from emp where deptno='30'); --12．列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金。 select e.ename,e.sal from emp e where sal >(select max(sal) from emp where deptno='30'); --13．列出在每个部门工作的员工数量、平均工资和平均服务期限。 select deptno,count(ename),round(avg(sal),2), round(avg(sysdate-hiredate),2)as"平均服务期限" from emp group by deptno; --14．列出所有员工的姓名、部门名称和工资。内连接 select e.ename,d.dname,e.sal from dept d inner join emp e on d.deptno=e.deptno; --15.列出所有部门的详细信息和部门人数。select e.ename,d.* from emp e join dept d on e.deptno=d.deptno; select * from dept left join (select count(ename),deptno from emp group by deptno) using(deptno); select d.* ,ec.cou from dept d left join (select count(*) as cou, deptno from emp group by deptno) ec on d.deptno = ec.deptno --16．列出各种工作的最低工资。 select job,min(sal) from emp group by job; --17．列出MANAGER（经理）的最低薪金。 select min(sal) from emp where job='MANAGER'; --18．列出所有员工的年工资，按年薪从低到高排序。 select ename,sal*12 "年工资" from emp order by sal; --19. 查询职员表中的职员姓名、薪水、奖金，使用函数处理空值。注：NULL参与运算，结果为NULL，所以有些情况需要处理NULL select ename,sal,nvl(comm,0) from emp; --20.在使用UNION这类集合操作符时，如果select列表的列名不同，必须使用列位置来排序。 --查询部门表和职员表，列出所有的部门编码和部门名字，以及所有的职员编码和职员名字，并以部门彪马和职员编码 --作为升序排列的标准 select d.deptno,d.dname,e.empno,e.ename from emp e join dept d on d.deptno=e.deptno order by d.deptno,e.empno; --union交集 SELECT deptno a, dname FROM dept UNION SELECT empno a,ename FROM emp ORDER BY a; --21.多列分组计算每个部门每个职位的平均薪水和最高薪水 select deptno,job, avg(sal),max(sal) from emp group by deptno,job order by deptno; --22.查询出薪水比本部门平均薪水高的员工信息 --select * from emp e natural join (select avg(sal)as"平均薪水",deptno from emp group by deptno)pp; 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 distinct e.s# from sc e where (select score from sc where c#=(select c# from course where cname='语文') and s#=e.s#) > (select score from sc where c#=2 and s#=e.s#); 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 s.s#,s.sname,ss.* from student s left join (select count(c#),sum(score),s# from sc group by s#)ss on s.s#=ss.s#; --4.查询姓叶的老师的个数 select count(t#) from teacher where tname like'叶%'; --5.查询没学过“叶平”老师课的同学的学号、姓名； select s#,sname from student where s# not in (select s# from sc join course on sc.c#=course.c# where course.t# in(select t# from teacher where tname='叶平')); --6.查询学过“语文”并且也学过“数学”课程的同学的学号、姓名； select s#,sname from(select s.s#,s.sname,course.cname from student s join sc on sc.s#=s.s# join course on sc.c#=course.c# where course.cname='语文' or course.cname='数学') group by s#,sname having count(s#)=2; 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# ,s.sname from student s join sc on s.s#=sc.s# group by s.s#,s.sname having max(sc.score)<60; --有一门 select S#,Sname from Student where S# in (select S# from SC where score<666660); --10.查询没有学全所有课的同学的学号、姓名； where sc.c# in(select c# from course) select s.s#,s.sname from student s join sc on s.s#=sc.s# group by s.s#,s.sname having count(s.s#)< (select count(c#)from course); select Student.S#,Student.Sname from Student where (select count(*) from SC where SC.S# = Student.S#) < (select count(*) from Course) --11.查询至少有一门课与学号为“1”的同学所学相同的同学的学号和姓名； select distinct s#,sname from student join sc using(s#) where sc.c# in(select c# from sc where s#='1'); --12.查询和学号为“6”同学所有课数量相等的其他同学的学号和姓名； select s.s#,s.sname from student s join sc on s.s#=sc.s# group by s.s#,s.sname having count(sc.c#)=(select count(c#) from sc where s#='6'); --13.查询和“1”号的同学学习的课程完全相同的其他同学学号和姓名； select s.s#,s.sname from student s join sc on s.s#=sc.s# where sc.c# in(select c# from sc where s#='1') group by s.s#,s.sname having count(sc.c#)=(select count(c#) from sc where s#='1') and s.s#!='1'; select s.s# , s.sname from (select S#, count(*) cou from sc where c# in (select C# from SC where S# = 2) and S# <>2 group by s#) a , (select count(*) cou from sc where s# = 2) 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 where b.score>60 group by b.c# order by avg(b.score),"及格率" desc; select * from ( select C#,avg(score) ret from SC group by sc.C# order by ret ) union all select * from (select a.c#, cou1/cou2 as ret from (select c#, count(*) cou1 from sc where score > 60 group by c#) a, (select c#, count (*) cou2 from sc group by c#) b where a.c# = b.c# order by ret desc)

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