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.Youcurrentlystoredateinformationintwocolumns.Onecolumncontainsthedateinlocaltimeand

onecolumncontainsthedifferencebetweenlocaltimeandUTCtime.Youneedtostorethisdataina

singlecolumn.

Whichdatatypeshouldyouuse?

A.time

B.datetime2

C.datetime2(5)

D.datetimeoffset

Answer:D

15.YouhavetwopartitionedtablesnamedTransactionandTransactionHistory.

YouneedtoarchiveoneofthepartitionsoftheTransactiontabletotheTransactionHistorytable.

Whichmethodshouldyouuse?

A.ALTERTABLE...

Thesafer,easierwaytohelpyoupassanyITexams.

9/80

SWITCH...

B.INSERT...SELECT...;

TRUNCATETABLE

C.ALTERPARTITIONFUNCTION...

MERGE...

D.ALTERPARTITIONFUNCTION...

SPLIT...

Answer:A

16.Youarecreatinganewtableinadatabase.Yourbusinessrequiresyoutostoredatainthetablefor

onlysevendays.

Youneedtoimplementapartitionedtabletomeetthisbusinessrequirement.

Whichtasksshouldyoucomplete?

A.Createthepartitionfunction

Createthepartitionscheme

Createthetable

B.Createthepartitionfunction

Createthetable

Createafilteredindex

C.Addasecondaryfiletotheprimaryfilegroups

Createthetable

Createthedistributedpartitionedview

D.Createthepartitionfunction

Createthepartitionscheme

Createthedistributedpartitionedview

Answer:A

17.YouneedtoalterstoredprocedurestousetheWITHRECOMPILEoption.Whichtypesofstored

proceduresshouldyoualter?(Eachcorrectanswerrepresentsacompletesolution.Choosetwo.)

A.StoredproceduresimplementedfromCLRassemblies.

B.StoredproceduresthatrequiretheFORREPLICATIONoption.

C.StoredproceduresthatrequiretheWITHENCRYPTIONoption.

D.StoredproceduresthatcontainqueriesthatusetheOPTION(RECOMPILE)hint.

Answer:CD

18.YouhaveaSQLServerdatabase.ThedatabasecontainstwoschemasnamedMarketingandSales.

TheMarketingschemaisownedbyausernamedMarketingManager.TheSalesschemaisownedbya

Thesafer,easierwaytohelpyoupassanyITexams.

10/80

usernamedSalesManager.

AusernamedJohnmustbeabletoaccesstheSales.Orderstablebyusingastoredprocedurenamed

Marketing.GetSalesSummary.JohnisnotgrantedaSELECTpermissionontheSales.Orderstable.A

usernamedSalesUserdoeshaveSELECTpermissionontheSales.Orderstable.Youneedtoimplement

appropriatepermissionsforJohnandthestoredprocedureMarketing.GetSalesSummary.

Whatshouldyoudo?

A.Marketing.GetSalesSummaryshouldbecreatedbyusingtheEXECUTEAS'SalesUser'clause.

JohnshouldbegrantedEXECUTEpermissiononMarketing.GetSalesSummary.

B.Marketing.GetSalesSummaryshouldbecreatedbyusingtheEXECUTEASOWNERclause.

JohnshouldbegrantedEXECUTEWITHGRANTOPTIONonMarketing.GetSalesSummary.

C.Marketing.GetSalesSummaryshouldbecreatedbyusingtheEXECUTEASCALLERclause.

JohnshouldbegrantedIMPERSONATEpermissionfortheusernamedSalesUser.

D.Marketing.GetSalesSummaryshouldbecreatedwithoutanEXECUTEASclause.

JohnshouldbegrantedSELECTpermissionontheSales.Orderstable.

Answer:A

19.Youneedtocreateastoredprocedurethatacceptsatable-valuedparameternamed@Customers.

Whichcodesegmentshouldyouuse?

A.CREATEPROCEDUREAddCustomers

(@Customersvarchar(max))

B.CREATEPROCEDUREAddCustomers

(@CustomersCustomerREADONLY)

C.CREATEPROCEDUREAddCustomers

(@CustomersCustomerTypeOUTPUT)

D.CREATEPROCEDUREADDCUSTOMERS

(@Customersvarchar(max))

AS

EXTERNALNAMECustomer.Add.NewCustomer

Answer:B

20.Youhaveacomputedcolumnthatisimplementedwithauser-definedfunction.Theuser-defined

