sql语句面试题练习

1. 与"李威彤"同乡的男生姓名

SELECT sname,snativeplace FROM student WHERE snativeplace IN
	(SELECT snativeplace FROM student WHERE sid IN 
		(SELECT sid FROM student WHERE ssex='男') 
			AND sid IN
		(SELECT sid FROM student WHERE sname='李威彤') 
	)

2. 选修 ‘刘强’ 老师所讲课程的学生人数

select count(sid) from student where sid in
	(select sid from mark where cid in
		(select cid from course where tid in
			(select tid from teacher where tname='刘强')
		)
	)

5. 统计每门课的平均成绩，并按顺序降序排序

select cname,avg(cmark)amk from mark,course group by cname order by amk desc 

待续