oracle 查询基础 实例

上一篇 / 下一篇  2013-10-12 16:27:06 / 个人分类：数据库

查看( 508 ) / 评论( 0 ) / 评分( 0 / 0 )
create table emp--雇员表
(
 empno int primary key, --雇员编号，是唯一编号
 ename varchar(10), --雇员姓名
 job varchar(9), --工作职位
 mgr int,--一个雇员的领导编号
 hiredate date,--雇佣日期
 sal number(7,2),--月薪，工资
 comm number(7,2),--奖金，佣金
 deplno number(2) references dept(deptno) --部门编号
);

create table dept--部门表
(
 deptno number(20) primary key,--部门编号，是唯一编号
 dname varchar(20),--部门名称
 loc varchar(20)--部门位置
);
create table salgrade --工资等级表
(
 grade number,--等级名称
 losal number,--此等级的最低工资
 hisal number--此等级的最高工资
);
create table bonus--奖金表
(
 ename varchar(10),--雇员姓名
 job varchar(9),--雇员工作
 sal number,--雇员工资
 comm number--雇员奖金
)


--给部门dept表插入信息
insert into dept values(1,'研发部1','科技园');
insert into dept values(2,'研发部2','科技园');
insert into dept values(3,'研发部3','科技园');
insert into dept values(4,'研发部4','科技园');
insert into dept values(5,'研发部5','科技园');
insert into dept values(6,'研发部6','科技园');
insert into dept values(7,'测试部1','科技园');
insert into dept values(8,'测试部2','科技园');
insert into dept values(9,'研发部','科技园');
insert into dept values(10,'测试部3','科技园');
insert into dept values(11,'测试部4','科技园');
insert into dept values(12,'测试部5','科技园');
insert into dept values(13,'测试部6','科技园');
insert into dept values(14,'运维部','科技园');
insert into dept values(15,'市场部','科技园');
insert into dept values(16,'财政部','科技园');
insert into dept values(17,'人事部','科技园');
insert into dept values(18,'行政部','科技园');
insert into dept values(19,'法务部','科技园');
insert into dept values(20,'科研部','科技园');
insert into dept values(21,'外包部1','科技园');
insert into dept values(22,'外包部2','科技园');
insert into dept values(23,'外包部3','科技园');
insert into dept values(24,'外包部4','科技园');
insert into dept values(25,'外包部5','科技园');
insert into dept values(26,'外包部6','科技园');
insert into dept values(27,'外包部7','科技园');
insert into dept values(28,'外包部8','科技园');
insert into dept values(29,'外包部9','科技园');
insert into dept values(30,'外包部10','科技园');

--插入雇员emp表信息(注意：插入date数据的时候用to_date（'2010-09-09','yyyy-mm-dd'）来插入)
insert into emp values(1,'tom','开发',1,to_date('2013-01-01','yyyy-mm-dd'),'2800','200',1);
insert into emp values(2,'jry','调研',1,to_date('2013-01-01','yyyy-mm-dd'),'2800','200',2);
insert into emp values(3,'ron','开发',1,to_date('2012-03-01','yyyy-mm-dd'),'2800','200',3);
insert into emp values(4,'ray','开发',1,to_date('2012-01-01','yyyy-mm-dd'),'2800','200',4);
insert into emp values(5,'jacy','开发',1,to_date('2010-01-01','yyyy-mm-dd'),'2800','200',5);
insert into emp values(6,'arvin','美工',1,to_date('2010-01-01','yyyy-mm-dd'),'2800','200',6);
insert into emp values(7,'bill','测试',1,to_date('2011-01-01','yyyy-mm-dd'),'2800','200',7);
insert into emp values(8,'rise','测试',1,to_date('2013-09-01','yyyy-mm-dd'),'2800','200',1);
insert into emp values(9,'joanna','测试',1,to_date('2010-05-01','yyyy-mm-dd'),'2800','200',2);
insert into emp values(10,'running','测试',1,to_date('2013-01-01','yyyy-mm-dd'),'2800','200',1);
insert into emp values(11,'eat','测试',1,to_date('2013-07-01','yyyy-mm-dd'),'2800','200',1);
insert into emp values(12,'learn','测试',1,to_date('2013-01-01','yyyy-mm-dd'),'2800','200',1);
insert into emp values(13,'sat','人事',1,to_date('2013-09-01','yyyy-mm-dd'),'2800','200',2);
insert into emp values(14,'cat','人事',1,to_date('2002-01-01','yyyy-mm-dd'),'2800','200',1);
insert into emp values(15,'pig','人事',1,to_date('2013-02-01','yyyy-mm-dd'),'2800','200',1);
insert into emp values(16,'duck','开发',1,to_date('2013-01-01','yyyy-mm-dd'),'2800','200',1);
insert into emp values(17,'aimee','开发',1,to_date('2013-02-01','yyyy-mm-dd'),'2800','200',2);
insert into emp values(18,'mary','开发',1,to_date('2013-01-01','yyyy-mm-dd'),'2800','200',1);
insert into emp values(19,'tiger','开发',1,to_date('2013-08-01','yyyy-mm-dd'),'2800','200',1);
insert into emp values(20,'smart','开发',1,to_date('2013-01-01','yyyy-mm-dd'),'2800','200',1);
insert into emp values(21,'lucy','开发',1,to_date('2013-01-01','yyyy-mm-dd'),'2800','200',2);
insert into emp values(22,'garya','开发',1,to_date('2013-02-01','yyyy-mm-dd'),'2800','200',30);
insert into emp values(23,'josh','开发',1,to_date('2005-01-01','yyyy-mm-dd'),'2800','200',30);
insert into emp values(24,'jash','开发',1,to_date('2006-01-01','yyyy-mm-dd'),'2800','200',30);
insert into emp values(25,'dennis','开发',1,to_date('2008-01-01','yyyy-mm-dd'),'2800','200',1);
insert into emp values(26,'steven','开发',1,to_date('2009-01-01','yyyy-mm-dd'),'2800','200',30);
insert into emp values(27,'stay','开发',1,to_date('2005-01-01','yyyy-mm-dd'),'2800','200',1);
insert into emp values(28,'big','开发',1,to_date('2009-01-01','yyyy-mm-dd'),'2800','200',1);
insert into emp values(29,'small','开发',1,to_date('2007-01-01','yyyy-mm-dd'),'2800','200',30);
insert into emp values(30,'watch','开发',1,to_date('2005-08-01','yyyy-mm-dd'),'2800','200',1);
insert into emp values(31,'watch','开发',1,to_date('2005-08-01','yyyy-mm-dd'),'2800','3200',1);

select * from emp;
select * from dept;


--选择在部门30中员工的所有信息
select * from emp where deplno = 30
--列出职位为测试的员工的编号和姓名
select empno,ename from emp where job = '测试'
--找出奖金高于工资的员工
select * from emp e where comm > sal
--找出每个员工工资和奖金的总和
select empno,ename,job,mgr,hiredate, comm+sal,deplno from emp
--找出部门1中的开发和部门2中的人事
select * from emp where (deplno = 2 and job= '人事') or (deplno = 1 and job = '开发') 
--找出部门1中既不是开发也不是人事，而且工资大于2000的员工
select * from emp where deplno = 1 and job not in('开发','人事') and sal > 2000
--找出有奖金的员工的不同工作
select distinct job from emp where comm > 0
--找出咩有奖金或奖金少于500的员工
select * from emp where comm < 0 or comm <500
--显示雇员姓名，根据其服务年限，将最老的年限排在前面
select ename from emp  order by hiredate 


收藏 举报

TAG:

 

评分：0

我来说两句

Open Toolbar