SQL 初级考核题

  1 --题1.按每个表要求，为4个表增加唯一索引，写出方法或语句。
  2 use sql_test
  3 go
  4 create index org_index on org(orgid);
  5 create index branch_index on branch(orgid, brhid);
  6 create index user_index on users(userid);
  7 create index msg_index on msg(sendsno);
  8 
  9 --题2.select
 10 --a按照如下格式显示用户详细信息
 11 --        userid,username,orgid,brhid,userprop,orgname,brhname,userpropname
 12 --例：    1     ,员工1   ,0001 ,0101 ,0       ,公司1  ,销售1部,员
 13 select ROW_NUMBER() OVER(order by c.brhid) as userid, a.username, a.orgid, a.brhid, a.userprop, b.orgname,c.bhname as brhname,
 14         (case when userprop=‘0‘ then ‘经理‘
 15          when userprop=‘1‘ then ‘副经理‘
 16          when userprop=‘2‘ then ‘助理‘
 17          else ‘员工‘ end) as userpropname
 18 from users a, org b, branch c
 19 where a.orgid = b.orgid and b.orgid=c.orgid and c.brhid = a.brhid
 20 
 21 --b使用左联按照如下格式显示用户详细信息，没有的字段内容用"-"显示
 22 --        userid,username,orgid,brhid,userprop,orgname,brhname,userpropname
 23 --例：    1     ,员工1   ,0001 ,0101 ,0       ,公司1  ,销售1部,员工
 24 select (ROW_NUMBER() OVER(order by a.brhid)) as userid, isnull(a.username,‘-‘) as username, isnull(b.orgid,‘-‘) as orgid,
 25  isnull(c.brhid,‘-‘) as brhid, isnull(a.userprop,‘-‘) as userprop,isnull(b.orgname,‘-‘) as orgname, isnull(c.bhname,‘-‘) as brhname,
 26  isnull((case when userprop=‘0‘ then ‘经理‘
 27              when userprop=‘1‘ then ‘副经理‘
 28              when userprop=‘2‘ then ‘助理‘
 29              else ‘员工‘ end), ‘-‘) as userpropname
 30 from users a left join org b on a.orgid = b.orgid left join branch c on b.orgid = c.orgid and a.brhid = c.brhid
 31 
 32 --题3.insert
 33 --通过msg表发送2条消息，消息格式如下：
 34 --消息1发给全体人员：公司名+部门名+职务名+姓名+你好，您的用户号码为:userid。
 35 --消息2发给员工：员工+姓名+你好，下午3点在部门助理(助理姓名+用户号码)工位集合，发送人:（经理姓名+用户号）。
 36 
 37 --消息1：(注意insert后的select语句不能用括号括起来，会报语法错误)
 38 --truncate table msg
 39 insert into msg 
 40 select ROW_NUMBER() OVER(order by a.userid), GETDATE(), a.userid, b.orgname+‘+‘+c.bhname+‘+‘+
 41             (case when userprop=‘0‘ then ‘经理‘
 42                   when userprop=‘1‘ then ‘副经理‘
 43                   when userprop=‘2‘ then ‘助理‘
 44                   else ‘员工‘ end)+‘+‘+a.username+‘+‘+‘你好，您的用户号码为：‘+‘+‘+a.userid, 1 
 45 from users a, org b, branch c where a.orgid=b.orgid and a.brhid=c.brhid and b.orgid=c.orgid
 46 --消息2：
 47 --truncate table msg
 48 insert into msg
 49 select ROW_NUMBER() OVER(order by a.userid), GETDATE(), a.userid, ‘员工‘+‘+‘+a.username+‘+‘+
 50 ‘你好，下午3点部门助理(‘+a.username+‘+‘+a.userid+‘)工位集合，发送人：(‘+a.username+‘+‘+userid+‘)。‘, 1
 51 from users a, org b, branch c 
 52 where a.orgid=b.orgid and b.orgid=c.orgid and a.brhid=c.brhid and a.username like ‘%员工%‘
 53 select * from msg;
 54 
 55 --题4.update
 56 --0003公司各部门号码变更，研发用销售，销售用市场，市场用工程 ，工程用研发，
 57 --按要求修改branch和users
 58 if exists(select * from sys.objects where type=‘U‘ and name=‘branch_bak‘)
 59 drop table branch_bak
 60 if exists(select * from sys.objects where type=‘U‘ and name=‘users_bak‘)
 61 drop table users_bak
 62 select * into users_bak from users;
 63 select * into branch_bak from branch;
 64 
 65 --select SUBSTRING(bhname,3,1) from branch;
 66 --select a.brhid,b.brhid from branch a, branch_bak b
 67 --where a.orgid=‘0003‘ and b.orgid=‘0003‘ and substring(a.bhname, 3, 1)=substring(b.bhname, 3, 1) and a.bhname like ‘%工程%‘ and b.bhname like ‘%研发%‘
 68 update a set a.brhid=b.brhid from branch a, branch_bak b
 69 where a.orgid=‘0003‘ and b.orgid=‘0003‘ and substring(a.bhname, 3, 1)=substring(b.bhname, 3, 1) and a.bhname like ‘%研发%‘ and b.bhname like ‘%销售%‘
 70 update a set a.brhid=b.brhid from branch a, branch_bak b
 71 where a.orgid=‘0003‘ and b.orgid=‘0003‘ and substring(a.bhname, 3, 1)=substring(b.bhname, 3, 1) and a.bhname like ‘%销售%‘ and b.bhname like ‘%市场%‘
 72 update a set a.brhid=b.brhid from branch a, branch_bak b
 73 where a.orgid=‘0003‘ and b.orgid=‘0003‘ and substring(a.bhname, 3, 1)=substring(b.bhname, 3, 1) and a.bhname like ‘%市场%‘ and b.bhname like ‘%工程%‘
 74 update a set a.brhid=b.brhid from branch a, branch_bak b
 75 where a.orgid=‘0003‘ and b.orgid=‘0003‘ and substring(a.bhname, 3, 1)=substring(b.bhname, 3, 1) and a.bhname like ‘%工程%‘ and b.bhname like ‘%研发%‘
 76 
 77 --select * from users a, branch_bak b, branch c
 78 --where a.brhid=b.brhid and b.bhname=c.bhname and a.orgid=‘0003‘ and b.orgid=‘0003‘ and c.orgid=‘0003‘
 79 update a set a.brhid=c.brhid from users a, branch_bak b, branch c
 80 where a.brhid=b.brhid and b.bhname=c.bhname and a.orgid=‘0003‘ and b.orgid=‘0003‘ and c.orgid=‘0003‘
 81 
 82 
 83 --题5.delete
 84 --删除发给0003公司人员msg表中，发送内容有"发送人"信息的记录。
 85 delete from msg where msgtxt like ‘%发送人%‘ and userid like ‘0003%‘
 86 
 87 --题6.统计group by 
 88 --一条语句显示：机构名，部门名，经理数，副经理数，助理数，员工数，用户总数
 89 select b.orgname as ‘机构名‘, c.bhname as ‘部门名‘, 
 90 sum(case when a.userprop=‘0‘ then 1 else 0 end) as ‘经理数‘,
 91 sum(case when a.userprop=‘1‘ then 1 else 0 end) as ‘副经理数‘,
 92 sum(case when a.userprop=‘2‘ then 1 else 0 end) as ‘助理数‘,
 93 sum(case when a.userprop=‘3‘ then 1 else 0 end) as ‘员工数‘,
 94 count(*) as ‘用户总数‘
 95 from users a, org b, branch c
 96 where a.orgid=b.orgid and b.orgid=c.orgid and a.brhid=c.brhid 
 97 group by b.orgname, c.bhname
 98 
 99 --题7.字符串操作substring,charindex,len 
