SQL 认证考试 简单英文题库.docx

SQL 认证考试 简单英文题库.docx

《SQL 认证考试 简单英文题库.docx》由会员分享，可在线阅读，更多相关《SQL 认证考试 简单英文题库.docx（116页珍藏版）》请在冰豆网上搜索。

SQL认证考试简单英文题库

1.YouhaveausernamedJohn.HehasSELECTaccesstotheSalesschema.Youneedtoeliminate

John'sSELECTaccessrightsfromtheSales.SalesOrdertablewithoutaffectinghisotherpermissions.

WhichTransact-SQLstatementshouldyouuse?

A.DROPUSERJohn;

B.DENYSELECTONSales.SalesOrderTOJohn;

C.GRANTDELETEONSales.SalesOrderTOJohn;

D.REVOKESELECTONSales.SalesOrderFROMJohn;

Answer:

B

2.Youneedtocreateacolumnthatallowsyoutocreateauniqueconstraint.

Whichtwocolumndefinitionsshouldyouchoose?

（Eachcorrectanswerpresentsacompletesolution.

Choosetwo.）

A.nvarchar（100）NULL

B.nvarchar（max）NOTNULL

C.nvarchar（100）NOTNULL

D.nvarchar（100）SPARSENULL

Answer:

AC

3.YoumanageaSQLServer2008databasethatislocatedatyourcompany'scorporateheadquarters.

Thedatabasecontainsatablenameddbo.Sales.Youneedtocreatedifferentviewsofthedbo.Sales

tablethatwillbeusedbyeachregiontoinsert,update,anddeleterows.Eachregionalofficemustonlybe

abletoinsert,update,anddeleterowsfortheirrespectiveregion.

WhichviewshouldyoucreateforRegion1?

A.CREATEVIEWdbo.Region1Sales

AS

SELECTSalesID,OrderQty,SalespersonID,RegionID

FROMdbo.Sales

WHERERegionID=1;

B.CREATEVIEWdbo.Region1Sales

AS

SELECTSalesID,OrderQty,SalespersonID,RegionID

FROMdbo.Sales

WHERERegionID=1

WITHCHECKOPTION;

C.CREATEVIEWdbo.Region1Sales

WITHSCHEMABINDING

Thesafer,easierwaytohelpyoupassanyITexams.

3/80

AS

SELECTSalesID,OrderQty,SalespersonID,RegionID

FROMdbo.Sales

WHERERegionID=1;

D.CREATEVIEWdbo.Region1Sales

WITHVIEW_METADATA

AS

SELECTSalesID,OrderQty,SalespersonID,RegionID

FROMdbo.Sales

WHERERegionID=1;

Answer:

B

4.YouadministeraSQLServer2008databasethatcontainsatablenamedbo.Sales,whichcontainsthe

followingtabledefinition:

CREATETABLE[dbo].[Sales]（

[SalesID][int]IDENTITY（1,1）NOTNULLPRIMARYKEYCLUSTERED,

[OrderDate][datetime]NOTNULL,

[CustomerID][int]NOTNULL,

[SalesPersonID][int]NULL,

[CommentDate][date]NULL）;

Thistablecontainsmillionsoforders.Yourunthefollowingquerytodeterminewhensalespersons

commentinthedbo.Salestable:

SELECTSalesID,CustomerID,SalesPersonID,CommentDate

FROMdbo.Sales

WHERECommentDateISNOTNULL

ANDSalesPersonIDISNOTNULL;

Youdiscoverthatthisqueryrunsslow.Afterexaminingthedata,youfindonly1%ofrowshavecomment

datesandtheSalesPersonIDisnullon10%oftherows.Youneedtocreateanindextooptimizethe

query.Theindexmustconservediskspacewhileoptimizingyourquery.

Whichindexshouldyoucreate?

A.CREATENONCLUSTEREDINDEXidx1

ONdbo.Sales（CustomerID）

INCLUDE（CommentDate,SalesPersonID）;

B.CREATENONCLUSTEREDINDEXidx1

ONdbo.Sales（SalesPersonID）

Thesafer,easierwaytohelpyoupassanyITexams.

4/80

INCLUDE（CommentDate,CustomerID）;

C.CREATENONCLUSTEREDINDEXidx1

ONdbo.Sales（CustomerID）

INCLUDE（CommentDate）

WHERESalesPersonIDISNOTNULL;

