sql练习

表关系

强化练习
-- 1、查询每个平均成绩大于70分的同学的学号和平均成绩。
select student_id,avg(score) from studentcourse group by student_id having avg(score)>70;

-- 2、查询每个同学的学号、姓名、选课数、总成绩。
思路
 select student_id,sum(score) from studentcourse group by student_id having sum(score)
 select student_id,count(course_id) from studentcourse group by student_id having count(course_id)
 select student_id,course_id from studentcourse group by student_id,course_id

答案
select student.*,temp.cou,temp.sumscore from student,
(select student_id,count(*)as cou,sum(score)as sumscore from studentcourse group by student_id)
as temp where student.id=temp.student_id
注意：student_id,sum(score) ,count(course_id)这三个字段放在一起报错，重点count(*)


-- 3、查询学过赵云老师所教课的同学的学号、姓名。
select student.id,student.name from student,
(select student_id from studentcourse where course_id in (
select id from course where teacher_id =(
select id from teacher where name='赵云'
)))as temp where temp.student_id=student.id

查询学过张飞老师课的学生

select s.`name` from teacher t,course c,studentcourse sc,student s 
where t.id = c.teacher_id and c.id = sc.course_id and sc.student_id = s.id
and t.name ='张飞' GROUP BY s.id;	

没有学全所有课程的学生

select s.name from student s,studentcourse sc
where s.id = sc.student_id
GROUP BY s.id having count(course_id)<(select count(course_id) from studentcourse)

增加几位老师，学生，课（有学生没有上课，有课没学生上）

insert into teacher VALUES(null,'托尼');
INSERT into teacher VALUES(null,'芭比');

insert into course VALUES(null,'地理','3');
insert into course VALUES(null,'美术','2');

insert into student values(null,'大白','江苏','19');
INSERT INTO student VALUES(null,'大黄','上海','20');

INSERT into studentcourse values(5,4,90);

所有上课学生的名字，课程名

select s.`name`,c.`name` from student s,studentcourse sc,course c
where s.id=sc.student_id and c.id=sc.course_id
GROUP BY s.id,c.id

查询所有学生的选课信息

SELECT temp.name,c.`name` from course c RIGHT JOIN(
select name,course_id from student s left join studentcourse sc on s.id=sc.student_id
) as temp on temp.course_id=c.id   ---第一次做的答案

SELECT s.`name`,c.`name` from student s LEFT JOIN studentcourse sc on s.id = sc.student_id
LEFT JOIN course c on c.id = sc.course_id 

以下题目来自
https://blog.youkuaiyun.com/fashion2014/article/details/78826299

1、查询"01"课程比"02"课程成绩高的学生的信息及课程分数

select s_score from score where c_id='01'

select a.*,b.s_score,c.s_score from student a JOIN score b on a.s_id = b.s_id and b.c_id='01'
LEFT JOIN score c on a.s_id = c.s_id and c.c_id='02' where b.s_score > c.s_score

select a.*,b.s_score,c.s_score from student a, score b ,score c
where a.s_id=b.s_id and a.s_id = c.s_id and b.c_id = '01' and c.c_id = '02' and b.s_score > c.s_score

2、查询"01"课程比"02"课程成绩低的学生的信息及课程分数

select a.*,b.s_score,c.s_score from student a join score b on a.s_id=b.s_id and b.c_id='01'
LEFT JOIN score c on a.s_id=c.s_id and c.c_id='02' where b.s_score <c.s_score

3、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩

--查询每个学生的平均成绩
select ROUND(AVG(a.s_score),2) avg_score  from score a GROUP BY a.s_id having avg_score >=60;

select a.s_id,s_name ,ROUND(AVG(b.s_score),2) avg_score from student a left join score b on a.s_id=b.s_id 
GROUP BY a.s_id having avg_score>=60
就是一个列，只是加了一些聚合函数而已，可以起别名

– 4、查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩(包括有成绩的和无成绩的)

---我的答案（不包含分数为零的，错）
select a.s_id,s_name,ROUND(AVG(s_score),2) avg_score from student a 
join score b on a.s_id = b.s_id GROUP BY a.s_id having avg_score<60

--转载博客答案（包含分数为零的）
select b.s_id,b.s_name,ROUND(AVG(a.s_score),2) as avg_score from 
student b 
left join score a on b.s_id = a.s_id
GROUP BY b.s_id,b.s_name HAVING avg_score <60
union
select a.s_id,a.s_name,0 as avg_score from 
student a 
where a.s_id not in (
			select distinct s_id from score);

