Oracle数据库PL SQL开发、Oracle-SQL开发习题答案

1.使用两种方式查询所有员工（EMP）信息。

SELECT *

FROM emp;

SELECT empno,ename,job,mgr,hiredate,sal,comm,deptno

FROM emp;

2.查询（EMP）员工编号、员工姓名、员工职位、员工月薪、工作部门编号。

SELECT empno,ename,job,sal,deptno

FROM emp;

1.员工转正后，月薪上调20%，请查询出所有员工转正后的月薪。

SELECT ename,sal*(1+0.2)

FROM emp;

2.员工试用期6个月，转正后月薪上调20%，请查询出所有员工工作第一年的年薪所得（不考虑奖金部分，年薪的试用期6个月的月薪+转正后6个月的月薪）

SELECT empno,sal*(1+0.2)*6+sal*6

FROM emp;

1.员工试用期6个月，转正后月薪上调20%，请查询出所有员工工作一年的所有收入（需考虑奖金部分），要求显示列标题为员工姓名，工资收入，奖金收入，总收入。

SELECT ename 员工姓名,sal*(1+0.2)*6+sal*6 工资收入,comm 奖金收入,sal*(1+0.2)*6+sal*6+nvl(comm*12,0) 总收入

FROM emp;

1.员工试用期6个月，转正后月薪上调20%，请查询出所有员工工作第一年的所有收入（需考虑奖金部分），要求显示格式为：XXX的第一年总收入为XXX。

SELECT ename || '的第一年总收入为' || (sal*(1+0.2)*6+sal*6+nvl(comm*12,0))

FROM emp;

2.查询员工表中一共有哪几种岗位类型。

SELECT DISTINCT deptno

FROM emp;

1.分别选择员工表、部门表、薪资等级表中的所有数据。

SELECT *

FROM emp;

SELECT *

FROM dept;

SELECT *

FROM salgrade;

2.分别查看员工表、部门表、薪资等级表结构。

DESC emp;

DESC dept;

DESC salgrade;

第3章限制数据和对数据排序

1.查询职位为SALESMAN的员工编号、职位、入职日期。

SELECT empno,job,hiredate

FROM emp

WHERE job='SALESMAN';

2.查询1985年12月31日之前入职的员工姓名及入职日期。

SELECT empno,hiredate

FROM emp

WHERE hiredate<'31-12月1985';

3.查询部门标号不在10部门的员工姓名、部门编号。

SELECT ename,deptno

FROM emp

WHERE deptno<>10;

1.查询入职日期造82年至85年员工姓名，入职日期。

SELECT ename,hiredate

FROM emp

WHERE hiredate BETWEEN '01-1月-82' AND '01-1月-85';

SELECT ename,hiredate

FROM emp

WHERE hiredate BETWEEN TO_DATE('1982-01-01','YYYY-MM-DD')

AND TO_DATE('1985-01-01','YYYY-MM-DD');

2.查询月薪在3000到5000的员工姓名，月薪。

SELECT ename,sal

FROM emp

WHERE sal BETWEEN 3000 AND 5000;

3.查询部门编号为10或者20的员工姓名，部门编号。

SELECT ename,deptno

FROM emp

WHERE deptno=10 OR deptno=20;

4.查询经理编号为7709，7566，7788的员工姓名，经理编号。

SELECT ename,mgr

FROM emp

WHERE mgr IN(7709,7756,778);

1.查询员工姓名以W开头的员工姓名。

SELECT ename

FROM emp

WHERE ename LIKE 'W%';

SELECT ename

FROM emp

WHERE INSTR(ename,'W')=1;

2.查询员工姓名倒数第2个字符为T的员工姓名。

SELECT ename

FROM emp

WHERE ename LIKE '%T_';

SELECT ename

FROM emp

WHERE SUBSTR(ename,-2,1)='T';

3.查询奖金为空的员工姓名，奖金。

SELECT ename,comm

FROM emp

WHERE comm IS NULL;

1.查询工资超过2000并且职位是WANAGER，或者职位是SALESMAN的员工姓名、职位、工资。

SELECT ename,job,sal

FROM emp

WHERE (sal>2000 AND job='MANAGER') OR job='SALESMAN';

