Oracle的sql语句练习题含答案

Oracle的sql语句练习题含答案（二）Oracle_SQL练习题1

1.选择部门30中的所有员工.

2.列出所有办事员(CLERK)的姓名，编号和部门编号.

3.找出佣金高于薪金的员工.

4.找出佣金高于薪金的60%的员工.

5.找出部门10中所有经理(MANAGER)和部门20中所有办事员(CLERK)的详细资料.

6.找出部门10中所有经理(MANAGER),部门20中所有办事员(CLERK),既不是经理又不是办事员但其薪金大于或等于2000的所有员工的详细资料.

7.找出收取佣金的员工的不同工作.

8.找出不收取佣金或收取的佣金低于100的员工.

9.找出各月倒数第3天受雇的所有员工.

10.找出早于12年前受雇的员工.

11.以首字母大写的方式显示所有员工的姓名.

12.显示正好为5个字符的员工的姓名.

13.显示不带有"R"的员工的姓名.

14.显示所有员工姓名的前三个字符.

15.显示所有员工的姓名,用a替换所有"A"

16.显示满10年服务年限的员工的姓名和受雇日期.

17.显示员工的详细资料,按姓名排序.

18.显示员工的姓名和受雇日期,根据其服务年限,将最老的员工排在最前面.

19.显示所有员工的姓名、工作和薪金,按工作的降序排序,若工作相同则按薪金排序.

20.显示所有员工的姓名、加入公司的年份和月份,按受雇日期所在月排序,若月份相同则将最早年份的员工排在最前面.

21.显示在一个月为30天的情况所有员工的日薪金,忽略余数.

22.找出在(任何年份的)2月受聘的所有员工。

23.对于每个员工,显示其加入公司的天数.

24.显示姓名字段的任何位置包含"A"的所有员工的姓名.

25.以年月日的方式显示所有员工的服务年限.

答案：

1.select ename,job,mgr,hiredate,sal,comm ,deptno from emp where deptno=30;

2.select ename,empno,deptno from emp where job='CLERK';

3.select * from emp where comm>sal;

4.select *

from emp

where comm>sal*0.6;

5.select empno ,ename, job,hiredate,sal,comm,deptno

from emp

where( job='MANAGER' and deptno=10)

or( job='CLERK' and deptno=20);

6.select empno,ename,job, hiredate,sal, deptno

from emp

where ( job='MANAGER' and deptno=10)

or( job='CLERK' and deptno=20)

or((job <>'MANAGER' and job<>'CLERK') and sal>=2000);

7.select distinct ename,job,comm

from emp

where NVL(comm,0)>0;

注意如果像上面这样写，就job ename comm 3个同时不同的都列出来

正确的是：

select distinct job

from emp

where NVL(comm,0)>0;

8.select ename,comm. from emp where NVL(comm,0)=0 or NVL(comm,0)<100; 如果按上面的写法就重复了，直接可以写成

select ename,comm

from emp

where NVL(comm,0)<100;

9 select ename, job,last_day(hiredate)-3

from emp;

select ename, job,hiredate

from emp

where hiredate=last_day(hiredate)-2;

10.select ename，job

from emp

where months_between(sysdate,hiredate)/12>12

11.select initcap(ename) name ,job, hiredate

from emp;

12. select ename,job,hiredate from emp where length(ename)=5;

13 select ename, job from emp where ename not like '%R%';

14.select substr(ename, 1,3) name,job,hiredate from emp；

15select replace(ename, 'A','a') name,job,hiredate from emp;

16select ename, job, hiredate from emp

where months_between(sysdate,hiredate)/12>=10;

17. select * from emp order by ename;

18.select ename, hiredate from emp order by hiredate desc;

19.select ename,job, sal from emp order by job desc, sal asc;

20.select ename, to_char(hiredate,'yyyy') year, to_char(hiredate,'mm') months

from emp

order by months, year asc;

21.select ename, sal/30 dailysal

