本文所使用的是oracle11r2版本,在PLSQL Developer上联系查询操作,题目来源网络(好像是某位博主的)
刚开始接触oracle,有部分题目理解不到位,查询不到位,欢迎指正交流。
所使用的表格为emp与dept
emp表:
dept表:
--1.列出至少有一个员工的所有部门。
我的答案:
select distinct e.deptno,d.dname
from emp e
left join dept d
on e.deptno=d.deptno;
网上答案:
select dname from dept where deptno in(select deptno from emp group by deptno having count(deptno)>=1);
--2.列出薪金比“SMITH”多的所有员工。
我的答案:
select empno,ename,sal
from emp
where sal>(
select sal
from emp
where ename='SMITH');
网上答案:
select * from emp where sal>(select sal from emp where ename='SMITH');
--3.列出所有员工的姓名及其直接上级的姓名。
我的答案:
select a.ename empname,b.ename manager
from emp a
left join emp b
on a.mgr=b.empno;
网上答案:
select a.ename,(select ename from emp b where b.empno=a.mgr) as boss_name from emp a;
--4.列出受雇日期早于其直接上级的所有员工。
我的答案:
select a.ename as empname,a.HIREDATE as ehire,b.ename as manager,b.hiredate as mhire
from emp a,emp b
where a.mgr=b.empno
and (to_date(a.HIREDATE)-to_date(b.hiredate))<0;
网上答案:
select a.ename from emp a where a.hiredate>(select hiredate from emp b where b.empno=a.mgr);
--5.列出部门名称和这些部门的员工信息,同时列出那些没有员工的部门(left join从左表那里返回所有的行,即使在右表中没有匹配的行。)
我的答案:
select dname,empno,ename,job
from dept d left join emp e
on d.deptno=e.deptno
order by dname;
网上答案:
Select a.dname,b.empno,b.ename,b.job,b.mgr,b.hiredate, b.sal, b.deptno from dept a left join emp b on a.deptno=b.deptno;
--6.列出所有“CLERK”(办事员)的姓名及其部门名称。(:as 是 alias 的缩写,是“别名”的意思,后面接中文需要用 "" 。)
我的答案:
select ename,dname
from emp a left join dept b
on a.deptno=b.deptno
where a.job='CLERK';
网上答案:
select a.ename,(select dname from dept b where b.deptno=a.deptno) as dname from emp a where a.job='CLERK';select a.ename,b.dname from emp a join dept b on a.deptno=b.deptno and a.job='CLERK';
--7.列出最低薪金大于1500的各种工作。(distinct 筛选重复值)
我的答案:
不会
网上答案:
select distinct job from emp group by job having min(sal)>1500;
--8.列出在部门“SALES”(销售部)工作的员工的姓名,假定不知道销售部的部门编号。
我的答案:
select ename
from emp a,dept b
where a.deptno=b.deptno
and b.dname='SALES';
网上答案:
select ename from emp where deptno=(select deptno from dept where dname='SALES');
--9.列出薪金高于公司平均薪金的所有员工。
我的答案:
select ename
from emp
where sal>
(select avg(sal)
from emp);
网上答案:
select ename from emp where sal>(select avg(sal) from emp);
--10.列出与“SCOTT”从事相同工作的所有员工。
我的答案:
select ename
from emp
where ename not in('SCOTT')
and job=(select job
from emp
where ename='SCOTT');
网上答案:
select ename from emp where job=(select job from emp where ename='SCOTT');
--11.列出薪金等于部门30中员工的薪金的所有员工的姓名和薪金。(in 操作符允许我们在where子句中规定多个值)
网上答案:
select ename,sal from emp where sal in(select sal from emp where deptno=30);
--12.列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金。(max() 求最大值)
网上答案:
select ename,sal from emp where sal>(select max(sal) from emp where deptno=30);
--13.列出在每个部门工作的员工数量、平均工资和平均服务期限。(group by+[分组字段](可以有多个)用于结合合计函数,根据一个或多个列对结果集进行分组。)
我的答案:
select count(deptno) sl,avg(sal) pjgz
from emp a group by deptno;
网上答案:
select (select b.dname from dept b where b.deptno=a.deptno) 部门, count(deptno) 平均工资,avg(sal) 平均服务期限 from emp a group by deptno;
--14.列出所有员工的姓名、部门名称和工资。
我的答案:
select ename,dname,sal
from emp a left join dept b
on a.deptno=b.deptno;
网上答案:
select ename,(select b.dname from dept b where b.deptno= a.deptno) 部门,sal from emp a;
--15.列出所有部门的详细信息和部门人数。(count(列名)函数返回指定列的值的数目,返回的是一个数字)
我的答案:
select b.deptno,b.dname,b.loc,(select count(ename) from emp a where a.deptno=b.deptno) coun from dept b
网上答案:
select deptno, dname, loc,(select count(deptno) from emp b where b.deptno=a.deptno group by deptno) 部门人数 from dept a;
--16.列出各种工作的最低工资。( min(列名) 求最小值 ;max(列名) 求最大值 )
我的答案:
select job,min(sal)
from emp group by(job);
网上答案:
select job, min(sal+nvl(comm,0) from emp group by job;
select job, min(nvl2(comm,sal+comm,sal)) from emp group by job;
--17.列出各个部门的MANAGER(经理)的最低薪金。
我的答案:
select deptno,min(sal)
from (select a.deptno,a.ename,a.sal
from emp a,emp b
where b.mgr=a.empno) group by deptno
网上答案:
select deptno,(select dname from dept b where b.deptno=a.deptno) 部门,min(sal) from emp a where job='MANAGER' group by deptno;
--18.列出所有员工的年工资,按年薪从低到高排序。( nvl(表达式,value):如果表达式计算结果为 null ,则返回 value)
网上答案:
select ename,(sal+nvl(comm,0))*12 as salpersal from emp order by salpersal;
select ename,(nvl2(comm,sal+comm, sal))*12 as salpersal from emp order by salpersal;
------第二篇
--1. 找出EMP表中的姓名(ENAME)第三个字母是A 的员工姓名。(like 操作符用于在 where 子句中搜索列中的指定模式。 "%" 可用于定义通配符(模式中缺少的字母)。)
我的答案:
select ename
from emp
where ename like '__A%';
网上答案:
select ename from emp where ename like '__A%';
--2. 找出EMP表员工名字中含有A 和N的员工姓名。(and 并且 ; or 或者)
我的答案:
select ename from emp
where ename like '%A%' and ename like '%N%';
网上答案:
select ename from emp where ename like '%A%' and ename like '%N%'
--3. 找出所有有佣金的员工,列出姓名、工资、佣金,显示结果按工资从小到大,佣金从大到小。(order by 默认按照升序排序;order by 列名 desc 降序)
我的答案:
select ename,sal,comm
from emp
where comm is not null
order by sal,comm desc;
网上答案:
select ename,nvl2(comm,sal+comm,sal) as wage,comm from emp order by wage,comm desc;
--4. 列出部门编号为20的所有职位。(和第一篇的第七题类似)
我的答案:
select distinct job
from emp
where deptno=20;
网上答案:
select distinct job from emp where deptno=20;
--5. 列出不属于SALES 的部门。
--in和exists的区别:
--exists(相关子查询):存在,后面一般都是子查询,不返回列表的值,只是返回一个ture或false的结果
--in:包含,in()后面的子查询 是返回结果集的
网上答案:
select distinct * from dept where dname not in('SALES');select distinct * from dept where exists(select dname from dept where dname='SALES');
--6. 显示工资不在1000 到1500 之间的员工信息:名字、工资,按工资从大到小排序。(between ... and 会选取介于两个值之间的数据范围。)
我的答案:
方法一:
select ename,sal
from emp
where sal<1000 or sal>1500
order by sal desc;
方法二:
select ename,sal
from emp
where sal not in (select sal from emp where sal between 1000 and 1500)
order by sal desc ;
网上答案:
select ename,nvl2(comm,sal+comm,sal) as wage from emp where nvl2(sal,comm+comm,sal) not between 1000 and 1500 order by wage desc;select ename,nvl2(comm,sal+comm,sal) as wage from emp where nvl2(sal,comm+comm,sal)<1000 or sal+comm>1500 order by wage desc;
-- 7. 显示职位为 MANAGER 和 SALESMAN,年薪在15000 和20000 之间的员工的信息:名字、职位、年薪。
我的答案:
select ename,job,(sal+nvl(comm,0))*12 as ysal
from emp
where job in ('MANAGER','SALESMAN')
and (sal+nvl(comm,0))*12 between 15000 and 20000;
网上答案:
select ename 姓名,job 职位,(sal+nvl(comm,0))*12 年薪 from emp where (sal+comm)*12 between 15000 and 20000 and job in('MANAGER','SALESMAN')
-- 8. 说明以下两条SQL语句的输出结果:
-- SELECT EMPNO,COMM FROM EMP WHERE COMM IS NULL;
-- SELECT EMPNO,COMM FROM EMP WHERE COMM = NULL;
网上答案:
IS NULL:是判断某个‘字段’是否为空,为空并不等价于为空字符串或为数字0;
NULL :是判断某个‘值’是否等于NULL,NULL=NULL和NULL="" 都返回false。<br="">
--9.让SELECT 语句的输出结果为
-- SELECT * FROM SALGRADE;
-- SELECT * FROM BONUS;
-- SELECT * FROM EMP;
-- SELECT * FROM DEPT;
-- ……
--列出当前用户有多少张数据表,结果集中存在多少条记录。
我的答案:
select *
from user_table;
网上答案:
select * from user_tables;
--10.判断SELECT ENAME,SAL FROM EMP WHERE SAL > '1500'是否报错,为什么?
网上答案:
不会报错,这儿存在隐式数据类型的。