1.选择部门30中的所有员工.
select*fromiv_empwheredeptno=30;
2.列出所有办事员(CLERK)的,编号和部门编号.
selectename,empno,deptno
fromiv_emp
wherejob='CLERK';
3.找出佣金高于薪金的员工.
select*
fromiv_emp
wherecomm>sal;
4.找出佣金高于薪金的60%的员工.
select*
fromiv_emp
wherecomm>sal*0.6;
5.找出部门10中所有经理(MANAGER)和部门20中所有办事员(CLERK)的详细资料.
select*
fromiv_emp
where(dept=10andjob='MANAGER')or(dept=20andjob='CLERK');
6.找出部门10中所有经理(MANAGER),部门20中所有办事员(CLERK),既不是经理又不是办事员但其薪金大于或等于2000的所有员工的详细资料.
select*
fromiv_emp
where(dept=10andjob='MANAGER')or(dept=20andjob='CLERK')or(jobnotin('MANAGER','CLERK')andsal>=2000);
-----------------------------------------
单条sql
orandnot
[not]in
[not]betwwenand
isnull
isnotnull
like
----------------------------------------
Oracle内置函数
sysdate
to_char();
to_date();
last_day();
months_between();
initcap();
length();
substr();
replace();
截取小数round()--->四舍五入
trunc()--->不四舍五入
截取日期trunc(sysdate,'month')
year日期所在年份的第一天
month日期所在月份的第一天
day日期所在周的第一天(周日的日期)
add_months();
9.找出各月倒数第3天受雇的所有员工.(last_day())
131-29=2
229-27=2
331-29=2
430-28=2
selecthiredate
fromiv_emp
wherelast_day(hiredate)-2=hiredate;
10.找出早于30年前受雇的员工.
months_between(日期,日期)/12;
selecthiredate
fromiv_emp
wheremonths_between(sysdate,hiredate)/12>=30
11.以首字母大写的方式显示所有员工的.(initcap)
selectinitcap(ename)
fromiv_emp;
12.显示正好为5个字符的员工的(length)
selectename
fromiv_emp
wherelength(ename)=5;
selectename
fromiv_emp
whereenamelike'_____';
13.显示不带有"R"的员工的.
selectename
fromiv_emp
whereenamenotlike'%R%'
14.显示所有员工的前三个字符substr
selectsubstr(ename,1,3)
fromiv_emp;
15.显示所有员工的,用a替换所有"A"replace
selectreplace(ename,'A','a')
fromiv_emp;
16.显示满10年服务年限的员工的和受雇日期.(同10)
17.显示员工的详细资料,按排序.
select*
fromiv_emp
orderbyenameasc[默认]
desc
18.显示员工的和受雇日期,根据其服务年限,将最老的员工排在最前面.
selectename,hiredate
fromiv_emp
orderbyhiredate;
19.显示所有员工的、工作和薪金,按工作的降序排序,若工作相同则按薪金排序.
selectename,job,salary
fromiv_emp
orderbyjobdesc,sal;
20.显示所有员工的、加入公司的年份和月份,按受雇日期所在月排序,若月份相同则将最早年份的员工排在最前面.
selectename,to_char(hiredate,'yyyy')year,to_char(hiredate,'mm')month
fromiv_emp
orderbymonth,yearasc;
21.显示在一个月为30天的情况所有员工的日薪金,忽略余数
截取小数round()--->四舍五入
trunc()--->不四舍五入
截取日期trunc(sysdate,'month')
year日期所在年份的第一天
month日期所在月份的第一天
day日期所在周的第一天(周日的日期)
selecttrunc(sysdate,'year')
fromdual;
selecttrunc(sal/30)
fromiv_emp;
22.找出在(任何年份的)2月受聘的所有员工。
select*
fromiv_emp
whereto_char(hiredate,'mm')=02;
23.对于每个员工,显示其加入公司的天数.
selectsysdate-hiredate
fromiv_emp;
24.显示字段的任何位置包含"A"的所有员工的.
25.以显示所有员工的服务年限.(大概)
26当期日期所在下一个月日期
selectadd_months(sysdate,1)
fromdual;
------------------------------------------------------------
子查询多表联合查询
26查询各个部门的平均工资
selectavg(sal)
fromiv_emp
groupbydeptno;
27显示各种职位的最低工资
sql出现groupby关键字
那么select只能出现groupby
关键字后面的列
以及应用了聚合函数的列
selectjob,min(sal)
fromiv_emp
groupbyjob;
28按照入职日期由新到旧排列员工信息
29查询员工的基本信息,附加其上级的
自连接
selecte1.enameemployee,e2.enameshangji
fromiv_empe1
leftouterjoiniv_empe2
one1.mgr=e2.empno;
30显示工资比ALLEN高的所有员工的和工资
selectsal
fromiv_emp
whereename='ALLEN'
selectename,sal
fromiv_emp
wheresal>(selectsal
fromiv_emp
whereename='ALLEN');
31显示与SCOTT从事相同工作的员工的详细信息
selectjob
fromiv_emp
whereename='SCOTT'
select*
fromiv_emp
wherejob=(selectjob
fromiv_emp
whereename='SCOTT')
andename<>'SCOTT';
32显示销售部(SALES)员工的
selecte.*
fromiv_empe
innerjoiniv_deptd
one.deptno=d.deptno
whered.dname='SALES';
selecte.*
fromiv_empe
wheree.deptno=(selectdeptno
fromiv_dept
wheredname='SALES');
33显示与30号部门MARTIN员工工资相同的员工和工资
selectsal
fromiv_emp
wheredeptno=30andename='MARTIN';
selectename,sal
fromiv_emp
wheresal=(selectsal
fromiv_emp
wheredeptno=30andename='MARTIN';
)
andename!='MARTIN';
34查询所有工资高于平均工资的销售人员(SALES)的个人信息
selecte.*
fromiv_empe
innerjoiniv_deptd
one.deptno=d.deptno
whered.dname='SALES'andsal>(selectavg(sal)fromiv_emp);
35显示所有职员的及其所在的部门的名称和工资
selecte.ename,d.dname,e.sal
fromiv_empe
innerjoiniv_deptd
one.deptno=d.deptno;
36查询在研发部(RESEARCH)工作员工的编号,,工作部门,工作所在位置。
selecte.empno,e.ename,d.dname,d.loc
fromiv_empe
innerjoiniv_deptd
one.deptno=d.deptno
whered.dname='RESERCH';
37查询各个部门的名称和员工个数
count(*)统计行数
count(empno)统计这个列中非空数据的个数
selectd.dname,count(empno)
fromiv_empe
rightouterjoiniv_deptd
one.deptno=d.deptno
groupbyd.dname;
38查询各个职位员工工资大于平均工资的人数和员工职位
selectavg(sal)
fromiv_emp
selectjob,count(*)
fromiv_emp
wheresal>(selectavg(sal)
fromiv_emp)
groupbyjob;
39查询工资相同的员工的工资和
selectsal
fromiv_emp
groupbysal
havingcount(*)>1;
selectsal,ename
fromiv_emp
wheresalin(selectsal
fromiv_emp
groupbysal
havingcount(*)>1);
40查询工资最高的3名员工信息
selecte.*
from(select*fromiv_emporderbysaldesc)e
whererownum<=3;
41求入职日期相同的(年月日相同)的员工(同39)
42查询每个部门的最高工资
selectdeptno,max(sal)
fromiv_emp
groupbydeptno;
43查询每个部门,每种职位的最高工资
selectdeptno,job,max(sal)
fromiv_emp
groupbydeptno,job;
45查询工资最高的第6--10名员工
selectee.*
from(selecte.*,rownumr
from(select*fromiv_emporderbysaldesc)e
)ee
wherer<=10andr>=6;
46查询各部门工资最高的员工信息
selectmax(sal)
fromiv_emp
groupbydeptno
select*
fromiv_emp
wheresalin(selectmax(sal)
fromiv_emp
groupbydeptno);
insertintoiv_emp(empno,ename,sal,deptno)
values(8000,'suns',3000,10);
selectdeptno,max(sal)
fromiv_emp
groupbydeptno;
selecte.*
fromiv_empeinnerjoin
(selectdeptno,max(sal)maxsal
fromiv_emp
groupbydeptno)m
one.deptno=m.deptno
wheree.sal=m.maxsal;
47查询出有3个以上下属的员工信息
select*
fromiv_emp
whereempnoin(
selectmgr
fromiv_emp
groupbymgr
havingcount(*)>3);
48查询所有大于本部门平均工资的员工信息
selectdeptno,avg(sal)avgsal
fromiv_emp
groupbydeptno;
selecte.*
fromiv_empe
innerjoin(selectdeptno,avg(sal)avgsal
fromiv_emp
groupbydeptno)ee
one.deptno=ee.deptno
wheree.sal>ee.avgsal;