mysql 查询练习

mysql 查询练习

1.3 基础练习
1.查询男生、女生的人数；

select count(case when gender='男' then 1 end) '男',count(case when gender='女' then 1 end) '女' from student ;

2.查询姓“张”的学生名单；

select sid,gender,sname from student where sname LIKE '张%';

3.课程平均分从高到低显示

select student_id,sname,avg(num) from score join 
student on score.student_id =student.sid
group by student_id DESC;

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

select  b.sid ,num ,sname from score a LEFT JOIN student b ON a.student_id=b.sid where num<60 order by num desc;

5.查询至少有一门课与学号为1的同学所学课程相同的同学的学号和姓名；

select distinct student_id,sname from score join student on score.student_id=student.sid where course_id in (select course_id 
FROM score 
where student_id=1);

6.查询出只选修了一门课程的全部学生的学号和姓名；

select student_id,num,b.sname from 
score a LEFT JOIN student b on a.student_id =b.sid
 group by student_id having count(num)=1;

7.查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分；

select cname '科目', min(num) '最低',max(num) '最高' 
from score a join course b on a.course_id=b.cid group by course_id ;

8.查询课程编号“2”的成绩比课程编号“1”课程低的所有同学的学号、姓名；

select  a.student_id, c.sname,a.num from score a join (select * from score where course_id=2) b on a.student_id=b.student_id
join student c on a.student_id=c.sid
where a.course_id=1 AND a.num>b.num;

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

select a.student_id from score a join (select * from score where course_id=2) b
on a.student_id=b.student_id 
where a.course_id=1 and a.num>b.num;

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

select student_id ,avg(num) from score group by student_id having avg(num)>60 ORDER BY avg(num)

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

select  student_id '学号',sname '姓名',count(course_id)'选课数',sum(num)'总成绩' from score join student on score.student_id=student.sid group by student_id;

12.查询姓“李”的老师的个数；

select count(tname) from teacher where tname like '李%'

13.查询没学过“张磊老师”课的同学的学号、姓名；

select student_id,sname from 
score join student on score.student_id=student.sid where student_id not in
(select student_id from score join teacher on score.course_id=teacher.tid where tname='张磊老师') 

14.查询学过“1”并且也学过编号“2”课程的同学的学号、姓名；

select a.student_id,c.sname from score a  join 
(select * from score where course_id=2 GROUP BY student_id) b
on a.student_id= b.student_id join student c on a.student_id=c.sid where a.course_id=1 

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

select student_id,sname from score join student on
 score.student_id=student.sid 
 where course_id in(select tid from teacher where tid=2)

1.4 进阶练习
1.查询没有学全所有课的同学的学号、姓名；

select student_id,sname from score
join student on score.student_id=student.sid
 group by student_id having count(student_id)!=4

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

select * from score where course_id
 in(select course_id from score where student_id=2) 
group by student_id
having count(student_id)=3 and student_id !=2

3.删除学习“李平”老师课的SC表记录；

delete from score where course_id=2;

5.按平均成绩从低到高显示所有学生的“语文”、“数学”、“英语”三门的课程成绩，按如下形式显示： 学生ID,语文,数学,英语,有效课程数,有效平均分；

select student_id,
sum(case when course_id=1 then num end) '生物',
sum(case when course_id=2 then num end) '物理',
sum(case when course_id=3 then num end) '体育',
sum(case when course_id=4 then num end) '美术',
count(course_id) '有效课数',
round(avg(num),2) '有效平均分'
 from score group by student_id

6.查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分；

select course_id ,max(num),min(num)from score group by course_id; 

7.按各科平均成绩从低到高和及格率的百分数从高到低顺序；

select course_id, round(avg(num),2) as pj,CONCAT((round((SUM(case when num>60 then 1 end)/count(student_id))*100,2)),'%') as bfb from score group by course_id 
order by pj asc,bfb desc

8.查询各科成绩前三名的记录:(不考虑成绩并列情况)

(select student_id,course_id ,num from score where course_id=1 order by num desc limit 3)
union 
(select student_id,course_id ,num from score where course_id=2 order by  num desc limit 3)
union 
(select student_id,course_id ,num from score where course_id=3 order by  num desc limit 3)
UNION
(select student_id,course_id ,num from score where course_id=4 order by  num desc limit 3);

9.查询每门课程被选修的学生数；

select case when course_id=1 then '生物'
when course_id=2 then '物理'
when course_id=3 then '体育'
when course_id=4 then '美术'
end  as '科目'
, count(student_id)  from score group by course_id 

10.查询同名同姓学生名单，并统计同名人数；

select sname,count(*) from student GROUP BY sname HAVING count(*)>1

11.查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列；

select course_id,avg(num) from score GROUP BY course_id order by avg(num) asc,course_id DESC

12.查询平均成绩大于85的所有学生的学号. 姓名和平均成绩；

select student_id,sname,round(avg(num),2) from score join student on score.student_id=student.sid group by student_id having avg(num)>85

13.查询课程名称为“生物”，且分数低于60的学生姓名和分数；

select sname,num from score join student on score.student_id=student.sid where course_id=1 and num<60

14.查询课程编号为003且课程成绩在80分以上的学生的学号和姓名；

select student_id,sname from score join student on score.student_id=student.sid where course_id=3 AND num>80

15.求选了课程的学生人数

select count(distinct student_id) as '学生人数' from score 

16.查询选修“杨艳”老师所授课程的学生中，成绩最高的学生姓名及其成绩；

select  course_id,sname, max(num) 
from score a join student b on a.student_id=b.sid  
GROUP BY a.student_id HAVING a.course_id=3

17.查询各个课程及相应的选修人数；

select count(*) from score GROUP BY course_id

18.查询不同课程但成绩相同的学生的学号、课程号、学生成绩；

select student_id,course_id,count(num) from score GROUP BY student_id,course_id having count(num)>1

19.查询每门课程成绩最好的前两名；

(select * from score where course_id=1 ORDER BY num desc LIMIT 2)
union 
(select * from score where course_id=2 ORDER BY num desc LIMIT 2)
union 
(select * from score where course_id=3 ORDER BY num desc LIMIT  2)
union 
(select * from score where course_id=4  ORDER BY num desc LIMIT 2)

20.检索至少选修两门课程的学生学号；

select student_id,count(course_id) from score group by student_id having count(*)>1

21.查询全部学生都选修的课程的课程号和课程名；

select course_id,cname from score join course on score.course_id=course.cid group by course_id having count(course_id)>0

22.查询没学过“朱云海老师”老师讲授的任一门课程的学生姓名；

select sname from score join student on score.student_id=student.sid
GROUP BY student_id 
having  student_id not in (select student_id from score where course_id=4)

23.查询两门以上不及格课程的同学的学号及其平均成绩；

select student_id,sname,sum(case when num<60 then 1 else 0 end) num6 from score a 
join   student b on a.student_id=b.sid
 group by student_id having num6>=2

24.检索“004”课程分数小于60，按分数降序排列的同学学号；

select * from score  where course_id=4 and num<60 order by num desc;

25.删除“002”同学的“001”课程的成绩；

delete from score where student_id=2 AND course_id=1;