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

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

 

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

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

 

作业四 数据库中数据的查询-----分组查询和统计查询

 

一.实验内容及说明

在作业一的基础上,完成如下题目。并将每个实验结果均存储为一张视图表。

方式如下:create view T4_* [(..)]AS 

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

注:个别题目的排序结果在视图中没办法实现,请再排序的结果无误的情况下,将未排序的结果生成视图提交到服务器中。

 

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

1. 查询各个系学生选修 1001 课程的平均成绩。(同作业三 T3_2_1)   

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

create view T4_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 T4_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. 查询各个系学生选修课程的总门数。(同作业三 T3_2_2)

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

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

查询结果:

 

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

 

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

create view T4_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 T4_3_1 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 T4_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. 列出学校开设的课程总数。(同作业三 T3_2_4)

 

 

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

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

查询结果:

 

 

5. 列出每门课程的平均分。(同作业三 T3_2_6)

 

 

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

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

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

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

查询结果:

 

 

6. 查询各个系学生选修每门课程的平均成绩。

 

方法:按系编号sdno和课程号cno分组,组内用聚集函数avg()求平均成绩,按sdno、cno升序排序

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

查询结果:

 

7. 列出成绩高于课程平均成绩的所有学生信息,并列出相关详细信息。

方法1:不相关子查询嵌套相关子查询,父查询从学生表查,不相关子查询从选课表c1查,相关子查询从选课表c2查(c1的成绩大于c2的平均成绩,并且是同一课程)

create view T4_7 as 
select student.sno,sname,ssex,sage,sdno 
from student 
where student.sno in( select c1.sno 
		      from sc c1 
		      where grade>( select avg(grade) avg 
				    from sc c2 
				    where c1.cno=c2.cno 
				    group by cno
				   )
		     );

方法2:父查询连接学生表、选课表c1,相关子查询从选课表c2查(c1的成绩大于c2的平均成绩,并且是同一课程)

create view T4_7_1 as 
select distinct student.sno,sname,ssex,sage,sdno 
from student,sc c1 
where student.sno=c1.sno 
and grade>( select avg(grade) avg 
	    from sc c2 
	    where c1.cno=c2.cno 
	    group by c2.cno
	   );

查询结果:

 

进阶方法:将课程号cno和平均成绩avg查询为派生表t1,再把学生表、选课表、课程表、派生表t1连接,查询所有信息

create view T4_7_2 as 
select student.sno,sname,ssex,sage,sdno,sc.cno,cname,ccredit,grade,t1.avg 
from student,sc,course,( select cno,avg(grade) avg from sc group by cno ) t1 
where student.sno=sc.sno 
and sc.cno=course.cno
and sc.cno=t1.cno 
and grade>t1.avg;

查询结果:

 

8. 列出每个系学生的详细信息。

方法:连接学生表、系别表,按系编号升序排序

create view T4_8 as 
select student.sdno,dname,sno,sname,ssex,sage
from student,dept 
where student.sdno=dept.dno 
order by sdno;

查询结果:

 

9. 列出每门课程的详细授课信息。

方法:连接教师表、课程表

create view T4_9 as 
select cno,cname,ccredit,tno,tname,tsex,prof,tdno 
from course,teacher 
where teacher.tno=course.ctno;

查询结果:

 

10. 列出每门课程的最低成绩和最高成绩。

方法1:按课程号cno分组,查询课程号,最低、最高成绩

create view T4_10 as
select cno,min(grade) min,max(grade) max
from sc
group by cno;

方法2:按课程号cno分组,连接课程表、选课表,查询课程号,课程名,最低、最高成绩

create view T4_10_1 as 
select sc.cno,cname,min(grade) min,max(grade) max 
from course,sc 
where course.cno=sc.cno 
group by sc.cno,cname 
order by sc.cno;

查询结果:

11. 列出所有学生选课的详细信息,并按每位学生选课成绩降序显示。

方法1:按学号sno分组,排序按学号sno升序,成绩grade降序排序

