mysql查询练习11-30

mysql查询练习11-30

---

11 查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩

以stuNo分组,获取分组后的count(score) 的成绩数量,判断 COUNT(score) 的数量>=2
select stuscore.*,COUNT(score) from stuscore where score < 60 GROUP BY stuNo HAVING COUNT(score)

select  a.* from student a,(select stuscore.*,COUNT(score) from stuscore 
        where score < 60 GROUP BY stuNo HAVING COUNT(score)) b where a.stuNo = b.stuNo

12.查询至少有一门课与学号为” 01 “的同学所学相同的同学的信息

select * from student where stuNo in (select DISTINCT(stuNo) from stuscore where courseNo in( 
    select courseNo from stuscore where stuNo = '01'))

13.查询和” 01 “号的同学学习的课程完全相同的其他同学的信息

select * from stuscore where courseNo in( 
    select courseNo from stuscore where stuNo = '01') 
                    GROUP BY stuNo HAVING COUNT(score) = (select COUNT(score) from stuscore where stuNo = '01')

14.查询没学过「张三」老师讲授的任一门课程的学生姓名

SELECT teacherNo from teacher where teacherName='张三' -- 获取张三老师teacherNo，查询到张三的teacherNo为01

select courseNo from course where course.courseTeaNo = '01' -- 获取courseTeaNo为01的 courseNo 为02

select stuNo from stuscore where courseNo = '02' -- 获取学过courseNo = '02',的学生id

15.查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
查询所有学生信息并且不包含 (select stuNo from stuscore where courseNo = ‘02’) 这些的Id

SELECT sc.stuNo,s.stuName, AVG(SC.SCORE)
FROM student s,stuscore sc
WHERE s.stuNo=sc.stuNo and courseNo != '2'
GROUP BY s.stuNo,sc.stuNo
having count(case when sc.score <60 then 1 end)>=2

16.检索” 01 “课程分数小于 60，按分数降序排列的学生信息

SELECT stuNo from stuscore where courseNo = '01' and score<60 GROUP BY score desc

select a.* ,b.*from student a RIGHT  JOIN
    (   SELECT * from stuscore where courseNo = '01' and score<60 ) b
            on  a.stuNo = b.stuNo ORDER  BY score desc

17.按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩

select a.stuNo,a.stuName,
    MAX(case c.courseName when '语文' then b.score else null end) 语文,
    MAX(case c.courseName when '数学' then b.score else null end) 数学,
    max(case c.courseName when '英语' then b.score else null end) 英语,
    ifnull(cast(avg(b.score) as decimal(18,2)),0) 平均分

from  student a
LEFT JOIN stuscore b on a.stuNo = b.stuNo
LEFT JOIN course c ON b.courseNo = c.courseNo
GROUP BY a.stuNO
ORDER BY 平均分 desc

18.查看每个学生选择的科目

select stu.stuNo, stu.stuName,
    MAX(case c.courseName when '语文' then c.courseName  else null end) 语文,
    MAX(case c.courseName when '数学' then  c.courseName  else null end) 数学,
    max(case c.courseName when '英语' then  c.courseName  else null end) 英语
FROM  student stu 
LEFT JOIN stuscore scc ON  stu.stuNo = scc.stuNo
LEFT JOIN course c ON c.courseNo = scc.courseNO
GROUP BY scc.stuNo

18.查询各科成绩最高分、最低分和平均分：
– 以如下形式显示：课程 ID ，课程 name ，最高分，最低分，平均分，及格率，中等率，优良率，优秀率
– 及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90

select course.courseNo as 课程Id,course.courseName as 课程名称,MAX(stuscore.score) as 最高分,
MIN(stuscore.score) as 最低分,AVG(stuscore.score) as 平均分,count(*)as 选修人数,
sum(case when stuscore.score>=60 then 1 else 0 end )/count(*)as 及格率,
sum(case when stuscore.score>=70 and stuscore.score<80 then 1 else 0 end )/count(*)as 中等率,
sum(case when stuscore.score>=80 and stuscore.score<90 and stuscore.score<80 then 1 else 0 end )/count(*)as 优良率,sum(case when stuscore.score>=90 then 1 else 0 end )/count(*)as 优秀率
from course
LEFT JOIN stuscore ON course.courseNo = stuscore.courseNo 
GROUP BY 课程Id

