使用TSQL操作面試SQL Server開發(fā)人員

2024-08-31 01:05:34

字體：大中小

供稿：網(wǎng)友

在上一篇文章中，我列出了在面試SQL Server數(shù)據(jù)庫開發(fā)者時會提問的一些問題，如果我對應(yīng)聘者的口頭回答部分感到滿意，我會讓他們參加TSQL編程能力的測試，沒有比動手操作數(shù)據(jù)庫更有效的方法了，我認(rèn)為TSQL測試是考察應(yīng)聘人員的好辦法。

一些免責(zé)聲明

即使不是全部的話，這些TSQL問題中的大部分都可以使用不同的方法解決，我所提供的答案是我書寫查詢的方法，也是我所希望見到的答案，但是，既然答案不是唯一的，那么一位精通SQL編程的閱卷人是至關(guān)重要的，這樣不同的答案才能被合理地考察和評分。

在這個測試中，沒有關(guān)于指針、存儲過程或觸發(fā)器的問題，因?yàn)槲宜疾斓氖巧暾堈邔?shí)現(xiàn)復(fù)雜查詢和數(shù)據(jù)修改語句的能力，如果應(yīng)聘者能夠在測試中表現(xiàn)良好，那么我相信他們在編寫存儲過程和觸發(fā)器方面是不會遇到很多麻煩的。

預(yù)備考試腳本

在開始考試之前，我需要一個模式和一些數(shù)據(jù)來運(yùn)行所要考核的查詢，列表A創(chuàng)建了所需的這些數(shù)據(jù)：

列表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