发布时间 : 星期日 文章Oracle的sql语句练习题含答案更新完毕开始阅读
emp.deptno(+)=dept.deptno group by dept.deptno,dept.dname,dept.loc
--42.列出各种工作的最低工资
select job, min(sal + nvl(comm, 0)) \最低工资\ --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') \年工资\ --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.给myemp的empno列添加主建约束。
alter table myemp add constraint pk primary key(empno); --49.给myemp添加一条记录。
insert into myemp values(1,'a','ab',999999,to_date('2008-9-9','yyyy-mm-dd'),5000,1000,30); --50.给myemp添加一条记录只有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.deptno=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 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的雇员姓名和工资。
from
emp,dept
where
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. 创建触发器，实现学生表与课程表更新时成绩表作同步更新。