日本做a爱片网站/软件开发平台

https://www.cnblogs.com/Eva-J/articles/9688383.html 里面的综合练习题

1、查询男生、女生的人数；

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

select * from(

(select * from score where course_id in (select cid from course where cname = '生物')) t1

left join

(select * from score where course_id in (select cid from course where cname = '物理')) t2

ont1.student_id = t2.student_id)

where t1.num > t2.num;

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

# 先查看每个同学的平均分数

select student_id,avg(num) from score group by student_id;

# 在筛选成绩大于60分的同学的学号和平均成绩；

# select student_id,avg(num) from score group by student_id having avg(num) > 60;

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

# 先查看每个同学的总成绩

select student_id,sum(num) from score group by student_id;

# 学生和课程的关系只有成绩表中存在，因此要获取每个学生选择的课程，需要通过score表

select count(sid),student_id from score group by student_id;

# 将上面两步合并

select sum(num),count(sid),student_id from score group by student_id;

# 将学生的信息和成绩选课情况拼在一起

select sid,sname,sum_num ,count_stu

from student

left join

(select sum(num) sum_num,count(sid) count_stu,student_id from score group by student_id) t2

onsid = student_id;

# 还可以更严谨，那些没有选课的同学选课数和总成绩应该是0

select sid,sname,

(

CASE

WHEN sum_num isnull THEN 0

ELSE sum_num

END

) as sum_num ,

(

CASE

WHEN count_stu isnull THEN 0

ELSE count_stu

END

) as count_stu

from student

left join

(select sum(num) sum_num,count(sid) count_stu,student_id from score group by student_id) t2

onsid = student_id;

4、查询姓“李”的老师的个数；

# 找到所有姓李的

# 方法一

# select * from teacher where tname like '李%';

# 方法二

# select * from teacher where tname regexp '^李';

# 统计个数

select count(tid) from teacher where tname regexp '^李';

或者

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

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

# 找到张磊老师的id

select tid from teacher where tname == '张磊老师';

# 找到张磊老师所教课程

select cid from course where teacher_id = (select tid from teacher where tname = '张磊老师');

# 找到所有学习这门课的学生id

select student_id from score where course_id = (select cid from course where teacher_id = (select tid from teacher where tname = '张磊老师'));

# 找到没有学过这门课的学生对应的学生学号、姓名

select sid,sname from student where sid not in

(select student_id from score where course_id = (select cid from course where teacher_id = (select tid from teacher where tname = '张磊老师'))

);

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

# 先查询学习课程id为1的所有学生

select * from score where course_id = 1;

# 先查询学习课程id为2的所有学生

select * from score where course_id = 2;

# 把这两张表按照学生的id 内连接起来去掉只学习某一门课程的学生

select t1.student_id from

(select student_id from score where course_id = 1)t1

inner join

(select student_id from score where course_id = 2) t2

on t1.student_id = t2.student_id

# 根据学号在学生表中找到对应的姓名

select sid,sname from student where sid in (select t1.student_id from (select student_id from score where course_id = 1)t1 inner join (select student_id from score where course_id = 2) t2 on t1.student_id = t2.student_id);

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

#找到李平老师的tid

select tid from teacher where tname ='李平老师';

# 找到李平老师教的所有课程cid

select cid from course where teacher_id in (select tid from teacher where tname ='李平老师');

# 找到李平老师教的所有课程数

select count(cid) from course where teacher_id in (select tid from teacher where tname ='李平老师');

# 找到所有学习李平老师课程的学生

select * from score where course_id in ( select cid from course where teacher_id in (select tid from teacher where tname ='李平老师'));

# 查看所有学习李平老师课程的学生选课数

select student_id,count(course_id) from score where course_id in ( select cid from course where teacher_id in (select tid from teacher where tname ='李平老师')) group by student_id;

# 找到所有选择了李平老师所有课程的学生id

selectstudent_id from (

select student_id,count(course_id) course_count from score where course_id in ( select cid from course where teacher_id in (select tid from teacher where tname ='李平老师')) group by student_id) t1

where t1.course_count =

(select count(cid) from course where teacher_id in (select tid from teacher where tname ='李平老师'));

# 找到学生的其他信息

select sid,sname from student where sid in (

selectstudent_id from (

select student_id,count(course_id) course_count from score where course_id in ( select cid from course where teacher_id in (select tid from teacher where tname ='李平老师')) group by student_id) t1

where t1.course_count =

(select count(cid) from course where teacher_id in (select tid from teacher where tname ='李平老师'))

);

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

# 先找到每个学生的课程编号“1”的和课程编号“2”的成绩组成一张表

select t1.student_id from (select num num2,student_id from score where course_id = 2) t2 inner join (select student_id,num num1 from score where course_id = 1) t1 on t1.student_id = t2.student_id

# 再找到课程编号“2”的成绩比课程编号“1”课程低的所有学生的学号

select t1.student_id from (select num num2,student_id from score where course_id = 2) t2 inner join (select student_id,num num1 from score where course_id = 1) t1 on t1.student_id = t2.student_id where num2 < num1

# 再找到所有学生的学号、姓名

select sid,sname from student where sid in(select t1.student_id from (select num num2,student_id from score where course_id = 2) t2 inner join (select student_id,num num1 from score where course_id = 1) t1 on t1.student_id = t2.student_id where num2 < num1);

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

# 先查询成绩小于60分的同学的学号

select distinct student_id from score where num < 60;

# 再查询有课程成绩小于60分的同学的学号、姓名

select sid,sname from student where sid in (select distinct student_id from score where num < 60);

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

# 先看看学号为1的同学都学了哪些课程

select course_id from score where student_id = 1

# 找到学习学号为1的同学所学课程的学号

select distinct student_id from score where course_id in (select course_id from score where student_id = 1);

#找到学习学号为1的同学所学课程的学号\姓名

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

11、课程平均分从高到低显示

select course_id,avg(num) avg_num from score group by course_id order by avg_num desc;

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

# 查询出只选修了一门课程的全部学生的学号

select student_id,count(student_id) from score group by student_id having count(student_id) =1;

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

select sid,sname from student where sid in (select student_id from score group by student_id having count(student_id) =1);

13、查询男生、女生的人数；

select gender,count(sid) from student group by gender;

14、查询姓“张”的学生名单；

select * from student where sname like '张%';

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

# 查询成绩的最高分

select course_id c1,max(num) from score group by course_id

# 查询成绩的最低分

select course_id c1,min(num) from score group by course_id

# 查询成绩的最高分和最低分拼接

select * from ( (select course_id c1,max(num) from score group by course_id) t1 inner join (select course_id c2,min(num) from score group by course_id) t2 on t1.c1 = t2.c2 );

# 格式整理

select t1.c1,t1.max_num,t2.min_num from ( (select course_id c1,max(num) max_num from score group by course_id) t1 inner join (select course_id c2,min(num) min_num from score group by course_id) t2 on t1.c1 = t2.c2 );