数据库(S-C-SC-T)多表查询题目汇总

数据来源

https://blog.csdn.net/lhxez6868/article/details/98942018?utm_medium=distribute.pc_relevant.none-task-blog-BlogCommendFromMachineLearnPai2-1.channel_param&depth_1-utm_source=distribute.pc_relevant.none-task-blog-BlogCommendFromMachineLearnPai2-1.channel_param

1查询PHP课程比c++课程成绩高的所有学生的学号

SELECT S1.SID FROM SC S1,SC S2,COURSE C1,COURSE C2 WHERE C1.CNAME='PHP' AND C2.CNAME='C++' AND S1.CID=C1.CID AND S2.CID=C2.CID AND S1.SCORE>S2.SCORE AND S1.SID =S2.SID;

2查询名字含有’李‘的老师的个数

SELECT COUNT(TID) FROM TEACHER WHERE TNAME LIKE '李%';

3 查询平均成绩大于70分的学生的学号以及平均分

SELECT SID, AVG(SCORE) AS T FROM SC GROUP BY SID HAVING T>70;

4查询各科最高分和最低分

SELECT CID,MAX(SCORE),MIN(SCORE) FROM SC GROUP BY CID;

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

SELECT STUDENT.SID,SNAME,COUNT(CID),SUM(SCORE) FROM STUDENT,SC WHERE STUDENT.SID=SC.SID GROUP BY STUDENT.SID;

6查询学过“李纯”老师课的同学的学号、姓名

SELECT S.SID,SNAME FROM SC,COURSE C,STUDENT S,TEACHER T WHERE T.TNAME='李纯' AND T.TID=C.TID AND C.CID=SC.CID AND SC.SID=S.SID;

7查询没学过“李雷”老师课的同学的学号、姓名

SELECT S.SID,SNAME FROM STUDENT S WHERE S.SID NOT IN (SELECT DISTINCT SC.SID FROM SC WHERE SC.CID IN (SELECT CID FROM TEACHER T,COURSE C WHERE T.TNAME='李雷' AND C.TID=T.TID));

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

SELECT S.SID,SNAME FROM STUDENT S,SC S1,SC S2 WHERE S1.CID='001' AND S2.CID='002' AND S1.SID=S2.SID AND S1.SID=S.SID;

9查询课程编号“002”的成绩比课程编号“001”课程低的所有同学的学号、姓名

SELECT S.SID,SNAME FROM STUDENT S,SC S1,SC S2 WHERE S1.CID='001' AND S2.CID='002' AND S1.SID=S2.SID AND S1.SID=S.SID AND S1.SCORE>S2.SCORE;

10删除学习“李纯”老师课的SC表记录

DELECT FROM SC WHERE CID IN (SELECT CID FROM TEACHER T,COURSE C WHERE C.TID=T.TID AND T.TNAME='李纯');

11查询学过“李雷”老师所教的所有课的同学的学号、姓名

select s.sid,s.sname from student s,(
    select sc.sid,count(sc.sid) from sc where sc.cid in(
        select c.cid from course c,teacher t where c.tid=t.tid and tname='李雷'
    ) 
    group by sc.sid having count(sc.sid)=(
        select count(*) from course c,teacher t where c.tid=t.tid and tname='李雷'
    )
) t
where s.sid = t.sid

12.查询至少有一门课与学号为“1001”的同学所学相同的同学的学号和姓名

select sid,sname from student where sid in(
    select sid from sc where cid in(
        select cid from sc where sid = '1001'
    ) and sid!='1001'
)

13查询没有学全所有课的同学的学号、姓名

select s.sid,s.sname from student s,
    (
        select sid,count(*) from sc group by sid 
            having count(*)=(select count(*) from course)
    ) t
    where s.sid=t.sid

14按各科平均成绩从低到高和及格率的百分数从高到低顺序
补充：concat() 方法用于连接两个或多个数组
百分数表示方法 CONCAT(值1/值2*100,'%')

select s1.cid,avg(s1.score) avg_sc,concat(t.c2/count(s1.score)*100,'%') rate
from sc s1 left join 
    (select cid cid2,count(*) c2 from sc where score>=60 group by cid) t
    on s1.cid = t.cid2
group by s1.cid order by avg_sc,rate desc