# mysql练习题

2、查询“生物”课程比“物理”课程战绩高的具备学员的学号；
1)
select A.student_id,生物分数,物理分数 from
(select student_id,num as 生物分数
from score
where course_id=(select cid from course where cname=’生物’)) as A
left join
(select student_id,num as 物理分数
from score
where course_id=(select cid from course where cname=’物理’)) as B
on A.student_id = B.student_id where 生物分数 >
if(isnull(物理分数),0,物理分数);
2)
select A.student_id,生物分数,物理分数 from
(select student_id,num as 生物分数 from score left join course on
score.course_id = course.cid where course.cname = ‘生物’) as A
left join
(select student_id,num as 物理分数 from score left join course on
score.course_id = course.cid where course.cname = ‘物理’) as B
on A.student_id = B.student_id where 生物分数 >
if(isnull(物理分数),0,物理分数);

3、查询平均战表超过60分的同桌的学号和平均战绩；
select student_id,avg(num) as 平均成绩 from score group by student_id
having 平均成绩 > 60;

4、查询全数同学的学号、姓名、选课数、总战绩；
select student.sid,sname,count(student_id),sum(num)
from score right join student
on student.sid=student_id
group by student_id;

5、查询姓“李”的教师职员和工人的个数；
select count(*) from teacher where tname like ‘李%’;

6、查询没学过“李平先生”老师课的同室的学号、姓名；
select distinct student.sid,sname from student where sid not in (
select distinct student_id
from score
where course_id in(
select cid
from course left join teacher
on course.teacher_id=teacher.tid
where tname = ‘李平先生’)
);

7、查询学过“001”何况也学过数码“002”课程的同室的学号、姓名；
1)
select A.sid,A.sname from
(select student.sid,sname from score right join student
on student.sid=student_id
where course_id=’001′) as A
inner join
(select student.sid,sname from score right join student
on student.sid=student_id
where course_id=’002′) as B
on A.sid=B.sid
2)
select student_id,sname from
(select student_id,course_id from score where course_id = 1 or
course_id = 2) as B
left join student on B.student_id = student.sid group by student_id
HAVING count(student_id) > 1

8、查询学过“叶平”老师所教的全体课的校友的学号、姓名；
select student.sid,sname from score right join student
on student.sid=student_id
where course_id in(
select cid from course left join teacher
on course.teacher_id=teacher.tid
where tname=’李平先生’
) group by student.sid HAVING count(student.sid) = (
select count(cid) from course left join teacher
on course.teacher_id=teacher.tid
where tname=’李平先生’
);

9、查询课程编号“002”的大成比课程编号“001”课程低的全数同学的学号、姓名；
select sname,sid from student inner join (
select A.student_id from
(select student_id,num as s1 from score left join course on
score.course_id = course.cid where course.cid = ‘001’) as A
left join
(select student_id,num as s2 from score left join course on
score.course_id = course.cid where course.cid = ‘002’) as B
on A.student_id = B.student_id where s1 > if(isnull(s2),0,s2)
) as C
on C.student_id=student.sid;

10、查询有学科成绩小于60分的同窗的学号、姓名；
select distinct student.sid,sname from student left join score
on student.sid=score.student_id
where num<60;

11、查询没有学全全部课的同校的学号、姓名；
select student.sid,sname from score right join student
on student.sid=score.student_id
group by student_id
having count(student_id) != (
select count(*) from course
);

12、查询至少有一门课与学号为“001”的同班所学一样的同班的学号和人名；
select student.sid,sname,count(sname) from score right join student
on student.sid=student_id
where student_id !=’001′ and course_id in (
select course_id from score where student_id=’001′
) group by student_id;

13、查询至少学过学号为“001”同学全体课的别的同学学号和人名；
select student.sid,sname from score right join student
on student.sid=student_id
where student_id !=’001′ and course_id in (
select course_id from score where student_id=’001′
) group by student_id
having count(sname) >= (
select count(*) from score
where student_id=’001′);

14、查询和“002”号的校友学习的教程完全同样的其余同学学号和人名；

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

15、删除学习“叶平”老师课的score表记录；
delete from score where course_id in (
select cid from course left join teacher on course.teacher_id=tid where
tname=’李平先生’
)

16、向SC表中插入一些记下，这么些记录要求符合以下法规：①尚无上过编号“002”课程的同窗学号；②插入“002”号课程的平均成绩；
insert into score(student_id, course_id, num) select sid,2,(select
avg(num) from score where course_id = 2)
from student where sid not in (
select student_id from score where course_id = 2
)

17、按平均战绩从低到高

select student_id,
(select num from score s left join course c on s.course_id=c.cid where
cname=’生物’ and sc.student_id=s.student_id) as 生物分数,
(select num from score s left join course c on s.course_id=c.cid where
cname=’物理’ and sc.student_id=s.student_id) as 物理分数,
(select num from score s left join course c on s.course_id=c.cid where
cname=’体育’ and sc.student_id=s.student_id) as 体育分数,
(select num from score s left join course c on s.course_id=c.cid where
cname=’美术’ and sc.student_id=s.student_id)as 绘画分数,
count(course_id),avg(num)
from score sc
group by student_id
order by avg(num);

