SQL数据库系统SQL补充习题2答案

《SQL数据库系统SQL补充习题2答案》由会员上传分享，免费在线阅读，更多相关内容在教育资源-天天文库。

1、1.找出所有为Firstbankcorporation工作的雇员名字和居住城市；selecte.employeename,cityfromemployeee,workswwherew.companyname=’FirstBankCorporation’andw.employeename=e.employeename2.找出所有为Firstbankcorporation工作且薪金超过1万元的雇员名字、居住街道和城市；Ifpeoplemayworkforseveralcompanies,thefollo

2、wingsolutionwillonlylistthosewhoearnmorethan$10,000perannumfrom“FirstBankCorporation”alone.select*fromemployeewhereemployeenamein(selectemployeenamefromworkswherecompanyname=’FirstBankCorporation’andsalary¿10000)Asinthesolutiontothepreviousquery,wecanu

3、seajointosolvethisonealso.3.找出所有不为Firstbankcorporation工作的雇员；Thefollowingsolutionassumesthatallpeopleworkforexactlyonecompany.selectemployeenamefromworkswherecompanyname_=’FirstBankCorporation’Ifoneallowspeopletoappearinthedatabase(e.g.inemployee)butnot

4、appearinworks,orifpeoplemayhavejobswithmorethanonecompany,thesolutionisslightlymorecomplicated.selectemployeenamefromemployeewhereemployeenamenotin(selectemployeenamefromworkswherecompanyname=’FirstBankCorporation’)4.找出数据库中工资比Smallbankcorporation的每一个雇员

5、都高的所有雇员；Thefollowingsolutionassumesthatallpeopleworkforatmostonecompany.selectemployeenamefromworkswheresalary>all(selectsalaryfromworkswherecompanyname=’SmallBankCorporation’)Ifpeoplemayworkforseveralcompaniesandwewishtoconsiderthetotalearningsofeachp

6、erson,theproblemismorecomplex.Itcanbesolvedbyusinganestedsubquery,butweillustratebelowhowtosolveitusingthewithclause.withemptotalsalaryas(selectemployeename,sum(salary)astotalsalaryfromworksgroupbyemployeename)selectemployeenamefromemptotalsalarywheret

7、otalsalary>all(selecttotalsalaryfromemptotalsalary,workswhereworks.companyname=’SmallBankCorporation’andemptotalsalary.employeename=works.employeename)5.假设一个公司可以在好几个城市有分部。找出位于Smallbankcorporation所有所在城市的所有公司；Thesimplestsolutionusesthecontainscomparisonw

8、hichwasincludedin　theoriginalSystemRSequellanguagebutisnotpresentinthesubsequentSQLversions.selectT.companynamefromcompanyTwhere(selectR.cityfromcompanyRwhereR.companyname=T.companyname)contains(selectS.cityfromcompanySwhereS.companynam