sqlzoo练习19

SQL练习-4张表

针对下面的4张表格进行SQL语句的练习。

SQL练习-题目

查询001课程比002课程成绩高的所有学生的学号

需要用到的表：SC

select a.Sid
from (select Sid, score from SC where Cid='001') a,   -- 从SC表中同时选择两个结果，再进行比较
		 (select Sid, score from SC where Cid='002') b
where a.score > b.score
and a.Sid = b.Sid;  -- 保证是同一个学号（学生）的成绩相比较

查询平均成绩大于60分的同学的学号和平均成绩

需要用到的表：SC

select Sid, avg(score)
from SC
group by Sid
having avg(score) > 60;

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

需要用到的表：Student、SC

1
2
3

select Student.Sid, Student.Sname, count(SC.Cid), sum(SC.score)  -- 从两个表中选择4个统计字段
from Student left outer join SC on Student.Sid=SC.Sid   -- 使用外联结
group by Student.Sid, Sname  -- 根据学号和姓名进行分组

查询姓“李”老师的个数

需要使用的表：Teacher

1
2
3

select count(distinct(Tname))  -- 去重之后再进行统计
from Teacher
where tname like '李%';

查询没有学过“小风”老师所教课程的学生的学号、姓名

需要使用的表：Teacher、Student、SC、Course

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='小风';

查询学过“小风”老师所教课程的学生的学号、姓名

需要使用的表：Teacher、Student、SC、Course

select S.Sid, S.Sname
from Student S
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 Teacher.Tname='小风'))

查询学过001和002课程的同学的学号、姓名

需要使用的表：Course、Student

select S.Sid, S.Sname
from Student S, SC
where S.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');

所有课程成绩小于60分的同学的学号和姓名

select Sid, Sname
from Student
where Sid not in (select Student.Sid   -- 将成绩全部大于60分的同学的学号筛选出来，再进行取反not in
                  from Student, SC
                  where Student.Sid=SC.Sid
                  and score > 60;)

检索004课程分数小于60，按照分数降序排列的同学学号

select Sid
from SC
where Cid='004'  -- 指定学号
order by score desc;  -- 降序

删除002（学号）同学的001（课程编号）课程的成绩

1
2
3

delect from SC
where Sid='002'
and Cid='001';

查询2门以上不及格课程的同学的学号（学号）以及平均成绩（score）

需要用到的表：SC

select Sid, avg(isnull(score,0))  -- isnull函数表示：将SC表中所有的NULL替换成0
from SC
where Sid in (select Sid from SC where score < 60 group by Sid having count(*) > 2)   -- 筛选2门不及格
group by Sid;

查询全部学生选修的课程的课程号、课程名

select Cid, Cname
from Course
where Cid in (select Cid   -- 通过分组的方式选择出Cid
              from SC
              group by Cid);

查询不同课程，成绩相同的学生和学号、课程号、学生成绩

select distinct a.Sid, b.Score
from SC a, SC b
where a.Score=b.Score
and a.Cid <>b.Cid;

查询和1002号的同学学习的课程完全相同的其他同学学号和姓名

select Sid
from SC
where Cid in (select Cid from SC where Sid='1002')
group by Sid
having count(*)=(select cont(*) from SC where Sid='1002');   -- 统计的是002选修的课程总数，其他人的总数应该是和他相同

查询各科成绩的前3名记录

select t1.Sid as 学生ID, t1.Cid as 课程ID, score as 分数
from SC t1
where Score in (select top 3 score  -- top3
                from SC
                where t1.Cid=Cid
                order by Score desc);

查询只选修1门课程的全部学生的学号和姓名

select SC.Sid, Student.Sname, count(Cid) as 选课数
from SC, Student
where Sc.Sid=Student.Sid
group by Sc.Sid, Student.Sname
having count(Cid)=1;

查询男女人数

select count(Ssex) as 男生人数
from Student
group by Ssex
having Ssex='男';

select count(Ssex) as 女生人数
from Student
group by Ssex
having Ssex='女';