Programming Interviews Exposed 2.pdf

《Programming Interviews Exposed 2.pdf》由会员上传分享，免费在线阅读，更多相关内容在学术论文-天天文库。

1、148Chapter7ThissolutionhasarunningtimeofO(m)wheremisthenumberofl'sinthesolution.Theremaybeevenbettersolutions.Keepinmindthatthissolutionwaspresentedforinterest,andthefirstsolutionisallthatwouldbeexpectedinaninterview.Problem:SimpleSQL•GivenadatabasewiththetableOlympics(cityCHAR(16),year

2、INT(4));writeaSQLstatementtoinsertMontrealand1976intothedata-base.ThisisanextremelyeasyquestionthataninterviewermightusetodeterminewhetheryouhaveeverusedSQLbeforeorwhetheryouwerepaddingyourresumewhenyoumentionedit.IfyouknowSQL,you'reallset.It'sastraightforwardSQLINSERTstatement;notricks

3、atall.Ifyoudon'treallyknowSQL,you'reintrouble.Thecorrectansweris:INSERTINTOOlympicsVALUES('Montreal',1976);Problem:CompanyandEmployeeDatabase•Youaregivenadatabasewiththefollowingtables:Company(companyNameCHAR(30),idINT(4));EmployeesHired(idINT(4),numHiredINT(4),fiscalQuarterINT(4));Youm

4、aymaketheassumptionthattheonlypossiblefiscalquar-tersare1through4.SampledataforthisschemaarepresentedinTable7.3.WriteaSQLstatementthatreturnsthenamesofallthecompaniesthathiredemployeesinfiscalquarter4.Thisquestioninvolvesretrievingdatafromtwotables.Youwillhavetojointhetwotablestogetallo

5、ftheneededinformation,idistheonlykeycommontobothtablessoyouwillwanttojoinonthevalueid.Onceyouhavejoinedthetwotables,youcanselectthecompanynamewherethefiscalquarteris4.ThisSQLstatementlookslikethis:SELECTcompanyNameFROMCompany,EmployeesHiredWHERECompany.Id=EmployeesHired.idANDfiscalQuart

6、er=4;OtherProgrammingTopics149Table7.3CompanyandEmployeesSampleDataThereisasmallproblemwiththisSQLstatement.ThinkofwhatmighthappenifacompanydidnothireanyoneinQ4.TherecouldstillbeatuplelikeEmployeesHired(6,0,4).Thecompanywithid6wouldbereturnedbytheprecedingqueryeventhoughtheyhirednoonedu

7、ringfiscalquarter4.Tofixthisbug,youneedtomakesurethatnumHiredisgreaterthan0.TherevisedSQLstatementlookslikethis:SELECTcompanyNameFROMCompany,EmployeesHiredWHERECompany.id=EmployeesHired.idANDfiscalQuarter=4ANDnumHired>0;•Now,usingthesameschema,writeaSQLstatementthatreturnsthena