2 .查询工资超过2000并且职位是WANAGER或SALESMAN的员工姓名、职位、工资。

SELECT ename,job,sal

FROM emp

WHERE (sal>2000 AND job='MANAGER') OR job='SALESMAN';

3.查询部门在10或者20，并且工资在3000到5000之间的员工姓名、部门、工资。

SELECT ename,deptno,sal

FROM emp

WHERE (deptno=10 OR deptno=20) AND sal BETWEEN 3000 AND 5000;

4.查询入职日期在81年，并且职位不是SALES开头的员工姓名、入职日期、职位。

SELECT ename,hiredate,job

FROM emp

WHERE (hiredate BETWEEN '01-1月-81' AND '31-12月-81') AND job NOT LIKE 'SALES%';

SELECT ename,hiredate,job

FROM emp

WHERE (hiredate BETWEEN '01-1月-81' AND '31-12月-81')

AND SUBSTR(job,0,5)<>'SALES';

5.查询职位为SALESMAN或MANAGER，部门编号为10或者20，姓名包含A的员工姓名、职位、部门编号。

SELECT ename,job,deptno

FROM emp

WHERE (job='SALESMAN' OR job='MANAGER') AND (deptno=10 OR deptno=20) AND ename LIKE '%A%';

SELECT ename,job,deptno

FROM emp

WHERE (job='SALESMAN' OR job='MANAGER')

AND (deptno=10 OR deptno=20)

AND INSTR(ename,'A')>0;

练习5

1.查询部门在20或30的员工姓名，部门编号，并按照工资升序排序。

SELECT ename,deptno,sal

FROM emp

WHERE deptno=20 OR deptno=30

ORDER BY sal;

2.查询工资在2000-3000之间，部门不在10号的员工姓名，部门编号，工资，并按照部门升序，工资降序排序。

SELECT ename,deptno, sal

FROM emp

WHERE (sal BETWEEN 2000 AND 3000) AND deptno<>10

ORDER BY deptno,sal DESC;

3.查询入职日期在82年至83年之间，职位以SALES或者MAN开头的员工姓名，入职日期，职位，并按照入职日期降序排序。

--3.查询入职日期在82年至83年之间，职位以SALES或者MAN开头的员工姓名，入职日期，职位，并按照入职日期降序排序。

SELECT ename,hiredate,job

FROM emp

WHERE (hiredate BETWEEN '01-1月-82' AND '01-1月-83')

AND (job LIKE 'SALES%' OR job LIKE 'MAN%')

ORDER BY hiredate DESC;

1.查询入职时间在1982-7-9之后，并且不从事SALESMAN工作的员工姓名、入职时间、职位。

SELECT ename,hiredate,job

FROM emp

WHERE hiredate>'09-7月-1982' AND job<>'SALESMAN';

2.查询员工姓名的第三个字母是a的员工姓名。

SELECT ename

FROM emp

WHERE SUBSTR(ename,3,1)='a';

SELECT ename

FROM emp

WHERE ename LIKE '__a%';

3.查询除了10、20号部门以外的员工姓名、部门编号。

SELECT ename,deptno

FROM emp

WHERE deptno NOT IN(10,20);

4.查询部门号为30号员工的信息，先按工资降序排序，再按姓名升序排序。

SELECT *

FROM emp

WHERE deptno=30

ORDER BY sal DESC,ename;

5.查询没有上级的员工（经理号为空）的员工姓名。

SELECT ename

FROM emp

WHERE mgr IS NULL;

6.查询工资大于等于4500并且部门为10或者20的员工的姓名，工资，部门编号。

SELECT ename,sal,deptno

FROM emp

WHERE sal>=4500 AND deptno IN(10,20);

第4章单行函数

1.写一个查询，用首字母大写，其他字母小写显示雇员的ename，显示名字的长度，并给每列一个适当的标签，条件是满足所有雇员名字的开始字母是J、A或M的雇员，并对查询结果按雇员的ename升序排序。（提示：使用initcap、length、substr）

SELECT INITCAP(ename) 雇员姓名,LENGTH(ename) 姓名长度

FROM emp

WHERE ename LIKE 'J%' OR ename LIKE 'A%' OR ename LIKE 'M%'

ORDER BY ename;

1.查询员工姓名中包含大写或小写字母A的员工姓名。

SELECT ename

FROM emp

WHERE SUBSTR(ename,1,1)='A' OR SUBSTR(ename,1,1)='a';

2.查询部门编号为10或20，入职日期在81年5月1日之后，并且姓名中包含大写字母A的员工姓名，员工姓名长度。（提示：要求使用INSTR函数）

SELECT ename 员工姓名,LENGTH(ename) 姓名长度

FROM emp

WHERE (deptno=10 OR deptno=20)

AND hiredate>'01-5月-81'

AND SUBSTR(ename,1,1)='A';

3.查询每个职工的编号，姓名，工资

-要求将查询到的数据按照一定的格式合并成一个字符串

-前10位：编号，不足部分用*填充，左对齐

-中间10位：姓名，不足部分用*填充，左对齐

-后10位：工资，不足部分用*填充，右对齐

SELECT CONCAT(CONCAT(LPAD(empno,10,'*'),LPAD(ename,10,'*')),RPAD(sal,10,'*'))

FROM emp;

1.写一个查询，分别计算100.456四舍五入到小数点后第2位，第1位，整数位的值。

SELECT ROUND(100.456,2)

FROM dual;

SELECT ROUND(100.456,1)

FROM dual;

SELECT ROUND(100.456,-1)

FROM dual;

2.写一个查询，分别计算100.456从小数点后第2位，第1位，整数位截断的值。

SELECT TRUNC(100.456,2)

FROM dual;

SELECT TRUNC(100.456,1)

FROM dual;

SELECT TRUNC(100.456,-1)

FROM dual;

1.查询每个员工截止到现在一共入职多少天？

SELECT empno,SYSDATE-hiredate 入职天数

FROM emp

2.当前日期为2015年，指定日期格式DD-MON-RR，指定日期为01-1月-01，该日期实际所代表的日期为？

2001-01-01

3. 当前日期为2015年，指定日期格式DD-MON-RR，指定日期为01-1月-95，该日期实际所代表的日期为？

1995-01-01

4.当前日期为1998年，指定日期格式DD-MON-RR，指定日期为01-1月-01，该日期实际所代表的日期为？

2001-01-01

5.当前日期为1998年，指定日期格式DD-MON-RR，指定日期为01-1月-95，该日期实际所代表的日期为？

1995-01-01

6.当前日期为1998年，指定日期格式DD-MON-YY，指定日期为01-1月-01，该日期实际所代表的日期为？

1901-01-01

7.当前日期为1998年，指定日期格式DD-MON-YY，指定日期为01-1月-95，该日期实际所代表的日期为？

1995-01-01

练习5

1.查询服务器当前时间。

SELECT SYSDATE

FROM dual;

2.查询部门10，20的员工截止到2000年1月1日，工作了多少个月，入职的月份。（使用months_between, extract）

SELECT MONTHS_BETWEEN('01-1月-2000',hiredate) 工作月份,

EXTRACT(MONTH FROM DATE '2000-01-01') 入职月份

FROM emp

WHERE deptno=10 OR deptno=20;

3.如果员工使用期6个月，查询职位不是MANAGER员工姓名，入职日期，转正日期，入职日期后的第一个星期一，入职当前月的最后一天日期。（使用add_months, next_day, last_day）

SELECT ename,hiredate,

ADD_MONTHS(hiredate,6) 转正日期,

NEXT_DAY(hiredate,'星期一') 入职日期后的第一个星期一,

LAST_DAY(hiredate) 入职当前月的最后一天日期

FROM emp

WHERE job<>'MANAGER';

练习6

1.显示服务器系统当前时间，格式为2007-10-12 17:11:11。（用to_char函数）

SELECT to_char(SYSDATE,'YYYY-MM-DD HH24:MI:SS')

FROM dual;

2.显示ename，hiredate和雇员开始工作日是星期几，列标签DAY。（用to_char函数）

SELECT ename,hiredate,

to_char(hiredate,'DAY') DAY

FROM emp;

3.查询员工姓名，工资，格式化的工资（￥999,999.99）。（用to_char函数）

SELECT ename,sal,

to_char(sal,'$999,999.99') 格式化的工资

FROM emp;

4.把字符串2015-3月-18 13:13:13转换成日期格式，并计算和系统当前时间间隔多少天。（用to_char函数）

SELECT TO_DATE('2015-3月-18 13:13:13','YYYY-MM"月"-DD HH24:MI:SS'),

SYSDATE-TO_DATE('2015-3月-18 13:13:13','YYYY-MM"月"-DD HH24:MI:SS')

FROM dual;

1.计算2000年1月1日到现在又多少月，多少周（四舍五入）。

SELECT ROUND(MONTHS_BETWEEN(SYSDATE,'01-1月-2000')),

ROUND(ROUND(SYSDATE-TO_DATE('2000-01-01','YYYY-MM-DD'))/7)

FROM dual;

2.查询员工ename的第三个字母是A的员工信息（使用两个函数）。

SELECT *

FROM emp

WHERE SUBSTR(ename,3,1)='A';

SELECT *

FROM emp

WHERE INSTR(ename,'A',3)=3;

3.使用trim函数将字符串‘hello’，‘Hello’，‘bllb’，‘hello’分别处理得以下字符串ello、Hello、ll、hello。

SELECT TRIM('h' FROM 'hello'),

TRIM(' ' FROM 'Hello '),

TRIM('b' FROM 'bllb'),

TRIM(' ' FROM 'hello ')

FROM dual;

4.将员工工资按如下格式显示：123,234.00 RMB。

SELECT empno,to_char(sal,'999,999.99') || 'RMB'

FROM emp;

5.查询员工的姓名及其经理编号，要求对于没有经理的显示“No Manager”字符串。

SELECT ename,NVL(to_char(mgr),'No Manager')

FROM emp;

6.将员工的参加工作日期按照如此啊格式显示：月份/年份

SELECT empno,to_char(hiredate,'MM/YYYY')

FROM emp;

7.在员工表中查询除员工的工资，并计算应交税款：如果工资小于1000，税率为0，如果工资大于等于1000并小于2000，税率为10%，如果工资大于等于2000并小于3000，税率为15%，如果工资大于等于3000，税率为20%。

SELECT empno,sal,

(CASE

WHEN sal>=0 AND sal<1000 THEN sal*0

WHEN sal>=1000 AND sal<2000 THEN sal*0.1

WHEN sal>=2000 AND sal<3000 THEN sal*0.15

WHEN sal>=3000 THEN sal*0.1

ELSE 0 END) 税率

FROM emp;

8.创建一个查询显示所有雇员的ename和sal。格式化sal围殴15个字符长度，用$左填充，列标签SALARY。

SELECT ename,LPAD(sal,15,'$') SALARY

FROM emp;

第5章多表连接

1.写一个查询，显示所有员工姓名，部门编号，部门姓名。

SELECT ename,emp.deptno,dname

FROM emp,dept

WHERE emp.deptno=dept.deptno;

2.写一个查询，显示所有工作在CHICAGO并且奖金不为空的员工姓名，工作地点，奖金。

SELECT ename,loc,comm

FROM emp,dept

WHERE emp.deptno=dept.deptno AND comm IS NOT NULL;

3.写一个查询，显示所有姓名中含有A字符的员工姓名，工作地点。

SELECT ename,loc

FROM emp,dept

WHERE emp.deptno=dept.deptno AND ename LIKE '%A%';

1.查询每个员工的编号，姓名，工资，工资等级，所在工作城市，按照工资等级进行升序排序。

SELECT e.deptno,ename,sal,grade,loc

FROM emp e,dept d,salgrade s

WHERE e.deptno=d.deptno

AND (e.sal BETWEEN s.losal AND s.hisal)

ORDER BY grade;

练习3.

1.查询所有工作在NEW YORK和CHICAGO的员工姓名，员工编号，以及他们的经理姓名，经理编号。

SELECT e.ename 员工姓名,e.empno 员工编号,

m.ename 经理姓名,m.empno 经理编号

FROM emp e,emp m

WHERE e.mgr=m.empno;

2.在上一题的基础上，添加没有经理的员工King，并按照员工编号排序。

SELECT e.ename 员工姓名,e.empno 员工编号,

m.ename 经理姓名,m.empno 经理编号

FROM emp e,emp m