from emp;

22. select ename, hiredatefrom empwhere to_char(hiredate,'mm')='02';

23. select ename,sysdate-hiredate 天数from emp;

24.select ename from emp where instr(ename,'A',1)>0;

25. select ename, to_char(hiredate,'yyyy/mm/dd') from emp；

--26.列出至少有一个员工的所有部门.

select dname from emp,dept where emp.deptno=dept.deptno group by dname;

select distinct dname from emp,dept where emp.deptno=dept.deptno;

--说明：（1）select中出现的字段一定要出现在group by中;

-- （2）如果select中出现的字段不出现在group by中，则一定要出现在select的聚合函数中，对于想出现的字段，可用max()或min()。

-- （3）默认的情况是按group by中的字段来排序，如果要用order by来自己排序，则order by后的字段一定是group by后的字段，不能是聚合函数的字段

--27.列出薪金比“SMITH”多的所有员工.

select ename, sal from emp where sal > (select sal from emp where upper(ename)='SMITH');

--28.列出所有员工的姓名及其直接上级的姓名.

select a.ename, b.ename mgrname from emp a,emp b where a.mgr = b.empno;

--29.列出受雇日期早于其直接上级的所有员工

select e.ename, m.ename from emp e, emp m where e.mgr=m.empno and (e.hiredate < m.hiredate ); 30.列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门

select dname, emp.* from dept, emp where emp.deptno(+) = dept.deptno order by dname;

--说明：外连接（使一个都不能少，（+）对面的表里的字段都不能少）

--31.列出所有“CLERK”（办事员）的姓名及其部门名称

select distinct emp.ename, dept.deptno from emp, dept where emp.deptno = dept.deptno and upp er(emp.job)='CLERK';

--32.列出最低薪金大于1500的各种工作

select job from emp group by job having min(sal) > 1500;

--33.列出在部门“SALES”（销售部）工作的员工的姓名，假定不知道销售部的部门编号

select ename from emp, dept where emp.deptno = dept.deptno and dname = 'SALES';

--34.列出薪金高于公司平均薪金的所有员工

select ename, sal from emp where sal > (select avg(sal) from emp);

--35.列出与“SCOTT”从事相同工作的所有员工

--SCOTT从事一种工作

select ename from emp where job = (select job from emp where upper(ename) = 'SCOTT');

--SCOTT从事多种工作

select ename, job from emp where job in (select job from emp where upper(ename) = 'SCOTT');

--36.列出薪金等于部门30中员工的薪金的所有员工的姓名和薪金

select ename, sal from emp where sal in(select sal from emp where deptno = 30);

--37.列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金

select ename, sal from emp where sal > (select max(sal) from emp where deptno = 30);

--38.列出在每个部门工作的员工数量、平均工资和平均服务期限

select deptno "部门号", count(ename) "员工数量", avg(sal + nvl(comm, 0)) "平均工资", trunc(avg(sysdate-hiredate)) "平均服务期限(天)"

from emp group by deptno;

--39.列出所有员工的姓名、部门名称和工资

select ename, dname, sal+nvl(comm,0) from emp, dept where emp.deptno = dept.deptno;

--40.列出从事同一种工作但属于不同部门的员工的一种组合

select distinct e.deptno, e.job from emp e, emp m where e.deptno != m.deptno and e.job = m.job o rder by e.job;

--41.列出所有部门的详细信息和部门人数

select dept.*, (select count(ename) from emp where emp.deptno(+)=dept.deptno) 人数 from dept;

--42.列出各种工作的最低工资

select job, min(sal + nvl(comm, 0)) "最低工资" from emp group by job;

--43.列出各个部门的MANAGER（经理）的最低薪金

select min(sal + nvl(comm,0)) from emp where upper(job) = 'MANAGER' group by deptno;

--显示部门名称

select dname, min(sal + nvl(comm,0)) from emp, dept where emp.deptno = dept.deptno and upper (job) = 'MANAGER' group by dname;

