标签归档:sql面试

mssql sqlserver 面试问题–用户反应业务系统慢的时候,作为DBA,你需要做的第一件事?


摘要:
下文讲述sql server面试时,遇到的问题解答方法,供大家参考


个人觉得,系统慢时,首先从以下几个方面检查数据库系统:
1. sp_who 检查是否存在阻塞
2. 通过动态系统视图 检查最耗CPU 耗时最长的Sql语句,进行分析和优化
3. 检查服务器上的 CPU 内存 硬盘读写是否合理
4. 检查服务器设置的日常监控的各种报警值是否正常
5. 分析最近业务方面是否发生变化

mssql sqlserver 面试题-基础脚本编写(sql脚本编写)-3


摘要:
下文为公司最近根据实际业务编写的sql基础脚本面试16例,也是基本的sql脚本操作,您学习sql必须知道的知识,供大家参阅
主要考察 group by、having、where、连接、row_number 、聚合函数、插入数据、删除数据、查询操作
实验环境:sqlserver 2008 R2


 
  create table [用户表](userId int,userName nvarchar(10))
  create table [客户表](cusId int,cusName nvarchar(10))
  create table [维修主表](userId int,cusId int,writeDate datetime,billNo varchar(30),JinE decimal(18,2)) 
   go

   ---生成数据
   insert into [用户表](userId,userName) values(1,'张三'),(2,'猫猫'),(3,'经理')

   insert into [客户表](cusId ,cusName)values(1,'麻花藤'),(2,'东哥'),(6,'顺子')

   insert into [维修主表](userId,cusId,writeDate,billNO,JinE)values(1,1,'2018-5-17 10:00:02','A01',100)
   insert into [维修主表](userId,cusId,writeDate,billNO,JinE)values(1,2,'2018-5-17 11:02:02','A02',200)
   insert into [维修主表](userId,cusId,writeDate,billNO,JinE)values(2,1,'2018-5-18 10:00:02','A03',30)
   insert into [维修主表](userId,cusId,writeDate,billNO,JinE)values(1,1,'2018-5-19 10:00:02','A04',60) 
   insert into [维修主表](userId,cusId,writeDate,billNO,JinE)values(1,2,'2018-5-20 10:00:02','A05',60) 
   go
  
--面试1: 获取用户2018年5月接待客户总单数和总金额
    select count(distinct cusId) as [总接待客户],sum(jinE) as [总金额] from [维修主表] where writeDate between  '2018-5-1 0:00:00' and '2018-5-31 23:59:59' 
--面试2: 获取2018年5月来店总次数大于2次的客户
 select a.* from  [客户表] a 
 left join 
 (
select *,row_number() over(partition by cusId order by userId) as keyId
 from [维修主表] where writeDate between  '2018-5-1 0:00:00' and '2018-5-31 23:59:59' ) as b
 on  a.cusId =b.cusId 
 where b.keyId>2 
--面试3: 获取客户名等于"东哥"的维修记录信息
select a.*,b.cusName from [维修主表] a right join [客户表] b on a.cusId =b.cusId  where  b.cusName ='东哥'
--面试4: 获取维修金额大于100的客户信息
    select b.cusName,b.cusId from [维修主表] a right join [客户表] b on a.cusId =b.cusId  where  a.jine>100
--面试5: 获取未来店维修的客户信息
select * from [客户表] where not exists(select null from [维修主表] where [维修主表].cusId =[客户表].cusId)
--面试6: 获取同时被两个及以上店员接待过的客户信息
select * from [客户表] 
where exists(
 select null from  (select count(distinct userId) as userQty,cusId from [维修主表] group by cusId  
  having count(distinct userId)>=2)   t where t.cusId = [客户表].cusId 
 )

--面试7: 获取未接待过客户的店员信息
  select * from [用户表] 
   where not exists(
      select null from [维修主表] where [维修主表].userId = [用户表].userId
   )
   
