快捷搜索:  as  test  1111  test aNd 8=8  test++aNd+8=8  as++aNd+8=8  as aNd 8=8

亿鼎博娱乐官网网址_集报网



预备考试脚本

在开始考试之前,我必要一个模式和一些数据来运行所要稽核的查询,列表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&#亿鼎博娱乐官网网址39;,'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(F亿鼎博娱乐官网网址irstName,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,SalePr亿鼎博娱乐官网网址ice,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,Custo亿鼎博娱乐官网网址merID,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

一旦我载入了这些数据,我就可以开始测试了(提示:我会让应聘者将他们编写的SELECT/UPDATE/INSERT/DELETE 语句存储在一个文本文件中,这样我今后可以随时阅览)。

测试

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

SELECT

c.FirstName,c.LastName,p.ProductName,s.SalePrice

FROM

Saless

INNERJOINCustomerscONs.CustomerID=c.CustomerID

INNERJOINProductspONs.ProductID=p.ProductID

WHERE

s.SaleDate>='10/1/2005'AND

s.SaleDate

测试项目#2:返回没有购买产品并且位于客户表格上的人的姓名及其客户ID,谜底拜见列表C:

SELECT

c.CustomerID,c.FirstName,c.LastName

FROM

Saless

RIGHTOUTERJOINCustomerscONs.CustomerID=c.CustomerID

WHERE

s.CustomerIDISNULL

测试项目#3:返回客户姓名、贩卖价格、建议售价、建议售价和实际价格的差额,该差额必需是正数,谜底见列表D:

SELECT

c.FirstName,c.LastName,s.SalePrice,p.RecommendedPrice,

ABS(s.SalePrice-p.RecommendedPrice)

ASAbsoluteSalePriceDifference

FROM

Saless

INNERJOINCustomerscONs.CustomerID=c.CustomerID

INNERJOINProductspONs.ProductID=p.ProductID

测试项目#4:根据产品种别谋略匀称价格,谜底见列表E:

SELECT

p.Category,AVG(s.SalePrice)ASAverageSalePrice

FROM

Saless

INNERJOINProductspONs.ProductID=p.ProductID

GROUPBYp.Category

免责声明:以上内容源自网络,版权归原作者所有,如有侵犯您的原创版权请告知,我们将尽快删除相关内容。

您可能还会对下面的文章感兴趣: