数据库实验报告（头歌）实验三

1.第一关  内连接（1）

select sum(gnum)
From class,teaching
where (class.gno=teaching.gno AND cno='G001');

2.第二关  内连接 （2）

select teacher.tno,tname,cno
from teacher INNER JOIN teaching ON teacher.tno=teaching.tno
where tname='杨梅'

3.第三关 外连接（1）

select class.gno,cno,term,classroom
from teaching
left outer join class  on class.gno=teaching.gno
where grade='18级 '

4.第四关 外连接（2）

select class.gno,cno,term,classroom
 from class join teaching on class.gno=teaching.gno ;

5.第五关 自连接

select gno,gname,gnum
from class
where gnum<all(select gnum
               from class
               where gname='软件19级3班'   
);

6.第六关 不相关子查询（1）

select tname
from teacher join teaching on teacher.tno=teaching.tno
where cno='R003';

7.第七关 不相关子查询（2）

select cno,class.gno,tno
from class join teaching on class.gno=teaching.gno
where class.gnum<all(select gnum from class where gname='软件19级3班');

8.第八关 相关子查询（1）

select class.gno,gname
from class join teaching on class.gno=teaching.gno 
where cno='G001';

9.第九关 相关子查询（2）

select tno,tname
from teacher
where tno in(select tno from class join teaching on class.gno=teaching.gno
            where grade='19绾 ' );

第十关  数据操纵中的子查询-更新

update teaching 
set classroom='B001'
where cno='R009';