SQL-基础到高级练习

– （1） 查询10号部门的所有员工信息。
select * from emp where deptno =10
– （2） 查询所有职位编号为“SAlESMAN”的员工的员工号、员工名和部门号。
select empno,ename,deptno from emp where job =’SAlESMAN’
– （3） 查询每个员工的员工号、工资、奖金以及工资与奖金的和。
select empno,sal,comm ,sal+ifnull(comm,0) from emp;
– （4） 查询10号部门中职位编号为“ANALYST”和30号部门中职位编号为“clerk”的员工的信息。
select * from emp where (deptno=10 and job =’ANALYST’) or (deptno=30 and job =’clerk’ )
– （5） 查询所有职位名称不是“manager”且工资大于或等于2000的员工的详细信息。

      select * from emp where job !='manager' and sal>=2000 

– （6） 查询有奖金的员工的不同职位和名称。
select job ,ename,comm from emp where ifnull(comm,0)!=0;

  select job ,ename,comm from emp where comm is not null;

– 查询没有奖金或奖金低于100元的员工信息。
select * from emp where comm is null or comm <100;
– 7） 查询员工名（）中不包含字母“S”的员工。
select * from emp where ename not like ‘%S%’;
– （8） 查询员工的姓名和入职日期，并按入职日期从先到后进行排序。
select ename,hiredate from emp order by hiredate asc;

– （9） 显示所有员工的姓名、职位、工资和奖金，按职位降序排序，
– 若职位相同则按工资升序排序。

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

– （10） 查询所有员工的姓名及其直接上级的姓名。
select y.ename 员工名, l.ename 领导名 from emp y,emp l
where y.mgr = l.empno;
– 查询各个部门号、部门名称、部门所在地以及部门领导的姓名 MANAGER。
select * from emp order by deptno;
select * from dept ;
select p.deptno,dname,loc,ename
from emp p,dept d
where p.deptno = d.deptno
and job =’manager’;
– （11） 查询所有部门及其员工信息，包括那些没有员工的部门
select * from emp p RIGHT join dept d on p.deptno = d.deptno
– （12） 查询所有员工及其部门信息，包括那些还不属于任何部门的员工。
select * from emp p left join dept d on p.deptno = d.deptno

– （13） 查询所有员工的员工号、员工名、部门名称、职位名称、工资和奖金
select empno,ename,job,sal,comm ,dname from emp p left join dept d on p.deptno = d.deptno
– （14） 查询至少有一个员工的部门信息。
select * from dept;
select * from emp;

        select * from dept d 
        where d.deptno in (select DISTINCT e.deptno from emp e);
  -- （15）   查询工资比7902号员工工资高的所有员工信息
    select sal from emp where empno = '7902';
    select * from emp where sal > (select sal from emp where empno = '7902')
    -- （16） 查询工资高于公司平均工资的所有员工信息。
 select avg(sal) from emp ;  -- max min sum count avg 
  select * from emp where sal > (select avg(sal) from emp )
       -- （17）  查询各个部门中不同职位的最高工资
      select max(sal),job,deptno from emp group by deptno ,job;
    -- （18） 查询各个部门的人数及平均工资
         select count(*),avg(sal) from emp group by deptno;
    -- （19） 统计各个职位的员工人数与平均工资。
        select count(*),avg(sal) ,job from emp group by job;
   -- （20）  统计每个部门中各职位的人数与平均工资。
           select count(*),avg(sal) ,job from emp group by deptno,job;
       -- （21）  查询平均工资低于2000元的部门及其员工信息。
            select * from emp where deptno in (
            select deptno from  emp group by deptno 
            having avg(sal) <2000  )
       -- 查询在销售部工作的员工的姓名信息。
          select * from emp where deptno 
           =  (select deptno from dept where dname ='SALES');

 -- （22）    查询与7902号员工从事相同工作的所有员工信息
         select * from emp where job = (
               select job from emp where empno = 7902)
                    and empno !=7902;
 -- （23）    查询工资高于7902号部门中所有员工的员工姓名和工资。

                select * from emp 
                where sal >(select max(sal) from emp 
                where deptno =(select deptno from emp where empno = 7902)
                )
-- （24） 查询每个部门中的员工数量、平均工资
-- 五个聚合函数 sum count max min avg  5遍
             select deptno,count(*),avg(sal) from emp group by deptno; 
      -- （25）   查询人数最多的部门信息。
        select deptno,count(*) from emp group by deptno order by count(*) desc limit 0,1; 
   -- （26）  查询所有员工中工资排序在5~10名之间的员工信息
        select * from emp order by sal limit 4,5

– （27）查询员工信息，要求以首字母大写的方式显示所有员工名。
select concat (UPPER(substring(ename,1,1)) , LOWER(substring(ename,2,length(ename)-1))), ename from emp;
– UPPER 转大写 lower 转小写
– （28） 查询员工名 正好为6个字符的员工的信息。
select * from emp where ename like ‘__’
– （29） 查询员工名（first_name）的第2个字母为“M”的员工信息。
select * from emp where ename like ‘_M%’
– （30） 查询所有员工名，如果包含字母“s”，则用“S”替换。
select ename,REPLACE(ename,’S’,’XX’) from emp where ename like ‘%s%’;
– （31） 查询在2月份入职的所有员工信息。
select * from emp where MONTHNAME(hiredate)= ‘February’;
– 找出每个月倒数第三天受雇的员工
select LAST_DAY(hiredate) from emp;
– 查询和部门10的工作相同的工作的雇员的名字，岗位，工资，部门号
select ename,job ,sal ,deptno
from emp
where job in (select job from emp where deptno = 10)
;

– 找出每个月倒数第三天受雇的员工（如：2009-5-29）
select * from emp where day(hiredate) = day(LAST_DAY(hiredate)) - 2
– 找出25年前雇的员工
select year(now()) - year(hiredate) from emp where year(now()) - year(hiredate) >=25
– 选择工资大于所有 JOB = ‘SALESMAN’的员工的工资的员工的 name, job, salary
select ename,job,sal from emp
where sal> all(

select sal from emp where job = 'SALESMAN')

– 所有员工名字前加上Dear ,并且名字首字母大写
select concat(‘Dear’,UPPER(SUBSTR(ename,1,1)), lower(SUBSTR(ename,2,length(ename)-1))) from emp;
– 返回比本部门平均工资高的员工的 last_name, department_id, salary 及平均工资
select deptno,avg(sal) from emp group by deptno; – 子查询 将查询结果作为表
select * from emp order by deptno;
select ename,p.deptno,sal,d.asal from emp p ,(select deptno,avg(sal) asal from emp group by deptno) d
where p.deptno = d.deptno
and p.sal > d.asal;

– 查询员工的 empno,ename, 要求按照员工的 dname 排序
select * from dept order by dname ;
– case when then else end : 相当于 多重if.
– – deptno=1的loc显示中国 其他显示外国
select loc,(
CASE
when deptno=1 then ‘中国’
when deptno=10 then ‘日本’
else ‘外国’
END
) from dept;

 select empno,ename,d.dname from emp p, (select * from dept order by dname) d
    where p.deptno = d.deptno;

– – sal <2K 发3倍 2-3之间 1.3倍 3K以上原本发 ENAME,sal
select ename,sal,(
CASE
when sal<=2000 then sal*3
when sal>2000 and sal<=3000 then sal*1.3
else sal
END
)当月工资
from emp ;