Oracle——SQL基础练习

Oracle——SQL基础练习

题目 1：查询当前用户下的所有表

select *from user_tables;

题目 2：查询雇员表中所有信息

select * from emp;

题目 3：查询雇员编号, 姓名, 工作, 工资

select empno,ename,job,sal from emp;

题目 4：查询雇员编号, 姓名, 工作, 工资, 列标题需显示中文

select empno 编号,ename 姓名,job 工作,sal 工资 from emp;

题目 5：消除重复列, 查询雇员工作种类

select distinct job from emp;

题目 6：查询所有雇员编号, 姓名, 工作

select empno,ename,job from emp;

题目 7：查询雇员编号, 姓名, 工作, 年薪

 select empno,ename,job,sal*12 from emp;

题目 8：查询工资大于 1500 的雇员所有信息

select * from emp where sal > 1500;

题目 9：查询可以得到奖金的雇员所有信息

select * from emp where comm is not null;

题目 10：查询工资大于 1500 或可以得到奖金的雇员

select * from emp where sal > 1500 or comm is not null;

题目 11：查询工资大于 1500 并且可以领取奖金的雇员

select * from emp where sal >1500 and comm is not null;

题目 12：查询工资不大于 1500 或者不可以领取奖金的雇员

select * from emp where sal <= 1500 or comm is null;

题目 13：查询工资在 1500 到 3000 的所有雇员信息

select * from emp where sal between 1500 and 3000;

题目 14：查询在 1981 年雇用的员工信息

elect * from emp where to_char(hiredate,'yyyy') = 1981;

题目 15：查询雇员姓名中第二个字母为 “M” 的雇员信息

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

题目 16：查询雇员工资中带 8 这个数字的

select * from emp where sal like '%8%';

题目 17：查询编号是 7369,7499,7521,7900 的雇员信息

select * from emp where empno in(7369,7499,7521,7900);

题目 18：查询雇员编号不是 7369,7499,7521,7900 的所有雇员信息

 select * from emp where empno not in(7369,7499,7521,7900);

题目 19：查询雇员编号为 7369 的雇员信息

select * from emp where empno =7369;

题目 20：查询雇员编号不为 7369 的雇员信息

select * from emp where empno <>7369;

题目 21：查询雇员信息, 按工资由低到高排序

select * from emp order by sal asc;

题目 22：查询雇员信息, 按工资由高到低排序

select * from emp order by sal desc;

题目 23：HR 需要得到一个月薪资 $12000 以上的员工名 (last_name) 和薪水报告, 请完成查询语句

select last_name,salary from employees where salary > 12000

题目 25：HR 需要识别高收入和低收入员工群体不在5000到12000之间的

select * from employees where salary <=5000 or salary >=12000;

题目 26：请查询中 1994 年雇员的员工名和雇佣日期

select ename,hiredate from emp where to_char(hiredate,'yyyy') = '1994';

题目 27：请查询没有领导的员工名和职位

select ename,job from emp where mgr is null;

题目 28：请查询有奖金的员工名和月薪、奖金，并按奖金系数排降序

select ename,sal,comm from emp where comm is not null order by comm desc;

题目 29：请查询月薪大于 1000 的员工名和月薪

 select ename,sal from emp where sal >1000;

题目 30：请查询第三个字母是 a 的员工名

select ename from emp where ename like '__a%'; 

题目 31：请查询员工名包含 a 或 e 的员工名

select ename from emp where ename like '%a%' or ename like '%e%';

题目 32：请查询职位是 “SA_REP” 或“ST_CLERK”，且月薪不是 $2500、$3500、$7000 的员工信息

select * from emp where job in('SA_REP','ST_CLERK') AND SAL not in(2500,3500,7000);

题目 33：请查询奖金系数是 20% 的员工名、薪水和奖金系数

select first_name,salary,commission_pct from employees where commission_pct = 0.2;

题目 34：查询部门 30 中的所有员工信息

select * from employees where department_id = 30;

题目 35：列出所有办事员 (CLERK) 的姓名, 编号和部门编号

select first_name,employee_id,department_id from employees where job_id ='PU_CLERK';

题目 36：找出佣金高于薪金的员工信息

select * from emp where comm > sal;

题目 37：找出佣金高于薪金的 60% 的员工信息

 select * from emp where comm >sal*0.6;

题目 38：找出部门 10 中所有经理 (MANAGER) 和部门 20 中所有办事员 (CLERK) 的详细资料

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

题目 39：找出部门 10 中所有经理 (MANAGER), 部门 20 中的所有办事员 (CLERK), 既不是经理又不是办事员但薪金大于或等于 2000 的所有员工的详细资料

 select * from emp where deptno in(10,20) or job in('MANAGER','CLERK') and(job not in('MANAGER','CLERK') and sal >=2000);

