您当前的位置：首页 > 网络编程 > sqlserver教程

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

时间：2014-11-30 来源：互联网作者：佚名

预备考试脚本

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

列表A：

IF OBJECT_ID('Sales') > 0

DROP TABLE Sales

IF OBJECT_ID('Customers') > 0

DROP TABLE Customers

IF OBJECT_ID('Products') > 0

DROP TABLE Products

CREATE TABLE Customers

(

CustomerID INT IDENTITY PRIMARY KEY,

FirstName VARCHAR(50),

LastName VARCHAR(50),

City VARCHAR(50),

State CHAR(2),

Zip VARCHAR(10)

)

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')