SQL 面试题 一 (有关内连接、左、右连接、最大最小值)

题目一：
有两张表：部门表department 部门编号dept_id 部门名称dept_name
员工表employee 员工编号emp_id 员工姓名emp_name 部门编号dept_id 工资emp_wage
根据下列题目写出sql：
1、列出工资大于5000的员工所属的部门名、员工id和员工工资； 2、列出员工表中的部门id对应的名称和员工id（左连接） 3、列出员工大于等于2人的部门编号 4、列出工资最高的员工姓名 5、求各部门的平均工资 6、求各部门的员工工资总额 7、求每个部门中的最大工资值和最小工资值，并且它的最小值小于5000，最大值大于10000 8、假如现在在库中有一个和员工表结构相同的空表employee2,请用一条sql语句将employee表中的所以记录插入到employee2表中。 answer:

--------------------------------------------------------------------------------

1:列出工资大于5000的员工所属的部门名、员工id和员工工资；

select emp_id,emp_wage,dept_name from employee as e inner join department as d on e.dept_id=d.dept_id where e.emp_wage>5000 group by e.emp_id;


--------------------------------------------------------------------------------

2:列出员工表中的部门id对应的名称和员工id（左连接）

select dept_name,emp_id from department d left join employee e on e.dept_id=d.dept_id group by e.emp_id;

+------------+--------+
| dept_name | emp_id |
+------------+--------+
| 咨询部 | NULL |
| 软件开发部 | 1001 |
| 市场策划部 | 1002 |
| 销售部 | 1003 |
| HR | 1004 |
| HR | 1005 |
| HR | 1006 |
| 软件开发部 | 1007 |
+------------+--------+


--------------------------------------------------------------------------------

3：列出员工大于等于2人的部门编号

select dept_name from department d [inner] join employee e on d.dept_id=e.dept_id group by dept_name
having count(e.dept_id) >=2;


--------------------------------------------------------------------------------

4:列出工资最高的员工姓名

select * from employee
where emp_wage =(select max(emp_wage) from employee);
+--------+----------+---------+----------+
| emp_id | emp_name | dept_id | emp_wage |
+--------+----------+---------+----------+
| 1007 | ad | 1 | 12000 |
+--------+----------+---------+----------+

--------------------------------------------------------------------------------

5: 求各部门的平均工资

select dept_name,AVG(emp_wage) as '平均工资' from employee e join department d on e
.dept_id=d.dept_id Group by dept_name;
+------------+------------+
| dept_name | 平均工资 |
+------------+------------+
| HR | 7500.0000 |
| 市场策划部 | 2500.0000 |
| 软件开发部 | 10000.0000 |
| 销售部 | 3200.0000 |
+------------+------------+
如果用右连接：
select dept_name,AVG(emp_wage) as '平均工资' from employee e right
join department d on e.dept_id=d.dept_id Group by dept_name;
+------------+------------+
| dept_name | 平均工资 |
+------------+------------+
| HR | 7500.0000 |
| 咨询部 | NULL |
| 市场策划部 | 2500.0000 |
| 软件开发部 | 10000.0000 |
| 销售部 | 3200.0000 |
+------------+------------+


--------------------------------------------------------------------------------



6：求各部门的员工工资总额
select dept_name,SUM(emp_wage) as '部门工资总额' from employee e join department d on e.dept_id=d.dept_id
Group by dept_name;

--------------------------------------------------------------------------------
7：求每个部门中的最大工资值和最小工资值，并且它的最小值小于5000，最大值大于10000
select dept_name ,max(emp_wage)from employee e inner join department d on e.dept_id=d.dept_id
where emp_wage>=10000
group by dept_name
union all
select dept_name ,
min(emp_wage) from employee e inner join department d on e.dept_id=d.dept_id
where emp_wage<=5000
group by dept_name;
如果是求每个部门中的最大工资值和最小工资值-->
select dept_name ,max(emp_wage),min(emp_wage) from employee e inner join department d on e.dept_id=d.dept_id group by dept_name;


--------------------------------------------------------------------------------

8：假如现在在库中有一个和员工表结构相同的空表employee2,请用一条sql语句将employee表中的所以记录插入到employee2表中。
insert into employee2 select * from employee;


二
--编写多表查询语句的一般过程
--（1）、分析句子要涉及到哪些表
--（2）、对应的表中要查询哪些关联字段
--（3）、确定连接条件或筛选条件
--（4）、写成完整的SQL查询语句

