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