对scott中emp表进行各种操作
1，显示每个雇员的年工资
select sal*12 年工资 from emp;
2，显示每个雇员的年收入
select empno,ename,(sal+nvl(comm,0))*12 年收入 from emp;
3，显示工资高于3000的员工
select empno,ename,sal from emp where sal>3000;
4，如何查找1982.1.1后入职的员工
select empno,ename,sal,HIREDATE from emp where HIREDATE>'1-1月-1982';
5，如何显示工资在2000到2500的员工情况
select empno,ename,sal,HIREDATE from emp where sal between 2000 and 2500;
6，如何显示首字符为S的员工姓名和工资
select empno,ename,sal,HIREDATE from emp where ename like 'S%';
7，如何显示第三个字符为大写O的所有员工的姓名和工资
select empno,ename,sal,HIREDATE from emp where ename like '__O%';
8,如何显示empno为 123,345,800...的雇员情况
select empno,ename,job,sal from emp where empno in (7369,7499,7521);
9,如何显示没有上级的雇员的情况
select empno,ename,job,sal from emp where mgr is null;
10,查询工资高于500或是岗位为MANAGER的雇员，同时还要满足他们的姓名首写字母为大写的J
SQL> select ename,sal,job from emp where (sal>500 or job='MANAGER') and ename like 'J%';
ENAME SAL JOB
---------- --------- ---------
JONES 2975.00 MANAGER
JAMES 950.00 CLERK
11,如何按照工资的从低到高的顺序显示雇员的信息
SQL> select ename,sal from emp order by sal;
ENAME SAL
---------- ---------
SMITH 800.00
JAMES 950.00
ADAMS 1100.00
WARD 1250.00
MARTIN 1250.00
MILLER 1300.00
TURNER 1500.00
ALLEN 1600.00
CLARK 2450.00
BLAKE 2850.00
JONES 2975.00
SCOTT 3000.00
FORD 3000.00
KING 5000.00
12,按照部门号升序而雇员的入职时间降序排列
SQL> select empno,HIREDATE from (select empno,HIREDATE from emp order by empno) order by HIREDATE desc;
EMPNO HIREDATE
----- -----------
7876 1987/5/23
7788 1987/4/19
7934 1982/1/23
7902 1981/12/3
7900 1981/12/3
7839 1981/11/17
7654 1981/9/28
7844 1981/9/8
7782 1981/6/9
7698 1981/5/1
7566 1981/4/2
7521 1981/2/22
7499 1981/2/20
7369 1980/12/17
13,使用列的别名排序
SQL> select ename,sal*12 年薪 from emp order by 年薪 asc;
ENAME 年薪
---------- ----------
SMITH 9600
JAMES 11400
ADAMS 13200
WARD 15000
MARTIN 15000
MILLER 15600
TURNER 18000
ALLEN 19200
CLARK 29400
BLAKE 34200
JONES 35700
SCOTT 36000
FORD 36000
KING 60000
14,如何显示所有员工中最高工资和最低工资 和相应的名字和工资
SQL> select ename,sal from emp where sal=(select max(sal) from emp) or sal=(select min(sal) from emp);
ENAME SAL
---------- ---------
SMITH 800.00
KING 5000.00
15,显示所有员工的平均工资和工资总和
SQL> select avg(sal),sum(sal) from emp;
AVG(SAL) SUM(SAL)
---------- ----------
2073.21428 29025
16，计算共有多少员工
SQL> select count(ename) from emp;
COUNT(ENAME)
------------
14
17，请显示工资最高的员工的名字，工作岗位
SQL> select ename,job from emp where sal=(select max(sal) from emp);
ENAME JOB
---------- ---------
KING PRESIDENT
18，请显示工资高于平均工资的员工信息
SQL> select empno,ename,sal from emp where sal>(select avg(sal) from emp);
EMPNO ENAME SAL
----- ---------- ---------
7566 JONES 2975.00
7698 BLAKE 2850.00
7782 CLARK 2450.00
7788 SCOTT 3000.00
7839 KING 5000.00
7902 FORD 3000.00
19，如何显示每个部门的平均工资和最高工资
SQL> select avg(sal),max(sal) from emp group by deptno;
AVG(SAL) MAX(SAL)
---------- ----------
1566.66666 2850
2175 3000
2916.66666 5000
20，显示每个部门的每种岗位的平均工资和最低工资
SQL> select avg(sal),min(sal),job,deptno from emp group by deptno,job order by deptno;
AVG(SAL) MIN(SAL) JOB DEPTNO
---------- ---------- --------- ------
1300 1300 CLERK 10
2450 2450 MANAGER 10
5000 5000 PRESIDENT 10
3000 3000 ANALYST 20
950 800 CLERK 20
2975 2975 MANAGER 20
950 950 CLERK 30
2850 2850 MANAGER 30
1400 1250 SALESMAN 30
21，显示平均工资低于2000的部门号和它的平均工资
SQL> select deptno,avg(sal) from emp group by deptno having avg(sal)<2000;
DEPTNO AVG(SAL)
------ ----------
30 1566.66666
22，显示雇员名,雇员工资及所在部门的名字
SQL> select ename,sal,dname from emp,dept where emp.deptno=dept.deptno;
ENAME SAL DNAME
---------- --------- --------------
CLARK 2450.00 ACCOUNTING
KING 5000.00 ACCOUNTING
MILLER 1300.00 ACCOUNTING
JONES 2975.00 RESEARCH
FORD 3000.00 RESEARCH
ADAMS 1100.00 RESEARCH
SMITH 800.00 RESEARCH
SCOTT 3000.00 RESEARCH
WARD 1250.00 SALES
TURNER 1500.00 SALES
ALLEN 1600.00 SALES
JAMES 950.00 SALES
BLAKE 2850.00 SALES
MARTIN 1250.00 SALES
23，显示各个员工的姓名，工资，及其工资的级别
SQL> select a.ename,a.sal,b.grade from emp a,salgrade b where a.sal between b.losal and b.hisal;
ENAME SAL GRADE
---------- --------- ----------
SMITH 800.00 1
JAMES 950.00 1
ADAMS 1100.00 1
WARD 1250.00 2
MARTIN 1250.00 2
MILLER 1300.00 2
TURNER 1500.00 3
ALLEN 1600.00 3
CLARK 2450.00 4
BLAKE 2850.00 4
JONES 2975.00 4
SCOTT 3000.00 4
FORD 3000.00 4
KING 5000.00 5
24，显示雇员名,雇员工资及所在部门的名字,并按部门排序.
SQL> select a.ename,a.sal,b.dname,a.deptno from emp a,dept b where a.deptno=b.deptno order by a.deptno;
ENAME SAL DNAME DEPTNO
---------- --------- -------------- ------
CLARK 2450.00 ACCOUNTING 10
KING 5000.00 ACCOUNTING 10
MILLER 1300.00 ACCOUNTING 10
JONES 2975.00 RESEARCH 20
FORD 3000.00 RESEARCH 20
ADAMS 1100.00 RESEARCH 20
SMITH 800.00 RESEARCH 20
SCOTT 3000.00 RESEARCH 20
WARD 1250.00 SALES 30
TURNER 1500.00 SALES 30
ALLEN 1600.00 SALES 30
JAMES 950.00 SALES 30
BLAKE 2850.00 SALES 30
MARTIN 1250.00 SALES 30
25，显示员工的上级领导的姓名，比如显示’FORD’的上级.
SQL> select empno,ename from emp where empno=(select mgr from emp where ename='FORD');
EMPNO ENAME
----- ----------
7566 JONES
26，显示各员工的姓名和他的上级领导姓名.
SQL> select a.ename,b.ename from emp a,emp b where a.mgr=b.empno;
ENAME ENAME
---------- ----------
FORD JONES
SCOTT JONES
TURNER BLAKE
ALLEN BLAKE
WARD BLAKE
JAMES BLAKE
MARTIN BLAKE
MILLER CLARK
ADAMS SCOTT
BLAKE KING
JONES KING
CLARK KING
SMITH FORD
27，如何显示与SMITH同一部门的所有员工?
SQL> select ename from emp where deptno=(select deptno from emp where ename='SMITH');
ENAME
----------
SMITH
JONES
SCOTT
ADAMS
FORD
28，如何查询和部门10的工作相同的雇员的名字、岗位、工资、部门号
SQL> select * from emp where job in (select distinct job from emp where deptno=10);
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
7782 CLARK MANAGER 7839 1981/6/9 2450.00 10
7698 BLAKE MANAGER 7839 1981/5/1 2850.00 30
7566 JONES MANAGER 7839 1981/4/2 2975.00 20
7839 KING PRESIDENT 1981/11/17 5000.00 10
7934 MILLER CLERK 7782 1982/1/23 1300.00 10
7900 JAMES CLERK 7698 1981/12/3 950.00 30
7876 ADAMS CLERK 7788 1987/5/23 1100.00 20
7369 SMITH CLERK 7902 1980/12/17 800.00 20
29，如何显示工资比部门30的所有员工的工资高的员工的姓名、工资和部门号
SQL> select ename,sal,deptno from emp where sal>all(select sal from emp where deptno=30);
ENAME SAL DEPTNO
---------- --------- ------
JONES 2975.00 20
SCOTT 3000.00 20
FORD 3000.00 20
KING 5000.00 10
30，如何显示工资比部门30的任意一个员工的工资高的员工的姓名、工资和部门号
SQL> select ename,sal,deptno from emp where sal>any(select sal from emp where deptno=30);
ENAME SAL DEPTNO
---------- --------- ------
KING 5000.00 10
FORD 3000.00 20
SCOTT 3000.00 20
JONES 2975.00 20
BLAKE 2850.00 30
CLARK 2450.00 10
ALLEN 1600.00 30
TURNER 1500.00 30
MILLER 1300.00 10
WARD 1250.00 30
MARTIN 1250.00 30
ADAMS 1100.00 20
31，查询与smith的部门和岗位完全相同的所有雇员
SQL> select * from emp where (deptno,job) = (select deptno,job from emp where ename='SMITH');
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
7369 SMITH CLERK 7902 1980/12/17 800.00 20
7876 ADAMS CLERK 7788 1987/5/23 1100.00 20
32，如何显示高于自己部门平均工资的员工的信息
SQL> select a.*,b.myavg from emp a,(select avg(sal) myavg,deptno from emp group by deptno) b where a.deptno=b.deptno and a.sal>b.myavg;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO MYAVG
----- ---------- --------- ----- ----------- --------- --------- ------ ----------
7499 ALLEN SALESMAN 7698 1981/2/20 1600.00 300.00 30 1566.66666
7566 JONES MANAGER 7839 1981/4/2 2975.00 20 2175
7698 BLAKE MANAGER 7839 1981/5/1 2850.00 30 1566.66666
7788 SCOTT ANALYST 7566 1987/4/19 3000.00 20 2175
7839 KING PRESIDENT 1981/11/17 5000.00 10 2916.66666
7902 FORD ANALYST 7566 1981/12/3 3000.00 20 2175
33，查找每个部门工资最高的人的详细资料
SQL> select * from emp a where sal=(select max(sal) from emp where deptno=a.deptno);
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
7698 BLAKE MANAGER 7839 1981/5/1 2850.00 30
7788 SCOTT ANALYST 7566 1987/4/19 3000.00 20
7839 KING PRESIDENT 1981/11/17 5000.00 10
7902 FORD ANALYST 7566 1981/12/3 3000.00 20
