基础的sql练习,全都理解你就是高手了!

以下sql我都是亲测:大多数用法都会在面试当中被问到,切记一步一个脚印的去实现,结果不重要,重要的是你的实现过程的想法,第一步做什么然后第二步做什么等具体的详细过程!(学东西不能贪多,慢慢来)
先从单表查询开始——>多表查询->复杂查询->嵌套查询等等;
01.查询每个雇员的编号、姓名、职位。

  select e.empno as "雇员编号",e.ename as "雇员姓名",e.job as "雇员职位" from emp e

02.查询每个雇员的职位,职位。

  select e.job as "雇员职位" ,e.* from emp e;

03.查询每个雇员的职位,使用DISTINCT消除掉显示的重复行记录。

  select DISTINCT e.job as "雇员职位",e.* from emp e;

04.计算出每个雇员的基本年薪,同时查询出雇员的编号、姓名。

  select e.empno as "雇员编号",e.ename as "雇员姓名",(e.sal+IFNULL(comm,0)*12) as "基本年薪" from emp e

05.每个雇员每个月公司会补贴饭食200元,交通补助300元,计算年薪(年薪=(工资+奖金)*12)。

  select e.empno as "雇员编号",e.ename as "雇员姓名",(e.sal+IFNULL(comm,0)+200+300)*12 as "基本年薪" from emp e;

06.查询基本工资高于2000的全部雇员信息。

  select e.* from emp e where e.sal>2000;

07.查询出smith的信息。

  select e.* from emp e where e.ename="SMITH";

08.查询出所有不是CLERK的详细信息。

  select e.* from emp e where e.job != "CLERK";

  select e.* from emp e where e.job <> "CLERK";

  select e.* from emp e WHERE e.job not in("CLERK");

09.查询出所有销售人员(SALESMAN)的基本信息,并且要求销售人员的工资高于1300。

  select e.* from emp e where e.job = "SALESMAN" && e.sal>1300;

  select e.* from emp e where e.job in ("SALESMAN") and e.sal>1300;

10.查询出工资范围在1500~3000之间的全部雇员信息(包含1500和3000)。

  select e.* from emp e where e.sal>=1500 and e.sal<= 3000;

  select e.* from emp e where e.sal BETWEEN 1500 and 3000;

11.查询出所有经理或者是销售人员的信息,并且要求这些人的基本工资高于1500

  select e.* from emp e WHERE (e.job=‘SALESMAN‘ or e.job=‘manager‘) and e.sal>1500;

12.要求查询出所有在1981年雇佣的雇员信息。

  select * from emp e where e.HIREDATE between(‘1981-1-01‘) and(‘1981-12-31‘);

  select * from emp e where e.hiredate >= ‘1981-1-01‘ and e.hiredate <= ‘1981-12-31‘;

13.查询所有领取奖金的雇员信息(comm不为空)

  select * from emp e where e.comm is not null;

  select * from emp e where e.comm="";

14.查询所有领取奖金高于100的雇员信息

  select e.* from emp e WHERE e.comm > 100;

15.查询出雇员编号是7369、7566、9999的雇员信息。

  select e.* from emp e where e.empno in (7369,7566,9999);

16.查询出所有雇员姓名是以A开头的全部雇员信息。

  select * from emp e where e.ename like "A%";

17.查询出雇员姓名第二个字母是M的全部雇员信息。

  select * from emp e where SUBSTR(e.ename,2,1)=‘m‘;

  select * from emp e where e.ename like "%_m";

18.查询出雇员姓名任意位置上包含字母A的全部雇员信息。

  select * from emp e WHERE e.ename like "%a%";

  select * from emp e where regexp_like(first_name,‘[,a]+‘);(正则表达式)

19.查询出所有雇员的信息,要求按照工资排序。

select * from emp e order by e.sal asc;(默认是asc,是升序)

select * from emp e order by e.sal desc;(desc,是降序)

20.要求查询所有雇员的信息,按照雇佣日期由先后排序。
  select * from emp e order by e.HIREDATE asc;
  select * from emp e order by e.HIREDATE
21.查询全部雇员信息,按照工资由高到低排序,如果工资相同,则按照雇佣日期由先后排序。

  select * from emp e order by e.sal desc,e.hiredate asc;

22.查询部门30中的所有员工。
  select * from emp e where e.deptno = 30;
23.查询出所有办事员(CLERK)的姓名,编号和部门编号。
  select * from emp e where e.job = "CLERK";
24.查询出奖金高于薪金的员工。
  select * from emp e where e.comm > e.sal;
25.查询出奖金高于薪金的60%的员工。
  select * from emp e where e.comm > 0.6*e.sal
