oracle50筑基练习

--1.       查询员工表所有数据, 并说明使用*的缺点 
select * from emp;

--2.       查询职位(JOB)为'PRESIDENT'的员工的工资
select ename,sal,job from emp where job='PRESIDENT';
--3.       查询佣金(COMM)为0或为NULL的员工信息
select ename,comm from emp where comm=0 or comm is null;
--4.       查询入职日期在1981-5-1 到1981-12-31之间的所有员工信息
select * from emp where to_char(hiredate,'yyyy-MM-dd') between '1981-05-01' and '1981-12-31';
--5.       查询所有名字长度为4 的员工的员工编号,姓名
 select empno,ename from emp where length(ename)=4 ;
--6.       显示10 号部门的所有经理('MANAGER')和20号部门的所有职员('CLERK')的详细信息
select deptno,job as Manager from emp where deptno=10 and job='Manager';
select * from emp where deptno=20 and job='CLERK';
select *from emp where ( deptno=10 and job='Manager') or (deptno=20 and job='CLERK')
--7.       显示姓名中没有'L'字的员工的详细信息或含有'SM'字的员工信息
select * from emp where ename not like'%L%' or ename like '%SM%';
--8.       显示各个部门经理('MANAGER')的工资
select deptno,job as MANAGER,sal from emp where job='MANAGER';
(9).       显示佣金(COMM)收入比工资(SAL)高的员工的详细信息 
select * from emp where comm > sal;
(10).    把hiredate列看做是员工的生日,求本月过生日的员工
select ename from emp where extract(month from hiredate)=extract(month from sysdate)；
--11.    把hiredate列看做是员工的生日,求下月过生日的员工
select ename from emp where extract(month from hiredate)=extract(month from sysdate)+1;
--12.    求1982年入职的员工
 select ename,hiredate from emp where extract(year from hiredate)='1982';
--13.    求1981年下半年入职的员工
select ename,hiredate from emp where extract(year from hiredate)=1981 and extract(month from hiredate)in(7,8,9,10,11,12) ;
(14).    求1981年各个月入职的的员工个数
  SELECT COUNT(*),TRUNC(HIREDATE,'MONTH'