数据库系统原理与应用--综合练习（2）

不念不忘少年蓝@ 2022-05-21 04:59 147阅读 0赞

1. 在Oracle数据库中新建用户，用户名为你的姓名（例如：zhangsan），密码为123。

![70][]

2. 为新建用户授权，包括：建表、建视图、建触发器、建存储过程等权限。

![70 1][]

3. 利用新建用户连接数据库，并在新用户（如：zhangsan）模式下，按照要求及说明建立下列所示的表。（10分）

学校表School：

学生表Student：

<table style="background:#FFFFFF;"> 
 <tbody> 
 <tr> 
 <td>字段</td> 
 <td>类型</td> 
 <td>说明</td> 
 </tr> 
 <tr> 
 <td>Id</td> 
 <td>Number</td> 
 <td>主键</td> 
 </tr> 
 <tr> 
 <td>Name</td> 
 <td>varchar2(12)</td> 
 <td>学生姓名（非空）</td> 
 </tr> 
 <tr> 
 <td>School_id</td> 
 <td>Number</td> 
 <td>所属学校ID</td> 
 </tr> 
 <tr> 
 <td>Department</td> 
 <td>varchar2(100)</td> 
 <td>所属院系名称</td> 
 </tr> 
 <tr> 
 <td>Grade</td> 
 <td>Number</td> 
 <td>年级（1800-2800之间）</td> 
 </tr> 
 <tr> 
 <td>Birthday</td> 
 <td>Date</td> 
 <td>生日</td> 
 </tr> 
 <tr> 
 <td>Code</td> 
 <td>varchar2(12)</td> 
 <td>学号</td> 
 </tr> 
 </tbody> 
</table>

添加测试数据：

begin

insert intoschool values(1,'北京大学',default);

insert intoschool values(2,'清华大学',default);

insert intoschool values(3,'北京理工大学',default);

insert intoschool values(4,'人民大学',default);

end;

begin

insert intostudent values(1,'李勇',1,'信息学院',2016,'11-6月\-1999','2016001');

insert intostudent values(2,'李明',1,'信息学院',2016,'19-5月\-1999','2016002');

insert intostudent values(3,'张新',1,'生命科学学院',2015,'11-2月\-1999','2016003');

insert intostudent values(4,'李斯',2,'信息学院',2016,'11-6月\-1999','2016001');

insert intostudent values(5,'李丽',2,'信息学院',2016,'19-5月\-1999','2016002');

insert intostudent values(6,'赵雨',2,'生命科学学院',2015,'11-2月\-1998','2015001');

insert intostudent values(7,'李力',2,'生命科学学院',2013,'11-2月\-1996','2013002');

insert intostudent values(8,'刘同',3,'信息学院',2016,'11-6月\-1999','2016001');

insert intostudent values(9,'曾明',3,'信息学院',2016,'19-5月\-1999','2016002');

insert intostudent values(10,'李鑫',4,'生命科学学院',2016,'11-2月\-1999','2016001');

insert intostudent values(11,'李鑫',4,'信息学院',2016,'11-2月\-1999','2016002');

end;

查询表数据：

设置显示格式:

sql>SETLINESIZE 300;

sql>SETPAGESIZE 30;

sql>colname for A10;

sql>coladdress for A10;

sql>coldepartment for A20;

sql>colcode for A12;

查询当前表数据：

sql>select\* from school;

sql>select\* from student;

创建表：

创建school 表

create table school (  
    id number primary key,  
    name varchar2(100),  
    address varchar2(200) default '北京'

);

创建 student 表

create table student (  
    id number primary key,  
    name varchar2(12) not null,  
    school\_id number,  
    department varchar2(100),  
    grade number,  
    birthday date,  
    code varchar2(12)  
);

导入数据：

我们用图形界面来做.(我在创建表的时候表名写错了。你们在创建的时候注意一下)

完成图：

![70 2][]

4. 修改学生表，为学生表增加外键约束，即学生的所属学校应参照学校表中的学校ID。

alter table student   
add constraint student\_school\_fk  
foreign key(school\_id)

references school(id);

5. 在学生表中添加一条你自己的信息。

insert into student values(  
13,  
'田贵',  
4,  
'信息科学与工程学院',  
1612,  
'22-3月-97',  
201612160227  
);

6. 将学校编号为1的学生“李勇” 所在学院改为“环境工程学院”。

update student   
set department='环境工程学院'

where school\_id=1 and name='李勇'

7. 删除地址为空的学校记录信息。

delete from school where address is null;

8. 查询所有地址所在地为“北京”的学生；

select \* from  
student inner join school  
on student.school\_id=school.id

where school.ADDRESS='北京';

9. 查询所有年龄大于20的学生信息；

select \* from  
student inner join school  
on student.school\_id=school.id  
where to\_char(sysdate,'yyyy')-to\_char(birthday,'yyyy') >20;

10. 查询清华大学所有年龄大于20的“王”姓学生；

select \* from

student inner join school

on student.school\_id=school.id  
where to\_char(sysdate,'yyyy')-to\_char(birthday,'yyyy') >20

and school.name='清华大学' and student.name like '王%'

11. 统计每个学校的学生人数，查询结果包括学校编号、学生人数；

select school\_id,count(\*)from  
student group by school\_id;

12. 创建一个视图，统计每个学校、各个年级的学生人数，视图内容包括学校名称、年级、学生人数。

create view school\_view as  
select school.name 学校名称,grade 年级,count(\*) 学生人数  
from SCHOOL inner join STUDENT on  
school.id=student.school\_id  
group by school.name ,grade;

13. 通过视图查询2015级人数超过3人的学校名称。

select 学校名称 from school\_view  
where "年级"=2015 and "学生人数">3;

14. 创建一个存储过程，根据学校ID和年级统计每个学校该年级的学生人数，并执行该存储过程。

**输出形式**：学校ID:1 年级:2016 人数：2

create or replace procedure my\_pro(xxbh number,nj number)  
as  
rs int;  
xx\_name school.name%type;  
begin  
    select school.name,count(\*) into xx\_name,rs  
    from student inner join school on school\_id =school.id  
    where school\_id = xxbh and grade=nj  
    group by school.name;  
    dbms\_output.put\_line('学校名称：'||xx\_name||'，年级：'||nj||'，人数：'||rs);  
end;  
\--执行：  
set serveroutput on;  
begin  
my\_pro(1,2016);  
end;

15. 创建一个触发器，当删除学校表中的记录信息时，将学生表中对应该学校的学生一并删除。（10分）

create or replace trigger del\_trg   
before delete on school  
for each row  
begin  
delete from student where school\_id=:old.id;  
end;  
  
  
  
  
\--验证触发器：  
delete from school where id=3;

[70]: /images/20220521/cf70946edc46422f93a96826742b1287.png
[70 1]: /images/20220521/8e65e3db53da4ce087d40d97faae27d4.png
[70 2]: /images/20220521/5ed0a13fe92c41589a115b0463918eab.png