functionreturnsaformattedaccountnumber.Thecolumnmustbeindexedtoprovideadequatesearch

performance.

Youplantocreateanindexonthecomputedcolumn.Youneedtoidentifythevalidcombinationof

ObjectPropertyEXvaluesfortheuser-definedfunction.

Whichcombinationshouldyouuse?

Thesafer,easierwaytohelpyoupassanyITexams.

11/80

A.IsDeterministic=True

IsSystemVerified=True

UserDataAccess=False

SystemDataAccess=False

B.IsDeterministic=True

IsSystemVerified=True

IsPrecise=True

IsTableFunction=True

C.IsDeterministic=False

IsSystemVerified=True

UserDataAccess=False

SystemDataAccess=False

D.IsDeterministic=False

IsSystemVerified=True

IsPrecise=True

SystemDataAccess=False

Answer:A

21.Youneedtoidentify,withinagivenclause,ifthemonthofFebruarywillcontain29daysforaspecified

year.

Whichobjectshouldyouuse?

A.DMLtrigger

B.Storedprocedure

C.Table-valuedfunction

D.Scalar-valuedfunction

Answer:D

22.Youarecreatingafunctionthatreferencesatable.

Youneedtopreventthetablefrombeingdropped.

Whichoptionshouldyouusewhenyoucreatethefunction?

A.WITHENCRYPTION

B.WITHEXECUTEAS

C.WITHSCHEMABINDING

D.WITHRETURNSNULLONNULLINPUT

Answer:C

23.ClicktheExhibitbutton.

Thesafer,easierwaytohelpyoupassanyITexams.

12/80

YouaredevelopingadatabaseusingMicrosoftSQLServer2008.Thedatabasecontainsthetables

shownintheexhibit.

Youarerequiredtopreventpartsfrombeingdeletediftheybelongtoakit.Ifapartbelongstoakit,the

deleteshouldnotoccurandtheIsDeletedcolumnfortherowshouldbechangedto'True'.Partscanbe

deletediftheydonotbelongtoakit.

YouhavethefollowingTransact-SQLstatementtobeusedinatrigger:

UPDATEp

SETIsDeleted=1

FROMKitPartkp

JOINdeleteddONkp.PartID=d.PartID

JOINPartpONkp.PartID=p.PartID;

DELETEFROMp

FROMPartp

JOINdeleteddONp.PartID=d.PartID

LEFTOUTERJOINKitPartkpONp.PartID=kp.PartID

WHEREkp.KitIDISNULL;

YouneedtoimplementtheTransact-SQLstatementinatrigger.Whichtriggersyntaxshouldyouuse?

A.CREATETRIGGERtr_Part_dONPart

AFTERDELETEAS

BEGIN

END

Thesafer,easierwaytohelpyoupassanyITexams.

13/80

B.CREATETRIGGERtr_Part_dONPart

INSTEADOFDELETEAS

BEGIN

END

C.CREATETRIGGERtr_KitPart_dONKitPart

AFTERDELETEAS

BEGIN

END

D.CREATETRIGGERtr_KitPart_dONKitPart

INSTEADOFDELETEAS

BEGIN

END

Answer:B

24.Youhaveathird-partyapplicationthatinsertsdatadirectlyintoatable.

Youaddtwonewcolumnstothetable.ThesecolumnscannotacceptNULLvaluesandcannotuse

defaultconstraints.

Youneedtoensurethatthenewcolumnsdonotbreakthethird-partyapplication.

Whatshouldyoudo?

A.CreateaDDLtrigger.

B.Createastoredprocedure.

C.CreateanAFTERINSERTtrigger.

D.CreateanINSTEADOFINSERTtrigger.

Answer:D

25.YourdatabasecontainstwotablesnamedOrderandOrderDetailsthatstoreorderinformation.They

relatetoeachotherusingtheOrderIDcolumnineachtable.Yourbusinessrequiresthatthe

LastModifiedDatecolumnintheOrdertablemustreflectthedateandtimewhenachangeismadeinthe

OrderDetailstablefortherelatedorder.

Youneedtocreateatriggertoimplementthisbusinessrequirement.

WhichTransact-SQLstatementshouldyouuse?

A.CREATETRIGGER[uModDate]ON[OrderDetails]

INSTEADOFUPDATEFORREPLICATION

Thesafer,easierwaytohelpyoupassanyITexams.

