数据库练习题

练习1

将正负分开

select Fnumber, 
( 
case 
when Fmount>0 then Fmount 
else 0 
end 
) as 收入, 
( 
case 
when Fmount<0 then ABS(Fmount) 
else 0 
end 
) as 支出 
from mount

练习2

先考虑将胜负用1和0表示

select Name, 
( 
case Score 
when N'胜' then 1 
else 0 
end 
)as 胜, 
( 
case Score 
when N'负' then 1 
else 0 
end 
) as 负 
from Score

再将每个队胜的总和与负的总和累计，并按队进行分组。

select Name, 
sum( 
case Score 
when N'胜' then 1 
else 0 
end 
)as 胜, 
sum( 
case Score 
when N'负' then 1 
else 0 
end 
) as 负 
from Score 
group by Name

练习3

创建一张表，记录电话呼叫员的工作流水，记录呼叫员编号，对方号码，通话开始时间，通话结束时间。

要求：

输出所有数据中通话时间最长的5条记录；（order by ,datediff）

输出所有数据中拨打长途号码（对方号码以0开头）的总时长；(like,sum)

输出本月通话总时长最多的前三个呼叫员编号；datediff(month…),sum,order by

输出本月拨打电话次数最多的前三个呼叫员编号；group by,count()

输出所有数据的拨号流水，并且在最后一行添加总呼叫时长

      *呼叫员编号，对方号码，通话时长

      *…..

      *汇总【市内号码总时长】【长途号码总时长】

create table phone1(ID  nvarchar(50) not null,Fnumber varchar(50)null,starttime datetime null ,endtime datetime null )

插入数据

insert into phone1(ID,Fnumber,starttime,endtime)values('PN001','13689876545','2013-04-05 11:23:25','2013-04-05 11:26:21') 
insert into phone1(ID,Fnumber,starttime,endtime)values('PN001','13689123456','2013-04-11 12:23:25','2013-04-11 12:26:21') 
insert into phone1(ID,Fnumber,starttime,endtime)values('PN001','013689876789','2013-04-04 11:23:25','2013-04-04 11:29:21') 
insert into phone1(ID,Fnumber,starttime,endtime)values('PN001','013456786545','2013-04-22 09:23:25','2013-04-22 09:33:21') 
insert into phone1(ID,Fnumber,starttime,endtime)values('PN001','013684587545','2013-04-23 15:23:25','2013-04-23 15:24:20') 
insert into phone1(ID,Fnumber,starttime,endtime)values('PN001','13689876545','2013-04-06 11:23:25','2013-04-06 11:26:21') 
insert into phone1(ID,Fnumber,starttime,endtime)values('PN001','13689345545','2013-04-09 10:23:25','2013-04-09 10:25:15')

insert into phone1(ID,Fnumber,starttime,endtime)values('PN002','13689876545','2013-04-05 11:23:25','2013-04-05 11:26:21') 
insert into phone1(ID,Fnumber,starttime,endtime)values('PN002','13689123456','2013-04-11 12:23:25','2013-04-11 12:26:21') 
insert into phone1(ID,Fnumber,starttime,endtime)values('PN002','013689876789','2013-04-04 11:23:25','2013-04-04 11:26:29') 
insert into phone1(ID,Fnumber,starttime,endtime)values('PN002','013456786545','2013-04-22 09:23:25','2013-04-22 09:26:21') 
insert into phone1(ID,Fnumber,starttime,endtime)values('PN002','13684587545','2013-04-23 15:23:25','2013-04-23 15:24:20') 
insert into phone1(ID,Fnumber,starttime,endtime)values('PN002','013689876545','2013-04-06 11:23:25','2013-04-06 11:26:21') 
insert into phone1(ID,Fnumber,starttime,endtime)values('PN002','013689345545','2013-04-09 10:23:25','2013-04-09 10:25:15') 
insert into phone1(ID,Fnumber,starttime,endtime)values('PN002','013684587545','2013-04-23 15:13:25','2013-04-23 15:14:20') 
insert into phone1(ID,Fnumber,starttime,endtime)values('PN002','013689876545','2013-04-07 10:43:25','2013-04-07 10:46:21') 
insert into phone1(ID,Fnumber,starttime,endtime)values('PN002','13689345545','2013-04-09 10:53:25','2013-04-09 10:55:15')

insert into phone1(ID,Fnumber,starttime,endtime)values('PN003','13689123456','2013-04-11 12:23:25','2013-04-11 12:26:21') 
insert into phone1(ID,Fnumber,starttime,endtime)values('PN003','013689876789','2013-04-04 11:23:25','2013-04-04 11:26:29') 
insert into phone1(ID,Fnumber,starttime,endtime)values('PN003','013456786545','2013-04-22 09:23:25','2013-04-22 09:26:21') 
insert into phone1(ID,Fnumber,starttime,endtime)values('PN003','13684587545','2013-04-23 15:23:25','2013-04-23 15:24:20') 
insert into phone1(ID,Fnumber,starttime,endtime)values('PN003','013689876545','2013-04-06 11:23:25','2013-04-06 11:26:21') 
insert into phone1(ID,Fnumber,starttime,endtime)values('PN003','013689345545','2013-04-09 10:23:25','2013-04-09 10:25:15')

