SQL數(shù)據(jù)庫系統(tǒng)SQL補充習(xí)題2答案.doc

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. 找出數(shù)據(jù)庫中工資比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. 假設(shè)一個公司可以在好幾個城市有分部。找出位于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. 找出數(shù)據(jù)庫中所有居住街道和城市與其經(jīng)理相同的雇員；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. 找出