18、查询各科战表最高和最低的分：以如下形式显得：课程ID，最高分，最低分；
select course_id,max(num),min(num) from score group by course_id;

19、按各科平均成绩从低到高和及格率的比例从高到低依次；
1)
select
avg(num),score.course_id,课程人数,课程及格人数,(课程及格人数/课程人数*100)
as 及格率 from score left join
(select course_id,count(student_id) as 课程人数 from score group by
course_id order by course_id) as A
on score.course_id=A.course_id
left join
(select course_id,count(student_id) as 课程及格人数 from score where
num >=60 group by course_id order by course_id) as B
on A.course_id=B.course_id
group by score.course_id
having
order by avg(num),及格率 desc;
2)
select course_id, avg(num) as avgnum,sum(case when score.num > 60
then 1 else 0 END)/count(1)*100 as percent from score group by
course_id order by avgnum asc,percent desc;

20、课程平均分从高到低突显（现实任课老师）；
select tname,avg(课程平均分) from
(select tname,c.cid from teacher t right join course c on
t.tid=c.teacher_id) as A
left join
(select avg(num) as 课程平均分,course_id from score group by course_id
order by avg(num) desc) as B
on A.cid=B.course_id
group by tname
order by avg(课程平均分) desc;

21、查询各科成绩前5名的记录:(不思考战表并列情状)；
select distinct cname,course_id,
(select num from score as s2 where s2.course_id = s1.course_id order
by num desc limit 0,1) as first_num,
(select num from score as s2 where s2.course_id = s1.course_id order
by num desc limit 1,1) as second_num,
(select num from score as s2 where s2.course_id = s1.course_id order
by num desc limit 2,1) as third_num,
(select num from score as s2 where s2.course_id = s1.course_id order
by num desc limit 3,1) as fourth_num,
(select num from score as s2 where s2.course_id = s1.course_id order
by num desc limit 4,1) as fifth_num
from score s1 left join course c on c.cid=s1.course_id;

22、查询每门课程被选修的学生数；
select course_id,count(student_id) from score group by course_id;

23、查询出只选修了一门课程的漫天学生的学号和姓名；
select student_id,sname from student st left join score sc on
st.sid=sc.student_id
group by student_id
having count(course_id)=1;

27、查询每门科目标平分战绩，结果按平均成绩升序排列，平均战表同样时，按学科号降序排列；
select course_id,avg(if(isnull(num),0,num)) from score group by
course_id order by avg(num),course_id desc;

28、查询平均成绩超越85的装有学生的学号、姓名和平均成绩；
select student_id,sname,avg(num) from score sc left join student st on
sc.student_id=st.sid group by student_id;

29、查询课程名为“生物”，且分数低于60的学习者姓名和分数；
select sname,num from score sc left join student st on
sc.student_id=st.sid
left join course c on c.cid=sc.course_id
where c.cname=’生物’ and num<60;

31、求选了课程的上学的小孩子人数
select count(distinct student_id) from score

32、查询选修“张磊先生”老师所授课程的上学的小孩子中，战绩最高的上学的小孩子姓名及其战表；
select sname,max(num) from score sc
left join student st on sc.student_id=st.sid
where course_id in (
select cid from course c
left join teacher t on c.teacher_id=t.tid
where tname=’张磊先生’
);

33、查询种种科目及相应的选修人数；
select cname,count(student_id) from score sc
left join course c on c.cid=sc.course_id
group by course_id;

34、查询不一致科目但战表同样的学生的学号、课程号、学生战绩；
select student_id,course_id,num from score where sid in (
select s1.sid from score s1
inner join score s2 on s1.num=s2.num and s1.course_id != s2.course_id)

35、查询每门课程战表最佳的前两名；
select distinct cname,course_id,
(select num from score as s2 where s2.course_id = s1.course_id order
by num desc limit 0,1) as first_num,
(select num from score as s2 where s2.course_id = s1.course_id order
by num desc limit 1,1) as second_num
from score s1 left join course c on c.cid=s1.course_id;

36、检索至少选修两门学科的学习者学号；
select student_id from score sc group by student_id having
count(course_id)>1;

37、查询全部学生都选修的课程的课程号和科目名；
select course_id,cname from score sc
right join course c on c.cid=sc.course_id
group by course_id
having count(student_id)=(
select count(distinct sid) from student
);

38、查询没学过“叶平”老师教学的任一门科指标学习者姓名；
select dictinct sname from score sc
left join student st on sc.student_id=st.sid
where student_id not in (
select student_id from score where course_id in (
select cid from course c left join teacher t on c.teacher_id=t.tid
where tname=’张磊先生’
)
);

39、查询两门以上不比格课程的校友的学号及其平均成绩；
select student_id,avg(num) from score where student_id in (
select student_id from score where num<60 group by student_id
having count(course_id)>1
) group by student_id;

40、检索“004”课程分数小于60，按分数降序排列的同窗学号；
select student_id from score where course_id=4 and num<60 order by
num desc;