mysql练习题练习

1、数据库是按照原文制作的，表格结构一样具体存储的数据有些差异

原文地址：MySQL练习题

原答案地址：MySQL练习题参考答案

2、查询“生物”课程比“物理”课程成绩高的所有学生的学号；

select wl.sid from (select sc.student_id as sid,sc.number as grade from score sc,course c
where c.cid=sc.course_id and c.cname='物理') as wl,(select sc.student_id as sid,sc.number as
grade from score sc,course c where c.cid=sc.course_id and c.cname='生物') as sw where
wl.sid=sw.sid and sw.grade>wl.grade;

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

select student_id,avg(number) as avg from score group by student_id having avg(number)>60;

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

select s.sid,s.sname,w.total,w.sum from student s,(select student_id,count(course_id) as
total,sum(number) as sum from score group by student_id) as w where s.sid=w.student_id;

5、查询姓“李”的老师的个数；

select count(*) from teacher where tname like '钱%';

6、查询没学过“叶平”老师课的同学的学号、姓名；

select s.sid,s.sname from student as s where s.sid not in (select distinct sc.student_id from
score as sc,teacher as t,course as c where t.tid=c.teacher_id and c.cid=sc.course_id and
t.tname='赵');

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

select student.sid,student.sname from student,(select * from score where course_id=1) as w,
(select * from score where course_id=2) as e where w.student_id=e.student_id and
student.sid=w.student_id;

8、查询学过“叶平”老师所教的所有课的同学的学号、姓名；

select s.sid,s.sname from student as s where s.sid in (select distinct sc.student_id from
score as sc,teacher as t,course as c where t.tid=c.teacher_id and c.cid=sc.course_id and
t.tname='赵');

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

select student.sid as id,student.sname as name from student,(select w.student_id as wid from
(select * from score where course_id=1) as w,(select * from score where course_id=2) as e
where w.student_id=e.student_id and w.number>e.number) as m where m.wid=student.sid;

10、查询有课程成绩小于60分的同学的学号、姓名；

select s.sid as id,s.sname as name from student as s,score as sc where s.sid=sc.student_id
and sc.number<60 group by s.sid;

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

select sid,sname from student where sid in (select distinct student_id from score group by
student_id having count(course_id)=(select count(*) from course));

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

select sid,sname from (select distinct student_id from score left join (select course_id from
score left join student on score.student_id = student.sid where student.sid=1) as c on
score.course_id = c.course_id where student_id != 1) s left join student on
s.student_id=student.sid;

13、查询至少学过学号为“001”同学所有一门课的其他同学学号和姓名；

select student.sid,student.sname from student right join score on
student.sid=score.student_id where student_id!=1 and course_id in (select course_id from
score where student_id=1) group by student_id having count(course_id)=(select count
(course_id) from score where student_id=1);

14、查询和“002”号的同学学习的课程完全相同的其他同学学号和姓名；

select sid,sname from student where sid in (select student_id from score right join (select
course_id from score where student_id=1) w on score.course_id=w.course_id where student_id!=1
group by student_id having count(student_id)=(select count(*) from score where
student_id=1));

15、删除学习“叶平”老师课的SC表记录；

delete from score where course_id in (select cid from teacher,course where
teacher.tid=course.teacher_id and teacher.tname='周');

16、向SC表中插入一些记录，这些记录要求符合以下条件：①没有上过编号“002”课程的同学学号；②插入“002”号课程的平均成绩； 

insert into score(student_id,course_id,number) select sid,1,(select avg(number) from score
where course_id=1) from student where sid not in (select student_id from score where
course_id=1);

未完待续