Orcale数据库操作练习

/*============================第一部分===========================*/
/*显示所有部门名称*/
select dname from dept
/*显示雇员名及其年收入，并制定列别名为“年收入”*/
select ename,(sal+nvl(comm,0)) as "年收入" from emp
/*查询显示不存在雇员的所有部门号*/
select deptno from dept 
where 
deptno not in (
       select distinct deptno from emp
       )
/*2、限制查询*/
/*显示EMP表雇员工资超过2850的雇员的名字和工资*/
select ename,sal as "工资" from emp
where
sal>2850
/*显示EMP表雇员工资不在1500~2850之间的雇员的名字和工资*/
select ename,sal as pay from emp
where
sal<1500 or sal>2850
select ename,sal as "工资" from emp
where
sal not in(
         select sal as pay from emp where sal>1500 and sal<2850
          )
/*查询EMP表显示代码为7566的雇员姓名及所在部门代码*/
select ename,deptno from emp
where
empno = '7566'
/*查询EMP表显示部门10和30中工资超过1500的雇员名及工资---别名的使用*/
select ename,sal as "工资" from(
       select ename,sal from emp where deptno = '10' or deptno='30')
where
sal>1500
/*查询EMP表显示第2个字符为"A"的所有雇员名其工资*/
select ename,sal as "工资" from emp
where
ename like '_A%'
/*查询EMP表显示补助非空的所有雇员名及其补助*/
select ename,comm from emp
where
comm is not null
/*3、排序查询*/
/*查询EMP表显示所有雇员名、工资、雇佣日期，并以雇员名的升序进行排序*/
select ename,sal,hiredate from emp order by ename
/*查询EMP表显示在1981年2月1日到1981年5月1日之间雇佣的雇员名、岗位及雇佣日期，并以雇佣日期进行排序*/
select ename,job,hiredate from emp
where
hiredate between to_date('1980-2-1','yyyy-mm-dd') and to_date('1981-5-1','yyyy-mm-dd')
order by hiredate
/*查询EMP表显示获得补助的所有雇员名、工资及补助，并以工资升序和补助降序排序*/
select ename,sal,comm from emp order by sal,comm desc

/*============================第二部分===========================*/
/*列出至少有一个雇员的所有部门*/
select distinct dept.deptno,dept.dname,dept.loc from dept,emp
where 
dept.deptno = emp.deptno
/*列出薪金比“SMITH”多的所有雇员*/
select ename from emp
where
(sal+nvl(comm,0)) > (select (sal+nvl(comm,0)) from emp where ename = 'SMITH')
/*列出所有雇员的姓名及其上级的姓名-----自连接查询*/
select e1.ename,e2.ename from emp e1,emp e2 
where
e1.mgr = e2.empno
/*列出入职日期早于其直接上级的所有雇员*/
select n1,e1_d,n2,e2_d from
  (select e1.ename as n1,e1.hiredate as e1_d,e2.ename as n2,e2.hiredate as e2_d from emp e1,emp e2
  where
  e1.mgr = e2.empno)
