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

时间:2011-12-16 16:10:02  来源:第二电脑网  作者:第二电脑网

  第二电脑网导读:免责声明即使不是全部的话,这些TSQL问题中的大部分都可以使用不同的方法解决,我所提供的答案是我书写查询的方法,也是我所希望见到的答案,但是,既然答案不是唯一的,那么一位精通SQL编程的阅卷人是至关重要的,这样不同的答案才能被合理地考察和评分。在这个测试中,没有关于指针、存储过程或触发器的问题,因为我所考察的是申请者实现复杂查询和数据修改语句...
  正文:

在上一篇文章中,我列出了在面试SQL Server数据库开发者时会提问的一些问题,如果我对应聘者的口头回答部分感到满意,我会让他们参加TSQL编程能力的测试,没有比动手操作数据库更有效的方法了,我认为TSQL测试是考察应聘人员的好办法。

一些免责声明

即使不是全部的话,这些TSQL问题中的大部分都可以使用不同的方法解决,我所提供的答案是我书写查询的方法,也是我所希望见到的答案,但是,既然答案不是唯一的,那么一位精通SQL编程的阅卷人是至关重要的,这样不同的答案才能被合理地考察和评分。

在这个测试中,没有关于指针、存储过程或触发器的问题,因为我所考察的是申请者实现复杂查询和数据修改语句的能力,如果应聘者能够在测试中表现良好,那么我相信他们在编写存储过程和触发器方面是不会遇到很多麻烦的。

预备考试脚本

在开始考试之前,我需要一个模式和一些数据来运行所要考核的查询,列表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 语句存储在一个文本文件中,这样我以后可以随时阅览)。

使用TSQL操作面试SQL Server开发人员》由第二电脑网原创提供,转载请注明:http://www.002pc.com/master/College/Database/SQLServer/2009-01-07/8336.html


关键字:

关于《使用TSQL操作面试SQL Server开发人员》文章的评论

相关文章

    无相关信息
站内搜索: 高级搜索

热门搜索:关键字 键盘  Windows PHP CPU style 系统 Google td tr IP function