求教多表关联查询语句

Question

 Student (SNO,SNAME）         学生关系表。SNO 为学号，SNAME 为姓名
   Course (CNO,CNAME,CTEACHER)  课程关系表。CNO 为课程号，CNAME 为课程名，CTEACHER 为任课教师
   SC(SNO,CNO,SCGRADE)          选课关系表。SNO 为学号，CNO 为课程号，SCGRADE 为成绩要求使用多表关联查询语句实现如下5个处理：
  1． 找出没有选修过“张锋”老师讲授课程的所有学生姓名
  2． 列出有二门以上（含两门）不及格课程的学生姓名及其平均成绩
  3． 列出既学过“1”号课程，又学过“2”号课程的所有学生姓名
  4． 列出“1”号课成绩比“2”号同学该门课成绩高的所有学生的学号
  5． 列出“1”号课成绩比“2”号课成绩高的所有学生的学号及其“1”号课和“2”号课的成绩

Answer 1

很明显是老师布置的作业.自己做吧,CSDN不是培养懒人的地方.

Answer 2

部分框架代码，复习课本后参考
select * from table1 a
where not exists (select 1 from table2 b
  where b.field=a.column)select id, avg(value)
from table a
where exists (select 1 from table b 
    where b.id=a.id and value<60
    group by id having count(1)>=2)
group by idselect id
from table a
where exists (select 1 from table2 b where a.id=b.id and b.field=@value1)
and exists (select 1 from table2 b where a.id=b.id and b.field=@value2)...

Answer 3

1. 找出没有选修过“张锋”老师讲授课程的所有学生姓名　　--实现代码:　　Select SNAME FROM S　　Where NOT EXISTS(　　Select * FROM SC,C　　Where SC.CNO=C.CNO　　AND CNAME='张锋'　　AND SC.SNO=S.SNO)　　2. 列出有二门以上(含两门)不及格课程的学生姓名及其平均成绩　　--实现代码:　　Select S.SNO,S.SNAME,AVG_SCGRADE=AVG(SC.SCGRADE)　　FROM S,SC,(　　Select SNO　　FROM SC　　Where SCGRADE<60　　GROUP BY SNO　　HAVING COUNT(DISTINCT CNO)>=2　　)A Where S.SNO=A.SNO AND SC.SNO=A.SNO　　GROUP BY S.SNO,S.SNAME　　3. 列出既学过“1”号课程，又学过“2”号课程的所有学生姓名　　--实现代码:　　Select S.SNO,S.SNAME　　FROM S,(　　Select SC.SNO　　FROM SC,C　　Where SC.CNO=C.CNO　　AND C.CNAME IN('1','2')　　GROUP BY SNO　　HAVING COUNT(DISTINCT CNO)=2　　)SC Where S.SNO=SC.SNO　　4. 列出“1”号课成绩比“2”号同学该门课成绩高的所有学生的学号　　--实现代码:　　Select S.SNO,S.SNAME　　FROM S,(　　Select SC1.SNO　　FROM SC SC1,C C1,SC SC2,C C2　　Where SC1.CNO=C1.CNO AND C1.NAME='1'　　AND SC2.CNO=C2.CNO AND C2.NAME='2'　　AND SC1.SCGRADE>SC2.SCGRADE　　)SC Where S.SNO=SC.SNO   　　5. 列出“1”号课成绩比“2”号课成绩高的所有学生的学号及其“1”号课和“2”号课的成绩　　--实现代码:　　Select S.SNO,S.SNAME,SC.[1号课成绩],SC.[2号课成绩]　　FROM S,(　　Select SC1.SNO,[1号课成绩]=SC1.SCGRADE,[2号课成绩]=SC2.SCGRADE　　FROM SC SC1,C C1,SC SC2,C C2　　Where SC1.CNO=C1.CNO AND C1.NAME='1'　　AND SC2.CNO=C2.CNO AND C2.NAME='2'　　AND SC1.SCGRADE>SC2.SCGRADE　　)SC Where S.SNO=SC.SNO

Answer 4

/*
 1）COUNT（*）　　　　　　　　　　返回行数 
　　　 2）COUNT（DISTINCT COLNAME）　　 返回指定列中唯一值的个数 
　　　 3）SUM（COLNAME/EXPRESSION）　　 返回指定列或表达式的数值和； 
　　　 4）SUM（DISTINCT COLNAME）　　　 返回指定列中唯一值的和 
　　　 5）AVG（COLNAME/EXPRESSION）　　 返回指定列或表达式中的数值平均值 
　　　 6）AVG（DISTINCT COLNAME）　　　 返回指定列中唯一值的平均值 
　　　 7）MIN（COLNAME/EXPRESSION）　　 返回指定列或表达式中的数值最小值 
　　　 8）MAX（COLNAME/EXPRESSION）　　 返回指定列或表达式中的数值最大值 已知关系模式：
S (SNO,SNAME）                       学生关系。SNO 为学号，SNAME 为姓名
C (CNO,CNAME,CTEACHER)  课程关系。CNO 为课程号，CNAME 为课程名，CTEACHER 为任课教师
SC(SNO,CNO,SCGRADE)        选课关系。SCGRADE 为成绩要求实现如下5个处理：
  1． 找出没有选修过“李明”老师讲授课程的所有学生姓名 
  4． 列出“1”号课成绩比“2”号同学该门课成绩高的所有学生的学号
  5． 列出“1”号课成绩比“2”号课成绩高的所有学生的学号及其“1”号课和“2”号课的成绩
*/
create table S
(
  SNO int identity (1,1) primary key,
  SNAME varchar (20)
)
insert into S(SNAME) values('aa')
insert into S(SNAME) values('bb')
insert into S(SNAME) values('cc')
insert into S(SNAME) values('dd')
insert into S(SNAME) values('ee')
insert into S(SNAME) values('ff')
insert into S(SNAME) values('gg')
insert into S(SNAME) values('hh')
create table C
(
 CNO int identity(1,1) primary key,
 CNAME varchar(20),--课程名
 CTEACHER varchar(20)--任课教师名
)
insert into C(CNAME,CTEACHER) values('高数','张老师')
insert into C(CNAME,CTEACHER) values('英语','李老师')
insert into C(CNAME,CTEACHER) values('物理','王老师')
insert into C(CNAME,CTEACHER) values('C#','胡老师')
insert into C(CNAME,CTEACHER) values('文学','周老师')
insert into C(CNAME,CTEACHER) values('SQL','刘老师')
insert into C(CNAME,CTEACHER) values('ASP.NET','彭老师')
insert into C(CNAME,CTEACHER) values('ASP.NET','李明')create table SC
(
  SNO int foreign key references S(SNO),
  CNO int foreign key references C(CNO),
  SCGRADE int--成绩
)
if object_id('huguo.dbo.SC') is not null drop table SC
insert into SC(SNO,CNO,SCGRADE) values(1,8,58)
insert into SC(SNO,CNO,SCGRADE) values(2,8,68)
insert into SC(SNO,CNO,SCGRADE) values(3,8,48)
insert into SC(SNO,CNO,SCGRADE) values(4,1,68)
insert into SC(SNO,CNO,SCGRADE) values(1,2,38)
insert into SC(SNO,CNO,SCGRADE) values(2,3,68)
insert into SC(SNO,CNO,SCGRADE) values(3,4,56)
insert into SC(SNO,CNO,SCGRADE) values(2,5,68)
insert into SC(SNO,CNO,SCGRADE) values(5,1,88)
insert into SC(SNO,CNO,SCGRADE) values(6,2,78)
insert into SC(SNO,CNO,SCGRADE) values(7,2,98)
insert into SC(SNO,CNO,SCGRADE) values(8,1,90)
insert into SC(SNO,CNO,SCGRADE) values(2,6,18)
insert into SC(SNO,CNO,SCGRADE) values(1,3,59)
select * from SC
select * from S where not exists(select * from C,SC where C.CNO=SC.CNO and CTEACHER='李明'and S.SNO=SC.SNO)
--列出有二门以上（含两门）不及格课程的学生姓名及其平均成绩
select S.SNO,S.SNAME,CNO_AVG=avg(SC.SCGRADE)
from S,SC,
(
  select SNO from SC
  where SCGRADE<60
  group by SNO
  having count(distinct CNO)>=2
) A where S.SNO=A.SNO and SC.SNO=A.SNO
group by S.SNO,S.SNAME
--列出既学过“1”号课程，又学过“2”号课程的所有学生姓名
select S.SNO,S.SNAME from S,
(
  select SC.SNO
  from SC,C
  where SC.CNO=C.CNO and
  C.CNAME in('ASP.NET,','英语')
  group by SNO
  having count(distinct C.CNO) =2
)ss where S.SNO=ss.SNO
-- 列出“1”号课成绩比“2”号同学该门课成绩高的所有学生的学号SELECT S.SNO,S.SNAME
FROM S,SC SC1,SC SC2
    WHERE SC1.CNO=1
        AND SC2.SNO=2
        AND SC1.SNO=S.SNO
        AND SC1.SCGRADE>SC2.SCGRADE