oracle 学习小练习

1、 选择在部门30中员工的所有信息
select  * from emp where deptno=30;
2、 列出职位为（MANAGER）的员工的编号，姓名
select empno,ename from emp where job ='MANAGER';
3、 找出奖金高于工资的员工
select * from emp where comm>sal;
4、 找出每个员工奖金和工资的总和
select ename,sal+nvl(comm,0) from emp;
5、 找出部门10中的经理(MANAGER)和部门20中的普通员工(CLERK)
select * from emp where (deptno=10 and job='MANAGER') or (deptno=20 and job ='CLERK');
6、 找出部门10中既不是经理也不是普通员工，而且工资大于等于2000的员工
select * from emp where deptno=10 and job not in('MANAGER','CLERK') and sal>=2000;
7、 找出有奖金的员工的不同工作
select distinct job from emp comm!=0 and comm is not null;
8、 找出没有奖金或者奖金低于500的员工
select * from emp  where comm is null or comm<500;
9、 显示雇员姓名，根据其服务年限，将最老的雇员排在最前面
select ename,to_char(hiredate,'yyyy-MM-dd'),months_between(sysdate,hiredate)/12 from emp order by hiredate;




1、  分组统计各部门下工资>500的员工的平均工资
select deptno,avg(sal) from emp where sal>500 group by deptno;
2、  统计各部门下平均工资大于500的部门
select deptno,avg(sal) from emp group by deptno having avg(sal)>500;
3、  算出部门30中得到最多奖金的员工奖金
select max(comm) from emp where deptno=30;
4、  算出部门30中得到最多奖金的员工姓名（需要用到子查询）
select ename from emp where deptno=30 and comm=(select max(comm) from emp where deptno=30);
5、  算出每个职位的员工数和最低工资
select job,count(*),min(nvl(sal,0)) from emp group by job;
6、  算出每个部门,，每个职位的平均工资和平均奖金(平均值包括没有奖金)，如果平均奖金大于300，显示“奖金不错”，如果平均奖金100到300，显示“奖金一般”，如果平均奖金小于100，显示“基本没有奖金”，按部门编号降序，平均工资降序排列
select deptno,job,avg(nvl(sal,0)),avg(nvl(comm,0)),case  
when avg(nvl(comm,0))>300 then '奖金不错'
when avg(nvl(comm,0)) between 100 and 300 then '奖金一般' 
when avg(nvl(comm,0))<100 then '基本没有奖金' end "奖金情况"
 from emp group by deptno,job order by deptno desc,avg(nvl(sal,0)) desc;
7、  列出员工表中每个部门的员工数，和部门no
select deptno,count(*) from emp group by deptno;
8、  得到工资大于自己部门平均工资的员工信息（需要用到子查询）
select e.ename,e.sal,t.deptno,t.a
from emp e inner join 
(select deptno,avg(sal) a from emp group by deptno) t
on e.deptno=t.deptno and e.sal>t.a;

select e.ename,e.sal from emp e where e.sal>(select avg(sal) form emp where deptno=e.deptno);
9、  分组统计每个部门下，每种职位的平均奖金（也要算没奖金的人）和总工资(包括奖金)
select deptno,job,avg(nvl(comm,0)),sum(sal+nvl(comm,0)) from emp group by deptno,job;


----
1,找出工资比jones多的员工  
select * from emp where sal>(select sal from emp where ename='JONES')
2,列出所有员工的姓名和其上级的姓名
select e1.ename,e2.ename from emp e1 left outer join emp e2 on e1.mgr=e2.empno;
3,以职位分组，找出平均工资最高的两种职位
select job,a from (select job,avg(nvl(sal,0)) a from emp group by job order by a desc) where rownum<3;
4,查找出不在部门20，且比部门20中任何一个人工资都高的员工姓名、部门名称
(1)
select e.ename,e.sal,d.dname
from
(select ename,deptno,sal from emp where deptno<>20 and sal>(select max(sal) from emp where deptno=20)) e inner join dept d on e.deptno = d.deptno;


