sql习题二--函数的使用，多表查询

2019独角兽企业重金招聘Python工程师标准>>>

1.显示编号为7839的员工名字(名字小写)，薪水
SQL> select lower(ename) ,sal from emp where empno=7839;
 
2.显示编号为7839的员工名字(首字符大写其他小写)，薪水
 SQL> select initcap(ename) ,sal from emp where empno=7839;
 
3.显示有字符A的员工的名字，薪水
SQL> select sal ,ename from emp where regexp_instr(ename,'A',1)>0;
 
 >>select ename,sal from emp where ename lile '%A%';
 
4.显示一个A字符的员工的名字，薪水
SQL> select sal ,ename from emp where regexp_count(ename,'A',1)=1 ;
 
5.显示二个A字符的员工的名字，薪水
SQL> select sal ,ename from emp where regexp_count(ename,'A',1)=2 ;
 
6.显示超过二个A字符的员工的名字，薪水
 SQL> select sal ,ename from emp where regexp_count(ename,'A',1)>2 ;
 
 
7.显示有A字符员工名字，薪水[员工名字的A字符显示为小写a字符]
SQL> select sal ,regexp_replace(ename,'A','a') as "new" from emp where
regexp_count(ename,'    A',1)>0;
 
>>>select ename,sal,replace(ename,'A','a') from emp;
 
8.显示员工名字，名字含有A字符出现的字符位置，薪水
SQL> select sal, ename ,instr(ename,'A') from emp;
 
9.显示员工名字，名字字符长度，薪水
SQL> select ename,length(ename),sal from emp;
 
10.显示员工名字，职位，薪水，职位列名居中显示，职位列值靠右显示
SQL> select ename,lpad(job,8),sal from emp;
>>>col job just center
    select ename,job,sal,lpad(job,15) from emp;
 
11.显示员工名字，薪水[名字中T,TT,TTT这样相同T字符结尾的不显示，其他字符要显示]
SQL> select trim(trailing 'T' from ename),sal from emp;
>>>select ename,sal,rtrim(ename,'T') from emp;
 
12.不使用||操作符实现部门表每个列之间使用逗号分割显示
SQL> select concat(concat(concat(concat(deptno,','),dname),','),loc) from
dept;
 
13.显示员工名字，薪水[名字字符显示为第一个字符到第一次出现A的所有字符]
SQL> select
sal,substr(ename,1,decode(instr(ename,'A'),0,length(ename),instr(ename,'A')))
from emp;
 
14.显示员工名字，薪水[名字字符显示为第一次出现A的字符到最后一个字符]
SQL> select
sal,substr(ename,decode(instr(ename,'A'),0,1,instr(ename,'A')),length(ename))
from emp;
 
 
15.显示员工名字，薪水增加500，减少500，乘以500，除以500的结果
SQL> select ename,sal+500,sal-500,sal*500,sal/500 from emp;
 
16.显示日期，显示当前时间，显示当前时间戳
SQL> select sysdate,to_char(sysdate,'yyyy/mm/dd HH24:MI:SS'),systimestamp from
dual;
 
17.显示员工名字，入职时间，到今天经过了多少天
SQL> select ename  hiredate,sysdate-hiredate from emp;
 
18.显示员工名字，入职时间，到今天经过了多少周
SQL> select hiredate,(sysdate-hiredate)/7 from emp;
 
19.显示员工名字，入职时间，到今天经过了多少年
SQL> select hiredate,(sysdate-hiredate)/365 from emp;
>>> select hiredate,ename ,(months_between(sysdate,hiredate))/12 from emp;
20.显示员工名字，入职时间，到今天经过了多少月
SQL> select hiredate,to_char(sysdate,'yyyy')-to_char(hiredate,'yyyy') from
emp;
 
21.显示员工名字，入职时间，3个月转正的时间
SQL> select hiredate,add_months(hiredate,3) from emp;
 
