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