10--26sql 看看肯定是有用的

--类型转换
1 把日期转换成 月日年 的字符串的格式输出
select convert (varchar(50),getdate())
2 查询出每个销售员的销售总金额，以及总的销售金额（底

部汇总）

select
销售员,
销售金额=SUM (销售数量*销售价格)
from MyOrders
group by 销售员 
union all
select
'总的销售金额',
SUM (销售数量* 销售价格)
from MyOrders

3 要求在一个表格中查询出学生的英语最高成绩、最低成绩

、平均成绩

（1）查询结果为1行

select
英语成绩最高=MAX(FEnglish),
英语成绩最低=MIN(FEnglish),--列名
平均成绩=AVG(FEnglish)
from Mystudents

（2）查询结果为3行

select'最高英语成绩', MAX(FEnglish)from Mystudents  union all
select '最低英语成绩',MIN(FEnglish)from Mystudents union all
select '平均英语成绩', AVG(FEnglish)from Mystudents

4 复制表(只要表结构,不要数据)
  

 （1）方法1

select *
into New2Teacher
from TblTeacher
where 1<>1

（2）方法2
use TestSchool
select top 0 *
into New3Teacher
from TblTeacher

5 把一个表的数据copy到另一个 已经用上面的方式生成的

表中。
set identity_insert New3Teacher on
insert into New3Teacher values('玉良',1,22,2000,'2010-10-10')
select* from TblTeacher
set identity_insert New3Teacher off

6 创建一张表，记录电话呼叫员的工作流水，记录呼叫员编

号、对方号码、通话开始时间、通话结束时间。建表、插数

据等最后都自己写SQL语句。
   要求：
  CREATE TABLE CallRecords
(
  Id int NOT NULL identity(1,1) primary key,
     CallerNumber nvarchar(50),
  TelNum varchar(50),
  StartDateTime datetime not null,
  EndDateTime datetime not null
)
insert  into CallRecords (CallerNumber, TelNum, StartDateTime, EndDateTime) values('001', '0208888888', CAST(0x00009DAF00A4CB80 AS DateTime), CAST(0x00009DAF00A62E94 AS DateTime));
insert  into CallRecords (CallerNumber, TelNum, StartDateTime, EndDateTime) values ('001', '0208888888', CAST(0x00009DB000D63BC0 AS DateTime), CAST(0x00009DB000D68DC8 AS DateTime));
insert  into CallRecords (CallerNumber, TelNum, StartDateTime, EndDateTime) values('001', '89898989', CAST(0x00009DB000E85C60 AS DateTime), CAST(0x00009DB000E92F50 AS DateTime));
insert  into CallRecords (CallerNumber, TelNum, StartDateTime, EndDateTime) values ('002', '98987676', CAST(0x00009DB2015BB7A0 AS DateTime), CAST(0x00009DB2015C4DA0 AS DateTime));
insert  into CallRecords (CallerNumber, TelNum, StartDateTime, EndDateTime) values ('002', '02188839389', CAST(0x00009DA4014C9C70 AS DateTime), CAST(0x00009DA4014E0308 AS DateTime));
insert  into CallRecords (CallerNumber, TelNum, StartDateTime, EndDateTime) values ('001', '767676766', CAST(0x00009DB400DAA0C0 AS DateTime), CAST(0x00009DB400DD5FE0 AS DateTime));
insert  into CallRecords (CallerNumber, TelNum, StartDateTime, EndDateTime) values ('003', '0227864656', CAST(0x00009DB200B9AB40 AS DateTime), CAST(0x00009DB200B9FC1C AS DateTime));
insert  into CallRecords (CallerNumber, TelNum, StartDateTime, EndDateTime) values ('003', '676765777', CAST(0x00009DB8014042B8 AS DateTime), CAST(0x00009DB80141804C AS DateTime));
insert  into CallRecords (CallerNumber, TelNum, StartDateTime, EndDateTime) values('001', '89977653', CAST(0x00009D9A00FB9898 AS DateTime), CAST(0x00009D9A00FE6118 AS DateTime));
insert  into CallRecords (CallerNumber, TelNum, StartDateTime, EndDateTime) values ('004', '400400400', CAST(0x00009D9A00FB9898 AS DateTime), CAST(0x00009D9A00FE6118 AS DateTime));

--（1）输出所有数据中通话时间最长的5条记录。orderby datediff
select
top 5 *
from CallRecords
order by datediff(second,StartDateTime,EndDateTime) desc
--（2）输出所有数据中拨打长途号码（对方号码以0开头）的总时长。like、sum
select
sum(datediff(second,StartDateTime,EndDateTime))
from CallRecords
where TelNum like '0%'
--（3）输出本月通话总时长最多的前三个呼叫员的编号。
select
top 3 CallerNumber,
sum(datediff(ss,StartDateTime,EndDateTime))
from CallRecords
where datediff(month,StartDateTime,'2010-07-1') = 0
group by CallerNumber
order by sum(datediff(ss,StartDateTime,EndDateTime)) desc
----------------方法二

select top 3 CallerNumber ,SUM (DATEDIFF (second,StartDateTime,EndDateTime )) from CallRecords
where DATEPART (MONTH ,StartDateTime)=DATEPART (MONTH ,GETDATE() )
group by callerNumber
order by SUM (DATEDIFF (second,StartDateTime,EndDateTime )) desc;

--(4)输出本月拨打电话次数最多的前三个呼叫员的编号.group by,count(*)按照月份分组。

select
top 3 CallerNumber,
count(*) 
from CallRecords
where datediff(month,StartDateTime ,'2010-07-1') = 0
group by CallerNumber
order by count(*) desc
---------------方法二
select
top 3 CallerNumber,
count(*) from
CallRecords
where DATEPART(month,StartDateTime)=DATEPART(month,getdate())
group by CallerNumber
order by count(*) desc