练习题

1.从emp表中查一下员工工号为7788 的员工的姓名和工资。
select ename,sal from emp where empno =7788


2.部门位置在NEW YORK的，所有部门员工的姓名和工资
select ename,sal from emp where deptno in (select deptno from dept where loc ='NEW YORK')
select ename,sal from emp where deptno = (select deptno from dept where loc ='NEW YORK')


select ename,sal from emp,dept where emp.deptno = dept.deptno and loc ='NEW YORK'
select ename,sal from emp left join dept on emp.deptno = dept.deptno where loc ='NEW YORK'
select ename,sal from emp join dept using(deptno) where loc ='NEW YORK'
select ename,sal from emp  join dept on emp.deptno = dept.deptno and loc ='NEW YORK'


select ename,sal from emp where exists(select 1 from dept where deptno = emp.deptno and loc ='NEW YORK')


3.每个部门的每个月的总的人力成本（只算sal）和部门号
select deptno,sum(sal) as 人力成本 from emp group by deptno


4.每个部门的每个月的总的人力成本（只算sal）和部门名称
select dname,sum(sal) as 人力成本 from emp,dept where emp.deptno = dept.deptno group by dname
select emp.deptno,dname,sum(sal) as 人力成本 from emp,dept where emp.deptno = dept.deptno group by emp.deptno,dname
select (select dname from dept where deptno = emp.deptno),sum(sal) as 人力成本 from emp group by deptno


5.每个部门的每个月的总的人力成本（只算sal）和部门名称,无员工的部门要求人力成本显示0
select dname,nvl(sum(sal),0) as 人力成本 from emp right join dept on emp.deptno = dept.deptno group by dname
select dname,nvl2(sum(sal),sum(sal),0) as 人力成本 from emp , dept where emp.deptno(+) = dept.deptno group by dname


6.员工工资大于所有人平均工资的员工的员工姓名和员工工资
select ename,sal from emp where sal >(select avg(sal) from emp)




7.员工工资大于自己部门内所有人平均工资的员工的员工姓名和员工工资
select ename,sal from emp,(select deptno,avg(sal) avgsal from emp group by deptno) b where emp.deptno = b.deptno and sal>avgsal;
select ename,sal from emp a where sal >(select avg(sal) from emp where deptno = a.deptno); 
select ename,sal from emp a where exists (select avg(sal) from emp where deptno = a.deptno having avg(sal)<a.sal); 


8.跟7788在同一个部门并且由同一个直接领导领导的员工的员工姓名和员工工资
select ename,sal from emp where deptno in(select deptno from emp where empno =7788) and mgr in(select mgr from emp where empno =7788) and empno <>7788


select ename,sal from emp where (deptno,mgr) in(select deptno,mgr from emp where empno =7788) and empno <>7788


select ename,sal from emp a where exists(select 1 from emp where deptno = a.deptno and mgr = a.mgr and empno =7788) and empno<>7788


9.将部门名称和员工名称合并到一列多行，再追加一列用‘a’ 表示 部门，用‘b’表示员工   第一列降序，第二列升序排列
select dname,'a' from dept
union 
select ename ,'b' from emp order by 1 desc,2 


select dname,'a' as b from dept
union 
select ename ,'b' from emp order by dname desc,b 


select dname,'a' from dept
union 
select ename ,'b' from emp order by dname desc,"'A'"






10.根据emp表创建表emp1，将员工工号为7788的工资加100
drop table emp1
create table emp1 as select * from emp
update emp1 set sal = sal + 100 where empno =7788


11.根据dept表创建表dept1，emp1增加一列dname，将所在部门（dept1）的部门名称更新到emp1中


drop table dept1;
create table dept1 as select * from dept;
alter table emp1 add dnam varchar(20);
alter table emp1 rename column dnam to dname
update emp1 set dname = (select dname from dept1 where deptno = emp1.deptno)


update emp1 set dname = (select dname from dept1 where deptno = emp1.deptno)
where exists(select 1 from dept1 where deptno = emp1.deptno)






12.在emp1表中将员工工号为7788 的员工删除掉
delete from emp1 where empno =7788
delete  emp1 where empno =7788


13.在emp1表中将部门名称为“SALES”的员工都给删除掉
delete from emp1 where deptno in(select deptno from dept1 where dname ='SALES')
delete from emp1 where exists(select 1 from dept1 where deptno = emp1.deptno and dname ='SALES')
create view vi_1 as select empno,emp1.deptno,dept1.dname from  emp1,dept1 where emp1.deptno = dept1.deptno and dept1.dname ='SALES'
delete  from vi_1