使用T-SQL操作面试SQL Server开发人员

预备考试脚本

在开始考试之前，需要一个模式和一些数据来运行所要考核的查询，列表A创建了所需的这些数据：

列表A：

IF OBJECT_ID('Sales') > 0

       DROP TABLE Sales

 GO

 IF OBJECT_ID('Customers') > 0

       DROP TABLE Customers

 GO

 IF OBJECT_ID('Products') > 0

       DROP TABLE Products

 GO

 CREATE TABLE Customers

 (

 CustomerID INT IDENTITY PRIMARY KEY,

 FirstName VARCHAR(50),

 LastName VARCHAR(50),

 City VARCHAR(50),

 State CHAR(2),

 Zip VARCHAR(10)

 )

 GO

 CREATE TABLE Products

 (ProductID TINYINT IDENTITY PRIMARY KEY,

ProductName VARCHAR(20),RecommendedPrice

MONEY,Category VARCHAR(10)

 )GO CREATE TABLE Sales(SaleID INT IDENTITY

PRIMARY KEY,ProductID TINYINT NOT NULL

REFERENCES Products(ProductID),CustomerID INT

NOT NULL REFERENCES Customers(CustomerID),SalePrice

MONEY NOT NULL,SaleDate SMALLDATETIME NOT NULL)GO

 INSERT INTO Products(ProductName, RecommendedPrice, Category)

VALUES('DVD',105,'LivingRoom')INSERT INTO

Products(ProductName, RecommendedPrice, Category)

VALUES('Microwave',98,'Kitchen')INSERT

INTO Products(ProductName, RecommendedPrice,

Category)VALUES('Monitor',200,'Office')INSERT

INTO Products(ProductName, RecommendedPrice, Category)

VALUES('Speakers',85,'Office')INSERT INTO

Products(ProductName, RecommendedPrice, Category)

VALUES('Refrigerator',900,'Kitchen')INSERT INTO

Products(ProductName, RecommendedPrice, Category)

VALUES('VCR',165,'LivingRoom')

 INSERT INTO Products(ProductName, RecommendedPrice, Category)

 VALUES('CoffeePot',35,'Kitchen')GO

 INSERT INTO Customers(FirstName, LastName, City, State, Zip)

VALUES('John','Miller','Asbury','NY','23433') INSERT INTO

Customers(FirstName, LastName, City, State, Zip)

VALUES('Fred','Hammill','Basham','AK','85675')

 INSERT INTO Customers(FirstName, LastName, City, State, Zip)

VALUES('Stan','Mellish','Callahan','WY','38556')

 INSERT INTO Customers(FirstName, LastName, City, State, Zip)

VALUES('Adrian','Caparzo','Denver','CO','12377')

 INSERT INTO Customers(FirstName, LastName, City, State, Zip)

VALUES('Mike','Horvath','Easton','IN','47130')

 INSERT INTO Customers(FirstName, LastName, City, State, Zip)

VALUES('Irwin','Wade','Frankfurt','KY','45902')

 INSERT INTO Customers(FirstName, LastName, City, State, Zip)

VALUES('George','Marshall','Gallipoli','ND','34908')

 INSERT INTO Customers(FirstName, LastName, City, State, Zip)

VALUES('Frank','Costello','Honolulu','HI','23905')

 INSERT INTO Customers(FirstName, LastName, City, State, Zip)

VALUES('Billy','Costigan','Immice','SC','75389')

 INSERT INTO Customers(FirstName, LastName, City, State, Zip)

VALUES('Shelly','Sipes','Lights','AZ','35263')

 INSERT INTO Customers(FirstName, LastName, City, State, Zip)

VALUES('Chirsty','Melton','Spade','CA','97505')

 INSERT INTO Customers(FirstName, LastName, City, State, Zip)

VALUES('Amanda','Owens','Flask','CN','50386')

 INSERT INTO Customers(FirstName, LastName, City, State, Zip)

VALUES('Brittany','Smits','Bourbon','KY','24207')

 INSERT INTO Customers(FirstName, LastName, City, State, Zip)

VALUES('Kristy','Bryant','Tarp','FL','58960')

 INSERT INTO Customers(FirstName, LastName, City, State, Zip)

VALUES('Kelly','Street','TableTop','ID','57732')

 INSERT INTO Customers(FirstName, LastName, City, State, Zip)

VALUES('Tricia','Hill','Camera','ME','46738')

 INSERT INTO Customers(FirstName, LastName, City, State, Zip)

VALUES('Holly','Raines','Compact','MS','35735')

 INSERT INTO Customers(FirstName, LastName, City, State, Zip)

VALUES('Natalie','Woods','Woods','IN','87219')

 INSERT INTO Customers(FirstName, LastName, City, State, Zip)

