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

核心提示：预备考试脚本在开始考试之前，我需要一个模式和一些数据来运行所要考核的查询，用T-SQL操作面试SQL Server开发人员，列表A创建了所需的这些数据：列表A：IFOBJECT_ID('Sales')>0DROPTABLESalesGOIFOBJECT_ID('Customers'

预备考试脚本

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

列表A：

　　IFOBJECT_ID('Sales')>0
　　DROPTABLESales
　　GO
　　IFOBJECT_ID('Customers')>0
　　DROPTABLECustomers
　　GO
　　IFOBJECT_ID('Products')>0
　　DROPTABLEProducts
　　GO
　　CREATETABLECustomers
　　(
　　CustomerIDINTIDENTITYPRIMARYKEY,
　　FirstNameVARCHAR(50),
　　LastNameVARCHAR(50),
　　CityVARCHAR(50),
　　StateCHAR(2),
　　ZipVARCHAR(10)
　　)
　　GO
　　CREATETABLEProducts
　　(ProductIDTINYINTIDENTITYPRIMARYKEY,
　　ProductNameVARCHAR(20),RecommendedPrice
　　MONEY,CategoryVARCHAR(10)
　　)GOCREATETABLESales(SaleIDINTIDENTITY
　　PRIMARYKEY,ProductIDTINYINTNOTNULL
　　REFERENCESProducts(ProductID),CustomerIDINT
　　NOTNULLREFERENCESCustomers(CustomerID),SalePrice
　　MONEYNOTNULL,SaleDateSMALLDATETIMENOTNULL)GO
　　INSERTINTOProducts(ProductName,RecommendedPrice,Category)
　　VALUES('DVD',105,'LivingRoom')INSERTINTO
　　Products(ProductName,RecommendedPrice,Category)
　　VALUES('Microwave',98,'Kitchen')INSERT
　　INTOProducts(ProductName,RecommendedPrice,
　　Category)VALUES('Monitor',200,'Office')INSERT
　　INTOProducts(ProductName,RecommendedPrice,Category)
　　VALUES('Speakers',85,'Office')INSERTINTO
　　Products(ProductName,RecommendedPrice,Category)
　　VALUES('Refrigerator',900,'Kitchen')INSERTINTO
　　Products(ProductName,RecommendedPrice,Category)
　　VALUES('VCR',165,'LivingRoom')
　　INSERTINTOProducts(ProductName,RecommendedPrice,Category)
　　VALUES('CoffeePot',35,'Kitchen')GO
　　INSERTINTOCustomers(FirstName,LastName,City,State,Zip)
　　VALUES('John','Miller','Asbury','NY','23433')INSERTINTO
　　Customers(FirstName,LastName,City,State,Zip)
　　VALUES('Fred','Hammill','Basham','AK','85675')
　　INSERTINTOCustomers(FirstName,LastName,City,State,Zip)
　　VALUES('Stan','Mellish','Callahan','WY','38556')
　　INSERTINTOCustomers(FirstName,LastName,City,State,Zip)
　　VALUES('Adrian','Caparzo','Denver','CO','12377')
　　INSERTINTOCustomers(FirstName,LastName,City,State,Zip)
　　VALUES('Mike','Horvath','Easton','IN','47130')
　　INSERTINTOCustomers(FirstName,LastName,City,State,Zip)
　　VALUES('Irwin','Wade','Frankfurt','KY','45902')
　　INSERTINTOCustomers(FirstName,LastName,City,State,Zip)
　　VALUES('George','Marshall','Gallipoli','ND','34908')
　　INSERTINTOCustomers(FirstName,LastName,City,State,Zip)
　　VALUES('Frank','Costello','Honolulu','HI','23905')
　　INSERTINTOCustomers(FirstName,LastName,City,State,Zip)
　　VALUES('Billy','Costigan','Immice','SC','75389')
　　INSERTINTOCustomers(FirstName,LastName,City,State,Zip)
　　VALUES('Shelly','Sipes','Lights','AZ','35263')
　　INSERTINTOCustomers(FirstName,LastName,City,State,Zip)
　　VALUES('Chirsty','Melton','Spade','CA','97505')
　　INSERTINTOCustomers(FirstName,LastName,City,State,Zip)
　　VALUES('Amanda','Owens','Flask','CN','50386')
　　INSERTINTOCustomers(FirstName,LastName,City,State,Zip)
　　VALUES('Brittany','Smits','Bourbon','KY','24207')
　　INSERTINTOCustomers(FirstName,LastName,City,State,Zip)
　　VALUES('Kristy','Bryant','Tarp','FL','58960')
　　INSERTINTOCustomers(FirstName,LastName,City,State,Zip)
　　VALUES('Kelly','Street','TableTop','ID','57732')
　　INSERTINTOCustomers(FirstName,LastName,City,State,Zip)
　　VALUES('Tricia','Hill','Camera','ME','46738')
　　INSERTINTOCustomers(FirstName,LastName,City,State,Zip)
　　VALUES('Holly','Raines','Compact','MS','35735')
　　INSERTINTOCustomers(FirstName,LastName,City,State,Zip)
　　VALUES('Natalie','Woods','Woods','IN','87219')
　　INSERTINTOCustomers(FirstName,LastName,City,State,Zip)
　　VALUES('Wendy','Hilton','Action','KY','47093')
　　GO
　　INSERTINTOSales(ProductID,CustomerID,SalePrice,SaleDate)
　　VALUES(1,1,130,'2/6/2005')
　　INSERTINTOSales(ProductID,CustomerID,SalePrice,SaleDate)
　　VALUES(2,2,97,'1/7/2005')
　　INSERTINTOSales(ProductID,CustomerID,SalePrice,SaleDate)
　　VALUES(3,3,200,'8/8/2005')
　　INSERTINTOSales(ProductID,CustomerID,SalePrice,SaleDate)
　　VALUES(4,4,80,'4/9/2005')
　　INSERTINTOSales(ProductID,CustomerID,SalePrice,SaleDate)
　　VALUES(5,5,899,'10/10/2005')
　　INSERTINTOSales(ProductID,CustomerID,SalePrice,SaleDate)
　　VALUES(6,6,150,'10/11/2005')
　　INSERTINTOSales(ProductID,CustomerID,SalePrice,SaleDate)
　　VALUES(3,7,209,'12/12/2005')
　　INSERTINTOSales(ProductID,CustomerID,SalePrice,SaleDate)
　　VALUES(4,8,90,'5/13/2005')
　　INSERTINTOSales(ProductID,CustomerID,SalePrice,SaleDate)
　　VALUES(6,9,130,'6/14/2005')
　　INSERTINTOSales(ProductID,CustomerID,SalePrice,SaleDate)
　　VALUES(2,14,85,'6/19/2005')
　　INSERTINTOSales(ProductID,CustomerID,SalePrice,SaleDate)
　　VALUES(3,15,240,'9/20/2005')
　　INSERTINTOSales(ProductID,CustomerID,SalePrice,SaleDate)
　　VALUES(1,16,99,'7/21/2005')
　　INSERTINTOSales(ProductID,CustomerID,SalePrice,SaleDate)
　　VALUES(1,17,87,'3/22/2005')
　　INSERTINTOSales(ProductID,CustomerID,SalePrice,SaleDate)
　　VALUES(2,18,99,'1/23/2005')
　　INSERTINTOSales(ProductID,CustomerID,SalePrice,SaleDate)
　　VALUES(6,19,150,'3/24/2005')
　　INSERTINTOSales(ProductID,CustomerID,SalePrice,SaleDate)
　　VALUES(5,5,900,'3/10/2005')
　　INSERTINTOSales(ProductID,CustomerID,SalePrice,SaleDate)
　　VALUES(4,6,86,'8/11/2005')
　　INSERTINTOSales(ProductID,CustomerID,SalePrice,SaleDate)
　　VALUES(2,7,88,'8/12/2005')
　　INSERTINTOSales(ProductID,CustomerID,SalePrice,SaleDate)
　　VALUES(3,8,198,'12/13/2005')
　　INSERTINTOSales(ProductID,CustomerID,SalePrice,SaleDate)
　　VALUES(1,9,150,'5/14/2005')
　　INSERTINTOSales(ProductID,CustomerID,SalePrice,SaleDate)
　　VALUES(6,14,99,'7/19/2005')
　　INSERTINTOSales(ProductID,CustomerID,SalePrice,SaleDate)
　　VALUES(6,15,104,'9/20/2005')
　　INSERTINTOSales(ProductID,CustomerID,SalePrice,SaleDate)
　　VALUES(3,16,270,'2/21/2005')
　　INSERTINTOSales(ProductID,CustomerID,SalePrice,SaleDate)
　　VALUES(4,17,90,'7/22/2005')
　　INSERTINTOSales(ProductID,CustomerID,SalePrice,SaleDate)
　　VALUES(1,1,130,'3/6/2005')
　　INSERTINTOSales(ProductID,CustomerID,SalePrice,SaleDate)
　　VALUES(2,2,102,'4/7/2005')
　　INSERTINTOSales(ProductID,CustomerID,SalePrice,SaleDate)
　　VALUES(1,3,114,'11/8/2005')
　　INSERTINTOSales(ProductID,CustomerID,SalePrice,SaleDate)
　　VALUES(5,4,1000,'5/9/2005')
　　INSERTINTOSales(ProductID,CustomerID,SalePrice,SaleDate)
　　VALUES(5,5,1100,'10/10/2005')
　　INSERTINTOSales(ProductID,CustomerID,SalePrice,SaleDate)
　　VALUES(3,6,285,'6/11/2005')
　　INSERTINTOSales(ProductID,CustomerID,SalePrice,SaleDate)
　　VALUES(2,7,87,'10/12/2005')
　　INSERTINTOSales(ProductID,CustomerID,SalePrice,SaleDate)
　　VALUES(3,8,300,'7/13/2005')
　　GO