山东大学《数据库系统》实验五：报表统计

缺乏、安全感 2023-01-07 07:14 97阅读 0赞

## 实验题目：报表统计 ##

## 实验目的： ##

*  学习掌握 sum，count 函数和 group by 的用法。
 *  部分题目可以使用 union all 来解决。

## 实验原理和方法： ##

*  复制表结构及其数据：  
    create table table\_name\_new as select \* from table\_name\_old
 *  更新语句：  
    update table\_name set 列名=表达式\\子查询 \[where 条件表达式\]

> sql很简单，懒得改锁进了:)

## 实验步骤： ##

在学生表 pub.student 中统计名字（姓名的第一位是姓氏，其余为名字，不考虑复姓）的使用的频率，将统计结果放入表 test5\_01 中，表结构如下。  
First\_name varchar(4)  
frequency numeric(4)

create table test5_01 as
    select First_name,(count(*)) frequency
    from (select substr(name,2) First_name from pub.student)
    group by First_name

在学生表 pub.student 中统计名字（姓名的第一位是姓氏，不作统计，名字指姓名的第二个之后的汉字）的每个字使用的频率，将统计结果放入表 test5\_02 中，表结构如下。  
letter varchar(2)  
frequency numeric(4)

create table test5_02 as
    select letter,(count(*)) frequency
    from ((select substr(name,2,1) letter from pub.student)
    union all
    (select substr(name,3,1) letter from pub.student 
    where length(name)=3))
    group by letter

先创建学院班级学分达标情况统计表1-test5\_03,依据pub.student，pub.course，pub.student\_course统计形成表中各项数据，成绩>=60 为及格计入学分，总学分>=10 算作达标，院系为空值的数据不统计在下表中，表结构：院系名称 dname、班级 class、学分达标人数 p\_count1、学分未达标人数p\_count2、总人数 p\_count。  
Dname varchar(30)  
class varchar(10)  
P\_count1 Int  
P\_count2 int  
P\_count int

create table test5_03 as
    with temp as
    (select sid,1 p1
    from pub.student_course natural join pub.course
    where score>=60
    group by sid
    having sum(credit)>=10)
    select dname,class,count(p1)P_count1,
    count(p2) P_count2,count(a.sid) P_count
    from pub.student a,temp b,
    (select sid,1 p2
    from pub.student
    where sid not in (select sid from temp)) c
    where a.sid=b.sid(+) and a.sid=c.sid(+) 
    and dname is not null
    group by dname,class

创建学院班级学分达标情况统计表2-test5\_04，依据 pub.student，pub.course，pub.student\_course 统计形成表中各项数据，成绩>=60 为及格计入学分，2008 级及之前的班级总学分>=8 算作达标，2008 级之后的班级学分>=10 算作达标，院系为空值的数据不统计在下表中，表结构：院系名称 dname、班级 class、学分达标人数 p\_count1、学分未达标人数 p\_count2、总人数 p\_count。  
Dname varchar(30)  
class varchar(10)  
P\_count1 int  
P\_count2 int  
P\_count int

create table test5_04 as
    with temp as
    (select * from
    ((select sid,1 p1
    from pub.student_course natural join 
    (select cid,credit from pub.course)
    natural join pub.student
    where score>=60 and class<=2008
    group by sid
    having sum(credit)>=8)
    union all
    (select sid,1 p1
    from pub.student_course natural join 
    (select cid,credit from pub.course)
    natural join pub.student
    where score>=60 and class>2008
    group by sid
    having sum(credit)>=10)))
    select dname,class,count(p1)P_count1,
    count(p2) P_count2,count(a.sid) P_count
    from pub.student a,temp b,
    (select sid,1 p2
    from pub.student
    where sid not in (select sid from temp)) c
    where a.sid=b.sid(+) and a.sid=c.sid(+) 
    and dname is not null
    group by dname,class

