oracle语法练习汇总
全是自己一个一个敲出来的啊 啊 啊
1 --(1)查询20号部门的所有员工信息。 2 select * from emp e where e.deptno=20 3 4 --(2)查询所有工种为CLERK的员工的工号、员工名和部门名。 5 select e.empno,e.ename,d.dname from emp e natural join dept d where e.job='CLERK' 6 7 --(3)查询奖金(COMM)高于工资(SAL)的员工信息。 8 select * from emp e where e.sal<e.comm 9 10 --(4)查询奖金高于工资的20%的员工信息。 11 select * from emp e where e.sal*0.2<e.comm 12 13 --(5)查询10号部门中工种为MANAGER和20号部门中工种为CLERK的员工的信息。 14 select * from emp e where (e.deptno=10 and e.job='MANAGER') or (e.deptno=20 and e.job='CLERK') 15 16 --(6)查询所有工种不是MANAGER和CLERK,且工资大于或等于2000的员工的详细信息。 17 select * from emp e where e.job !='MANAGER' and e.job !='CLERK' and e.sal>2000 18 19 --(7)查询有奖金的员工的不同工种。 20 select distinct e.job from emp e where e.comm is not null 21 22 --(8)查询所有员工工资和奖金的和。 23 select sum(e.sal+nvl(e.comm,0)) from emp e 24 25 --(9)查询没有奖金或奖金低于100的员工信息。 26 select * from emp e where e.comm is null or e.comm<100 27 28 --(10)查询各月倒数第2天入职的员工信息。 29 select * from emp e where e.hiredate=(last_day(e.hiredate)-1) 30 31 --(11)查询员工工龄大于或等于10年的员工信息。 32 select * from emp e where months_between(sysdate,e.hiredate)/12>=10 33 34 35 --(12)查询员工信息,要求以首字母大写的方式显示所有员工的姓名。 36 select initcap(e.ename) from emp e 37 38 39 --(13)查询员工名正好为6个字符的员工的信息。 40 select * from emp e where length(e.ename)=6 41 42 43 --(14)查询员工名字中不包含字母“S”员工。 44 select * from emp e where instr(e.ename,'S')=0 45 46 47 --(15)查询员工姓名的第2个字母为“M”的员工信息。 48 select * from emp e where e.ename like '_M%' 49 50 --(16)查询所有员工姓名的前3个字符。 51 select substr(e.ename,1,3) from emp e; 52 53 --(17)查询所有员工的姓名,如果包含字母“s”,则用“S”替换。 54 select replace(lower(e.ename),'s','S') from emp e; 55 56 57 --(18)查询员工的姓名和入职日期,并按入职日期从先到后进行排列。 58 select e.ename,e.hiredate from emp e order by e.hiredate 59 60 --(19)显示所有的姓名、工种、工资和奖金,按工种降序排列,若工种相同则按工资升序排列。 61 select e.ename,e.job,e.sal,e.comm from emp e order by e.job desc,e.sal asc 62 63 64 --(20)显示所有员工的姓名、入职的年份和月份,若入职日期所在的月份排序,若月份相同则按入职的年份排序。 65 select e.ename,extract(year from e.hiredate) y,extract(month from e.hiredate) m from emp e order by m,y 66 67 --(21)查询在2月份入职的所有员工信息。 68 select * from emp e where extract(month from e.hiredate)=2 69 70 71 --(22)查询所有员工入职以来的工作期限,用“**年**月**日”的形式表示。 72 73 select extract(year from e.hiredate)||'年'||extract(month from e.hiredate)||'月'||extract(day from e.hiredate)||'日' from emp e 74 75 --(23)查询至少有一个员工的部门信息。 76 select distinct d.* from dept d join emp e on d.deptno=e.deptno 77 78 --(24)查询工资比SMITH员工工资高的所有员工信息。 79 select * from emp e where e.sal>(select e.sal from emp e where e.ename='SMITH') 80 81 82 --(25)查询所有员工的姓名及其直接上级的姓名。 83 select m.ename,e.ename from emp e join emp m on e.empno=m.mgr; 84 85 --(26)查询入职日期早于其直接上级领导的所有员工信息。 86 select m.* from emp e join emp m on e.empno=m.mgr and e.hiredate<m.hiredate; 87 88 --(27)查询所有部门及其员工信息,包括那些没有员工的部门。 89 select * from emp e right join dept d on e.deptno=d.deptno 90 91 92 --(28)查询所有员工及其部门信息,包括那些还不属于任何部门的员工。 93 select * from emp e left join dept d on e.deptno=d.deptno 94 95 96 --(29)查询所有工种为CLERK的员工的姓名及其部门名称。 97 select e.ename,d.dname from emp e natural join dept d where e.job='CLERK' 98 99 100 --(30)查询最低工资大于2500的各种工作。 101 select distinct e.job from emp e where e.sal > 2500 102 103 --(31)查询最低工资低于2000的部门及其员工信息。 104 select * 105 from emp 106 where deptno in 107 (select deptno 108 from (select min(sal) min_sal, deptno from emp group by deptno) 109 where min_sal < '2000'); 110 111 --(32)查询在SALES部门工作的员工的姓名信息。 112 select e.ename from emp e natural join dept d where d.dname='SALES'; 113 114 --(33)查询工资高于公司平均工资的所有员工信息。 115 select * from emp e where e.sal > (select avg(e.sal) from emp e) 116 117 118 --(34)查询与SMITH员工从事相同工作的所有员工信息。 119 select * from emp e where e.job=(select e.job from emp e where e.ename='SMITH') 120 121 122 --(35)列出工资等于30号部门中某个员工工资的所有员工的姓名和工资。 123 select e.ename,e.sal from emp e where e.sal = any(select e.sal from emp e where e.deptno=30) 124 125 126 --(36)查询工资高于30号部门中工作的所有员工的工资的员工姓名和工资。 127 128 129 select e.ename,e.sal from emp e where e.sal > all(select e.sal from emp e where e.deptno=30) 130 131 --(37)查询每个部门中的员工数量、平均工资和平均工作年限。 132 select count(1),avg(e.sal),avg(months_between(sysdate,e.hiredate)/12) from emp e group by e.deptno 133 134 135 --(38)查询从事同一种工作但不属于同一部门的员工信息。 136 select e.* from emp e join emp m on e.job=m.job and e.deptno!=m.deptno 137 138 139 --(39)查询各个部门的详细信息以及部门人数、部门平均工资。 140 select * 141 from (select d.deptno deptno, count(e.deptno), avg(e.sal) 142 from emp e 143 right join dept d 144 on e.deptno = d.deptno 145 group by d.deptno) m natural 146 join dept d 147 148 --(40)查询各种工作的最低工资。 149 select e.job, min(e.sal) from emp e group by e.job 150 151 --(41)查询各个部门中的不同工种的最高工资。 152 select e.deptno,e.job,max(e.sal) from emp e group by e.deptno,e.job 153 154 155 --(42)查询10号部门员工以及领导的信息。 156 select * from emp e where e.deptno=10; 157 158 --(43)查询各个部门的人数及平均工资。 159 select e.deptno, count(1) , avg(e.sal) from emp e group by e.deptno 160 161 --(44)查询工资为某个部门平均工资的员工信息。 162 select * from emp e where e.sal in (select avg(e.sal) from emp e group by e.deptno) 163 164 --(45)查询工资高于本部门平均工资的员工的信息。 165 select e.* 166 from emp e natural 167 join (select e.deptno d, avg(e.sal) a from emp e group by e.deptno) m 168 where e.sal > m.a 169 and e.deptno = m.d 170 171 --(46)查询工资高于本部门平均工资的员工的信息及其部门的平均工资。 172 select e.*,m.a 173 from emp e natural 174 join (select e.deptno d, avg(e.sal) a from emp e group by e.deptno) m 175 where e.sal > m.a 176 and e.deptno = m.d 177 178 --(47)查询工资高于20号部门某个员工工资的员工的信息。 179 select * 180 from emp e 181 where e.sal > any (select e.sal from emp e where e.deptno = 20) 182 183 184 --(48)统计各个工种的人数与平均工资。 185 select e.job,count(e.empno) ,nvl(avg(e.sal),0) from emp e group by e.job 186 187 188 --(49)统计每个部门中各个工种的人数与平均工资。 189 select e.deptno, e.job, count(e.empno), nvl(avg(e.sal), 0) 190 from emp e 191 group by e.job, e.deptno 192 order by e.deptno 193 194 --(50)查询工资、奖金与10 号部门某个员工工资、奖金都相同的员工的信息。 195 select * 196 from emp e 197 where e.sal in (select e.sal from emp e where e.deptno = 10) 198 and e.comm in (select e.comm from emp e where e.deptno = 10) 199 200 select emp.* 201 from emp 202 join (select sal, comm from emp where deptno = 10) t 203 on emp.sal = t.sal 204 and nvl(emp.comm, 0) = nvl(t.comm, 0) 205 and emp.deptno != 10; 206 207 --(51)查询部门人数大于5的部门的员工的信息。 208 select e.* 209 from emp e natural 210 join (select e.deptno d, count(1) a from emp e group by e.deptno) m 211 where m.a > 5 and e.deptno=m.d 212 213 214 --(52)查询所有员工工资都大于1000的部门的信息。 215 select * 216 from dept d 217 where d.deptno not in (select e.deptno from emp e where e.sal < 1000) 218 219 --(53)查询所有员工工资都大于1000的部门的信息及其员工信息。 220 221 select distinct * 222 from emp e 223 join (select * 224 from dept d 225 where d.deptno not in 226 (select e.deptno from emp e where e.sal < 1000)) m 227 on e.deptno = m.deptno 228 229 230 --(54)查询所有员工工资都在900~3000之间的部门的信息。 231 --一 232 select * 233 from dept d 234 where d.deptno in 235 (select distinct d.deptno 236 from dept d 237 right join emp e 238 on e.deptno = d.deptno 239 and d.deptno not in 240 (select e.deptno 241 from emp e 242 where e.sal not between 900 and 3000)) 243 --二 244 select * 245 from dept 246 where deptno in (select distinct deptno 247 from emp 248 where deptno not in 249 (select distinct deptno 250 from emp 251 where sal not between 900 and 3000)); 252 253 254 --(55)查询所有工资都在900~3000之间的员工所在部门的员工信息。 255 select * 256 from emp e 257 where e.deptno not in 258 (select e.deptno from emp e where e.sal not between 900 and 3000) 259 260 --(56)查询每个员工的领导所在部门的信息。 261 select * 262 from dept d 263 where d.deptno in 264 (select m.deptno from emp e right join emp m on e.mgr = m.empno) 265 266 267 --(57)查询人数最多的部门信息。 268 select * 269 from dept d 270 join (select e.deptno d, count(1) a from emp e group by e.deptno) m 271 on d.deptno = m.d 272 and m.a = (select max(count(1)) a from emp e group by e.deptno) 273 274 275 --(58)查询30号部门中工资排序前3名的员工信息。 276 select * 277 from (select * from emp e where e.deptno = 30 order by e.sal desc) m 278 where rownum <= 3 279 280 281 --(59)查询所有员工中工资排在5~10名之间的员工信息。 282 select * 283 from (select m.*, rownum r 284 from (select * from emp e order by e.sal desc) m 285 where rownum <= 10) mm 286 where mm.r > 5 287 288 289 --(60)向emp表中插入一条记录,员工号为1357,员工名字为oracle,工资为2050元,部门号为20,入职日期为2002年5月10日。 290 insert into emp (empno,ename,hiredate,sal,deptno) values(1357,'oracle','5/10月/2002',2050,20); 291 292 select * from emp 293 294 --(61)向emp表中插入一条记录,员工名字为FAN,员工号为8000,其他信息与SMITH员工的信息相同。 295 insert into emp values(8000,'FAN',(select job,mgr,hiredate,sal,comm,deptno from emp where ename='SMITH')) 296 297 298 299 --(62)将各部门员工的工资修改为该员工所在部门平均工资加1000。 300 update emp t1 301 set sal = 302 (select new_sal 303 from (select avg(sal) + 1000 new_sal, deptno 304 from emp 305 group by deptno) t2 wher e t1.deptno = t2.deptno);