Oracle查询练习.docx
《Oracle查询练习.docx》由会员分享,可在线阅读,更多相关《Oracle查询练习.docx(20页珍藏版)》请在冰豆网上搜索。
![Oracle查询练习.docx](https://file1.bdocx.com/fileroot1/2023-1/10/f0257cdd-dfdf-4b62-946c-93de0d1aeb68/f0257cdd-dfdf-4b62-946c-93de0d1aeb681.gif)
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