Oracle数据库练习题

Oracle数据库练习题

（1） 查询20号部门的所有员工信息
select * from emp where deptno=20;
（2） 查询所有职业为CLERK的员工号、员工姓名和部门号
select empno,ename,deptno from emp where job=‘CLERK’;
（3） 查询资金高于工资的员工信息
select * from emp where comm>sal;
（4） 查询奖金高于工资20%的员工信息
select * from emp where comm>1.2sal;
（5） 查询10号部门中职业为MANAGER和20号部门中职业为CLERK的员工信息
select * from emp where (deptno=10 and job=‘MANAGER’)or(deptno=20 and job=‘CLERK’);
（6） 查询所有职业不是MANAGER和CLERK，具工资大于或者等于2000的员工信息。
select * from emp where (job not in(‘MANAGER’,‘CLERK’)and sal>=2000);
（7） 查询所有员工工资与资金的总和
select sum(sal+nvl(comm,0))as salary from emp;
（8） 查询每个员工的部门信息(含部门名称和地址) 外连接的理解
select ename,d.dname,d.loc from emp e,dept d where e.deptno(+)=d.deptno;
（9） 查询每个员工的直接领导者姓名
select e.ename,e1.ename as leader from emp e,emp e1 where e1.empno=e.mgr;
（10） 查询每个部门的职工姓名，没有职工的部门也要显示
select distinct(d.dname,d.deptno),e.ename from emp e,dept d where d.deptno=e.deptno(+);
（1） 以首字母大写的方式显示所有员工的姓名
select initcap(ename) from emp;
（2） 将员工的职位用小写显示
select lower(job) from emp;
（3） 将员工的名字分别用大写和小写显示
select initcap(ename),lower(ename) from emp;
（4） 将员工名字，首字母小写，其他字母大写的方式显示
select concat(lower(substr(ename,1,1)),upper(substr(ename,2,(length(ename)-1)))) from emp;
（5） 显示员工姓名为5个字符的员工
select * from emp where length(ename)=5;
（6） 显示所有员工姓名的前三个字符
select substr(ename,1,3) from emp;
（7） 显示所有员工姓名的后三个字符
select substr(ename,length(ename)-2,length(ename)) from emp;
（8） 以字符长度为10的方式显示员工职位，多余的位数在右边以来填充
select rpad(job,10,’’) from emp;
（9） 找出字符串"oracle training"中第二个ra出现的位置
select instr(‘oracle training’,‘ra’,1,2) from dual;
（10） 去除字符串‘ aadde gf ’两边的空格
select trim(’ adddw gf ') from dual;
（11） 以指定格式显示员工的工资（格式：SMITH 的工资是 800）
select concat(concat(ename,‘的工资是：’),sal) from emp;
（1） 查询一个月前入职的员工
select * from emp where hiredate<(select trunc(add_months(sysdate,-1),‘MM’) from dual);
（2） 显示在一个月为30天的情况所有员工的日薪,忽略余数
select empno,ename,trunc(sal/30)as daysal from emp;
（3） 显示员工在此公司工作了几个月（要求结果是整数）
select ename,trunc(months_between(sysdate,hiredate)) workmonths from emp;
（4） 显示所有12月份入职的员工
select * from emp where to_char(hiredate,‘MM’)=12;
（5） 显示员工的年薪（12个月的工资+补贴）
select ename,(sal12+nvl(comm,0))as nianxing from emp;
（6） 显示所有员工的姓名、加入公司的年份和月份，并且按照年份排序
select ename,to_char(hiredate,‘YYYY’) 年,to_char(hiredate,‘MM’) 月 from emp order by to_char(hiredate,‘YYYY’);
（7） 显示每月倒数第3天入职的所有员工
select * from emp where hiredate=last_day(hiredate)-2;
（8） 显示入职满10年的员工的姓名和受雇日期。
select ename,hiredate from emp where hiredate<(select sysdate-interval’10’ year from dual);
（1） 查询至少有一个员工的部门信息
select * from (select deptno from emp group by deptno)t,dept where dept.deptno=t.deptno;
（2） 查询工资比SMITH员工工资高的所有员工信息
select * from emp where sal>(select sal from emp where ename=‘SMITH’);
（3） 查询入职日期早于其直接领导的所有员工信息
select * from emp e where hiredate<(select hiredate from emp e1 where e.mgr=e1.empno);
（5） 查询所有部门及其员工信息，包括那些没有员工的部门
select emp.,dept. from emp,dept where emp.deptno(+)=dept.deptno;
（6） 查询所有职业为CLERK的员工的姓名及其部门名称
select emp.ename,emp.job,dept.dname from emp,dept where emp.job='CLERK’and emp.deptno=dept.deptno;
（7） 查询最低工资大于 2500的各种职业。
select min(sal),job from emp where sal>2500 group by job;
（8） 查询在SALES部门工作的员工的姓名信息
select * from emp where deptno=(select deptno from dept where dname=‘SALES’);
（9） 查询工资高于公司平均工资的所有员工信息
select * from emp where sal>(select avg(sal) from emp);
（10） 查询与SMITH从事相同工作的所有员工信息
select * from emp where job=(select job from emp where ename=‘SMITH’);
（11） 列出工资等于30号部门中某个员工工资的所有员工的姓名和工资
select * from emp where sal in(select sal from emp where deptno=30)and deptno !=30;
（12） 查询工资高于30号部门中工作的所有员工的工资的员工姓名和工资
select * from emp where sal>(select max(sal) from emp where deptno =30);
（1） 查询平均工资低于2000的部门及其员工信息
select * from emp where deptno in(select emp.deptno from emp,(select avg(sal),deptno from emp where sal<2000 group by deptno)t where t.deptno=emp.deptno group by emp.deptno);
select * from emp,dept where emp.deptno(+)=dept.deptno and dept.deptno in(select deptno from emp group by deptno having avg(sal)<1000);
select avg(sal),deptno from emp where sal<1000 group by deptno;—这个是先找出小于一千块钱的人，然后再在这些人里进行部门分组
（2） 查询每个部门中的员工数量、平均工资和平均工作年限
select deptno,count()as 人数,avg(sal)as 平均工资,sum(to_char(sysdate,‘YYYY’)-to_char(hiredate,‘YYYY’))/count()as 平均工作年限 from emp group by deptno;
（3） 查询各个部门的详细信息以及部门人数、部门平均工资
select d.dname 部门名称,d.deptno 部门编号,count(e.empno) 人数,avg(e.sal) 平均工资 from emp e,dept d where e.deptno=d.deptno(+) group by d.deptnod,d.dname;
（4） 查询各种工作的最低工资
select min(sal) from emp group by job;
（5） 查询部门中不同工种的最高工资
select deptno,job,max(sal) from emp group by deptno,job;
（6） 查询部门人数大于5的部门员工信息
select * from emp where deptno IN(select deptno from emp group by deptno having count()>5);
（7） 查询所有员工工资都在2000—3000之间的部门的信息
select * from emp where sal between 2000 and 3000;
（8） 查询人数最多的部门信息
select * from dept where deptno in(select deptno from emp group by deptno having count()>=(select max(count())from emp group by deptno));
（9） 查询所有员工中工资排序在5—10之间的员工信息
select * from (select emp.,rank()over(order by sal desc) rn from emp)t where t.rn between 5 and 10;
（10） 查询每个部门中工资排名第二的员工信息
select * from (select emp.,rank()over(partition by deptno order by sal) rn from emp)t where t.rn=2;
（11） 以树状结构查询所有员工与其领导之间的层次关系
select level,emp. from emp start with empno=7839 connect by prior empno=mgr;
（12） 采用case分支语句编写程序，把emp表的员工工资分成多个等级。
select ename,sal,case when sal>3003 then ‘高’ when sal>2000 then ‘中’ else ‘低’ end as 工资级别 from emp order by sal desc;
（1） 通过create table emp_bak as select * from emp语句创建表emp_bak
create table emp_bak as select * from emp;
（2）在emp_bak表中插入一条记录
insert into emp_bak values()
（3） 删除emp_bak表中工资低于本部门平均工资的员工信息
delete from emp_bak where emp_bak.deptno=(select deptno from emp_bak group by deptno) and emp_bak.sal<(select avg(sal) from emp_bak group by deptno);
delete from emp_bak having where sal<avg(sal) group by deptno;
delete from emp_bak where empno in( select empno from emp_bak,(select deptno,avg(sal)as sall from emp_bak group by deptno)t where t.deptno=emp_bak.deptno and emp_bak.sal<t.sall);
delet from emp_bak where empno in(select empno from emp_bak group by deptno having sal<avg(sal));
（4）将20号部门中从事SALES工作的员工的工资增加100元
update emp_bak set sal=sal+100 where deptno=20 and job=‘CLERK’;
（5）将30号部门员工的工资修改为10号部门的平均工资
update emp_bak set sal=(select avg(sal) from emp_bak where deptno=20) where deptno=30;
（6）删除职业和SMITH一样，但部门和SMITH不一样的员工信息
delete from emp_bak where emp_bak.job=(select job from emp_bak where ename=‘SMITH’) and deptno not in((select deptno from emp_bak where ename=‘SMITH’));
（1） 创建新用户 TEST 密码为123456，默认表空间为USERS
create tablespace TS1 datafile ‘D:\Ts1.dat’ size 10M autoextend on;–创建一个表空间
alter tablespace TS1 add datafile ‘D:\Ts1_ex.dat’ size 10M autoextend on next 1M;–给表空间添加一个文件
create user TEST identified by 1234567 default tablespace TS1;
（2） 让TEST用户连接数据库服务器
grant create session to TEST;
（3） 让TEST用户可以建表
grant create table to TEST;
（4） 授予TEST可以访问SCOTT用户下表的权限
grant select on scott.emp to TEST;
（5） 删除TEST用户
drop user TEST cascade;
（6） 建立角色
create role ro;
（7） 再重新建立用户TEST
create user TEST identified by 1234567 default tablespace TS1;
（8） 把创建的角色授予给TEST用户
grant ro to TEST;
（9） 查询TEST用户下所具有的系统权限的角色
select * from user_role_privs;
（10） 创建资源文件

（11） 并使TEST用户使用该资源文件
（1） 创建表student(sno,sname,sage,ssex,address) 其中sno为主键，并使用序列自动生成sno。
创建一个表
create table t(id int primary key,
sno int not null);
创建一个序列
create sequence ts increment by 1 start with 1 nomaxvalue nocache nocycle
创建一个触发器-
create or replace trigger tt before insert on t for each row
when (new.id is null)
begin
select ts.nextval into :new.id from dual;
end;
（2） 创建表course（cno,cname,credit）其中cno为主键
create table course(cno int primary key,
cname varchar(6) not null,
credit int not null);
（3） 创建表sc(sno,cno,grade) 其中sno + cno为主键分别参照student和course表
create table sc(id int foreign key(id) reference t(id),
cno int foreign key(cno) reference course(cno),
dd int)
（4） 在student表上以sage为索引字段创建索引，并对比查询语句 select * from student where sage=20在创建索引前后执行计划
create index z on student(sage);
（5） 创建一个100-2000、步长为5的循环序列。
create sequence tt start with 100 increment by 5 minvalue 100 maxvalue 2000;
（6） 为student表年龄超过20的人创建一个视图。
create or replace view s as select * from t where age>20 with check option;
create or replace view ss as select id,price from t where price<30 with check option constraint;
（7） 创建一个物化视图，保存不同性别平均年龄，并对比创建前后查询不同性别平均年龄的执行计划。
grant create materialized view to zhx;
grant create materialized view to scott;
create materialized view mvi_stu_dept_avg refresh force on commit as select count(sage) cnt,deptno,avg(sage) as v_age from stu group by deptno;
（1） 创建一个存储过程，以员工号为参数，输出该员工的工资
create or replace procedure rr(n emp.empno%type,re out emp.sal%type)as
begin
select sal into re from emp where empno=n;
end;
declare n emp.sal%type;
begin
rr(7369);
end;

带尺寸的图片:

居中的图片:

居中并且带尺寸的图片:

当然，我们为了让用户更加便捷，我们增加了图片拖拽功能。

如何插入一段漂亮的代码片

去博客设置页面，选择一款你喜欢的代码片高亮样式，下面展示同样高亮的 代码片.

// An highlighted block
var foo = 'bar';

生成一个适合你的列表

• 项目
  • 项目
    • 项目

1. 项目1
2. 项目2
3. 项目3

• 计划任务
• 完成任务

创建一个表格

一个简单的表格是这么创建的：

项目	Value
电脑	$1600
手机	$12
导管	$1

设定内容居中、居左、居右

使用:---------:居中
使用:----------居左
使用----------:居右

第一列	第二列	第三列
第一列文本居中	第二列文本居右	第三列文本居左

SmartyPants

SmartyPants将ASCII标点字符转换为“智能”印刷标点HTML实体。例如：

TYPE	ASCII	HTML
Single backticks	'Isn't this fun?'	‘Isn’t this fun?’
Quotes	"Isn't this fun?"	“Isn’t this fun?”
Dashes	-- is en-dash, --- is em-dash	– is en-dash, — is em-dash

创建一个自定义列表

Markdown

Text-to- HTML conversion tool

Authors

John

Luke

如何创建一个注脚

一个具有注脚的文本。¹

注释也是必不可少的

Markdown将文本转换为 HTML。

KaTeX数学公式

您可以使用渲染LaTeX数学表达式 KaTeX:

Gamma公式展示 $\Gamma (n)=(n-1)!\forall n\in \mathbb{N}$ 是通过欧拉积分

$$\Gamma (z)={\int }_0^{\infty }{t}^{z-1}{e}^{-t}dt.$$

你可以找到更多关于的信息 LaTeX 数学表达式here.

新的甘特图功能，丰富你的文章

• 关于 甘特图 语法，参考 这儿,

UML 图表

可以使用UML图表进行渲染。 Mermaid. 例如下面产生的一个序列图：

这将产生一个流程图。:

• 关于 Mermaid 语法，参考 这儿,

FLowchart流程图

我们依旧会支持flowchart的流程图：

• 关于 Flowchart流程图 语法，参考 这儿.

导出与导入

导出

如果你想尝试使用此编辑器, 你可以在此篇文章任意编辑。当你完成了一篇文章的写作, 在上方工具栏找到 文章导出 ，生成一个.md文件或者.html文件进行本地保存。

导入

如果你想加载一篇你写过的.md文件，在上方工具栏可以选择导入功能进行对应扩展名的文件导入，
继续你的创作。

---

1. 注脚的解释 ↩︎