内蒙古大学 2017-2018《数据库原理与应用A》实验指导书 作业三

(本文中蓝色字体为个人理解,非标准答案,仅供参考)

 

作业一         作业二          作业三           作业四           作业五

=======================================

 

作业三 数据库中数据的查询-----复杂查询

 

一.实验内容及说明

在实验一和实验二的基础上,进行数据库的查询,充分体会 SQL 语言的强大功能。并将每个实验结果均存储为一张视图表。

方式如下:create view T3_*_*[(..)]as                

                (Select ……);//查询语句

 

二.实验步骤 (Oracle 10g测试通过)

 

1. 复合查询(查询结果存储为 T3_1_题目序号)

(1) 查询每个学生及其选修课程的情况(与作业二中11题的进阶二方法相同)

方法1:使用两次left join ... on,把学生表、选课表、课程表左外连接,查询学生所有信息+选课信息+课程信息

create view T3_1_1 as 
select student.sno,sname,ssex,sage,sdno,course.cno,cname,ccredit,grade 
from student left join sc on (student.sno=sc.sno) left join course on(sc.cno=course.cno);

方法2:使用两次left join ... using,把学生表、选课表、课程表左外连接,查询学生所有信息+选课信息+课程信息

create view T3_1_1_2 as 
select sno,sname,ssex,sage,sdno,cno,cname,ccredit,grade 
from student left join sc using(sno) left join course using(cno);

方法3:使用where 某属性=某属性(+) and 某属性=某属性(+),把学生表、选课表、课程表左外连接,查询学生所有信息+选课信息+课程信息

 

create view T3_1_1_3 as 
select student.sno,sname,ssex,sage,sdno,course.cno,cname,ccredit,grade   /*查询sc.sno和查询student.sno是一样的,查询sc.cno和查询course.cno是一样的*/
from student,sc,course 
where student.sno=sc.sno(+) 
and sc.cno=course.cno(+);

查询结果:

 

(2) 查询选修 1001 号课程且分数在 70 分以上的学生学号、姓名及成绩。

方法1:连接学生表和选课表

create view T3_1_2 as 
select sc.sno,sname,grade 
from student,sc 
where student.sno=sc.sno 
and cno='1001' 
and grade>70;

方法2:先将sno,grade查询为派生表t1,再让学生表和派生表t1连接

create view T3_1_2_1 as 
select student.sno,sname,t1.grade 
from student,(select sno,grade from sc where cno='1001' and grade>70) t1 
where student.sno=t1.sno;

查询结果:

 

 

 

(3) 查询‘cs’系所有学生的所有课程的成绩。

方法1:连接学生表、选课表、系别表

create view T3_1_3 as
select student.sno,sname,cno,grade
from student,sc,dept
where student.sno=sc.sno
and student.sdno=dept.dno
and dname='cs';

方法2:连接学生表、选课表、系别表、课程表 (增加课程信息)

create view T3_1_3_1 as 
select student.sdno,dname,student.sno,sname,course.cno,cname,grade 
from student,course,sc,dept 
where student.sno=sc.sno 
and course.cno=sc.cno 
and student.sdno=dept.dno 
and dname='cs';

查询结果:

 

(4) 查询‘cs’系所有教师的所有课程信息。

方法1:连接教师表、课程表、系别表

create view T3_1_4 as
select tno,tname,cno,cname,ccredit
from teacher,course,dept
where teacher.tno=course.ctno
and teacher.tdno=dept.dno
and dname='cs';

方法2:连接教师表、课程表、系别表(增加系别信息)

create view T3_1_4_1 as 
select teacher.tdno,dname,teacher.tno,tname,course.cno,cname,ccredit 
from teacher,course,dept 
where teacher.tno=course.ctno 
and teacher.tdno=dept.dno 
and dname='cs';

查询结果:

 

(5) 查询 “男”教师及其所上的课程信息。

方法1:连接教师表和课程表(缺点:查询不到没有带课的男教师)

create view T3_1_5 as
select tno,tname,tsex,cno,cname,ccredit
from teacher,course
where teacher.tno=course.ctno
and tsex='男';

