SQL数据库经典题型1查询“001“课程比“002“课程成绩高的所有学生的学号

题目：

学生表：Student(S#,Sname,Sage,Ssex) ，学号：S#，学生姓名：Sname，学生年龄：Sage，学生性别：Ssex

课程表：Course(C#,Cname,T#)，课程编号：C#，课程名字：Cname，教师编号：T#

成绩表：SC(S#,C#,score)， 学号：S#，课程编号：C#，成绩：score

教师表：Teacher(T#,Tname)，教师编号：T#，教师名字：Tname

创建表格：

--学生表
CREATE TABLE s_student(
s_id VARCHAR(20),
s_name VARCHAR(20) NOT NULL,
s_birth VARCHAR(20) NOT NULL,
s_sex VARCHAR(10) NOT NULL,
PRIMARY KEY(s_id)
);
--课程表
CREATE TABLE c_course(
c_id VARCHAR(20),
c_name VARCHAR(20) NOT NULL,
t_id VARCHAR(20) NOT NULL,
PRIMARY KEY(c_id)
);
--教师表
CREATE TABLE t_teacher(
t_id VARCHAR(20),
t_name VARCHAR(20) NOT NULL,
PRIMARY KEY(t_id)
);
--成绩表
CREATE TABLE s_score(
s_id VARCHAR(20),
c_id VARCHAR(20),
s_score number(3),
PRIMARY KEY(s_id,c_id)
);
--插入学生表测试数据
insert into s_student(s_id, s_name, s_birth, s_sex) values('01' , '赵雷' , '1990-01-01' , '男');
insert into s_student(s_id, s_name, s_birth, s_sex) values('02' , '钱电' , '1990-12-21' , '男');
insert into s_student(s_id, s_name, s_birth, s_sex) values('03' , '孙风' , '1990-05-20' , '男');
insert into s_student(s_id, s_name, s_birth, s_sex) values('04' , '李云' , '1990-08-06' , '男');
insert into s_student(s_id, s_name, s_birth, s_sex) values('05' , '周梅' , '1991-12-01' , '女');
insert into s_student(s_id, s_name, s_birth, s_sex) values('06' , '吴兰' , '1992-03-01' , '女');
insert into s_student(s_id, s_name, s_birth, s_sex) values('07' , '郑竹' , '1989-07-01' , '女');
insert into s_student(s_id, s_name, s_birth, s_sex) values('08' , '王菊' , '1990-01-20' , '女');
commit;
--课程表测试数据
insert into c_course(c_id, c_name, t_id) values('01' , '语文' , '02');
insert into c_course(c_id, c_name, t_id) values('02' , '数学' , '01');
insert into c_course(c_id, c_name, t_id) values('03' , '英语' , '03');
commit;

--教师表测试数据
insert into t_teacher(t_id, t_name) values('01' , '张三');
insert into t_teacher(t_id, t_name) values('02' , '李四');
insert into t_teacher(t_id, t_name) values('03' , '王五');
commit;

--成绩表测试数据
insert into s_score(s_id, c_id, s_score) values('01' , '01' , 80);
insert into s_score(s_id, c_id, s_score) values('01' , '02' , 90);
insert into s_score(s_id, c_id, s_score) values('01' , '03' , 99);
insert into s_score(s_id, c_id, s_score) values('02' , '01' , 70);
insert into s_score(s_id, c_id, s_score) values('02' , '02' , 60);
insert into s_score(s_id, c_id, s_score) values('02' , '03' , 80);
insert into s_score(s_id, c_id, s_score) values('03' , '01' , 80);
insert into s_score(s_id, c_id, s_score) values('03' , '02' , 80);
insert into s_score(s_id, c_id, s_score) values('03' , '03' , 80);
insert into s_score(s_id, c_id, s_score) values('04' , '01' , 50);
insert into s_score(s_id, c_id, s_score) values('04' , '02' , 30);
insert into s_score(s_id, c_id, s_score) values('04' , '03' , 20);
insert into s_score(s_id, c_id, s_score) values('05' , '01' , 76);
insert into s_score(s_id, c_id, s_score) values('05' , '02' , 87);
insert into s_score(s_id, c_id, s_score) values('06' , '01' , 31);
insert into s_score(s_id, c_id, s_score) values('06' , '03' , 34);
insert into s_score(s_id, c_id, s_score) values('07' , '02' , 89);
insert into s_score(s_id, c_id, s_score) values('07' , '03' , 98);
commit;
 

1.查询"001"课程比"002"课程成绩高的所有学生的学号

--先查询001课程成绩，002课程成绩
select * from s_score where c_id='01';
select * from s_score where c_id='02';
--方法一
select c1.s_id s_id,c1.s_score score1,c2.s_score score2 from (select * from s_score where c_id='01') c1,(select * from s_score where c_id='02') c2
where c1.s_id=c2.s_id and c1.s_score>c2.s_score;
--方法二
select s1.s_id from s_score s1,s_score s2
where s1.s_id=s2.s_id and s1.c_id='01' and s2.c_id='02' and s1.s_score>s2.s_score;
--扩展，需要查找学生姓名时，用连接将学生表与其他两表连接
select s_student.*,s1.s_score,s2.s_score from s_student, s_score s1,s_score s2
where s_student.s_id=s1.s_id and s_student.s_id=s2.s_id
and s1.s_id=s2.s_id and s1.c_id='01' and s2.c_id='02' and s1.s_score>s2.s_score;

select s_student.*,s1.s_score,s2.s_score from s_student inner join s_score s1 on s_student.s_id=s1.s_id
inner join s_score s2 on s_student.s_id=s2.s_id
where s1.s_id=s2.s_id and s1.c_id='01' and s2.c_id='02' and s1.s_score>s2.s_score;

这篇好文章是转载于：学新通技术网