mysql语句练习

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

SELECT student.*,s1.s_score,s2.s_score FROM student,score s1,score s2 WHERE student.s_id=s1.s_id AND student.s_id=s2.s_id AND s1.c_id=01 AND s2.c_id=02 AND s1.s_score>s2.s_score

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

SELECT student.s_id,student.s_name,AVG(score.s_score) FROM student,score WHERE student.s_id=score.s_id GROUP BY score.s_id HAVING AVG(score.s_score)>=60

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

-- (包括有成绩的和无成绩的)

SELECT student.s_id,student.s_name,AVG(score.s_score) FROM student,score WHERE student.s_id=score.s_id AND student.s_id NOT IN (SELECT score.s_score FROM score WHERE score.s_score IS NULL )GROUP BY score.s_id HAVING AVG(score.s_score)<60

4.查询学过"张三"老师授课的同学的信息

SELECT student.* FROM student,teacher,score,course WHERE student.s_id=score.s_id AND score.c_id=course.c_id AND course.t_id=teacher.t_id AND teacher.t_name="张三"

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

SELECT student.s_id,student.s_name,COUNT(*),SUM(score.s_score) FROM student,score WHERE student.s_id=score.s_id GROUP BY score.s_id

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

SELECT student.* FROM student,score s1,score s2 WHERE student.s_id=s1.s_id AND s1.s_id=s2.s_id AND s1.c_id=01 AND s2.c_id=02

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

SELECT student.* FROM student WHERE student.s_id IN(SELECT score.s_id FROM score WHERE score.c_id=01) AND student.s_id NOT IN (SELECT score.s_id FROM score WHERE score.c_id=02)