26.查询出部门10中所有经理(MANAGER)和部门20中所有办事员(CLERK)的详细资料。

  (select * from emp e where e.job=‘manager‘ and e.DEPTNO=10 )union all(SELECT * from emp b where b.job=‘clerk‘ and b.DEPTNO=20);

        select * from emp e where e.deptno = 10 and e.job = ‘Manager‘ union select * from emp e where e.deptno = 20 and e.job = ‘clerk‘;

27.查询出部门10中所有经理,部门20中所有办事员,既不是经理又不是办事员但其薪金大于或等于2000的所有员工的信息。

  (select * from emp e where e.job!=‘manager‘ and e.DEPTNO=10 and e.SAL>=2000 and e.job != ‘clerk‘ )union (select * from emp b where b.job!=‘clerk‘ and b.job!= ‘manager‘and b.DEPTNO=20 and b.sal>=2000);

28.查询出收取奖金的员工的不同工作。

  select e.job,e.* from emp e where e.comm !="";
  select e.job,e.* from emp e where e.comm is not null;
29.查询出不收取奖金或收取的奖金低于100的员工。
 
30.查询出不带有“R”的员工的姓名。
  select * from emp e where e.ename not like "%R%";
       select * from emp e where not regexp_like(first_name,‘[,r]+‘);
31.查询出每个雇员的姓名、职位、领导姓名。
  select a.ename "雇员的姓名", a.job "雇员的职位", b.ename "雇员领导姓名" FROM emp a,emp b where a.mgr = b.empno;
32.查询出所有员工的编号、姓名及其直接上级的编号、姓名,显示的结果按领导年工资的降序排列。 

  select a.empno as "员工编号",a.ename as "员工姓名" , b.empno"上司编号",b.ename as "上司姓名", (B.SAL+IFNULL(B.COMM,0))*12 "领导年工资"
  from emp a,emp b
  where a.mgr= b.empno
  orderby by (b.sal+IFNULL(b.comm,0))*12 desc;

33.查询出在销售部(SALES)工作的员工姓名、基本工资、雇佣日期、部门名称。(不知道销售部编号)。

  select e.ename as "员工姓名",e.empno as "员工编号",e.sal as "员工薪资",e.hiredate as "雇佣日期",d.dname as "部门名称" from emp e,dept d where e.job="salesman" and e.deptno = d.deptno;

34.查询出所有员工的姓名、部门名称和工资。

  select emp.ename as "雇员姓名",dept.dname as "雇员职位",emp.sal as "雇员薪资" from emp,dept WHERE emp.deptno = dept.deptno;

35.查询出所有员工的年工资,所在部门名称,按年薪从低到高排序。

  select (e.sal+IFNULL(e.comm,0))*12 as allYearSales,d.dname as "部门名称",e.* from emp e,dept d where e.deptno = d.deptno order by allYearSales desc;

36.查询出某个员工的上级主管及所在部门名称,并要求出这些主管中的薪水超过3000。

  select distinct
  e2.ename,d.dname
  from emp e1,emp e2,dept d
  where e1.mgr = e2.empno && e2.deptno = d.deptno && e2.sal > 300;

37.查询出公司的最高和最低工资。

  select MAX(e.sal) as "最高工资",MIN(e.sal) as "最低工资" from emp e;

38.查询出每个部门的人数、平均工资,只显示部门编号。

  select deptno as "部门编号",count(e.deptno) as "部门员工数量",avg(e.sal) as "平均薪资" from emp e group by e.deptno;

39.查询出每种职位的最高和最低工资。

  SELECT job as "职位", MAX(sal) as "最高薪资",MIN(sal) as "最低薪资" FROM emp group by job;

40.查询平均工资高于2000的职位信息,以及从事此职位的雇员人数、平均工资。  

  select job as "职业",count(job) as "从事的职业数量",AVG(sal) as "平均薪资" from emp group by job having AVG(sal)>2000;

41查询出至少有一个员工的所有部门编号、名称,并统计出这些部门的平均工资、最低工资、最高工资。  

  select
  e.deptno as "部门编号", d.dname as "部门名称", AVG(sal) as "部门平均薪资",MIN(sal) as "最低薪资",MAX(sal) as "最高薪资"
  from
  emp e,dept d
  where
  e.deptno = d.deptno
  group by
  d.dname;

42.查询出部门名称和这些部门的员工信息(数量、平均工资),同时列出那些没有员工的部门。 

  select p.deptno as "部门编号",p.dname as "部门名称",p.loc as "部门地址",count(job)as "部门人数",IFNULL(AVG(e.sal),0) as "部门平均工资" from emp e
  right join dept p
  on e.deptno = p.deptno
  group by p.dname;   (右连接)

 
码字不易,如果对你的学习有帮助,请在下方打个赏,给我一点儿信心,谢谢啦!
基础的sql练习,全都理解你就是高手了!基础的sql练习,全都理解你就是高手了!
 

相关推荐