100 --从user表中的userid字段中取出用"-"号分隔的三部分内容。
101 --例如0001-0101-201：    第一部分,第二部分,第三部分,字符串总长度，
102 --                        0001    ,0101    ,201     ,13
103 
104 --法一：
105 select substring(userid, 1, charindex(‘-‘, userid)-1) as ‘第一部分‘,
106 substring(userid, charindex(‘-‘,userid)+1, charindex(‘-‘,userid,charindex(‘-‘,userid)+1)-1-charindex(‘-‘,userid)) as ‘第二部分‘,
107 substring(userid, len(userid)-charindex(‘-‘,reverse(userid))+2, charindex(‘-‘,reverse(userid))-1) as ‘第三部分‘,
108 len(userid) as ‘字符串总长度‘
109 from users order by userid;
110 --法二：
111 select substring(userid, 1, CHARINDEX(‘-‘, userid)-1) as ‘第一部分‘,
112 substring(userid,CHARINDEX(‘-‘, userid)+1,len(userid)-CHARINDEX (‘-‘, REVERSE(userid))-CHARINDEX(‘-‘, userid)) as ‘第二部分‘,
113 substring(REVERSE(userid), 1, charindex(‘-‘, reverse(userid))-1) as ‘第三部分‘,
114 len(userid) as ‘字符串总长度‘ from users order by userid
115 --法三：
116 select LEFT(userid, CHARINDEX(‘-‘, userid)-1) as ‘第一部分‘, 
117 LEFT(RIGHT(userid, len(userid)-CHARINDEX(‘-‘, userid)),charindex(‘-‘, RIGHT(userid, len(userid)-CHARINDEX(‘-‘, userid)))-1) as ‘第二部分‘,
118 reverse(LEFT(REVERSE(userid), charindex(‘-‘, reverse(userid))-1)) as ‘第三部分‘,
119 LEN(userid) as ‘字符串总长度‘ from users order by userid
120 
121 --题8.日期时间操作convert,datepart,datediff...
122 --a，从msg表中查询结果，格式如下：
123 --     序号，日期，星期几，年，月，日，时，分，秒，毫秒，距现在时间间隔（小时），距现在时间间隔（分钟），距现在时间间隔（秒）
124 --select * from msg;
125 select sendsno as ‘序号‘, CONVERT(varchar(20),sendtime,102) as ‘日期‘,datename(W, sendtime) as ‘星期‘,DATEPART(YYYY,sendtime) as ‘年‘,
126 datepart(mm,sendtime) as ‘月‘,datepart(dd,sendtime) as ‘日‘,datepart(hh,sendtime) as ‘时‘,datepart(n,sendtime) as ‘分‘,datepart(s,sendtime) as ‘秒‘,
127 datepart(ms,sendtime) as ‘毫秒‘,datediff(HH,sendtime,getdate()) as ‘距现在的时间间隔（小时）‘,datediff(N,sendtime,getdate()) as ‘距现在的时间间隔（分钟）‘,
128 datediff(ss,sendtime,getdate()) as ‘距现在的时间间隔（秒）‘
129 from msg;
130 --b，找出序号30消息发送时间前后小于等于1秒的全部记录。
131 select * from msg where abs(datediff(ss, (select sendtime from msg where sendsno=30), sendtime)) <= 1;
132 
133 
134 --题9.编写函数 fn_get_orgbrh_name
135 --select userid,username,orgid,brhid,dbo.fn_get_orgbrh_name(orgid,0) as orgname,dbo.fn_get_orgbrh_name(orgid,brhid) as brhname from users
136 --编写能够返回机构名和部门名称的函数，使用函数实现如下显示
137 --        userid,username,orgid,brhid,orgname,brhname
138 --例：    1     ,员工1   ,0001 ,0101 ,公司1  ,销售1部
139 if exists (select * from sysobjects where name=‘fn_get_orgbrh_name‘)
140 drop function fn_get_orgbrh_name
141 go
142 create function fn_get_orgbrh_name(@orgid_tmp char(5), @brhid_tmp char(5))
143 returns varchar(20) 
144 as
145 begin
146     declare @tmp varchar(20)
147     if(CAST(@brhid_tmp as int)=0)
148         select @tmp=orgname from sql_test..org where orgid=@orgid_tmp
149         --set @tmp=(select orgname from org where orgid=@orgid_tmp)  --注意这样写必须加括号，否则语法错误
150     else
151         select @tmp=bhname from sql_test..branch where @orgid_tmp=orgid and @brhid_tmp=brhid
152     return @tmp
153 end
154 go
155 select userid,username,orgid,brhid,dbo.fn_get_orgbrh_name(orgid,0) as orgname,dbo.fn_get_orgbrh_name(orgid,brhid) as brhname from users
156 
157 
158 --题10.编写存储过程 up_sql_test_proc
159 --存储过程使用游标逐条判断，找出uses表中，员工编号中包含的机构和部门号码，与实际所属的机构和部门号码不一致的用户，
160 --同时发送msg表消息给该员工，消息内容为现有号码和机构部门的不一致情况的描述和通知该员工来变更资料，
161 --存储过程返回值格式：执行成功，不一致人数【10】人。
162 --注意：用户编号的组成是机构编码+部门编码+用户编号三部分组成。
163 
164 --select * from msg
165 if exists (select * from sysobjects where name=‘up_sql_test_proc‘)
166 drop procedure up_sql_test_proc
167 go
168 
169 create proc up_sql_test_proc
170 as
171 begin
172     if Cursor_Status(‘global‘,‘tmp_cursor‘)>0
173     deallocate usercursor 
174     
175     declare tmp_cursor cursor global scroll for select userid, orgid,brhid  from users
176     declare @t1 varchar(20), @t2 varchar(20), @t3 varchar(20), @t4 varchar(20), @t5 varchar(20), @cnt int, @msg varchar(50)
177     open tmp_cursor
178     fetch next from tmp_cursor into @t1, @t2, @t3
179     set @cnt = 0
180     while(@@fetch_status = 0)
181     begin
182         set @t4=SUBSTRING(@t1, 1, CHARINDEX(‘-‘, @t1)-1)
183         set @t5=substring(@t1, charindex(‘-‘, @t1)+1, len(@t1)-charindex(‘-‘,reverse(@t1))-CHARINDEX(‘-‘, @t1))
184         --print @t4
185         --print @t5
186         if(@t2 != @t4 or @t3 != @t5)
187         begin
188             set @cnt = @cnt+1
189             insert into msg values(1, GETDATE(), @t1, ‘你好，你的机构号码‘+@t2+‘和部门号码‘+@t3+‘有误，请及时更正！！‘, 1)
190         end
191         fetch next from tmp_cursor into @t1, @t2, @t3
192     end
193     close tmp_cursor
194     deallocate tmp_cursor
195     set @msg=‘执行成功，不一致人数【‘+convert(varchar(20), @cnt)+‘】人‘
196     print @msg
197 end
198 GO
199 exec up_sql_test_proc