SQL争霸赛

SQL争霸赛决赛试题

 

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

 

表名	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、  对于通话记录表中存在大数据量的情况，你有哪些查询的优化建议？

 

//答案

 

说明：两张关联表，删除主表中已经在副表中没有的信息 delete from table1 where not exists ( select * from table2 where table1.field1=table2.field1 ) 说明：日程安排提前五分钟提醒 select * from 日程安排 where datediff('minute',f开始时间,getdate())>5 说明：删除重复记录 delete from tablename where id not in (select max(id) from tablename group by col1,col2,...) 说明：外连接查询(表名1：a 表名2：b) select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c

 

 

--1、	请在数据库中建立电讯业务数据库Comm，并按以上的要求建立数据库表；
if exists (select * from sysdatabases where name = 'Comm')
	drop database Comm
go
create database Comm
on
(
	name = 'Comm_data',
	filename = 'd:\Comm_data.mdf',
	size = 3mb,
	maxsize = 10mb,
	filegrowth = 10%
)
log on
(
	name = 'Comm_log',
	filename = 'd:\Comm_log.ldf',
	size = 10mb,
	maxsize = 1000mb,
	filegrowth = 50%
)
go
/*---创建表UserInfo---*/
use Comm
go
if exists (select * from sysobjects where name = 'UserInfo')
	drop database UserInfo
go
create table UserInfo
(
	UserID int not null identity(1,1) primary key,
	UserName varchar(30) not null,
	UelNo nvarchar(30) not null,
	Address nvarchar(100) not null
)
go

/*---创建用户信息表---*/
if exists (select * from sysobjects where name = 'TelRecord')
	drop database TelRecord
go
create table TelRecord
(
	CommID int identity(1,1) primary key,
	TelFrom nvarchar(30) not null,
	TelDest nvarchar(30) not null,
	STime datetime not null,
	ETime datetime not null
)
go

--2、	用户在查询话费的时候，因为牵涉到多次通话时间的计算，
--系统反应比较慢，从数据库的角度考虑，有何好的办法来缓解这个问题？
  /*可以创建索引，再分组查询*/
create index IX_TelRecord_TelFrom
	on TelRecord(TelFrom)
	with fillfactor = 10
go

/*3分别为用户信息表、通话记录表输入以下的测试数据（或者使用SQL语句插入），
如下图所示：*/
select * from UserInfo 

insert into UserInfo values('张三丰','98290000','湖北')
insert into UserInfo values('朱算','68290900','上海')
insert into UserInfo values('胡柳','0215466546656','上海')
insert into UserInfo values('朱算','21546654666','上海')
insert into UserInfo values('杨飞','035730928370','山东')
insert into UserInfo values('胡一三','78290000','山东')

insert into TelRecord values('98290000','0216566656654','2007-2-1 9:49:53','2007-2-1 9:50:16') 
insert into TelRecord values('98290000','0216566656654','2007-2-1 9:49:53','2007-2-1 9:50:16') 
insert into TelRecord values('98290000','021546654666','2007-2-1 9:50:29','2007-2-1 9:51:41') 

insert into TelRecord values('98290000','021546654666','2007-2-1 9:50:58','2007-2-1 9:51:12')  

insert into TelRecord values('68290900','075513329866','2007-2-2 10:04:31','2007-2-2 10:07:13') 

insert into TelRecord values('78290000','0755255708638','2007-2-1 10:48:31','2007-2-1 10:49:23')    
insert into TelRecord values('78290000','0755821119109','2007-2-1 10:49:39','2007-2-1 10:52:55')  
insert into TelRecord values('78290000','035730928370','2007-2-3 11:30:45','2007-2-3 11:31:58') 

insert into TelRecord values('78290000','0871138889904','2007-2-1 11:33:47','2007-2-1 11:35:00')  
insert into TelRecord values('68290000','035730928379','2007-2-1 11:52:20','2007-2-1 11:54:56')
insert into TelRecord values('68290000','0298521811199','2007-2-1 12:44:45','2007-2-1 12:45:04')
insert into TelRecord values('68290000','0298521811199','2007-2-1 12:44:45','2007-2-1 12:45:04')

select * from TelRecord
delete TelRecord where CommID = 2 or CommID = 11
select dateDiff(ss,'2007-2-1 9:50:22','2007-2-1 9:51:12')

--4、编写SQL语句，查询至少有2个电话的用户名、电话号码的个数；
select * from UserInfo

select UserName,电话个数=count(UelNo) from UserInfo group by UserName having count(UserName) >1

--5、编写SQL语句，查询通话时间少于1分钟的通话ID
select * from TelRecord

select CommID from TelRecord where datediff(ss,STime,ETime)<60

/*--6
编写SQL语句，找出通话记录表中存在的电话号码而在用户信息表中没有的电话号码，

然后把这些电话号码插入到用户信息表中，新插入的数据用户名为“未知”、地址为“未知”；
*/
select * from UserInfo
select * from TelRecord

insert into UserInfo select distinct  '未知',TelFrom,'未知' from TelRecord where TelFrom not in (select UelNo from UserInfo) 
insert into UserInfo select distinct  '未知',TelDest,'未知' from TelRecord where TelDest not in (select UelNo from UserInfo)

--7、编写存储过程，按输入的月份、主叫号码统计通话记录、通话总时间；
select * from TelRecord

alter procedure zsw
	@month int ,
	@TelFrom nvarchar(30)
	as
	set nocount on
    print convert(char(2),@month)+'月期间，主叫号码'+@TelFrom+'通过记录为：'
	select * from TelRecord where datename(mm,STime)=@month and TelFrom=@TelFrom
	declare @time int
	select @time=sum(datediff(ss,STime,ETime)) from TelRecord where datename(mm,STime)=@month and TelFrom=@TelFrom
	
	print convert(char(2),@month)+'月期间，主叫号码'+@TelFrom+'通过总时间为：'+convert(varchar(15),@time)+' 秒'

go

exec zsw 2,98290000