Student(Sid,Sname,Sage,Ssex) 学生表 Course(Cid,Cname,Tid) 课程表 SC(Sid,Cid,score) 成绩表 Teacher(Tid,Tname) 教师表 1、查询“001”课程比“002”课程成绩高的所有学生的学号; select sid,score from sc where cid='001'; select sid,socre from sc where cid='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 7 查询学过“叶平”老师所教的所有课的同学的学号、姓名; --叶平老师所带的课程编号--------- select cid from course where tid = (select tid from teacher where tname="叶平"); --选过叶平老师课程的学生-----(重要) select sid,cid from sc where cid in (select cid from course where tid = (select tid from teacher where tname="叶平")); --查询学过“叶平”老师所教的所有课的同学的学号---- select sid from (select count(cid) num from sc where cid in (select cid from course where tid = (select tid from teacher where tname="叶平")) group by sid) where num=(select count(cid) from course where tid = (select tid from teacher where tname="叶平")) 8 查询所有课程成绩小于60分的同学的学号、姓名; -----选出学生的姓名---- select distinct sid from sc; -----选出课程成绩>=60的姓名-----(重要) select distinct sid from sc where score>=60; -----选出课程成绩<60的姓名 select distinct sid from sc where sid not in (select distinct sid from sc where score>=60); 9 查询没有学全所有课的同学的学号、姓名; -------所有课程数---- select count(cid) from course; ------没有学全所有课-- select sid,count(cid) from sc group by sid having count(cid)
java面试题学生表成绩表老师表_sql面试题 学生表, 课程表,成绩表,教师表
最新推荐文章于 2023-06-28 20:42:39 发布