--面试8: 生成2018年5月店员接待客户的业绩报表
  select a.*,isnull(b.[业绩],0) as [业绩]
     from [用户表]  a left join 
       (
           select  userId ,sum(jinE) as [业绩] 
           from [维修主表]  where writeDate between  '2018-5-1 0:00:00' and '2018-5-31 23:59:59'  group by userId 
        ) as b 
    on a.userId =b.userId  
    
--面试9: 获取2018年5月客户平均消费金额
   select a.cusId,a.cusName,avg(isnull(b.[业绩],0)) as [平均业绩]
     from [客户表]  a left join 
       (
           select  cusId,sum(jinE) as [业绩] 
           from [维修主表]  where writeDate between  '2018-5-1 0:00:00' and '2018-5-31 23:59:59'  group by cusId 
        ) as b 
    on a.cusId =b.cusId  
    group by a.cusId,a.cusName
--面试10: 删除维修单号等于"A04"的维修记录
 delete from [维修主表] where billNo ='A04'
--面试11:获取不同客户,但维修金额相同的维修信息及客户信息
    select * from [维修主表] c
   left join  [客户表] d on c.cusId =d.cusId
   where exists
   (
   select null from [维修主表] a
    left join [维修主表] b on a.cusId !=b.cusId and a.JinE =b.JinE
      where b.billNO =c.billNO
   ) 
--面试12:获取维修客户总数
select count(distinct cusId) as [客户总数] from [维修主表] 
--面试13: 获取维修金额大于30的单据,并按维修金额倒序排列
   select *  from [维修主表] 
 where jinE >30 order by jine desc 
--面试14: 获取最大维修金额的维修记录数
   select * from 
   (
     select *,row_number() over (order by jine desc ) as keyId from [维修主表] 
     ) A where A.keyId=1
--面试15:删除店员 “猫猫”所属的维修记录
 delete from [维修主表] 
where exists(
 select null from [用户表] where [用户表].userName='猫猫' and [用户表].userId =[维修主表].userId
)
--面试16:获取维修单金额大于平均维修金额的维修记录
 select * from [维修主表] where jine>(
  select avg(JinE) from [维修主表]
 )
  

   ---清理表

 go
  drop table [维修主表] 
  drop table  [客户表]
  drop table [用户表]

mssql sqlserver 面试题-基础部分(sql脚本编写)-1


摘要:
今天去参加c#开发工程师面试,最后尽然面试了sql脚本的写法
实验环境: sqlserver 2008 R2


   create table test(keyId int identity,qty int,info varchar(30))
   insert into test(qty,info)values(1,'a'),(2,'b'),(3,'C'),(6,'d'),(7,null),(8,'f')
   insert into test(qty,info)values(20,'a'),(6,'a'),(1,'m'),(1,'g'),(1,'f'),(10,null)
    go
   ---面试1.取表test,4~6行记录,keyId倒序排列,尽量采用多种方法---考虑最分页的掌握
     --方法1:使用top
	 select top 3 * from test where keyId not in (select top 3 keyId from test order by keyId desc  )  order by keyId desc 
	 --方法2:top +max 
	 select top 3 * from test where keyId <(select min(keyId) from (select top 3 keyId from test order by keyId desc  )as testExtend)  order by keyId desc 
	 --方法3:开窗函数 row_number 重新排序后,然后使用between
	 select * from (
	  select *,row_number() over(order by keyId desc ) as keyId2 from test ) as testExtend 
	  where testExtend.keyId2 between 4 and 6 
-------------------------------------------------------------------------
--面试2:请估算以下脚本的运行结果 ---考察对count的掌握
  select count(*) from test 
  select count(info) from test 
  ---分别输出 12 和10
  go
 ------------------------------------------------------------------------
 --面试3:获取info列重复超过2条的数据 ---考察对聚合函数筛选的用法
 select * from test where info in (
 select info from test group by info  having count(info)>2
 )
    
  go 
  truncate table test 
  drop table test