--1、查询出每一位雇员的姓名、职位、以及领导的姓名。
SELECT e.ename 雇佣姓名,e.job 职位,m.ename 领导姓名 FROM EMP e,EMP m WHERE e.mgr = m.empno;
--结果
雇佣姓名 职位 领导姓名
---------- --------- ----------
FORD ANALYST JONES
SCOTT ANALYST JONES
TURNER SALESMAN BLAKE
ALLEN SALESMAN BLAKE
WARD SALESMAN BLAKE
JAMES CLERK BLAKE
MARTIN SALESMAN BLAKE
MILLER CLERK CLARK
ADAMS CLERK SCOTT
BLAKE MANAGER KING
JONES MANAGER KING
CLARK MANAGER KING
SMITH CLERK FORD

13 rows selected

--2、查询每个雇佣的雇佣编号，姓名、基本工资、职位、领导的姓名、部门名称及位置
SELECT e.empno 编号,e.ename 姓名,e.sal 工资,e.job 职位,m.ename 领导姓名,d.dname 部门名称,d.loc 所在位置 FROM emp e,emp m,dept d WHERE m.empno=e.mgr AND m.deptno = d.deptno;
--结果
编号 姓名 工资 职位 领导姓名 部门名称 所在位置
----- ---------- --------- --------- ---------- -------------- -------------
7369 SMITH 800.00 CLERK FORD RESEARCH DALLAS
7499 ALLEN 1600.00 SALESMAN BLAKE SALES CHICAGO
7521 WARD 1250.00 SALESMAN BLAKE SALES CHICAGO
7566 JONES 2975.00 MANAGER KING ACCOUNTING NEW YORK
7654 MARTIN 1250.00 SALESMAN BLAKE SALES CHICAGO
7698 BLAKE 2850.00 MANAGER KING ACCOUNTING NEW YORK
7782 CLARK 2450.00 MANAGER KING ACCOUNTING NEW YORK
7788 SCOTT 3000.00 ANALYST JONES RESEARCH DALLAS
7844 TURNER 1500.00 SALESMAN BLAKE SALES CHICAGO
7876 ADAMS 1100.00 CLERK SCOTT RESEARCH DALLAS
7900 JAMES 950.00 CLERK BLAKE SALES CHICAGO
7902 FORD 3000.00 ANALYST JONES RESEARCH DALLAS
7934 MILLER 1300.00 CLERK CLARK ACCOUNTING NEW YORK

13 rows selected

--要求查询出每一个雇员的编号、姓名、工资、部门名称、工资在所在公司的工资等级
SELECT e.empno,e.ename,e.sal,d.dname,sg.grade FROM emp e,dept d,salgrade sg WHERE e.deptno = d.deptno AND sg.hisal >=e.sal AND sg.losal <=e.sal;
--结果

EMPNO ENAME SAL DNAME GRADE
----- ---------- --------- -------------- ----------
7369 SMITH 800.00 RESEARCH 1
7900 JAMES 950.00 SALES 1
7876 ADAMS 1100.00 RESEARCH 1
7654 MARTIN 1250.00 SALES 2
7521 WARD 1250.00 SALES 2
7934 MILLER 1300.00 ACCOUNTING 2
7844 TURNER 1500.00 SALES 3
7499 ALLEN 1600.00 SALES 3
7782 CLARK 2450.00 ACCOUNTING 4
7698 BLAKE 2850.00 SALES 4
7566 JONES 2975.00 RESEARCH 4
7902 FORD 3000.00 RESEARCH 4
7788 SCOTT 3000.00 RESEARCH 4
7839 KING 5000.00 ACCOUNTING 5

14 rows selected

--或者
SELECT e.empno,e.ename,e.sal,d.dname,sg.grade FROM emp e,dept d,salgrade sg WHERE e.deptno = d.deptno AND e.sal BETWEEN sg.losal AND sg.hisal;
--结果
EMPNO ENAME SAL DNAME GRADE
----- ---------- --------- -------------- ----------
7839 KING 5000.00 ACCOUNTING 5
7902 FORD 3000.00 RESEARCH 4
7788 SCOTT 3000.00 RESEARCH 4
7566 JONES 2975.00 RESEARCH 4
7698 BLAKE 2850.00 SALES 4
7782 CLARK 2450.00 ACCOUNTING 4
7499 ALLEN 1600.00 SALES 3
7844 TURNER 1500.00 SALES 3
7934 MILLER 1300.00 ACCOUNTING 2
7521 WARD 1250.00 SALES 2
7654 MARTIN 1250.00 SALES 2
7876 ADAMS 1100.00 RESEARCH 1
7900 JAMES 950.00 SALES 1
7369 SMITH 800.00 RESEARCH 1