VALUES('Wendy','Hilton','Action','KY','47093')

 GO

 INSERT INTO Sales(ProductID, CustomerID, SalePrice, SaleDate)

VALUES(1,1,130,'2/6/2005')

 INSERT INTO Sales(ProductID, CustomerID, SalePrice, SaleDate)

VALUES(2,2,97,'1/7/2005')

 INSERT INTO Sales(ProductID, CustomerID, SalePrice, SaleDate)

VALUES(3,3,200,'8/8/2005')

 INSERT INTO Sales(ProductID, CustomerID, SalePrice, SaleDate)

VALUES(4,4,80,'4/9/2005')

 INSERT INTO Sales(ProductID, CustomerID, SalePrice, SaleDate)

VALUES(5,5,899,'10/10/2005')

 INSERT INTO Sales(ProductID, CustomerID, SalePrice, SaleDate)

VALUES(6,6,150,'10/11/2005')

 INSERT INTO Sales(ProductID, CustomerID, SalePrice, SaleDate)

VALUES(3,7,209,'12/12/2005')

 INSERT INTO Sales(ProductID, CustomerID, SalePrice, SaleDate)

VALUES(4,8,90,'5/13/2005')

 INSERT INTO Sales(ProductID, CustomerID, SalePrice, SaleDate)

VALUES(6,9,130,'6/14/2005')

 INSERT INTO Sales(ProductID, CustomerID, SalePrice, SaleDate)

VALUES(2,14,85,'6/19/2005')

 INSERT INTO Sales(ProductID, CustomerID, SalePrice, SaleDate)

VALUES(3,15,240,'9/20/2005')

 INSERT INTO Sales(ProductID, CustomerID, SalePrice, SaleDate)

VALUES(1,16,99,'7/21/2005')

 INSERT INTO Sales(ProductID, CustomerID, SalePrice, SaleDate)

VALUES(1,17,87,'3/22/2005')

 INSERT INTO Sales(ProductID, CustomerID, SalePrice, SaleDate)

VALUES(2,18,99,'1/23/2005')

 INSERT INTO Sales(ProductID, CustomerID, SalePrice, SaleDate)

VALUES(6,19,150,'3/24/2005')

 INSERT INTO Sales(ProductID, CustomerID, SalePrice, SaleDate)

VALUES(5,5,900,'3/10/2005')

 INSERT INTO Sales(ProductID, CustomerID, SalePrice, SaleDate)

VALUES(4,6,86,'8/11/2005')

 INSERT INTO Sales(ProductID, CustomerID, SalePrice, SaleDate)

VALUES(2,7,88,'8/12/2005')

 INSERT INTO Sales(ProductID, CustomerID, SalePrice, SaleDate)

VALUES(3,8,198,'12/13/2005')

 INSERT INTO Sales(ProductID, CustomerID, SalePrice, SaleDate)

VALUES(1,9,150,'5/14/2005')

 INSERT INTO Sales(ProductID, CustomerID, SalePrice, SaleDate)

VALUES(6,14,99,'7/19/2005')

 INSERT INTO Sales(ProductID, CustomerID, SalePrice, SaleDate)

VALUES(6,15,104,'9/20/2005')

 INSERT INTO Sales(ProductID, CustomerID, SalePrice, SaleDate)

VALUES(3,16,270,'2/21/2005')

 INSERT INTO Sales(ProductID, CustomerID, SalePrice, SaleDate)

VALUES(4,17,90,'7/22/2005')

 INSERT INTO Sales(ProductID, CustomerID, SalePrice, SaleDate)

VALUES(1,1,130,'3/6/2005')

 INSERT INTO Sales(ProductID, CustomerID, SalePrice, SaleDate)

VALUES(2,2,102,'4/7/2005')

 INSERT INTO Sales(ProductID, CustomerID, SalePrice, SaleDate)

VALUES(1,3,114,'11/8/2005')

 INSERT INTO Sales(ProductID, CustomerID, SalePrice, SaleDate)

VALUES(5,4,1000,'5/9/2005')

 INSERT INTO Sales(ProductID, CustomerID, SalePrice, SaleDate)

VALUES(5,5,1100,'10/10/2005')

 INSERT INTO Sales(ProductID, CustomerID, SalePrice, SaleDate)

VALUES(3,6,285,'6/11/2005')

 INSERT INTO Sales(ProductID, CustomerID, SalePrice, SaleDate)

VALUES(2,7,87,'10/12/2005')

 INSERT INTO Sales(ProductID, CustomerID, SalePrice, SaleDate)

VALUES(3,8,300,'7/13/2005')

 GO