WHERE e.mgr=m.empno(+)

ORDER BY e.empno;

3.查询所有员工编号，姓名，部门名称，包括没有部门的员工也要显示出来。

SELECT emp.empno,ename,dname

FROM emp,dept

WHERE emp.deptno=dept.deptno(+);

使用SQL-99写法，完成如下练习

1.创建一个员工表和部门表的交叉连接。

SELECT *

FROM emp

CROSS JOIN dept;

2.使用自然连接，显示入职日期在80年5月1日之后的员工姓名，部门名称，入职日期。

SELECT ename,dname,hiredate

FROM emp

NATURAL JOIN dept

WHERE hiredate>'01-5月-80';

3.使用USING子句，显示工作在CHICAGO的员工姓名，部门名称，工作地点。

SELECT ename,dname,loc

FROM emp

JOIN dept USING(deptno)

WHERE loc='CHICAGO';

4.使用ON子句，显示工作在CHICAGO的员工姓名，本门名称，工作地点，薪资等级。

SELECT ename,dname,loc,grade

FROM emp e

JOIN dept d

ON e.deptno=d.deptno AND loc='CHICAGO'

JOIN salgrade s

ON sal BETWEEN losal AND hisal;

5.使用左连接，查询每个员工的姓名，经理姓名，没有经理的King也要显示出来。

SELECT e.ename 员工姓名,m.ename 经理姓名

FROM emp e

OUTER JOIN emp m

ON e.mgr=m.empno;

6.使用右外连接，查询每个员工的姓名，经理姓名，没有经理的King也要显示出来。

SELECT e.ename 员工姓名,m.ename 经理姓名

FROM emp m

RIGHT OUTER JOIN emp e

ON e.mgr=m.empno;

1.显示员工SMITH的姓名，部门名称，直接上级名称。

SELECT e.ename,dname,m.ename 直接上级名称

FROM emp e,emp m,dept d

WHERE e.mgr=m.empno AND e.deptno=d.deptno AND e.ename='SMITH';

2.显示员工姓名，部门名称，工资，工资级别，要求工资级别大于4级。

SELECT ename,dname,sal,grade

FROM emp e,dept d,salgrade s

WHERE e.deptno=d.deptno

AND (sal BETWEEN losal AND hisal)

AND grade>4;

3.显示员工KING和FORD管理的员工姓名及其经理姓名。

SELECT e.ename 员工姓名,m.ename 经理姓名

FROM emp e,emp m

WHERE e.mgr=m.empno

AND m.ename IN('KING','FORD');

4.显示员工姓名，参加工作时间，经理名，参加工作时间，要求参加时间比经理早。

SELECT e.ename,e.hiredate,m.ename,m.hiredate

FROM emp e,emp m

WHERE e.mgr=m.empno AND e.hiredate>m.hiredate;

第6章分组函数

1.查询部门20的员工，每月的工资总和及平均工资。

SELECT SUM(sal),AVG(sal)

FROM emp

WHERE deptno=20;

2.查询工作在CHICAGO的员工人数，最高工资及最低工资。

SELECT COUNT(*),MIN(sal),MAX(sal)

FROM emp e,dept d

WHERE e.deptno=d.deptno AND loc='CHICAGO';

3.查询员工表中一共有几种岗位类型。

SELECT COUNT(DISTINCT job)

FROM emp;

1.查询每个部门的部门编号，部门名称，部门人数，最高工资，最低工资，工资总和，平均工资。

SELECT d.deptno,dname,COUNT(e.deptno),MIN(sal),MAX(sal),SUM(sal),AVG(sal)

FROM emp e,dept d

WHERE e.deptno(+)=d.deptno

GROUP BY d.deptno,dname,loc;

2.查询每个部门，每个岗位的部门编号，部门名称，岗位名称，部门人数，最高工资，最低工资，工资总和，平均工资。

SELECT d.deptno,dname,job,COUNT(e.empno),MAX(sal),MIN(sal),SUM(sal),AVG(sal)

FROM emp e,dept d

WHERE e.deptno=d.deptno

GROUP BY d.deptno,dname,e.job;

3.查询每个经理所管理的人数，经理编号，经理姓名，要求包括没有经理的人员信息。