--还或者
SELECT e.empno,e.ename,e.sal,d.dname,sg.grade,DECODE(sg.grade,'1'，'第一等级工资','2'，'第二等级工资','3'，'第三等级工资','4'，'第四等级工资','5'，'第五等级工资') 等级 FROM emp e,dept d,salgrade sg WHERE e.deptno = d.deptno AND e.sal BETWEEN sg.losal AND sg.hisal;
--结果
EMPNO ENAME SAL DNAME GRADE 等级
----- ---------- --------- -------------- ---------- ------------
7839 KING 5000.00 ACCOUNTING 5 第五等级工资
7902 FORD 3000.00 RESEARCH 4 第四等级工资
7788 SCOTT 3000.00 RESEARCH 4 第四等级工资
7566 JONES 2975.00 RESEARCH 4 第四等级工资
7698 BLAKE 2850.00 SALES 4 第四等级工资
7782 CLARK 2450.00 ACCOUNTING 4 第四等级工资
7499 ALLEN 1600.00 SALES 3 第三等级工资
7844 TURNER 1500.00 SALES 3 第三等级工资
7934 MILLER 1300.00 ACCOUNTING 2 第二等级工资
7521 WARD 1250.00 SALES 2 第二等级工资
7654 MARTIN 1250.00 SALES 2 第二等级工资
7876 ADAMS 1100.00 RESEARCH 1 第一等级工资
7900 JAMES 950.00 SALES 1 第一等级工资
7369 SMITH 800.00 RESEARCH 1 第一等级工资

14 rows selected

--左右连接
--当(+)在连接条件的左边的时候，表示的是右连接
--当(+)在连接条件的右边的时候，表示的是左连接

--3、查询每个雇员的姓名和领导的姓名
SELECT e.ename 雇员姓名,m.ename 领导姓名 FROM emp e,emp m WHERE m.empno(+) = e.mgr;
--结果
雇员姓名 领导姓名
---------- ----------
FORD JONES
SCOTT JONES
JAMES BLAKE
TURNER BLAKE
MARTIN BLAKE
WARD BLAKE
ALLEN BLAKE
MILLER CLARK
ADAMS SCOTT
CLARK KING
BLAKE KING
JONES KING
SMITH FORD
KING

14 rows selected

--4、统计出领取佣金和不领取佣金的雇员人数和平均工资
SELECT comm,COUNT(empno),AVG(sal) FROM emp GROUP BY comm;--（此语句不完善，那位看了之后又什么想法的话欢迎大家回复）
--结果
COMM COUNT(EMPNO) AVG(SAL)
--------- ------------ ----------
10 2342.5
1400.00 1 1250
500.00 1 1250
300.00 1 1600
0.00 1 1500

--5、按照职位分组，求出每个职位的最高工资、最低工资以及平均工资
SELECT job ,COUNT(empno),MAX(sal),MIN(sal),AVG(sal) FROM emp GROUP BY job;
--结果
JOB COUNT(EMPNO) MAX(SAL) MIN(SAL) AVG(SAL)
--------- ------------ ---------- ---------- ----------
CLERK 4 1300 800 1037.5
SALESMAN 4 1600 1250 1400
PRESIDENT 1 5000 5000 5000
MANAGER 3 2975 2450 2758.33333
ANALYST 2 3000 3000 3000

--6、统计平均工资最高和最低
SELECT MAX(AVG(sal)),MIN(AVG(sal)) FROM EMP GROUP BY job;
--结果
MAX(AVG(SAL)) MIN(AVG(SAL))
------------- -------------
5000 1037.5

--7、查询出每个部门的名称、部门人数、平均工资
SELECT d.dname,COUNT(e.empno),NVL(AVG(e.sal),0) FROM emp e,dept d WHERE d.deptno = e.deptno(+) GROUP BY d.dname;
--结果
DNAME COUNT(E.EMPNO) NVL(AVG(E.SAL),0)
-------------- -------------- -----------------
ACCOUNTING 3 2916.66666666667
OPERATIONS 0 0
RESEARCH 5 2175
SALES 6 1566.66666666667

--8、查询出每个部门的名称、位置、部门人数、平均工资(这是一个多字段分组查询)
SELECT d.deptno,d.dname,d.loc,COUNT(e.empno),NVL(AVG(sal),0) FROM emp e, dept d WHERE d.deptno = e.deptno(+) GROUP BY d.deptno,d.dname,d.loc ;

DEPTNO DNAME LOC COUNT(E.EMPNO) NVL(AVG(SAL),0)
------ -------------- ------------- -------------- ---------------
20 RESEARCH DALLAS 5 2175
40 OPERATIONS BOSTON 0 0
10 ACCOUNTING NEW YORK 3 2916.6666666666
30 SALES CHICAGO 6 1566.6666666666

