Oracle 数据库基础知识


depyno 部门编号 部门表
dname    部门名称
location 地址
----------------------------------------
empno     员工编号        员工表
ename     员工名称
job       职位
salary    薪资
bonus     奖金
hiredate  入职时间
mgr       上级领导
deptno    所属部门


1.列出至少有一个员工共的所在部门
select deptno
from emp
group by deptno
having count(deptno)>1


select deptno from dept d
where  not exists (select 1 from emp where deptno = d.deptno)


2.列出薪资比 ‘smith’高的所有员工
select * from emp where sal =(select sal from emp where ename='SMITH')


3.列出所有员工的姓名 和他上级的姓名
select ename,(select ename  from emp where empno =f.mgr) from emp f


4.列出入职日期早于上级的入职日期的员工
select empno from emp f where hiredate < (select hiredate from emp where empno=f.mgr)


5.列出最低薪资大于1500的工作
select deptno,min(job)
from emp
group by deptno
having min(sal)>15


6.列出在每个部门的员工数量,平均薪资和平均服务期限
select deptno 部门编号, count(deptno) 员工数量,avg(nvl(sal,0)) 平均薪资,avg(sysdate - hiredate)/365 入职期限
from emp
group by deptno



1.查询工资在0-1000,1000-2000,2000-3000,3000以上各个工资范围的员工数
select deptno ,sum(case when sal>=0 and sal <=1000 then 1 else 0 end ) as "工资0-1000元的员工",
      sum (case when sal>=1000  and sal <=2000 then 1 else 0 end) as "工资在1000-2000的员工",
      sum(case when sal >= 2000 and sal <=3000 then 1 else 0 end)  as "工资在2000-3000的员工",
      sum(case when sal>3000 then 1 else 0 end)  as "工资在3000元以上的员工"
from emp
group by deptno


2.求出每个部门的最低工资的员工信息
select ename,sal from emp where sal in (select min(sal)
from emp
group by deptno)


select ename, sal from emp f where sal=(select min(sal) from emp where deptno=f.deptno)


5.查询各个职位工资大于平均工资(所有哦人)的人数和员工职位
select count(*), min(job) from emp where sal>(select avg(sal) from emp)


8,返回比本部门平均工资高的员工的empno ename deptno sal,以及平均工资
select empno ,ename,deptno,sal,(select avg(sal) from emp where deptno=f.deptno)
from emp f where sal > (select avg(sal) from emp where deptno=f.deptno)


9查询与7369 或者7499号相同job 和deptno 的其他员工的 empno ename job
select empno ,ename ,job from emp where (job,deptno) in (select job,deptno from emp where empno =7369 or empno = 7499)


10.列出从事同一职位,但不属于统一部门的员工的不同组合
select a.ename,b.ename
from emp a,emp b
where a.job=b.job and a.deptno<>b.deptno


14.把hiredate 看作是员工的生日,求本月过生日的员工
select * from emp
where to_char(hiredate,'mm')=to_char(sysdate,'mm')


15.查询出1981各个月入职的员工数
select to_char(hiredate,'mm'),count(*)
from emp
where to_char(hiredate,'yyyy')=1981
group by to_char(hiredate,'mm')


//////////////////////////////////////////////基础练习/////////////////////////////////////////
1.计算员工的月收入(包括工资和奖金)
select ename, sal + nvl(comm,0)
from emp


二. distinct  消除重复项    
     1. 机构中有多少种职位  
    select  count(distinct job) from emp


2. 查询每个部门不重复的职位  
     select deptno,job from emp


三. 转义字符 escape '/'
           1. 列出职位中第二个字是a的员工数据          
           select * from emp where JOB like '_a%'  escape '/'


2. 查询数据库中有多少名字以&lsquo;S_&rsquo;开头的表          
           select count(*)  from tables where tables_name like 'S/_%'  escape '/'


3 查询数据库中名字带&ldquo;%&lsquo;的数据          
           select  from tables where tables_name lick '%/%? escape '/'


四.nvl(字段,代替数值)   判断是否为空,如果为空,又第二个值代替;'


1.  计算总月收入多少
          select sal+nvl(comm,0) from emp   每个人


