Oracle之库表操作

--1、创建表myemp和emp表具有相同的结构和记录。
create table myemp as
select * from emp;
CREATE TABLE MYDEPT AS SELECT* FROM DEPT;
select* from myemp


--2、给myemp的empno列添加主建约束。
alter table MYEMP
  add constraint MYEMP_PK primary key (EMPNO)


--3、给myemp添加一条记录。
insert into  MYEMP(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO)
 VALUES (001,'AA',NULL,NULL,SYSDATE,0,0,NULL)


--4、给myemp添加一条记录只有empno,ename,mgr,sal，deptno有值，其他列为空。


INSERT INTO MYEMP(empno,ename,mgr,sal，deptno)
VALUES (002,'HH',004,0,10)


--5、显示所有薪金高于公司均薪金的人。
SELECT * FROM MYEMP  WHERE SAL>(
SELECT avg(sal)avgsal FROM MYEMP
)


--6、显示所有薪金高于各自部门平均薪金的人。   关联子查询*******
SELECT * FROM MYEMP E1 WHERE SAL>(
SELECT AVG(SAL) FROM MYEMP E2 WHERE E2.DEPTNO=E1.DEPTNO
)


--7、给所有10部门的经理（MANAGER）和20部门的职员(CLERK)，增加薪金10%。
UPDATE MYEMP
SET SAL=1.1*SAL WHERE DEPTNO=10 AND JOB='MANAGER'
OR DEPTNO =20 AND JOB='CLERK';




--8、使用EMP,DEPT表显示所有销售部'SALES'的员工。
SELECT * FROM MYemp where exists(
select * from mydept where mydept.deptno=myemp.deptno and mydept.dname=upper('sales')
) 


--9、删除DEPT中没有员工的部门。
delete from mydept where  not exists(
select  * from myemp where myemp.deptno is not null and myemp.deptno=mydept.deptno
) 


delete from mydept where deptno mot in (select  deptno from myemp)


--10、显示所有的部门信息和人数。（多表查，注意是所有部门 40号也要显示）
select mydept.*,nvl(cnt,0)coun from mydept left join   
(select deptno, count(*)cnt from emp group by deptno 
)t on t.deptno=mydept.deptno 






--11、删除雇佣年限低于20年的员工。
--delete from myemp where (sysdate-hiredate)<20*365；不准确
delete from emp where month_between(SYSDATE,hiredate) < 20*12;


--12、显示各部门员工薪金最高的前2名
select * from (select ename, deptno, sal, row_number() over(partition by deptno order by sal desc) r from emp ) where r <=2




select ename,deptno,sal ,row_number()over(partition by deptno order by sal desc)k from emp


--13、显示薪金最高的3位员工


select rownum,t.* from
(select myemp.* from myemp order by sal desc)t where rownum<4


--14、为EMP的各字段建立必要的索引，使得如下查询能使用上索引
create index ix$_emp_ename on emp(ename);
select * from emp where ename like 'S%';


create index ix$_emp_job on emp(job);
select * from emp where job='MANAGER';


create index ix$_emp_hiredate on emp(hiredate);
select * from emp where hiredate>to_date('1982','yyyy');