--9、统计平均工资大于2000的部门的详细信息
SELECT d.*,AVG(e.sal) FROM emp e,dept d WHERE d.deptno = e.deptno GROUP BY d.deptno,d.dname,d.loc HAVING AVG(e.sal)>2000;
--结果
DEPTNO DNAME LOC AVG(E.SAL)
------ -------------- ------------- ----------
20 RESEARCH DALLAS 2175
10 ACCOUNTING NEW YORK 2916.66666


--10、显示非销售人员工资名称及从事同一工作雇员的月工资的总和，并且要满足从事同一工作的雇员的月工资合计大于5000，
--给出的结果按月工资的合计升序排序：
SELECT job,SUM(sal) FROM emp GROUP BY job HAVING SUM(sal)>5000 ORDER BY SUM(sal);
--结果
JOB SUM(SAL)
--------- ----------
SALESMAN 5600
ANALYST 6000
MANAGER 8275


--11、计算出工资比SMITH的要高的员工详细信息
SELECT * FROM EMP WHERE sal > (SELECT sal FROM EMP WHERE ename='SMITH');
--结果
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
7499 ALLEN SALESMAN 7698 20-二月-81 1600.00 300.00 30
7521 WARD SALESMAN 7698 22-二月-81 1250.00 500.00 30
7566 JONES MANAGER 7839 02-四月-81 2975.00 20
7654 MARTIN SALESMAN 7698 28-九月-81 1250.00 1400.00 30
7698 BLAKE MANAGER 7839 01-五月-81 2850.00 30
7782 CLARK MANAGER 7839 09-六月-81 2450.00 10
7788 SCOTT ANALYST 7566 19-四月-87 3000.00 20
7839 KING PRESIDENT 17-十一月-81 5000.00 10
7844 TURNER SALESMAN 7698 08-九月-81 1500.00 0.00 30
7876 ADAMS CLERK 7788 23-五月-87 1100.00 20
7900 JAMES CLERK 7698 03-十二月-81 950.00 30
7902 FORD ANALYST 7566 03-十二月-81 3000.00 20
7934 MILLER CLERK 7782 23-一月-82 1300.00 10

13 rows selected

--12、查询出工资高于公司平均工资的员工详细信息
SELECT * FROM EMP WHERE sal >(SELECT AVG(sal) FROM EMP);
--结果
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
7566 JONES MANAGER 7839 02-四月-81 2975.00 20
7698 BLAKE MANAGER 7839 01-五月-81 2850.00 30
7782 CLARK MANAGER 7839 09-六月-81 2450.00 10
7788 SCOTT ANALYST 7566 19-四月-87 3000.00 20
7839 KING PRESIDENT 17-十一月-81 5000.00 10
7902 FORD ANALYST 7566 03-十二月-81 3000.00 20

6 rows selected


三
--1、选择部门30的所有员工
SELECT * FROM EMP WHERE deptno=30;
--结果
SELECT * FROM EMP WHERE deptno=30;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
7499 ALLEN SALESMAN 7698 20-二月-81 1600.00 300.00 30
7521 WARD SALESMAN 7698 22-二月-81 1250.00 500.00 30
7654 MARTIN SALESMAN 7698 28-九月-81 1250.00 1400.00 30
7698 BLAKE MANAGER 7839 01-五月-81 2850.00 30
7844 TURNER SALESMAN 7698 08-九月-81 1500.00 0.00 30
7900 JAMES CLERK 7698 03-十二月-81 950.00 30

--2、列出所有办事员（CLERK）的姓名，员工编号和部门编号
SELECT ename 姓名,empno 员工编号,deptno 部门编号 FROM emp WHERE job='CLERK';
--结果
姓名 员工编号 部门编号
---------- -------- --------
SMITH 7369 20
ADAMS 7876 20
JAMES 7900 30
MILLER 7934 10

--3、找出佣金高于薪金的员工
SELECT * FROM EMP WHERE NVL(comm,0)>sal;
--结果
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
7654 MARTIN SALESMAN 7698 28-九月-81 1250.00 1400.00 30


--4、找出佣金高于薪金60%的员工
SELECT * FROM EMP WHERE NVL(comm,0)>sal*0.6;
--结果
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
7654 MARTIN SALESMAN 7698 28-九月-81 1250.00 1400.00 30

--5、找出部门10中的所有经理（MANAGER）和部门20的所有办事员（CLERK）的详细资料
SELECT * FROM EMP WHERE (job='MANAGER' AND deptno=10) OR (job='CLERK' AND deptno=20);
--结果
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
7369 SMITH CLERK 7902 17-十二月-80 800.00 20
7782 CLARK MANAGER 7839 09-六月-81 2450.00 10
7876 ADAMS CLERK 7788 23-五月-87 1100.00 20

