SQL 认证考试 简单英文题库.docx
- 文档编号:12188172
- 上传时间:2023-04-17
- 格式:DOCX
- 页数:116
- 大小:44.02KB
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
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- SQL 认证考试 简单英文题库 认证 考试 简单 英文 题库
![提示](https://static.bdocx.com/images/bang_tan.gif)