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)