--6、找出部门10的所有经理（MANAGER），部门20中所有办事员（CLERK），既不是办事员也不是经理但是薪金大于2000的所有员工的详细资料
SELECT * FROM EMP WHERE (job='MANAGER' AND deptno=10) OR (job='CLERK' AND deptno=20) OR (job NOT IN('MANAGER','CLERK') AND sal>2000);
--结果
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
7369 SMITH CLERK 7902 17-十二月-80 800.00 20
7782 CLARK MANAGER 7839 09-六月-81 2450.00 10
7788 SCOTT ANALYST 7566 19-四月-87 3000.00 20
7839 KING PRESIDENT 17-十一月-81 5000.00 10
7876 ADAMS CLERK 7788 23-五月-87 1100.00 20
7902 FORD ANALYST 7566 03-十二月-81 3000.00 20

6 rows selected


--7、找出收取佣金的员工的员工的不同工作
SELECT DISTINCT(job) FROM EMP WHERE comm IS NOT NULL AND comm !=0;
--结果
JOB
---------
SALESMAN

--8、找出不取佣金或者是收取佣金小于100的员工详细信息
SELECT * FROM EMP WHERE comm IS NULL OR comm<100;
--结果
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
7369 SMITH CLERK 7902 17-十二月-80 800.00 20
7566 JONES MANAGER 7839 02-四月-81 2975.00 20
7698 BLAKE MANAGER 7839 01-五月-81 2850.00 30
7782 CLARK MANAGER 7839 09-六月-81 2450.00 10
7788 SCOTT ANALYST 7566 19-四月-87 3000.00 20
7839 KING PRESIDENT 17-十一月-81 5000.00 10
7844 TURNER SALESMAN 7698 08-九月-81 1500.00 0.00 30
7876 ADAMS CLERK 7788 23-五月-87 1100.00 20
7900 JAMES CLERK 7698 03-十二月-81 950.00 30
7902 FORD ANALYST 7566 03-十二月-81 3000.00 20
7934 MILLER CLERK 7782 23-一月-82 1300.00 10

11 rows selected

--9、找出各月的倒数第三天受雇佣的员工
--每个员工的雇佣时间是不一样的，所有需要找出每个员工雇佣的时间所在月份的最后一天，
--之后按照‘日期-数字’的方式求出前三天的日期，这个日期必须和雇佣日期相符才能满足条件。
SELECT LAST_DAY(hiredate),EMP.* FROM EMP WHERE LAST_DAY(hiredate)-2=hiredate;
--结果
LAST_DAY(HIREDATE) EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
------------------ ----- ---------- --------- ----- ----------- --------- --------- ------
30-九月-81 7654 MARTIN SALESMAN 7698 28-九月-81 1250.00 1400.00 30

--10、找出12早于年前受雇的员工
--先使用MONTHS_BETWEEN(SYSDATE,hiredate)求出雇佣的月份，然后除以12的到雇佣的年份
SELECT MONTHS_BETWEEN(SYSDATE,hiredate)/12 雇佣年份,EMP.* FROM EMP WHERE MONTHS_BETWEEN(SYSDATE,hiredate)/12 > 12;
--结果
雇佣年份 EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ----- ---------- --------- ----- ----------- --------- --------- ------
31.5449952 7369 SMITH CLERK 7902 17-十二月-80 800.00 20
31.3702640 7499 ALLEN SALESMAN 7698 20-二月-81 1600.00 300.00 30
31.3648877 7521 WARD SALESMAN 7698 22-二月-81 1250.00 500.00 30
31.25 7566 JONES MANAGER 7839 02-四月-81 2975.00 20
30.7654253 7654 MARTIN SALESMAN 7698 28-九月-81 1250.00 1400.00 30
31.1713393 7698 BLAKE MANAGER 7839 01-五月-81 2850.00 30
31.0665006 7782 CLARK MANAGER 7839 09-六月-81 2450.00 10
25.2062855 7788 SCOTT ANALYST 7566 19-四月-87 3000.00 20
30.6283285 7839 KING PRESIDENT 17-十一月-81 5000.00 10
30.8191887 7844 TURNER SALESMAN 7698 08-九月-81 1500.00 0.00 30
25.1121995 7876 ADAMS CLERK 7788 23-五月-87 1100.00 20
30.5826296 7900 JAMES CLERK 7698 03-十二月-81 950.00 30
30.5826296 7902 FORD ANALYST 7566 03-十二月-81 3000.00 20
30.4455328 7934 MILLER CLERK 7782 23-一月-82 1300.00 10

