oracle基本语句练习.docx
- 文档编号:9956951
- 上传时间:2023-02-07
- 格式:DOCX
- 页数:13
- 大小:19.47KB
oracle基本语句练习.docx
《oracle基本语句练习.docx》由会员分享,可在线阅读,更多相关《oracle基本语句练习.docx(13页珍藏版)》请在冰豆网上搜索。
oracle基本语句练习
--1、选择部门30中的雇员
select*fromempwheredeptno=30;
--2、列出所有办事员的姓名、编号和部门
selectename,empno,dnamefromempeinnerjoindeptdone.deptno=d.deptnowherejob=upper('clerk’);
--3、找出佣金高于薪金的雇员
select*fromempwherecomm>sal;
--4、找出佣金高于薪金60%的雇员
select*fromempwherecomm>sal*0.6
--5、找出部门10中所有经理和部门20中的所有办事员的详细资料
select*fromempwhere(deptno=10andjob=upper('manager'))or(deptno=20andjob=upper('clerk'));
--6、找出部门10中所有经理、部门20中所有办事员,既不是经理又不是办事员但其薪金>=2000的所有雇员的详细资料
select*fromempwhere(deptno=10andjob=upper('manager'))or(deptno=20andjob=upper('clerk'))or(job<>upper(‘manager’)andjob<>upper(‘clerk’)andsal>=2000)
--7、找出收取佣金的雇员的不同工作
selectdistinctjobfromempwherecomm>0;
--8、找出不收取佣金或收取的佣金低于100的雇员
select*fromempwherenvl(comm,0)<100;
--9、找出各月最后一天受雇的所有雇员
select*fromempwherehiredate=last_day(hiredate);
--10、找出早于25年之前受雇的雇员
select*fromempwheremonths_between(sysdate,hiredate)/12>25;
select*fromempwherehiredate
--11、显示只有首字母大写的所有雇员的姓名
selectenamefromempwhereename=initcap(ename);
--12、显示正好为6个字符的雇员姓名
selectenamefromempwherelength(ename)=6
--13、显示不带有'R'的雇员姓名
Selectenamefromempwhereenamenotlike‘%R%’;
Selectenamefromempwhereinstr(ename,’R’)=0;
--14、显示所有雇员的姓名的前三个字符
selectsubstr(ename,1,3)fromemp
--15、显示所有雇员的姓名,用a替换所有'A'
Selectreplace(ename,’A’,’a’)fromemp
--16、显示所有雇员的姓名以及满10年服务年限的日期
Selectename,add_months(hiredate,12*10)‘服务年限的日期’fromemp
--17、显示雇员的详细资料,按姓名排序
Select*fromemporderbyename
--18、显示雇员姓名,根据其服务年限,将最老的雇员排在最前面
Selectenamefromemporderbyhiredate
--19、显示所有雇员的姓名、工作和薪金,按工作的降序顺序排序,而工作相同时按薪金升序
Selectename,job,salfromemporderbyjobdesc,salasc
--20、显示所有雇员的姓名和加入公司的年份和月份,按雇员受雇日所在月排序,将最早年份的项目排在最前面
selectename,to_char(hiredate,'yyyy'),to_char(hiredate,'mm')fromemporderbyhiredateasc
--21、显示在一个月为30天的情况下所有雇员的日薪金
selectename,sal/30fromemp;
--22、找出在(任何年份的)2月受聘的所有雇员
select*fromempwhereto_char(hiredate,'mm')='02';
--23、对于每个雇员,显示其加入公司的天数
selectename,sysdate-hiredatefromemp
--24、显示姓名字段的任何位置,包含"A"的所有雇员的姓名
selectenamefromempwhereenamelike'%A%';
selectenamefromempwhereinstr(ename,’A’,1)>0;
--25、以年、月和日显示所有雇员的服务年限
Selectmonths_between(sysdate,hiredate)/12as“年”,months_between(sysdate,hiredate)as“月”,sysdate-hiredateas“日”fromemp
empdept是oracle中的两张默认表
每天都花上十分钟写一写,保证自己不会忘记啊~
--连接到scott用户
connectscott/tiger;
--查看当前用户下的所有表
select*fromtabwheretabType='TABLE';
--查看表结构
descdept;
descemp;
--1列出emp表中各部门的部门号,最高工资,最低工资
selectdeptnoas部门号,max(sal)最高工资,min(sal)as"最低工资"
fromempgroupbydeptno;
--2列出emp表中各部门job为'CLERK'的员工的最低工资,最高工资
selectdeptnoas部门号,max(sal)最高工资,min(sal)as"最低工资"
fromempwherejob='CLERK'groupbydeptno;
--3对于emp中最低工资小于1000的部门,列出job为'CLERK'的员工的部门号,最低工资,最高工资
--查询出最低工资小于1000的部门号及最低工资值
--selectdeptno,min(sal)fromempgroupbydeptnohavingmin(sal)<1000
--查询出最低工资小于1000的部门号
--selectdeptnofromempgroupbydeptnohavingmin(sal)<1000;
--子查询
selectdeptnoas部门号,max(sal)最高工资,min(sal)as"最低工资"
fromempwherejob='CLERK'
anddeptnoin(selectdeptnofromempgroupbydeptnohavingmin(sal)<1000)
groupbydeptno;
--4写出对上题的另一解决方法
--where+groupby+having
selectdeptnoas部门号,max(sal)最高工资,min(sal)as"最低工资"
fromempwherejob='CLERK'groupbydeptnohavingmin(sal)<1000;
--子查询和父查询关联起来,做联接查询
select deptnoas部门号,max(sal)最高工资,min(sal)as"最低工资"
fromempawherejob='CLERK'
and1000>(selectmin(sal)fromempbwherea.deptno=b.deptno)
groupbydeptno;
--5根据部门号由高而低,工资由低而高列出每个员工的姓名,部门号,工资
selectename姓名,deptno部门号,sal工资fromemporderbydeptnodesc,salasc;
--6列出'张三'所在部门中每个员工的姓名与部门号
selectename,deptnofromempwhere
deptnoin(selectdeptnofromempwhereename='张三');
--selectename,deptnofromempwheredeptno=20ordeptno=30;
--selectename,deptnofromempwheredeptnoin(20,30);
--7列出每个员工的姓名,工作,部门号,部门名
--内联接:
返回两个表匹配的数据
--1.
selecte.ename,e.job,d.deptno,d.dnamefromempe
innerjoin
deptd
one.deptno=d.deptno;
--2.
selecte.ename,e.job,d.deptno,d.dnamefromempe,deptdwheree.deptno=d.deptno;
--8列出emp中工作为'CLERK'的员工的姓名,工作,部门号,部门名
--1.
selecte.ename,e.job,d.deptno,d.dnamefromdeptd
innerjoin
empe
one.deptno=d.deptno
wheree.job='CLERK';
--2.
selecte.ename,e.job,d.deptno,d.dnamefromempe,deptd
wheree.deptno=d.deptnoande.job='CLERK';
--9对于emp中有管理者的员工,列出姓名,管理者姓名(mgr列是管理者员工号)
--内联接的特列:
自联接
selecta.ename,b.enamefromempa,empbwherea.mgr=b.empno;
--10对于dept表中,列出所有部门名,部门号,同时列出各部门工作为'CLERK'的员工名与工作
--外联接:
左外联:
返回左表所有数据和右表匹配的数据
--1.通用的左外连接写法
selectd.dname,d.deptno,e.ename,e.jobfromdeptd
leftjoin
(select*fromempwhereemp.job='CLERK')e
ond.deptno=e.deptno;
--2.oracle支持的左外联接写法
selectd.dname,d.deptno,e.ename,e.jobfromdeptd,
(select*fromempwhereemp.job='CLERK')e
whered.deptno=e.deptno(+);
--11对于工资高于本部门平均水平的员工,列出部门号,姓名,工资,按部门号排序
selectdeptno,ename,salfromempa
wherea.sal>(selectavg(sal)fromempbwherea.deptno=b.deptno)
orderbydeptno;
--12对于emp,列出各个部门中平均工资高于本部门平均水平的员工数和部门号,按部门号排序
selecta.deptno,a.ename,a.sal,a1.dnamefromempa,depta1wherea.deptno=a1.deptnoanda.sal>(selectavg(sal)fromempbwherea.deptno=b.deptno)orderbydeptno;
--13对于emp中工资高于本部门平均水平,人数多与1人的,列出部门号,人数,按部门号排序
selectdeptno,count(*)fromempa
wherea.sal>(selectavg(sal)fromempbwherea.deptno=b.deptno)
groupbydeptnohavingcount(*)>1orderbydeptno;
--14对于emp中低于自己工资至少5人的员工,列出其部门号,姓名,工资,
--以及工资少于自己的人数
selectdeptno,ename,sal,
(selectcount(empno)fromempbwhereb.sal fromempawhere(selectcount(empno)fromempbwhereb.sal 针对DEPT和EMP表,查询出SMITH所在部门的部门名称,部门工资平均值 selecta1.dname,a2.mysalfromdepta1,(selectdeptno,avg(sal)mysalfromempgroupbydeptno)a2wherea1.deptno=a2.deptnoanda2.deptno=(selectdeptnofromempwhereename='SMITH'); 按年限长工资 selectename,hiredate,sal,sal*(1+round(months_between(sysdate,hiredate)/100.0))asnewsal fromemp; selecta1.deptno,a2.dname,a1.ename,a1.salfromempa1,depta2wherea1.deptno=a2.deptnoanda1.deptno=( selectdeptnofromempwheresal>(selectavg(sal)fromemp)groupbydeptnohavingmax(count(deptno))); oracle经典题目 Oracle工作 --01按以下格式显示下面的信息,条件是工资大于1500的。 -- 部门名称 姓名 工资 selectdname,ename,sal fromdept,emp wheredept.deptno=emp.deptnoandsal>1500 --2按以下格式显示下面信息,条件是此人工资在所有人中最高。 -- 部门 姓名 工资 selectdname,ename,sal fromdept,emp wheredept.deptno=emp.deptno andemp.sal=(selectmax(sal)fromemp) --3按以下格式显示下面信息 -- 某人为某人工作 selecte.ename||'为'||m.ename||'工作'as描述 fromempe,empm wheree.mgr=m.empno --4为所有人长工资,标准是: 10部门长10%;20部门长15%; -- 30部门长20%其他部门长18%(要求用DECODE函数) selectename,deptno,sal,sal*(1+nvl(decode(deptno,10,0.1,20,0.15,30,0.2),0.18))asnewsal fromemp --5 --根据工作年限长工资,标准是: 为公司工作了几个月就长几个百分点。 selectename,hiredate,sal,sal*(1+round(months_between(sysdate,hiredate)/100.0))asnewsal fromemp --6查询出king所在部门的部门号\部门名称\部门人数 --ex1 selectd.deptno,d.dname,count(*) fromdeptd,empe,empm whered.deptno=e.deptno ande.deptno=m.deptno andm.ename='KING' groupbyd.deptno,d.dname --ex2 selectd.deptno,d.dname,count(*) fromdeptd,empe whered.deptno=e.deptno ande.deptno=(selectdeptnofromempwhereename='KING') groupbyd.deptno,d.dname --7查询出king所在部门的工作年限最大的员工名字 selectename,hiredate fromemp where(deptno,hiredate)in(selectdeptno,min(hiredate) fromemp wheredeptnoin(selectdeptno fromemp whereename='KING') groupbydeptno) --8查询出管理员工人数最多的人的名字和他管理的人的名字 withmas (selectempno,ename fromemp whereempnoin(selectmgr fromemp groupbymgr havingcount(*)>=all(selectcount(*) fromemp groupbymgr) ) ) selectename,'manager'astypefromm union selecte.ename,'emp'astypefromm,empewheree.mgr=m.empno --9查询出工资成本最高的部门的部门号和部门名称 selectd.deptno,d.dname fromdeptd,empe whered.deptno=e.deptno groupbyd.deptno,d.dname havingsum(e.sal)>=all(selectsum(sal) fromemp groupbydeptno) --10查询出工资不超过2500的人数最多的部门名称 selectd.deptno,d.dname fromdeptd,empe whered.deptno=e.deptno ande.sal<=2500 groupbyd.deptno,d.dname havingcount(*)>=all(selectcount(*) fromemp wheresal<=2500 groupbydeptno) --11查询出没有下属员工的人的名字和他的职位 selectename,job fromemp whereempnonotin(selectdistinctnvl(mgr,0) fromemp) --12查询出人数最多的那个部门的部门编号和部门名称 selectd.deptno,d.dname fromdeptd,empe whered.deptno=e.deptno groupbyd.deptno,d.dname havingcount(*)>=all(selectcount(*) fromemp groupbydeptno) --13查询出没有员工的那个部门的部门编号和部门名称(要求用两种方法,其中一种要用集合运算) --ex1 selectdeptno,dname fromdept wheredeptnonotin(selectdeptnofromemp) --ex2 selectdeptno,dname fromdept wherenotexists (selectdeptnodeptno fromemp wheredept.deptno=emp.deptno) --ex3 select
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- oracle 基本 语句 练习