一旦载入了这些数据，就可以开始测试了。

测试

测试项目#1：返回在2005年10月售出的所有产品的名称、价格和客户姓名，答案见列表B：

SELECT

c.FirstName, c.LastName, p.ProductName, s.SalePrice

FROM

Sales s

INNER JOIN Customers c ON s.CustomerID = c.CustomerID

INNER JOIN Products p ON s.ProductID = p.ProductID

WHERE

s.SaleDate >= '10/1/2005' AND

s.SaleDate < '11/1/2005'

测试项目#2：返回没有购买产品并且位于客户表格上的人的姓名及其客户ID，答案参见列表C:

SELECT

c.CustomerID, c.FirstName, c.LastName

FROM

Sales s

RIGHT OUTER JOIN Customers c ON s.CustomerID = c.CustomerID

WHERE

s.CustomerID IS NULL

测试项目#3：返回客户姓名、销售价格、建议售价、建议售价和实际价格的差额，该差额必需是正数，答案见列表D：

SELECT

c.FirstName, c.LastName, s.SalePrice, p.RecommendedPrice,

ABS(s.SalePrice - p.RecommendedPrice)

AS AbsoluteSalePriceDifference

FROM

Sales s

INNER JOIN Customers c ON s.CustomerID = c.CustomerID

INNER JOIN Products p ON s.ProductID = p.ProductID

测试项目#4：根据产品类别计算平均价格，答案见列表E：

SELECT

p.Category, AVG(s.SalePrice) AS AverageSalePrice

FROM

Sales s

INNER JOIN Products p ON s.ProductID = p.ProductID

GROUP BY p.Category

测试项目#5：将以下的客户和销售信息加入到数据库中：

FirstName: Chris

 

LastName: Kringle

 

City: Henryville

 

State: IN

 

Zip: 47126

 

ProductID: 3

 

SalePrice: 205

 

SaleDate: 12/31/2005

答案见列表F：

INSERT INTO Customers(FirstName, LastName, City, State, Zip)

VALUES('Chris', 'Kringle', 'Henryville', 'IN', '47126')

INSERT INTO Sales(CustomerID, ProductID, SalePrice, SaleDate)

VALUES(SCOPE_IDENTITY(), 3, 205, '12/31/2005')

测试项目#6：从数据库中删除来自缅因洲(‘ME’)的客户，答案见列表G：

DELETE s

FROM

Sales s

JOIN Customers c ON s.CustomerID = c.CustomerID

WHERE

c.State = 'ME'

DELETE c

FROM 

Customers c

WHERE

c.State = 'ME'

测试项目#7：返回客户购买了两个或多个产品的平均售价和产品类别，答案见列表H：

SELECT

p.Category, AVG(s.SalePrice)

FROM

Sales s

INNER JOIN

(

SELECT s.CustomerID

FROM 

Sales s

GROUP BY s.CustomerID

HAVING COUNT(CustomerID) >= 2

) x ON s.CustomerID = x.CustomerID

INNER JOIN Products p ON s.ProductID = p.ProductID

GROUP BY p.Category

测试项目#8：将销售在2005年6月10日到6月20日之间的产品的销售价格升级为建议售价，答案见列表I：

UPDATE s

SET      SalePrice = p.RecommendedPrice

FROM

Sales s

INNER JOIN Products p ON s.ProductID = s.ProductID

WHERE

SaleDate >= '6/10/2005' AND

SaleDate < '6/21/2005'

测试项目#9：根据产品种类计算建议售价超过实际售价10元及以上的销售数量，答案见列表J：

SELECT

p.Category, COUNT(*) AS NumberOfSales

FROM

Sales s

INNER JOIN Products p ON s.ProductID = p.ProductID

GROUP BY p.Category

HAVING     

AVG(p.RecommendedPrice) >= AVG(s.SalePrice)+10

测试项目#10：不使用叠代构建，返回所由销售产品的销售日期，并按照该日期升序排列，答案见列表K：

SELECT

s.SaleDate,

s.SalePrice,

(

SELECT

SUM(SalePrice)

FROM

Sales s2

WHERE

s2.SaleDate <= s.SaleDate

) AS RunningTotal

FROM

Sales s

ORDER BY

s.SaleDate ASC

评分

曾经使用类似的考题去考察很多应聘SQL Server数据库开发职位的人，但是迄今为止，只有2个人可以正确地回答出所有的问题。

平均分大约为50－60％，如果应聘者的表现高于这个平均分，那么我就认为他或她是一位优秀的TSQL程序员，如果应聘者获得了90％以上的得分，那么他或她就是一位非常优异的程序员。

如果您对我的答案有任何问题或者想发表评论，请在文章的讨论区发贴进行讨论。