2018-07-31
1 ---练习3 2 ---创建sporter表 3 create table sporter( 4 sporterid number(10) constraint sport_id primary key, 5 sname varchar2(20) not null, 6 sex varchar2(10), 7 department varchar2(10) not null 8 ); 9 select * from sporter; 10 ---向sporter表中插入数据 11 insert into sporter values(1001,'李盼','女','计算机系'); 12 insert into sporter values(1002,'王玥','女','数学系'); 13 insert into sporter values(1003,'丁赛','男','计算机系'); 14 insert into sporter values(1004,'汪丽','女','物理系'); 15 insert into sporter values(1005,'李娜','女','心理系'); 16 insert into sporter values(1006,'王骋','女','化学系'); 17 18 ---创建item表 19 create table item( 20 itemid varchar2(10) constraint item_id primary key, 21 itemname varchar2(20) not null, 22 loc varchar2(20) 23 ); 24 25 select * from item 26 27 ---向item表中插入数据 28 insert into item values('x001','男子五千米','一操场'); 29 insert into item values('x002','男子标枪','一操场'); 30 insert into item values('x003','男子跳远','二操场'); 31 insert into item values('x004','女子跳高','二操场'); 32 insert into item values('x005','女子三千米','三操场'); 33 34 ---创建grade积分表 35 create table grade( 36 sprterid number(10), 37 itemid varchar2(10), 38 mark number(10) constraint grade_mark check(mark in(6,4,2,0)) 39 ); 40 ---修改列名 41 alter table grade rename column sprterid to sporterid; 42 43 select * from grade ; 44 ---向grade积分表中插入数据 45 insert into grade values(1001,'x001',6); 46 insert into grade values(1002,'x001',4); 47 insert into grade values(1003,'x001',2); 48 insert into grade values(1004,'x001',0); 49 insert into grade values(1001,'x003',4); 50 insert into grade values(1002,'x003',6); 51 insert into grade values( 1004,'x003',2); 52 insert into grade values(1004,'x004',6); 53 insert into grade values(1006,'x004',4); 54 --要求 55 --1.求出总积分最高的系名及总积分 56 select department ,sum(mark) 57 from sporter natural join grade 58 group by department 59 having sum(mark)=(select max(sum(mark)) 60 from sporter natural join grade 61 group by department ); 62 ---？？？ 63 --2.查询在一操场进行比赛的项目名称及其冠军的姓名 64 select itemid,max(mark) 65 from sporter natural join grade 66 group by itemid 67 having itemid in(select itemid 68 from item 69 where loc='一操场') 70 71 --3.找出参加了王玥所参加过的项目的其他同学的姓名 72 select distinct sname 73 from sporter natural join grade 74 where itemid in (select itemid 75 from grade 76 where sporterid =(select sporterid 77 from sporter 78 where sname='王玥')) 79 and sporterid!=(select sporterid 80 from sporter 81 where sname='王玥') 82 83 --4.经查,王玥因为使用了违禁药品，其成绩都记为0分， 84 --请在数据库中做出相应修改 85 update grade set mark =0 where sporterid=(select sporterid from sporter where sname='王玥') ; 86 87 --5.经组委会协商，需要删除女子跳高比赛项目 88 89 delete from item where itemname='女子跳高'
1 ---练习3 2 ---创建sporter表 3 create table sporter( 4 sporterid number(10) constraint sport_id primary key, 5 sname varchar2(20) not null, 6 sex varchar2(10), 7 department varchar2(10) not null 8 ); 9 select * from sporter; 10 ---向sporter表中插入数据 11 insert into sporter values(1001,'李盼','女','计算机系'); 12 insert into sporter values(1002,'王玥','女','数学系'); 13 insert into sporter values(1003,'丁赛','男','计算机系'); 14 insert into sporter values(1004,'汪丽','女','物理系'); 15 insert into sporter values(1005,'李娜','女','心理系'); 16 insert into sporter values(1006,'王骋','女','化学系'); 17 18 ---创建item表 19 create table item( 20 itemid varchar2(10) constraint item_id primary key, 21 itemname varchar2(20) not null, 22 loc varchar2(20) 23 ); 24 25 select * from item 26 27 ---向item表中插入数据 28 insert into item values('x001','男子五千米','一操场'); 29 insert into item values('x002','男子标枪','一操场'); 30 insert into item values('x003','男子跳远','二操场'); 31 insert into item values('x004','女子跳高','二操场'); 32 insert into item values('x005','女子三千米','三操场'); 33 34 ---创建grade积分表 35 create table grade( 36 sprterid number(10), 37 itemid varchar2(10), 38 mark number(10) constraint grade_mark check(mark in(6,4,2,0)) 39 ); 40 ---修改列名 41 alter table grade rename column sprterid to sporterid; 42 43 select * from grade ; 44 ---向grade积分表中插入数据 45 insert into grade values(1001,'x001',6); 46 insert into grade values(1002,'x001',4); 47 insert into grade values(1003,'x001',2); 48 insert into grade values(1004,'x001',0); 49 insert into grade values(1001,'x003',4); 50 insert into grade values(1002,'x003',6); 51 insert into grade values( 1004,'x003',2); 52 insert into grade values(1004,'x004',6); 53 insert into grade values(1006,'x004',4); 54 --要求 55 --1.求出总积分最高的系名及总积分 56 select department ,sum(mark) 57 from sporter natural join grade 58 group by department 59 having sum(mark)=(select max(sum(mark)) 60 from sporter natural join grade 61 group by department ); 62 ---？？？ 63 --2.查询在一操场进行比赛的项目名称及其冠军的姓名 64 select itemid,max(mark) 65 from sporter natural join grade 66 group by itemid 67 having itemid in(select itemid 68 from item 69 where loc='一操场') 70 ---*** 71 select itemname,sname 72 from sporter s,item i, grade g 73 where s.sporterid =g.sporterid and i.itemid=g. itemid 74 and loc='一操场' and mark=6 75 76 --3.找出参加了王玥所参加过的项目的其他同学的姓名 77 select distinct sname 78 from sporter natural join grade 79 where itemid in (select itemid 80 from grade 81 where sporterid =(select sporterid 82 from sporter 83 where sname='王玥')) 84 and sporterid!=(select sporterid 85 from sporter 86 where sname='王玥') 87 88 --4.经查,王玥因为使用了违禁药品，其成绩都记为0分， 89 --请在数据库中做出相应修改 90 update grade set mark =0 where sporterid=(select sporterid from sporter where sname='王玥') ; 91 92 --5.经组委会协商，需要删除女子跳高比赛项目 93 94 delete from item where itemname='女子跳高'
老师
练习作业
1 -------------------第五章---------------- 2 ---练习1 3 1.写一个查询，显示所有员工姓名，部门编号，部门名称。 4 select e.ename,e.deptno,d.dname 5 from emp e,dept d 6 where e.deptno = d.deptno 7 8 2.写一个查询，显示所有工作在CHICAGO并且奖金不为空的员工姓名，工作地点，奖金 9 select e.ename,d.loc,e.comm 10 from emp e,dept d 11 where e.deptno = d.deptno and upper(d.loc)='CHICAGO' and e.comm>0; 12 13 3.写一个查询，显示所有姓名中含有A字符的员工姓名，工作地点。 14 select e.ename,d.loc 15 from emp e,dept d 16 where e.deptno = d.deptno and e.ename like '%A%'; 17 18 19 ---练习2 20 1.查询每个员工的编号，姓名，工资，工资等级，所在工作城市，按照工资等级进行升序排序。 21 select e.empno,e.ename,e.sal,s.grade,d.loc 22 from emp e,dept d ,salgrade s 23 where e.deptno = d.deptno and e.sal between s.losal and s.hisal 24 order by e.sal ; 25 26 27 ---练习3 28 1.查询所有工作在NEW YORK和CHICAGO的员工姓名，员工编号，以及他们的经理姓名，经理编号。 29 select e.ename,e.empno,e.mgr,m.ename 30 from emp e,dept d,emp m 31 where e.deptno = d.deptno and e.mgr = m.empno 32 and upper(d.loc) in ('NEW YORK','CHICAGO') 33 34 2.第上一题的基础上，添加没有经理的员工King，并按照员工编号排序。 35 select e.ename,e.empno,e.mgr,m.ename 36 ---左连接 table1 left join table2 on ... 37 from dept d,emp e left join emp m on e.mgr = m.empno 38 where e.deptno = d.deptno 39 and upper(d.loc) in ('NEW YORK','CHICAGO') 40 order by e.empno 41 42 3.查询所有员工编号，姓名，部门名称，包括没有部门的员工也要显示出来。 43 select e.empno,e.ename,d.dname 44 from emp e left join dept d on e.deptno= d.deptno 45 46 ---练习4 47 使用SQL-99写法，完成如下练习 48 1.创建一个员工表和部门表的交叉连接。 49 select * 50 from emp cross join dept; 51 52 2.使用自然连接，显示入职日期在80年5月1日之后的员工姓名，部门名称，入职日期 53 select e.ename,d.dname,e.hiredate 54 from emp e natural join dept d 55 ---自然连接 自动匹配两个表中的相同字段 56 where e.hiredate>'1-5月-1980' 57 58 3.使用USING子句，显示工作在CHICAGO的员工姓名，部门名称，工作地点 59 --JOIN...USING(公共列名) 60 --using后不要忘记加() 61 select ename,dname,loc 62 from emp join dept using (deptno) 63 where upper(loc)='CHICAGO' 64 65 --- 66 4.使用ON子句，显示工作在CHICAGO的员工姓名，部门名称，工作地点，薪资等级 67 --语法：table1 join table2 on 连接条件; 68 69 select e.ename,d.dname,d.loc,s.grade 70 from emp e ,dept d ,salgrade s 71 where e.deptno = d.deptno and e.sal between s.losal and s.hisal 72 and upper(d.loc)='CHICAGO' 73 74 5.使用左连接，查询每个员工的姓名，经理姓名，没有经理的King也要显示出来。 75 select e.ename,m.ename 76 from emp e left join emp m on e.mgr = m.empno 77 78 6.使用右连接，查询每个员工的姓名，经理姓名，没有经理的King也要显示出来。 79 select e.ename,m.ename 80 from emp m right join emp e on e.mgr = m.empno 81 82 -------------------第六章---------------- 83 ---练习1 84 1.查询部门20的员工，每个月的工资总和及平均工资。 85 select sum(sal),avg(sal) 86 from emp 87 group by deptno 88 having deptno =20 89 90 2.查询工作在CHICAGO的员工人数，最高工资及最低工资。 91 select count(empno) 员工人数, max(sal),min(sal) 92 from emp natural join dept 93 group by loc 94 having upper(loc)= 'CHICAGO'; 95 96 3.查询员工表中一共有几种岗位类型 97 select job 98 from emp 99 group by job ; 100 101 ---练习2 102 1.查询每个部门的部门编号，部门名称，部门人数，最高工资，最低工资，工资总和，平均工资。 103 select deptno,dname,count(empno),max(sal),min(sal),sum(sal),avg(sal) 104 from emp e natural join dept d 105 group by deptno,dname 106 107 2.查询每个部门，每个岗位的部门编号，部门名称，岗位名称，部门人数， 108 最高工资，最低工资，工资总和，平均工资。 109 select deptno,dname,job,count(empno),max(sal),min(sal),sum(sal),avg(sal) 110 from emp e natural join dept d 111 group by deptno,dname,job 112 113 3.查询每个经理所管理的人数，经理编号，经理姓名，要求包括没有经理的人员信息。 114 select count(e.empno),e.mgr 115 from emp e left join emp m on e.mgr = m.empno 116 group by e.mgr 117 118 ---练习3 119 1.查询部门人数大于2的部门编号，部门名称，部门人数。 120 select deptno,dname,count(empno) 121 from emp natural join dept 122 group by deptno,dname 123 having count(empno)>2 124 125 2.查询部门平均工资大于2000，且人数大于2的部门编号，部门名称，部门人数， 126 部门平均工资，并按照部门人数升序排序。 127 select deptno,dname,count(empno) 128 from emp natural join dept 129 group by deptno,dname 130 having count(empno)>2 and avg(sal)>2000 131 order by count(empno) 132 133 -------------------第七章---------------- 134 ---练习1 135 1.查询入职日期最早的员工姓名，入职日期 136 SELECT rownum,t.ename,t.hiredate 137 FROM (SELECT ROWNUM r,emp.* FROM emp) t 138 where hiredate is not null and t.r=1 139 order by hiredate 140 141 2.查询工资比SMITH工资高并且工作地点在CHICAGO的员工姓名，工资，部门名称 142 select ename,sal,dname 143 from emp natural join dept 144 where sal>(select sal 145 from emp 146 where upper(ename)='SMITH') 147 and upper(loc) ='CHICAGO'; 148 149 150 3.查询入职日期比20部门入职日期最早的员工还要早的员工姓名，入职日期 151 select ename,hiredate 152 from emp 153 where hiredate >all(select hiredate 154 from emp 155 where deptno=20); 156 157 4.查询部门人数大于所有部门平均人数的的部门编号，部门名称，部门人数 158 select deptno, dname,count(empno)部门人数 159 from emp natural join dept 160 group by deptno,dname 161 having count(empno)>(select count(empno)/count(distinct deptno) 162 from emp) 163 164 165 ---练习2 166 1.查询入职日期比10部门任意一个员工晚的员工姓名、入职日期，不包括10部门员工 167 select ename,hiredate 168 from emp 169 where hiredate>any(select hiredate 170 from emp 171 where deptno=10) 172 and deptno!=10; 173 174 2.查询入职日期比10部门所有员工晚的员工姓名、入职日期，不包括10部门员工 175 select ename,hiredate 176 from emp 177 where hiredate>all(select hiredate 178 from emp 179 where deptno=10) 180 and deptno!=10; 181 ---比子查询的返回结果的最大值要大 182 183 3.查询职位和10部门任意一个员工职位相同的员工姓名，职位，不包括10部门员工 184 select ename,job 185 from emp 186 where job in (select job 187 from emp 188 where deptno=10) 189 and deptno!=10; 190 191 ---练习3 192 1.查询职位及经理和10部门任意一个员工职位及经理相同的员工姓名，职位， 193 不包括10部门员工 194 select ename,job 195 from emp 196 where (job,mgr) in (select job,mgr 197 from emp 198 where deptno=10) 199 and deptno!=10; 200 201 2.查询职位及经理和10部门任意一个员工职位或经理相同的员工姓名，职位， 202 不包括10部门员工 203 select ename,job 204 from emp 205 where job in (select job from emp where deptno=10) 206 or mgr in (select mgr from emp where deptno=10) 207 and deptno!=10; 208 209 210 ---练习4 211 1.查询比自己职位平均工资高的员工姓名、职位，部门名称，职位平均工资 212 --相同job,平均工资 213 --表 j 用job 和其它表建立连接 214 select job,avg(sal) a 215 from emp 216 group by job 217 ----------------- 218 select e.ename,e.job,d.dname,j.a 219 from emp e,dept d,(select job,avg(sal) a 220 from emp 221 group by job ) j --三个表连接 222 where e.deptno = d.deptno and e.job = j.job 223 and e.sal>j.a ; 224 225 226 2.查询职位和经理同员工SCOTT或BLAKE完全相同的员工姓名、职位，不包括SCOOT和BLAKE本人。 227 select ename,job 228 from emp 229 where (job,mgr) in (select job,mgr 230 from emp 231 where upper(ename) in('SCOTT','BLAKE')) 232 and upper(ename) not in('SCOTT','BLAKE'); 233 234 3.查询不是经理的员工姓名。 235 select ename 236 from emp 237 where empno not in(select distinct mgr 238 from emp 239 where mgr is not null); 240 241 ---练习5 242 1.查询入职日期最早的前5名员工姓名，入职日期。 243 SELECT rownum,t.ename,t.hiredate 244 FROM (SELECT ROWNUM r,emp.* FROM emp) t 245 where t.hiredate is not null and t.r<=5 246 order by t.hiredate 247 248 2.查询工作在CHICAGO并且入职日期最早的前2名员工姓名，入职日期。 249 SELECT rownum,t.ename,t.hiredate 250 FROM (SELECT ROWNUM r,emp.* FROM emp) t 251 where t.hiredate is not null and t.r<=2 252 and t.deptno=(select deptno 253 from dept 254 where upper(loc)='CHICAGO') 255 order by t.hiredate 256 257 ---练习6 258 1.按照每页显示5条记录，分别查询第1页，第2页，第3页信息， 259 要求显示员工姓名、入职日期、部门名称。 260 select rownum ,t.ename,t.hiredate,t.dname 261 from (select rownum r, empno,ename,job,mgr,hiredate,sal,comm,deptno,deptno,dname,loc 262 from emp natural join dept) t 263 WHERE t.r>(1-1)*5 and t.r<=1*5 264 or t.r>(2-1)*5 and t.r<=2*5 265 or t.r>(3-1)*5 and t.r<=3*5 266 267 ---练习7 268 1.按照每页显示5条记录，分别查询工资最高的第1页，第2页，第3页信息， 269 要求显示员工姓名、入职日期、部门名称、工资。 270 select rownum ,t.ename,t.hiredate,t.dname,t.sal 271 from (select rownum r, empno,ename,job,mgr,hiredate,sal,comm,deptno,deptno,dname,loc 272 from emp natural join dept) t 273 WHERE t.r>(1-1)*5 and t.r<=1*5 274 or t.r>(2-1)*5 and t.r<=2*5 275 or t.r>(3-1)*5 and t.r<=3*5 276 order by t.sal desc 277 278 279 ---课后作业 280 1.查询工资高于编号为7782的员工工资，并且和7369号员工从事相同工作的员工的编号、姓名及工资。 281 select empno,ename,sal 282 from emp 283 where sal>(select sal 284 from emp 285 where empno=7782) 286 and job = (select job 287 from emp 288 where empno=7369); 289 --- 290 2.查询工资最高的员工姓名和工资。 291 select rownum,t.ename,t.sal 292 from (select rownum r,emp.* 293 from emp 294 order by sal desc) t 295 where t.r=1 296 297 3.查询部门最低工资高于10号部门最低工资的部门的编号、名称及部门最低工资。 298 select deptno,dname,min(sal) 299 from emp natural join dept 300 group by deptno,dname 301 having min(sal)>(select min(sal) 302 from emp 303 group by deptno 304 having deptno=10) 305 306 4.查询员工工资为其部门最低工资的员工的编号和姓名及工资。 307 select empno,ename,sal 308 from emp 309 where (deptno,sal) in (select deptno ,min(sal) 310 from emp 311 group by deptno) 312 313 5.显示经理是KING的员工姓名，工资。 314 select ename,sal 315 from emp 316 where mgr=(select empno 317 from emp 318 where upper(ename)='KING') 319 320 6.显示比员工SMITH参加工作时间晚的员工姓名，工资，参加工作时间。 321 select ename,sal,hiredate 322 from emp 323 where hiredate>(select hiredate 324 from emp 325 where upper(ename)='SMITH') 326 327 7.使用子查询的方式查询哪些职员在NEW YORK工作。 328 select * 329 from emp 330 where deptno=(select deptno 331 from dept 332 where upper(loc)='NEW YORK') 333 334 8.写一个查询显示和员工SMITH工作在同一个部门的员工姓名，雇用日期， 335 查询结果中排除SMITH。 336 select ename,hiredate 337 from emp 338 where deptno in (select deptno 339 from emp 340 where upper(ename)='SMITH') 341 342 9.写一个查询显示其工资比全体职员平均工资高的员工编号、姓名。 343 select empno,ename 344 from emp 345 where sal>( select avg(sal) 346 from emp) 347 348 10.写一个查询显示其上级领导是King的员工姓名、工资。 349 select ename,sal 350 from emp 351 where mgr=(select empno 352 from emp 353 where upper(ename)='KING') 354 355 11.显示所有工作在RESEARCH部门的员工姓名，职位。 356 select ename,job 357 from emp 358 where deptno =(select deptno 359 from dept 360 where upper(dname)='RESEARCH') 361 362 12.查询每个部门的部门编号、平均工资，要求部门的平均工资高于部门20的平均工资。 363 select deptno,avg(sal) 364 from emp natural join dept 365 group by deptno 366 having avg(sal)>(select avg(sal) 367 from emp natural join dept 368 group by deptno 369 having deptno=20) 370 --- 371 13.查询大于自己部门平均工资的员工姓名，工资， 372 所在部门平均工资，高于部门平均工资的额度。 373 select e.ename,e.sal,a,sal-a 374 from emp e,(select deptno ,avg(sal) a 375 from emp 376 group by deptno) t 377 where e.deptno= t.deptno 378 and sal>a 379 380 381 14. 列出至少有一个雇员的所有部门 382 select deptno 383 from emp 384 group by deptno 385 having count(empno)>0 386 387 15. 列出薪金比"SMITH"多的所有雇员 388 select * 389 from emp 390 where sal>(select sal 391 from emp 392 where upper(ename)='SMITH') 393 394 --16. 列出入职日期早于其直接上级的所有雇员 395 select * 396 from emp e join emp m on e.mgr=m.empno 397 where e.hiredate < m.hiredate 398 399 17. 找员工姓名和直接上级的名字 400 select e.ename,m.ename 401 from emp e join emp m on e.mgr=m.empno 402 403 18. 显示部门名称和人数 404 select dname,count(empno) 405 from emp natural join dept 406 group by dname 407 408 19. 显示每个部门的最高工资的员工 409 select * 410 from emp 411 where (deptno,sal) in (select deptno,max(sal) 412 from emp 413 group by deptno) 414 415 20. 显示出和员工号7369部门相同的员工姓名，工资 416 select ename,sal 417 from emp 418 where deptno=(select deptno 419 from emp 420 where empno=7369); 421 422 21. 显示出和姓名中包含"W"的员工相同部门的员工姓名 423 select ename 424 from emp 425 where deptno=(select deptno 426 from emp 427 where upper(ename) like '%W%'); 428 429 22. 显示出工资大于平均工资的员工姓名，工资 430 select ename,sal 431 from emp 432 where sal>(select avg(sal) 433 from emp); 434 435 23. 显示出工资大于本部门平均工资的员工姓名，工资 436 select ename,sal 437 from emp 438 where sal > (select avg(sal) 439 from emp 440 group by deptno) 441 442 24. 显示每位经理管理员工的最低工资，及最低工资者的姓名 443 select m,ename 444 from emp e,(select mgr,min(sal) m 445 from emp 446 group by mgr) t 447 where e.mgr = t.mgr 448 and sal = m 449 450 25. 显示比工资最高的员工参加工作时间晚的员工姓名，参加工作时间 451 select ename,hiredate 452 from emp 453 where hiredate>(select hiredate 454 from emp 455 where sal=( 456 select max(sal) 457 from emp)) 458 459 26. 显示出平均工资最高的的部门平均工资及部门名称 460 select dname,avg(sal) 461 from emp natural join dept 462 group by deptno,dname 463 having avg(sal)=(select max(avg(sal)) 464 from emp 465 group by deptno)
自己
课后练习
-------------------第五章---------------- ---练习1 1.写一个查询，显示所有员工姓名，部门编号，部门名称。 select e.ename,e.deptno,d.dname from emp e,dept d where e.deptno = d.deptno 2.写一个查询，显示所有工作在CHICAGO并且奖金不为空的员工姓名，工作地点，奖金 select e.ename,d.loc,e.comm from emp e,dept d where e.deptno = d.deptno and upper(d.loc)='CHICAGO' and e.comm>0; 3.写一个查询，显示所有姓名中含有A字符的员工姓名，工作地点。 select e.ename,d.loc from emp e,dept d where e.deptno = d.deptno and e.ename like '%A%'; ---练习2 1.查询每个员工的编号，姓名，工资，工资等级，所在工作城市，按照工资等级进行升序排序。 select e.empno,e.ename,e.sal,s.grade,d.loc from emp e,dept d ,salgrade s where e.deptno = d.deptno and e.sal between s.losal and s.hisal order by e.sal ; ---练习3 1.查询所有工作在NEW YORK和CHICAGO的员工姓名，员工编号，以及他们的经理姓名，经理编号。 select e.ename,e.empno,e.mgr,m.ename from emp e,dept d,emp m where e.deptno = d.deptno and e.mgr = m.empno and upper(d.loc) in ('NEW YORK','CHICAGO') 2.第上一题的基础上，添加没有经理的员工King，并按照员工编号排序。 select e.ename,e.empno,e.mgr,m.ename ---左连接 table1 left join table2 on ... from dept d,emp e left join emp m on e.mgr = m.empno where e.deptno = d.deptno and upper(d.loc) in ('NEW YORK','CHICAGO') order by e.empno 3.查询所有员工编号，姓名，部门名称，包括没有部门的员工也要显示出来。 select e.empno,e.ename,d.dname from emp e left join dept d on e.deptno= d.deptno ---练习4 使用SQL-99写法，完成如下练习 1.创建一个员工表和部门表的交叉连接。 select * from emp cross join dept; 2.使用自然连接，显示入职日期在80年5月1日之后的员工姓名，部门名称，入职日期 select e.ename,d.dname,e.hiredate from emp e natural join dept d ---自然连接 自动匹配两个表中的相同字段 where e.hiredate>'1-5月-1980' 3.使用USING子句，显示工作在CHICAGO的员工姓名，部门名称，工作地点 --JOIN...USING(公共列名) --using后不要忘记加() select ename,dname,loc from emp join dept using (deptno) where upper(loc)='CHICAGO' --- 4.使用ON子句，显示工作在CHICAGO的员工姓名，部门名称，工作地点，薪资等级 --语法：table1 join table2 on 连接条件; select e.ename,d.dname,d.loc,s.grade from emp e ,dept d ,salgrade s where e.deptno = d.deptno and e.sal between s.losal and s.hisal and upper(d.loc)='CHICAGO' 5.使用左连接，查询每个员工的姓名，经理姓名，没有经理的King也要显示出来。 select e.ename,m.ename from emp e left join emp m on e.mgr = m.empno 6.使用右连接，查询每个员工的姓名，经理姓名，没有经理的King也要显示出来。 select e.ename,m.ename from emp m right join emp e on e.mgr = m.empno -------------------第六章---------------- ---练习1 1.查询部门20的员工，每个月的工资总和及平均工资。 select sum(sal),avg(sal) from emp group by deptno having deptno =20 2.查询工作在CHICAGO的员工人数，最高工资及最低工资。 select count(empno) 员工人数, max(sal),min(sal) from emp natural join dept group by loc having upper(loc)= 'CHICAGO'; 3.查询员工表中一共有几种岗位类型 select job from emp group by job ; ---练习2 1.查询每个部门的部门编号，部门名称，部门人数，最高工资，最低工资，工资总和，平均工资。 select deptno,dname,count(empno),max(sal),min(sal),sum(sal),avg(sal) from emp e natural join dept d group by deptno,dname 2.查询每个部门，每个岗位的部门编号，部门名称，岗位名称，部门人数， 最高工资，最低工资，工资总和，平均工资。 select deptno,dname,job,count(empno),max(sal),min(sal),sum(sal),avg(sal) from emp e natural join dept d group by deptno,dname,job 3.查询每个经理所管理的人数，经理编号，经理姓名，要求包括没有经理的人员信息。 select count(e.empno),e.mgr from emp e left join emp m on e.mgr = m.empno group by e.mgr ---练习3 1.查询部门人数大于2的部门编号，部门名称，部门人数。 select deptno,dname,count(empno) from emp natural join dept group by deptno,dname having count(empno)>2 2.查询部门平均工资大于2000，且人数大于2的部门编号，部门名称，部门人数， 部门平均工资，并按照部门人数升序排序。 select deptno,dname,count(empno) from emp natural join dept group by deptno,dname having count(empno)>2 and avg(sal)>2000 order by count(empno) -------------------第七章---------------- ---练习1 1.查询入职日期最早的员工姓名，入职日期 SELECT rownum,t.ename,t.hiredate FROM (SELECT ROWNUM r,emp.* FROM emp) t where hiredate is not null and t.r=1 order by hiredate 2.查询工资比SMITH工资高并且工作地点在CHICAGO的员工姓名，工资，部门名称 select ename,sal,dname from emp natural join dept where sal>(select sal from emp where upper(ename)='SMITH') and upper(loc) ='CHICAGO'; 3.查询入职日期比20部门入职日期最早的员工还要早的员工姓名，入职日期 select ename,hiredate from emp where hiredate >all(select hiredate from emp where deptno=20); 4.查询部门人数大于所有部门平均人数的的部门编号，部门名称，部门人数 select deptno, dname,count(empno)部门人数 from emp natural join dept group by deptno,dname having count(empno)>(select count(empno)/count(distinct deptno) from emp) ---练习2 1.查询入职日期比10部门任意一个员工晚的员工姓名、入职日期，不包括10部门员工 select ename,hiredate from emp where hiredate>any(select hiredate from emp where deptno=10) and deptno!=10; 2.查询入职日期比10部门所有员工晚的员工姓名、入职日期，不包括10部门员工 select ename,hiredate from emp where hiredate>all(select hiredate from emp where deptno=10) and deptno!=10; ---比子查询的返回结果的最大值要大 3.查询职位和10部门任意一个员工职位相同的员工姓名，职位，不包括10部门员工 select ename,job from emp where job in (select job from emp where deptno=10) and deptno!=10; ---练习3 1.查询职位及经理和10部门任意一个员工职位及经理相同的员工姓名，职位， 不包括10部门员工 select ename,job from emp where (job,mgr) in (select job,mgr from emp where deptno=10) and deptno!=10; 2.查询职位及经理和10部门任意一个员工职位或经理相同的员工姓名，职位， 不包括10部门员工 select ename,job from emp where job in (select job from emp where deptno=10) or mgr in (select mgr from emp where deptno=10) and deptno!=10; ---练习4 1.查询比自己职位平均工资高的员工姓名、职位，部门名称，职位平均工资 --相同job,平均工资 --表 j 用job 和其它表建立连接 select job,avg(sal) a from emp group by job ----------------- select e.ename,e.job,d.dname,j.a from emp e,dept d,(select job,avg(sal) a from emp group by job ) j --三个表连接 where e.deptno = d.deptno and e.job = j.job and e.sal>j.a ; 2.查询职位和经理同员工SCOTT或BLAKE完全相同的员工姓名、职位，不包括SCOOT和BLAKE本人。 select ename,job from emp where (job,mgr) in (select job,mgr from emp where upper(ename) in('SCOTT','BLAKE')) and upper(ename) not in('SCOTT','BLAKE'); 3.查询不是经理的员工姓名。 select ename from emp where empno not in(select distinct mgr from emp where mgr is not null); ---练习5 1.查询入职日期最早的前5名员工姓名，入职日期。 SELECT rownum,t.ename,t.hiredate FROM (SELECT ROWNUM r,emp.* FROM emp) t where t.hiredate is not null and t.r<=5 order by t.hiredate 2.查询工作在CHICAGO并且入职日期最早的前2名员工姓名，入职日期。 SELECT rownum,t.ename,t.hiredate FROM (SELECT ROWNUM r,emp.* FROM emp) t where t.hiredate is not null and t.r<=2 and t.deptno=(select deptno from dept where upper(loc)='CHICAGO') order by t.hiredate ---练习6 1.按照每页显示5条记录，分别查询第1页，第2页，第3页信息， 要求显示员工姓名、入职日期、部门名称。 select rownum ,t.ename,t.hiredate,t.dname from (select rownum r, empno,ename,job,mgr,hiredate,sal,comm,deptno,deptno,dname,loc from emp natural join dept) t WHERE t.r>(1-1)*5 and t.r<=1*5 or t.r>(2-1)*5 and t.r<=2*5 or t.r>(3-1)*5 and t.r<=3*5 ---练习7 1.按照每页显示5条记录，分别查询工资最高的第1页，第2页，第3页信息， 要求显示员工姓名、入职日期、部门名称、工资。 select rownum ,t.ename,t.hiredate,t.dname,t.sal from (select rownum r, empno,ename,job,mgr,hiredate,sal,comm,deptno,deptno,dname,loc from emp natural join dept) t WHERE t.r>(1-1)*5 and t.r<=1*5 or t.r>(2-1)*5 and t.r<=2*5 or t.r>(3-1)*5 and t.r<=3*5 order by t.sal desc ---课后作业 1.查询工资高于编号为7782的员工工资，并且和7369号员工从事相同工作的员工的编号、姓名及工资。 select empno,ename,sal from emp where sal>(select sal from emp where empno=7782) and job = (select job from emp where empno=7369); --- 2.查询工资最高的员工姓名和工资。 select rownum,t.ename,t.sal from (select rownum r,emp.* from emp order by sal desc) t where t.r=1 3.查询部门最低工资高于10号部门最低工资的部门的编号、名称及部门最低工资。 select deptno,dname,min(sal) from emp natural join dept group by deptno,dname having min(sal)>(select min(sal) from emp group by deptno having deptno=10) 4.查询员工工资为其部门最低工资的员工的编号和姓名及工资。 select empno,ename,sal from emp where (deptno,sal) in (select deptno ,min(sal) from emp group by deptno) 5.显示经理是KING的员工姓名，工资。 select ename,sal from emp where mgr=(select empno from emp where upper(ename)='KING') 6.显示比员工SMITH参加工作时间晚的员工姓名，工资，参加工作时间。 select ename,sal,hiredate from emp where hiredate>(select hiredate from emp where upper(ename)='SMITH') 7.使用子查询的方式查询哪些职员在NEW YORK工作。 select * from emp where deptno=(select deptno from dept where upper(loc)='NEW YORK') 8.写一个查询显示和员工SMITH工作在同一个部门的员工姓名，雇用日期， 查询结果中排除SMITH。 select ename,hiredate from emp where deptno in (select deptno from emp where upper(ename)='SMITH') 9.写一个查询显示其工资比全体职员平均工资高的员工编号、姓名。 select empno,ename from emp where sal>( select avg(sal) from emp) 10.写一个查询显示其上级领导是King的员工姓名、工资。 select ename,sal from emp where mgr=(select empno from emp where upper(ename)='KING') 11.显示所有工作在RESEARCH部门的员工姓名，职位。 select ename,job from emp where deptno =(select deptno from dept where upper(dname)='RESEARCH') 12.查询每个部门的部门编号、平均工资，要求部门的平均工资高于部门20的平均工资。 select deptno,avg(sal) from emp natural join dept group by deptno having avg(sal)>(select avg(sal) from emp natural join dept group by deptno having deptno=20) --- 13.查询大于自己部门平均工资的员工姓名，工资， 所在部门平均工资，高于部门平均工资的额度。 select e.ename,e.sal,a,sal-a from emp e,(select deptno ,avg(sal) a from emp group by deptno) t where e.deptno= t.deptno and sal>a 14. 列出至少有一个雇员的所有部门 select deptno from emp group by deptno having count(empno)>0 15. 列出薪金比"SMITH"多的所有雇员 select * from emp where sal>(select sal from emp where upper(ename)='SMITH') --16. 列出入职日期早于其直接上级的所有雇员 select * from emp e join emp m on e.mgr=m.empno where e.hiredate < m.hiredate 17. 找员工姓名和直接上级的名字 select e.ename,m.ename from emp e join emp m on e.mgr=m.empno 18. 显示部门名称和人数 select dname,count(empno) from emp natural join dept group by dname 19. 显示每个部门的最高工资的员工 select * from emp where (deptno,sal) in (select deptno,max(sal) from emp group by deptno) 20. 显示出和员工号7369部门相同的员工姓名，工资 select ename,sal from emp where deptno=(select deptno from emp where empno=7369); 21. 显示出和姓名中包含"W"的员工相同部门的员工姓名 select ename from emp where deptno=(select deptno from emp where upper(ename) like '%W%'); 22. 显示出工资大于平均工资的员工姓名，工资 select ename,sal from emp where sal>(select avg(sal) from emp); 23. 显示出工资大于本部门平均工资的员工姓名，工资 select ename,sal from emp where sal > (select avg(sal) from emp group by deptno) 24. 显示每位经理管理员工的最低工资，及最低工资者的姓名 select m,ename from emp e,(select mgr,min(sal) m from emp group by mgr) t where e.mgr = t.mgr and sal = m 25. 显示比工资最高的员工参加工作时间晚的员工姓名，参加工作时间 select ename,hiredate from emp where hiredate>(select hiredate from emp where sal=( select max(sal) from emp)) 26. 显示出平均工资最高的的部门平均工资及部门名称 select dname,avg(sal) from emp natural join dept group by deptno,dname having avg(sal)=(select max(avg(sal)) from emp group by deptno)