19按各科成绩进行排序，并显示排名， Score 重复时保留名次空缺

select stuscore.courseNo ,@curRank:=@curRank+1 as rank,stuscore.score from
(select @curRank:=0) as t ,stuscore  ORDER BY stuscore.score desc

20 按各科成绩进行排序，并显示排名， Score 重复时合并名次

select a.stuNo,a.courseNo,a.score,@j:=@j+1 as j,
@k:=(case when @checkscore = a.score then @k else @j end) as rank,
@checkscore:=a.score as checkscore 
from(
    select * from stuscore ORDER BY score desc
) a,
(select @k:=0,@j:=0,@checkscore=0) t

21.统计各科成绩各分数段人数：课程编号，课程名称，[100-85]，[85-70]，[70-60]，[60-0] 的人数

select * FROM stuscore
where score BETWEEN 0 and 100 and courseNo = 1
-- BETWEEN 85 and 100 区间为: [85,100]

SELECT stuscore.courseNo,course.courseName,
(SUM(case when stuscore.score BETWEEN 85 and 100 then 1 else 0 end))/COUNT(*) as '[85-100]人数',
(SUM(case when stuscore.score BETWEEN 70 and 84 then 1 else 0 end))/COUNT(*)  as '[70-84]人数',
(SUM(case when stuscore.score BETWEEN 60 and 69 then 1 else 0 end))/COUNT(*)  as '[60-69]人数',
(SUM(case when stuscore.score BETWEEN 0  and 59  then 1 else 0 end))/COUNT(*)  as '[0-59]人数'
from stuscore LEFT JOIN course on stuscore.courseNo = course.courseNo

GROUP BY courseNo

22.查询各科成绩前三名的记录

-- 方式一：
select a.stuNo,a.courseNo,a.score from stuscore a   
left join stuscore b on a.courseNo=b.courseNo and a.score<b.score  
group by a.stuNo,a.courseNo,a.score  
having COUNT(b.stuNo)<3  
order by a.courseNo,a.score desc  

-- 方式二：
select * from stuscore a 
where (
    select COUNT(*) from stuscore b where a.courseNO = b.courseNO and a.score <= b.score 
        ORDER BY a.score desc
)<=3 

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

    select courseNo,COUNT(score)from stuscore GROUP BY courseNo;

24.查询出只选修两门课程的学生学号和姓名

select  stuNo,(case when score is  not null then 1 else 0 end) selectNum FROM stuscore GROUP BY stuNo HAVING selectNum = 2

25.查询男生、女生人数

-- 方法1
SELECT sum(case when stuSex= '男' then 1 else 0 end ) as  male,
sum(case when stuSex = '女' then 1 else 0 end) as women
from student


-- 方法2

select  stuSex,COUNT(stuSex) from student GROUP BY stuSex

26.查询名字中含有「风」字的学生信息

select * from student where stuName LIKE '%风%'

27.查询名字相同，性别相同的学生名单，并统计同名人数

select a.stuNo,a.stuName,a.stuSex,COUNT(*) from student a,student b 
    where a.stuName = b.stuName  and a.stuSex = b.stuSex and a.stuNo != b.stuNo
    GROUP BY  a.stuName

28.查询 1990 年出生的学生名单

select * from student where YEAR(stuBrithday)  = 1990

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

select courseNo,avg(score) avgSocre from stuscore GROUP BY courseNo ORDER BY avgSocre,courseNo

30.查询平均成绩大于等于 70 的所有学生的学号、姓名和平均成绩

select a.stuNo,AVG(a.score) avgscore from  stuscore  a   GROUP BY a.stuNo HAVING avgscore>=70