1、 S(SNO,SName) 学生关系表。SNO 为学号,Sname 为姓名
2、 C(CNO,CNAME,CTEACHER)课程关系表。CNO 为课程号,CNAME为课程名,CTEACHER为任课教师
3、 SC(SNO,CNO,SCGRADE) 选课关系表。SCGRADE为成绩完成下面的查询语句
1、 找出没有选修过“李明”老师讲授课的所有学生姓名。
2、 列出两门以上(含两门)不及格课程的学生姓名及其平均成绩
3、 列出既学过java课程,又学过c#课程的所有学生姓名
4、 列出java课程成绩比c#课程成绩高的所有学生的学号及其java号课和c#号课的成绩。
2、 C(CNO,CNAME,CTEACHER)课程关系表。CNO 为课程号,CNAME为课程名,CTEACHER为任课教师
3、 SC(SNO,CNO,SCGRADE) 选课关系表。SCGRADE为成绩完成下面的查询语句
1、 找出没有选修过“李明”老师讲授课的所有学生姓名。
2、 列出两门以上(含两门)不及格课程的学生姓名及其平均成绩
3、 列出既学过java课程,又学过c#课程的所有学生姓名
4、 列出java课程成绩比c#课程成绩高的所有学生的学号及其java号课和c#号课的成绩。
select s.sname from s where sno not in
(select distinct sc.sno from sc , c where sc.cno = s.cno and c.CTEACHER = '李明')2、 列出两门以上(含两门)不及格课程的学生姓名及其平均成绩
select s.sname , avg(sc.SCGRADE) SCGRADE from s where sno in
(select sno from sc where SCGRADE < 60 group by sno having count(1) > 1)
group by s.snameselect s.sname , avg(sc.SCGRADE) SCGRADE from s ,
(select sno from sc where SCGRADE < 60 group by sno having count(1) > 1) p
where s.sno = p.sno
group by s.sname3、 列出既学过java课程,又学过c#课程的所有学生姓名
select s.sname from s where sno in
(
select sno from
(
select distinct sc.sno from sc , c where sc.cno = c.cno and c.cname = 'java'
union all
select distinct sc.sno from sc , c where sc.cno = c.cno and c.cname = 'c#'
) t group by sno having count(1) = 2
)4、 列出java课程成绩比c#课程成绩高的所有学生的学号及其java号课和c#号课的成绩。
--如果所有的学生都学了java,c#
select m.sno , m.SCGRADE [java课程成绩] , n.SCGRADE [c#课程成绩] from
(select sc.sno , sc.SCGRADE from sc , c where sc.cno = c.cno and c.CNAME = 'java') m,
(select sc.sno , sc.SCGRADE from sc , c where sc.cno = c.cno and c.CNAME = 'c#') n
where m.sno = n.sno and m.SCGRAD > n.SCGRAD
无法绑定由多个部分组成的标识符 "sc.SCGRADE"。消息 207,级别 16,状态 1,第 2 行
列名 'cno' 无效。不行呀! 通不过
1、 找出没有选修过“李明”老师讲授课的所有学生姓名。
select s.sname from s where sno not in
(select distinct sc.sno from sc , c where sc.cno = s.cno and c.CTEACHER = '李明')2、 列出两门以上(含两门)不及格课程的学生姓名及其平均成绩
select s.sname , avg(sc.SCGRADE) SCGRADE from s,sc where s.sno = sc.sno and sno in
(select sno from sc where SCGRADE < 60 group by sno having count(1) > 1)
group by s.snameselect s.sname , avg(sc.SCGRADE) SCGRADE from s , sc
(select sno from sc where SCGRADE < 60 group by sno having count(1) > 1) p
where s.sno = sc.sno and s.sno = p.sno
group by s.sname3、 列出既学过java课程,又学过c#课程的所有学生姓名
select s.sname from s where sno in
(
select sno from
(
select distinct sc.sno from sc , c where sc.cno = c.cno and c.cname = 'java'
union all
select distinct sc.sno from sc , c where sc.cno = c.cno and c.cname = 'c#'
) t group by sno having count(1) = 2
)4、 列出java课程成绩比c#课程成绩高的所有学生的学号及其java号课和c#号课的成绩。
--如果所有的学生都学了java,c#
select m.sno , m.SCGRADE [java课程成绩] , n.SCGRADE [c#课程成绩] from
(select sc.sno , sc.SCGRADE from sc , c where sc.cno = c.cno and c.CNAME = 'java') m,
(select sc.sno , sc.SCGRADE from sc , c where sc.cno = c.cno and c.CNAME = 'c#') n
where m.sno = n.sno and m.SCGRAD > n.SCGRAD
列名 'cno' 无效。这是为什么? 我被你的这个弄晕了,第四题还弄出n来了。 不过能通过就OK 快点帮我弄弄第一题 吧 谢谢你了
1、 找出没有选修过“李明”老师讲授课的所有学生姓名。
select s.sname from s where sno not in
(select distinct sc.sno from sc , c where sc.cno = c.cno and c.CTEACHER = '李明')
列名 'cno' 无效。你好厉害! distanct 关键字是什么作用呀, 我要想半天了