查询结果:

 

方法2:使用left join ... on,把学生表、选课表左外连接,查询教师信息+课程信息

create view T3_1_5_1_1 as 
select tno,tname,tsex,cno,cname,ccredit 
from teacher left join course on (teacher.tno=course.ctno) 
where tsex='男';

方法2.1:使用where  某属性=某属性(+),把学生表、选课表左外连接,查询教师信息+课程信息

create view T3_1_5_1_2 as 
select tno,tname,tsex,cno,cname,ccredit
from teacher,course 
where teacher.tno=course.ctno(+) 
and tsex='男';

查询结果:(可以看到教师"任白"没有带课,而普通连接查询不到,所以必须使用左外连接查询)

 

(6) 查询和“李勇”同性别的所有同学的 Sname。

方法1:不相关子查询,查询性别相同的姓名(不包括'李勇')

create view T3_1_6 as 
select sname 
from student 
where sname!='李勇'
and ssex=( select ssex 
	   from student 
	   where sname='李勇'
	 );

 

方法2:自身连接,查询性别相同的姓名(不包括'李勇')

create view T3_1_6_1 as 
select s1.sname 
from student s1,student s2 
where s1.ssex=s2.ssex 
and s2.sname='李勇' 
and s1.sname!='李勇';

查询结果:

 

(7) 查询和“李勇”同性别并同系的同学 Sname。

方法1:不相关子查询,查询性别相同,系别相同的姓名(不包括'李勇')

create view T3_1_7 as 
select sname 
from student 
where sname!='李勇'
and ssex=( select ssex 
	   from student 
	   where sname='李勇'
	 ) 
and sdno=( select sdno 
	   from student 
	   where sname='李勇'
	 );

方法2:自身连接,查询性别相同,系别相同的姓名(不包括'李勇')

create view T3_1_7_1 as 
select s1.sname 
from student s1,student s2 
where s1.ssex=s2.ssex 
and s1.sdno=s2.sdno 
and s2.sname='李勇' 
and s1.sname!='李勇';		

查询结果:

 

(8) 查询选修了“数据库”课程且成绩在 80 分以上的所有学生的学号和姓名。

方法1:连接学生表、选课表、课程表

create view T3_1_8 as 
select student.sno,sname 
from student,course,sc 
where student.sno=sc.sno 
and course.cno=sc.cno 
and cname='数据库' 
and grade>80;

方法2:不相关子查询,父查询从学生表查,子查询选课表和课程表连接

create view T3_1_8_1 as
select sno,sname
from student
where sno in( select sno 
	      from sc,course 
	      where course.cno=sc.cno 
	      and cname='数据库' 
	      and grade>80
	    );

方法3:两层不相关子查询,父查询从学生表查,第一层子查询从选课表查,第二层子查询从课程表查

 

create view T3_1_8_2 as
select sno,sname
from student 
where sno in( select sno 
	      from sc 
	      where grade>80 
	      and cno in( select cno 
			  from course 
			  where cname='数据库'
			)
	    );

查询结果:

 

(9) 查询每个学生的学号、姓名、课程及成绩(包括没选修课程的学生情况)。(类似作业二中11题的进阶二方法)

方法1:使用两次left join ... on,把学生表、选课表、课程表左外连接,查询学生信息+课程信息+成绩

create view T3_1_9 as 
select student.sno,sname,course.cno,cname,grade 
from student left join sc on (student.sno=sc.sno) left join course on(sc.cno=course.cno);

方法2:使用两次left join ... using,把学生表、选课表、课程表左外连接,查询学生信息+课程信息+成绩

create view T3_1_9_2 as 
select sno,sname,cno,cname,grade 
from student left join sc using(sno) left join course using(cno);

方法3:使用where 某属性=某属性(+) and 某属性=某属性(+),把学生表、选课表、课程表左外连接,查询学生信息+课程信息+成绩

create view T3_1_9_3 as 
select student.sno,sname,course.cno,cname,grade 
from student,sc,course 
where student.sno=sc.sno(+) 
and sc.cno=course.cno(+);

