sql原生查询（仅限参考）

sql原生查询（仅限参考）

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

Select * from (select score.s_id,score.c_id,score.s_score as
01_num,student.s_name,student.s_birth,student.s_sex from score left
join student on student.s_id=score.s_id where score.c_id=1 ) tmp left
join student on tmp.s_name=student.s_name left join score on
student.s_id=score.s_id where score.c_id=2 and tmp.01_num >
score.s_score;

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

select * from (select score.s_id,score.c_id,score.s_score as
01_num,student.s_name,student.s_birth,student.s_sex from score left
join student on student.s_id=score.s_id where score.c_id=1 ) tmp left
join student on tmp.s_name=student.s_name left join score on
student.s_id=score.s_id where score.c_id=2 and tmp.01_num <
score.s_score;

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

select student.s_id,student.s_name,avg(s_score) as average from
score left join student on score.s_id=student.s_id group by
student.s_name having avg(s_score)>=60;;

4、 查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩

select student.s_id,student.s_name,avg(s_score) as average from
score left join student on score.s_id=student.s_id group by
student.s_name having avg(s_score)<60;

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

select score.s_id,s_name,count(c_id) as course_num,sum(s_score) as
total_points from student left join score on student.s_id=score.s_id
group by s_name;

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

select t_id, t_name, count(t_name) as num from teacher where t_name
like ‘李%’;

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

select student.s_id,student.s_name,student.s_birth,student.s_sex from
score left join course on score.c_id=course.c_id left join teacher on
course.t_id=teacher.t_id left join student on student.s_id=score.s_id
where teacher.t_name=‘张三’;

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

select student.* from score left join student on
score.s_id=student.s_id where score.s_id not in(select s_id from
score where c_id=2) group by s_id;

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

select student.* from score left join student on
score.s_id=student.s_id where score.s_id in (select score.s_id from
score where score.c_id in (‘01’,‘02’)) group by student.s_name ;

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

select student.* from (select * from score where score.c_id=‘01’ and
score.c_id in(select score.s_id from score where score.c_id =‘02’))
tom left join student on tom.s_id=student.s_id;

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

select * from (select score.*,count(score.c_id) from score group by
score.s_id having count(score.c_id) < 3 ) top left join student on
top.s_id=student.s_id;

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

select student.* from (select * from score where score.c_id in (select
score.c_id from score where score.s_id=01)) tmp left join student on
tmp.s_id=student.s_id group by s_id ;

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

select student.* from (select * from (select score.s_id,score.c_id
from score where score.s_id <> 1 and score.c_id in (select score.c_id
from score where score.s_id = 1)) tmp group by tmp.s_id having
count(tmp.c_id) = (select count(score.c_id) from score where
score.s_id = ‘01’)) stu left join student on stu.s_id=student.s_id;

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

select student.s_name from student where student.s_id not in(select
score.s_id from score where score.c_id = (select course.c_id from
course where course.t_id=(select teacher.t_id from teacher where
teacher.t_name=‘张三’)));

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

select student.s_id,student.s_name, avg(tmp.s_score) from (select *
from score where score.c_id in (select c_id from course) and
score.s_score < 60) tmp left join course on tmp.c_id = course.c_id
left join student on tmp.s_id = student.s_id group by student.s_id;

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

select student. * from score left join student on
score.s_id=student.s_id where score.c_id=1 and s_score <60 order by
s_score desc ;

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

select student.s_name, course.c_name, tmp2.average from (select
tmp.*, avg(tmp.s_score) as average from (select * from score order by
c_id) tmp group by tmp.s_id order by avg(tmp.s_score) desc) tmp2 left
join student on tmp2.s_id=student.s_id left join course on
tmp2.c_id=course.c_id ;

18、 查询每门课程被选修的学生数

select *,count(s_id) from score group by c_id

19、 查询出只有两门课程的全部学生的学号和姓名

select * from student where s_id in (select s_id from score group by
s_id having count(s_id) =2) ;

20、 查询男生、女生人数

select s_sex,count(s_id) as students_num from student group by s_ ;

21、 查询名字中含有"风"字的学生信息

select * from student where s_name like ‘%风%’;