14 rows selected


--11、以首字母大写的方式显示员工姓名
SELECT INITCAP(ename) FROM emp;
--结果
INITCAP(ENAME)
--------------
Smith
Allen
Ward
Jones
Martin
Blake
Clark
Scott
King
Turner
Adams
James
Ford
Miller

14 rows selected

--12、显示姓名正好为5个长度的所有员工
SELECT * FROM EMP WHERE LENGTH(ename) = 5;
--结果
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
7369 SMITH CLERK 7902 17-十二月-80 800.00 20
7499 ALLEN SALESMAN 7698 20-二月-81 1600.00 300.00 30
7566 JONES MANAGER 7839 02-四月-81 2975.00 20
7698 BLAKE MANAGER 7839 01-五月-81 2850.00 30
7782 CLARK MANAGER 7839 09-六月-81 2450.00 10
7788 SCOTT ANALYST 7566 19-四月-87 3000.00 20
7876 ADAMS CLERK 7788 23-五月-87 1100.00 20
7900 JAMES CLERK 7698 03-十二月-81 950.00 30

8 rows selected

--13、显示带有'R'的员工的详细
SELECT * FROM EMP WHERE ename LIKE '%R%';
--结果
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
7521 WARD SALESMAN 7698 22-二月-81 1250.00 500.00 30
7654 MARTIN SALESMAN 7698 28-九月-81 1250.00 1400.00 30
7782 CLARK MANAGER 7839 09-六月-81 2450.00 10
7844 TURNER SALESMAN 7698 08-九月-81 1500.00 0.00 30
7902 FORD ANALYST 7566 03-十二月-81 3000.00 20
7934 MILLER CLERK 7782 23-一月-82 1300.00 10

6 rows selected

--14、显示员工姓名的前三个字符
SELECT ename 全名,SUBSTR(ename,0,3) 前三个字符 FROM EMP;
--结果
全名 前三个字符
---------- ----------
SMITH SMI
ALLEN ALL
WARD WAR
JONES JON
MARTIN MAR
BLAKE BLA
CLARK CLA
SCOTT SCO
KING KIN
TURNER TUR
ADAMS ADA
JAMES JAM
FORD FOR
MILLER MIL

14 rows selected

--15、显示所有员工的姓名，用‘a’替换所有的'A'
SELECT ename 原名,REPLACE(ename,'A','a') 大A替换为小a FROM EMP;
--结果
原名 大A替换为小A
---------- ------------
SMITH SMITH
ALLEN aLLEN
WARD WaRD
JONES JONES
MARTIN MaRTIN
BLAKE BLaKE
CLARK CLaRK
SCOTT SCOTT
KING KING
TURNER TURNER
ADAMS aDaMS
JAMES JaMES
FORD FORD
MILLER MILLER

14 rows selected

--16、--列出满10年雇佣期限的员工的详细信息
SELECT * FROM EMP WHERE MONTHS_BETWEEN(SYSDATE,hiredate)/12>10;
--结果
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
7369 SMITH CLERK 7902 17-十二月-80 800.00 20
7499 ALLEN SALESMAN 7698 20-二月-81 1600.00 300.00 30
7521 WARD SALESMAN 7698 22-二月-81 1250.00 500.00 30
7566 JONES MANAGER 7839 02-四月-81 2975.00 20
7654 MARTIN SALESMAN 7698 28-九月-81 1250.00 1400.00 30
7698 BLAKE MANAGER 7839 01-五月-81 2850.00 30
7782 CLARK MANAGER 7839 09-六月-81 2450.00 10
7788 SCOTT ANALYST 7566 19-四月-87 3000.00 20
7839 KING PRESIDENT 17-十一月-81 5000.00 10
7844 TURNER SALESMAN 7698 08-九月-81 1500.00 0.00 30
7876 ADAMS CLERK 7788 23-五月-87 1100.00 20
7900 JAMES CLERK 7698 03-十二月-81 950.00 30
7902 FORD ANALYST 7566 03-十二月-81 3000.00 20
7934 MILLER CLERK 7782 23-一月-82 1300.00 10

14 rows selected

