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. 创建触发器,实现学生表与课程表更新时成绩表作同步更新。