基础查询练习题
一
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 '/';
