用T-SQL操作面试SQL Server开发人员(1)

IF OBJECT_ID('Sales') > 0 

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



SELECT 

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



UPDATE s 
 



预备考试脚本
在开始考试之前，我需要一个模式和一些数据来运行所要考核的查询，列表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

一旦我载入了这些数据，我就可以开始测试了(提示：我会让应聘者将他们编写的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 
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%，假如应聘者的表现高于这个平均分，那么我就认为他或她是一位优秀的T-SQL程序员，假如应聘者获得了90%以上的得分，那么他或她就是一位非常优异的程序员。
假如您对我的答案有任何问题或者想发表评论，请在文章的讨论区进行讨论。
	
	

	
	

 上一篇返回首页 下一篇
			
	

声明：   此文观点不代表本站立场；转载务必保留本文链接；版权疑问请联系我们。
	
	

				

					
别人在看
					
									
				

			

	


		

			
hiberfil.sys文件可以删除吗？了解该文件并手把手教你删除C盘的hiberfil.sys文件
			
		
 

		

			
Window 10和 Windows 11哪个好？答案是：看你自己的需求
			
		
 

		

			
盗版软件成公司里的“隐形炸弹”？老板们的“法务噩梦” 有救了！
			
		
 

		

			
帝国CMS7.5编辑器上传图片取消宽高的三种方法
			
		
 

		

			
帝国cms如何自动生成缩略图的实现方法
			
		
 

		

			
Windows 12即将到来，将彻底改变人机交互
			
		
 

		

			
帝国CMS 7.5忘记登陆账号密码怎么办？可以phpmyadmin中重置管理员密码
			
		
 

		

			
帝国CMS 7.5 后台编辑器换行，修改回车键br换行为p标签
			
		
 

		

			
Windows 11 版本与 Windows 10比较，新功能一览
			
		
 

		

			
Windows 11激活产品密钥收集及专业版激活方法
			
		
 	
			
		
		
	
			
		
	
		

	
			

	

		

			

				
				
				

					在上方输入关键词后，回车键 开始搜索。Esc键 取消该搜索窗口。