




全文预览已结束
下载本文档
版权说明:本文档由用户提供并上传,收益归属内容提供方,若内容存在侵权,请进行举报或认领
文档简介
1. 找出所有为First bank corporation工作的雇员名字和居住城市;select e.employee name, cityfrom employee e, works wwhere pany name = First Bank Corporation andw.employee name = e.employee name2. 找出所有为First bank corporation工作且薪金超过1万元的雇员名字、居住街道和城市;If people may work for several companies, the following solution will onlylist those who earn more than $10,000 per annum from “First Bank Corporation”alone.select *from employeewhere employee name in(select employee namefrom workswhere company name = First Bank Corporation and salary 10000)As in the solution to the previous query, we can use a join to solve this onealso.3. 找出所有不为First bank corporation工作的雇员;The following solution assumes that all people work for exactly one company.select employee namefrom workswhere company name _= First Bank CorporationIf one allows people to appear in the database (e.g. in employee) but notappear in works, or if people may have jobs with more than one company,the solution is slightly more complicated.select employee namefrom employeewhere employee name not in(select employee namefrom workswhere company name = First Bank Corporation)4. 找出数据库中工资比Small bank corporation的每一个雇员都高的所有雇员;The following solution assumes that all people work for at most one company.select employee namefrom workswhere salary all(select salaryfrom workswhere company name = Small Bank Corporation)If people may work for several companies and we wish to consider thetotal earnings of each person, the problem is more complex. It can be solvedby using a nested subquery, but we illustrate below how to solve it usingthe with clause.with emp total salary as(select employee name, sum(salary) as total salaryfrom worksgroup by employee name)select employee namefrom emp total salarywhere total salary all(select total salaryfrom emp total salary, workswhere pany name = Small Bank Corporation andemp total salary.employee name = works.employee name)5. 假设一个公司可以在好几个城市有分部。找出位于Small bank corporation所有所在城市的所有公司;The simplest solution uses the contains comparison which was included inthe original System R Sequel language but is not present in the subsequent SQL versions.select T.company namefrom company Twhere (select R.cityfrom company Rwhere R.company name = T.company name)contains(select S.cityfrom company Swhere S.company name = Small Bank Corporation)Below is a solution using standard SQL.select S.company namefrom company Swhere not exists (select cityfrom companywhere company name = Small Bank Corporation)except(select cityfrom company Twhere S.company name = T.company name)6. 找出雇员最多的公司;select company namefrom worksgroup by company namehaving count (distinct employee name) = all(select count (distinct employee name)from worksgroup by company name)7. 找出平均工资高于First bank corporation平均工资的所有公司;select company namefrom worksgroup by company namehaving avg (salary) (select avg (salary)from workswhere company name = First Bank Corporation)8. 找出数据库中所有居住街道和城市与其经理相同的雇员;Find all employees in the database who live in the same cities and on thesame streets as do their managers.select P.employee namefrom employee P, employee R, manages Mwhere P.employee name = M.employee name andM.manager name = R.employee name andP.street = R.street and P.city = R.city9. 找出工资高于其所在公司雇员平均工资的所有雇员;Find all employees who earn more than the average salary of all employeesof their company.The following solution assumes that all people work for atmost one company.select employee namefrom works Twhere salary (select avg (salary)from works Swhere T.company name = S.company name)10. 找出
温馨提示
- 1. 本站所有资源如无特殊说明,都需要本地电脑安装OFFICE2007和PDF阅读器。图纸软件为CAD,CAXA,PROE,UG,SolidWorks等.压缩文件请下载最新的WinRAR软件解压。
- 2. 本站的文档不包含任何第三方提供的附件图纸等,如果需要附件,请联系上传者。文件的所有权益归上传用户所有。
- 3. 本站RAR压缩包中若带图纸,网页内容里面会有图纸预览,若没有图纸预览就没有图纸。
- 4. 未经权益所有人同意不得将文件中的内容挪作商业或盈利用途。
- 5. 人人文库网仅提供信息存储空间,仅对用户上传内容的表现方式做保护处理,对用户上传分享的文档内容本身不做任何修改或编辑,并不能对任何下载内容负责。
- 6. 下载文件中如有侵权或不适当内容,请与我们联系,我们立即纠正。
- 7. 本站不保证下载资源的准确性、安全性和完整性, 同时也不承担用户因使用这些下载资源对自己和他人造成任何形式的伤害或损失。
最新文档
- 2025年4月内蒙古哈伦能源集团有限责任公司招聘模拟试卷及答案详解(夺冠)
- 沧州市人民医院产科急诊超声考核
- 2025春季粤规院科技集团招聘考前自测高频考点模拟试题及完整答案详解1套
- 2025广东广州市增城区教育局招聘广州增城外国语实验中学教师10人(编制)模拟试卷及答案详解(名校卷)
- 张家口市人民医院个人持续学习与知识更新记录及测试
- 北京市人民医院组织架构与决策流程知识测试
- 重庆市人民医院儿童结石碎石技术考核
- 2025第二人民医院前庭康复治疗技能考核
- 2025湖南湘潭市市直学校人才引进45人考前自测高频考点模拟试题及答案详解1套
- 秦皇岛市人民医院故障识别处理考核
- 2024年中级注册安全工程师《安全生产法律法规》真题及答案
- ISO 22003-1:2022《食品安全-第 1 部分:食品安全管理体系 审核与认证机构要求》中文版(机翻)
- 外科学-第三十六章-阑尾疾病
- 八年级物理上册期中考试卷及答案【A4打印版】
- 防盗门订货合同范本
- 教科版科学四年级上册第一单元《声音》测试卷含答案(典型题)
- 《名著阅读 艾青诗选》核心素养课件1(第2课时)
- 人工智能在船舶工程中的应用展望
- 高中化学教师培训课件
- 锲而不舍成功从不言败主题班会课件
- 高血压员工免责协议范本
评论
0/150
提交评论