(2)
select t.ename,t.sal,  d.dname from dept d inner join 
(select  ename ,deptno, sal from emp where  sal>(select   max(sal) from emp where deptno=20) and  deptno<>20)
t on d.deptno=t.deptno;
5,查找出职位和'MARTIN'  或者'SMITH'一样的员工的平均工资
select avg(nvl(sal,0)) from emp where job in(select job from emp where ename='MARTIN' or ename='SMITH');
6,查找出不属于任何部门的员工
select * from emp where deptno is null;
7,列出没有对应部门表信息的所有雇员的姓名、工作以及部门号
select ename,job,deptno from emp where deptno is null or deptno not in(select deptno from dept);
8,查找工资在1000～3000之间的雇员所在部门的所有人员信息
select * from emp where deptno in(select distinct deptno from emp where sal between 1000 and 3000);
9,雇员中谁的工资最高
select * from emp where sal=(select max(sal) from emp);
10,雇员中谁的工资第二高（考虑并列第一,第二的情况，如何处理）
select * from emp where sal=(select max(sal) from emp where sal<(select max(sal) from emp));


---
1.scott下面创建一个表 
 emp1 
 empno number(10) 
 ename varchar2(50) 
 
  create table emp1( empno number(10), ename varchar2(50) );


2.添加一个字段 
 sal number(10,2) 
 alter table emp1 add(sal number (10,2));
3.修改字段   ename varchar2(100) 
 alter   table emp1  modify ename varchar2(100);
4.删除字段sal 
alter table emp1 drop(sal);
5.把表emp1改成emp2 
alter table emp1 rename to emp2;
6.删除表emp2 
drop table emp2;
7.创建一个和emp结构一样的表，并同时插入工资大于1000的数据 
create  table emp_copy as (select * from emp where sal>1000); 
8.清空表数据（用truncate) 
truncate table emp_copy;


----------------------------------------


 1.创建一张表student 
 id     number 
 name   varchar2(10) 
 age     number(10) 
 tel    varchar2（10） 
 给id字段添加主键约束 
 给name字段添加非空约束 
 给age字段添加check约束（age必须大于18岁)                                                                       
给tel 添加唯一  非空  约束
 create table student (
 id number primary key,
 name varchar2(10)  not null,
 age number(10) check (age>18),
 tel varchar2(10) unique  not null
 );
2.创建一张学员兴趣爱好表  hobby 
 id number(10) 
 hobby_name varchar2(10) 
 sid number --学生id 
 给sid字段添加外键约束，并且要带级联删除 
 create table hobby(
id number(10),
hobby_name varchar2(10),
sid number ,
constraint hobby_sid_ck foreign key(sid) references student(sid) on delete cascade
);
 
3.删除掉student表中tel字段的唯一约束（先写出查看该表约束的sql) 
select * from user_constraints where table_name=upper('student');
 alter table student drop constraint SYS_C0011115;
4.手动添加student表中tel字段的唯一约束（约束名为：MY_CONSTRAINT_1) 
 alter table student add constraint MY_CONSTRAINT_1 unique(tel);
5.禁用约束MY_CONSTRAINT_1 
 alter table student disable constraint MY_CONSTRAINT_1;
6.启用约束MY_CONSTRAINT_1
 alter table student enable constraint MY_CONSTRAINT_1;
--
1、找出25 年前雇的员工 
select * from emp where (round(months_between(sysdate,hiredate)/12))>25;
2、所有员工名字前加上Dear ,并且名字首字母大写 
 select ename ,lpad(initcap(ename),length(ename)+4,'Dear') from emp;
3、找出姓名为5 个字母的员工
 select ename from emp  where length(ename)=5;
4、找出姓名中不带R 这个字母的员工 
 select * from emp where ename not in (select ename from emp where ename like '%R%');
5、显示所有员工的姓名的第一个字
 select substr(ename,1,1) from emp;   
6、假设一个月为30 天，找出所有员工的日薪，不计小数
 select  empno, ename,round((nvl(sal,0)+nvl(comm,0))/30)  from emp;   
7、找到2 月份受雇的员工 
 select *  from emp where to_number(to_char(hiredate,'MM'))=02; 
