表结构
------------------------------课程表
课程编号跟课程名字
create table COURSE
(
KC VARCHAR2(50),
KNAME VARCHAR2(50)
)
insert into COURSE (KC, KNAME)
values ('001', '语文');
insert into COURSE (KC, KNAME)
values ('002', '数学');
insert into COURSE (KC, KNAME)
values ('003', '英语');
commit;
------------------------------分数表
create table SCORE
(
XH VARCHAR2(50),
KC VARCHAR2(50),
CJ NUMBER(5,2)
)
insert into SCORE (XH, KC, CJ)
values ('0001', '001', 85);
insert into SCORE (XH, KC, CJ)
values ('0001', '002', 90);
insert into SCORE (XH, KC, CJ)
values ('0001', '003', 100);
insert into SCORE (XH, KC, CJ)
values ('0002', '001', 75);
insert into SCORE (XH, KC, CJ)
values ('0002', '002', 60);
insert into SCORE (XH, KC, CJ)
values ('0002', '003', 45);
insert into SCORE (XH, KC, CJ)
values ('0003', '001', 88);
insert into SCORE (XH, KC, CJ)
values ('0003', '002', 79);
insert into SCORE (XH, KC, CJ)
values ('0003', '003', 80);
insert into SCORE (XH, KC, CJ)
values ('0004', '001', 83);
insert into SCORE (XH, KC, CJ)
values ('0004', '002', 92);
insert into SCORE (XH, KC, CJ)
values ('0004', '003', 86);
insert into SCORE (XH, KC, CJ)
values ('0005', '001', 84);
insert into SCORE (XH, KC, CJ)
values ('0005', '002', 95);
insert into SCORE (XH, KC, CJ)
values ('0005', '003', 92);
insert into SCORE (XH, KC, CJ)
values ('0006', '001', 68);
insert into SCORE (XH, KC, CJ)
values ('0006', '002', 82);
insert into SCORE (XH, KC, CJ)
values ('0006', '003', 65);
insert into SCORE (XH, KC, CJ)
values ('0007', '001', null);
commit;
------------------------------分数表
create table STUDENT
(
XH VARCHAR2(50),
NAME VARCHAR2(50)
)
insert into STUDENT (XH, NAME)
values ('0001', '杨一');
insert into STUDENT (XH, NAME)
values ('0002', '颜二');
insert into STUDENT (XH, NAME)
values ('0003', '张三');
insert into STUDENT (XH, NAME)
values ('0004', '李四');
insert into STUDENT (XH, NAME)
values ('0005', '王五');
insert into STUDENT (XH, NAME)
values ('0006', '苟六');
insert into STUDENT (XH, NAME)
values ('0007', '大帅逼');
commit;
做题吧孩子
1.分组取各科最小成绩及所在行的数据信息。
2.查询各科成绩后两名的记录。
3.查询所有学生学号、姓名、总成绩。
4.查询平均成绩小于80分的所有学生的学号、姓名、平均成绩。
5.查询学生的选课情况。
6.查询每门课程的及格人数和不及格人数。
7.查询课程编号为002且成绩在90分以下的学生学号和姓名。
8.100-85,85-70,70-60,<60分段统计各科成绩，分别统计：各分数段人数，课程号和课程名称
9.因为学号为0007语文缺考了，将他的语文成绩改成0.
不会就看答案吧
基本上用的就是group by having , order by , left join ,case when 这些语法。不会就百度语法。觉得嗯还能简写并且更好的就麻烦给个意见啦
--1.分组取各科最小成绩及所在行的数据信息。
select a.kname,b.* from course a left join (select kc,min(cj) from score group by kc) b on a.kc = b.kc
--2.查询各科成绩后两名的记录。--
select * from (select * from score where kc='001' order by cj asc) a where rownum<=2
union all select * from (select * from score where kc='002' order by cj asc) a where rownum<=2
union all select * from (select * from score where kc='003' order by cj asc) a where rownum<=2
--3.查询所有学生学号、姓名、总成绩。
select b.xh,b.name,sum(cj) from score a left join student b on a.xh = b.xh group by b.xh,b.name
--4.查询平均成绩小于80分的所有学生的学号、姓名、平均成绩。
select b.xh,b.name,avg(cj) as avgcj from score a left join student b on a.xh = b.xh group by b.xh,b.name having avg(cj)<80
--5.查询学生的选课情况。
select a.xh,a.name,c.kname from student a left join score b on a.xh = b.xh left join course c on b.kc = c.kc order by a.name desc
--6.查询每门课程的及格人数和不及格人数。
select b.kname,a.* from (select kc,sum(case when cj>=60 then 1 else 0 end) 及格人数,sum(case when cj<60 then 1 else 0 end) 不及格人数 from score group by kc) a left join course b on a.kc = b.kc
--7.查询课程编号为002且成绩在90分以下的学生学号和姓名。
select a.xh,a.name from student a left join score b on a.xh = b.xh left join course c on b.kc = c.kc where c.kc = '002' and b.cj <90
--8.100-85,85-70,70-60,<60分段统计各科成绩，分别统计：各分数段人数，课程号和课程名称
select a.kname,b.* from course a left join (
select kc,
sum(case when (cj>=85 and cj<=100) then 1 else 0 end) "85-100",
sum(case when (cj>=70 and cj<85) then 1 else 0 end) "70-85",
sum(case when (cj>=60 and cj<70) then 1 else 0 end) "60-70",
sum(case when cj<60 then 1 else 0 end) "不及格"
from score group by kc
) b on a.kc = b.kc
--9.因为学号为0007语文缺考了，将他的语文成绩改成0.
select a.name,b.kc,nvl(b.cj,0) cj from student a left join score b on a.xh = b.xh where a.xh = 0007