查询结果:

 

延伸问题:查询每个学生的学号、姓名、课程及成绩(包括没选课的学生 与 没有被选的课程)

方法1:使用full join ... on,把学生表、选课表、课程表全外连接,查询学生信息+课程信息+成绩

create view T3_1_9_X as 
select student.sno,sname,course.cno,cname,grade 
from student full join sc on(student.sno=sc.sno) full join course on(course.cno=sc.cno);

方法2:使用full join ... using,把学生表、选课表、课程表全外连接,查询学生信息+课程信息+成绩

create view T3_1_9_Y as 
select sno,sname,cno,cname,grade
from student full join sc using(sno) full join course using(cno);

查询结果:(可以看到课程"生物信息学"没有学生选择)

 

(10) 查询每门课程的详细授课情况,列课程出号、课名、授课教师姓名及学分。

方法1:连接课程表、教师表(缺点:查询不到没有教师授课的课程)

create view T3_1_10 as 
select cno,cname,tname,ccredit 
from teacher,course 
where teacher.tno=course.ctno;

方法2.1:使用eft join ... on,把课程表、教师表左外连接,查询教师信息+课程信息

create view T3_1_10_2_1 as 
select cno,cname,tname,ccredit
from course left join teacher on(course.ctno=teacher.tno);

方法2.2:使用where 某属性=某属性(+),把课程表、教师表左外连接查询教师信息+课程信息

create view T3_1_10_2_2 as 
select cno,cname,tname,ccredit
from course,teacher
where course.ctno=teacher.tno(+);

查询结果:

 

2. 组合查询和统计查询(查询结果存储为 T3_2_题目序号)

(1) 查询各个系学生选修 1001 课程的平均成绩。   

方法1:连接学生表、选课表,按所在系sdno分组,聚集函数avg()统计平均成绩

create view T3_2_1 as 
select sdno,avg(grade) avg 
from student,sc 
where student.sno=sc.sno 
and cno='1001' 
group by sdno 

方法2:连接学生表、选课表,按所在系sdno分组,聚集函数avg()统计平均成绩,按sdno升序排序

create view T3_2_1_1 as 
select sdno,avg(grade) avg 
from student,sc 
where student.sno=sc.sno 
and cno='1001' 
group by sdno 
order by sdno;

查询结果:

 

(2) 查询各个系学生选修课程的总门数。

方法:连接学生表、选课表,按所在系sdno分组,然后聚集函数avg()在组内统计不重复的cno个数

create view T3_2_2 as 
select sdno,count(distinct cno) count 
from student,sc 
where student.sno=sc.sno 
group by sdno;

查询结果:

 

(3) 查询’cs’系且选修课程的最低成绩大于等于 60 分的学生信息。

方法1:连接学生表、选课表、系别表,找到'cs'系,并且最低成绩min(grade)>=60的学生

create view T3_2_3 as
select student.sno,sname,ssex,sage,student.sdno
from student,sc,dept
where student.sno=sc.sno
and student.sdno=dept.dno
and dname='cs'
group by student.sno,sname,ssex,sage,student.sdno
having min(grade)>=60;

方法2:父查询连接学生表、系别表,找到'cs'系,子查询最低成绩min(grade)>=60的学生(子查询简化group by子句)

create view T3_2_3_2 as 
select student.sno,sname,ssex,sage,student.sdno 
from student,dept 
where student.sdno=dept.dno 
and dname='cs' 
and sno in( select sno 
	    from sc 
	    group by sno 
	    having min(grade)>=60
	  );

方法3:两个不相关子查询,找到'cs'系的系编号dno,并且sno不在grade<60的学生

(grade<60说明最低成绩要比60还要低。那么sno not in,就会选出最低成绩>=60的学生)

create view T3_2_3_2 as
select sno,sname,ssex,sage,sdno
from student
where sdno=( select dno 
	     from dept 
	     where dname='cs'
	   )
and sno not in( select sno 
		from sc 
		where grade<60
	      );

查询结果:

 

(4) 查询学校开设的课程总数。

方法:使用聚集函数count()统计课程个数

