题目1:查询当前用户下的所有表
select * from user_tables;
题目2:查询雇员表中所有信息
select empno,ename, job,mgr,hiredate,sal,comm,deptno 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:查询所有雇员编号,姓名,工作.按以下格式显示:编号:7369,姓名:SMITH,工作:CLERK
select '编号:'||empno||',姓名:'||ename||',工作:'||job 雇员资料 from emp ;
题目7:查询雇员编号,姓名,工作,年薪
select empno 编号,ename 姓名,job 工作,(sal+nvl(comm,0))*12 年薪 from emp;
题目8:查询工资大于1500的雇员所有信息
select empno,ename, job,mgr,hiredate,sal,comm,deptno from emp where sal>1500;
题目9:查询可以得到奖金的雇员所有信息
select empno,ename, job,mgr,hiredate,sal,comm,deptno from emp where comm is not null and comm <>0;
题目10:查询工资大于1500或可以得到奖金的雇员
select empno,ename, job,mgr,hiredate,sal,comm,deptno from emp
where sal>1500 or comm is not null and comm <>0;
题目11:查询工资大于1500并且可以领取奖金的雇员
select empno,ename, job,mgr,hiredate,sal,comm,deptno from emp
where sal>1500 and comm is not null and comm <>0;
题目12:查询工资不大于1500或者不可以领取奖金的雇员
select empno 编号,ename 姓名,job 工作,sal 月薪,comm 奖金,hiredate 雇用日期,deptno 部门编号,mgr 管理者 from emp
where sal<=1500 or comm is null ;
题目13:查询工资在1500到3000的所有雇员信息
select empno,ename, job,mgr,hiredate,sal,comm,deptno from emp
where sal between 1500 and 3000;
题目14:查询在1981年雇用的员工信息
select empno 编号,ename 姓名,job 工作,sal 月薪,comm 奖金,hiredate 雇用日期,deptno 部门编号,mgr 管理者 from emp
where hiredate between '01-1月 -1981' and '31-12月 -1981';
题目15:查询雇员姓名中第二个字母为"M"的雇员信息
select empno,ename, job,mgr,hiredate,sal,comm,deptno from emp where ename like '_M%';
题目16:查询雇员工资中带8这个数字的
select empno,ename, job,mgr,hiredate,sal,comm,deptno from emp where sal like '%8%';
题目17:查询编号是7369,7499,7521,7900的雇员信息
select empno,ename, job,mgr,hiredate,sal,comm,deptno from emp where empno in (7369,7499,7521,7900);
题目18:查询雇员编号不是7369,7499,7521,7900的所有雇员信息
select empno,ename, job,mgr,hiredate,sal,comm,deptno from emp where empno not in (7369,7499,7521,7900);
题目19:查询雇员编号为7369的雇员信息
select empno 编号,ename 姓名,job 工作,sal 月薪,comm 奖金,hiredate 雇用日期,deptno 部门编号,mgr 管理者 from emp
where empno = 7369;
题目20:查询雇员编号不为7369的雇员信息
select empno,ename, job,mgr,hiredate,sal,comm,deptno from emp where empno <>7369;
题目21:查询雇员信息,按工资由低到高排序
select empno,ename, job,mgr,hiredate,sal,comm,deptno from emp order by sal asc;
题目22:查询雇员信息,按工资由高到低排序
select empno,ename, job,mgr,hiredate,sal,comm,deptno 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 last_name,salary from employees where salary not between 5000 and 12000;
题目26:请查询中1994年雇员的员工名和雇佣日期
select last_name 员工名,hire_date 雇佣日期 from employees where to_char(hire_date,'yyyy')='1994';
题目27:请查询没有领导的员工名和职位
select last_name, job_id from employees where manager_id is null;
题目28:请查询有奖金的员工名和月薪、奖金,并按奖金系数排降序
select last_name,salary,salary*nvl(commission_pct,0) from employees
where commission_pct is not null order by commission_pct desc;
题目29:请查询月薪大于1000的员工名和月薪
select last_name,salary from employees where salary >1000;
题目30:请查询第三个字母是a的员工名
select last_name from employees where last_name like '__a%';
题目31:请查询员工名包含a或e的员工名
select last_name from employees where last_name like '%a%' or last_name like '%e%';
题目32:请查询职位是“SA_REP”或“ST_CLERK”,且月薪不是$2500、$3500、$7000的员工信息
select employee_id,,first_name,last_name,email,phone_number,hire_date,job_id,salary,commission_pc,manager_id,department_id from employees
where job_id in('SA_REP','ST_CLERK') and salary not in (2500,3500,7000);
题目33:请查询奖金系数是20%的员工名、薪水和奖金系数
1. select last_name,salary,commission_pct from employees where
commission_pct = 0.2;
题目34:查询部门30中的所有员工信息
select empno,ename,job,mgr,hiredate,sal,comm,deptno from emp where deptno=30;
题目35:列出所有办事员(CLERK)的姓名,编号和部门编号
select ename,empno,deptno from emp where job='CLERK';
题目36:找出佣金高于薪金的员工信息
select empno,ename,job,mgr,hiredate,sal,comm,deptno from emp where nvl(comm,0)>sal;
题目37:找出佣金高于薪金的60%的员工信息
select empno,ename,job,mgr,hiredate,sal,comm,deptno from emp where nvl(comm,0)>sal*0.6;
题目38:找出部门10中所有经理(MANAGER)和部门20中所有办事员(CLERK)的详细资料
select empno,ename,job,mgr,hiredate,sal,comm,deptno from emp
where (deptno=10 and job='MANAGER')or(deptno=20 and job='CLERK');
题目39:找出部门10中所有经理(MANAGER),部门20中的所有办事员(CLERK), 既不是经理又不是办事员但薪金大于或等于2000的所有员工的详细资料
select empno,ename,job,mgr,hiredate,sal,comm,deptno from emp
where (deptno=10 and job='MANAGER')or(deptno=20 and job='CLERK')
or((job not in('MANAGER','CLERK'))and sal>=2000 );
题目40:找出收取佣金的员工的不同工作
select distinct job from emp where comm is not null or comm=0;
题目41:找出不收取佣金或收取佣金低于100的员工信息
select empno,ename,job,mgr,hiredate,sal,comm,deptno from emp where nvl(comm,0)<100 or comm is null;
题目42:找出各月倒数第3天受雇的所有员工信息
select empno,ename,job,mgr,hiredate,sal,comm,deptno from emp where last_day(hiredate)-2=hiredate;
题目43:找出早于12年前受雇的员工信息
select empno,ename,job,mgr,hiredate,sal,comm,deptno 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 not like '%R%' ;
题目47:显示所有员工的姓名的前三个字符
select substr(ename,0,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 empno,ename,job,mgr,hiredate,sal,comm,deptno 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,year;
题目54:显示在一个月为30天的情况,所有员工的名字和日薪金,忽略余数
select ename,trunc(sal/30) from emp;
题目55:找出在(任何年份的)2月受雇的所有员工信息
select empno,ename,job,mgr,hiredate,sal,comm,deptno from emp where to_char(hiredate,'mm')=2;
题目56:对于每个员工,显示名字和其加入公司的天数,保留2位小数(四舍五入)
select ename,round(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 last_name, salary, trunc(salary * 1.155) "new salary" from employees;
题目60 :显示员工加薪15.5%(取整)之后员工的名字、原薪水和增加的薪水。(不允许做update操作,trunc取整)
Select last_name, salary, trunc(salary * 0.155) "new salary" from employees;
题目61 :显示所有以’J’,‘A’,'M’打头的员工的名字和名字长度,且按照名字排升序
select last_name, LENGTH(last_name) from employees
where substr(last_name, 0, 1) in ('J', 'A', 'M') order by last_name asc;
题目62 :请查询员工名和工作时间(换算成月并取整),并按工作时间排降序
Select last_name, trunc(months_between(sysdate, hire_date), 0) from employees
order by trunc(months_between(sysdate, hire_date), 0) desc;
题目63 :请查询员工的名字和薪水,并将薪水列变成15个字符长度,左边填充“$”符号
select last_name, lpad(salary, 15, '$') from employees;
题目64 :请查询部门id为90的所有员工的名字和他们参加工作的星期数(保留2位小数,不需要四舍五入)使用
select last_name, trunc((sysdate - hire_date) / 7, 2) from employees where department_id = 90;
题目65 :创建报告,显示员工名和奖金系数,如果奖金系数为空,则显示$无奖金
select last_name, decode(commission_pct, '', '无奖金', commission_pct) from employees;
题目66 :请使用case语句,查询员工的job_id和级别.例如:
Select job_id,
case job_id
when 'AD_PRES' then
'A'
when 'ST_MAN' then
'B'
when 'IT_PROG' then
'C'
when 'SA_REP' then
'D'
when 'ST_CLERK' then
'E'
else
'0'
end "Grage"
from employees;
题目67 :请使用decode语句,查询员工的job_id和级别.例如:
select t.job_id,
decode(t.job_id,
'AD_PRES',
'A',
'ST_MAN',
'B',
'IT_PROG',
'C',
'SA_REP',
'D',
'ST_CLERK',
'E',
'0')
from employees t;
题目69:查询emp和dept表,产生笛卡尔积(多表行相乘)
select e.empno,e.ename,e.job,e.mgr,e.hiredate,e.sal,e.comm,e.deptno ,d.deptno,d.dname,d.loc
from emp e,dept d;
题目70:加where条件过滤查询emp和dept表产生的笛卡尔积
select e.empno,e.ename,e.job,e.mgr,e.hiredate,e.sal,e.comm,e.deptno ,d.deptno,d.dname,d.loc
from emp e,dept d where e.deptno=d.deptno;
题目71:查询emp和dept表,产生笛卡尔积(多表行相乘),并为表取别名
select e.empno,e.ename,e.job,e.mgr,e.hiredate,e.sal,e.comm,e.deptno ,d.deptno,d.dname,d.loc
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 e1.ename,e1.job,e2.ename from emp e1,emp e2 where e1.mgr=e2.empno(+);
题目74:查询雇员姓名,工作,领导姓名及部门名称
select e1.ename,e1.job,e2.ename,d.dname from emp e1,emp e2,dept d where e1.mgr=e2.empno(+) and e1.deptno = d.deptno;
题目75:查询雇员姓名,工作,工资及工资等级
select e.ename 姓名,e.job 工作,e.sal 工资,s.grade 工资等级
from emp e,salgrade s
where e.sal between s.losal and s.hisal ;
题目76:查询雇员姓名,工作,工资及工资等级,要求工资等级显示为A B C D E
select e.ename,e.job,e.sal,decode(s.grade,1,'A',2,'B',3,'C',4,'D','E') from emp e,salgrade s
where e.sal between s.losal and s.hisal
题目77:查询雇员姓名,年薪,按年薪从高到低排序
select ename,(sal+nvl(comm,0))*12 y from emp order by y desc;
题目78:查询每个部门中工资最高的雇员姓名,工作,工资,部门名称,最后按工资从高到低排序
select e.ename,e.job,e.sal,d.dname from emp e,dept d,
(select deptno, max(sal) maxsal from emp group by deptno) m
where e.deptno=d.deptno and e.sal=maxsal and e.deptno=m.deptno order by maxsal desc
还可以这样写
SELECT e.ename,e.job,e.sal,d.deptno from emp e
RIGHT JOIN dept d on e.deptno=d.deptno
LEFT JOIN (select deptno, max(sal) maxsal from emp group by deptno) m
on m.deptno =d.deptno
WHERE e.sal=m.maxsal
题目79:查询每个部门的部门编号和雇员数量
select dept.deptno,count(empno) from emp,dept where emp.deptno(+) =dept.deptno group by dept.deptno
方法二.
select d.deptno,d.dname,NVL(COUNT(e.empno), 0) from emp e
right JOIN dept d on e.deptno=d.deptno
GROUP BY d.deptno,d.dname
注:需要用右连接 否则信息不完整
题目80:求出每个部门的部门名和平均工资(保留2位小数,截断)
select d.dname, nvl(trunc(avg(e.sal), 2), 0) from emp e, dept d where e.deptno(+) = d.deptno group by d.dname
题目81:按部门分组,并显示部门的名称,以及每个部门的员工数
select d.dname, nvl(count(e.empno), 0) from emp e, dept d where e.deptno(+) = d.deptno group by d.deptno, d.dname
题目82:要求显示平均工资大于2000的部门编号和平均工资(保留2位小数,截断)
select deptno,trunc(avg(sal),2) salary from emp group by deptno having avg(sal)> 2000;
题目83**:显示非销售人员工作名称以及从事同一工作雇员的月工资的总和,并且要满足从事同一工作的雇员的月工资大于$1500,输出结果按月工资的合计升序排列**
select emp_temp.job, sum(emp_temp.sal) sum_sal from (
select job,sal from emp where lower(job)<>'salesman') emp_temp
where emp_temp.sal>1500 group by emp_temp.job order by sum_sal asc;
方法二.
select job, SUM(sal) sumsal from(select job,sal from emp where lower(job)<>'salesman' and sal>1500
)
GROUP BY job
ORDER BY sumsal asc
题目84:求出平均工资最高的部门名称
select dname from(
select d.dname,avg(e.sal) sal from emp e,dept d
where e.deptno = d.deptno group by d.dname)where sal = (select max(sal) from
(select d.dname,avg(e.sal) sal from emp e,dept d
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 ename, job ,sal from emp where sal in ( select min(sal) from emp group by deptno );
题目89:要求查询出部门名称,部门的员工数,部门的平均工资,部门的最低收入雇员姓名
select d.dname, nvl(cou, 0), nvl(avgsal, 0), nvl(e1.ename, null)
from dept d
left join (select deptno,
avg(nvl(sal, 0)) avgsal,
min(sal) minsal,
count(empno) cou
from emp
group by deptno) e
on d.deptno = e.deptno
left join emp e1
on e1.sal = e.minsal
题目90:查询部门人数大于2的部门
select d.deptno,d.dname,count(d.deptno) as num
from emp e
left join dept d on e.deptno=d.deptno
GROUP BY d.deptno,d.dname HAVING count(d.deptno)>2