Mysql系列课程--第八章 sql精选35道题

mysql精选35道题
数据参照：
Mysql系列课程–第三章 建表 插数据

/1、 查询所有学生的姓名、性别和班级。/

select s_name,s_sex,c.c_no from student s,class c where s.c_no=c.c_no;

/2、 查询杨振老师所教的课程（老师名，课程名）。/

select t.t_name,sub.sb_name from teacher t,subject sub where t.t_no=sub.t_no and t.t_name=
'杨振老师';

/3、 查询成绩表中成绩在60到80之间的所有学生信息（学生名，课程名，成绩）。/

select s_name , sb_name ,score from student s,result r,subject sb 
where r.sb_no=sb.sb_no and r.s_no=s.s_no and score >60 and score < 80;

/4、 查询成绩表中成绩为88，90或100的学生记录（学生名，课程名，成绩）。/

select s_name,sb_name ,score from student s,result r,subject sb where s.s_no=r.s_no and
r.sb_no=sb.sb_no and r.score in (88,90,100);

/5、 查询“极客荣耀”班和性别为“女”的同学记录（学生名,性别，班级）。/

select s_name,s_sex,c_name from student s,class c where s.c_no=c.c_no and 
c.c_name='极客荣耀'and s.s_sex='女'; 

/6、 以年龄降序查询学生所有记录，年龄相同以学号升序查询（学号,学生名，年龄）/

select s_no,s_name,s_age from student order by s_age desc,s_no asc;

/7、 查询各班的学生人数（班级名，人数）/

select count(*),c_name from student s,class c where c.c_no=s.c_no group by c_name; 

/8、查询成绩表中的最高分的学生学号和课程号(学生名，课程名，成绩）。/
/第一种写法/

select s_name,score,sb_name from student s, result r, subject sb where s.s_no=r.s_no and 
r.sb_no=sb.sb_no and score = (select score from result order by score desc limit 1);

/第二种写法/

select s_name,score,sb_name from student s, result r, subject sb where s.s_no=r.s_no and 
r.sb_no=sb.sb_no and score = (select max(score) from result );

/9、查询‘JAVA’课程的平均分（课程名，平均分）。/

select sb_name,avg(score) from result r, subject sb where sb.sb_no=r.sb_no and sb.sb_name='java';

/10、查询成绩表中至少有3名学生选修的课程平均分数（课程名，平均分）。/

select sb.sb_name,avg(r.score) from result r,subject sb
where r.sb_no=sb.sb_no group by sb.sb_name having count(r.s_no)>=3;

/11、查询所选课程中最低分大于70，最高分小于90的学生信息（学生名,课程名,成绩）。/

select s.s_name, sb_name,score from student s,result r ,subject sb where r.sb_no= sb.sb_no and 
s.s_no=r.s_no and score between 71 and 89;

/12、查询杨文老师教了哪些学生（学生名，课程名，老师名）。/

select s.s_name,sub.sb_name,t.t_name from student s,result r,subject sub,teacher t where 
s.s_no=r.s_no and r.sb_no=sub.sb_no and sub.t_no=t.t_no and t.t_name='杨文老师';

/13、查询哪些学生选修了‘JAVA’课程（学生名，课程名）/

select s_name,sb_name from student s,subject  sb,result r where
s.s_no=r.s_no and sb.sb_no=r.sb_no and sb.sb_name= 'java';

/14、查询“极客巅峰”班所选课程的平均分（班级名，平均分）。/

select c_name,avg(score) from  class c ,student s,result r where r.s_no =s.s_no 
and s.c_no =c.c_no and c_name='极客巅峰';

/15、查询选修’JAVA’课程，学生成绩等级(大于90为A，大于80为B，大于60为C，其他为D)。/

select s_name,score ,case when score >90 then 'A'  when score >80 then 'B' when score > 60 then
 'C' when score > 0 then 'D' end  '等级' from student s,result r where s.s_no = r.s_no and 
 r.sb_no=(select sb_no from subject where sb_name='java');

/16、查询选修“JAVA”课程的成绩高于“夏殿欢”同学成绩的所有同学的记录（学生名）。/

select s_name,score from student s,subject sb,result r where s.s_no=r.s_no  and 
r.sb_no=sb.sb_no  and sb.sb_name='JAVA'  and (score) >(select  score  from result 
r,student s where r.s_no=s.s_no  and s_name='夏殿欢' ); 

/17、查询成绩表中选学一门以上课程的同学中分数为非最高分成绩的记录（学生名，课程，成绩)。/

