Oracle_SQL练习_08

--1、创建表myemp和emp表具有相同的结构和记录。create table myemp as select *  from emp;--2、给myemp的empno列添加主建约束。alter table myemp add constraint pk_myemp primary key(empno);--3、给myemp添加一条记录。insert into myemp(empno,ename...) values()--4、给myemp添加一条记录只有empno,ename,mgr,sal，deptno有值，其他列为空。insert into myemp(empno, ename, mgr, sal, deptno) values()--5、显示所有  薪金高于公司均薪金的人。SELECT * FROM emp WHERE nvl(sal, 0) > (SELECT AVG(nvl(sal, 0)) FROM emp)--6、显示所有  薪金高于各自部门平均薪金的人。   关联子查询*******SELECT *FROM   empINNER  JOIN (SELECT round(AVG(nvl(sal, 0)), 2) avgsal, deptno             FROM   emp             GROUP  BY deptno) t1ON     emp.deptno = t1.deptnoWHERE  nvl(sal, 0) > avgsal--7、给所有10部门的经理（MANAGER）和20部门的职员(CLERK)，增加薪金10%。UPDATE empSET    sal = sal * 1.1WHERE  (deptno = 10 AND job = 'MANAGER')       OR (deptno = 20 AND job = 'CLERK')--8、使用EMP,DEPT表显示所有销售部'SALES'的员工。select * from emp where job = 'SALESMAN'--9、删除DEPT中没有员工的部门。DELETE FROM dept WHERE deptno NOT IN (SELECT DISTINCT deptno FROM emp)--10、显示所有的部门信息和人数。******SELECT t1.deptno, t1.cnt 员工人数, dept.dname, dept.locFROM   (SELECT COUNT(*) CNT, deptno FROM emp GROUP BY deptno) t1INNER  JOIN deptON     dept.deptno = t1.deptno--11、删除雇佣年限低于20年的员工。DELETE FROM emp WHERE months_between(SYSDATE, hiredate) < 20 * 12--12、显示各部门员工薪金最高的前2名SELECT *FROM   (SELECT emp.*,               row_number() over(PARTITION BY deptno ORDER BY nvl(sal, 0) DESC) grade        FROM   emp) t1WHERE  t1.grade <= 2--13、显示薪金最高的3位员工SELECT *FROM   (SELECT emp.*, row_number() over(ORDER BY nvl(sal, 0) DESC) grade        FROM   emp) t1WHERE  t1.grade <= 3--14、为EMP的各字段建立必要的索引，使得如下查询能使用上索引--   select * from emp where ename like 'S%';  --   select * from emp where job='MANAGER';--   select * from emp where hiredate>to_date('1982','yyyy');