先建表 插入数据（oracle数据库为例，建表没有考虑外键约束）
create table tb_student(
student_id number(11),
s_name varchar2(20),
s_age number(11),
s_sex varchar2(2))
create table tb_course(
course_id number(11),
c_name varchar2(20),
teacher_id number(11)
)
create table tb_score(
student_id number(11),
score varchar2(20),
course_id number(11)
)
create table tb_teacher(
teacher_id number(11),
t_name varchar2(20)
)
将tb_score 字段score的类型改为 number类型
alter table tb_score rename column score to score_tmp;
alter table tb_score add score number(11)
update tb_score set score = trim(score_tmp)
alter table tb_score drop column score_tmp;
开始实战练习
1.查询课程id=1的成绩大于课程id=2的成绩的学生学号
select sc1.student_id
from tb_score sc1, tb_score sc2
where sc1.course_id = 1
and sc2.course_id = 2
and sc1.score > sc2.score
and sc1.student_id = sc2.student_id
select * from
(select student_id ,Score from tb_score where course_id=1) a,
(select student_id ,Score from tb_score where course_id=2) b
where a.student_id=b.student_id and a.Score> b.score
2.查询平均成绩大于60分的同学的学号和平均成绩；
select student_id 学生学号 ,avg(score) 平均成绩 from tb_score group by student_id having avg(score)>60 order by avg(score) desc
3.查询所有同学的学号、姓名、选课数、总成绩；
select s.student_id 学生学号,
s.s_name 学生姓名,
count(course_id) 选课数,
sum(score) 总成绩
from tb_student s, tb_score s1
where s.student_id = s1.student_id
group by s.student_id , s.s_name , s.s_age ,s.s_sex order by sum(score) desc
4.查询姓“李”的老师的个数；
select count(distinct teacher_id) from tb_teacher where t_name like '李%'
5.查询没学过“肖老师”老师课的同学的学号、姓名；用 (not) exists 代替 (not) in
select s.student_id 学生编号, s.s_name 学生姓名
from tb_student s
where exists (select *
from tb_teacher tt, tb_course tc, tb_score ts
where s.student_id = ts.student_id
and ts.course_id = tc.course_id
and tt.teacher_id = tc.teacher_id
and tt.t_name = '肖老师')
6.查询学过“1”并且也学过编号“2”课程的同学的学号、姓名；
select s.student_id 学生编号, s.s_name 学生姓名
from tb_student s ,tb_score ts
where s.student_id = ts.student_id and ts.course_id =1 and exists (select *
from tb_student s ,tb_score ts
where s.student_id = ts.student_id and ts.course_id =2)
select s.student_id 学生编号, s.s_name 学生姓名
from tb_student s ,tb_score ts
where s.student_id = ts.student_id and ts.course_id =1
intersect
select s.student_id 学生编号, s.s_name 学生姓名
from tb_student s ,tb_score ts
where s.student_id = ts.student_id and ts.course_id =2
PS:EXISTS用于检查子查询是否至少会返回一行数据，该子查询实际上并不返回任何数据，而是返回值True或False。那么，这里我们来看一下in和exists的区别：
①in 是把外表和内表作hash 连接，而exists是对外表作loop循环，每次loop循环再对内表进行查询。
②一直以来认为exists比in效率高的说法是不准确的。
-->如果查询的两个表大小相当，那么用in和exists差别不大。
-->如果两个表中一个较小，一个是大表，则子查询表大的用exists，子查询表小的用in。
（7）查询学过“肖老师”老师所教的所有课的同学的学号、姓名；
select s.student_id 学生编号, s.s_name 学生姓名 from tb_student s ,tb_score ts ,tb_teacher tt ,tb_course tc
where s.student_id = ts.student_id and ts.course_id = tc.course_id and tc.teacher_id = tt.teacher_id and tt.t_name = '肖老师'
（8）查询课程编号“002”的成绩比课程编号“001”课程低的所有同学的学号、姓名；
select s.student_id 学生编号, s.s_name 学生姓名 from tb_student s,
(select student_id ,score from tb_score ts where ts.course_id =1) a ,
(select student_id ,score from tb_score ts where ts.course_id =2) b
where s.student_id = a.student_id and s.student_id = b.student_id and a.score < b.score
9.查询有课程成绩小于60分的同学的学号、姓名；
select s.student_id 学生编号, s.s_name 学生姓名, min(score) 最低成绩
from tb_student s, tb_score ts
where s.student_id = ts.student_id
group by s.student_id, s.s_name, s.s_age, s.s_sex
having min(score) < 60
select s.student_id 学生编号, s.s_name 学生姓名
from tb_student s
where s.student_id in
(
select distinct(ts.student_id) from tb_score ts where ts.student_id = s.student_id and ts.score <60
)
10.查询没有学全所有课的同学的学号、姓名；
select s.student_id 学生编号, s.s_name 学生姓名
from tb_student s
where s.student_id not in
(select ts.student_id
from tb_score ts
group by ts.student_id
having count(distinct ts.course_id) = (select count(distinct
tc.course_id)
from tb_course tc))
（11）查询至少有一门课与学号为“1001”的同学所学相同的同学的学号和姓名；
select distinct(s.student_id) 学生编号, s.s_name 学生姓名
from tb_student s ,tb_score ts
where s.student_id = ts.student_id and ts.course_id in (
select distinct（course_id） from tb_score ts2 where ts2.student_id = 1001
) order by s.student_id
（13）把“成绩”表中“温老师”老师教的课的成绩都更改为此课程的平均成绩；
update tb_score t
set t.score = (select avg(score)
from tb_score ts, tb_course tc, tb_teacher tt
where ts.course_id = tc.course_id
and tc.teacher_id = tt.teacher_id
and tt.t_name = '温老师')
where t.course_id = (
select course_id from tb_course tc ,tb_teacher tt where tc.teacher_id = tt.teacher_id and tt.t_name = '温老师'）
（14）查询和“1001”号的同学学习的课程完全相同的其他同学学号和姓名；
select distinct (s.student_id) 学生编号, s.s_name 学生姓名
from tb_student s
where s.student_id != 1001
and s.student_id in
(select distinct (t.student_id)
from tb_score t
where t.course_id in (select ts.course_id
from tb_score ts
where ts.student_id = 1001)
group by t.student_id
having count(course_id) = (select count(course_id)
from tb_score
where student_id = 1001))
（17）按平均成绩从低到高显示所有学生的“语文”、“数学”、“英语”三门的课程成绩，按如下形式显示： 学生ID,语文,数学,英语,有效课程数,有效平均分；
select t.student_id as 学生编号,
(select score
from tb_score t1
where t.student_id = t1.student_id
and t1.course_id = 1) as 语文,
(select score
from tb_score t1
where t.student_id = t1.student_id
and t1.course_id = 4) as 数学,
(select score
from tb_score t1
where t.student_id = t1.student_id
and t1.course_id = 2) as 英语,
avg(t.score) as 平均成绩,
count(t.course_id) as 有效科目数
from tb_score t
group by t.student_id
order by avg(t.score) desc
转载请注明原文地址: https://ju.6miu.com/read-22388.html
