用java实现统计各科的最高分_P 0040_sql全套_高级练习_经典46题1-5

-- 建表 造数据

drop table student;

drop table teacher;

drop table course;

drop table sc;

create table student(

sno varchar2(10) primary key,

sname varchar2(20),

sage number(2),

ssex varchar2(5)

);

create table teacher(

tno varchar2(10) primary key,

tname varchar2(20)

);

create table course(

cno varchar2(10),

cname varchar2(20),

tno varchar2(20),

constraint pk_course primary key (cno,tno)

);

create table sc(

sno varchar2(10),

cno varchar2(10),

score number(4,2),

constraint pk_sc primary key (sno,cno)

);

-- /*******初始化学生表的数据******/

insert into student values ('s001','张三',23,'男');

insert into student values ('s002','李四',23,'男');

insert into student values ('s003','吴鹏',25,'男');

insert into student values ('s004','琴沁',20,'女');

insert into student values ('s005','王丽',20,'女');

insert into student values ('s006','李波',21,'男');

insert into student values ('s007','刘玉',21,'男');

insert into student values ('s008','萧蓉',21,'女');

insert into student values ('s009','陈萧晓',23,'女');

insert into student values ('s010','陈美',22,'女');

commit;

-- /******************初始化教师表***********************/

insert into teacher values ('t001', '刘阳');

insert into teacher values ('t002', '谌燕');

insert into teacher values ('t003', '胡明星');

commit;

-- /***************初始化课程表****************************/

insert into course values ('c001','J2SE','t002');

insert into course values ('c002','Java Web','t002');

insert into course values ('c003','SSH','t001');

insert into course values ('c004','Oracle','t001');

insert into course values ('c005','SQL SERVER 2005','t003');

insert into course values ('c006','C#','t003');

insert into course values ('c007','JavaScript','t002');

insert into course values ('c008','DIV+CSS','t001');

insert into course values ('c009','PHP','t003');

insert into course values ('c010','EJB3.0','t002');

commit;

-- /***************初始化成绩表***********************/

insert into sc values ('s001','c001',78.9);

insert into sc values ('s002','c001',80.9);

insert into sc values ('s003','c001',81.9);

insert into sc values ('s004','c001',60.9);

insert into sc values ('s001','c002',82.9);

insert into sc values ('s002','c002',72.9);

insert into sc values ('s003','c002',81.9);

insert into sc values ('s001','c003',59);

commit;

练习：

1、查询“c001”课程比“c002”课程成绩高的所有学生的学号；

2、查询平均成绩大于60 分的同学的学号和平均成绩；

3、查询所有同学的学号、姓名、选课数、总成绩；

4、查询姓“刘”的老师的个数；

5、查询没学过“谌燕”老师课的同学的学号、姓名；

6、查询学过“c001”并且也学过编号“c002”课程的同学的学号、姓名；

7、查询学过“谌燕”老师所教的所有课的同学的学号、姓名；

8、查询课程编号“c001”的成绩比课程编号“c002”课程低的所有同学的学号、姓名；

9、查询所有课程成绩小于60 分的同学的学号、姓名；

10、查询没有学全所有课的同学的学号、姓名；

11、查询至少有一门课与学号为“s002”的同学所学相同的同学的学号和姓名；

12、查询至少学过学号为“s002”同学所有门课的其他同学学号和姓名；

13、查询和“s002”号的同学学习的课程完全相同的其他同学学号和姓名；

14、把“SC”表中“谌燕”老师教的课的成绩都更改为此课程的平均成绩；

15、删除学习“谌燕”老师课的SC 表记录；

16、向SC 表中插入一些记录，这些记录要求符合以下条件：没有上过编号“c002”课程的同学学号、“c002”号课的平均成绩；

17、查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分

18、按各科平均成绩从低到高和及格率的百分数从高到低顺序

19、查询不同老师所教不同课程平均分从高到低显示

20、统计列印各科成绩,各分数段人数:课程ID,课程名称,[100-85],[85-70],[70-60],[ <60]

--

select * from student;

