mysql三表group_MySQL三表查询

16查询所有学生的sname，cname和degree列

--sname -->student

--cname -->course

--degree--score

select sname,cname ,degree from student,course,score

where student.sno=score.sno and course.cno=score.cno;

select sname,cname ,degree ,student.sno as stu_sno ,course.cno as cou_cnofrom student,course,score

where student.sno=score.sno and course.cno=score.cno;

17查询95031班学生每门课的平均分

先将95031班所有学生的信息找出来  select * from student where class ='95031';

select sno * from student where class ='95031';

select * from score where sno in (select sno * from student where class ='95031' );

select  cno ,avg(degree)

from score

where sno

in (select sno * from student where class ='95031' )

group by cno;

18、查询选修3-105课程的成绩高于109号同学3-105成绩的所有同学的记录。(所有选修3-105课程的同学中，哪些是高于109号同学的成绩)

select degree from score where sno='109' and cno='3-105';

select *from score where cno='3-105' and degree>(select degree from score where sno='109' and cno='3-105')

19查询成绩高于学号为109，课程号为3-105同学的成绩的同学的所有记录

select * from score where degree >(select degree from score where sno ='109' and cno='3-105');