8、列出员工加入公司的天数(四舍五入）
 select  empno,ename, round((months_between(sysdate, hiredate))*30) as days from emp ;  
9、分别用case 和decode 函数列出员工(emp)所在的部门，deptno=10 显示'部门10', 
 deptno=20 显示'部门20' 
 deptno=30 显示'部门30' 
 deptno=40 显示'部门40' 
 否则为'其他部门'
 
  select empno,ename,deptno,case deptno
 when 10 then '部门10' 
 when 20 then '部门20'
 when 30 then '部门30'
 else '其他部门' end  as 部门 from  emp;
 
  select empno ,ename,deptno, decode(deptno, 10,'部门10',20,'部门20',30,'部门30','其他部门') as 部门 from emp;
10.查询出每个人的姓名、工资以及他领导的姓名以及工资；
select e1.ename, e1.sal,e2.ename as bossname,e2.sal as bosssal  from emp e1 left outer join emp e2 on e1.mgr= e2.empno ;
11.把上面例子中加上他们各自的部门名称；
(1)
 select e.ename,e.sal,m.ename as boss ,m.sal, e.deptno, d.dname from emp e,emp m , dept d 
 where e.mgr=m.empno and e.deptno=d.deptno;
(2)
select e1.ename, e1.sal, e2.ename ,e2.sal, e2.deptno ,d.dname from emp e1 
 left outer join emp e2 on e1.mgr= e2.empno inner join dept d  on e2.deptno=d.deptno;


12.查询出每个雇员的姓名、工资、部门名称、工资所在等级以及领导的姓名及领导的工资和领导工资所在公资的等级；


 select e1.ename, e1.sal,s1.grade, e2.ename as boss,e2.sal as boss_sal,s2.grade as boss_sal_grade, e2.deptno ,d.dname from emp e1 
 left outer join emp e2 on e1.mgr= e2.empno inner join dept d  on e2.deptno=d.deptno
 inner join  salgrade s1 on e1.sal>=s1.losal and e1.sal<=s1.hisal
 inner join  salgrade s2 on e2.sal>=s1.losal and e2.sal<=s2.hisal;
13.要求查出部门名称、部门的员工数、部门的平均工资和部门的最低收入雇员的姓名；


 select d.dname,t.c, t.a, e.ename  from emp e inner join 
 (select min(nvl(sal,0)) as m,round(avg(nvl(sal,0))) as a,   count(*) as c from emp group by deptno) t
 on e.sal=t.m
 inner join dept d on e.deptno= d.deptno;
---
1.按照如下要求建立学生表和教师表：
学生表：STUDENT
字段名 字段中文名 类型 长度 备注
SID 学生编号 NUMBER 4 PK
SNAME 学生姓名 VARCHAR2 20 不能为空
SEX 性别 CHAR 2 只能是男或女
AGE 年龄 NUMBER 3 不能小于10，不能大于120
PHONE 电话 VARCHAR2 20 不能重复
EMAIL 邮箱 VARCHAR2 50
TID 教师编号 NUMBER 4 FK


create table student(
  sid number(4) primary key,
  sname varchar2(20) not null,
  sex char(2) ,
  age number(3) ,
  phone varchar2(20) unique,
  email varchar2(50),
  tid number(4),
  constraint student_sex_ck check (sex in('男','女')),
  constraint student_age_ck check (age between 10 and 120),
  constraint student_tid_fk foreign key(tid) references teacher(tid)
  );
教师表：TEACHER
字段名 字段中文名 类型 长度 备注
TID 教师编号 NUMBER 4 PK
TNAME 教师姓名 VARCHAR2 20 不能为空
SUBJECT 科目 VARCHAR2 20 不能为空
create table teacher (
  tid  number(4) primary key,
  tname varchar2(20)  not null,
  subject varchar2(20) not null
)
2.使用程序代码往两张数据库表中插入测试数据。


insert into teacher values(0001,'龙哥','oracle')


insert into student values(0001,'阿飞','男',26,'15789348746','1212@qq.com',0001)
3.查询出学生编号、学生姓名、性别、年龄、电话、邮箱、教师姓名、科目打印到控制台。
select s.sid,s.sname,s.sex,s.age,s.phone,s.email,t.tname,t.subject from student s 
inner join teacher t on s.tid= t.tid;