Oracle经典查询练手第四篇

--1、	查询emp表中的所有信息
select * from emp;

--2、	显示emp表的雇员名称和工资。
select ename,sal from emp;

--3、	查询emp表中部门编号为20的并且sal（工资）大于3000的所有员工信息
select * from emp where deptno=20 and sal >3000;

--4、	查询emp表中部门编号为20的或者sal（工资）大于3000的所有员工信息
select * from emp where deptno=20 or sal >3000;

--5、	使用between and 查询工资在2000和4000之间的员工（用and 重新实现）
select * from emp where sal between 2000 and 4000;

--6、	使用in 查询 部门编号10，20的所有员工
select * from emp where deptno in (10,20);

--7、	使用like查询所有名字中包括 W的员工信息
select * from emp where ename like '%W%';

--8、	使用like查询所有员工名字中第二子字母为W的员工信息
select * from emp where ename like '_W%';

--9、	查询所有员工信息并按照部门编号和工资进行排序
select * from emp order by deptno ,sal;

--10、	显示雇员工资上浮20%的结果。
select ename,sal*1.2 from emp;

--11、	显示EMP表的雇员名称以及工资和津贴的和。
select ename,sal+(
  case
  when comm is null then 0
  else comm
  end ) from emp;

select ename,sal+nvl(comm,0) from emp;

--12、	在查询中使用列别名。特殊的别名如别名中有空格
select sal "the sal" from emp ;

--13、	显示DEPT表的内容，使用别名将表头转换成中文显示。
select deptno as 部门编号 , dname  as 部门名称,loc as  所在地址 from dept;

--14、	显示DEPT表的内容，按以下的形式:部门ACCOUNTING所在的城市为NEW YORK     
select '部门 '|| dname|| ' 所在的城市 ' ||loc from dept;

--15、	使用DISTINCT消除重复行显示。显示emp表中的的职位
select distinct job from emp;

--16、	查询雇员姓名和工资，并按工资从小到大排序。
select  ename ,sal from emp order by sal asc;

--17、	查询雇员姓名和雇佣日期，并按雇佣日期排序，后雇佣的先显示。
select ename ,hiredate from emp order by hiredate desc;

--18、	查询雇员信息，先按部门从小到大排序，再按雇佣时间的先后排序。
select * from emp order by deptno asc ,hiredate asc;

--19、	按工资和工作月份的乘积排序。
select  sal* to_number(to_char(hiredate,'MM')) k from emp order by k;
select  sal *extract(month from (hiredate)) k from emp order by k;

--20、	显示职务为“SALESMAN”的雇员的姓名、职务和工资。
select ename,job,sal from emp where job ='SALESMAN';

--21、	显示工资大于等于3000的雇员姓名、职务和工资。
select ename,job,sal from emp where sal >=3000;

--22、	显示1982年以后雇佣的雇员姓名和雇佣时间。
selectename ,floor((sysdate-hiredate)/365) from emp where hiredate >to_date(1982,'YY');

--23、	显示部门编号为10的雇员姓名和雇佣时间
select ename,to_char(hiredate,'YYYY-MM-DD') from emp where deptno =10;

--24、	显示工资在1000～2000之间(不包括1000和2000)的雇员信息。
select * from emp where sal>1000 and sal <2000;

--25、	显示部门10中工资大于1500的雇员。
select * from emp where sal>1500 and deptno=10;

--26、	显示职务为CLERK或MANAGER的雇员信息。
select * from emp where job in ('CLERK','MANAGER');

--27、	显示部门10以外的其他部门的雇员。
select * from emp where deptno != 10;

--28、	显示部门10和部门20中工资小于1500的雇员。
select * from emp where sal<1500 and deptno in(10,20);

--29、	显示经理编号没有填写的雇员。
select * from emp where mgr is null;

--30、	如果不知道表的字段内容是大写还是小写，可以转换后比较。
select upper(ename)from emp;
select lower(ename) from emp;

--31、	显示名称以“W”开头的雇员，并将名称转换成以大写开头。
select initcap(ename) from emp where ename like 'W%';

--32、	显示雇员名称中包含“S”的雇员名称及名称长度。
select ename ,length(ename) from emp where ename like '%S%';

--33、	显示部门表中部门和所在城市列表，中间以下划线“_”连接，城市名转换成以大写字母开头。
select dname||'_'||initcap(loc) from dept;

--34、	返回系统的当前日期
select to_char(sysdate,'YYYY-MM-DD') from dual;

--35、	返回2003年2月的最后一天。
select last_day(to_date(200302,'YYYYMM')) from dual;

--36、	假定当前的系统日期是2003年2月6日，求再过1000天的日期。
select to_date(20030206,'YYYYMMDD')+1000 from dual;

--37、	假定当前的系统日期是2003年2月6日，显示部门10雇员的雇佣天数。
select to_date(20030206,'YYYYMMDD') - hiredate from emp;

--38、	显示雇员名称和雇佣的星期数
select floor((sysdate - hiredate)/7) from emp;

--39、	显示从本年1月1日开始到现在经过的天数(当前时间取SYSDATE的值)。
select sysdate - to_date(concat(extract(year from (sysdate)),'0101'),'YYYYMMDD') from dual;

