逆袭之旅DAY13.东软实训.Oracle.简单的查询语句.限制.排序

2018-07-09  21:34:00

  1 一、简单查询：
  2 1、查询数据表的所有列：
  3 SELECT * FROM 表名;
  4 SELECT 列名,列名.... FROM 表名;
  5 
  6 2、起别名：
  7 SELECT 列名 [AS 别名],列名 别名.... 
  8 FROM 表名;
  9 
 10 ---正常
 11 SELECT ename AS 员工姓名,sal salary
 12 FROM emp;
 13 
 14 ---别名是关键字，别名需要用双引号引起来
 15 
 16 SELECT ename "select",sal "from"
 17 FROM emp;
 18 
 19 ---别名中间存在空格，别名需要用双引号引起来
 20 SELECT ename "employee name",sal 
 21 FROM emp;
 22 
 23 3、distinct:去重
 24 ---查询emp表中的不同职务
 25 SELECT DISTINCT job
 26 FROM emp;
 27 
 28 ---查询emp表中每个部门的不同职务
 29 SELECT DISTINCT deptno,job
 30 FROM emp;
 31 
 32 ---查询emp表中不同部门的不同职务
 33 SELECT DISTINCT deptno,DISTINCT job
 34 FROM emp;   ---错误
 35 
 36 注意：select后面，多列去重，在所有列的前面写一个distinct关键字即可
 37 
 38 4、|| 连接输出
 39 ---将每个员工的信息输出为如下格式：
 40 姓名为：xxxx，工资为：xxx。
 41 字符串和日期用单引号引起来的。
 42 
 43 SELECT '姓名为：'|| ename || '，工资为：' || sal || '。'  员工信息
 44 FROM emp;
 45 
 46 5、四则运算：
 47 ---查询员工的姓名和年薪
 48 SELECT ename,sal*12 年薪
 49 FROM emp;
 50 
 51 SELECT ename,(sal+comm)*12 年薪
 52 FROM emp;
 53 
 54 ---当与null值参与运算或是比较时，结果一定为null
 55 nvl(agr1,arg2):判断arg1是否为Null,如果不为null，结果为arg1，如果为null,结果为arg2
 56 SELECT ename,(sal+nvl(comm,0))*12 年薪
 57 FROM emp;
 58 
 59 1.员工转正后，月薪上调20%，请查询出所有员工转正后的月薪。
 60 SELECT SAL*1.2 "员工转正后的月薪"
 61 FROM EMP
 62 2.员工试用期6个月，转正后月薪上调20%，请查询出所有员工工作第一年的年薪所得（不考虑奖金部分,年薪的试用期6个月的月薪+转正后6个月的月薪)
 63 SELECT SAL*6+SAL*1.2*6 "工作第一年年薪"
 64 FROM EMP
 65 1.员工试用期6个月，转正后月薪上调20%，请查询出所有员工工作第一年的所有收入（需考虑奖金部分)，要求显示列标题为员工姓名，工资收入，奖金收入，总收入。
 66 SELECT ENAME 员工姓名,SAL*6+SAL*1.2*6 工资收入,nvl(comm,0) 奖金收入,(SAL*6+SAL*1.2*6+nvl(comm,0)) 总收入 
 67 FROM EMP
 68 1.员工试用期6个月，转正后月薪上调20%，请查询出所有员工工作第一年的所有收入（需考虑奖金部分)，要求显示格式为:XXX的第一年总收入为XXX
 69 SELECT ENAME||'的第一年总收入为'||(SAL*6+SAL*1.2*6+nvl(comm,0)) 
 70 FROM EMP
 71 
 72 二、排序：
 73 SELECT 列名 [AS 别名],列名 别名.... 
 74 FROM 表名
 75 ORDER BY 排序字段 ASC|DESC,排序字段 ASC|DESC...;
 76 
 77 排序字段：列名，列的别名，列的顺序号，函数结果
 78 
 79 SELECT *
 80 FROM emp
 81 ORDER BY deptno,sal desc;
 82 
 83 SELECT ename a,deptno b,sal c
 84 FROM emp
 85 ORDER BY b,c desc;
 86 
 87 SELECT ename a,deptno b,sal c
 88 FROM emp
 89 ORDER BY 2,3 desc;
 90 
 91 ---按照员工的部门编号 升序排序，部门相同按照奖金降序排序
 92 
 93 SELECT ename a,deptno b,sal c,comm
 94 FROM emp
 95 ORDER BY 2,nvl(comm,0) desc;
 96 
 97 三、限定查询
 98 SELECT 列名 [AS 别名],列名 别名.... 
 99 FROM 表名
