MySQL作业题

MySQL作业题

11.查询没有学全所有课程的同学的信息 。

SELECT
st.*
FROM student st
LEFT JOIN score sc ON st.s_id=sc.s_id
GROUP BY st.s_id HAVING COUNT(sc.s_score)<3;

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

SELECT 
DISTINCT st.*
FROM student st 
LEFT JOIN score sc ON sc.s_id=st.s_id
WHERE sc.c_id IN (
SELECT 
sc2.c_id 
FROM student st2
LEFT JOIN score sc2 ON sc2.s_id=st2.s_id
WHERE st2.s_id ='01'
);

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

select * from student 
where s_id in(
select distinct s_id from score where s_id not in
(select s_id from score where c_id not in (select c_id from score where s_id='01'))
group by s_id
having count(c_id)=(select count(c_id) from score where s_id='01') and s_id<>'01');

14.查询没学过"娄心鹏"老师讲授的任一门课程的学生姓名。

select s_name from student 
where s_id not in(
select s_id from score where c_id in(
select c_id from course,teacher
where course.t_id=teacher.t_id and t_name='娄心鹏'));

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

select student.s_id,student.s_name,avg(s_score)
from student, score
where student.s_id=score.s_id and s_score<60
group by student.s_id
having count(c_id)>=2;
# case when then else end
select student.s_id,student.s_name,avg(s_score)
from student, score
where student.s_id=score.s_id 
group by student.s_id
having sum(case when s_score<60 then 1 else 0 end)>=2;

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

SELECT 
st.*,
sc.s_score
FROM student st
LEFT JOIN  score sc 
ON sc.s_id=st.s_id
WHERE sc.c_id='01' AND sc.s_score<60
ORDER BY sc.s_score DESC ;

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

SELECT
 st.*,
 GROUP_CONCAT(c.c_name) 课程,
 GROUP_CONCAT(sc.s_score) 分数,
 AVG(sc.s_score) 平均分 
FROM student st 
LEFT JOIN score sc 
on st.s_id=sc.s_id JOIN course c 
ON sc.c_id=c.c_id 
GROUP BY sc.s_id ORDER BY AVG(sc.s_score) DESC;

18.查询各科成绩最高分、最低分和平均分：以如下形式显示：课程ID，课程name，最高分，最低分，平均分。

select a.cid,cname,max(a.score)"最高分",min(a.score)“最低分”,avg(a.score)"平均分",
       ((select count(sid) from SC where score>=60 and cid=b.cid )/(select count(sid) from SC where cid=b.cid)) "及格率"
from SC a
inner join Course b on a.cid = b.cid
group by b.cid;

19.查询出各科成绩总分，并按总分降序排序：以如下形式显示：课程ID，课程name，总分

select stu.name,c.Java编程,c.应用统计学,c.数据库,c.总成绩 from
(
select a1.stu_id,a1.score 'Java编程',a2.score '应用统计学',a3.score '数据库',b.sumscore '总成绩' from
(select sc.stu_id,sc.score from score sc where sc.subject_id=1001) a1
left join
(select sc.stu_id,sc.score from score sc where sc.subject_id=1002) a2
on a1.stu_id = a2.stu_id left join
(select sc.stu_id,sc.score from score sc where sc.subject_id=1003) a3
on a3.stu_id = a2.stu_id left join
(select sc.stu_id,sum(sc.score) sumscore from score sc group by sc.stu_id) b
on a3.stu_id = b.stu_id order by b.sumscore desc
) c
left join student stu on stu.id = c.stu_id;

20.查询学生的总成绩及学生信息。

SELECT student.s_id, student.s_name, SUM(score.s_score) AS total_score
FROM student
JOIN score ON student.s_id = score.s_id
GROUP BY student.s_id, student.s_name;