--显示员工的详细资料，并按姓名排序
SELECT * FROM EMP ORDER BY ename;
--结果
SELECT * FROM EMP ORDER BY ename;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
7876 ADAMS CLERK 7788 23-五月-87 1100.00 20
7499 ALLEN SALESMAN 7698 20-二月-81 1600.00 300.00 30
7698 BLAKE MANAGER 7839 01-五月-81 2850.00 30
7782 CLARK MANAGER 7839 09-六月-81 2450.00 10
7902 FORD ANALYST 7566 03-十二月-81 3000.00 20
7900 JAMES CLERK 7698 03-十二月-81 950.00 30
7566 JONES MANAGER 7839 02-四月-81 2975.00 20
7839 KING PRESIDENT 17-十一月-81 5000.00 10
7654 MARTIN SALESMAN 7698 28-九月-81 1250.00 1400.00 30
7934 MILLER CLERK 7782 23-一月-82 1300.00 10
7788 SCOTT ANALYST 7566 19-四月-87 3000.00 20
7369 SMITH CLERK 7902 17-十二月-80 800.00 20
7844 TURNER SALESMAN 7698 08-九月-81 1500.00 0.00 30
7521 WARD SALESMAN 7698 22-二月-81 1250.00 500.00 30

14 rows selected

--18、显示员工的姓名和受雇日期，并按照老的员工排在前面的方式显示出来
SELECT ename,hiredate FROM EMP ORDER BY hiredate ASC;
--结果
ENAME HIREDATE
---------- -----------
SMITH 17-十二月-80
ALLEN 20-二月-81
WARD 22-二月-81
JONES 02-四月-81
BLAKE 01-五月-81
CLARK 09-六月-81
TURNER 08-九月-81
MARTIN 28-九月-81
KING 17-十一月-81
JAMES 03-十二月-81
FORD 03-十二月-81
MILLER 23-一月-82
SCOTT 19-四月-87
ADAMS 23-五月-87

14 rows selected

--19、显示所有员工的姓名、工作和薪金，按工作的降序排列，工作相同则按照薪金的升序排列
SELECT ename,job,sal FROM emp ORDER BY job DESC,sal ASC;
--结果
ENAME JOB SAL
---------- --------- ---------
WARD SALESMAN 1250.00
MARTIN SALESMAN 1250.00
TURNER SALESMAN 1500.00
ALLEN SALESMAN 1600.00
KING PRESIDENT 5000.00
CLARK MANAGER 2450.00
BLAKE MANAGER 2850.00
JONES MANAGER 2975.00
SMITH CLERK 800.00
JAMES CLERK 950.00
ADAMS CLERK 1100.00
MILLER CLERK 1300.00
SCOTT ANALYST 3000.00
FORD ANALYST 3000.00

14 rows selected

--20、显示所有员工的姓名、加入公司的年份和月份、按接受所在雇佣月排序，若月的相同则按最早年份的员工排在最前面
--本程序需要求出所雇的日期的年份和月份，然后再来显示
SELECT ename 姓名,TO_CHAR(hiredate,'yyyy') 年份， TO_CHAR(hiredate,'mm') 月份 FROM emp ORDER BY 月份,年份;
--结果
姓名 年份 月份
---------- ---- ----
MILLER 1982 01
ALLEN 1981 02
WARD 1981 02
JONES 1981 04
SCOTT 1987 04
BLAKE 1981 05
ADAMS 1987 05
CLARK 1981 06
TURNER 1981 09
MARTIN 1981 09
KING 1981 11
SMITH 1980 12
JAMES 1981 12
FORD 1981 12

14 rows selected


--21、显示一个月为30天的情况下，所有员工的日薪，忽略余数
SELECT ename 姓名,sal 月薪,TRUNC(sal/30) 日薪 FROM EMP;
--结果
姓名 月薪 日薪
---------- --------- ----------
SMITH 800.00 26
ALLEN 1600.00 53
WARD 1250.00 41
JONES 2975.00 99
MARTIN 1250.00 41
BLAKE 2850.00 95
CLARK 2450.00 81
SCOTT 3000.00 100
KING 5000.00 166
TURNER 1500.00 50
ADAMS 1100.00 36
JAMES 950.00 31
FORD 3000.00 100
MILLER 1300.00 43

--22、找出在（任何年份的）2月受雇的员工
SELECT * FROM EMP WHERE TO_CHAR(hiredate,'mm') = 02;
--结果
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
7499 ALLEN SALESMAN 7698 20-二月-81 1600.00 300.00 30
7521 WARD SALESMAN 7698 22-二月-81 1250.00 500.00 30

--23、对于每个员工显示其来到公司的天数
SELECT ename 姓名,SYSDATE-hiredate 来公司的天数 FROM EMP;
--结果
姓名 来公司的天数
---------- ------------
SMITH 11520.760555
ALLEN 11455.760555
WARD 11453.760555
JONES 11414.760555
MARTIN 11235.760555
BLAKE 11385.760555
CLARK 11346.760555
SCOTT 9206.7605555
KING 11185.760555
TURNER 11255.760555
ADAMS 9172.7605555
JAMES 11169.760555
FORD 11169.760555
MILLER 11118.760555