create view T4_11 as 
select sno,cno,grade 
from sc 
group by sno,cno,grade 
order by sno,grade desc;

方法2:把学生表、选课表、课程表左外连接,按学号sno分组,排序按学号sno升序,成绩grade降序排序

create view T4_11_1 as 
select student.sno,sname,sc.cno,cname,grade 
from student,sc,course 
where student.sno=sc.sno(+) 
and sc.cno=course.cno(+) 
group by student.sno,sname,sc. cno,cname,grade 
order by sno,grade desc;

查询结果:

 

12. 列出同一系中选修相同课程的学生的详细信息。

方法1:将学生表、选课表连接当作学生1。派生表将学生表、选课表连接当作学生2。

然后将基表与派生表连接,即选出同系,相同课程的学生1、学生2。(distinct用来去重)

create view T4_12 as 
select distinct student.sdno,sc.cno,student.sno,sname,ssex,sage 
from student,sc,(select sdno,cno,student.sno from student,sc where student.sno=sc.sno) t1 
where student.sno=sc.sno 
and student.sdno=t1.sdno 
and sc.cno=t1.cno 
and student.sno!=t1.sno;

方法2.1:学生1的学生表s1、选课表c1。学生2的学生表s2,选课表c2。

先s1与s2的系编号sdno相等,然后c1与c2的课程号cno相等,再让s1,c1连接,s2,c2连接,即选出同系,相同课程的学生1、学生2。

create view T4_12_1 as 
select * 
from student s1 
where exists( select * 
	      from student s2 
	      where s1.sno!=s2.sno 
	      and s1.sdno=s2.sdno 
	      and exists( select * 
			  from sc c1 
			  where s1.sno=c1.sno 
			  and exists( select * 
				      from sc c2 
				      where c1.sno!=c2.sno 
				      and s2.sno=c2.sno 
				      and c1.cno=c2.cno  
				    )
			)
	    );

方法2.2:学生1的学生表s1、选课表c1。学生2的学生表s2,选课表c2。

先s1,c1连接,然后s2,c2连接,再让s1与s2的系编号sdno相等,c1与c2的课程号cno相等即选出同系,相同课程的学生1、学生2。

create view T4_12_2 as 
select * 
from student s1 
where exists( select * 
	      from sc c1 
	      where s1.sno=c1.sno
	      and exists( select * 
			  from student s2
			  where s1.sno!=s2.sno 
			  and s1.sdno=s2.sdno
			  and exists( select * 
				      from sc c2 
				      where s2.sno=c2.sno 
				      and c1.cno=c2.cno  
				    )
			 )
	    );

 

查询结果:

 

13. 列出学生选修课程人数最多的三门课程。

方法1:将课程cno和人数count查询为派生表并按人数count降序排序,然后查询前三行(rownum<=3)。仅有一张表且为派生表,派生表的别名可有可无

create view T4_13 as 
select cno,count 
from (select cno,count(sno) count from sc group by cno order by count desc) 
where rownum<=3;

方法2:将课程cno、课程名cname、人数count查询为派生表并按人数count降序排序,然后查询前三行(rownum<=3)。仅有一张表且为派生表,派生表的别名可有可无,此处别名为t1

create view T4_13_1 as 
select t1.cno,t1.cname,t1.count 
from ( select sc.cno,cname,count(sno) count 
       from course,sc 
       where course.cno=sc.cno 
       group by sc.cno,cname 
       order by count desc
     )t1 
where rownum<=3;

查询结果:

 

14. 统计每个学生的学分,列出学号、姓名、学生。(要求:不及格课程无学分)

方法:连接学生表、选课表、课程表,找出有成绩(成绩非空),且成绩>=60的学生,聚集函数sum()统计总学分

create view T4_14 as 
select student.sno,sname,sum(ccredit) sum 
from student,sc,course 
where student.sno=sc.sno 
and sc.cno=course.cno 
and grade>=60 
and grade is not null 
group by student.sno,sname
order by student.sno;