D.CREATENONCLUSTEREDINDEXidx1

ONdbo.Sales（CommentDate,SalesPersonID）

INCLUDE（CustomerID）

WHERECommentDateISNOTNULL;

Answer:

D

5.Yourdatabaseis5GBandcontainsatablenamedSalesHistory.Salesinformationisfrequently

insertedandupdated.

Youdiscoverthatexcessivepagesplittingisoccurring.

YouneedtoreducetheoccurrenceofpagesplittingintheSalesHistorytable.

Whichcodesegmentshouldyouuse?

.

A.ALTERDATABASESales

MODIFYFILE

（NAME=Salesdat3,

SIZE=10GB）;

B.ALTERINDEXALLONSales.SalesHistory

REBUILDWITH（FILLFACTOR=60）;

C.EXECsys.sp_configure'fillfactor（%）','60';

D.UPDATESTATISTICSSales.SalesHistory（Products）

WITHFULLSCAN,NORECOMPUTE;

Answer:

B

6.Youhaveatablenameddbo.Customers.ThetablewascreatedbyusingthefollowingTransact-SQL

statement:

CREATETABLEdbo.Customers

（

CustomerIDintIDENTITY（1,1）PRIMARYKEYCLUSTERED,

AccountNumbernvarchar（25）NOTNULL,

FirstNamenvarchar（50）NOTNULL,

LastNamenvarchar（50）NOTNULL,

AddressLine1nvarchar（255）NOTNULL,

Thesafer,easierwaytohelpyoupassanyITexams.

5/80

AddressLine2nvarchar（255）NOTNULL,

Citynvarchar（50）NOTNULL,

StateProvincenvarchar（50）NOTNULL,

Countrynvarchar（50）NOTNULL,

PostalCodenvarchar（50）NOTNULL,

CreateDatedatetimeNOTNULLDEFAULT（GETDATE（））,

ModifiedDatedatetimeNOTNULLDEFAULT（GETDATE（））

）

YoucreateastoredprocedurethatincludestheAccountNumber,Country,andStateProvincecolumns

fromthedbo.Customerstable.Thestoredprocedureacceptsaparametertofiltertheoutputonthe

AccountNumbercolumn.

Youneedtooptimizetheperformanceofthestoredprocedure.Youmustnotchangetheexisting

structureofthetable.

WhichTransact-SQLstatementshouldyouuse?

A.CREATESTATISTICSST_Customer_AccountNumber

ONdbo.Customer（AccountNumber）

WITHFULLSCAN;

B.CREATECLUSTEREDINDEXIX_Customer_AccountNumber

ONdbo.Customer（AccountNumber）;

C.CREATENONCLUSTEREDINDEXIX_Customer_AccountNumber

ONdbo.Customer（AccountNumber）

WHEREAccountNumber='';

D.CREATENONCLUSTEREDINDEXIX_Customer_AccountNumber

ONdbo.Customer（AccountNumber）

INCLUDE（Country,StateProvince）;

Answer:

D

7.YouhaveatablenamedCustomer.

Youneedtoensurethatcustomerdatainthetablemeetsthefollowingrequirements:

creditlimitmustbezerounlesscustomeridentificationhasbeenverified.

creditlimitmustbelessthan10,000.

Whichconstraintshouldyouuse?

A.CHECK（CreditLimtBETWEEN1AND10000）

B.CHECK（Verified=1ANDCreditLimtBETWEEN1AND10000）

C.CHECK（（CreditLimt=0ANDVerified=0）OR（CreditLimtBETWEEN1AND10000ANDVerified=1））

Thesafer,easierwaytohelpyoupassanyITexams.

6/80

D.CHECK（（CreditLimt=0ANDVerified=0）AND（CreditLimtBETWEEN1AND10000ANDVerified=

1））

Answer:

C

8.YouhaveatablenamedAccountsReceivable.Thetablehasnoindexes.Thereare75,000rowsinthe

table.YouhaveapartitionfunctionnamedFG_AccountData.TheAccountsReceivabletableisdefinedin

thefollowingTransact-SQLstatement:

CREATETABLEAccountsReceivable（

column_aINTNOTNULL,

column_bVARCHAR（20）NULL）

ON[PRIMARY];

YouneedtomovetheAccountsReceivabletablefromthePRIMARYfilegrouptoFG_AccountData.

