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

 
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

一旦我载入了这些数据，我就可以开始测试了（提示：我会让应聘者将他们编写的SELECT/UPDATE/INSERT/DELETE 语句存储在一个文本文件中，这样我以后可以随时阅览）。

测试

测试项目#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
共4页: 上一页 [1] [2] 3 [4] 下一页

评论加载中…