数据库原理-实验2-交互式SQL

（5）外连接查询

1）插入数据：产生没有选课的学生信息

insert into stu_end.dbo.s(sno,sname,ssex,sage,sdept) values

('1050330001','刘炼','男',23,'信管') ('1050330002','丁蕾','女',22,'信管') ('1050330003','陈勇','男',24,'信管')

, , ,

('1050330004','余霞','女',21,'信管'), ('1050330005','胡海燕','女',22,'信管'), ('1050330007','付强','男',23,'信管'), ('1050330008','何楠楠','女',22,'信管'), ('1050330009','李晓飞','女',23,'信管'), ('1050330011','蔡盼盼','女',23,'信管'), ('1050330012','赵刚','男',24,'信管'),

('1050330013','涂四超','男',22,'信管'), ('1050330014','陈昌钦','男',21,'信管'), ('1050330015','王爱玲','女',21,'信管'), ('1050330016','王俊','女',21,'信管'), ('1050330017','刘晶','女',23,'信管'), ('1050330018','吴棚','男',23,'信管'), ('1050330019','胡国丰','男',22,'信管'), ('1050330021','叶梦竹','女',21,'信管'), ('1050330022','庄健','男',23,'信管'), ('1050330023','张志琪','女',22,'信管'), ('1050330024','邱珊珊','女',22,'信管'), ('1050330025','张凤','女',23,'信管'), ('1050330026','王杰','男',24,'信管'), ('1050330027','张婷','女',24,'信管'), ('1050330028','吴丹','女',23,'信管'), ('1050330029','弓兴宇','男',22,'信管'), ('1050330031','张美佳','女',23,'信管') 实验操作结果：

（5）左外连接：查询学生信息及选课信息 select s.*,cno,grade

from s left outer join sc12 on s.sno=sc12.sno 实验操作结果：

在本例中，受参照完整性的制约，左外连接与全外连接等价，右外连接与自

然连接等价。

（6）自身连接查询：求每门课的间接先行课

select c1.cno,c2.cpno from c as c1,c as c2 where c1.cpno=c2.cno 实验操作结果：

（7）复合连接条件查询：查询选修了?00002?课程，且至少有一门课程在

85分以上的所有学生信息

select s.*,sc12.grade

from s,sc12 where s.sno=sc12.sno and sc12.cno='00002' and sc12.grade>85 实验操作结果：