查询结果:

 

15. 某高校学士学位授予条件为:每门课程必须及格且平均成绩达 75 分以上。请查询 Cs 系可授予学士学位的学生名单。

方法1:连接学生表、选课表、系别表,找到'cs'系的学生,按学号sno分组,每门课程必须及格(即最小成绩>=60),平均成绩avg(grade)>75

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

方法2:连接学生表、选课表、系别表,查询学号sno、平均成绩avg作为派生表t1并连接,找到'cs'系的学生,按学号sno分组,每门课程必须及格(即最小成绩>=60),并查询出最小成绩

create view T4_15_1 as 
select student.sdno,dname,student.sno,sname,min(grade) min,t1.avg 
from student,sc,dept,(select sno,avg(grade) avg from sc group by sno having avg(grade)>75) t1 
where student.sno=sc.sno 
and student.sdno=dept.dno 
and student.sno=t1.sno 
and dname='cs' 
group by student.sdno,dname,student.sno,sname,t1.avg 
having min(grade)>=60 
order by student.sdno,student.sno;

查询结果:

 

16. 建立一张统计信息表 DEPTage(sdept,avage),要求存放每个系学生的平均年龄。 (注意是建表,而不是建立视图)

方法:按系编号sdno分组,聚集函数avg()统计平均年龄

create table DEPTage(sdept,avage) as 
select sdno,avg(sage) 
from student 
group by sdno 
order by sdno;

查询结果:

 

17. 查询只选 1 门课且成绩不及格学生的选课记录。

方法:在不相关子查询中先找出只选1门课的学生学号sno,然后父查询找出成绩<60的不及格学生信息

(如果不用子查询,而是把having count(cno)=1放在where grade<60后面。SQL会先把成绩<60的记录找出,然后再分组统计课程数。则会出现选了多门课,只有1门不及格的情况被当作结果查出)

create view T4_17 as 
select sno,cno,grade 
from sc 
where grade<60 
and sno in( select sno 
	    from sc 
	    group by sno 
	    having count(cno)=1
	  );

查询结果:(因为只选1门课的成绩都>60,所以查询不到,提示未选定行)

 

18. 查询 cs 系只选 1 门课且成绩不及格学生的选课记录。

方法:与17题同理,只不过增加了cs系的条件,只需再连接学生表、系别表,增加系名称,学生名信息

create view T4_18 as 
select student.sdno,dname,student.sno,sname,cno,grade 
from student,sc,dept 
where student.sno=sc.sno 
and student.sdno=dept.dno 
and dname='cs' 
and grade<60 
and sc.sno in ( select sno 
		from sc 
		group by sno 
		having count(cno)=1
	      );

查询结果:(因为只选1门课的成绩都>60,所以查询不到,提示未选定行)

 

 

 

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

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

