mysql50到例题_关于MySQL的经典例题50道 答案参考

答案不全面，欢迎交流沟通

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

select * from  sc s INNER JOIN sc c  INNER JOIN student st on s.S = c.S and st.S=s.S where s.C=01 and c.C=02 and s.score > c.score;

-- //select * from sc s inner join sc c on s.s=c.s where s.c=01 and c.c=02 and s.scorse＞c.scorse;

-- select * from student,(select * from sc where c=01) q INNER JOIN(select * from sc where c=02) w on q.s=w.s  on  where q.score > w.score;//

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

select * from  sc s INNER JOIN sc c  INNER JOIN student st on s.S = c.S and st.S=s.S where s.C=01 and c.C=02 and s.score < c.score;

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

select s.S,avg(s.score),st.Sname from student st INNER JOIN sc s on st.S=s.S  group by s.S having avg(s.score) >=60;

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

select s.S,avg(s.score),st.Sname from student st INNER JOIN sc s on st.S=s.S GROUP BY s.S HAVING avg(score)>60;

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

select st.S,st.Sname, sum(s.score),count(s.C)from student st INNER JOIN sc s ON st.S=s.S GROUP BY s.S;

-- 6、查询"江"姓老师的数量

select count(s) as '总人数'from sc where sc.C=(select t from teacher where Tname='江老师')

-- 7、查询学过"向老师"授课的同学的信息

select * from sc INNER JOIN student on sc.S =student.S where sc.C=(select t from teacher where Tname='向老师') and sc.S=student.S;

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

//select * from teacher tt INNER JOIN course c ON tt.T=c.T INNER JOIN sc s on c.C=s.C INNER JOIN student st on s.S=st.S where tt.Tname !='向老师';

-- 9、查询学过编号为"01"