SqlServer基操误6，请坐~
SQL Server 技术水平测试题
一、 语言测试题 （50分）
注：只需写出语句，不必列出执行结果
前提：假设现有两张表，以【A0190】（人员编号）为关联。表结构分别如下描述：
A01 （人员信息表）
|
序号 |
字段名称 |
中文描述 |
数据类型 |
数据长度 |
属性 |
|
1 |
A0190 |
人员编号 |
字符 |
16 |
主键约束 |
|
2 |
A0101 |
姓名 |
字符 |
10 |
非空 |
|
3 |
A0177 |
身份证号 |
字符 |
18 |
可空 |
|
4 |
A0111 |
出生日期 |
日期 |
|
可空 |
|
5 |
A01101 |
年龄 |
数值 |
4 |
可空 |
|
6 |
A01114 |
学历 |
字符 |
20 |
可空 |
A64 （学历信息表）
|
序号 |
字段名称 |
中文描述 |
数据类型 |
数据长度 |
属性 |
|
1 |
A0190 |
人员编号 |
字符 |
16 |
主键约束 |
|
2 |
A_ID |
序号 |
整型 |
4 |
非空 自增量（1，1） |
|
3 |
A0405 |
学历 |
字符 |
10 |
可空 |
|
4 |
A0410 |
所学专业 |
字符 |
30 |
可空 |
|
5 |
A0415 |
入学时间 |
日期 |
10 |
可空 |
|
6 |
A0425 |
学制 |
数值 |
4，1 |
可空 |
A01表内数据
|
人员编号 |
姓名 |
身份证号 |
学历 |
出生日期 |
|
2 |
刘敏 |
130404195402153033 |
大本毕业 |
1954-2-15 |
|
4 |
郝贤 |
130404720405243 |
研究生 |
1972-4-5 |
|
11 |
冷传东 |
130404561030302 |
研究生 |
1956-10-30 |
|
12 |
黄晓楠 |
130481770128167 |
大本毕业 |
1977-1-28 |
|
13 |
洪军 |
230102791008162 |
大本毕业 |
1979-10-8 |
|
14 |
王敏 |
130481740905271 |
大本毕业 |
1974-9-5 |
|
16 |
王德财 |
130404561115247 |
大本毕业 |
1956-11-15 |
|
18 |
于静 |
130404751201303 |
大本毕业 |
1975-12-1 |
|
19 |
王宪君 |
130404197306092410 |
大本毕业 |
1973-6-9 |
A64 表内数据
|
人员编号 |
序号 |
学历 |
所学专业 |
入学时间 |
学制 |
|
2 |
1 |
大本毕业 |
企业管理 |
1974-7-1 |
4 |
|
2 |
2 |
高中 |
NULL |
1971-9-1 |
3 |
|
2 |
3 |
研究生毕业 |
MBA |
2000-4-10 |
2 |
|
4 |
4 |
大学毕业 |
经济信息管理 |
1991-7-1 |
3 |
|
4 |
5 |
研究生毕业 |
MBA |
2000-4-1 |
2 |
|
11 |
6 |
研究生 |
材料及热处理 |
1985-9-1 |
2 |
|
12 |
7 |
大本毕业 |
材料及热处理 |
1980-7-1 |
4 |
|
13 |
8 |
大本毕业 |
材料及热处理 |
1982-9-1 |
4 |
|
14 |
9 |
大本毕业 |
材料及热处理 |
1983-9-1 |
4 |
|
16 |
10 |
大本毕业 |
材料及热处理 |
1984-9-1 |
4 |
|
18 |
11 |
大本毕业 |
材料及热处理 |
1983-10-1 |
4 |
|
19 |
12 |
大本毕业 |
材料及热处理 |
1988-1-1 |
4 |
1、 给A64表插入一个列 （5分）
|
7 |
A01115 |
血型 |
字符 |
20 |
可空 |
Arter table A64 add a011115 varchar(20) null
2、 往A64表内插入下面的数据 （5分）
|
人员编号 |
学历 |
所学专业 |
入学时间 |
|
12 |
大本毕业 |
企业管理 |
1979-7-1 |
Insert into A64(人员编号，学历，所学专业，入学时间)
values (12,’大本毕业’,’企业管理’,’1979-7-1’);
3、 更新A01表，把所有“王”姓人员，变成“李”姓人员（10分）
update A01 set A0101= '李'+ SUBSTRING(A0101, LEN('王') + 1, LEN(A0101)) where A0101like '王%'
4、 写出一段语句，计算出在70年代的人员占总数的百分比是多少？ （5分）
Select (Select count(1) form A01 where
CONVERT(VARCHAR(10),出生日期,120) >’1970%’
and CONVERT(VARCHAR(10),出生日期,120) <’1980%’
) / count(1) as a1 form A01
5、 更新A01表，根据【出生日期】，得出每个人的年龄 （5分）
提示：与系统时间比较，取相差年数
Update A01 set A01101 = DATEDIFF(year,A0111,getdate()) where A0190=A0190
6、 查询出A01表中大于平均年龄的人员名单 （5分）
Select * from A01 where DATEDIFF(year,A0111,getdate()) >(Select sum(DATEDIFF(year,A0111,getdate())) / count(1) from A01)
7、 查询出A01表中，学历记录 〉=2的人员名单 （15分）
提示：A01与A64 【A0190】关联，以A64中每个人的记录数 〉=2为条件
Select * from A01 where A0190 in (Select A01.A0190 from A01 left join A64 on A01.A0190 = A64.A0190 group by having count(A0190) >= 2)
加试题：
C21表中，应税工资【C2101】，个人所得税【C2102】
根据个人所得税计算公式及税率表，计算出个人所得税。
语句应该涵盖各种税率，要求用Case语句。
个人所得税计算公式：
个人所得税=（应税工资-1600）× 税率－速算扣除数
|
级数 |
应纳税所得额 |
税率（%） |
速算扣除数 |
|
1 |
不超过500元的 |
5 |
0 |
|
2 |
超过500元至2，000的部分 |
10 |
25 |
|
3 |
超过2，000元至5，000的部分 |
15 |
125 |
|
4 |
超过5，000元至20，000元的部分 |
20 |
375 |
|
5 |
超过20，000元至40，000元的部分 |
25 |
1375 |
|
6 |
超过40，000元至60，000元的部分 |
30 |
3375 |
|
7 |
超过60，000元至80，000元的部分 |
35 |
6375 |
|
8 |
超过80，000元至100，000元的部分 |
40 |
10375 |
|
9 |
超过100，000元的部分 |
45 |
15375 |
select
case when (C2101-1600) <500 then (C2101-1600) * C2102
case when (C2101-1600) BETWEEN 500 and 2000
then (C2101-1600)* C2102 -25
case when (C2101-1600) BETWEEN 2000 and 5000
then (C2101-1600)* C2102 -125
case when (C2101-1600) BETWEEN 5000 and 20000
then (C2101-1600)* C2102 -375
case when (C2101-1600) BETWEEN 20000 and 40000
then (C2101-1600)* C2102 -1375
case when (C2101-1600) BETWEEN 40000 and 60000
then (C2101-1600)* C2102 -3375
case when (C2101-1600) BETWEEN 60000 and 80000
then (C2101-1600)* C2102 -6375
case when (C2101-1600) BETWEEN 80000 and 100000
then (C2101-1600)* C2102 -10375
else
(C2101-1600)* C2102 -15375
end
from C21
