SQL函数知识点
SQL题目(一)
1、查询部门编号为10的员工信息
select*from emp where empno=10;
2、查询年薪大于3万的人员的姓名与部门编号
select ename,sal from emp where sal*12>30000
3、查询佣金为null的人员姓名与工资
select*from emp where comm is null
4 查询工资大于1500 且 and 含有佣金的人员姓名
select*from emp where comm >0 and sal>1500
5 查询工资大于1500 或 or含有佣金的人员姓名
select*from emp where comm >0 or sal>1500
6查询姓名里面含有 S 员工信息 工资、名称
select SAL,ENAME from emp where ename like '%S%'
7求姓名以J开头第二个字符O的员工姓名的与工资
select ENAME, SAL,empno from emp where ename like '%J%'and ename like '_O%'
8、求包含%的雇员姓名
(1)insert into emp(empno,ename) values(9527,'huan%an');
(2)select * from emp where ename like '%\%%' escape '\'
9 使用in查询部门名称为 SALES 和 RESEARCH 的雇员姓名、工资、部门编号
select * from emp where job in('SALES','CLERK')
10 使用exists查询部门名称为SALES和RESEARCH 的雇员姓名、工资、部门编号。
select * from emp where exists(select * from dept where job in('SALES','CLERK'))
select empno,ename,job,sal from emp e where exists(select* from dept d where job in('SALES','CLERK') and e.epstno=d.epstno)
SQL题目(二)
1、查询10号部门中编号最新入职的员工，工龄最长的员工的个人信息。
2、从“software”找到‘f’的位置，用‘*’左右填充到15位，去除其中的‘a’。
3、查询员工的奖金，如果奖金不为NULL显示‘有奖金’，为null则显示无奖金
4、写一个查询显示当前日期，列标题显示为Date。再显示六个月后的日期，下一个星期 日的日期，该月最后一天的日期。
5、查询EMP表按管理者编号升序排列，如果管理者编号为空则把为空的在最前显示
6、求部门平均薪水
7、按部门求出工资大于1300人员的 部门编号、平均工资、最小佣金、最大佣金,并且最大佣金大于100
8、找出每个部门的平均、最小、最大薪水
9、查询出雇员名，雇员所在部门名称， 工资等级。
*/
--1、查询10号部门中最新入职的员工，工龄最长的员工的个人信息。
select e.* from emp e where e.hiredate in (
(select max(e1.hiredate) from emp e1 where e1.deptno = 10),
(select min(e2.hiredate) from emp e2 where e2.deptno = 10));
--2、从“software”找到‘f’的位置，用‘*’左右填充到15位，去除其中的‘a’。
select instr('software','f') from dual;
select lpad('software',15,'*') from dual;
select rpad('software',15,'*') from dual;
select replace('software','a') from dual;
--3、查询员工的奖金，如果奖金不为NULL显示‘有奖金’，为null则显示无奖金
select decode(e.comm,null,'无奖金','有奖金') from emp e;
--4、写一个查询显示当前日期，列标题显示为Date。
--再显示六个月后的日期，下一个星期 日的日期，该月最后一天的日期。
select sysdate "Date", add_months(sysdate,6) 六个月后 ,next_day(sysdate,'星期日') 下个星期日, last_day(sysdate) 该月最后一天 from dual;
--5、查询EMP表按管理者编号升序排列，如果管理者编号为空则把为空的在最前显示 (nulls first)
select * from emp e order by e.mgr asc nulls first;
--6、求部门平均薪水
select e.deptno ,avg(e.sal) from emp e group by e.deptno;
--7、按部门求出工资大于1300人员的 部门编号、平均工资、最小佣金、最大佣金,并且最大佣金大于100
select e.deptno,avg(e.sal),min(e.comm),max(e.comm) from emp e where e.sal>1300 group by e.deptno having max(nvl(e.comm,0))>100;
--8、找出每个部门的平均、最小、最大薪水
select e.deptno,avg(e.sal),min(e.sal),max(e.sal) from emp e group by e.deptno;
--9、查询出雇员名，雇员所在部门名称，工资等级。
select * from emp e ,dept d where e.deptno = d.deptno;
select * from salgrade;
select e.ename,d.dname from emp e, dept d where e.deptno = d.deptno;
select e.ename ,sg.grade from emp e,salgrade sg where e.sal between sg.losal and sg.hisal;
select e.ename, d.dname, sg.grade
from emp e, dept d, salgrade sg
where e.deptno = d.deptno
and e.sal between sg.losal and sg.hisal;
/*
1、查询82年员工
2、查询32年工龄的人员
3、显示员工雇佣期 6 个月后下一个星期一的日期
4、找没有上级的员工，把mgr的字段信息输出为 "boss"
5、为所有人长工资，标准是：10部门长10%；20部门长15%；30部门长20%其他部门长18%
*/
select * from emp;
--1、查询82年员工
select e.ename from emp e where to_char(e.hiredate,'yy') = '82';
--2、查询32年工龄的人员
select e.*,(months_between(sysdate,e.hiredate)/12) from emp e where (months_between(sysdate,e.hiredate)/12) >= 32;
--3、显示员工雇佣期 6 个月后下一个星期一的日期
select next_day(add_months(e.hiredate,6),'星期一') from emp e;
--4、找没有上级的员工，把mgr的字段信息输出为 "boss"
select e.ename,nvl(to_char(e.mgr),'boss') from emp e;
--5、为所有人长工资，标准是：10部门长10%；20部门长15%；30部门长20%,其他部门长18%
select e.ename,e.sal,e.deptno,(case e.deptno when 10 then e.sal*1.1
when 20 then e.sal*1.15
when 30 then e.sal*1.2
else e.sal*1.18 end ) from emp e;
select e.,decode(e.deptno,10,e.sal1.1,20,e.sal1.15,30,e.sal1.2,e.sal*1.18) from emp e;
select * from emp;
需要注意的点
--分组可以按多个列分组
select e.job,e.sal from emp e group by e.job,e.sal;
--group by分组的列可以不出现在select里，检索字段(select后面跟着的字段)必须出现在分组列表里。
--错误的：select e.job,e.detpno from emp e group by e.job;
--正确的：select e.deptno from emp e group by e.deptno,e.sal,e.job;
--如果分组列中具有null值，则null将作为一个分组返回。如果列中有多行null值，他们将分为一组。
select comm from emp e group by e.comm;
--group by 子句必须出现在where子句之后，order by 子句之前。
select e.deptno from emp e where e.sal>800 group by e.deptno having e.deptno=20 order by e.deptno desc
--where过滤行记录 having 过滤组记录
--select 后面只能跟列( group by 出现的列)或组函数
--不能在 WHERE 子句中使用组函数
--错误的：select e.deptno from emp e where avg(e.sal) group by e.deptno;
--求部门下雇员的平均工资>2000 人数
select e.deptno ,count(1),avg(e.sal) from emp e group by e.deptno having avg(e.sal) > 2000;
/*
Sql语句执行过程
1.读取from子句中的基本表、视图的数据，[执行笛卡尔积操作]。
2.选取满足where子句中给出的条件表达式的元组
3.按group子句中指定列的值分组，同时提取满足Having子句中组条件表达式的那些组
4.按select子句中给出的列名或列表达式求值输出
5.Order by子句对输出的目标表进行排序。
Sql语句执行顺序
from - where - group by - having - select - order by
组函数
--1.min() 求一组数据中的最小值
select min(e.sal) from emp e;
--2.max() 求一组数据中的最大值
select max(e.sal) from emp e;
--3.avg() 求一组数据中的平均值
select avg(e.sal) from emp e;
--4.sum() 求一组数据中的和
select sum(e.sal) from emp e;
--5.count() 求一组数据有多少行
select count(1) from emp e;