14/80

AS

UPDATE[Order]

SET[LastModifiedDate]=GETDATE()

FROMinserted

WHEREinserted.[OrderID]=[Order].[OrderID];

B.CREATETRIGGER[uModDate]ON[Order]

INSTEADOFUPDATENOTFORREPLICATION

AS

UPDATE[Order]

SET[LastModifiedDate]=GETDATE()

FROMinserted

WHEREinserted.[OrderID]=[Order].[OrderID];

C.CREATETRIGGER[uModDate]ON[Order]

AFTERUPDATEFORREPLICATION

AS

UPDATE[Order]

SET[LastModifiedDate]=GETDATE()

FROMinserted

WHEREinserted.[OrderID]=[Order].[OrderID];

D.CREATETRIGGER[uModDate]ON[OrderDetails]

AFTERUPDATENOTFORREPLICATION

AS

UPDATE[Order]

SET[LastModifiedDate]=GETDATE()

FROMinserted

WHEREinserted.[OrderID]=[Order].[OrderID];

Answer:D

26.Youneedtoensurethattablesarenotdroppedfromyourdatabase.

Whatshouldyoudo?

A.CreateaDDLtriggerthatcontainsCOMMIT.

B.CreateaDMLtriggerthatcontainsCOMMIT.

C.CreateaDDLtriggerthatcontainsROLLBACK.

D.CreateaDMLtriggerthatcontainsROLLBACK.

Answer:C

Thesafer,easierwaytohelpyoupassanyITexams.

15/80

27.YouareresponsibleforaSQLServerdatabase.Yourequirethetablestobeaddedoralteredonlyon

thefirstdayofthemonth.Youneedtoensurethatifthetablesareattemptedtobemodifiedorcreatedon

anyotherday,anerrorisreceivedandtheattemptisnotsuccessful.

WhichTransact-SQLstatementshouldyouuse?

A.CREATETRIGGERTRG_TABLES_ON_FIRST

ONDATABASEFORCREATE_TABLE

AS

IFDATEPART(day,getdate())>1

BEGIN

RAISERROR('Mustwaittilnextmonth.',16,1)

END

B.CREATETRIGGERTRG_TABLES_ON_FIRST

ONDATABASEFORCREATE_TABLE,ALTER_TABLE

AS

IFDATEPART(day,getdate())>1

BEGIN

RAISERROR('Mustwaittilnextmonth.',16,1)

END

C.CREATETRIGGERTRG_TABLES_ON_FIRST

ONDATABASEFORCREATE_TABLE,ALTER_TABLE

AS

IFDATEPART(day,getdate())>1

BEGIN

ROLLBACK

RAISERROR('Mustwaittilnextmonth.',16,1)

END

D.CREATETRIGGERTRG_TABLES_ON_FIRST

ONALLSERVERFORALTER_DATABASE

AS

IFDATEPART(day,getdate())>1

BEGIN

ROLLBACK

RAISERROR('Mustwaittilnextmonth.',16,1)

END

Thesafer,easierwaytohelpyoupassanyITexams.

16/80

Answer:C

28.YouhaveasingleCLRassemblyinyourdatabase.Theassemblyonlyreferencesblessed

assembliesfromtheMicrosoft.NETFrameworkanddoesnotaccessexternalresources.

Youneedtodeploythisassemblybyusingtheminimumrequiredpermissions.Youmustensurethatyour

databaseremainsassecureaspossible.

Whichoptionsshouldyouset?

A.PERMISSION_SET=SAFE

TRUSTWORTHYON

B.PERMISSION_SET=SAFE

TRUSTWORTHYOFF

C.PERMISSION_SET=UNSAFE

TRUSTWORTHYON

D.PERMISSION_SET=EXTERNAL_ACCESS

TRUSTWORTHYOFF

Answer:B

29.Youhavecreatedanassemblythatutilizesunmanagedcodetoaccessexternalresources.

Youneedtodeploytheassemblywiththeappropriatepermissions.

Whichpermissionsetshouldyouuse?

A.SAFE

B.UNSAFE

C.EXTERNAL_ACCESS

D.Defaultpermissionset

Answer:B

30.YouhavetablesnamedProductsandOrderDetails.TheProductstablehasaforeignkeyrelationship

withtheOrderDetailstableontheProductIDcolumn.YouhavethefollowingTransact-SQLbatch:

BEGINTRY

