练习一

1、建立学员表stuInfo和学员成绩表stuMarks 2、向两个表中分别录入信息

-- 1.查询两表的数据 select * from stuinfo; select * from stumarks;

-- 2.查询男学员名单 select * from stuinfo where STUSEX LIKE '男';

-- 3.查询笔试成绩优秀的学员情况（成绩在75~100之间) select * from stumarks where writtenExam BETWEEN 75 AND 100;

-- 4.显示学员信息，地址没有填写的显示“地址不详” select stuname,stuno,stusex,stuage,IFNULL(stuaddress,'地址不详')as "stuAddress" from stuinfo;

-- 5.统计笔试考试平均分和机试考试平均分 ？空值问题

select AVG(writtenExam) 笔试平均分, AVG(labExam) 机试平均分 FROM stumarks ;

-- 6.统计参加本次考试的学员人数 select count(stuNo) as 考试人数 from stumarks

-- 7.查询没有通过考试的人数（笔试或机试小于60分） ？空值问题 select count(stuNo) as 未通过的人数 from stumarks where labExam<60 or writtenExam<60

-- 8.查询学员成绩，显示学号，笔试成绩，机试成绩，平均分 select stuNO as 学号,writtenExam as 笔试,labExam as 机试,(writtenExam+labExam)/2.0 as 平均分 from stumarks

-- 9.排名次（按平均分从高到低排序），显示名次、学号、平均分 、名次 select stuNO as 学号,(writtenExam+labExam)/2.0 as 平均分 from stumarks order by 平均分 desc

-- 10.机试成绩都提5分 -- select stuno,writtenexam,ifnull(labexam,0)+5 as"labExam" from stumarks; update stumarks set LabExam=LabExam+5 ;

-- 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

-- 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;

练习二

--1、选择部门30中的雇员 select * from emp where deptno=30

--2、列出所有办事员的姓名、编号和部门 select ename,empno,dname from emp,dept where emp.deptno=dept.deptno and emp.job='CLERK'

--3、找出佣金高于薪金的雇员 select * from emp where ifnull(emp.comm,0)>emp.sal

--4、找出佣金高于薪金60%的雇员 select * from emp where ifnull(emp.comm,0)>emp.sal*0.6

--5、找出部门10中所有经理manager和部门20中的所有办事员clerk的详细资料 select * from emp where deptno=10 and job='MANAGER' or deptno=20 and job='CLERK'

--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)

--7、找出收取佣金的雇员的不同工作 select distinct job from emp where comm is not null

--8、找出不收取佣金或收取的佣金低于100的雇员 select ename from emp where comm is null or comm<100

--9、找出各月最后一天受雇的所有雇员 select * from emp where hiredate=last_day(hiredate)

--10、找出早于25年之前受雇的雇员

--11、显示只有首字母大写的所有雇员的姓名 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) 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