22.显示员工名字，入职时间，第一次上班时间[办理入职后下个星期一]
SQL> select hiredate,next_day(add_months(hiredate,3),'MON') from emp;
 
23.显示员工的总行数
SQL> select count(*) from emp;
 
24.显示员工的薪水总和，最大值，最小值，平均值
SQL> select sum(sal), max(sal),min(sal),avg(sal) from emp;
 
25.显示员工的名字最大值，最小值
SQL> select max(ename),min(ename) from emp;
 
26.显示员工的唯一部门总数
SQL> select count(distinct deptno) from emp;
 
27.通过奖金统计员工总数，统计有奖金的员工总数
SQL> select count(comm),count(nvl(comm,0)) from emp;
 
28.显示每个部门的总人数，薪水总和，最大值，最小值
SQL> select deptno,count(*),sum(sal),max(sal),min(sal) from emp group by
deptno;
 
29.显示每个职位的总人数，薪水总和，最大值，最小值
SQL> select job,count(*),sum(sal),max(sal),min(sal) from emp group by job;
 
30.显示职位薪水总和超过3000的职位，职位薪水总和
SQL> select job,sum(sal)  from emp group by job having sum(sal)>3000 ;
 
31.显示当前部门的每个职位的总人数，薪水总和，最大值，最小值
SQL> select deptno, job,count(*),sum(sal),max(sal),min(sal)  from emp group by
job,deptno ;
 
 
32.显示部门平均薪水最大值，部门平均薪水最小值
SQL> select  max(avg(sal)),min(avg(sal)) from emp group by deptno;
 
33.统计员工入职时间在同一年的人数
SQL> select to_char(hiredate,'yyyy'),count(*) from emp group by
to_char(hiredate,'yyyy');
 
 
 
 
 
练习
1.找出比10部门最低工资要高的员工，显示姓名，部门编号，薪水
SQL> select ename,deptno,sal from emp where sal>(select min(sal) from emp
where deptno=10);
 
2.找出比自己部门平均薪水要低的员工，显示姓名，薪水
SQL> select ename,sal from emp e where sal<(select avg(sal) from emp where
e.deptno=deptno);
 
3.找出除KING外工资最高的员工，显示姓名，职位，薪水
SQL> select ename,job,sal from emp where sal=(select max(sal) from emp where
ename<>'KING');
 
 
 
4.找出职位是CLERK,并且薪水比SCOTT要高的员工有哪些
>>>select * from emp where job='CLERK' and sal >(select sal from emp where
ename='SCOTT');
 
SQL> select ename from emp where sal>(select sal from emp where
 ename='SCOTT') and job='CLERK';
 
5.找出每个部门的薪水前2名，显示姓名，部门编号，薪水
SQL> select * from(select ename,deptno,sal,row_number() over(partition by
deptno order by sal desc) asal from emp ) where asal<=2;
 
6.找出薪水排名在6-8的员工，显示薪水和姓名
SQL> select * from(select ename,deptno,sal,row_number() over(partition by
deptno order by sal desc) asal from emp ) where asal between 6 and 8;
 
 
7.显示员工的部门名，部门总人数
SQL> select dname,(select count(*) from dept where d.deptno=deptno) from dept
d;
 
8.显示员工的部门编号，部门名，职位，薪水
select deptno,dname,job,sal from emp natural join dept;
select deptno,dname,job,sal from emp e,dept d where d.deptno=e.deptno;
select deptno,dname,job,sal from emp join dept using(deptno);
select deptno,dname,job,sal from emp e join dept d on(d.deptno=e.deptno);
SQL> select dname,job,sal  from emp e join dept d on(d.deptno=e.deptno);
 
9.显示员工的部门名，员工名字，薪水等级
select dname,ename,grade from emp natural join dept join salgrade on(sal
between losal and hisal);
 
10.显示员工的工作地址，部门名，员工名字，薪水等级。
 
select loc, dname,ename,grade from emp natural join dept join salgrade
on(sal between losal and hisal);

 

转载于:https://my.oschina.net/liubaizi/blog/796106