数据库多表查询和链接的exercise（包含外连接的应用）

一、连接查询
1、求学生的学号、姓名、选修的课程名及成绩；
2、求选修1号课程且成绩在90 分以上的学生学号、姓名及成绩；
3、查询每一门课的先修课，包括课程号、课程名、先修课程号和先修课程名；
4、查询每个学生的基本信息以及他（她）所选修的课程的课程号和成绩（包括没有选课的学生）
5、求课程的课程号、课程名和选修该课程的人数；
6、求选修课超过2门课（包括2门）的学生学号、姓名。
7、查询"李勇"的各门课程成绩，要求显示姓名、课程名称和成绩。
8、查询数学系"刘晨"的"数据库"这门课的成绩，要求显示姓名、成绩。
9、查询各系男生的考试平均成绩。
10、统计有两门及以上课程不及格的学生的学号和姓名。
studnet表：

course表：

cs表：

select sc.sno,sname,cno,grade
from student,sc
where student.sno = sc.sno 

select sc.sno,sname,grade
from student inner join sc on student.sno = sc.sno
where cno=1 and grade>=90

select c1.cno,c1.cname,c2.cpno,c2.cname
from  course c1 left outer join course c2 on c1.cpno = c2.cno

select student.sno,sname,ssex,sage,sdept,cno,grade
from student left outer join sc on student.sno = sc.sno

select sc.cno,cname,count(sno) 选课人数
from course inner join sc on course.cno = sc.cno
group by sc.cno,cname

select student.sno,sname
from student inner join sc on student.sno = sc.sno 
group by student.sno,sname
having count(cno)>=2

select sname,cname,grade
from student inner join sc on student.sno = sc.sno
				inner join course on course.cno = sc.cno
where sname like '%勇'

select sname,grade
from student inner join sc on student.sno = sc.sno
			inner join course on course.cno = sc.cno
where sdept = 'MA' and cname='数据库'

select sdept,AVG(grade)
from student left outer join sc on student.sno = sc.sno
where ssex = '男'
group by sdept

select sc.sno,sname
from student inner join sc on student.sno = sc.sno
where grade < 60
group by sc.sno,sname
having count(grade)>=2