实验1 数据库及数据定义 1、创建一个名为XSCJ的数据库,其初始大小为5MB,最大大小10MB,允许数据库自动增长,增长方式是按10%比例增长;日志文件初始为2MB,最大可增长到5MB,按1MB增长。 2、将以上XSCJ数据库的主数据文件的最大大小改为不限制。 3、参照教材82页例3.5、例3.6、例3.7分别建立Student表、Course表和SC表,并录入数据库表中相应元组。 4、分别采用SQL编程模式和设计模式修改Course表,为其增加一列开课学期,数据类型自行定义。 5、为Course表的Cname列建立一个唯一索引,索引名Cname_idx。 6、分离数据库XSCJ并拷贝文件至U盘保存好,用于下次实验;然后再将其附加上。 7、删除唯一索引Cname_idx。 8、删除基本表Student。 实验2 数据查询 将上次实验课的学生成绩数据库(XSCJ)附加到SQL SERVER中,录入数据后做如下查询。 1) 查询选修了课程的学生的学号; 2) 查询全体学生的姓名和出生年份,并将列别名改为XM和CSNF; 3) 查询年龄在18到20(包括18岁和20岁)之间学生的学号、姓名和系。 4) 查询信息系(IS)、数学系(MA)和计算机科学系(CS)学生的姓名和性别。 5) 用LIKE查询课程名为DB_ S的课程号和学分。 6) 查所有有成绩的学生学号和课程号。 7) 查询全体学生情况,查询结果按所在系的系号升序排列,同一系中的学生按年龄升序排列 8) 查询选修了课程的学生人数。 9) 计算1号课程的学生平均成绩。 10) 查询有2人以上(含2人)同学选修课程的课程号。 11) 查询1号课程的间接先修课。 12) 查询每个学生的学号、姓名、选修的课程名及成绩。 13) 找出每个学生高于他选修课程平均成绩的课程号。 实验3 数据更新和视图 将学生成绩数据库(XSCJ)附加到SQL SERVER中,完成如下实验任务。 1) 将一个新学生元组(学号:95007;姓名:张娜丽;性别:女;所在系:IS;年龄:18岁)插入到Student表中。 2) 插入一条选课记录,插入一条选课记录( '95007','1 ')。 3) 对每一个系,求学生的平均成绩,并把结果存入数据库。 (建立一张表,然后通过子查询将结果插入表中。) 4)将全体学生的年龄增加一岁。 5)将信息系全体学生的成绩置零。 6)删除95001学生的记录。 7)删除信息系所有学生的选课记录。 8)建立计算机学生的视图CS_S。(视图中包括学号,姓名,年龄,系),并要求进行修改和插入操作时仍需保证该视图只有计算机系的学生。(视图中包含的属性名称自拟) 9)向CS_S视图中插入分别插入两条信息,如果不能插入说明原因。 (学号:95002;姓名:李华华;所在系:IS;年龄:18岁) (学号:95003;姓名:王冬冬;所在系:CS;年龄:20岁) 10)利用CS_S视图完成以下查询。 查询计算机学生年龄小于20岁的学生(学号、年龄) 查询计算机系选修了2号课程的学生(学号、姓名)。 实验4 数据库的安全性 实验内容 将学生成绩数据库(XSCJ)附加到SQL SERVER中,完成如下实验任务。 (1)在SQL Server Management Studio中创建三个登录帐户:log1、log2、log3。 (2)利用前面建立的XSCJ数据库,用log1登录,能否操作XSCJ数据库?为什么? (3)将log1、log2、log3映射为XSCJ数据库中的用户。 注意:此映射步骤会为登陆名log1在XSCJ中自动建立同名数据库用户log1。log2和log3同理。 (4)用log1登录,能否访问XSCJ数据库?为什么? (5)授予log1、log2、log3具有对Student、Course、SC三张表的查询权。并且log1有权将查询SC表的权限授予其他用户。 (6)分别用log1、log2、log3登录,对上述三张表执行查询。 (7)分别用log1、log2登陆,在查询分析器中执行以下语句,用来授予log3 对SC表的访问权限,会出现什么情况? 实验5 数据库的完整性 将学生成绩数据库(XSCJ)附加到SQL SERVER中,完成如下实验任务。 (1)录入或变更数据,验证所建立的实体完整性规则及其违约处理。 (2)录入或变更数据,验证所建立的参照完整性规则及其违约处理。 (3)自行设定某些列,使得其不允许取空值。 (4)自行设定某些列,使得该列值不允许重复。 (5)自行设定某些列的默认值。 (6)用Check短语限定考试成绩的取值范围在0到100之间。 (7)为Student表设计一个触发器,使得新增一个学生时,自动为其分配一个学号。 实验6 数据库编程

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
©️2022 CSDN 皮肤主题:大白 设计师:CSDN官方博客 返回首页
评论

打赏作者

AN_drew

你的鼓励将是我创作的最大动力

¥2 ¥4 ¥6 ¥10 ¥20
输入1-500的整数
余额支付 (余额:-- )
扫码支付
扫码支付:¥2
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

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

余额充值