--44.列出所有员工的年工资,按年薪从低到高排序

select ename, to_char((sal+nvl(comm,0))*12, '9999,9999.00') "年工资" from emp order by 2;

--45.显示各部门员工薪金最高的前2名

select * from (select ename, deptno, sal, row_number() over(partition by deptno order by sal desc ) r from emp ) where r <=2

--46.显示薪金最高的3位员工

select * from (select ename, sal,dense_rank() over(order by sal desc) r from emp) where r <=3;

--47.创建表myemp和emp表具有相同的结构和记录。

create table myemp as select * from emp;

--48.给my emp的empno列添加主建约束。

alter table myemp add constraint pk primary key(empno);

--49.给my emp添加一条记录。

insert into myemp values(1,'a','ab',999999,to_date('2008-9-9','yyyy-mm-dd'),5000,1000,30);

--50.给my emp添加一条记录只有empno,ename,mgr,sal，deptno有值，其他列为空。

insert into myemp(empno,ename,mgr,sal,deptno) values(20,'aa',30,999999,20);

--51.显示所有薪金高于各自部门平均薪金的人。(关联子查询)

select e.ename, e.deptno, e.sal from emp e where e.sal>(select avg(sal) from emp p where e.deptn o=p.deptno);

--52.给所有10部门的经理（MANAGER）和20部门的职员(CLERK)，增加薪金10%。

update emp set sal=sal*(1+0.1) where (deptno=10 and upper(job)='MANAGER') or(deptno=20 and upper(job)='CLERK');

--53.删除DEPT中没有员工的部门。

delete from dept where deptno not in(select distinct deptno from emp);

--54.删除雇佣年限低于20年的员工。

delete from emp where trunc(sysdate-hiredate) < 365*20;

1. 查询所有雇员姓名以及其全年收入（工资+奖金），并指定列别名为“年收入”。

select ename,12*(sal+nvl(comm,0)) as年收入from emp;

2. 查询有雇员的所有部门的部门号和部门名称。

select empno,ename,dept.dname,dept.deptno from emp,dept where emp.deptno=dept.deptno;

3. 查询工资超过2850的雇员姓名和工资。

select ename, sal from emp where sal>2850;

4. 查询工资不在1500到2850之间的所有雇员姓名和工资。

select ename, sal from emp where sal not between 1500 and 2850;

5. 查询10号部门和30号部门工资超过1500的雇员姓名和工资。

select ename, sal from emp where sal < 1500 and deptno in (10,30);

6. 查询没有上司的员工姓名及其职位。

select ename, job from emp where mgr is null;

7. 查询在1981年2月1日~1981年5月1日之间入职的雇员姓名、职位及入职时间，并以入职时间的先后进行排序。

select ename,job,hiredate from emp where hiredate between '01-1月-81' and '01-5月-81' order by hiredate;

8. 查询有奖金的员工的姓名、工资和奖金，并按工资和奖金降序排序。

select ename,sal,comm from emp where comm is not null order by sal desc,comm desc; 9. 查询每种职位的雇员总数和平均工资。

select job,count(*),avg(sal) from emp group by job;

10. 查询所有雇员总数和获得奖金的员工数。

select count(empno),count(comm) from emp ;

11. 查询经理的总人数。

select count(*) from emp where job='MANAGER';

12. 查询20号部门的部门名称，以及该部门所有雇员姓名、工资和职位。

select dname,ename,sal,job from emp,dept where dept.deptno=emp.deptno and emp.deptno=20;

13. 查询有奖金的所有员工的姓名、奖金以及所在部门名称。

select ename,comm,dname from emp,dept where dept.deptno=emp.deptno and comm is not null;

14. 查询在“New York”工作的所有员工的姓名、工资及其所在部门名称。

select ename,sal,dname from emp,dept where dept.deptno=emp.deptno and loc= 'NEW YORK';

15. 查询雇员SCOTT的上级。