WhichTransact-SQLstatementshouldyouuse?

A.CREATECLUSTEREDINDEXidx_AccountsReceivable

ONAccountsReceivable（column_a）

ON[FG_AccountData];

B.CREATENONCLUSTEREDINDEXidx_AccountsReceivable

ONAccountsReceivable（column_a）

ON[FG_AccountData];

C.CREATECLUSTEREDINDEXidx_AccountsReceivable

ONAccountsReceivable（column_a）

ONFG_AccountData（column_a）;

D.CREATENONCLUSTEREDINDEXidx_AccountsReceivable

ONAccountsReceivable（column_a）

ONFG_AccountData（column_a）;

Answer:

C

9.YouhaveaSQLServer2008databasenamedContosowithatablenamedInvoice.Theprimarykeyof

thetableisInvoiceId,anditispopulatedbyusingtheidentityproperty.TheInvoicetableisrelatedtothe

InvoiceLineItemtable.YouremoveallconstraintsfromtheInvoicetableduringadataloadtoincrease

loadspeed.Younoticethatwhiletheconstraintswereremoved,arowwithInvoiceId=10wasremoved

fromthedatabase.Youneedtore-inserttherowintotheInvoicetablewiththesameInvoiceIdvalue.

WhichTransact-SQLstatementshouldyouuse?

A.INSERTINTOInvoice（InvoiceId,...

VALUES（10,...

B.SETIDENTITY_INSERTInvoiceON;

Thesafer,easierwaytohelpyoupassanyITexams.

7/80

INSERTINTOInvoice（InvoiceId,...

VALUES（10,...

SETIDENTITY_INSERTInvoiceOFF;

C.ALTERTABLEInvoice;

ALTERCOLUMNInvoiceIdint;

INSERTINTOInvoice（InvoiceId,...

VALUES（10,...

D.ALTERDATABASEContosoSETSINGLE_USER;

INSERTINTOInvoice（InvoiceId,...

VALUES（10,...

ALTERDATABASEContosoSETMULTI_USER;

Answer:

B

10.Youaredevelopinganewdatabase.ThedatabasecontainstwotablesnamedSalesOrderDetailand

Product.

YouneedtoensurethatallproductsreferencedintheSalesOrderDetailtablehaveacorresponding

recordintheProducttable.

Whichmethodshouldyouuse?

A.JOIN

B.DDLtrigger

C.Foreignkeyconstraint

D.Primarykeyconstraint

Answer:

C

11.YouarecreatingatablethatstorestheGPSlocationofcustomers.

Youneedtoensurethatthetableallowsyoutoidentifycustomerswithinaspecifiedsalesboundaryand

tocalculatethedistancebetweenacustomerandtheneareststore.

Whichdatatypeshouldyouuse?

A.geometry

B.geography

C.nvarchar（max）

D.varbinary（max）FILESTREAM

Answer:

B

12.YouplantoaddanewcolumnnamedSmallKeytotheSales.Producttablethatwillbeusedina

uniqueconstraint.Youarerequiredtoensurethatthefollowinginformationisappliedwhenaddingthe

newcolumn:

Thesafer,easierwaytohelpyoupassanyITexams.

8/80

'a1'and'A1'aretreatedasdifferentvalues

'a'and'A'sortbefore'b'and'B'inanORDERBYclause

Youneedtoselectthecollationthatmeetstherequirementsforthenewcolumn.Whichcollationshould

youselect?

A.Latin1_General_BIN

B.SQL_Latin1_General_CP1_CI_AI

C.SQL_Latin1_General_CP1_CI_AS

D.SQL_Latin1_General_CP1_CS_AS

Answer:

D

13.Youhavemultipletablesthatrepresentpropertiesofthesamekindofentities.Thepropertyvalues

arecomprisedoftext,geometry,varchar（max）,anduser-definedtypesspecifiedas'bitNOTNULL'data

types.

Youplantoconsolidatethedatafrommultipletablesintoasingletable.Thetablewillusesemi-structured

storagebytakingadvantageoftheSPARSEoption.

YouaretaskedtoidentifythedatatypesthatarecompatiblewiththeSPARSEoption.

WhichdatatypeiscompatiblewiththeSPARSEoption?

A.text

B.geometry

C.varchar（max）

D.Auser-definedtypedefinedas'bitNOTNULL'

Answer:

C

14.Youcurrentlystoredatei