select * from teacher;

select * from course;

select * from sc;

--

1、查询“c001”课程比“c002”课程成绩高的所有学生的学号；

-- 第一题  s002

-- 分析 数据在不同的行  无法比较  用行列转换放到一行上面去比较

-- 关联 decode case when 自带的函数  分析函数

-- 方法1 关联

select a.sno

from sc a

join sc b on a.sno = b.sno

and a.cno = 'c001'

and b.cno = 'c002'

and a.score > b.score;

-- 方法2 decode

-- having 后面只能接表达式 不能接别名

select s.sno

,max(decode(s.cno,'c001',s.score)) as c001

,max(decode(s.cno,'c002',s.score)) as c002

from sc s

group by s.sno

having max(decode(s.cno,'c001',s.score)) > max(decode(s.cno,'c002',s.score));

-- 方法3 case when

select s.sno

,max(case when s.cno = 'c001' then s.score end) as c001

,max(case when s.cno = 'c002' then s.score end) as c002

from sc s

group by s.sno

having max(decode(s.cno,'c001',s.score)) > max(decode(s.cno,'c002',s.score));

-- 方法4 自带的函数

with tmp as(

select *

from sc s

pivot ( sum(score) for cno in('c001' as c001,'c002' as c002) )

)

select *

from tmp t

where t.c001 > t.c002;

-- 方法5 分析函数  lead

with tmp as (

select s.sno

,s.score as c001

,lead(s.score) over(partition by s.sno order by s.cno) as c002

from sc s

where s.cno in ('c001','c002') )

select *

from tmp t

where t.c001 > t.c002;

-- 方法6 分析函数    lag

with tmp as (

select s.sno

,s.score as c002

,lag(s.score) over(partition by s.sno order by s.cno) as c001

from sc s

where s.cno in ('c001','c002') )

select *

from tmp t

where t.c001 > t.c002;

2、查询平均成绩大于60 分的同学的学号和平均成绩；

-- 第二题  考察点 分组聚合过滤

select s.sno

,avg(s.score) as avg_score

from sc s

group by s.sno

having avg(s.score) > 60;

3、查询所有同学的学号、姓名、选课数、总成绩；

-- 第三题

select s.sno

,count(1)     as ct

,sum(s.score) as sum_score

from sc s

group by s.sno;

select a.sno

,a.sname

,count(s.sno)        as ct

,nvl(sum(s.score),0) as sum_score

from student a

,sc      s

where a.sno = s.sno(+)

group by a.sno,a.sname;

4、查询姓“刘”的老师的个数；

-- 第四题  模糊查询

select count(1) from teacher t where t.tname like '刘%';

-- 截取实现需求

select substr(t.tname,1,1) from teacher t;

select count(1) from teacher t where substr(t.tname,1,1) = '刘';

-- instr 实现

select instr(t.tname,'刘',1,1) from teacher t;

select count(1) from teacher t where instr(t.tname,'刘',1,1) = 1;

-- ltrim 实现

select t.tname,ltrim(t.tname,'刘') from teacher t;

select count(1) from teacher t where ltrim(t.tname,'刘') <> t.tname;

5、查询没学过“谌燕”老师课的同学的学号、姓名；

-- 第五题  先求学过的  再求没有学过的

-- 先求出 谌燕老师教的课程

select c.cno

from teacher t

join course  c on t.tno = c.tno and t.tname = '谌燕';

-- 求出学过谌燕老师课程的同学

select *

from sc s

where s.cno in ( select c.cno

from teacher t

join course  c on t.tno = c.tno and t.tname = '谌燕');

-- not in 可以解决

select *

from student stu

where stu.sno not in

(select s.sno

from sc s

where s.cno in (select c.cno

from teacher t

join course c on t.tno = c.tno

and t.tname = '谌燕'));

-- 代码优化 去掉不必要的子查询

select stu.*

from teacher t

join course  c on t.tno = c.tno and t.tname = '谌燕'

join sc      s on c.cno = s.cno

right join student stu on s.sno = stu.sno

where s.sno is null;