-- 建表 造数据
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;