面试题---数据库多表查询

1.查询没有选修课名称为“语文”的学生姓名及选修课程

2.列出每门课程名称和平均成绩，并按照升序排序

3.查询选了2门课以上的学生姓名及学生选修的课程总数

1.

SELECT s.s_name,c.c_name FROM 

student s,chosen_class cs,class c WHERE cs.s_id IN

(

    SELECT s_id FROM student WHERE s_id  NOT IN

  (

     (SELECT s_id FROM chosen_class cs WHERE c_id IN 

        ( SELECT c_id FROM class WHERE c_name='语文')

     )

  )

) AND c.c_id=cs.c_id AND s.s_id=cs.s_id

2.

SELECT c.c_name,AVG(cs.grade) 平均成绩 FROM class c LEFT JOIN

chosen_class cs ON c.c_id=cs.c_id GROUP BY(cs.c_id) ORDER BY AVG(cs.grade)

3.

SELECT s.s_name,COUNT(cs.s_id) 选修课总数 FROM 

student s,chosen_class cs,class c WHERE s.s_id IN

(SELECT s_id FROM chosen_class GROUP BY(s_id) 

HAVING COUNT(s_id)>1)

AND c.c_id=cs.c_id AND s.s_id=cs.s_id GROUP BY(cs.s_id)