Oracle练习:有关Oracle用户scott的sql练习

1、列出薪金比“SMITH”多的所有员工。
select *from emp where sal+nvl(comm,0)>(select sal+nvl(comm,0) from emp where ename='ALLEN');

2、列出所有员工的姓名及其直接上级的姓名。
select ename as "员工姓名",(select ename as "上司姓名" from emp where empno in(a.mgr)) from emp a;
select m1.ename as "员工姓名",m2.ename as "上司姓名" from emp m1 left join emp m2 on m1.mgr=m2.empno;

3、列出受雇日期早于其直接上级的所有员工
Select m1.ename,m1.hiredate,m2.ename,m2.hiredate from emp m1,emp m2 where m1.hiredate<m2.hiredate and m1.mgr=m2.empno ;

4、列出薪资高于公司平均员工的所有信息
select *from emp where sal+nvl(comm,0)>(select avg(sal+nvl(comm,0)) from emp);

5、列出最低薪资大于1500的工作
select job from emp where sal+nvl(comm,0)>1500;

6、列出在部门'SALES'工作的所有员工，假设不知道部门的编号
select *from emp where deptno = (select deptno from dept where dname='SALES');

7、列出与"scott"从事相同工作的员工的信息，但是排除它本身
select *from emp where job = (select job from emp where ename='SCOTT') and ename!='SCOTT';

8、列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金
select *from emp where sal+nvl(comm,0)>(select max(sal+nvl(comm,0)) from emp where deptno=30);

9、列出在每个部门工作的员工的数量、平均工资、平均服役期
select count(*) as "员工数量", avg(sal+nvl(comm,0)) as "平均工资",avg(sysdate-hiredate) as "平均服役期" from emp group by deptno;

10、列出所有员工的姓名、部门名称和工资
select ename,dept.dname,sal+nvl(comm,0) from emp,dept where emp.deptno=dept.deptno;

11、列出每种工作的最低工资
select job, min(sal+nvl(comm,0)) from emp group by job;

12、检索员工表中员工姓名、员工工资、部门编号
select ename as "员工姓名",sal+nvl(comm,0) as 员工工资,deptno as "部门编号" from emp;

13、检索员工表中的员工姓名和雇用时间
select ename as "员工姓名",to_char(hiredate,'YYYY-MM-DD') as "雇用时间" from emp;

14、检索员工表中的员工姓名和全年收入
select ename  "员工姓名", (sal+nvl(comm,0))*12 "年薪" from emp;

15、检索员工表emp中的员工姓名和职位，显示成  某某员工的职位是什么
select ename || ' 的工作是 ' || job from emp;

16、检索月收入在1000到2000的员工姓名、职位以及雇用时间
select ename,job,sal,hiredate from emp where sal between 1000 and 2000;

17、检索员工姓名中低三个字符是A的员工的姓名、职位、工资和雇用时间
select ename ,job,sal+nvl(comm,0), hiredate from emp where ename like '__L%';

18、检索月收入在800或1250的员工信息
select *from emp where sal+nvl(comm,0) in (800,1250);

19、检索emp表中有提成的员工的信息
select *from emp where comm is not null;

20、检索emp表中部门编号是30的员工姓名、月收入及提成，并要求其结果按月收入升序、然后按提成降序显示。
select ename "员工姓名",comm "提成",sal "工资" from emp where deptno='30' order by sal asc,comm desc;

21、选择雇用时间在1981-02-01到1981-05-01之间的员工姓名
select ename "姓名",job "工作",to_char(hiredate,'YYYY-MM-DD') "雇用时间",sal "工资" from emp
where hiredate between to_date('1981-02-01','YYYY-MM-DD') and to_date('1981-05-01','YYYY-MM-DD');