数据库实验6-连接查询

2021011203

针对stu数据库中的student、course和sc三个表实现以下查询：

（1）求学生的学号、姓名、选修的课程名及成绩；

SELECT studentfcy.sno,sname,cname,grade
FROM studentfcy, scfcy, coursefcy 
WHERE studentfcy.sno=scfcy.sno and scfcy.cno=coursefcy.cno

有重复的列名指定查询的表 

 查询速度更快：

SELECT studentfcy.sno,sname, cname,grade
FROM  studentfcy INNER JOIN scfcy ON studentfcy.sno=scfcy.sno 
INNER JOIN coursefcy  ON scfcy.cno=coursefcy.cno

（2）求选修1号 课程且成绩在90 分以上的学生学号、姓名及成绩；

SELECT studentfcy.sno,sname,grade
FROM studentfcy, scfcy, coursefcy 
WHERE studentfcy.sno=scfcy.sno and scfcy.cno=coursefcy.cno and coursefcy.cno=1 and grade>90

SELECT studentfcy.sno,sname,grade
FROM studentfcy INNER JOIN scfcy on  studentfcy.sno=scfcy.sno 
WHERE cno=1 and grade>90

（3）查询每一门课的先修课，包括课程号、课程名、先修课程号和先修课程名；

SELECT c1.cno,c1.cname,c2.cno as 先修课程号,c2.cname as 先修课程名
FROM  coursefcy c1,coursefcy c2
WHERE c1.cpno=c2.cno

（4）查询每个学生的基本信息以及他（她）所选修的课程的课程号（包括没有选课的学生）；

SELECT studentfcy.sno,sname,ssex,sage,sdept,cno as 选修课程号
FROM  studentfcy LEFT OUTER JOIN scfcy on studentfcy.sno=scfcy.sno

 能不能使一个人所有选修课排在一行

（5）求课程的课程号、课程名和选修该课程的人数；

SELECT scfcy.cno, cname, count(sno) 选修人数
FROM coursefcy INNER JOIN scfcy ON coursefcy.cno=scfcy.cno 
GROUP BY scfcy.cno,cname

（6）求选修课超过2门课（包括2门）的学生学号、姓名。

SELECT scfcy.sno,sname
FROM studentfcy INNER JOIN scfcy ON studentfcy.sno=scfcy.sno
GROUP BY scfcy.sno,sname
HAVING count(cno)>=2

（7）查询“李勇”的各门课程成绩，要求显示姓名、课程名称和成绩；

SELECT sname as 姓名,cname as 课程名,grade as 成绩
FROM studentfcy,scfcy,coursefcy 
where studentfcy.sno=scfcy.sno and scfcy.cno=coursefcy.cno and sname='李勇'

（8）查询数学系"佟超"的"15"这门课的成绩，要求显示姓名、成绩。

select sname as 姓名,grade as 成绩

from scfcy,coursefcy,studentfcy

where scfcy.cno=coursefcy.cno

and scfcy.sno=studentfcy.sno

and cname='Course {15}'

and sdept='MA'

and sname='佟超'

有一些多余的空格会被识别为错误

（9）查询各系男生的考试平均成绩。

select sdept as 所在系,avg(grade) as 男生平均成绩

from scfcy,studentfcy

where scfcy.sno=studentfcy.sno

and ssex='男'

group by sdept

（10）统计有两门及以上课程不及格的学生的姓名。

SELECT sname

FROM studentfcy INNER JOIN scfcy ON studentfcy.sno=scfcy.sno

where grade<60

GROUP BY scfcy.sno,sname

HAVING count(1)>=2