select empno,ename from emp where empno=(select mgr from emp where ename='SCOTT');

16. 查询20号部门的员工姓名、工资及其级别。

select ename,sal,grade from emp,salgrade where deptno=20 and sal between losal and hisal;

17. 查询与BLAKE同一部门的其他员工。

select empno,ename from emp where deptno=(select deptno from emp where ename='BLAKE') and

ename!= 'BLAKE';

18. 查询工资超过平均工资的所有员工的姓名、工资和职位。

select ename,sal,job from emp where sal> (select avg(sal) from emp);

19. 查询工资、奖金与SCOTT完全相同的所有员工的姓名、工资和奖金。

select ename,sal, comm from emp where (sal,nvl(comm,-1)) in (select sal,nvl(comm,-1) from emp where

ename='SCOTT') ;

20. 查询81年入职的员工

select * from emp where to_char(hiredate, 'yy')= '81';

21. 按年月日查询员工信息

select to_char(hiredate, 'yyyy-mm-dd' )from emp;

22. 向DEPT表中插入一条数据，要求：部门号为50，部门名称为ADMINISTRATOR，部门位置为BOSTON。

Insert into dept values(50, 'ADMINISTRATOR','BOSTON');

23. 向EMP表中插入一条数据，要求：员工号为2000，姓名为JOHN，工资为1000，入职时间为2003年4月7日，部门号为30号。

Insert into emp (empno,ename,sal,hiredate,deptno) values(2000, 'JOHN',1000, '07-4月-03',30);

24. 给10号部门的每个雇员增加10%的工资。然后提交事务。

Update emp set sal=sal*1.1 where deptno=10;

commit

25. 删除50号部门。然后回滚事务。

Delete from dept where deptno=50;

Rollback;

新建一张学员信息表(student)，要求：

1. 字段如下：学号(sid)，姓名(name)，性别(sex)，年龄(age)，地址(address)．

2. 分别为字段添加约束：学号为主键，姓名为非空，性别为检查约束，年龄为检查约束，地址为默认约束．

3. 为表建立自增值（sid）, 建议初始值从为1001, 增量为 1.

4. 插入记录．

新建一张课程表(course)，要求：

1. 字段如下：课程编号(cid)，课程名称(subject)．

2. 分别为字段添加约束：课程编号为主键，课程名称为非空．

3. 为表建立自增值(cid), 建议初始值从为1, 增量为 1.

4. 插入记录．

新建一张学员考试成绩表(grade)，要求：

1. 字段如下：成绩编号(gid)，学号(sid)，课程编号(cid)，考试成绩(score).

2. 分别为字段添加约束：成绩编号为主键，学号为外键，课程编号为外键，考试成绩为非空.

3. 为表建立自增值(gid), 建议初始值从为101, 增量为 1.

4. 插入记录．

回顾分组查询：

1. group by

用途：对结果集进行分组，常与汇总函数一起使用.

2. having

用途：指定群组或汇总的搜寻条件。

注：having 通常与group by 子句同时使用。不使用group 时，having 则与where 子句功能相似。针对以上三张表，要求完成如下：

1. 按照课程编号分组并求出每一组的平均分数(每门课程的平均分)

2. 按照课程编号分组并求出每一组及格人数(grade >= 60)的平均分数(每门课程的及格人数的平均分)

3. 求每个学员所有的课程的平均分

4. 求每个学员所有的（考试成绩）及格课程的平均分

5. 每次内部测试不同学员的平均成绩(每个学员的每门课程的平均分)

6. 查询补考过的学员的平均成绩(求出学员的课程编号(cid)在分组内出现过一次以上)(每个学员的每门课程的课程编号出现过一次以上)

7. 使用多表内连接查询，求出学员的学号，姓名，考号，科目和成绩

8. 建立一张视图，要求字段为中文，包含学员的学号，姓名，考号，科目，成绩这些字段

9. 创建触发器，实现学生表与课程表更新时成绩表作同步更新。