SELECT COUNT(e.empno),m.empno,m.ename

FROM emp e,emp m

WHERE e.mgr=m.empno(+)

GROUP BY m.empno,m.ename;

1.查询部门人数大于2的部门编号，部门名称，部门人数。

SELECT d.deptno,dname,COUNT(empno)

FROM emp e,dept d

WHERE e.deptno=d.deptno

GROUP BY d.deptno,dname

HAVING COUNT(empno)>2;

2.查询部门平均工资大于2000，且人数大于2的部门编号，部门名称，部门人数，

部门平均工资，并按照部门人数升序排序。

SELECT d.deptno,dname,COUNT(empno),AVG(sal)

FROM emp e,dept d

WHERE e.deptno=d.deptno

GROUP BY d.deptno,dname

HAVING AVG(sal)>2000 AND COUNT(empno)>2

ORDER BY COUNT(empno);

1.查询部门平均工资在2500元以上的部门名称及平均工资。

SELECT dname,AVG(sal)

FROM emp e,dept d

WHERE e.deptno=d.deptno

GROUP BY dname

HAVING AVG(sal)>2500;

2.查询员工岗位中不是以“SA”开头并且平均工资在2500员以上的岗位及平均工资，并按平均工资降序排序。

SELECT job,AVG(sal)

FROM emp

WHERE job NOT LIKE 'SA%'

GROUP BY job

HAVING AVG(sal)>2500

ORDER BY AVG(sal) DESC;

3.查询部门人数在2人以上的部门名称，最低工资，最高工资，并对要求的工资进行四舍五入到整数位。

SELECT dname,ROUND(MIN(sal),-1) 最低工资,

ROUND(MAX(sal),-1) 最高工资

FROM emp e,dept d

WHERE e.deptno=d.deptno

GROUP BY dname

HAVING COUNT(empno)>2;

4.查询岗位不为SALESMSMAN，工资和大于等于2500的岗位及每种岗位的工资和。

SELECT job,SUM(sal)

FROM emp

WHERE job<>'SALESMSMAN'

GROUP BY job

HAVING SUM(sal)>2500;

5.显示经理编号和经理姓名，这个经理所管理员工的最低工资，没有经理的KING也要显示，不包括最低工资小于3000的，按最低工资由高到低排序。

SELECT m.empno,m.ename,MIN(e.sal)

FROM emp e,emp m

WHERE e.mgr=m.empno(+)

GROUP BY m.empno,m.ename

HAVING MIN(e.sal)>=3000

ORDER BY MIN(e.sal) DESC;

6.写一个查询，显示每个部门最高工资和最低工资的差额。

SELECT deptno,MAX(sal)-MIN(sal)

FROM emp

GROUP BY deptno;

第7章子查询

1.查询入职日期最早的员工姓名，入职日期。

--最早的入职日期

SELECT MIN(hiredate)

FROM emp;

SELECT ename,hiredate

FROM emp

WHERE hiredate=

(SELECT MIN(hiredate)

FROM emp);

2.查询工资比SMITH工资高并且工作地点在CHICAGO的员工姓名，工资，部门名称。

--SMITH的工资

SELECT sal

FROM emp

WHERE ename='SMITH';

SELECT ename,sal,dname

FROM emp e,dept d

WHERE sal>

(SELECT sal

FROM emp

WHERE ename='SMITH')

AND loc='CHICAGO';

3.查询入职日期比20部门入职日期最早的员工还早的员工姓名，入职日期。

--20部门员工的最早入职日期

SELECT MIN(hiredate)

FROM emp

WHERE deptno=20;

SELECT ename,hiredate

FROM emp

WHERE hiredate<

(SELECT MIN(hiredate)

FROM emp

WHERE deptno=20);

4.查询部门人数大于所有部门平均人数的部门编号，部门名称，部门人数。

--所有部门平均人数

SELECT AVG(COUNT(empno))

FROM emp

GROUP BY deptno;

SELECT d.deptno,dname,COUNT(empno)

FROM emp e,dept d

WHERE e.deptno=d.deptno

GROUP BY d.deptno,dname

HAVING COUNT(empno)>

(SELECT AVG(COUNT(empno))

FROM emp

GROUP BY deptno);