where
e1_d < e2_d
/*列出部门名称和这些部门的雇员，同时列出那些没有雇员的部门*/
select dept.dname,emp.ename from emp full join dept on emp.deptno = dept.deptno
/*列出所有“CLERK”（办事员）的姓名及其部门名称*/
select emp.ename,dept.dname from emp left join dept on emp.deptno = dept.deptno
where
emp.job = 'CLERK'
/*列出各种岗位的最低薪金，并显示最低薪金大于1500所有工作岗位及其最低薪资*/
select job, min(sal) from emp group by job having min(sal)>1500
/*列出从事“SALES”（销售）工作的雇员的姓名，假定不知道销售部的部门编号*/
select emp.ename from emp 
where
emp.deptno = (select deptno from dept where dept.dname = 'SALES')
/*列出薪金高于公司平均的所有雇员*/
select emp.ename from emp
where
emp.sal > (select avg(sal) from emp) 
/*列出与“SCOTT”从事相同工作的所有雇员*/
select emp.ename from emp
where
emp.job = (select emp.job from emp where emp.ename = 'SCOTT') and emp.ename != 'SCOTT'
/*列出薪金等于在部门30工作的所有雇员的薪金的雇员的姓名和薪金*/
select ename,sal from emp where sal in (select sal from emp where deptno=30)
select ename,sal from emp where deptno = 30
/*列出薪金高于在部门30工作的所有雇员的薪金的雇员的姓名和薪金*/
select ename,sal from emp
where
sal > (select max(sal) from emp where deptno = 30)
/*列出在每个部门工作的雇员的数量以及其他信息*/
select dept.deptno,dept.dname,dept.loc,count(emp.deptno) as count
from dept left join emp on dept.deptno = emp.deptno
group by dept.deptno,dept.dname,dept.loc
/*列出所有雇员的雇员名称、部门名称和薪金*/
select emp.ename,dept.dname,emp.sal from emp,dept
where
emp.deptno = dept.deptno
/*列出从事同一种工作但属于不同部门de雇员的不同组合*/
select e1.ename,e1.job,e1.deptno,e2.ename,e2.job,e2.deptno
from emp e1,emp e2
where 
e1.job = e2.job and e1.deptno != e2.deptno
/*列出分配有雇员数量的所有部门的详细信息即使是分配有0个雇员*/
Select dept.deptno,dname,loc,count(empno)
From dept,emp
Where dept.deptno=emp.deptno(+)
Group by dept.deptno,dname,loc
/*列出各种类别工作的最低工资*/
select job,min(sal) from emp group by job
/*列出各个部门的MANAGER（经理）的最低薪金*/
select deptno,min(sal) from emp
where
job = 'MANAGER'
group by deptno
/*列出按计算的字段排序的所有雇员的年薪*/
select ename,(sal+nvl(comm,0))*12 as pay from emp order by pay

/*======================第三部分=======================*/
/*找出各月倒数第3天受雇的所有员工---函数LAST_DAY*/
select *from emp where hiredate = LAST_DAY(hiredate)-2
/*找出早于12年前受雇的员工*/
select *from emp where MONTHS_BETWEEN(SYSDATE,hiredate)/12>12
/*以首字母大写的方式显示所有员工的姓名----initcap()*/
select initcap(ename) from emp
/*显示正好为5个字符的员工的姓名---length()*/
select ename from emp
where
length(ename) = 5
/*显示不带有"R"的员工的姓名*/
select ename from emp
where
ename not like '%R%'
/*显示所有员工姓名的前三个字符---substr()*/
select substr(ename,1,3)from emp
/*显示所有员工的姓名,用a替换所有"A"---translate()或者用replace()*/
select translate(ename,'A','a')from emp
select replace(ename,'A','a')from emp
/*显示满10年服务年限的员工的姓名和受雇日期*/
select ename,hiredate from emp
where
months_between(sysdate,hiredate)/12>10
/*显示员工的详细资料,按姓名排序*/
select *from emp order by ename
/*显示员工的姓名和受雇日期,根据其服务年限,将最老的员工排在最前面*/
select *from emp
order by months_between(sysdate,hiredate)/12 desc
/*显示所有员工的姓名、工作和薪金,按工作的降序排序,若工作相同则按薪金排序*/
select ename,job,sal from emp
order by job desc,sal
/*显示所有员工的姓名、加入公司的年份和月份,按受雇日期所在月排序,若月份相同则将最早年份的员工排在最前面*/
select ename,to_number(to_char(hiredate,'yyyy')) as year ,to_number(to_char(hiredate,'mm')) as month 
from emp order by month,year
/*显示在一个月为30天的情况所有员工的日薪金,忽略余数*/
select ename,round(sal/30) daysal from emp
/*找出在(任何年份的)2月受聘的所有员工*/
select *from emp
where
to_number(to_char(hiredate,'mm')) = 2
/*对于每个员工,显示其加入公司的天数*/
select ename,floor(sysdate-hiredate) as days from emp
/*显示姓名字段的任何位置包含"A"的所有员工的姓名*/
select ename from emp where ename like '%A%'
select ename from emp where instr(ename,'A')>0
/*以年月日的方式显示所有员工的服务年限--||字符连接符*/
select ename,to_char(hiredate,'YYYY')||'年'||to_char(hiredate,'MM')||'月'||to_char(hiredate,'DD')||'日' from emp
Select ename,months_between(sysdate,hiredate)/12 as "年", months_between(sysdate,hiredate) as "月", sysdate-hiredate as "日" from emp