题目 40：找出收取佣金的员工的不同工作

select distinct job from emp where comm is not null;

题目 41：找出不收取佣金或收取佣金低于 100 的员工信息

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

题目 42：找出各月倒数第 3 天受雇的所有员工信息

select * from emp where hiredate =  last_day(hiredate)-2;

题目 43：找出早于 12 年前受雇的员工信息

select * from emp where months_between(sysdate,hiredate)/12 >12;

题目 44：以首字母大写的方式显示所有员工的姓名

select initcap(ename) from emp;

题目 45：显示正好为 5 个字符的员工的姓名

select ename from emp where length(ename) = 5;

题目 46：显示不带有 “R” 的员工的姓名

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

题目 47：显示所有员工的姓名的前三个字符

select substr(ename,1,3) from emp;

题目 48：显示所有员工的姓名, 用 “a” 代替所有的 “A”

select replace(ename,'A','a') from emp;

题目 49：显示满 10 年服务年限的员工的姓名和受雇日期

select ename,hiredate from emp where months_between(sysdate,hiredate)/12 >10;

题目 50：显示员工详细信息, 按姓名排升序

select * from emp order by ename asc;

题目 51：显示员工的姓名和受雇日期, 根据其服务年限, 将最老的员工排在前面

select ename,hiredate from emp order by hiredate asc;

题目 52：显示所有员工的姓名, 工作和薪金, 按工作的降序排序, 若工作相同则薪金升序排序

select ename,job,sal from emp order by job desc,sal asc;

题目 53：显示所有员工的姓名, 加入公司的年份和月份, 按受雇日期所在月排序, 若月份相同则将最早年

份的员工排在前面（要求使用 extract 函数）

select ename,extract(year from hiredate) year,extract(month from hiredate) month from emp order by month asc,year desc;

题目 54：显示在一个月为 30 天的情况, 所有员工的名字和日薪金, 忽略余数

select ename,trunc(sal/30) from emp ;

题目 55：找出在 (任何年份的)2 月受雇的所有员工信息

select * from emp where extract(month from hiredate) = 2;

题目 56：对于每个员工, 显示名字和其加入公司的天数, 保留 2 位小数 (四舍五入)

select ename,around(sysdate - hiredate,2) from emp;

题目 57：显示姓名字段的任何位置包含 “A” 的所有员工的姓名

select ename from emp where ename like '%A%';

题目 58: 查询系统当前日期

select sysdate from dual;

题目 59 : 请查询每个员工的名字、薪水和加薪15.5%之后的薪水（trunc取整)

select ename,sal,trunc(sal*1.155) from emp;

题目 60 : 显示员工加薪 15

select sal+15 from emp;

题目 61 : 显示所有以’J’，‘A’，'M’打头的员工的名字和名字长度，且按照名字排升序

select ename,length(ename) from emp where  substr(ename,1,1) in('J','A','M');

题目 62 : 请查询员工名和工作时间（换算成月并取整），并按工作时间排降序

select ename,round((sysdate - hiredate)/30) worktime from emp order by worktime desc

题目 63 : 请查询员工的名字和薪水，并将薪水列变成 15 个字符长度，左边填充 “$” 符号

select ename,lpad(sal,15,'$') from emp;

题目 64 : 请查询部门 id 为 90 的所有员工的名字和他们参加工作的星期数 (保留 2 位小数，不需要四舍

五入) 使用

select ename,trunc((sysdate - hiredate)/7,2) from emp where deptno = 90;

题目 65 : 创建报告，显示员工名和奖金系数, 如果奖金系数为空, 则显示 $ 无奖金

select last_name,decode(commission_pct,'','$无奖金',commission_pct) from employees;

题目 66 : 请使用 case 语句，查询员工的 job_id 和级别

select job_id,case job_id when 'AC_ACCOUNT' THEN 'A'
                   WHEN 'FI_MGR' THEN 'B'
                   else 'C' end grade
from employees;
				   

题目 67 : 请使用 decode 语句，查询员工的 job_id 和级别

select job_id,decode(job_id,'AC_ACCOUNT','A','FI_MGR','B','C') from employees;

题目 69：查询 emp 和 dept 表, 产生笛卡尔积 (多表行相乘)

select * from emp,dept;

题目 70：加 where 条件过滤查询 emp 和 dept 表产生的笛卡尔积

select * from emp e,dept d where e.deptno = d.deptno;

题目 71：查询 emp 和 dept 表, 产生笛卡尔积 (多表行相乘), 并为表取别名

