select'编号:'|| empno ||'-姓名:'|| ename ||'-工作:'|| job as 详情 from emp;
查询雇员编号,姓名,工作,年薪
select empno,ename,job,(sal + nvl(comm,0))*12 YearlySalary from emp;
查询工资大于1500的雇员所有信息
select*from emp where sal >1500;
查询可以得到奖金的雇员的名字与奖金
select ename,comm from emp where NVL(comm,0)>0;select ename,comm from emp where DECODE(comm,null,0,comm)>0;
查询工资大于1500或可以得到奖金的雇员
select ename from emp where sal >1500or nvl(comm,0)>0;select ename from emp where sal >1500or DECODE(comm,null,0,comm)>0;
查询工资大于1500并且可以领取奖金的雇员
select ename from emp where sal >1500and nvl(comm,0)>0;select ename from emp where sal >1500and DECODE(comm,null,0,comm)>0;
查询工资不大于1500或者不可以领取奖金的雇员
select ename from emp where sal <=1500or nvl(comm,0)=0;
查询工资在1500到3000的所有雇员信息
select*from emp where sal >=1500and sal <=3000;
查询系统当前时间
select sysdate from dual;
查询在1981年雇用的员工信息
select*from emp where hiredate like'%81%';select*from emp where to_char(hiredate,'YYYY')='1981';select*from emp where hiredate <= to_date('1981-12-31','YYYY-mm-dd')and hiredate >= to_date('1981-01-01','YYYY-mm-dd');select*from emp where hiredate between to_date('1981-01-01','YYYY-mm-dd')and to_date('1981-12-31','YYYY-mm-dd');
查询雇员姓名中第三个字母为”A”的雇员信息
select*from emp where ename like'__A%';
查询雇员编号为7369的雇员信息
select*from emp where empno ='7369';
查询雇员编号不为7369的雇员信息
select*from emp where empno !='7369';select*from emp where empno <>'7369';
查询编号是7369,7900的雇员信息
select*from emp where empno in(7369,7900);
查询编号不是7369,7900的雇员信息
select*from emp where empno notin(7369,7900);
查询雇员信息,按工资由低到高排序
select*from emp orderby sal;select*from emp orderby sal asc;
查询雇员信息,按工资由高到低排序
select*from emp orderby sal desc;
请查询没有领导的员工名和职位
select ename,job from emp where mgr isnull;
查询有领导的员工名和职位
select ename,job from emp where mgr isnotnull;
查询所有员工名、职位以及领导名
select e1.ename,e1.job,e2.ename from emp e1,emp e2 where e1.mgr = e2.empno(+);
查询部门30中的所有员工信息
select*from emp where deptno =30;
列出所有办事员(CLERK)的姓名,编号和部门编号(按部门编号升序排列)
select ename,empno,deptno from emp where job ='CLERK'orderby deptno;