/*====================第四部分============================*/
/*显示雇员雇佣期满6个月后下一个星期五的日期，显示格式为  "2001年 , 12月 23日,星期六",并按雇佣日期排序*/
select to_char(d,'yyyy')||'年,'||to_char(d,'mm')||'月'||to_char(d,'dd')||'日,星期五'
from
(select next_day(add_months(hiredate,6),6) as d from emp)
/*显示雇员的姓名、受雇用日期及受雇用的当天是星期几（列标题为DAY）,并以DAY排序*/
select ename,hiredate,to_char(hiredate,'dy') as DAY from emp order by DAY
/*显示2005年的母亲节是什么日期(格式：年-月-日)(每年5月的第二个星期日)*/
select (to_char(d,'yyyy')||'年-'||to_char(d,'mm')||'月-'||to_char(d,'dd')||'日') as "母亲节"
from
(select next_day(to_date('2005-5-01','yyyy-mm-dd'),1) as d from dual)
/*显示当前周的起止日期，默认周日为第一天，周六为最后一天*/
select sd,next_day(sd,7) from
(select
        case to_number(to_char(sysdate,'d'))
        when 1 then to_date(to_char(sysdate,'yyyy-mm-dd'),'yyyy-MM-dd') 
        when 2 then to_date(to_char(sysdate,'yyyy-mm-dd'),'yyyy-MM-dd')-1
        when 3 then to_date(to_char(sysdate,'yyyy-mm-dd'),'yyyy-MM-dd')-2 
        when 4 then to_date(to_char(sysdate,'yyyy-mm-dd'),'yyyy-MM-dd')-3 
        when 5 then to_date(to_char(sysdate,'yyyy-mm-dd'),'yyyy-MM-dd')-4 
        when 6 then to_date(to_char(sysdate,'yyyy-mm-dd'),'yyyy-MM-dd')-5
        else to_date(to_char(sysdate,'yyyy-mm-dd'),'yyyy-MM-dd')-6
        end
as sd from dual)
/*显示下一周的今天的日期*/
select to_date(to_char(sysdate,'yyyy-mm-dd'),'yyyy-mm-dd')+7 as next_week from dual
/*用一个查询语句，实现查询各个岗位的总工资和各个部门的总工资和所有雇员的总工资---union/union all,用户合并结果集*/
select e.deptno||'',sum(e.sal) from emp e group by e.deptno
union all
select e.job, sum(e.sal) from emp e group by e.job
union all
select e.ename,sum(e.sal) from emp e group by e.ename
/*用一个查询语句，实现查询各个岗位的总工资和所有雇员的总工资*/
select e.job,sum(sal) from emp e group by e.job
union all
select 'sum',sum(sal) from emp e
/*用一个查询语句，实现查询各个部门的总工资和各个岗位的总工资*/
select e.deptno||'',sum(sal) from emp e group by e.deptno
union all
select e.job,sum(sal) from emp e group by e.job
/*用集合操作实现第8题的要求-----集合*/
select deptno,job,sum(sal) from emp group by deptno,job  
  union  
  select deptno,to_char(null),sum(sal) from emp group by deptno  
  union  
  select to_number(null),to_char(null),sum(sal) from emp
