综合练习4：企业综合SQL笔试1

【综合练习4：企业综合SQL笔试1】 在本次练习中，我们需要使用SQL语句来解决一系列涉及学生、课程、教师和成绩的数据查询问题。以下是针对每个问题的详细解答： 1. 查询“201”课程比“202”课程成绩高的所有学生的学号： ```sql select a.SID from ( select Sid, score from SC where CID=201 ) a, ( select Sid, score from SC where CID=202 ) b where a.score > b.score and a.Sid = b.Sid; ``` 这个查询通过子查询分别找出“201”和“202”课程的学生及其成绩，然后通过主查询比较两个子查询中的成绩，找出在“201”课程中成绩高于“202”课程的学生。 2. 查询平均成绩大于“60”分的同学的学号和平均成绩： ```sql select SID, avg(score) from sc group by SID having avg(score) > 60; ``` 这个查询使用`GROUP BY`对每个学生的成绩进行平均值计算，然后使用`HAVING`过滤出平均成绩大于60分的学生。 3. 查询“所有”同学的学号、姓名、选课数、总成绩： ```sql select Student.SID, Student.Sname, count(SC.CID), sum(score) from Student left outer join SC on Student.SID = SC.SID group by Student.SID, Sname; ``` 这里使用左外连接将学生表与成绩表关联，通过`COUNT()`计算每名学生的选课数，用`SUM()`计算总成绩，最后按学号和姓名分组。 4. 查询姓“李”的老师的个数： ```sql select count(distinct(Tname)) from Teacher where Tname like '李%'; ``` 此查询通过`LIKE`操作符匹配以“李”开头的教师姓名，使用`COUNT(DISTINCT)`统计不同的“李”姓教师数量。 5. 查询没学过“叶平”老师课的同学的学号、姓名： ```sql # 方法一 select Student.SID, Student.Sname from Student where SID not in ( select distinct(SC.SID) from SC, Course, Teacher where SC.CID = Course.CID and Teacher.TID = Course.TID and Teacher.Tname = '叶平' ); # 方法二 select student.sid, student.sname from student where sid not in ( select sid from sc where cid in ( select cid from course where tid = ( select tid from teacher where tname = '叶平' ) ) ); ``` 两种方法都是通过`NOT IN`子查询找出未选过“叶平”老师课程的学生。 6. 查询学过“201”并且也学过编号“202”课程的同学的学号、姓名： ```sql select Student.SID, Student.Sname from Student, SC where Student.SID = SC.SID and SC.CID = '001' and exists( select * from SC as SC_2 where SC_2.SID = SC.SID and SC_2.CID = '002' ); ``` 此查询使用`EXISTS`子查询检查学生是否同时选修了“201”和“202”两门课程。 7. 查询学过“叶平”老师所教的“所有课”的同学的学号、姓名： ```sql select SID, Sname from Student where SID in ( select SID from SC, Course, Teacher where SC.CID = Course.CID and Teacher.TID = Course.TID and Teacher.Tname = '叶平' group by SID having count(SC.CID) = ( select count(CID) from Course, Teacher where Teacher.TID = Course.TID and Tname = '叶平' ) ); ``` 这个查询首先找出“叶平”老师所教的所有课程，然后筛选出选修这些课程的所有课程的学生。 8. 查询课程编号“202”的成绩比课程编号“201”课程低的所有同学的学号、姓名： ```sql select SID, Sname from ( select Student.SID, Student.Sname, score, (select score from SC as SC_2 where SC_2.SID = SC.SID and SC_2.CID = '201' ) as score_201 from Student, SC where Student.SID = SC.SID and SC.CID = '202' ) subquery where score < score_201; ``` 这里先找出每个学生在“202”课程的成绩，然后通过子查询获取他们在“201”课程的成绩，最后对比并筛选出“202”课程成绩低于“201”课程成绩的学生。 9. 查询“所有课程成绩”小于60分的同学的学号、姓名（取反操作处理）： ```sql select SID, Sname from Student where SID not in ( select SID from SC where score >= 60 ); ``` 这个查询首先找出所有分数大于等于60分的学生，然后通过`NOT IN`子查询找出那些至少有一门课程成绩低于60分的学生。 10. 查询没有学全所有课的同学的学号、姓名（count(CID)得到每个学生的选课数）： ```sql select SID, Sname from ( select Student.SID, Student.Sname, count(distinct SC.CID) as num_courses from Student left outer join SC on Student.SID = SC.SID group by Student.SID, Sname ) subquery where num_courses != (select count(*) from Course) ``` 此查询首先计算每个学生选修的课程数量，然后通过子查询比较这个数量是否等于课程表中的总课程数，从而找出没有学完所有课程的学生。 以上是根据题目描述及提供的数据表结构给出的SQL查询解答，每个查询都针对一个特定的问题，旨在提取所需的信息。