数据库加强

--1计算100天后病毒发作的日期。
print Convert(varchar(50),dateadd(day,100,getdate()),111)


----------------------------------------------------------------------------------
--2凡是入职一年以上的员工，工资增加500￥
use Test
select * from Employees


update Employees set EmpSalary=EmpSalary+1000
where DATEADD(year,1,EmpInTime)<GETDATE()


--------------------------------------------------------------------------------
--3计算1975年10月5日到现在现在相差多少年/月/日/小时?
print datediff(year,'1975-10-5',getdate())
print datediff(month,'1975-10-5',getdate())
print datediff(day,'1975-10-5',getdate())
print datediff(hour,'1975-10-5',getdate())


----------------------------------------------------------------------------------------------------
--4统计2008年入职的员工
select * from Employees
where DATEPART(YEAR,EmpInTime)=2008


------------------------------------------------------------------------------------------------
--5输出所有数据中通话时间最长的5条记录.
use Test
select* from CallRecords


select top 5*,通话时长=DATEDIFF(second,StartDateTime,EndDateTime)
from CallRecords
order by 通话时长 desc,CallerNumber desc


------------------------------------------------------------------------
--6将结果集加入一列“通话时长（秒）”
use Test
select* from CallRecords


select *,通话时长=DATEDIFF(second,StartDateTime,EndDateTime)
from CallRecords
order by 通话时长 desc,CallerNumber desc




--------------------------------------------------------------------------------------------
--三、Case函数用法
--1.1
--use School
-- SELECT * FROM Score
-- SELECT * FROM Student
--90分以上优秀
--80分以上良好
--70分以上中等
--60分以上及格
--60分以下不及格




use MySchool
select * from Score
select * from Student


select studentId,scoreId,english,
评级=(
case
when english>=90 then '优秀'
when english>=80 then '良好'
when english>=70 then '中等'
when english>=60 then '及格'
else '不及格'
end)
from Score
order by english desc
------------------------------------------------------------------

--2、要求，查询结果集中有A B C三列,用SQL语句实现：
--当A列大于B列时，在C中显示A列的值否则显示B列中的值。

create table TestCase
(
A int,
B int
)
select * from TestCase
insert into TestCase values(10,20)
insert into TestCase values(100,32)

select
A,
B,
C=
CASE
WHEN A>=B THEN A
ELSE B
END
from TestCase

--------------------------------------------------------
--3、在订单表中，统计每个销售员的总销售金额，列出销售员名、总销售金额、称号（>6000金牌，>5500银牌,>4500铜牌，否则普通）
use MyOrder
select * from MyOrders

select 销售员,销售总金额=SUM(销售数量*销售价格),
称号=
(
case
when SUM(销售数量*销售价格)>6000 then '金牌'
when SUM(销售数量*销售价格)>5500 then '银牌'
when SUM(销售数量*销售价格)>4500 then '铜牌'
else '普通'
end
)
from MyOrders
group by 销售员
order by SUM(销售数量*销售价格) desc

----------------------------------------------------------------------------------
/*4.单号 金额
Rk1 10
Rk2 20
Rk3 -30
Rk4 -10
将上面的表输出为如下的格式：
单号 收入 支出
Rk1 10 0
Rk2 20 0
Rk3 0 30
Rk4 0 10
*/
create table OrderFrom(orderId int identity primary key,Number varchar(20),Ammont float)

insert into OrderFrom (Number,Ammont) values('Rk1',10)
insert into OrderFrom (Number,Ammont) values('Rk2',20)
insert into OrderFrom (Number,Ammont) values('Rk3',-30)
insert into OrderFrom (Number,Ammont) values('Rk4',-10)

select Number,
收入=
(
case
when Ammont>0 then Ammont
else 0
end
),
支出=
(
case
when Ammont<=0 then ABS(Ammont)
else 0
end
)
from OrderFrom