【综合练习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查询解答，每个查询都针对一个特定的问题，旨在提取所需的信息。
评论0