数据库原理习题课幻灯片.pptVIP

找出所有为First Bank Corporation工作的员工的名字 select employee-name from works where company-name=’First Bank Corporation’ 找出各个公司员工的平均工资，并按照公司名称排序（逆序） Select company-name, avg (salary) From works Group by company-name Order by company-name desc Copyright: Xinying Wang 找出First Bank Corporation的所有年收入在10,000美元以上的员工的姓名和居住的街道、城市 select a.employee-name, street, city from works as a, employee as b where company-name=‘First Bank Corporation’ and a.employee-name=b.employee-name and salary10000 找出所有居住地与工作的公司在同一城市的员工姓名 select a.employee-name from works a, employee b, company c where pany_name= pany_name and a.employee-name=b.employee-name and b.city=c.city Copyright: Xinying Wang 找出与其经理居住在同一城市同一街道的所有员工的姓名 select a.employee_name from employee as a, manager as b , (select a.employee_name , street , city from employee as a, manages as b where a.employee_name=b.manager_name) as mg(manager_name,street,city) where a.employee_name=mg.employee_name and b.manager_name=mg.manager_name and a.city=b.city and a.street=b.street Copyright: Xinying Wang 找出数据库中不在First Bank Corporation工作的所有员工的姓名 每个人只能在一个公司工作（不允许兼职） select employee-name from works where company-name‘First Bank Corporation’ 每个人只能在一个公司工作（不允许兼职） select employee-name from works except select employee-name from works where company-name=‘First Bank Corporation’ Copyright: Xinying Wang 找出比Small Bank Corporation所有员工收入都高的所有的员工的姓名 select employee_name from works where salary all (select max(salary) from works where company_name=‘S.B.C.’) 假设一个公司可以位于几个城市中，找出位于Small Bank Corporation所在的某个城市的所有公司 select company-name from company where city in (select city from company where company_name=‘S.B.C.’) Copyright: Xinying Wang 假设一个公司可以位于几个城市中，找出位于Small Bank Corporation所在的各个城市的所有公司 select company-name from company as c where not exists ( select city from company where co