学生课程成绩详解数据库

Student(Sid,Sname,Sage,Ssex) 学生表
Course(Cid,Cname,Tid) 课程表
SC(Sid,Cid,score) 成绩表
Teacher(Tid,Tname) 教师表 

1、查询“001”课程比“002”课程成绩高的所有学生的学号；

select a.sid from (select sid,score from sc where cid=001) a 
join (select sid,socre from sc where cid=002) b on a.sid = b.sid 
where a.score b.score;

2  查询平均成绩大于60分的同学的学号和平均成绩；

select sid,avg(score) from sc group by sid having avg(score) 60;

3  查询所有同学的学号、姓名、选课数、总成绩；

select stu.sid,stu.sname,count(cid),sum(score) from student stu join sc s on stu.sid = s.sid 

4 查询姓“李”的老师的个数；

select count(tid) from teacher where tname like 李%;

5 查询没学过“叶平”老师课的同学的学号、姓名；

 --叶平老师所带的课程编号 

 select cid from course where tid = (select tid from teacher where tname="叶平"); 

 ---选过叶平老师课程的学生编号(重要) 

 select distinct sid from sc where cid in (select cid from course where tid = (select tid from teacher where tname="叶平")); 

 ---没有选过叶平老师课程的学生 

 select sid,sname from student where sid not in (select distinct sid from sc where cid in (select cid from course where tid = (select tid from teacher where tname="叶平")));

6 查询学过“001”并且也学过编号“002”课程的同学的学号、姓名；

 --学过“001”--- 

 select sid from sc cid =001; 

 --学过“002”--- 

 select sid from sc cid =002; 

 --学过001同时学过002(重要 intersect)--- 

 select sid,sname from student where sid in ((select sid from sc cid =001) intersect (select sid from sc cid =002)); 

 --- exists

用一条 SQL  语句查询出每门课都大于 80 

准备数据的 sql 代码： 

create table score(id int primary key auto_increment,namevarchar(20),subject 

varchar(20),score int); 

insert into score values 

(null,张三,语文,81), 

(null,张三,数学,75), 

(null,李四,语文,76), 

(null,李四,数学,90), 

(null,王五,语文,81), 

(null,王五,数学,100), 

(null,王五 ,英语,90); 

select distinct(name) from score where name not in (select distinct(name) from score where score 80) 

select distinct(name) from score t1 where 80 all(select score from score where name = t1.name)

所有部门之间的比赛组合
一个叫 department 的表，里面只有一个字段 name,一共有 4
条纪录，分别是 a,b,c,d,对应四个球对，现在四个球对进行比赛，用一
条 sql 语句显示所有可能的比赛组合.

select a.name,b.name from team a, team b where a.name b.name

原创文章，作者：Maggie-Hunter，如若转载，请注明出处：https://blog.ytso.com/3961.html