某外企SQL Server面试题

2024-03-24 20:32

本文主要是介绍某外企SQL Server面试题,希望对大家解决编程问题提供一定的参考价值,需要的开发者们随着小编来一起学习吧!

Question 1:Can you use a batch SQL or store procedure to calculating the Number of Days in a Month

  Answer 1:找出当月的天数
select datepart(dd,dateadd(dd,-1,dateadd(mm,1,cast(cast(year(getdate()) as varchar)+'-'+cast(month(getdate()) as varchar)+'-01' as datetime))))

  Question2:Can you use a SQL statement to calculating it!
How can I print "10 to 20" for books that sell for between $10 and $20,"unknown" for books whose price is null, and "other" for all other prices?

  Answer 2:
  select bookid,bookname,price=case when price is null then   'unknown'
       when  price between 10 and 20 then '10 to 20' else price end
from books

  Question3:Can you use a SQL statement to finding duplicate   values!
  
How can I find authors with the same last name?
You can use the table authors in datatabase pubs. I want to get the result as below:
Output:
au_lname                                 number_dups
---------------------------------------- -----------
Ringer                                   2
(1 row(s) affected)

  Answer 3
  select au_lname,number_dups=count(1) from authors group by au_lname

  Question4:Can you create a cross-tab report in my SQL Server!
  How can I get the report about sale quality for each store and each quarter and the total sale quality for each quarter at year 1993?

  You can use the table sales and stores in datatabase pubs.
Table Sales record all sale detail item for each store. Column store_id is the id of each store, ord_date is the order date of each sale item, and column qty is the sale qulity. Table stores record all store information.
I want to get the result look like as below:
Output:

stor_name                                Total       Qtr1        Qtr2        Qtr3        Qtr4       
---------------------------------------- ----------- ----------- ----------- ----------- -----------
Barnum's                                 50          0           50          0           0
Bookbeat                                 55          25          30          0           0
Doc-U-Mat: Quality Laundry and Books     85          0           85          0           0
Fricative Bookshop                       60          35          0           0           25
Total                                    250         60          165         0           25

Answer 4:用动态SQL实现

  Question5: The Fastest Way to Recompile All Stored Procedures
  I have a problem with a database running in SQL Server 6.5 (Service Pack 4). We moved the database (object transfer) from one machine to another last night, and an error (specific to a stored procedure) is cropping up. However, I can't tell which procedure is causing it. Permissions are granted in all of our stored procedures; is there a way from the isql utility to force all stored procedures to recompile?

  Tips: sp_recompile can recomplie a store procedure each time

  Answer 5:在执行存储过程时,使用 with recompile 选项强制编译新的计划;使用sp_recompile系统存储过程强制在下次运行时进行重新编译

  Question6: How can I add row numbers to my result set?
In database pubs, have a table titles , now I want the result shown as below,each row have a row number, how can you do that?
Result:

line-no     title_id
----------- --------
1           BU1032
2           BU1111
3           BU2075
4           BU7832
5           MC2222
6           MC3021
7           MC3026
8           PC1035
9           PC8888
10          PC9999
11          PS1372
12          PS2091
13          PS2106
14          PS3333
15          PS7777
16          TC3218
17          TC4203
18          TC7777
  Answer 6:
  --SQL 2005的写法
  select row_number() as line_no ,title_id from titles
  --SQL 2000的写法
  select line_no identity(int,1,1),title_id into #t from titles
  select * from #t
  drop table #t

Question 7: Can you tell me what the difference of two SQL statements at performance of execution?
  Statement 1:
  if NOT EXISTS ( select * from publishers where state = 'NY')
  begin

  SELECT 'Sales force needs to penetrate New York market'end

  else
  begin
  SELECT 'We have publishers in New York'
  end
  Statement 2:
  if EXISTS ( select * from publishers where state = 'NY')
  begin
  SELECT 'We have publishers in New York'
  end
  else
  begin
  SELECT 'Sales force needs to penetrate New York market'
  end
  Answer 7:不同点:执行时的事务数,处理时间,从客户端到服务器端传送的数据量大小

  Question8: How can I list all California authors regardless of whether they have written a book?
  In database pubs, have a table authors and titleauthor , table authors has a column state, and titleauhtor have books each author written.
CA behalf of california in table authors.
  Answer 8:
  select * from  authors where state='CA'

  Question9: How can I get a list of the stores that have bought both 'bussiness' and 'mod_cook' type books?
  In database pubs, use three table stores,sales and titles to implement this requestment.
Now I want to get the result as below:

stor_id stor_name                               
------- ----------------------------------------
...
7896    Fricative Bookshop
...
...
...
Answer 9:
select distinct a.stor_id, a.stor_name from stores a,sales b,titles c
where a.stor_id=b.stor_id and b.title_id=c.title_id and c.type='business' and
exists(select 1 from sales k,titles g where stor_id=b.stor_id
and k.title_id=g.title_id and g.type='mod_cook')  

Question10: How can I list non-contignous data?
  In database pubs, I create a table test using statement as below, and I insert several row as below

create table test
( id int primary key )
go

insert into test values (1 )
insert into test values (2 )
insert into test values (3 )
insert into test values (4 )
insert into test values (5 )
insert into test values (6 )
insert into test values (8 )
insert into test values (9 )
insert into test values (11)
insert into test values (12)
insert into test values (13)
insert into test values (14)
insert into test values (18)
insert into test values (19)
go

