数据库5版第三章习题解答
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
第三章习题解答
表结构
employee（employee-name，street, city)
worker (employee-name, company-name, salary)
company (company-name, city )
manages (employee-name, manager-name)
3.2
a)找出为“first”工作的雇员名和城市
select employee.employee-name,city
from employee, works
where employee. employee-name=works. employee-name and company-name=”first”
b) 找出为“first”工作的工资超一万的雇员名、街道和城市select employee.employee-name,street,city
from employee , works
where employee. employee-name=works. employee-name and company-name=”first” and salary > 10000
c) 找出不为“first”工作的雇员名
select employee-name
from employee
where employee-name not in(select employee-name
from works
where company-name=”first”)
d) 找出工资比“small”每个雇员都高的雇员名
select employee-name
from works
where salary >all(select salary
from works
where company-name=”small”)
e) 找出位于“small”所有所在城市的其他公司名
select company-name
from company
where city in (select city
from company
where company-name=”small”) 或者
select s .company-name
from company as s, company as t
where s .city = t .city and pany-name=”small”
f) 找出雇员最多的公司名
select company-name
from works
group by company-name having count(*)>=all
( select count(*)
from works
group by company-name )
或者
select company-name, max(num)
from(select company-name, count(distinct employeename) from works
group by company-name as com(company-name,num))
g) 找出平均工资高于“first”平均工资的所有公司
select company-name,avg(salary)
from works
group by company-name having avg(salary) >
(select avg(salary)
from works
where company-name=”first”)
补充：找出所在城市包含small…公司所有城市的公司名称
select pany-name
from works as t
where not exists((select city
from company
where company-name=”small”)
except
(select city
from company
where company-name=pany-name)) 或者
select company-name
from works as x
where not exists (
select *
from works as y
where pany-name=”small” and
not exists(select *
from works as z
where pany-name=pany-name and
z.city=y.city))
3.3
a) 将Jones的居住城市改为newton
Update employee
Set city=”newton”
Where employee-name =”Jones”
b) 为”first”的经理加工资，低于100000美元的加10%，高于100000的加3%
Update works
Set salay =salay * 1.03
Where company-name =”first” and salay >=100000
And employee-name in (select manager-name
From manages)
Update works
Set salay =salay * 1.1
Where company-name =”first” and salay < 100000
And employee-name in (select manager-name
From manages)
或者
Update works
Set salay =case
When salay < 100000 then salay * 1.1