--我的答案优化
select a.s_id,s_name,ROUND(AVG(s_score),2) avg_score from student a 
left join score b on a.s_id = b.s_id GROUP BY a.s_id 
having avg_score<60 or avg_score is null;

5、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩

select a.s_id,s_name,COUNT(c_id),sum(s_score) from student a 
LEFT JOIN score b on a.s_id=b.s_id
GROUP BY a.s_id

6、查询"李"姓老师的数量

select COUNT(t_name )from teacher where t_name like '李%'

7、查询学过"张三"老师授课的同学的信息

select s.* from teacher t join course c on t.t_id=c.t_id 
JOIN score sc on c.c_id = sc.c_id join student s on sc.s_id= s.s_id where t_name='张三' 

8、查询没学过"张三"老师授课的同学的信息

select * from student where s_id not in(
select s_id from teacher t join course c on t.t_id=c.t_id join score sc on c.c_id =sc.c_id
where t_name='张三'
)

9、查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息

select s.* from student s 
join score a on s.s_id= a.s_id  and a.c_id='01'
join score b on s.s_id= b.s_id and  b.c_id='02'

--转载博客答案
select a.* from 
student a,score b,score c 
where a.s_id = b.s_id  and a.s_id = c.s_id and b.c_id='01' and c.c_id='02';

10、查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息

select s.* from student s
where s_id in(select s_id from score where c_id ='01') AND
s_id not in(SELECT s_id from score where c_id = '02')

！！！！优化答案暂未想出

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

select s.*from student s 
join score sc on s.s_id= sc.s_id 
GROUP BY sc.s_id having count(sc.c_id) < (select count(c_id) from course)

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

我的
select distinct * from student where s_id in(
select s_id from score where c_id in(
select c_id from score where s_id='01'
))

select * from student where s_id in(
select distinct a.s_id from score a where a.c_id in(select a.c_id from score a where a.s_id='01')
);

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

我的（这里因为总共只有三门课，所有答的是除了"01",其他全选了课程的学生信息）
select s.* from student s inner join score sc on s.s_id = sc.s_id 
group by s_id having count(c_id) =(select count(c_id) from score where s_id='01') and s_id != '01'

最终
select s.* from student s inner join score sc on s.s_id = sc.s_id  and c_id 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 sc.s_id from teacher t join course c on t.t_id = c.t_id 
join score sc on sc.c_id = c.c_id where t.t_name='张三')

15、查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩（待思考）

我的（答案不对，应该有两名学生，这个输出来只有一个）
select s.s_id,s.s_name,avg(s_score) from score sc right join student s on s.s_id=sc.s_id 
where s_score<60
group by s.s_id having count(c_id)>2 

正确的
select a.s_id,a.s_name,ROUND(AVG(b.s_score)) from 
student a 
left join score b on a.s_id = b.s_id
where a.s_id in(
		select s_id from score where s_score<60 GROUP BY  s_id having count(1)>=2)
GROUP BY a.s_id,a.s_name

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

select s.*,s_score from score sc join student s on s.s_id=sc.s_id
 where c_id ='01' and s_score<60 order by s_score desc

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

select avg(a.s_score),b.s_score ,c.s_score ,d.s_score  from score a 
join score b on b.c_id ='01'
join score c on c.c_id ='02'
join score d on d.c_id ='03'
group by a.s_id order by avg(a.s_score) desc

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

select a.c_id,b.c_name,MAX(s_score),MIN(s_score),ROUND(AVG(s_score),2),
ROUND(100*(SUM(case when a.s_score>=60 then 1 else 0 end)/SUM(case when a.s_score then 1 else 0 end)),2) as 及格率,
ROUND(100*(SUM(case when a.s_score>=70 and a.s_score<=80 then 1 else 0 end)/SUM(case when a.s_score then 1 else 0 end)),2) as 中等率,
ROUND(100*(SUM(case when a.s_score>=80 and a.s_score<=90 then 1 else 0 end)/SUM(case when a.s_score then 1 else 0 end)),2) as 优良率,
ROUND(100*(SUM(case when a.s_score>=90 then 1 else 0 end)/SUM(case when a.s_score then 1 else 0 end)),2) as 优秀率
from score a left join course b on a.c_id = b.c_id GROUP BY a.c_id,b.c_name

#case when 了解

19、按各科成绩进行排序，并显示排名