select s.s_name,sb.sb_name,r.score from student s,result r,subject sb where s.s_no=r.s_no
 and r.sb_no=sb.sb_no and s.s_no in (select s1.s_no from student s1,result r1 where 
 s1.s_no=r1.s_no group by s1.s_no having count(*)>1) and r.score<(select max(score) from result);

/18、查询选修”JAVA”且高于“JAVA”课程平均分的学生信息（学生名，课程名，成绩，平均分）。/

select s_name,sb_name,score , (select avg(score) from subject sb,result r where  r.sb_no=
sb.sb_no and sb.sb_name = 'java') s_score from student s,subject sb,result r where s.s_no
=r.s_no  and r.sb_no=sb.sb_no  and sb.sb_name='JAVA' and score > (select avg(score) from 
subject sb,result r where  r.sb_no=sb.sb_no and sb.sb_name = 'java');

/19、查询伍老师所带各课程的平均成绩（老师名，课程名，平均分）。/

select t_name,sb_name,avg(score) from teacher t,subject sb,result r where t.t_no=sb.t_no and
 sb.sb_no=r.sb_no and t.t_name='伍老师' group by sb_name;

/20、查询选修某课程的同学人数多于3人的教师姓名（老师名）。/

select distinct t_name from result r, subject sb, teacher t where r.sb_no=sb.sb_no and sb.t_no=
 t.t_no and sb.sb_no in (select r1.sb_no from result r1 ,subject sb1 where r1.sb_no= sb1.sb_no
 group by r1.sb_no having count(s_no)>3);

/21、查询”暴走萝莉”考试得了几个A，几个B。(大于90为A，大于80为B)/

select grade,count(grade) from (select score s,case when score > 90 then 'A' when 
score > 80 then 'B' else 'D' end as grade from result where s_no = (select s_no from 
student where s_name = '暴走萝莉')) ss where ss.s > 80 group by grade;

/22、极客巅峰JAVA谁考了第一（班级名，课程名，学生名,成绩）/
/第一种写法/

select c_name,sb_name,s_name,score from class c,student s,result r,subject sub where
 c.c_no=s.c_no and s.s_no=r.s_no and r.sb_no=sub.sb_no and c.c_name='极客巅峰' and 
 sub.sb_name='java' order by r.score desc limit 1;

/第二种写法/

select s_name,sb_name,score from student s,class c,subject sb,result r where s.c_no=c.c_no 
and sb.sb_no = r.sb_no and r.s_no=s.s_no and c.c_name='极客巅峰' and sb.sb_name='java' and 
r.score=(select max(score) from result r,subject sb where r.sb_no = sb.sb_no and 
sb.sb_name='java' ); 

/23、每门学科的状元（课程名，学生名,成绩）/

select sb_name,s_name,score from student s,result r,subject sb where s.s_no=r.s_no and 
r.sb_no=sb.sb_no and (sb_name,score) in (select sb_name,max(score) from result r,
subject sb where r.sb_no=sb.sb_no group by sb_name);

/24、查询年龄最大的学生（学生名，年龄）/
/第一种写法/

select s_name,s_age from student order by s_age desc limit 1 ;

/第二种写法/

select  s_name,s_age from  student  where  s_age=(select max(s_age) from student);

/25、查询各班年龄最大的学生（班级名，学生名，年龄）。/

select c.c_name,s.s_name,s.s_age from student s,class c where s.c_no=c.c_no
and (c.c_name,s.s_age) in (select c_name,max(s_age) from student,class where
 student.c_no=class.c_no group by c_name);