create view T3_2_4 as 
select count(cno) count 
from course;

查询结果:

 

(5) 查询所有学生所选课程的总成绩。(查询每个学生的总成绩)

方法:按学号sno分组,使用聚集函数sum()统计总成绩

create view T3_2_5 as 
select sno,sum(grade) sum 
from sc 
group by sno;

查询结果:

 

(6) 查询每门课程的平均分。

方法1:按课程号cno分组,使用聚集函数avg()统计平均成绩

create view T3_2_6 as 
select cno,avg(grade) avg 
from sc 
group by cno 
order by cno;

方法2:按课程号cno分组,使用聚集函数avg()统计平均成绩,并且按cno升序排序

create view T3_2_6_1 as 
select cno,avg(grade) avg 
from sc 
group by cno 
order by cno;

查询结果:

 

 3.视图查询(查询结果存储为 T3_3_题目序号)

(1) 利用视图 View1 中统计各门课程的选课人数,结果显示课程号、课程名及选课人数。

方法:view1(sname,cname,grade)中没有cno,所以需要连接课程表,然后按cno和cname分组,count()统计选课人数,以选课人数count降序排序

create view T3_3_1 as 
select cno,view1.cname,count(view1.sname) count 
from view1,course 
where view1.cname=course.cname 
group by cno,view1.cname 
order by count desc;

查询结果:

 

(2) 在视图 View2 中查询年龄小于 20 岁学生的学号。

方法:view2(sno,ssex,sage,birthday)中有sno,可以直接用sage<20查询

create view T3_3_2 as 
select sno from view2 
where sage<20;

查询结果:

 

(3) 在视图 JSGV 中查询选课多于 2 门的学生学号。

方法:JSGV(sno,cno,grade)中有sno,按学号sno分组,使用count()统计选课数

create view T3_3_3 as 
select sno 
from JSGV 
group by sno 
having count(cno)>2;

查询结果:

 

=======================================

作业一         作业二          作业三           作业四           作业五

一、实验目的 1.掌握SQL Server 2005的安装。 2.掌握SQL Server Management Studio的启动和使用。 3.掌握SQL Server 2005服务器的配置和注册。 4.掌握SQL Server 2005查询的基本使用。 5.掌握应用SQL Server Management Studio创建数据库的方法。 6.掌握应用SQL Server Management Studio修改和查看数据库的方法。 7.掌握应用SQL Server Management Studio删除数据库的方法。 8.掌握应用Transact-SQL语句创建数据库的方法。 9.掌握应用Transact-SQL修改和查看数据库的方法。 10.掌握通过Transact-SQL删除数据库的方法。 11.掌握SQL Server 2005数据库和操作系统物理文件的关系。 12.掌握数据库的分离和附加方法。 二、实验内容 1.完成SQL Server 2005开发版的安装。 提示:若计算机系统中已经安装有SQL Server 2005系统,则在安装时需要选择安装命名实例。安装过程中身份验证模式选择“混合模式”并设置sa账户的密码。 2.利用SQL Server配置管理器启动、停止SQL Server服务(包括默认实例和命名实例),配置SQL Server服务为自动启动。 3.利用SQL Server配置管理器配置进行SQL Server 2005网络配置,启用默认实例和命名实例的TCP/IP协议。 4.利用SQL Server外围配置器配置数据库引擎的服务及远程连接,设置为“本地连接和远程连接”,选择“同时使用TCP/IP和named pipes”。 5.利用SQL Server Management Studio注册安装的命名实例。 6.利用SQL Server Management Studio注册远程服务器。 提示:注册远程服务器时需要使用混合验证模式,利用sa账户和密码登录远程服务器。 7.启动SQL Server Management Studio,连接到服务器。新建一个查询,在其中输入如下代码: DECLARE @position int, @string char(5) SET @position = 1 SET @string = 'China' WHILE @position <= DATALENGTH(@string) BEGIN SELECT SUBSTRING(@string, @position, 1) 字符, ASCII(SUBSTRING(@string, @position, 1)) ASCII码 SET @position = @position + 1 END
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值