欢迎来到天天文库
浏览记录
ID:42326397
大小:38.50 KB
页数:5页
时间:2019-09-12
《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
此文档下载收益归作者所有