select  sum(sal+nvl(comm,0)) from emp   每个部门
          group by deptno


五.字符函数 substr(截取字符串) upper(转为大写) lower(转为小写) initcap(每个单词首字母转大写) length(取长度)  tirm(消除空格)
           lpad(左边填充字符)  rpad(右边填充字符串)   replace(string,ds,ds)替换字符串,在第一个字符串里查找第二个字符,然后用第三个字符替换第二个


六.数字函数:round(四舍五入)  ceil(decimal)向上取整 floor(decimal)向下取整


七.日期函数:months_between(date ,date)两个日期之间的月份数
            add_months(date , 3)  为指定日期增加指定月份数
            last_ady( date )  获取指定时间的最后一天


1.  计算员工入职多少天?             时间类型 相减,会得到天数      
          select round(sysdate-hiredate)
          from emp


2 . 计算员工入职多少个月        
          select months_between(sysdate,hiredate) from emp


3.  计算本月的最后一天
          select distinct last_day(sysdate) from emp


八. 转换函数:to_cahr(date, '格式 ')  将日期转换为指定格式的字符串


to_date(string, ' 格式 ')   将字符串按指定格式转为为日期


九.case: 跟C#里面的switch一样
             case  字段 when  '值 ' then 返回值 else 0 end
             case when sal>1000 then 1  else 0 end


1.根据员工的职位,计算加薪后的薪水数据
                如果职位是Analyst , 加薪10%
                如果职位是Programmer 加薪5%
                如果职位是clerk ,加薪2%
                其他职位,薪水不变


select case when job='Analyst' then sal * 1.1
                            when job='Programmer' then sal *1.05
                             when job ='clerk ' then sal * 1.02
                              else sal end
                from emp


十. decode() 函数: 和case 一样的功能,语法如下:
               decode(字段,'等于值1 ' ,   '返回 ',
                            '等于值2 ' ,   '返回 ',
                            '等于值3'  ,   '返回' ,
                            '都不等于,返回')


列题同上:
            select decode(job,'Analyst',sal * 1.1,
                              'Programmer',sal * 1.05,
                              'clerk', sal * 1.02,sal
            )      
            from emp


1. 计算最早和最晚的员工入职时间  
   select min(hiredate),max(hiredate)
   from emp


2.计算每个部门的最高,最低薪水,薪水总和,平均薪水,总人数等等。
   select max(sal) 最高薪水, min(sal) 最低薪水,avg(sal) 平均薪水,count(*) 总人数
   from emp
   group by deptno


3.每个职位的最高,最低薪水和人数?
   select max(sal),min(sal),count(deptno)
   from emp
   group by deptno


4.查询平均薪水大于2000元的部门数据?  
   select deptno
   from emp
   group by deptno
   having avg(sal) > 2000


5.求薪水总和大于20000的部门数据
   select deptno
   from emp
   group by deptno
   having sum(sal)>20000


6.那些职位的人数超过两个
   select deptno
   from emp
   group by deptno
   having count(*)>2


7.查询最高薪水是谁?
   select ename,sal from emp where sal in(select max(sal)
   from emp
   )


8.研发部又哪些职位?
  select job from emp deptno =(select deptno from dept where dname='研发部')


9.谁的薪水比张无忌高?  
   select * from emp where dal > (  select dal from emp where ename='张无忌')


10.谁和刘苍松同门,列出除了刘苍松之外的员工名字
  select ename from emp where deptno in(select deptno from emp where ename='刘苍松')
  and ename <> '刘苍松'


11.每个部门拿最高薪水的是谁?
   select ename ,sal
   from emp  f where sal =(select max(sal) from emp where deptno =f.deptno)


12.哪个部门的人数 比30部门的人数多
  select deptno
  from emp  group by deptno
  having count(*)<(select count(*) from emp where deptno ='30' )


13.列出员工的名字和职位,这些员工所在的部门平均薪水大于5000
   select ename,job from emp where deptno in(  select deptno from emp group by deptno
         having avg(sal)>5000
   )


14.哪些员工的薪水 比本部门的平均薪水低?
   select  ename
   from emp f
   where sal<(select avg(sal) from emp where deptno =f.deptno)