三张表 学生信息、 课程表、分数表
创表语句
-- 学生信息
create table t0_student
(
studentid varchar(10),
classid varchar(10),
name varchar(20),
sex varchar(1)
);
comment on column t0_student.studentid is '学生编码';
comment on column t0_student.classid is '学生班级 1 班级1 ，2 班级2';
comment on column t0_student.name is '学生姓名';
comment on column t0_student.sex is '学生性别,1 男，2 女';
alter table t0_student add constraint pk_t0_student primary key (studentid,classid)
-- 课程表
create table to_course
(
courseid varchar(10),
coursename varchar(10)
);
comment on column to_course.courseid is '课程编码，1,2,3,4,5';
comment on column to_course.coursename is '课程名称 1英语,2语文,3数学,4物理，5化学';
alter table to_course add constraint pk_to_course primary key (courseid);
-- 分数表
create table to_score
(
courseid varchar(10)，
classid varchar(10),
studentid varchar(10),
score number
);
comment on column to_score.courseid is '课程编码';
comment on column to_score.classid is '班级编码';
comment on column to_score.studentid is '学生编码';
comment on column to_score.score is '分数';
alter table to_score add constraint pk_to_score primary key (courseid,classid,studentid);
insert into t0_student values('1','1','张三','1');
insert into t0_student values('2','1','李四','2');
insert into t0_student values('3','1','王五','1');
insert into t0_student values('4','1','赵六','2');
insert into t0_student values('5','2','张三','1');
insert into t0_student values('6','2','李四','2');
insert into t0_student values('7','2','王五','1');
insert into t0_student values('8','2','赵六','2');
insert into t0_student values('9','1','jackMa','1');
insert into to_course values('1','英语');
insert into to_course values('2','语文');
insert into to_course values('3','数学');
insert into to_course values('4','物理');
insert into to_course values('5','化学');
insert into to_score values('1','1','1',50);
insert into to_score values('1','2','1',70);
insert into to_score values('2','1','2',100);
insert into to_score values('2','2','2',100);
insert into to_score values('3','1','3',90);
insert into to_score values('3','2','1',70);
insert into to_score values('4','1','4',80);
insert into to_score values('4','2','1',60);
insert into to_score values('5','1','4',90);
insert into to_score values('4','2','5',80);
insert into to_score values('3','2','5',80);
insert into to_score values('2','2','5',80);
insert into to_score values('1','2','5',80);
insert into to_score values('5','2','6',75);
insert into to_score values('4','2','6',60);
insert into to_score values('3','2','6',60);
insert into to_score values('2','2','6',60);
insert into to_score values('1','2','6',90);
/****************************************************************************sql 语句 ****************************************************************************/
--- 2班张三分数列表
select student.name, decode(student.sex,'1','男','2','女') sex,course.coursename,score.score
from to_score score, t0_student student, to_course course
where student.studentid='5' and student.classid='2'
and score.courseid = course.courseid
and student.studentid = score.studentid
and student.classid = score.classid
--- 2班 成绩行转列方式展示
with t as(
select student.name, decode(student.sex,'1','男','2','女') sex,course.coursename,score.score
from to_score score, t0_student student, to_course course
where score.courseid = course.courseid
and student.studentid = score.studentid
and student.classid = score.classid and student.classid='2' )
select * from(select * from t
)
pivot (sum(score) for coursename in ('英语','语文','数学','物理','化学'));
--- 查询分数大于80分 的人数
select count(distinct studentid) from to_score
where score>80
--查询有两科成绩大于 80分记录数
select studentid,score,count(*) from to_score
where score>=80
group by studentid,score
having count(*)>2
--- 查询所有的英语成绩 按成绩 由高往低排序
select * from to_score where courseid ='1' order by score desc
-- 查询 所有后英语成绩的总分，最高分，最低分,平均分（保留两位小数）
select sum(score) 总分,max(score) 最高分,min(score) 最低分,round(sum(score)/count(*),2) 平均分 from to_score where courseid ='1'
-- 查询所有成绩 优秀人数 (>=90)，良好人数(80-90)，及格人数(60-80)， 不及格人数(<60)
select suM(case
when score>=90 then 1
else 0 end ) 优秀人数,
suM( case
when score >=80 and score<90 then 1
when score >=60 and score<80 then 1
else 0 end) 良好人数,
suM(case
when score >=60 and score<80 then 1
else 0 end) 及格人数,
suM( case
when score <60 then 1
else 0 end) 不及格人数
from to_score
---查询90分以上各分数人数。rollup 统计总人次
select score ,count(*) from to_score where score >=90 group by rollup(score)
--- 删除名字重复的 学生 （如果没有studentid 这样的主键列的时候 可以通过rownum 增加一个虚拟列然后再删除）
delete t0_student
where studentid in(select studentid from (
select max(studentid) studentid ,name from t0_student group by name ))
--查询为非汉字的学生名字
select * from t0_student where asciistr(name) not like '%\%';
--随机抽取成绩中10% 数据
select * from to_score sample block(10);
