学生课程成绩
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
如果这篇文章对你有用,可以关注本人微信公众号获取更多ヽ(^ω^)ノ ~
![微信公众号二维码](https://images.cnblogs.com/cnblogs_com/aeolian/1679458/o_wechat_gzh_qrcode.jpg)