insert into phone1(ID,Fnumber,starttime,endtime)values('PN004','13689876545','2013-04-05 11:23:25','2013-04-05 11:26:21') 
insert into phone1(ID,Fnumber,starttime,endtime)values('PN004','13689123456','2013-04-11 12:23:25','2013-04-11 12:26:21') 
insert into phone1(ID,Fnumber,starttime,endtime)values('PN004','013689876789','2013-04-04 11:23:25','2013-04-04 11:36:29') 
insert into phone1(ID,Fnumber,starttime,endtime)values('PN004','013456786545','2013-04-22 09:23:25','2013-04-22 09:26:21') 
insert into phone1(ID,Fnumber,starttime,endtime)values('PN004','13684587545','2013-04-23 15:23:25','2013-04-23 15:34:20') 
insert into phone1(ID,Fnumber,starttime,endtime)values('PN004','013689876545','2013-04-06 11:23:25','2013-04-06 11:26:21') 
insert into phone1(ID,Fnumber,starttime,endtime)values('PN004','013689345545','2013-04-09 10:23:25','2013-04-09 10:25:15') 
insert into phone1(ID,Fnumber,starttime,endtime)values('PN004','013684587545','2013-04-23 15:13:25','2013-04-23 15:33:20') 
insert into phone1(ID,Fnumber,starttime,endtime)values('PN004','013689876545','2013-04-07 10:43:25','2013-04-07 10:46:21')

insert into phone1(ID,Fnumber,starttime,endtime)values('PN005','013689345545','2013-04-09 10:23:25','2013-04-09 10:25:15') 
insert into phone1(ID,Fnumber,starttime,endtime)values('PN005','013684587545','2013-04-23 15:13:25','2013-04-23 15:17:20') 
insert into phone1(ID,Fnumber,starttime,endtime)values('PN005','013689876545','2013-04-07 10:43:25','2013-04-07 10:49:21') 
insert into phone1(ID,Fnumber,starttime,endtime)values('PN005','13689345545','2013-04-09 10:53:25','2013-04-09 10:59:15') 
insert into phone1(ID,Fnumber,starttime,endtime)values('PN005','013689345545','2013-04-09 10:23:25','2013-04-09 10:25:15') 
insert into phone1(ID,Fnumber,starttime,endtime)values('PN005','013684587545','2013-04-23 15:13:25','2013-04-23 15:17:20') 
insert into phone1(ID,Fnumber,starttime,endtime)values('PN005','013689876545','2013-04-07 10:43:25','2013-04-07 10:49:21')

输出所有数据中通话时间最长的5条记录

select top 5 *,datediff(minute,starttime,endtime)as 时长 from phone1 order by datediff(minute,starttime,endtime) DESC 

输出所有数据中拨打长途号码（对方号码以0开头）的总时长

select ID, 
sum(datediff(minute,starttime,endtime))as 长途总时长 from phone1 
where substring(Fnumber,1,1)='0' 
group by ID 
union all 
select 'sum',sum(datediff(minute,starttime,endtime))as 长途总时长 from phone1 
where substring(Fnumber,1,1)='0' 

输出本月通话总时长最多的前三个呼叫员编号

select top 3 ID,sum(datediff(minute,starttime,endtime)) as 四月总时长 from phone1 group by ID 
order by sum(datediff(minute,starttime,endtime)) DESC 

输出本月拨打电话次数最多的前三个呼叫员编号

select top 3 ID, count(*)as 通话次数 from phone1 group by ID  order by count(*) DESC

输出所有数据的拨号流水，并且在最后一行添加总呼叫时长

      *呼叫员编号，对方号码，通话时长

      *…..

      *汇总【市内号码总时长】【长途号码总时长】

select ID as 呼叫员编号, Fnumber as 对方号码,datediff(second,starttime,endtime)as 通话时长 from phone1

select '汇总', 
sum( 
case substring(Fnumber,1,1) 
when 1 then datediff(minute,starttime,endtime) 
else 0 
end 
)as 市话总时长, 
sum( 
case substring(Fnumber,1,1) 
when 0 then datediff(minute,starttime,endtime) 
else 0 
end 
)as 长途总时长 
from phone1

汇总后的效果

select ID,Fnumber,DateDiff(second,StartTime,EndTime)
from phone1
union all
select '汇总',
convert(varchar(50),
sum((case 
when Fnumber not like '0%' then DateDiff(second,StartTime,EndTime)
else 0
end
))
) as 市内号码时长,
sum((case
when Fnumber like '0%' then DateDiff(second,StartTime,EndTime)
else 0
end
)) as 长途号码时长
from phone1;

练习3参考答案

--输出所有数据中通话时间最长的5条记录；（order by ,datediff）

select top 5 * from T_CallRecords1

order by DateDiff(second,StartDateTime,EndDateTime) Desc

--输出所有数据中拨打长途号码（对方号码以0开头）的总时长；(like,sum)

select sum(datediff(second,StartDateTime,EndDateTime))from T_CallRecords1

where TelNum like ‘0%’

 

--输出本月通话总时长最多的前三个呼叫员编号；datediff(month…),sum,order by

select top 3 CallerNumber from T_CallRecords1 

where datedifff(month,StartDateTime,getdate())=0

group by CallerNumber 

order by sum( datediff(second,StartDateTime,EndDateTime)) DESC

 

--输出本月拨打电话次数最多的前三个呼叫员编号；group by,count()

select top 3 CallerNumber,count(*)

from T_CallerNumber

where datedifff(month,StartDateTime,getdate())=0 

group by CallerNumber

order by count(*) DESC

 

输出所有数据的拨号流水，并且在最后一行添加总呼叫时长

      *呼叫员编号，对方号码，通话时长

      *…..

      *汇总【市内号码总时长】【长途号码总时长】

select CallerNumber,TelNum,datediff(second,StartDateTime,EndDateTime)

from T_CallRecords1

unin all

select  N ‘汇总’,

convert( varchar(50),sum(

case

when TelNum not like ‘0%’then datediff(second,StartDateTime,EndDateTime)

else o

end

) 

)as 市内通话,

sum(

case

when TelNum like ‘0%’then datediff(second,StartDateTime,EndDateTime)

else o

end

) as 长途通话

from T_CallRecords1