BEGINTRANSACTION

DELETEFROMProductsWHEREProductID=5;

BEGINTRANSACTION

INSERTINTOOrderDetails

(OrderID,ProductID,Quantity)

VALUES

(1234,5,12);

COMMITTRANSACTION

Thesafer,easierwaytohelpyoupassanyITexams.

17/80

COMMITTRANSACTION

ENDTRY

BEGINCATCH

ROLLBACKTRANSACTION

PRINTERROR_MESSAGE();

ENDCATCH

Youneedtoanalyzetheresultofexecutingthisbatch.Whatshouldbetheexpectedoutcome?

A.1.TheproductwillbedeletedfromtheProductstable.

2.TheorderdetailswillbeinsertedintotheOrderDetailstable.

B.1.TheproductwillbedeletedfromtheProductstable.

2.TheorderdetailswillnotbeinsertedintotheOrderDetailstable.

C.1.TheproductwillnotbedeletedfromtheProductstable.

2.TheorderdetailswillbeinsertedintotheOrderDetailstable.

D.1.TheproductwillnotbedeletedfromtheProductstable.

2.TheorderdetailswillnotbeinsertedintotheOrderDetailstable.

Answer:D

31.YouareusingTRY...CATCHerrorhandling.

YouneedtoraiseanerrorthatwillpasscontroltotheCATCHblock.

Whichseveritylevelshouldyouuse?

A.0

B.9

C.10

D.16

Answer:D

32.YouhaveatablenamedOrders.Youhavebeentaskedtomodifyyourcompany'smaindatabaseto

removeallinactiveorderrows.

Youaredevelopingastoredprocedurethatwillenableyoutodeletetheserows.Youhavewrittenthe

followingcodesegmenttoaccomplishthistask.(Linenumbersareincludedforreferenceonly.)

01BEGINTRY

02DECLARE@RowCountINT=1000

03WHILE@RowCount=1000

04BEGIN

05DELETETOP(1000)FROMOrdersWHEREStatus='Inactive';

06SET@RowCount=@@ROWCOUNT

Thesafer,easierwaytohelpyoupassanyITexams.

18/80

07...

08END

09ENDTRY

10BEGINCATCH

11PRINTERROR_MESSAGE()

12ENDCATCH

YouneedtoinsertaTransact-SQLstatementthatwillnotifyyouimmediatelyaftereachbatchofrowsis

deleted.WhichTransact-SQLstatementshouldyouinsertatline07?

A.RAISERROR('Deleted%irows',6,1,@RowCount)

B.RAISERROR('Deleted%irows',16,1,@RowCount)

C.RAISERROR('Deleted%irows',10,1,@RowCount)WITHNOWAIT

D.RAISERROR('Deleted%irows',11,1,@RowCount)WITHNOWAIT

Answer:C

33.Youhaveatransactionthatusestherepeatablereadisolationlevel.

Thistransactioncausesfrequentblockingproblems.Youneedtoreduceblocking.Youalsoneedtoavoid

dirtyreadsandnon-repeatablereads.

Whichtransactionisolationlevelshouldyouuse?

A.SNAPSHOT

B.SERIALIZABLE

C.READCOMMITTED

D.READUNCOMMITTED

Answer:A

34.YouarewritingabatchthatcontainsmultipleUPDATEstatementstomodifyexistingproducts.You

haveplacedtheseupdatesintooneexplicittransaction.Youneedtosetanoptionatthebeginningofthe

transactiontorollbackallchangesifanyoftheupdatesinthetransactionfail.Whichoptionshouldyou

enable?

A.ARITHABORT

B.XACT_ABORT

C.IMPLICIT_TRANSACTIONS

D.REMOTE_PROC_TRANSACTIONS

Answer:B

35.YouhaveatablenamedJobCandidate.YouaretaskedtodeletearowintheJobCandidatetable.You

needtowriteatransactionthatallowsthedatabasetoberestoredtotheexactpointtherecordwas

deletedwithoutknowingthetimeofexecution.Whichqueryshouldyouuse?

Thesafer,easierwaytohelpyoupassanyITexams.

19/80

A.BEGINTRANSACTION

DELETEFROMJobCandidate

WHEREJobCandidateID=10;

COMMITTRANSACTION;

B.BEGINTRANSACTION

WITHMARKN'DeletingaJobCandidate';

DELETEFROMJobCandidate

WHEREJobCandidateID=10;

