2026年sql英文测试题及答案

2026年sql英文测试题及答案

试卷部分1.单项选择题(总共10题，每题2分)1.WhichclauseismandatoryinabasicSQLSELECTstatement?A.WHEREB.GROUPBYC.SELECTD.ORDERBY2.Whichoperatorcombinesconditionsrequiringalltobetrue?A.ORB.ANDC.NOTD.IN3.WhichJOINreturnsalllefttablerows+matchedrighttablerows?A.INNERJOINB.LEFTJOINC.RIGHTJOIND.FULLJOIN4.Whichfunctioncalculatesthesumofanumericcolumn?A.COUNT()B.SUM()C.AVG()D.MAX()5.Whichconstraintenforcesunique,non-nullvalues?A.UNIQUEB.NOTNULLC.PRIMARYKEYD.FOREIGNKEY6.Whichstatementcreatesaview?A.CREATETABLEB.CREATEVIEWC.ALTERVIEWD.DROPVIEW7.WhichACIDpropertyensuresall-or-nothingtransactions?A.AtomicityB.ConsistencyC.IsolationD.Durability8.Whichsubqueryrunsonceperouterqueryrow?A.Non-correlatedB.CorrelatedC.NestedD.Scalar9.Whichfunctionreturnscurrentdateandtime?A.CURRENT_DATEB.CURRENT_TIMEC.NOW()D.DATE()10.Whichstatementrevokesuserprivileges?A.GRANTB.REVOKEC.ALTERUSERD.DROPUSER2.填空题(总共10题，每题2分)1.The______clausefiltersgroupsbasedonaggregatevalues.2.The______functionreturnstheaverageofanumericcolumn.3.A______keylinkstwotablesviareferencedcolumns.4.The______statementupdatesexistingtablerecords.5.The______clausesortsresults(asc/desc).6.The______constraintblocksNULLvaluesinacolumn.7.A______isavirtualtablefromaqueryresult.8.The______functionreturnsthemaximumcolumnvalue.9.The______JOINreturnsallrowsfrombothtables.10.The______statementdeletesspecificrecords(withWHERE).3.判断题(总共10题，每题2分)1.AFOREIGNKEYmustreferenceaPRIMARYKEYinanothertable.(True/False)2.SELECTcanretrievedatafrommultipletablesviaJOINs.(True/False)3.GROUPBYcomesbeforeWHEREinaSELECTstatement.(True/False)4.AVG()ignoresNULLvalueswhencalculatingaverages.(True/False)5.Aviewcanbeupdatedifbasedonasingletablewithnoaggregates.(True/False)6.COMMITsavesalltransactionchangestothedatabase.(True/False)7.LIKEiscase-sensitiveinallSQLdatabases.(True/False)8.APRIMARYKEYcanbeacompositeofmultiplecolumns.(True/False)9.DROPTABLEdeletestablestructureandalldata.(True/False)10.Windowfunctionscomputevaluesacrossrelatedrowswithoutgrouping.(True/False)4.简答题(总共4题，每题5分)1.ExplainthepurposeofGROUPBYandprovideasimpleexample.2.Differentiatebetweencorrelatedandnon-correlatedsubqueries.3.DescribethefourmainSQLjoinsandtheirusecases.4.ExplainhowindexesimproveSQLqueryperformance.5.讨论题(总共4题，每题5分)1.Whenshouldyouuseasubqueryvs.aJOIN?Provideexamples.2.Discussissueswithexcessiveindexesandmitigationstrategies.3.ExplaintheimportanceofACIDpropertieswithacriticalscenario.4.ListbestpracticesforwritingefficientSQLqueries.答案及解析一、单项选择题答案1.C(SELECTismandatory;otherclausesareoptional)2.B(ANDrequiresallconditionstobetrue)3.B(LEFTJOINretainsalllefttablerows)4.B(SUM()aggregatesnumerictotals)5.C(PRIMARYKEYenforcesuniqueness+NOTNULL)6.B(CREATEVIEWdefinesvirtualtables)7.A(Atomicityensurestransactionsareall-or-nothing)8.B(Correlatedsubqueriesdependonouterqueryrows)9.C(NOW()returnsdate+time;othersreturnpartialvalues)10.B(REVOKEremovespreviouslygrantedprivileges)二、填空题答案1.HAVING2.AVG()3.FOREIGN4.UPDATE5.ORDERBY6.NOTNULL7.VIEW8.MAX()9.FULL(OUTER)10.DELETE三、判断题答案1.True(FOREIGNKEYreferencesPRIMARYKEY/UNIQUEinanothertable)2.True(JOINscombinedatafrommultipletables)3.False(WHEREfiltersrowsbeforegrouping;GROUPBYfollowsWHERE)4.True(AVG()skipsNULLvaluesintargetcolumns)5.True(Updatableviewsneedsinglebasetable+noaggregates)6.True(COMMITpersistschanges;ROLLBACKundoesthem)7.False(Casesensitivitydependsondatabasecollation,e.g.,MySQLiscase-insensitive)8.True(CompositePRIMARYKEYsusemultiplecolumnsforuniqueness)9.True(DROPTABLEdeletesstructure+alldata)10.True(WindowfunctionslikeROW_NUMBER()computeacrossrelatedrows)四、简答题答案1.GROUPBYgroupsrowswithidenticalvaluesinspecifiedcolumnstocalculateaggregatedmetrics(e.g.,COUNT,SUM).Itworkswithaggregatefunctionstosummarizedatapergroup.Example:Tocountcustomerspercity:`SELECTcity,COUNT(customer_id)FROMcustomersGROUPBYcity;`Thisgroupscustomersbycityandreturnsthecountofcustomersineachcity.2.Anon-correlatedsubqueryrunsindependentlyoftheouterquery(oncebeforeouterexecution).Example:`SELECTnameFROMemployeesWHEREdepartment_idIN(SELECTidFROMdepartmentsWHERElocation='London');`Acorrelatedsubquerydependsonouterqueryrows(runsperouterrow).Example:`SELECTname,salaryFROMemployeese1WHEREsalary>(SELECTAVG(salary)FROMemployeese2WHEREe1.department_id=e2.department_id);`(calculatesaveragesalaryperdepartmentforeachemployee).3.Fourmainjoins:1)INNERJOIN:Returnsmatchingrowsfrombothtables(e.g.,joinordersandcustomersoncustomer_id).2)LEFTJOIN:Alllefttablerows+matchedrightrows(e.g.,allcustomers,eventhosewithnoorders).3)RIGHTJOIN:OppositeofLEFTJOIN(allrightrows+matchedleftrows).4)FULLJOIN:Allrowsfrombothtables(NULLswherenomatches,e.g.,allproducts+allcategories).4.Indexesaresorteddatabaseobjectsthatspeedupdataretrieval.Theyactlikeabookindex:insteadofscanningalltablerows(fulltablescan),thedatabaseusestheindextofindrowsquicklyviaindexedcolumns.IndexesimproveperformanceforSELECTquerieswithWHERE/JOIN/ORDERBY/GROUPBYonindexedcolumns,thoughtheyslowdownwriteoperations(INSERT/UPDATE/DELETE)asindexesmustbeupdated.五、讨论题答案1.Usesubqueriesforsingle-conditionfilters(e.g.,"employeeswithsalaryabovecompanyaverage"):`SELECTnameFROMemployeesWHEREsalary>(SELECTAVG(salary)FROMemployees);`UseJOINstocombinedatafrommultipletables(e.g.,"customernames+orderdates"):`SELECT,o.order_dateFROMcustomerscJOINordersoONc.id=o.customer_id;`Subqueriesaresimplerforisolatedfilters;JOINsarebetterformulti-tableresultsets.Avoidcorrelatedsubqueriesforlargedatasets(slowerthanJOINs).2.Excessiveindexesslowwriteoperations(indexesmustbeupdated)andconsumestorage.Mitigations:1)IndexonlycolumnsusedinWHERE/JOIN/ORDERBY/GROUPBY.2)Usecompositeindexesforfrequentlyqueriedcolumnpairs(e.g.,(city,zip)).3)Removeunusedindexes(usedatabasetoolslikeEXPLAINtoidentify).4)Usecoveringindexes(includeallquerycolumns)toavoidtablelookups.5)Partitionlargetablestoreduceindexsize.3.ACIDpropertiesensuretransactionreliability:1)Atomicity(all-or-nothing:e.g.,transferfromAtoB—ifAisdebitedbutBfails,rollback).2)Consistency(validstatetransitions:totalbalanceremainssameaftertransfer).3)Isolation(concurrenttransactionsdon’tinterfere:no