--40、	将日期显示转换成中文的年月日。
select to_char(sysdate,'YYYY')||'年'||to_char(sysdate,'MM')||'月'||to_char(sysdate,'DD')||'日' from dual;

--41、	将雇佣日期转换成字符串并按新格式显示。（17 12月 1980）
select to_char(hiredate,'DD MM YYYY') from emp;

--42、	使用nvl函数转换空值。（如果是数值转换成0 字符转换为 空）
select nvl(comm,0) from emp;



--43、.在emp表中查询出员工的工资，并计算应交税款：
--如果工资小于1000,税率为0，
--如果工资大于等于1000并小于2000，税率为10％，
--如果工资大于等于2000并小于3000，税率为15％，
--如果工资大于等于3000，税率为20％。（使用case）
select sal ,(
case 
  when sal < 1000 then 0
  when sal < 2000 then 10
  when sal < 3000 then 15
  else 20 
end
) from emp;

select sal,decode(floor(sal/1000),0,0,1,10,2,15,20) from emp;

--44、显示雇员的名称和所在的部门的编号和名称。
select emp.ename,dept.deptno,dept.dname from emp,dept where emp.deptno=dept.deptno;

-45、显示工资大于3000的雇员的名称、工资和所在的部门名称。
select emp.ename,emp.sal,dept.dname from emp,dept where emp.deptno=dept.deptno and emp.sal>3000;

--46、使用外连显示不满足相等条件的记录。显示工资小于1000的雇员的名称、工资和所在的部门名称。
select emp.ename ,emp.sal ,dept.dname from emp join dept on emp.deptno=dept.deptno where emp.sal <1000;

--47、显示雇员的名称、工资和所在的部门名称及没有属于任何部门的雇员。
select emp.ename ,emp.sal ,dept.dname from emp left join dept on emp.deptno=dept.deptno;

--48、得到当前日期前三个月的日期
select add_months(sysdate,-3) from dual;

--49、字符串转日期（12小时制）
select to_date('2015-01-02 11:23:12','yyyy-mm-dd hh:mi:ss') from dual;

--49、字符串转日期（24小时制）
select to_date('2015-01-02 23:23:12','yyyy-mm-dd hh24:mi:ss') from dual;

--50、本月最后一天
select last_day(sysdate) from dual;

--51、求某天的下个星期的某一天（如 ，下一个星期二）
select next_day(sysdate, 2)from dual;

--52、日期的四舍五入
select round(to_date('20130321 231232','YYYYMMDD hh24miss')) from dual; --进一
select round(to_date('20130321 111232','YYYYMMDD hh24miss')) from dual; --不变

--53、提取时间的部分属性值
select extract (year from sysdate) from dual;   --systimestamp也可以
select extract (month from sysdate) from dual;  --systimestamp也可以
select extract (day from sysdate) from dual;    --systimestamp也可以

select to_char(sysdate,'hh') from dual;
select extract(hour from systimestamp) from dual; --好像不正确
select extract(minute from systimestamp) from dual;
select extract(second from systimestamp) from dual;

--54、货币格式化
select to_char('123124.23','999,999,999.00') from dual;
select to_char('123124.23','L999,999,999.00') from dual;

--55.	找出EMP表中的姓名（ENAME）第三个字母是A 的员工姓名。
select ename from emp where ename like '__A%';

--56.	找出EMP表员工名字中含有A 和N的员工姓名。
select ename from emp where ename like'%A%N%' or ename like '%N%A%'  ;

--57.	找出所有有佣金的员工，列出姓名、工资、佣金，显示结果按工资从小到大，佣金从大到小。
select ename,sal,comm from emp where comm>0 order by sal asc ,comm desc;

--58.	列出部门编号为20的所有职位。
select distinct job from emp where deptno=20;

--59.	列出不属于SALES 的部门。
select distinct * from dept where dname !='SALES';

--60.	显示工资不在1000 到1500 之间的员工信息：名字、工资，按工资从大到小排序。
select ename ,sal from emp  where sal not between 1000 and 1500 order by sal desc;

--61.	显示职位为MANAGER 和SALESMAN，年薪在15000 和20000 之间的员工的信息：名字、职位、年薪。
select ename ,job,12*(sal)from emp where job in('MANAGER','SALESMAN') and 12*(sal) between 15000 and 20000;

--62.	说明以下两条SQL语句的输出结果：
	SELECT EMPNO,COMM FROM EMP WHERE COMM IS NULL;  --有数据
	SELECT EMPNO,COMM FROM EMP WHERE COMM = NULL;  --无数据
  --is null 和 = null的差别在于 is null 是进行逻辑判断，返回值是true或false,而 = null是进行数值判断，null在oracle中是表示未知，也就是不确定
  --所以在进行判断的时候返回的结果也是null，也是不确定，容易发生错误。

 --63.查询当前用户所创建的所有表
 select rownum ||'.SELECT * FROM ' || table_name from user_tables;

 --64.	判断SELECT ENAME,SAL FROM EMP WHERE SAL > '1500'是否报错，为什么？
 --不会，oracle 会将字符型转换成数值型，但这样会降低查询的效率，因为oracle会花费时间去进行隐性的数据类型转换