select * from emp e,dept d;

题目 72：查询雇员姓名, 所在部门编号和名称

select e.ename,e.deptno,d.dname from emp e,dept d where e.deptno = d.deptno;

题目 73：查询所有雇员姓名, 工作, 领导的姓名

select e.ename,e.job,e2.ename mgrname from emp e,emp e2 where e.mgr = e2.empno(+);

题目 74：查询雇员姓名, 工作, 领导姓名及部门名称

select e.ename,e.job,e2.ename mgrname,d.dname from emp e,emp e2,dept d where e.mgr = e2.empno(+) and d.deptno = e.deptno;

题目 75：查询雇员姓名, 工作, 工资及工资等级

select e.ename,e.job,e.sal,sg.grade from emp e,salgrade sg where e.sal between sg.losal and sg.hisal;

题目 76：查询雇员姓名, 工作, 工资及工资等级, 要求工资等级显示为 A B C D E

select e.ename,e.job,e.sal,sg.grade ,decode(sg.grade,1,'A',2,'B',3,'C',4,'D',5,'E') from emp e,salgrade sg where e.sal between sg.losal and sg.hisal;

题目 77：查询雇员姓名, 年薪, 按年薪从高到低排序

select ename,(sal*12) yearSal from emp order by yearSal desc;

题目 78：查询每个部门中工资最高的雇员姓名, 工作, 工资, 部门名称, 最后按工资从高到低排序

select e.ename,e.job,e.sal,dd.dname from emp e,(select e.deptno,d.dname,max(e.sal) maxsal from emp e,dept d where e.deptno = d.deptno group by e.deptno,d.dname) dd where e.deptno = dd.deptno and e.sal = dd.maxsal order by e.sal desc;

题目 79：查询每个部门的部门编号和雇员数量

select d.deptno,count(e.deptno) from emp e,dept d where d.deptno = e.deptno group by d.deptno; 

题目 80：求出每个部门的部门名和平均工资 (保留 2 位小数，截断)

select d.dname,trunc(avg(e.sal),2) from emp e,dept d where e.deptno(+) = d.deptno group by d.dname;

题目 81：按部门分组，并显示部门的名称，以及每个部门的员工数

select d.deptno,d.dname,count(e.deptno) from emp e,dept d where d.deptno = e.deptno(+) group by d.deptno,d.dname; 

题目 82：要求显示平均工资大于 2000 的部门编号和平均工资 (保留 2 位小数，截断)

select d.deptno,trunc(avg(e.sal),2) avgSal from emp e,dept d where e.deptno(+) = d.deptno group by d.deptno having trunc(avg(e.sal),2) >2000 ;

题目 83：显示非销售人员工作名称以及从事同一工作雇员的月工资的总和，并且要满足从事同一工作的雇员的月工资大于 $1500，输出结果按月工资的合计升序排列

select job,sum(sal) sumsal from emp where job <>'SALESMAN' and sal > 1500 group by job order by sumsal;

题目 84：求出平均工资最高的部门名称

select d.dname from emp e,dept d where e.deptno = d.deptno group by
d.dname having avg(e.sal) =(select max(avgsal) from(select d.dname,avg(e.sal) as avgsal from dept d,emp e where e.deptno = d.deptno group by d.dname)) ;

题目 85：要求查询出比 7654 工资要高的全部雇员的信息

select * from emp where sal >(select sal from emp where empno = 7654);

题目 86：要求查询工资比 7654 高，与 7788 从事相同工作的全部雇员信息

select * from emp where sal >(select sal from emp where empno = 7654) and job =(select job from emp where empno = 7788);

题目 87：查询出工资最低的雇员姓名，工作，工资

select ename,job,sal from emp where sal = (select min(sal) from emp);

题目 88：查询出各部门工资最低的雇员姓名，工作，工资

select e.ename,e.job,e.sal from emp e where sal in(select min(e.sal) minsal from dept d,emp e where d.deptno = e.deptno(+) group by d.deptno);

题目 89：要求查询出部门名称，部门的员工数，部门的平均工资，部门的最低收入雇员姓名

select e.ename,dd.dname,dd.counts,dd.avgsal,dd.minsal from emp e,(select d.deptno, d.dname,count(e.empno) counts,avg(e.sal) avgsal,min(e.sal) minsal from emp e,dept d where d.deptno=e.deptno(+) group by d.dname,d.deptno) dd where dd.deptno = e.deptno and e.sal in (dd.minsal); 

题目 90: 查询部门人数大于 2 的部门

select d.dname,count(e.deptno) from emp e,dept d where e.deptno (+)= d.deptno group by d.dname having count(e.deptno) >2;