Now I want to list the result of the non-contignous row as below,how can I do it?
Missing after Missing before
------------- --------------
6             8
9             11
...

  Answer 10:
  select id from test t where not exists(select 1 from test where id=t.id+1)
or not exists(select 1 from test where id=t.id-1)

  Question11: How can I list all book with prices greather than the average price of books of the same type?
  In database pubs, have a table named titles , its column named price mean the price of the book, and another named type mean the type of books.
Now I want to get the result as below:

type         title                                                                            price                
------------ -------------------------------------------------------------------------------- ---------------------
business     The Busy Executive's Database Guide                                              19.9900
...
...
...
...
  Answer 11:
  select a.type,a.title,a.price from titles a,
(select type,price=avg(price) from titles group by type)b
where a.type=b.type and a.price>b.price

      试题点评:通览整个试题,我们不难发现,这份试题是针对SQL Server数据库人员的。而从难度分析上来看,这份试题也属于同类试题中比较难的了。之所以说它难,首先是限定时间的全英文试题;其次,尽管这份试题主要是考核开发能力,但却涉及到了算法的选择和性能的调优;最后,这份试题还夹进了SQL Server数据库的升级问题。因此,综上所述,我们估计这是一家从事程序外包工作的外企招聘后台开发或与后台开发相关的SQL Server高级程序员的试题。

Resource:http://www.z599.com/news/0604/2006425827214.htm

 

  

这篇关于某外企SQL Server面试题的文章就介绍到这儿,希望我们推荐的文章对编程师们有所帮助!



http://www.chinasem.cn/article/842696

相关文章

MySQL的JDBC编程详解

《MySQL的JDBC编程详解》:本文主要介绍MySQL的JDBC编程,具有很好的参考价值,希望对大家有所帮助,如有错误或未考虑完全的地方,望不吝赐教... 目录前言一、前置知识1. 引入依赖2. 认识 url二、JDBC 操作流程1. JDBC 的写操作2. JDBC 的读操作总结前言本文介绍了mysq

java.sql.SQLTransientConnectionException连接超时异常原因及解决方案

《java.sql.SQLTransientConnectionException连接超时异常原因及解决方案》:本文主要介绍java.sql.SQLTransientConnectionExcep... 目录一、引言二、异常信息分析三、可能的原因3.1 连接池配置不合理3.2 数据库负载过高3.3 连接泄漏

Linux下MySQL数据库定时备份脚本与Crontab配置教学

《Linux下MySQL数据库定时备份脚本与Crontab配置教学》在生产环境中,数据库是核心资产之一,定期备份数据库可以有效防止意外数据丢失,本文将分享一份MySQL定时备份脚本,并讲解如何通过cr... 目录备份脚本详解脚本功能说明授权与可执行权限使用 Crontab 定时执行编辑 Crontab添加定

MySQL中On duplicate key update的实现示例

《MySQL中Onduplicatekeyupdate的实现示例》ONDUPLICATEKEYUPDATE是一种MySQL的语法,它在插入新数据时,如果遇到唯一键冲突,则会执行更新操作,而不是抛... 目录1/ ON DUPLICATE KEY UPDATE的简介2/ ON DUPLICATE KEY UP

MySQL分库分表的实践示例

《MySQL分库分表的实践示例》MySQL分库分表适用于数据量大或并发压力高的场景,核心技术包括水平/垂直分片和分库,需应对分布式事务、跨库查询等挑战,通过中间件和解决方案实现,最佳实践为合理策略、备... 目录一、分库分表的触发条件1.1 数据量阈值1.2 并发压力二、分库分表的核心技术模块2.1 水平分

Python与MySQL实现数据库实时同步的详细步骤

《Python与MySQL实现数据库实时同步的详细步骤》在日常开发中,数据同步是一项常见的需求,本篇文章将使用Python和MySQL来实现数据库实时同步,我们将围绕数据变更捕获、数据处理和数据写入这... 目录前言摘要概述:数据同步方案1. 基本思路2. mysql Binlog 简介实现步骤与代码示例1

Python 基于http.server模块实现简单http服务的代码举例

《Python基于http.server模块实现简单http服务的代码举例》Pythonhttp.server模块通过继承BaseHTTPRequestHandler处理HTTP请求,使用Threa... 目录测试环境代码实现相关介绍模块简介类及相关函数简介参考链接测试环境win11专业版python

使用shardingsphere实现mysql数据库分片方式

《使用shardingsphere实现mysql数据库分片方式》本文介绍如何使用ShardingSphere-JDBC在SpringBoot中实现MySQL水平分库,涵盖分片策略、路由算法及零侵入配置... 目录一、ShardingSphere 简介1.1 对比1.2 核心概念1.3 Sharding-Sp

MySQL 表空却 ibd 文件过大的问题及解决方法

《MySQL表空却ibd文件过大的问题及解决方法》本文给大家介绍MySQL表空却ibd文件过大的问题及解决方法,本文给大家介绍的非常详细,对大家的学习或工作具有一定的参考借鉴价值,需要的朋友参考... 目录一、问题背景:表空却 “吃满” 磁盘的怪事二、问题复现:一步步编程还原异常场景1. 准备测试源表与数据

Mac电脑如何通过 IntelliJ IDEA 远程连接 MySQL

《Mac电脑如何通过IntelliJIDEA远程连接MySQL》本文详解Mac通过IntelliJIDEA远程连接MySQL的步骤,本文通过图文并茂的形式给大家介绍的非常详细,感兴趣的朋友跟... 目录MAC电脑通过 IntelliJ IDEA 远程连接 mysql 的详细教程一、前缀条件确认二、打开 ID