11月 作业

--类型转换
1 把日期转换成 月日年 的字符串的格式输出
 
select CONVERT(varchar(50),GETDATE())
2 查询出每个销售员的销售总金额，以及总的销售金额（底
 
部汇总）
 
select 销售员,销售总金额=SUM (销售数量*销售价格)from MyOrder group by 销售员
union all
select '总销售金额',SUM(销售数量*销售价格) from MyOrder
3 要求在一个表格中查询出学生的英语最高成绩、最低成绩
 
、平均成绩
 
（1）查询结果为1行
 
select  英语最高成绩=MAX(fEnglish),英语最低成绩=min(fEnglish),英语平均成绩=AVG(fEnglish) from T_MyStudents
（2）查询结果为3行
 
select '英语最高成绩',MAX(fEnglish)from T_MyStudents
union all
select '英语最低成绩',MIN(fEnglish)from T_MyStudents
union all
select '英语平均成绩',AVG(fEnglish)from T_MyStudents
4 复制表(只要表结构,不要数据)

（1）方法1
 
select * into New1 from Teacher where 1<>1
（2）方法2
 
select top 0 * into New2 from Teacher
5 把一个表的数据copy到另一个 已经用上面的方式生成的
 
表中。
 
insert into New2 select * from Teacher
 
6 创建一张表，记录电话呼叫员的工作流水，记录呼叫员编
 
号、对方号码、通话开始时间、通话结束时间。建表、插数
 
据等最后都自己写SQL语句。
要求：
 
create table Callrecord (Id int identity(1,1) primary key,Callnumber nvarchar(50),Tellnumber varchar(50),Startdatetime datetime,Enddatetime datetime)
--drop table Callrecord
select * from Callrecord
insert into Callrecord(Callnumber,Tellnumber,Startdatetime,Enddatetime)values('001', '0208888888', CAST(0x00009DAF00A4CB80 AS DateTime), CAST(0x00009DAF00A62E94 AS DateTime));
insert into Callrecord(Callnumber,Tellnumber,Startdatetime,Enddatetime)values('001', '0208888888', CAST(0x00009DB000D63BC0 AS DateTime), CAST(0x00009DB000D68DC8 AS DateTime));
insert into Callrecord(Callnumber,Tellnumber,Startdatetime,Enddatetime)values('001', '89898989', CAST(0x00009DB000E85C60 AS DateTime), CAST(0x00009DB000E92F50 AS DateTime));
insert into Callrecord(Callnumber,Tellnumber,Startdatetime,Enddatetime)values('002', '98987676', CAST(0x00009DB2015BB7A0 AS DateTime), CAST(0x00009DB2015C4DA0 AS DateTime));
insert into Callrecord(Callnumber,Tellnumber,Startdatetime,Enddatetime)values('002', '02188839389', CAST(0x00009DA4014C9C70 AS DateTime), CAST(0x00009DA4014E0308 AS DateTime));
insert into Callrecord(Callnumber,Tellnumber,Startdatetime,Enddatetime)values('001', '767676766', CAST(0x00009DB400DAA0C0 AS DateTime), CAST(0x00009DB400DD5FE0 AS DateTime));
insert into Callrecord(Callnumber,Tellnumber,Startdatetime,Enddatetime)values('003', '0227864656', CAST(0x00009DB200B9AB40 AS DateTime), CAST(0x00009DB200B9FC1C AS DateTime));
insert into Callrecord(Callnumber,Tellnumber,Startdatetime,Enddatetime)values('003', '676765777', CAST(0x00009DB8014042B8 AS DateTime), CAST(0x00009DB80141804C AS DateTime));
insert into Callrecord(Callnumber,Tellnumber,Startdatetime,Enddatetime)values('001', '89977653', CAST(0x00009D9A00FB9898 AS DateTime), CAST(0x00009D9A00FE6118 AS DateTime));
insert into Callrecord(Callnumber,Tellnumber,Startdatetime,Enddatetime)values('004', '400400400', CAST(0x00009D9A00FB9898 AS DateTime), CAST(0x00009D9A00FE6118 AS DateTime));
 

（1）输出所有数据中通话时间最长的5条记录。orderby

datediff
 
select top 5 通话最长时间=datediff(second,Startdatetime,Enddatetime) from Callrecord order by 通话最长时间 desc;
 

（2）输出所有数据中拨打长途号码（对方号码以0开头
 
）的总时长。like、sum
 
select 总时长=SUM(datediff(second,Startdatetime,Enddatetime)) from Callrecord where Tellnumber like '0%';
（3）输出本月通话总时长最多的前三个呼叫员的编号。
SELECT top 3 Callnumber FROM Callrecord group by Callnumber order by SUM(DATEDIFF(SECOND,Startdatetime,Enddatetime)) desc;
 
（4）输出本月拨打电话次数最多的前三个呼叫员的编号
 
.group by,count(*)。
 
select top 3 Callnumber from Callrecord group by Callnumber order by COUNT(*)desc;