/26、查询存在有85分以上成绩的课程（学生名，课程名，成绩）./

select s.s_name,sb.sb_name,r.score from student s,result r,subject sb 
where s.s_no=r.s_no and r.sb_no=sb.sb_no and score>85
and exists (select score from result r1 where r1.score>85) ; 

/27、查询出男老师所教课程的成绩表（学生名，课程名，成绩，老师名）。/

select s_name,c_name,score,t_name from class c ,student s,result r,subject sb,teacher t
where r.sb_no=sb.sb_no and r.s_no=s.s_no and c.c_no = s.c_no and sb.t_no = t.t_no 
and t.t_sex = '男';

/28、查询选修“JAVA“课程且成绩高于选修“C语言”课程的学生信息,并按成绩从高到低次序排序（学生名，课程名，成绩）。/

select s.s_name,sb.sb_name,r.score from student s,result r,subject sb where r.sb_no=sb.sb_no 
and r.s_no=s.s_no and sb.sb_no=(select sb_no from subject where sb_name='java') and r.score
>any(select score from result where sb_no=(select sb_no from subject where sb_name='c语言')) 
order by r.score desc ; 

/29、查询选修“C语言”课程且成绩比选修“JAVA” 课程成绩都高的学生信息（学生名，课程名，成绩）./
/第一种写法/

select s.s_name,sb.sb_name,r.score from student s,subject sb,result r 
where s.s_no=r.s_no and r.sb_no=sb.sb_no and sb.sb_name='C语言'and score>
(select max(score) from result re,subject su where re.sb_no=su.sb_no and sb_name='JAVA');

/第二种写法/

select  s.s_name,sb.sb_name,r.score  from  student s,subject  sb,result  r  
where   s.s_no=r.s_no and r.sb_no=sb.sb_no and sb.sb_name='C语言'and score
 > all(select  score  from result re,subject su where re.sb_no=su.sb_no and sb_name='JAVA');

/30、查询所有“女”老师和“女”学生的信息./

select s_no,s_name,s_sex,s_age from student s where s_sex='女'
union all select t_no,t_name,t_sex,t_age from teacher t where t_sex='女';

/31、查询成绩比该课程平均成绩低的学生的信息（学生名，课程名，成绩，平均分）。/

select sb_name,s_name,score ,t1.a as '平均分'from (select sb_name  sbn,avg(score) a 
from student s,result r,subject sb where s.s_no=r.r_no and sb.sb_no=r.sb_no group by 
sb.sb_name) t1,student s,result r,subject sb where s.s_no=r.r_no and sb.sb_no=r.sb_no 
and  sb_name=t1.sbn and score<t1.a;

/32、查询至少有2名男生的班级（班级名，男生人数）。/

select c.c_name from student s,class c where s.c_no=c.c_no and s.s_sex='男' 
group by c.c_name having count(*)>2;

/33、查询学生表中不姓“王”的学生信息（学生名，性别，年龄）。/

select s_name,s_sex,s_age from student where s_name not like '王%';

/34、查询和“李文辉”同性别并同班的学生信息（学生名，性别，年龄，班级）./

select s.s_name,s.s_sex,s.s_age,c.c_name from student s,class c where s.c_no=c.c_no 
and s.s_name like '李%' and s.c_no=(select c_no from student where s_name='李文辉');

/35、查询所有选修“JAVA”课程的“男”同学的成绩(学生名，性别，课程名，成绩)/

select  s_name,s_sex,sb_name,score from result  r , student s, subject sb where s.s_no=r.s_no 
and sb.sb_no=r.sb_no  and s_sex='男'and sb_name= 'java';

详细课程查询：
Mysql系列课程–第一章 Sql分类 常用命令
Mysql系列课程–第二章 约束 自增主键
Mysql系列课程–第三章 建表 插数据
Mysql系列课程–第四章 备份 普通查询
Mysql系列课程–第五章 高级查询 表连接 子查询 case when
Mysql系列课程–第六章 索引和视图
Mysql系列课程–第七章 触发器和存储过程
Mysql系列课程–第八章 sql精选35道题