oracle数据库测试题

1、创建管理岗位业务培训信息表

需求说明
为管理岗位业务培训信息，创建3个表S、C、SC。
S (SID，SN，SD，SA)：SID 、SN、SD、SA分别代表学号、学员姓名、所属单位、学员年龄
C (CID，CN)：CID、CN分别代表课程编号、课程名称
SC (SID，CID，G)：SID、CID、G分别代表学号、所选修的课程编号、学习成绩。
（1） 创建数据库STU
（2） 创建管理岗位业务培训信息表S、C、SC。
（3） 向数据库表中插入测试数据，如图下图所示。

管理岗位业务培训信息的常规查询
需求说明
对上面创建的表进行如下的常规查询。
（1） 查询选修课程名称为“税收基础”的学员姓名和所属单位。
（2） 查询选修了课程的学员姓名、所属单位、年龄、选修课程名称及学习成绩。
（3） 查询选修了课程的学员人数。
以上查询结果如图1.3所示。

（4）：创建视图，使用中文列名向客户展示学员的学号，姓名，年龄，选修课程名称。
（5）：在学员名称列上创建非聚集索引，按学员名称逆序排列。
备注：提交代码，无需提交数据库。

2、电讯业务数据系统

在电讯业务中，要记录用户的信息和通话的信息，因此，分别存在用户表和通话记录表，表的结构如下图所示：

表名 UserInfo 作用 存储通话用户的信息
主键 UserID
序号 字段名称 字段说明 类型 长度 属性 备注
1 UserID 主键、自动增长 Int 非空
2 UserName 用户姓名 nvarchar 30 非空
3 TelNo 用户电话号码 nvarchar 30 非空
4 Address 用户地址 nvarchar 100 允许空
图1：用户信息表
表名 TelRecord 作用 存储通话记录
主键 CommID
序号 字段名称 字段说明 类型 长度 属性 备注
1 CommID 主键、自动增长 Int 非空
2 TelFrom 主叫电话号码 nvarchar 30 非空
3 TelDest 被叫电话号码 nvarchar 30 非空
4 STime 通话开始时间 datetime 非空
5 ETime 通话结束时间 datetime 非空
图2：通话记录表

在通话记录表中每天将会产生大量的数据，每月有将近500万行记录。

请执行以下操作或回答以下的问题：

1、 请在数据库中建立电讯业务数据库Comm，并按以上的要求建立数据库表；
2、 用户在查询话费的时候，因为牵涉到多次通话时间的计算，系统反应比较慢，从数据库的角度考虑，有何好的办法来缓解这个问题？（简答题）
3、 分别为用户信息表、通话记录表输入以下的测试数据（或者使用SQL语句插入），如下图所示：

图3：用户信息表中的数据

图4：通话记录表的数据

4、 编写SQL语句，查询至少有2个电话的用户名、电话号码的个数；
5、 编写SQL语句，查询通话时间少于1分钟的通话ID；
6、 编写SQL语句，找出通话记录表中存在的电话号码而在用户信息表中没有的电话号码，然后把这些电话号码插入到用户信息表中，新插入的数据用户名为“未知”、地址为“未知”；
7、 编写存储过程，按输入的月份、主叫号码统计通话记录、通话总时间；
8、 对于通话记录表中存在大数据量的情况，你有哪些查询的优化建议？（简答题）

评分说明:
1、1-6每项要求10分，7，8每项20分。
2、第2，8两题为简答题，不需要用代码实现。

答案

--创建3个表 S、C、SC
create table S(sid  varchar(20) primary key,sn varchar(20),sd varchar(20),sa int
);
create table C(cid  varchar(20) primary key,cn varchar(20)
);
create table SC(sid  varchar(20),cid varchar(20),G int,constraint fk_sid foreign key (sid) references S(sid),constraint fk_cid  foreign key(cid) references C(cid)
);
--插入数据
insert into S(sid,sd,sn,sa) values('1001','林林','中国汽车',28);
insert into S(sid,sd,sn,sa) values('1002','白杨','北大青鸟',30);insert into c(cid,cn) values('s001','税收基础');
insert into c(cid,cn) values('s002','初级英语');
insert into c(cid,cn) values('s003','雅思英语');insert into sc(cid,sid,g) values('s001','1001',85);
insert into sc(cid,sid,g) values('s002','1001',76);
insert into sc(cid,sid,g) values('s003','1002',90);--1.查询选修课程名称为“税收基础”的学员姓名和所属单位。
select sd,sn from s where sid in( select sid from sc where sc.cid=( select cid from c where cn='税收基础'));
--2.查询选修了课程的学员姓名、所属单位、年龄、选修课程名称及学习成绩。
select sd,sn,sa,cn,g from s,c,sc where s.sid=sc.sid and c.cid=sc.cid;
--3.查询选修了课程的学员人数
select count(sid) from (select sid from sc group by sid) e;--创建表
create sequence seq_userinfo1_userid start with 1
increment by 1 nomaxvalue cache 10;
create table userinfo(userid int default(seq_userinfo1_userid.nextval),username varchar(30) not null,telno varchar(30) not null,address varchar(100),constraint pk_userid primary key (userid)
);
--创建自增sequence
create sequence seq_telrecord_id start with 1 increment by 1 nomaxvalue cache 10;
create table telrecord(commid int default(seq_telrecord_id.nextval),telfrom varchar(30) not null,teldest varchar(30) not null,stime date not null,etime date not null
);--插入语句
insert into userinfo(username,telno,address) values('张三丰','122','湖北');
insert into userinfo(username,telno,address) values('朱算','133','上海');
insert into userinfo(username,telno,address) values('胡柳','144','上海');
insert into userinfo(username,telno,address) values('朱算','155','上海');
insert into userinfo(username,telno,address) values('杨飞','166','山东');
insert into userinfo(username,telno,address) values('胡一三','177','山东');insert into telrecord(telfrom,teldest,stime,etime) values('122','144',(select sysdate from dual),(select sysdate from dual));
insert into telrecord(telfrom,teldest,stime,etime) values('133','144',(select sysdate from dual),(select sysdate from dual));
insert into telrecord(telfrom,teldest,stime,etime) values('144','155',(select sysdate from dual),(select sysdate from dual));
insert into telrecord(telfrom,teldest,stime,etime) values('177','188',(select sysdate from dual),(select sysdate from dual));--4、	编写SQL语句，查询至少有2个电话的用户名、电话号码的个数；
select username,count(telno) from userinfo group by username having count(telno)>=2;
--5、	编写SQL语句，查询通话时间少于1分钟的通话ID；
select commid,(etime-stime)*24*60*60 from telrecord where (etime-stime)*24*60*60<60;
--6、	编写SQL语句，找出通话记录表中存在的电话号码而在用户信息表中没有的电话号码，然后把这些电话号码插入到用户信息表中，新插入的数据用户名为“未知”、地址为“未知”；
--我只会单行单次插入，后期修改
insert into userinfo(username,address,telno) values('未知','未知',(select telfrom from telrecord where telfrom not in(select telno from userinfo)and rownum<2));
insert into userinfo(username,address,telno) values('未知','未知',(select teldest from telrecord where teldest not in(select telno from userinfo)and rownum<2) );