--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'