oracle 55道练习题（若有问题请留言）

EMP表

DEPT表

–1查询20号部门的所有员工信息

select * from EMP t where deptno = '20'

–2查询所有工种为CLERK的员工的工号、员工名和部门名。

select t.empno, t.ename, d.dname
  from emp t
  left join dept d
    on t.deptno = d.deptno
 where t.job = 'CLERK'

–3查询奖金（COMM）高于工资（SAL）的员工信息
select * from emp where nvl(comm,0) > sal

在这里插入代码片

–4查询奖金高于工资的20%的员工信息

select * from emp where nvl(comm,0) > 0.2 * sal

–5查询10号部门中工种为MANAGER和20号部门中工种为CLERK的员工的信息
–(1)

select * from emp where job = 'MANAGER' and deptno = '10'
union select * from emp where job = 'CLERK' and deptno = '20'

–(2)

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 distinct (job) from emp where comm is not null

–8查询所有员工 工资和奖金的和

select sum(sal), sum(comm) from emp
select sum(sal) + sum(comm) from emp

–9查询没有奖金或奖金低于100的员工信息

select *
  from emp
 where comm < 100
    or comm is null

–10查询各月倒数第2天入职的员工信息

select * from emp where hiredate = last_day(hiredate) - 2

–last_day(date)函数返回指定日期对应月份的最后一天

–11查询员工工龄大于或等于10年的员工信息

select * from emp where months_between(sysdate, hiredate) / 12 >= 10

–MONTHS_BETWEEN函数返回两个日期之间的月份数。
–months_between(date1,date2)函数返回两个日期之间的月份数

–12查询员工信息，要求以首字母大写的方式显示所有员工的姓名

select initcap(ename) from emp

–initcap()首字母大写函数

–13查询员工名正好为6个字符的员工的信息
–(1)

select * from emp where length(ename) = 6

–(2)

select * from emp where ename like '______'

–14查询员工名字中不包含字母“S”员工

select * from emp where ename not like '%S%'

–15查询员工姓名的第2个字母为“M”的员工信息

select * from emp where ename like '_M%'

–"_"(下划线)用于表示一个字符
–"%"(百分号)用于表示0个或多个字符

–16查询所有员工姓名的前3个字符

select substr(ename, 1, 3) from emp

–substr函数
–格式1： substr(string string, int a, int b); //string 字符串，从a位开始截取，截取长度b
–格式2：substr(string string, int a) ; //string 字符串，截取从第a位开始后面所有字符

–17查询所有员工的姓名，如果包含字母“S”，则用“s”替换

select replace(ename, 'S', 's') from emp

–UPPER();全部转换为大写
–LOWER();全部转换为小写
–INITCAP();首字母大写
–replace(字符串1，字符串2，字符串3)：将串1中所有的串2，替换成串3

–18查询员工的姓名和入职日期，并按入职日期从先到后进行排列

select ename, hiredate from emp order by hiredate

–19显示所有的姓名、工种、工资和奖金，按工种降序排列，若工种相同则按工资升序排列

select ename, job, sal, comm from emp order by job desc, sal asc

–20显示所有员工的姓名、入职的年份和月份，若入职日期所在的月份排序，若月份相同则按入职的年份排序
–(1)

select ename,to_char(hiredate, 'yyyy') year,to_char(hiredate, 'MM') month
  from emp
 order by month, year

–(2)

 select ename,
        extract(year from hiredate) year,
        extract(month from hiredate) month
   from emp
  order by month, year

–extract()函数，截取年year\月month\日day\时timezon_hour\分minute\秒second
–时间间隔extract(day from （t1-t2）)

–21查询在2月份入职的所有员工信息
–(1)

select * from emp where to_char(hiredate, 'MM') = '02'

–(2)

select * from emp where extract(month from hiredate) = '2'

–22查询所有员工入职以来的工作期限，用“年月**日”的形式表示。（不做）

select months_between(sysdate, hiredate) / 12 as "年",
       months_between(sysdate, hiredate) as "月",
       sysdate - hiredate as "日"
  from emp

–23查询至少有一个员工的部门信息

select distinct dname from dept where deptno in(select distinct deptno from emp)

–24查询工资比SMITH员工工资高的所有员工信息

select * from emp where sal > (select sal from emp where ename = 'SMITH')

–25查询所有员工的姓名及其直接上级的姓名
–(1)

select e.ename as emp_name,
       (select b.ename from emp b where b.empno = e.mgr) as boss_name
  from emp e

–(2)

select  e.ename,b.ename from emp e left join emp b on e.mgr = b.empno

–26查询入职日期早于其直接上级领导的所有员工信息

select a.ename as 员工 ，b.ename as 领导, a.hiredate, b.hiredate
  from emp a
  left join emp b
    on a.mgr = b.empno
 where a.hiredate < b.hiredate

–27查询所有部门及其员工信息，包括那些没有员工的部门

select a.*, b.dname from emp a left join dept b on a.deptno = b.deptno

–28查询所有工种为CLERK的员工的姓名及其部门名称