14 rows selected


--24、显示姓名字段的任何位置包含有’A‘的员工
SELECT * FROM EMP WHERE ename LIKE '%A%';
--结果
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
7499 ALLEN SALESMAN 7698 20-二月-81 1600.00 300.00 30
7521 WARD SALESMAN 7698 22-二月-81 1250.00 500.00 30
7654 MARTIN SALESMAN 7698 28-九月-81 1250.00 1400.00 30
7698 BLAKE MANAGER 7839 01-五月-81 2850.00 30
7782 CLARK MANAGER 7839 09-六月-81 2450.00 10
7876 ADAMS CLERK 7788 23-五月-87 1100.00 20
7900 JAMES CLERK 7698 03-十二月-81 950.00 30

7 rows selected

--25、以月份的方式显示所有员工的服务年限
--结果分析：
-- 第一步：求出员工的雇佣年数，雇佣的月数除以12
SELECT ename,TRUNC(MONTHS_BETWEEN(SYSDATE,hiredate)/12) 年数 FROM EMP;

ENAME 年数
---------- ----------
SMITH 31
ALLEN 31
WARD 31
JONES 31
MARTIN 30
BLAKE 31
CLARK 31
SCOTT 25
KING 30
TURNER 30
ADAMS 25
JAMES 30
FORD 30
MILLER 30

14 rows selected
-- 第二步：求除去的出去年份之后的月份
SELECT ename,TRUNC(MONTHS_BETWEEN(SYSDATE,hiredate)/12) 年数,TRUNC(MOD(MONTHS_BETWEEN(SYSDATE,hiredate),12)) 月数 FROM emp;

ENAME 年数 月数
---------- ---------- ----------
SMITH 31 6
ALLEN 31 4
WARD 31 4
JONES 31 3
MARTIN 30 9
BLAKE 31 2
CLARK 31 0
SCOTT 25 2
KING 30 7
TURNER 30 9
ADAMS 25 1
JAMES 30 6
FORD 30 6
MILLER 30 5

14 rows selected

-- 第三步：求出的除去年份和月份之后的天数
SELECT ename,TRUNC(MONTHS_BETWEEN(SYSDATE,hiredate)/12) 年数,TRUNC(MOD(MONTHS_BETWEEN(SYSDATE,hiredate),12)) 月数,TRUNC(SYSDATE-ADD_MONTHS(hiredate,MONTHS_BETWEEN(SYSDATE,hiredate))) 天数 FROM emp;
ENAME 年数 月数 天数
---------- ---------- ---------- ----------
SMITH 31 6 15
ALLEN 31 4 12
WARD 31 4 10
JONES 31 3 0
MARTIN 30 9 4
BLAKE 31 2 1
CLARK 31 0 23
SCOTT 25 2 13
KING 30 7 15
TURNER 30 9 24
ADAMS 25 1 9
JAMES 30 6 29
FORD 30 6 29
MILLER 30 5 9

14 rows selected

--最后结果
SELECT ename,TO_CHAR(SYSDATE,'YYYY-MM-DD'),TO_CHAR(hiredate,'YYYY-MM-DD') 雇佣日期,TRUNC(MONTHS_BETWEEN(SYSDATE,hiredate)/12) 年数,TRUNC(MOD(MONTHS_BETWEEN(SYSDATE,hiredate),12)) 月数,TRUNC(SYSDATE-ADD_MONTHS(hiredate,MONTHS_BETWEEN(SYSDATE,hiredate))) 天数 FROM emp;
--结果
ENAME TO_CHAR(SYSDATE,'YYYY-MM-DD') 雇佣日期 年数 月数 天数
---------- ----------------------------- ---------- ---------- ---------- ----------
SMITH 2012-07-02 1980-12-17 31 6 15
ALLEN 2012-07-02 1981-02-20 31 4 12
WARD 2012-07-02 1981-02-22 31 4 10
JONES 2012-07-02 1981-04-02 31 3 0
MARTIN 2012-07-02 1981-09-28 30 9 4
BLAKE 2012-07-02 1981-05-01 31 2 1
CLARK 2012-07-02 1981-06-09 31 0 23
SCOTT 2012-07-02 1987-04-19 25 2 13
KING 2012-07-02 1981-11-17 30 7 15
TURNER 2012-07-02 1981-09-08 30 9 24
ADAMS 2012-07-02 1987-05-23 25 1 9
JAMES 2012-07-02 1981-12-03 30 6 29
FORD 2012-07-02 1981-12-03 30 6 29
MILLER 2012-07-02 1982-01-23 30 5 9

14 rows selected