SQL 数据库语句练习

题目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

​