100 WHERE 条件
101 ORDER BY 排序字段 ASC|DESC,排序字段 ASC|DESC...;
102 
103 1、关系运算符：
104 >,<,<=,>=,=,<>,!=
105 可以比较数据类型：数值型，字符串，日期
106 ---查询员工的工资大于2500的员工信息
107 SELECT *
108 FROM emp
109 WHERE sal>2500;
110 
111 ---查询员工姓名 为SMITH的员工信息
112 SELECT *
113 FROM emp
114 WHERE ename='SMITH';
115 
116 ---查询在1982年之后入职的员工信息
117 '日-月-年'
118 'DD-MM-RR'
119 
120 SELECT *
121 FROM emp
122 WHERE hiredate > '31-12月-1982';
123 
124 2、逻辑运算符：
125 and:与
126 or:或
127 not:非
128 
129 not>and>or
130 
131 ---查询员工的职务是MANAGER或是SALESMAN，且工资大于1500的员工的姓名，职务，工资
132 SELECT ename,job,sal
133 FROM emp
134 WHERE (job='MANAGER' OR JOB = 'SALESMAN') and sal>1500;
135 
136 3、between...and... 闭区间
137 语法：字段名 [not] between 最小值 and 最大值
138 
139 --查询在1982年入职的员工信息
140 SELECT *
141 FROM emp
142 WHERE hiredate between '1-1月-1982' and '31-12月-1982';
143 
144 4、in:集合运算
145 语法：字段名 [not] in(值1，值2，值3...)
146 ---查询员工的职务是MANAGER或是SALESMAN，且工资大于1500的员工的姓名，职务，工资
147 SELECT ename,job,sal
148 FROM emp
149 WHERE job in('MANAGER','SALESMAN') and sal>1500;
150 
151 5、is null:没有
152 is not null:有
153 
154 ---查询没有领导的员工信息
155 SELECT *
156 FROM emp
157 WHERE mgr is null;
158 
159 ---查询奖金不为null的员工信息
160 SELECT *
161 FROM emp
162 WHERE comm is not null;
163 
164 6、like:模糊查询
165 语法：字段名 [not] like '匹配串' [ESCAPE '转义字符']
166 _:任意1个字符
167 %：0个或 多个任意字段
168 
169 ---查询员工姓名中包含S的员工姓名
170 SELECT ename
171 FROM emp
172 WHERE ename like '%S%';
173 
174 insert into emp(empno,ename,sal,comm,deptno) values(2222,'li_si',2100,200,30);
175 
176 commit;
177 ---查询员工姓名中包含%的员工的姓名
178 SELECT ename
179 FROM emp
180 WHERE ename like '%#%%' ESCAPE '#';
181 
182 SELECT ename
183 FROM emp
184 WHERE ename like '%#_%' ESCAPE '#';
185 
186 SELECT ename
187 FROM emp
188 WHERE ename like '%!%%' ESCAPE '!';
189 
190 SELECT ename
191 FROM emp
192 WHERE ename like '%a_%' ESCAPE 'a';
193 
194 1.查询入职时间在1982-7-9之后，并且不从事SALESMAN工作的员工姓名、入职时间、职位。
195 select ename,hiredate,job
196 from emp
197 where hiredate>'9-7月-1982' and job<>'SALESMAN'
198 
199 
200 2.查询员工姓名的第三个字母是a的员工姓名。
201 select ename
202 from emp
203 where ename like '__a%';
204 
205 3.查询除了10、20号部门以外的员工姓名、部门编号。
206 
207 select ename,deptno
208 from emp
209 where deptno not in(10,20);
210 
211 
212 4.查询部门号为30号员工的信息，先按工资降序排序，再按姓名升序排序。
213 select * 
214 from emp
215 order by sal desc,ename
216 
217 5.查询没有上级的员工(经理号为空)的员工姓名。
218 select ename
219 from emp
220 where mgr is null
221 
222 6.查询工资大于等于4500并且部门为10或者20的员工的姓名\工资、部门编号。
223 select ename,sal,deptno
224 from emp
225 where sal>=4500 and deptno  in(10,20)
226 
227 四、单行函数
228 字符函数，数值函数，日期函数，转换函数，通用函数
229 1、字符函数：
230 (01) upper():转大写
231 (02) lower():转小写
232 (03) initcap():转首字符大写，其它字符小写
233 (04) length():长度
234 
235 DUAL:sys用户的一张表，只有一个字段，sys创建了该表的公共同义词
236 
237 SELECT upper(ename),lower(ename),initcap(ename),length(ename)
238 FROM emp;
239 
240 (05)trim():去首尾的空格
241 SELECT length(trim('      abc     '))
242 FROM dual;
243 
244 trim(letter from str):去掉str首尾的字母letter
245 SELECT trim('h' from 'hello')
246 FROM dual;
247 
248 SELECT trim('t' from 'test')
249 FROM dual;
250 
251 SELECT trim('t' from 'ttttttttesttttttt')
252 FROM dual;
253 
254 (06)concat(str1,str2):连接字符串   ||
255 
256 SELECT concat(ename,job)
257 FROM emp;
258 
259 SELECT concat(concat(ename,job),hiredate)
260 FROM emp;
261 
262 (07)instr(str1,str2,[n1,n2]):返回str2在str1中的位置,从n1开始查找第n2次出现的位置
263 没在str1存在的时，返回0
264 
265 SELECT instr('hello world','l')
266 FROM dual;
267 
268 SELECT instr('hello world','l',1,2)
269 FROM dual;
270 
271 SELECT instr('hello world','a')
272 FROM dual;
273 
274 ---查询员工姓名中不含有A的员工姓名
275 SELECT ename
276 FROM emp
277 WHERE instr(ename,'A')=0;
278 
279 SELECT instr('hello world','world')
280 FROM dual;
281 
282 (08)replace(str1,str2,str3):将str1中的str2替换为str3
283 SELECT replace('abc aaa abc bbb abc ccc','abc','ABC')
284 FROM dual;
285 
286 (09)substr(str,loc,length):截取子串,从str的loc开始截取长度为length的子串
287 --从首位截取,起始位置可以为0或者1
288 SELECT substr('hello world',1,3)
289 FROM dual;
290 
291 SELECT substr('hello world',0,3)
292 FROM dual;
293 
294 ---截取到字符串的尾部,第三个参数省略
295 SELECT substr('hello world',5)
296 FROM dual;
297 
298 ---截取中间子串
299 SELECT substr('hello world',4,3)
300 FROM dual;
301 
302 (10)lpad(str1,length,str2):判断 str1的长度是否等于Length,如果小于length,在str1的左边补充str2，直到长度等于length为止。
303 (11)rpad(str1,length,str2):判断 str1的长度是否等于Length,如果小于length,在str1的右边补充str2，直到长度等于length为止。
304 
305 ---判断hello的长度是否等于10，如果 不等于，在左边补充#
306 SELECT lpad('hello',10,'#')
307 FROM dual;
308 
309 SELECT lpad('hellohellohello',10,'#')
310 FROM dual;
311 
312 SELECT lpad('hello',10,'#$')
313 FROM dual;
314 
315 1.写一个查询,用首字母大写，其它字母小写显示雇员的 ename，显示名字的长度，并给每列一个适当的标签，
316 条件是满足所有雇员名字的开始字母是J、A 或 M 的雇员，并对查询结果按雇员的ename升序排序。（提示：使用initcap、length、substr）
317 SELECT initcap(ename),LENGTH(ename)
318 FROM emp
319 WHERE substr(ename,1,1) IN('J','A','M')
320 ORDER BY 1;
321 
322 
323 1.查询员工姓名中中包含大写或小写字母A的员工姓名。
324 
325 SELECT ename
326 FROM emp
327 WHERE upper(ename) like '%A%';
328 
329 ---查询emp表中职务是clerk的员工姓名
330 SELECT ename
331 FROM emp
332 WHERE lower(job)='clerk';
333 
334 
335 2.查询部门编号为10或20，入职日期在81年5月1日之后，并且姓名中包含大写字母A的员工姓名，
336 员工姓名长度（提示，要求使用INSTR函数，不能使用like进行判断)
337 
338 
339 
340 3.查询每个职工的编号,姓名，工资
341 要求将查询到的数据按照一定的格式合并成一个字符串.
342 前10位：编号,不足部分用*填充,左对齐
343 中间10位：姓名，不足部分用*填充,左对齐
344 后10位：工资，不足部分用*填充,右对齐
345 
346 SELECT rpad(empno,10,'*') || rpad(ename,10,'*') || lpad(sal,10,'*')
347 FROM emp;
348 
349 2、数值函数
350 （01）round(num,p):四舍五入
351 p=0:round(num),四舍五入为整数
352    SELECT round(455.55)
353    FROM dual;
354 p>0:四舍五入到小数点右边第P位
355    SELECT round(455.55,1)
356    FROM dual;
357 p<0:从小数点左边的第|p|位进行四舍五入
358     SELECT round(455.55,-1)
359    FROM dual;
360    
361   SELECT round(455.55,-3)
362    FROM dual;
363    SELECT round(555.55,-3)
364    FROM dual;
365 （02）trunc(num,p):截断
366 p=0:trunc(num),截断为整数
367    SELECT trunc(455.55)
368    FROM dual;
369 p>0:截断到小数点右边第p位
370      SELECT trunc(455.55,1)
371    FROM dual;
372 p<0:从小数点左边的第|p|位进行截断
373     SELECT trunc(455.55,-1)
374    FROM dual;
375 
376 (03):mod(num1,num2):求余
377 SELECT mod(3,2)
378 FROM dual;
379 
380 SELECT mod(3,0)
381 FROM dual;
382 
383 3、日期函数
384 规则： 日期-日期=天数
385       日期+天数=日期
386       
387 当前系统日期：sysdate
388 
389 (01)months_between(date1,date2):返回两个日期之间相差的月份。如果date1>date2，返回正数，反之，返回是负数
390 SELECT trunc(months_between(SYSDATE,'1-1月-2000'))
391 FROM dual;
392 
393 ---统计 emp表中每人入职了多少个月？
394 SELECT ename,trunc(months_between(SYSDATE,hiredate))
395 FROM emp;
396 
397 ---统计 每个人到目前为止入职了多少年零多少月零多少天？
398 SELECT trunc(months_between(SYSDATE,hiredate)/12) 年, trunc(mod((SYSDATE-hiredate),365)/30) 月,
399        trunc(mod(mod((SYSDATE-hiredate),365),30)) 天
400 FROM emp;
401 
402 （02）next_day(DATE,weekday):返回指定日期的下个星期几是什么日期
403 SELECT next_day(SYSDATE,'星期一')
404 FROM dual;
405 
406 (03) last_day(DATE):返回指定日期的所在月份的最后一天
407 
408 SELECT last_day(SYSDATE)
409 FROM dual;
410 
411 (04)add_months(DATE,months):在指定日期上增加若干个月后的日期
412 SELECT add_months(sysdate,4)
413 FROM dual;
414 
415 (05)EXTRACT([MONTH DAY YEAR] FROM DATE):从指定日期中取出日期中的年，月，日
416 SELECT EXTRACT(MONTH FROM SYSDATE)
417 FROM dual;
418 
419 SELECT EXTRACT(year FROM SYSDATE)
420 FROM dual;
421 
422 ---查询在1982年入职的员工信息
423 SELECT *
424 FROM emp
425 WHERE extract(year from hiredate)=1982;
426 
427 ---在一年月前半年入职的员工信息
428 SELECT *
429 FROM emp
430 WHERE EXTRACT(MONTH FROM hiredate)<=6;
431 
432 (06)round(DATE,fmt):
433 (07)trunc(DATE,fmt):
434 fmt:
435    CC：世纪
436    YY：年
437    MM:月
438    DD:日
439    HH:时
440 SELECT round(SYSDATE,'MM')
441 FROM dual;
442 
443 SELECT round(to_date('17-3月-2018'),'MM')
444 FROM dual;
445 
446 4、转换函数
447 （01）to_char():转为字符串
448 将日期转为字符串：
449    to_char(DATE,'format'):将日期转为固定格式的字符串
450 常用的格式：
451   yyyy:年
452   mm:月
453   dd:日
454   day：星期
455   hh:时  hh24:24小时制
456   mi:分
457   ss:秒
458   mon:月
459   
460 SELECT to_char(SYSDATE,'yyyy-mm-dd hh24:mi:ss day')
461 FROM dual;
462 
463 SELECT to_char(SYSDATE,'yyyy')
464 FROM dual;
465 
466 SELECT ename,hiredate
467 FROM emp
468 WHERE to_char(hiredate,'yyyy')='1982';
469 
470 将数值 转为字符串：
471 to_char(NUMBER,'format')：将指定的数据转为固定格式的字符串
472 SELECT to_char(455.55)
473 FROM dual;
474 常见的格式：
475    .:小数点
476    9：任意数字
477    ,:千位符
478    L:本地货币符号
479    $：美元
480 
481 SELECT to_char(4555.55,'L999,999.99')
482 FROM dual;
483 
484 (02)to_number(str,'format'):转为数值,将固定格式的字符串转为数值
485 
486 SELECT TO_NUMBER('￥4,555.55','L9,999.99')
487 FROM dual;
488 
489 (03)to_date(str,'format'):转为日期，将固定格式的字符串转为日期
490 SELECT to_date('21-12-2017','dd-mm-yyyy')
491 FROM dual;
492 
493 5、通用函数：
494 （01）nvl():
495 (02) nvl2(arg1,arg2,arg3):判断arg1是否为null，如果不为Null,返回arg2，如果为Null,返回arg3
496 SELECT ename,comm,nvl2(comm,comm,0)
497 FROM emp;
498 
499 ---查询员工的姓名，奖金，如果奖金为null,输出为“No comm.”
500 SELECT ename,comm,nvl2(comm,to_char(comm),'No comm.')
501 FROM emp;
502 
503 (03)decode():类似于等值判定，swith
504 语法：decode(exp/comm,值1,结果1,值2,结果2....,值n,结果n,结果m);
505 ---判定员工姓名中含A或a的位置，如果位置在首位，显示“A在首位”,如果位置在末尾，显示“A在末尾”，如果A在中间，显示"A在中间"，否则，“不包含A”
506 SELECT ename,decode(instr(upper(ename),'A'),1,'A在首位',0,'不包含A',LENGTH(ename),'A在末尾','A在中间')
507 FROM emp;
508 
509 (04):CASE():
510 格式一：
511 CASE WHEN 条件1 THEN 结果1
512      WHEN 条件2 THEN 结果2
513      ....
514      WHEN 条件N THEN 结果n
515      ELSE 结果m
516      END;
517 
518 SELECT ename,CASE WHEN upper(ename) LIKE 'A%' THEN 'A在首位'
519                   WHEN upper(ename) LIKE '%A' THEN 'A在末尾'
520                   WHEN upper(ename) NOT LIKE '%A%' THEN '不包含A'
521                   ELSE 'A在中间'
522                   END
523 FROM emp;
524 
525 格式二：
526 CASE 表达式/列名 WHEN 值1 THEN 结果1
527                WHEN 值2 THEN 结果2
528                。。。
529                WHEN 值n THEN 结果n
530                ELSE 结果m
531                END
532 ------------------------------练习题--------------------------------------------
533 ---课后练习
534 ---第三章课后作业
535 1.查询入职时间在1982-7-9之后，并且不从事SALESMAN工作的员工姓名、入职时间、职位。
536 
537 select ename,hiredate,job 
538 from emp
539 where hiredate>'9-7月-1982' and job not in('SALESMAN');
540 
541 2.查询员工姓名的第三个字母是a的员工姓名。
542 
543 select ename
544 from emp
545 where ename like '__a%'
546 
547 
548 3.查询除了10、20号部门以外的员工姓名、部门编号。
549 
550 select ename,deptno
551 from emp
552 where deptno not in(10,20);
553 
554 4.查询部门号为30号员工的信息，先按工资降序排序，再按姓名升序排序。 
555 
556 select * 
557 from emp
558 where deptno=30 
559 order by sal desc ,ename ;
560 
561 5.查询没有上级的员工(经理号为空)的员工姓名。
562 
563 select ename
564 from emp
565 where mgr is null;
566 
567 6.查询工资大于等于4500并且部门为10或者20的员工的姓名\工资、部门编号。
568 
569 select ename,sal,deptno
570 from emp
571 where sal>=4500 and deptno in(10,20);
572 
573 
574 ---第四章课后作业
575 ---？？？-----------------------------------------------------
576 1.计算2000年1月1日到现在有多少月，多少周（四舍五入）。
577 
578 select round(months_between(sysdate,'1-1月-2000')) 月,round(months_between(sysdate,'1-1月-2000'))*4 周
579 from emp;
580 
581 2.查询员工ENAME的第三个字母是A的员工的信息(使用2个函数)。
582 
583 select ename
584 from emp
585 where ename like '__A%';
586 
587 select ename
588 from emp
589 where instr(ename,'A')=3;
590 
591 
592 
593 3.使用trim函数将字符串‘hello’、‘  Hello ’、‘bllb’、‘ hello    ’
594 分别处理得到下列字符串ello、Hello、ll、hello。
595 ---'hello'---ello
596 select trim('h' from 'hello')
597 from dual
598 
599 ---'  Hello '---Hello
600 select trim('  Hello ')
601 from dual;
602 
603 ---'bllb'---ll
604 select trim('b' from 'bllb')
605 from dual;
606 
607 ---' hello    '---hello
608 select trim(' hello    ')
609 from dual;
610 
611 4.将员工工资按如下格式显示：123,234.00 RMB 。
612 
613 select to_char(sal,'999,999,99') ||' RMB'
614 from emp;
615 
616 5.查询员工的姓名及其经理编号，要求对于没有经理的显示“No Manager”字符串。
617 ---nvl2()返回的数值类型必须一致
618 select ename,nvl2(mgr,to_char(mgr),'No Manager')
619 from emp;
620 
621 6.将员工的参加工作日期按如下格式显示：月份/年份。 
622 
623 select to_char(hiredate,'mon/yyyy') 
624 from emp;
625 
626 7.在员工表中查询出员工的工资，并计算应交税款：
627 如果工资小于1000,税率为0，
628 如果工资大于等于1000并小于2000，税率为10％，
629 如果工资大于等于2000并小于3000，税率为15％，
630 如果工资大于等于3000，税率为20％。
631 
632 select sal 员工工资,
633 case when sal<1000 then 0 
634      when sal >=1000 and sal<2000  then sal*0.1 
635      when sal >=2000 and sal<3000  then sal*0.15
636      when sal >=3000 and sal<2000  then sal*0.2  
637      end
638 from emp;
639 ---end后不加";"号
640 ---应交税款 sal*0.1  工资*税率
641 
642 
643 8.创建一个查询显示所有雇员的 ename和 sal。格式化sal为 15 个字符长度，用 $ 左填充，列标签 SALARY。
644 
645 select ename ,lpad(sal,15,'$') SALARY
646 from emp;
647 
648 实验２  使用WHERE和ORDER BY子句
649 实验要求
650 （１）掌握WHERE子句的用法。
651 （２）掌握ORDER BY子句的用法。
652 实验步骤
653 （１）创建一个查询显示工资大于2850美元的雇员的姓名及工资
654 
655 select ename,sal
656 from emp
657 where sal>2850;
658 
659 （２）显示工资不在1500到2850美元之间的雇员的姓名及工资
660 
661 select ename,sal
662 from emp
663 where sal not between 1500 and 2850 ;
664 
665 
666 （３）显示在10和30部门工作并且工资大于1500美元的雇员的姓名和工资，
667       列标题显示为Employee和Monthly Salary 
668       
669 select ename Employee,sal "Monthly Salary"
670 from emp
671 where deptno in(10,30) and sal >1500;
672 
673 （４）显示奖金比工资多10％以上的雇员的姓名、工资及奖金。
674 
675 select ename,sal,comm
676 from emp
677 where sal*(1.1)<comm;
678 
679 思考练习题
680 １．创建一个查询显示雇员编号为7566的雇员的姓名和部门编号。
681 
682 select ename,deptno
683 from emp
684 where empno=7566
685 
686 ２．显示受雇时间在February 20，1981和May 1，1981之间的雇员的姓名、工资、
687 及受雇时间，并以受雇时间升序排列。
688 
689 select ename,sal,trunc(mod((SYSDATE-hiredate),365)/30)  受雇月数
690 from emp 
691 where hiredate between '20-2月-1981' and '1-5月-1981' 
692 order by  3;
693 
694 
695 ３．显示在10号和30号部门工作的雇员的姓名及其部门编号，并以字母顺序排列。
696 
697 select ename,deptno
698 from emp
699 where deptno in(10,30)
700 order by ename;
701 
702 ４．显示所有受雇于1982年的雇员的姓名和受雇时间。
703 
704 select ename ,hiredate
705 from emp
706 where extract(year from hiredate)=1982;
707 
708 
709 ５．显示没有上级管理员的雇员的姓名及其工作。
710 
711 select ename,job
712 from emp
713 where mgr is null;
714 
715 ６．显示能挣得奖金的雇员的姓名、工资、奖金，并以工资和奖金降序排列。
716 
717 select ename,sal,comm
718 from emp
719 where comm is not null and comm<>0
720 order by sal desc,comm desc;
721 
722 ７．显示姓名中第三个字母为A的雇员的姓名。
723 select ename
724 from emp
725 where ename like '__A%';
726 
727 ８．显示姓名中两次出现字母L并且在30部门工作或者其管理员编号是7782的雇员的姓名。
728 
729 select ename
730 from emp
731 where instr(ename,'L')=2 or mgr=7782;
732 
733 ９．显示工作为Clerk或Analyst并且工资不等于$1000、$3000、$5000的雇员的姓名、工作及工资。
734 
735 select ename,job,sal
736 from emp
737 where job in (upper('Clerk'),upper('Analyst')) and sal not in(1000,3000,5000);
738 
739 实验３  在SELECT语句中使用单行函数
740 实验要求
741 掌握常用的单行函数的用法。
742 实验步骤
743 （１）显示雇员的编号、姓名、工资以及工资增长15％后的整数值（以New Salary作为列标题）
744 
745 select empno,ename,sal,trunc(sal*(1+0.15)) "New Salary"
746 from emp;
747 
748 （２）在(1)的基础上，增加一个列Increase显示new salary和salary的差值
749 
750 select empno,ename,sal,trunc(sal*(1+0.15)) "New Salary",trunc(sal*(1+0.15))-sal 上涨工资
751 from emp;
752 
753 ---???
754 （３）显示雇员姓名并以*为指示符代表工资数额（列标题为EMPLOYEE_AND_THEIR_SALARIES），
755 一个指示符代表一百美元，并以工资数额降序排列。
756 
757 select ename,lpad(' ',(trunc(sal,-2)/100)+1,'*') EMPLOYEE_AND_THEIR_SALARIES
758 from emp
759 order by sal desc;
760 
761     
762 思考练习题
763 １．编写一个显示当期日期的查询，列标题为Date。
764 
765 select sysdate "Date"
766 from emp;
767 
768 ２．显示雇员姓名、受雇日期及工资复审日期（复审日期为受雇后6个月后的第一个星期一），
769 复审日期以列标题REVIEW显示。
770 
771 select ename,hiredate,next_day(add_months(hiredate,6),'星期一')
772 from emp;
773 
774 ３．显示每个雇员的姓名并计算出从受雇日期起到目前一共工作了多少个月，
775 以列标题MONTHS_WORKED显示月数（四舍五入），输出以月数升序排列。
776 
777 select ename,round(mod((SYSDATE-hiredate),365)/30) MONTHS_WORKED
778 from emp
779 order by MONTHS_WORKED;
780 
781 ４．编写一个以下面的形式输出的查询：
782 <employee name> earns <salary> monthly but wants <3 times salary>.
783 列标题为Dream Salaries。
784 
785 select ename||'earns'||sal||'monthly but wants'||sal*3  "Dream Salaries"
786 from emp;
787 ---重写列名有空格，" " 双引号
788 
789 ５．创建一个查询显示每个雇员的姓名及工资（列标题为SALARY），
790 工资的显示模式为：占15个字符的宽度，对于不够位数的以$填充。
791 
792 select ename,rpad(sal,15,'$') SALARY
793 from emp;
794 
795 ６．编写一个查询显示名字以J、A、M开头的雇员的姓名及姓名所占的字符数，
796 姓名的显示格式为第一个字母大写其他字母小写，为每个列设置合适的列标题。
797 
798 SELECT initcap(ename) 员工姓名,LENGTH(ename) 姓名长度
799 FROM emp
800 WHERE substr(ename,1,1) IN('J','A','M');
801 
802 ７．显示雇员的姓名、受雇日期及受雇当天是星期几（列标题为DAY），
803 并以DAY升序排列
804 
805 select ename,hiredate,to_char(hiredate,'day') DAY
806 from emp
807 order by 3;
808 
809 ８．创建一个查询显示雇员姓名及其奖金（列标题为COMM），
810 如果该雇员不能获得奖金则以“No Commission.”显示。
811 
812 select ename,nvl2(comm,to_char(comm),'No Commission.') COMM
813 from emp;
814               

 

转载于:https://www.cnblogs.com/twinkle-star/p/9282437.html