/*通过查询显示每个员工的姓名和工资等级*/
select e.ename,s.grade from emp e, salgrade s
where
e.sal between s.losal and s.hisal 
/*通过查询显示每个员工的姓名和部门名称，并通过部门的不同分组*/
select e.ename,d.dname from emp e,dept d
where
e.deptno = d.deptno order by d.deptno /*或者*/
select e.ename,d.dname from emp e join dept d on d.deptno = e.deptno
/*查询每个员工的姓名和他的上司的名字*/
select e1.ename,e2.ename from emp e1 left join emp e2 on
e1.mgr = e2.empno

/*===================数据操作部分===========================*/
/*1、向emp表中插入一纪录，员工TOM，80年1月10日入职，薪金为3000，没有补贴（comm）*/
insert into emp (empno,ename,hiredate,sal) values('7930','TOM',to_date('1980-1-10','yyyy-mm-dd'),3000)
/*利用子查询建立表emps，与表emp的结构相同，但是只是需要存储10号部门和岗位为MANAGER的员工*/
create table emps as select *from emp where emp.deptno = 10 or emp.job = 'MANAGER'
/*将emps表中的与emp表中scott用户具有相同工作岗位的人的工资更改为原来的105%*/
update emps set sal = sal*1.05
where
job = (select job from emp where ename = 'SCOTT')
/*4、将emp表中的属于同一工资等级的且此级别人数最多的那些人的工资增加3%*/
    /*=====说明====*/
update emp set sal = sal*(1+0.03)
where emp.ename in (
  select nm from 
         /*1、获取表中的职工名字跟工资等级 号 grade---自连接*/
    (select e.ename as nm,s.grade as g from salgrade s,emp e
    where
    e.sal between s.losal and s.hisal
    )
  where g = 
  (
  /*3、获取职工人数最多的工资等级 号 grade*/
  select g from
         /*2、按工资等级数量分组由多到少排序*/
    (select g,count(g) as count from 
            /*1、获取表中的职工名字跟工资等级---自连接*/
      (select e.ename,s.grade as g from salgrade s,emp e
      where
      e.sal between s.losal and s.hisal
      ) t1
    group by g order by count desc
    )
  where rownum = 1
  )
)
/*将emp表中的部门平均工资最低的部门的所有人按照工资等级分别增加1级5%，2级4%，3级3%，4级2%，5级1%*/

create table temptb as
(/*decode()==if-then-else*/
  select empno,sal,decode(grade,1,1.05,2,1.04,3,1.03,4,1.02,5,1.01) addg from emp e,salgrade s
  where e.sal between s.losal and s.hisal and e.deptno = 
  (/*获取平均工资最低的部门编号*/
    select dno from
      (select e.deptno as dno,count(e.deptno),avg(sal) as avge from emp e group by e.deptno order by avge)
      where rownum <=1
  )
)
        
create table temptb as
(/*decode()==if-then-else*/
  select empno,sal,decode(grade,1,1.05,2,1.04,3,1.03,4,1.02,5,1.01) addg from emp e,salgrade s
  where e.sal between s.losal and s.hisal and e.deptno = 
  (/*获取平均工资最低的部门编号*/
    select deptno from 
    (select deptno,avg(sal) as savg from emp group by deptno)
    where savg = 
    (select min(t1.savg)from     /*获取平均工资最低的部门的平均工资*/
      (select avg(sal) savg from emp group by deptno ) t1
    )
  )
)
update temptb set sal = sal*addg

/*6、将emp表中岗位平均工资最高的岗位的所有人插入到新表hi_job_emp*/
create table hi_job_emp as 
select *from emp 
where job = 
(
  select job from
  (select job,avg(sal) as savg from emp group by job order by savg desc
  )where rownum <=1
)











==================================================================================
新手，共同学习中！！！！！！！！！！！！！！！！！
==================================================================================