COMMITTRANSACTION

C.BEGINTRANSACTIONDelete_CandidateWITHMARK

DELETEFROMJobCandidate

WHEREJobCandidateID=10;

COMMITTRANSACTIONDelete_Candidate;

D.DECLARE@CandidateNamevarchar(50)='Delete_Candidate'

BEGINTRANSACTION@CandidateName

DELETEFROMJobCandidate

WHEREJobCandidateID=10;

COMMITTRANSACTION@CandidateName;

Answer:C

36.YouhavethefollowingtablenamedSales.

Youneedtoreturnsalesdataorderedbycustomernameanddateofsale.Foreachcustomer,themost

recentsalemustbelistedfirst.

Whichqueryshouldyouuse?

A.SELECTCustomerName,

SalesDate

FROMSales

ORDERBYCustomerName,

SalesDate;

B.SELECTCustomerName,

SalesDate

FROMSales

ORDERBYSalesDateDESC,

CustomerName;

C.SELECTCustomerName,

Thesafer,easierwaytohelpyoupassanyITexams.

20/80

SalesDate

FROMSales

ORDERBYCustomerName,

SalesDateDESC;

D.SELECTCustomerName,

SalesDate

FROMSales

ORDERBYCustomerNameDESC;

Answer:C

37.YouhaveatablenamedSales.SalesOrderHeaderandatablenamedPerson.Person.Youaretasked

towriteaquerythatreturnsSalesOrderIDandSalesPersonNamethathaveanOrderDategreaterthan

20040101.SalesPersonNameshouldbemadeupbyconcatenatingthecolumnsnamedFirstNameand

LastNamefromthetablenamedPerson.Person.Youneedtowriteaquerytoreturndata,sortedin

alphabeticalorder,bytheconcatenationofFirstNameandLastName.

WhichTransact-SQLstatementshouldyouuse?

A.SELECTSalesOrderID,FirstName+''+LastNameasSalesPersonName

FROMSales.SalesOrderHeaderH

JOINPerson.PersonPon

P.BusinessEntityID=H.SalesPersonID

WHEREOrderDate>'20040101'

ORDERBYFirstNameASC,LastNameASC

B.SELECTSalesOrderID,FirstName+''+LastNameasSalesPersonName

FROMSales.SalesOrderHeaderH

JOINPerson.PersonPon

P.BusinessEntityID=H.SalesPersonID

WHEREOrderDate>'20040101'

ORDERBYFirstNameDESC,LastNameDESC

C.SELECTSalesOrderID,FirstName+''+LastNameasSalesPersonName

FROMSales.SalesOrderHeaderH

JOINPerson.PersonPon

P.BusinessEntityID=H.SalesPersonID

WHEREOrderDate>'20040101'

ORDERBYSalesPersonNameASC

D.SELECTSalesOrderID,FirstName+''+LastNameasSalesPersonName

Thesafer,easierwaytohelpyoupassanyITexams.

21/80

FROMSales.SalesOrderHeaderH

JOINPerson.PersonPon

P.BusinessEntityID=H.SalesPersonID

WHEREOrderDate>'20040101'

ORDERBYSalesPersonNameDESC

Answer:C

38.YouhaveatablenamedSales.PotentialClients.ThistablecontainsacolumnnamedEmailAddress.

Youaretaskedtodevelopareportthatreturnsvalid".com"emailaddressesfromSales.PotentialClients.

Avalidemailaddressmusthaveatleastonecharacterbeforethe@sign,andonecharacterafterthe@

signandbeforethe".com."

YouneedtowriteaTransact-SQLstatementthatreturnsdatatomeetthebusinessrequirements.

WhichTransact-SQLstatementshouldyouuse?

A.select*fromSales.PotentialClients

whereEmailAddresslike'_%@_%.com'

B.select*fromSales.PotentialClients

whereEmailAddresslike'%@%.com'

C.select*fromSales.PotentialClients

whereEmailAddresslike'_%@_.com'

D.select*fromSales.PotentialClients

whereEmailAddresslike'%@%[.]com'

Answer:A

39.YouhaveatablenamedOrders.OrderIDisdefinedasanIDENTITY(1,1).OrderDatehasadefault

valueof1.

YouneedtowriteaquerytoinsertaneworderintotheOrderstableforCustomerID45withtoday'sdate

andacostof89.00.Whichstatementshouldyouuse?

A