#作业一 --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; --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; #作业三： --1．列出至少有一个员工的所有部门 select * from dept where dept.deptno in (select distinct deptno from emp); --思路(select deptno from emp group by deptno having count(*)>0) --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); #作业四：表架构 Student(S#,Sname,Sage,Ssex) 学生表 Course(C#,Cname,T#) 课程表 SC(S#,C#,score) 成绩表 Teacher(T#,Tname) 教师表建表语句 CREATE TABLE student(s# number, sname nvarchar(32),sage number,ssex nvarchar(8)) CREATE TABLE course(c# number, cname nvarchar(32), t# number) CREATE TABLE sc(s# number,c# number,score number) CREATE TABLE teacher(t# number,tname nvarchar(16)) 测试数据 insert into Student select 1,N'刘一',18,N'男' from dual union select 2,N'钱二',19,N'女' from dual union select 3,N'张三',17,N'男' from dual union select 4,N'李四',18,N'女' from dual union select 5,N'王五',17,N'男' from dual union select 6,N'赵六',19,N'女' from dual insert into Teacher select 1,N'叶平' from dual union all select 2,N'贺高' from dual union all select 3,N'杨艳' from dual union all select 4,N'周磊' from dual insert into Course select 1,N'语文',1 from dual union all select 2,N'数学',2 from dual union all select 3,N'英语',3 from dual union all select 4,N'物理',4 from dual insert into SC select 1,1,56 from dual union all select 1,2,78 from dual union all select 1,3,67 from dual union all select 1,4,58 from dual union all select 2,1,79 from dual union all select 2,2,81 from dual union all select 2,3,92 from dual union all select 2,4,68 from dual union all select 3,1,91 from dual union all select 3,2,47 from dual union all select 3,3,88 from dual union all select 3,4,56 from dual union all select 4,2,88 from dual union all select 4,3,90 from dual union all select 4,4,93 from dual union all select 5,1,46 from dual union all select 5,3,78 from dual union all select 5,4,53 from dual union all select 6,1,35 from dual union all select 6,2,68 from dual union all select 6,4,71 from dual --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. select S#, avg(score) 平均分 from SC group by S# having avg(score) > 60; --3. select sc.S# 学号,s.Sname 姓名,Count(C#) 课程数,Sum(score) 总成绩 from SC sc left join Student s on s.S# = sc.S# group by sc.S#,s.Sname order by sc.S#; select ssc.S# 学号,s.Sname 姓名,ssc.count 课程数,ssc.scores 总成绩 from student s left join (select s# , count(*) as count , sum(score) scores from sc group by s# ) as ssc on s.s# = ssc.s# --4. select count(*) from teacher where Tname like '叶%'; --5. select s.S#,s.Sname from Student s where s.S# not in (select S# from SC where C# in (select C# from Course where T# =(select T# from Teacher where Tname = '叶平'))); --6 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 select s#, sname from student where s# in( select b.s# from (select count（C#）as cou1 from Course where T# =(select T# from Teacher where Tname = '叶平'))) a, (select s#,count(s#) as cou2 from sc ,（select C# from Course where T# =(select T# from Teacher where Tname = '叶平')）as cc# where sc.c# = cc#.c# group by s#) b where a.cou1 = b.cou2) --8. 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) --或者 select a.S# , s.sname 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, student s where a.score>b.score and a.s#=b.s# and s.s# = a.s#; --9. select S#,Sname from Student where S# in (select S# from SC where score<60); --10 select Student.S#,Student.Sname from Student where (select count(*) from SC where SC.S# = Student.S#) < (select count(*) from Course) --或者 select Studen.s#,Student.Sname from Student,SC where Student.S#=SC.S# group by Student.S#,Student.Sname having count(C#) <(select count(C#) from Course); --11. select distinct Student.S#,Student.Sname from Student, SC where SC.S# = Student.S# and SC.C# in (select C# from SC where S# = 1) and Student.S# <>1; --12. 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. 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 select C#,Max(score),min(score) from SC group by C# --15 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)

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