select e.ename, d.dname
  from emp e
  left join dept d
    on e.deptno = d.deptno
 where e.job = 'CLERK'

–29查询最低工资大于2500的各种工作

select distinct job from emp where sal > 2500

–31查询最低工资低于2000的部门及其员工信息

select a.ename ename,min(a.sal), b.dname dname
  from emp a
  left join dept b
    on a.deptno = b.deptno
group by ename,dname
having min(sal)<2000

–having函数用在group by子句的后面，对分组结果集进行条件筛选。可以使用别名.

–32查询在SALES部门工作的员工的姓名信息
–(1)

select e.*
  from emp e
  left join dept d
    on e.deptno = d.deptno
 where d.dname = 'SALES'

–(2)

select *
  from emp
 where deptno = (select deptno from dept where dname = 'SALES')

–33查询工资高于公司平均工资的所有员工信息

select empno, ename, job, mgr, hiredate, sal, comm
  from emp
  left join (select avg(sal) avgsal from emp)
    on 1 = 1
 where sal > avgsal

–34查询与SMITH员工从事相同工作的所有员工信息

select *
  from emp
 where job = (select job from emp where ename = 'SMITH')
   and ename != 'SMITH'

–35列出工资等于30号部门中某个员工工资的所有员工的姓名和工资。

select *
  from emp
 where sal in (select sal from emp where deptno = 30)
   and deptno != 30

–36查询工资高于30号部门中工作的所有员工的工资的员工姓名和工资

select *
  from emp
 where sal > (select max(sal) maxsal from emp where deptno = 30)

–37查询每个部门中的员工数量、平均工资和平均工作年限

select count(ename) sum,
       avg(sal) avgsal,
       avg((sysdate - hiredate) / 365) avgyear
  from emp

–trunc 函数可用于截取日期时间 例：trunc(sysdate,’yyyy’)

–38查询从事同一种工作但不属于同一部门的员工信息

select e.*
  from emp e, emp m
 where e.job = m.job
   and e.deptno != m.deptno

–39查询各个部门的详细信息以及部门人数、部门平均工资

select d.deptno, d.dname, count(e.empno), avg(e.sal)
  from emp e, dept d
 where e.deptno = d.deptno
 group by d.deptno

–40查询各种工作的最低工资
select job,min(sal) from emp group by job

–41查询各个部门中的不同工种的最高工资

select max(sal), job from emp group by job

–42查询10号部门员工以及领导的信息

select e.*, m.*
  from emp e, emp m
 where e.empno = m.mgr
   and e.deptno = 10

–43查询各个部门的人数及平均工资
select count(deptno), round(avg(sal), 2), job from emp group by job

–44查询工资为某个部门平均工资的员工信息

select * from emp where sal = (select avg(sal) from emp)

–45查询工资高于本部门平均工资的员工的信息

select e.*
  from emp e
  left join (select round(avg(sal), 2) avgg, deptno from emp group by deptno) m
    on e.deptno = m.deptno
 where e.sal > m.avgg

–46查询工资高于本部门平均工资的员工的信息及其部门的平均工资

select e.*,
       m.avgg
  from emp e
  left join (select round(avg(sal), 2) avgg, deptno from emp group by deptno) m
    on e.deptno = m.deptno
 where e.sal > m.avgg

–47查询工资高于20号部门某个员工工资的员工的信息

select e.*
  from emp e
 where e.sal > (select max(sal) from emp where deptno = '20')
   and e.deptno != '20'

–48统计各个工种的人数与平均工资

select count(empno), round(avg(sal), 2), job from emp group by job

–49统计每个部门中各个工种的人数与平均工资

select count(empno), avg(sal), deptno, job from emp group by deptno, job

–50查询工资、奖金与10号部门某个员工工资、奖金都相同的员工的信息

select e.*
  from emp e
  left join (select sal, comm from emp where deptno = '10') s
    on 1 = 1
 where e.sal = s.sal
   and e.comm = s.comm
   and deptno != '10'

–51查询部门人数大于5的部门的员工的信息

select e.empno, e.ename, e.sal, e.comm, e.hiredate, e.deptno, s.cout
  from emp e
  left join (select count(empno) cout, deptno from emp group by deptno) s
    on e.deptno = s.deptno
 group by e.empno, e.ename, e.sal, e.comm, e.hiredate, e.deptno,s.cout
having s.cout > 5

–52查询所有员工工资都大于1000的部门的信息

select *
  from dept
 where deptno in
       (select deptno from emp group by deptno having min(sal) > 1000)

–53查询所有员工工资都大于1000的部门的信息及其员工信息

select d.*, e.*
  from dept d
  left join emp e
    on d.deptno = e.deptno
 where d.deptno in
       (select deptno from emp group by deptno having min(sal) > 1000)

–54查询所有员工工资都在900~3000之间的部门的信息

select d.*
  from dept d
  left join emp e
    on e.deptno = d.deptno
 where e.sal between 900 and 3000

–55查询所有工资都在900~3000之间的员工所在部门的员工信息

select e.*
  from dept d
  left join emp e
    on e.deptno = d.deptno
 where e.sal between 900 and 3000