雇员数据库查询

--3.9
--a.找出所有为'small Bank Corporation'工作的雇员的名字及其居住的城市

select employee.employee_name,city
from employee join works on(employee.employee_name=works.employee_name)
where company_name='small Bank Corporation'

--b.找出所有为'small Bank Corporation'工作且薪金超过10000美元的雇员名字、居住街道和城市

select employee.employee_name,city,street
from employee join works on(employee.employee_name=works.employee_name)
where company_name='small Bank Corporation' and salary>5000

--c.找出数据库中所有不为''small Bank Corporation'工作的雇员

select works.employee_name
from works
where works.employee_name not in(
    select employee.employee_name from employee join works on(employee.employee_name=works.employee_name)
    where company_name='small Bank Corporation' )

--d.找出数据库中工资高于''small Bank Corporation'的每个雇员的所有雇员

select employee_name
from works
where salary>=(
    select max(salary) from works where company_name='small Bank Corporation'
)

--e.假设一个公司在好几个城市有分部，找出位于'small Bank Corporation'所有所在城市的所有公司

select company_name from company as W where not exists(
    (select city from company where company_name='small Bank Corporation')
    except
    (select city from company as T where W.company_name=T.company_name)
)

--f.找出雇员最多的公司

select company_name
 from works
 group by  company_name
having count(*) >=all(
    select count(*)
    from works
 group by  company_name
)

--g.找出平均工资高于'small Bank Corporation'平均工资的那些公司
select company_name
 from works
 group by  company_name
having avg(salary) >all(
    select avg(salary)
    from works
 where company_name='small Bank Corporation'
)

--3.16
--a.找出所有为'small Bank Corporation'工作的雇员的名字

select employee_name from works where company_name='small Bank Corporation'

--b.找出数据库中所有居住城市和公司所在城市相同的雇员

select e.employee_name
 from employee as e,works as w,company as c
 where e.employee_name=w.employee_name and w.company_name=c.company_name and e.city=c.city

--c.找出数据库中所有居住的街道和城市与其经理相同的雇员************************                  

select t.employee_name
from employee as t
where street in(
    select street from employee as e join manages as m on(e.employee_name=m.manages_name)  
    where t.street=street
)
and city in(
    select city from employee as e join manages as m on(e.employee_name=m.manages_name)
    where t.city=city
)

--d.找出工资高于其所在公司雇员平均工资的所有雇员

select employee_name
from works as w
where salary>=all(
    select avg(salary) from works as t
    where w.company_name=t.company_name
)

--f.找出工资总和最少的公司***********************                          

select company_name
from works
group by company_name
having sum(salary)<=all(
    select sum(salary) from works group by company_name
)

--3.17
--a.为'small Bank Corporation'的所有雇员增长10%的工资

update works set salary=salary*1.1 where company_name='small Bank Corporation'

--b.为'small Bank Corporation'的所有经理增长10%的工资

update works set salary=salary*1.1 where company_name='small Bank Corporation' and employee_name in(
        select manages_name
        from manages
        )

--c.删除'small Bank Corporation'的雇员在works关系中的所有元组
delete from works where company_name='small Bank Corporation'