Oracle入门经典语句.doc

s 查询实训一、使用scott/tiger用户下的emp表和dept表完成下列练习Emp员工表empnoenamejobMgrHiredateSalCommDeptno员工号员工姓名工作上级编号受雇日期薪金佣金部门编号Dept表DeptnoDnameLoc部门编号部门名称地点1列出至少有一个员工的所有部门。select deptno,count(deptno) from emp group by deptno having count(deptno)=1;2列出薪金比“SMITH”多的所有员工。select ename from emp where sal in( select sal from emp where sal(select sal from emp where ename=SMITH);3列出所有员工的姓名及其直接上级的姓名。select a.empno,a.ename,b.empno,b.ename from emp a,emp b where a.mgr=b.empno4列出受雇日期早于其直接上级的所有员工。select a.empno,a.ename,a.hiredate,b.empno,b.ename,b.hiredate from emp a,emp b where a.hiredateb.hiredate;5列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门。select dept.deptno,dept.dname,emp.deptno,emp.ename from dept left join emp on dept.deptno=emp.deptno;6列出所有“CLERK”（办事员）的姓名及其部门名称。select ename,(select dname from dept where deptno=emp.deptno) from emp where job=CLERK;7列出最低薪金大于1500的各种工作。select job,min(sal) from emp group by job having min(sal)1500;8列出在部门“SALES”（销售部）工作的员工的姓名，假定不知道销售部的部门编号。select ename from emp where deptno=(select deptno from dept where dname=SALES);9列出薪金高于公司平均薪金的所有员工。select empno,ename,sal from emp where sal(select avg(sal) from emp);10列出与“SCOTT”从事相同工作的所有员工。select empno,ename,job from emp where job=(select job from emp where ename=SCOTT);11列出薪金等于部门30中员工的薪金的所有员工的姓名和薪金。select empno,sal from emp where sal=any(select sal from emp where deptno=30) and deptno30;12列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金。select ename,sal from emp where salany(select max(sal) from emp where deptno=30) and deptno30;13列出在每个部门工作的员工数量、平均工资和平均服务期限。select count(empno),avg(sal+comm),floor(avg(sysdate-hiredate)/365) from emp;14列出所有员工的姓名、部门名称和工资。select ename,(select dname from dept where deptno=emp.deptno) 部门,sal+comm 工资 from emp;15列出从事同一种工作但属于不同部门的员工的一种组合。select a.ename,a.job,b.job,b.ename from emp a,emp b where a.job=b.job and a.deptnob.deptno;16列出所有部门的详细信息和部门人数。select deptno,dname,loc,(select count(empno) from emp where deptno=dept.deptno) 部门人数 from dept;17列出各种工作的最低工资。select min(sal+nvl(comm,0) 工资,deptno from emp group by deptno;18列出各个部门的MANAGER（经理）的最低薪金。select min(sal),deptno from emp where job=MANAGER group by deptno;19列出所有员工的年工资,按年薪从低到高排序。select ename,(nvl(comm,0)+sal)*12 年薪 from emp order by 年薪 asc;二、使用scott/tiger用户完成以下问题：1、创建表myemp和emp表具有相同的结构和记录。create table myemp as select * from emp;2、给myemp的empno列添加主建约束。alter table myemp add constraint p_empno primary key(empno);3、给myemp添加一条记录。insert into myempvalues(1234,李白,CLERK,7369,sysdate,2340,350,40);4、给myemp添加一条记录只有empno,ename,mgr,sal，deptno有值，其他列为空。insert into myempvalues(1235,李清照,7369,1000,40);5、显示所有薪金高于公司均薪金的人。select ename,sal from emp where sal(select avg(sal) from emp);6、显示所有薪金高于各自部门平均薪金的人。select ename,sal,deptno from emp where sal in(select max(sal) from emp group by deptno);7、给所有10部门的经理（MANAGER）和20部门的职员(CLERK)，增加薪金10%。select sal+sal*0.1 修改后 from emp where job=MANAGER or job=CLERK and deptno=20 or deptno=10;8、使用EMP,DEPT表显示所有销售部SALES的员工。9、删除DEPT中没有员工的部门。delete dept where deptno in(select dept.deptno from dept left join emp on dept.deptno =emp.deptno where ename is null);10、显示所有的部门信息和人数。select * from emp,dept where emp.deptno=dept.deptno;11、删除雇佣年限低于20年的员工。12、显示各部门员工薪金最高的前2名select * from (select deptno, ename, sal, rank() over ( partition by dept