Oracle查询练习.docx
《Oracle查询练习.docx》由会员分享，可在线阅读，更多相关《Oracle查询练习.docx（20页珍藏版）》请在冰豆网上搜索。
Oracle查询练习
练习查询第一篇
1.--1、 查询emp表中的所有信息
2.select * from emp;
3.
4.--2、 显示emp表的雇员名称和工资。
5.select ename,sal from emp;
6.
7.--3、 查询emp表中部门编号为20的并且sal（工资）大于3000的所有员工信息
8.select * from emp where deptno=20 and sal >3000;
9.
10.--4、 查询emp表中部门编号为20的或者sal（工资）大于3000的所有员工信息
11.select * from emp where deptno=20 or sal >3000;
12.
13.--5、 使用between and 查询工资在2000和4000之间的员工（用and 重新实现）
14.select * from emp where sal between 2000 and 4000;
15.
16.--6、 使用in 查询 部门编号10，20的所有员工
17.select * from emp where deptno in （10,20）;
18.
19.--7、 使用like查询所有名字中包括 W的员工信息
20.select * from emp where ename like '%W%';
21.
22.--8、 使用like查询所有员工名字中第二子字母为W的员工信息
23.select * from emp where ename like '_W%';
24.
25.--9、 查询所有员工信息并按照部门编号和工资进行排序
26.select * from emp order by deptno ,sal;
27.
28.--10、 显示雇员工资上浮20%的结果。
29.select ename,sal*1.2 from emp;
30.
31.--11、 显示EMP表的雇员名称以及工资和津贴的和。
32.select ename,sal+（
33. case
34. when comm is null then 0
35. else comm
36. end ） from emp;
37.
38.select ename,sal+nvl（comm,0） from emp;
39.
40.--12、 在查询中使用列别名。
特殊的别名如别名中有空格
41.select sal "the sal" from emp ;
42.
43.--13、 显示DEPT表的内容，使用别名将表头转换成中文显示。
44.select deptno as 部门编号 , dname as 部门名称,loc as 所在地址 from dept;
45.
46.--14、 显示DEPT表的内容，按以下的形式:
部门ACCOUNTING所在的城市为NEW YORK
47.select '部门 '|| dname|| ' 所在的城市 ' ||loc from dept;
48.
49.--15、 使用DISTINCT消除重复行显示。
显示emp表中的的职位
50.select distinct job from emp;
51.
52.--16、 查询雇员姓名和工资，并按工资从小到大排序。
53.select ename ,sal from emp order by sal asc;
54.
55.--17、 查询雇员姓名和雇佣日期，并按雇佣日期排序，后雇佣的先显示。
56.select ename ,hiredate from emp order by hiredate desc;
57.
58.--18、 查询雇员信息，先按部门从小到大排序，再按雇佣时间的先后排序。
59.select * from emp order by deptno asc ,hiredate asc;
60.
61.--19、 按工资和工作月份的乘积排序。
62.select sal* to_number（to_char（hiredate,'MM'）） k from emp order by k;
63.select sal *extract（month from （hiredate）） k from emp order by k;
64.
65.--20、 显示职务为“SALESMAN”的雇员的姓名、职务和工资。
66.select ename,job,sal from emp where job ='SALESMAN';
67.
68.--21、 显示工资大于等于3000的雇员姓名、职务和工资。
69.select ename,job,sal from emp where sal >=3000;
70.
71.--22、 显示1982年以后雇佣的雇员姓名和雇佣时间。
72.Selectename ,floor（（sysdate-hiredate）/365） from emp where hiredate >to_date（1982,'YY'）;
73.
74.--23、 显示部门编号为10的雇员姓名和雇佣时间
75.select ename,to_char（hiredate,'YYYY-MM-DD'） from emp where deptno =10;
76.
77.--24、 显示工资在1000～2000之间（不包括1000和2000）的雇员信息。
78.select * from emp where sal>1000 and sal <2000;
79.
80.--25、 显示部门10中工资大于1500的雇员。
81.select * from emp where sal>1500 and deptno=10;
82.
83.--26、 显示职务为CLERK或MANAGER的雇员信息。
84.select * from emp where job in （'CLERK','MANAGER'）;
85.
86.--27、 显示部门10以外的其他部门的雇员。
87.select * from emp where deptno !
= 10;
88.
89.--28、 显示部门10和部门20中工资小于1500的雇员。
90.select * from emp where sal<1500 and deptno in（10,20）;
91.
92.--29、 显示经理编号没有填写的雇员。
93.select * from emp where mgr is null;
94.
95.--30、 如果不知道表的字段内容是大写还是小写，可以转换后比较。
96.select upper（ename）from emp;
97.select lower（ename） from emp;
98.
99.--31、 显示名称以“W”开头的雇员，并将名称转换成以大写开头。
100.select initcap（ename） from emp where ename like 'W%';
101.
102.--32、 显示雇员名称中包含“S”的雇员名称及名称长度。
103.select ename ,length（ename） from emp where ename like '%S%';
104.
105.--33、 显示部门表中部门和所在城市列表，中间以下划线“_”连接，城市名转换成以大写字母开头。
106.select dname||'_'||initcap（loc） from dept;
107.
108.--34、 返回系统的当前日期
109.select to_char（sysdate,'YYYY-MM-DD'） from dual;
110.
111.--35、 返回2003年2月的最后一天。
112.select last_day（to_date（200302,'YYYYMM'）） from dual;
113.
114.--36、 假定当前的系统日期是2003年2月6日，求再过1000天的日期。
115.select to_date（20030206,'YYYYMMDD'）+1000 from dual;
116.
117.--37、 假定当前的系统日期是2003年2月6日，显示部门10雇员的雇佣天数。
118.select to_date（20030206,'YYYYMMDD'） - hiredate from emp;
119.
120.--38、 显示雇员名称和雇佣的星期数
121.select floor（（sysdate - hiredate）/7） from emp;
122.
123.--39、 显示从本年1月1日开始到现在经过的天数（当前时间取SYSDATE的值）。
124.select sysdate - to_date（concat（extract（year from （sysdate））,'0101'）,'YYYYMMDD'） from dual;
125.
126.--40、 将日期显示转换成中文的年月日。
127.select to_char（sysdate,'YYYY'）||'年'||to_char（sysdate,'MM'）||'月'||to_char（sysdate,'DD'）||'日' from dual;
128.
129.--41、 将雇佣日期转换成字符串并按新格式显示。
（17 12月 1980）
130.select to_char（hiredate,'DD MM YYYY'） from emp;
131.
132.--42、 使用nvl函数转换空值。
（如果是数值转换成0 字符转换为 空）
133.select nvl（comm,0） from emp;
134.
135.
136.
137.--43、.在emp表中查询出员工的工资，并计算应交税款：
138.--如果工资小于1000,税率为0，
139.--如果工资大于等于1000并小于2000，税率为10％，
140.--如果工资大于等于2000并小于3000，税率为15％，
141.--如果工资大于等于3000，税率为20％。
（使用case）
142.select sal ,（
143.case
144. when sal < 1000 then 0
145. when sal < 2000 then 10
146. when sal < 3000 then 15
147. else 20
148.end
149.） from emp;
150.
151.select sal,decode（floor（sal/1000）,0,0,1,10,2,15,20） from emp;
152.
153.--44、显示雇员的名称和所在的部门的编号和名称。
154.select emp.ename,dept.deptno,dept.dname from emp,dept where emp.deptno=dept.deptno;
155.
156.-45、显示工资大于3000的雇员的名称、工资和所在的部门名称。
157.select emp.ename,emp.sal,dept.dname from emp,dept where emp.deptno=dept.deptno and emp.sal>3000;
158.
159.--46、使用外连显示不满足相等条件的记录。
显示工资小于1000的雇员的名称、工资和所在的部门名称。
160.select emp.ename ,emp.sal ,dept.dname from emp join dept on emp.deptno=dept.deptno where emp.sal <1000;
161.
162.--47、显示雇员的名称、工资和所在的部门名称及没有属于任何部门的雇员。
163.select emp.ename ,emp.sal ,dept.dname from emp left join dept on emp.deptno=dept.deptno;
164.
165.--48、得到当前日期前三个月的日期
166.select add_months（sysdate,-3） from dual;
167.
168.--49、字符串转日期（12小时制）
169.select to_date（'2015-01-02 11:
23:
12','yyyy-mm-dd hh:
mi:
ss'） from dual;
170.
171.--49、字符串转日期（24小时制）
172.select to_date（'2015-01-02 23:
23:
12','yyyy-mm-dd hh24:
mi:
ss'） from dual;
173.
174.--50、本月最后一天
175.select last_day（sysdate） from dual;
176.
177.--51、求某天的下个星期的某一天（如 ，下一个星期二）
178.select next_day（sysdate, 2）from dual;
179.
180.--52、日期的四舍五入
181.select round（to_date（'20130321 231232','YYYYMMDD hh24miss'）） from dual; --进一
182.select round（to_date（'20130321 111232','YYYYMMDD hh24miss'）） from dual; --不变
183.
184.--53、提取时间的部分属性值
185.select extract （year from sysdate） from dual; --systimestamp也可以
186.select extract （month from sysdate） from dual; --systimestamp也可以
187.select extract （day from sysdate） from dual; --systimestamp也可以
188.
189.select to_char（sysdate,'hh'） from dual;
190.select extract（hour from systimestamp） from dual; --好像不正确
191.select extract（minute from systimestamp） from dual;
192.select extract（second from systimestamp） from dual;
193.
194.--54、货币格式化
195.select to_char（'123124.23','999,999,999.00'） from dual;
196.select to_char（'123124.23','L999,999,999.00'） from dual;
197.
198.--55. 找出EMP表中的姓名（ENAME）第三个字母是A 的员工姓名。
199.select ename from emp where ename like '__A%';
200.
201.--56. 找出EMP表员工名字中含有A 和N的员工姓名。
202.select ename from emp where ename like'%A%N%' or ename like '%N%A%' ;
203.
204.--57. 找出所有有佣金的员工，列出姓名、工资、佣金，显示结果按工资从小到大，佣金从大到小。
205.select ename,sal,comm from emp where comm>0 order by sal asc ,comm desc;
206.
207.--58. 列出部门编号为20的所有职位。
208.select distinct job from emp where deptno=20;
209.
210.--59. 列出不属于SALES 的部门。
211.select distinct * from dept where dname !
='SALES';
212.
213.--60. 显示工资不在1000 到1500 之间的员工信息：
名字、工资，按工资从大到小排序。
214.select ename ,sal from emp where sal not between 1000 and 1500 order by sal desc;
215.
216.--61. 显示职位为MANAGER 和SALESMAN，年薪在15000 和20000 之间的员工的信息：
名字、职位、年薪。
217.select ename ,job,12*（sal）from emp where job in（'MANAGER','SALESMAN'） and 12*（sal） between 15000 and 20000;
218.
219.--62. 说明以下两条SQL语句的输出结果：
220. SELECT EMPNO,COMM FROM EMP WHERE COMM IS NULL; --有数据
221. SELECT EMPNO,COMM FROM EMP WHERE COMM = NULL; --无数据
222. --is null 和 = null的差别在于 is null 是进行逻辑判断，返回值是true或false,而 = null是进行数值判断，null在oracle中是表示未知，也就是不确定
223. --所以在进行判断的时候返回的结果也是null，也是不确定，容易发生错误。
224.
225. --63.查询当前用户所创建的所有表
226. select rownum ||'.SELECT * FROM ' || table_name from user_tables;
227.
228. --64. 判断SELECT ENAME,SAL FROM EMP WHERE SAL > '1500'是否报错，为什么？
229. --不会，oracle 会将字符型转换成数值型，但这样会降低查询的效率，因为oracle会花费时间去进行隐性的数据类型转换
练习查询第二篇
1. 哪些部门的人数比90 号部门的人数多。
2.select department_id ,count（*）
3.from employees
4.group by department_id
5.having count（*）>（select count（*） from employees where department_id=90）;
6.
7.--2. Den（FIRST_NAME）、Raphaely（LAST_NAME）的领导是谁（非关联子查询）。
8.select first_name||' '||last_name from employees where employee_id = （
9. select manager_id from employees where first_name='Den' and last_name='Raphaely'）;
10.
11.--3. Den（FIRST_NAME）、Raphaely（LAST_NAME） 领导谁（非关联子查询）。
12.select first_name||' '||last_name from employees where manager_id = （
13. select employee_id from employees where first_name='Den' and last_name='Raphaely'）;
14.
15.--4. Den（FIRST_NAME）、Raphaely（LAST_NAME） 的领导是谁（关联子查询）。
16.select first_name||' '||last_name
17.from employees a
18.where exists
19. （select 1 from employees b
20. where a.employee_id=b.manager_id
21. and b.first_name='Den'
22. and b.last_name='Raphaely'）;
23.
24.--5. Den（FIRST_NAME）、Raphaely（LAST_NAME） 领导谁（关联子查询）。
25.select first_name||' '||last_name
26.from employees a
27.where exists
28. （select 1 from employees b
29. where a.manager_id=b.employee_id
30. and b.first_name='Den'
31. and b.last_name='Raphaely'）;
32.
33.--6. 列出在同一部门共事，入职日期晚但工资高于其他同事的员工：
名字、工资、入职日期（关联子查询）。
34.select first_name||' '||last_name
35.from employees a
36.where exists
37. （select 1 from employees b
38. where a.department_id=b.department_id
升级会员 