ema heapdump 查询 查询emp表的第4-5条记录

一

1, 查询emp表的所有列数据;

select * from emp;

2, 查询dept表所有列的数据;

select * from dept;

3, 查询emp表的empno列;

select empno from emp;

4, 查询emp表的ename列;

select ename from emp;

5, 查询emp表的deptno列;

select deptno from emp;

6, 查询emp表的empno和ename两列;

select empno, ename from emp;

7, 查询emp表的empno,deptno和sal三列;

select empno, deptno, sal from emp;

8, 查询dept表的name,loc和deptno三列;

select dname, loc, deptno from dept;

9, 查询dept表的deptno,name两列;

select deptno, name from dept;

10, 查询emp表的ename,hiredate和deptno三列;

select ename, hiredate, deptno from emp;

二

1, 给emp表取别名a, 并查询empno, ename和deptno三列;

select a.empno,a.ename, a.deptno from emp a;

2, 为dept表取别名b,并查询deptno,dname两列;

select b.deptno, b.dname from dept b;

3, 查询emp表的ename, job和hiredate三列,并分别取别名 员工姓名,工种和入职时间;

select ename as 员工姓名, 
       job as 工种, 
       hiredate as 入职时间 
from emp;

4, 查询emp表的ename, deptno和sal三列,并分别取别名为EMP_NAME, 部门编号和收入;

select ename as EMP_NAME, 
       deptno as 部门编号, 
       sal as 收入 
from emp;

5, 查询emp表的empno, mgr和deptno三列,分别取别名员工编号,管理员编号和部门编号;

select empno as 员工编号, 
       mgr as 管理员编号, 
       deptno as 部门编号 
from emp;

三

1, 查询emp表中ename是SMITH的人的deptno和mgr;

select deptno, mgr from emp where ename='SMITH';

2, 查询emp表中job是MANAGER的人的信息;

select * from emp where job='MANAGER';

3, 查询emp表中sal是1250的人的ename和deptno;

select ename, deptno, sal from emp where sal=1250;

4, 查询emp表中comm是1000的所有人的信息;

select * from emp where comm=1000;

5, 查询emp表中deptno是30的ename和mgr

select ename, mgr, deptno from emp where deptno=30;

6, 查询emp表中sal值大于2450的所有数据;

select * from emp where sal>2450;

7, 查询emp表中comm值小于1000的所有数据;

select * from emp where comm<1000;

8, 查询emp表中comm值大于等于1000的所有数据;

select * from emp where comm>=1000;

9, 查询emp表中sal小于等于3000的所有数据;

select * from emp where sal<=3000;

10, 查询emp表中deptno不等于10的所有人信息;

select * from emp where deptno<>10;

四

1,查询工资大于1000的员工信息;

select * from emp where sal>1000;

2,找出部门10中所有经理(job是MANAGER的为经理)和部门20中的所有办事员（job是CLERK的是办事员）的详细资料;

select * from emp 
where deptno=10 and job='MANAGER' 
or deptno=20 and job='CLERK';

select * from emp 
where (deptno,job) 
in ((10,'MANAGER'),(20,'CLERK'));

3,请从表EMP中查找工种是职员CLERK或经理MANAGER的雇员姓名、工资;

select ename, sal from emp 
where job='CLERK' or job='MANAGER';

select ename, sal from emp
where job in('CLERK','MANAGER');

4,请在EMP表中查找部门号在10－30之间的雇员的姓名、部门号、工资、工作;

select ename, deptno, sal, job from emp 
where deptno>=10 and deptno<=30;

select ename, deptno, sal, job from emp 
where deptno between 10 and 30;

5,查找工资在1000～3000之间的雇员所在部门的所有人员信息;

select * from emp 
where sal>=1000 and sal<=3000;

select * from emp
where sal between 1000 and 3000;

6,查询工作是CLERK的员工信息;

select * from emp where job='CLERK';

7,查询表EMP中所有的工资大于等于2000的雇员姓名;

select * from emp where sal>=2000;

8,查询10号和20号部门的员工信息;

select * from emp where deptno=10 or deptno=20;

9,查询emp表中所有员工的信息，要求查询结果列名用中文显示;

select ename 员工姓名, 
       empno 员工编号, 
       sal 工资, 
       mgr 管理员编号, 
       deptno 部门编号, 
       job 工种, 
       hiredate 入职日期, 
       comm 佣金 
 from emp;

10,找出不收取佣金(comm)或收取的佣金低于100的雇员;

select * from emp where comm is null or comm<100;

五

1, 查询名字中有L的员工信息;

select * from emp where ename like '%L%';

2, 查询姓名中不带有R的员工信息;

select * from emp where ename not like '%R%';

3, 查询emp表中ename列所有以EN结尾的数据;

select * from emp where ename like '%EN';

4, 查找emp表中ename列所有以MA开头的数据;

select * from emp where ename like 'MA%';

5, 查找emp表中ename列中包含LA字样的数据;

select * from emp where ename like '%LA%';

6, 查找emp表中ename列有%的数据;

select * from emp where ename like '%/%%' escape '/';

7, 查找emp表中ename列有_的数据;

select * from emp where ename like '%/_%' escape '/';

8, 查找emp表中ename列同时有%和_的数据;

select * from emp 
where ename like '%/%%' escape '/' 
and ename like '%/_%' escape '/';

9, 查找emp表中ename列同时有%和_,且挨在一起的数据;

select * from emp 
where ename like '%/%/_%' escape '/' 
or ename like '%/_/%%' escape '/';

10, 查找emp表中ename列同时有%和_，且不挨在一起的数据;

select * from emp where ename like '%/%%' escape '/'  
and ename like '%/_%' escape '/' 
and ename not like '%/%/_%' escape '/' 
and ename not like '%/_/%%' escape '/';

select * from emp
where ename like '%/%%_/_%' escape '/'
or ename like '%/_%_/%%' escape '/';