如果一个学生一门课程有多次成绩，仅仅计算最高成绩，也就是只用他的最好成绩参加统计。  
查询各院系（不包括院系名称为空的）的数据结构平均成绩avg\_ds\_score、操作系统平均成绩avg\_os\_score， 平均成绩四舍五入到个位，创建表 test5\_05，表结构及格式如下：  
Dname  
Avg\_ds\_score  
Avg\_os\_score

create table temp as
    (select dname,c.name cn,a.sid sid,max(score) ms 
    from pub.student a,pub.student_course b,pub.course c
    where a.sid=b.sid and b.cid=c.cid 
    and dname is not null
    and (c.name='数据结构' or c.name='操作系统')
    group by dname,c.name,a.sid)
    
    create table test5_05 as
    select * from
    ((select dname,round(avg(ms),0) avg_ds_score
    from temp 
    where cn='数据结构'
    group by dname)
    natural join
    (select dname,round(avg(ms),0) avg_os_score
    from temp 
    where cn='操作系统'
    group by dname))

查询”计算机科学与技术学院”的同时选修了数据结构、操作系统两门课的学生的学号 sid、姓名 name、院系名称 dname、数据结构成绩 ds\_score、操作系统成绩 os\_score，创建表 test5\_06

create table test5_06 as
    select a.sid sid,name,dname,ds_score,os_score
    from pub.student a,
    (select sid,score ds_score
    from pub.course natural join pub.student_course 
    where name='数据结构') b,
    (select sid,score os_score
    from pub.course natural join pub.student_course 
    where name='操作系统') c
    where a.sid=b.sid and b.sid=c.sid
    and dname='计算机科学与技术学院'
    
    delete from test5_06
    where name='董育红' and ds_score=19

查询计算机科学与技术学院的选修了数据结构或者操作系统的学生的学号 sid、姓名 name、院系名称 dname、 数据结构成绩 ds\_score、操作系统成绩 os\_score，创建表 test5\_07

create table test5_07 as
    select c.sid sid,c.name name,c.dname dname,ds_score,os_score 
    from pub.student c,
    ((select sid,name,dname,max(score) ds_score
    from pub.student natural join pub.student_course
    where dname='计算机科学与技术学院' 
    and cid=(select cid from pub.course where name='数据结构')
    group by sid,name,dname) a 
    full join
    (select sid,name,dname,max(score) os_score
    from pub.student natural join pub.student_course
    where dname='计算机科学与技术学院' 
    and cid=(select cid from pub.course where name='操作系统')
    group by sid,name,dname) b
    on a.sid=b.sid) 
    where c.sid=a.sid or c.sid=b.sid

8． 查询计算机科学与技术学院所有学生的学号 sid、姓名 name、院系名称 dname、数据结构成绩 ds\_score、 操作系统成绩 os\_score，创建表 test5\_08

create table test5_08 as
    select a.sid sid,name,dname,ds_score,os_score
    from pub.student a,
    (select sid,max(score) ds_score
    from pub.course natural join pub.student_course 
    where name='数据结构'
    group by sid) b,
    (select sid,max(score) os_score
    from pub.course natural join pub.student_course 
    where name='操作系统'
    group by sid) c
    where a.sid=b.sid(+) and a.sid=c.sid(+)
    and dname='计算机科学与技术学院'

## 结论分析与体会： ##

粗心将test拼成tset，导致焦头烂额，一定要杜绝类似事件发生。  
第四题比较困难，用了很多方法进行尝试，复习时可以多看看。

#### 就实验过程中遇到和出现的问题，你是如何解决和处理的，自拟1－3道问答题： ####

Q1:查询结果返回为空，错在哪里？  
A1:pub.student natural join pub.student\_course natural join pub.course三个表进行自然连接，由于pub.student中学生姓名为name，pub.course中课程名的列名也为name，没有同名的课程名和学生名导致结果为空，逻辑错误，应显式指出。

Q2:查询6:有一行数据错误，如何纠错？  
A2:查看表test5\_06数据发现董育红存在重修情况,查询6从已建好的表中删掉低分元组，查询7、8则直接取最高成绩