Mysql练习题

第一天

1．列出至少有一个员工的所有部门。

select dname  from dept where deptno in(select deptno from emp);
或者
select distinct d.dname from emp e ,dept d where e.deptno = d.deptno;

2．列出薪金比“SMITH”多的所有员工。

 select ename from emp where sal >all(select sal from emp where ename = 'SMITH');

3．列出所有员工的姓名及其直接上级的姓名。

select w.ename,m.ename from emp w left outer join emp m on(w.mgr = m.empno);

4．列出受雇日期早于其直接上级的所有员工。

select e.ename,e.hiredate from emp e where 
e.hiredate < (select m.hiredate from emp m where m.empno = e.mgr);

5．列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门

select d.dname,e.* from dept d left outer join emp e on(e.deptno = d.deptno);

6．列出所有“CLERK”（办事员）的姓名及其部门名称。

select d.dname,e.ename from dept d,emp e where d.deptno = e.deptno and 
job = 'CLERK';

7．列出最低薪金大于1500的各种工作。

select  job from emp group by job having min(sal) > 1500;

8．列出在部门“SALES”（销售部）工作的员工的姓名，假定不知道销售部的部门编号。

select ename from emp where deptno =(select deptno from dept where dname = 'SALES');

9．列出薪金高于公司平均薪金的所有员工。

select ename from emp where sal > (select avg(sal) from emp);

10．列出与“SCOTT”从事相同工作的所有员工。

select ename from emp where job = (select job from emp where ename = 'SCOTT');

11．列出薪金等于部门30中员工的薪金的所有员工的姓名和薪金。

select a.ename,a.sal from emp a where
 a.sal in (select b.sal from emp b where b.deptno = 30) and a.deptno<>30;

12．列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金。

select ename,sal from emp a where sal > (select max(sal) from emp  where deptno = 30);

13．列出在每个部门工作的员工数量、平均工资和部门名称

select count(empno),avg(sal),d.dname from emp ,dept d where 
emp.deptno = d.deptno group by d.deptno;

14．列出所有员工的姓名、部门名称和工资。

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

15．列出所有部门的详细信息和部门人数。

select d.*,count(empno) from emp e right outer join dept d  on (e.deptno = d.deptno) 
group by deptno;

16．列出各种工作的最低工资。

select job ,min(sal) from emp group by job;

17．列出各个部门的MANAGER（经理）的最低薪金。

select ename,min(sal) from emp  where job = 'MANAGER' group by deptno,ename;

18．列出所有员工的年工资,按年薪从低到高排序。

select ename,(sal + ifnull(comm,0))*12 as sal from emp order by sal desc;

第二天

找出EMP表中的姓名（ENAME）第三个字母是A 的员工姓名。

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

找出EMP表员工名字中含有A 和N的员工姓名。

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

找出所有有佣金的员工，列出姓名、工资、佣金，显示结果按工资从小到大，佣金从大到小。

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

列出部门编号为20的所有职位。

 select job from emp where deptno = 20;

列出不属于SALES 的部门。

select dname from dept where dname <> 'SALES';

显示工资不在1000 到1500 之间的员工信息：名字、工资，按工资从大到小排序。

select ename,sal from emp where sal not between 1000 and 1500 order by sal desc;

显示职位为MANAGER 和SALESMAN，年薪在15000 和20000 之间的员工的信息：名字、职位、年薪。（用between...and 求区间时候，不能用别名）

select ename, job , (sal + ifnull(comm,0))*12 yearsal from emp where 
job in('MANAGER','SALESMAN') and (sal + ifnull(comm,0))*12 between 15000 and 20000;

说明以下两条SQL语句的输出结果：
SELECT EMPNO,COMM FROM EMP WHERE COMM IS NULL;
SELECT EMPNO,COMM FROM EMP WHERE COMM = NULL;

第一条输出为

第二条输出为： Empty set
说明：说明：IS NULL是判断某个字段是否为空，为空并不等价于为空字符串或为数字0；
--而 =NULL 是判断某个值是否等于 NULL，NULL = NULL和NULL <> NULL都为 FALSE。

让SELECT 语句的输出结果为
SELECT * FROM SALGRADE;
SELECT * FROM BONUS;
SELECT * FROM EMP;
SELECT * FROM DEPT;
……
列出当前用户有多少张数据表，结果集中存在多少条记录。

判断SELECT ENAME,SAL FROM EMP WHERE SAL > '1500'是否抱错，为什么？
不会报错，这儿存在隐式数据类型。

第三天

让SELECT TO_CHAR(SALARY,'L99,999.99') FROM HR.EMPLOYEES WHERE ROWNUM < 5 输出结果的货币单位是￥和$。

列出前五位每个员工的名字，工资、涨薪后的的工资（涨幅为8%），以“元”为单位进行四舍五入。

 select ename,sal,round(sal*1.08) totalsal from emp limit 5;

找出谁是最高领导，将名字按大写形式显示。

 select upper(ename) from emp where mgr is null;

找出SMITH的直接领导名字。

select ename from emp where empno = (select mgr from emp where ename = 'SMITH' );

KING领导谁。

select ename from emp where mgr = (select empno from emp where ename = 'KING');

哪些员工的工资高于他直接上司的工资，列出员工的名字和工资，上司的名字和工资。

select w.ename 员工名字,w.sal 员工工资,m.ename 经理名字,m.sal 经理工资 from emp w,emp m where w.mgr = m.empno and w.sal > m.sal;

哪些员工和SMITH同部门。

 select ename from emp where deptno = 
(select deptno from emp where ename ='SMITH') and ename <> 'SMITH';

哪些员工跟SMITH做一样职位。

 select ename,job from emp where job in(select job from emp where ename = 'SMITH');

哪些员工跟SMITH不在同一个部门。

select ename from emp where deptno <>(select deptno from emp where ename = 'SMITH');

哪些员工跟SMITH做不一样的职位。

select ename,job from emp where job not in(select job from emp where ename ='SMITH');

显示有提成的员工的信息：名字、提成、所在部门名称、所在地区的名称。

select e.ename,e.comm,d.dname,d.loc from emp e,dept d where e.deptno = d.deptno and  comm >0;

显示SALES部门有哪些职位。

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

整个公司中，最高工资和最低工资相差多少。

select max(sal)-min(sal) from emp ;

提成大于0 的人数。

select count(empno) from emp where comm is not null ;

显示整个公司的最高工资、最低工资、工资总和、平均工资保留到整数位。

select max(sal),min(sal),sum(sal),round(avg(sal) )from emp ;

整个公司有多少个领导。

select count(empno) from emp where empno in(select mgr from emp );
或者
select count(distinct mgr) from emp;

列出在同一部门入职日期晚但工资高于其他同事的员工：名字、工资、入职日期。

select a.ename,a.sal,a.hiredate from emp a where a.deptno in(select b.deptno 
from emp b where a.deptno = b.deptno and  a.sal > b.sal and a.hiredate > b.hiredate);

第四天

各个部门平均、最大、最小工资、人数，按照部门号升序排列。

select avg(sal),max(sal),min(sal),count(empno)
 from emp group by deptno order by deptno;

各个部门中工资大于5000的员工人数。

select count(empno) from emp where sal > 4000 group by deptno ;

各个部门平均工资和人数，按照部门名字升序排列。

select d.dname,avg(sal),count(empno) from 
emp e right outer join dept d on e.deptno = d.deptno
 group by d.deptno,d.dname order by d.dname;

列出每个部门中有同样工资的员工的统计信息，列出他们的部门号，工资，人数。

select e.deptno,e.sal,count(e.empno) from emp e left outer join emp d on e.deptno = d.deptno and 
e.sal = d.sal group by e.deptno,e.sal;

列出同部门中工资高于1000 的员工数量超过2 人的部门，显示部门名字、地区名称。

select d.dname,d.loc,count(e.empno) from dept d ,emp e where e.deptno = d.deptno and 
e.sal > 1000 group by e.deptno,d.dname,d.loc having  count(e.empno) > 2 ;

哪些员工的工资，高于整个公司的平均工资，列出员工的名字和工资（降序）。

select ename,sal from emp where sal > (select avg(sal) from emp);

哪些员工的工资，介于10号和30号部门平均工资之间。

select ename,sal from emp where sal between (select avg(sal) 
from emp group by deptno having deptno = 30)and (select avg(sal) from emp
 group by deptno having deptno = 20);

所在部门平均工资高于2000 的员工名字。

select ename from emp where deptno in 
(select deptno from emp group by deptno having avg(sal) > 2000);

列出各个部门中工资最高的员工的信息：名字、部门号、工资。（要查询的信息必须和子查询中的信息排列顺序一致）

select ename,deptno,sal from emp where (deptno,sal) 
in(select deptno,max(sal) from emp group by deptno);

最高的部门平均工资是多少。

select max(deptsal.avgsal) from (select avg(sal)avgsal from emp group by deptno) as deptsal;

第五天

哪些部门的人数比90 号部门的人数多。

select deptno,count(empno) from emp group by deptno having count(empno)
    >(select count(empno) from emp group by deptno having deptno = 20);

JONES的领导是谁（非关联子查询）。

select ename from emp where emp.empno = (select mgr from emp where ename = 'JONES');

3.JONES领导谁（非关联子查询）。

select ename from emp where mgr = (select empno from emp where ename = 'JONES');

JONES 的领导是谁（关联子查询）。

select a.ename from emp a where exists
 (select 1 from emp b where a.empno = b.mgr and b.ename = 'JONES');

Den(FIRST_NAME)、Raphaely(LAST_NAME) 领导谁（关联子查询）。

select ename from emp where emp.mgr = (select empno from emp where ename = 'JONES');

列出在同一部门共事，入职日期晚但工资高于其他同事的员工：名字、工资、入职日期
（关联子查询）。

select a.ename,a.sal,a.hiredate from emp a ,emp b where 
a.deptno = b.deptno and a.hiredate > b.hiredate and a.sal > b.sal;

哪些员工跟SMITH不在同一个部门（非关联子查询）。

select ename from emp where deptno <> (select deptno from emp where ename = 'Smith');

哪些员工跟Smith不在同一个部门（关联子查询）。

select a.ename from emp a where not exists
(select 1 from emp b where a.deptno = b.deptno and b.ename = 'Smith');

Finance部门有哪些职位（非关联子查询）。

select e.job ,d.dname from emp e ,dept d where e.deptno = d.deptno and d.dname = 'SALES';

SALES部门有哪些职位（关联子查询）。

select a.job from emp a where exists 
(select 1 from dept d where a.deptno = d.deptno and dname = 'SALES');

关联子查询和非关联子查询的区别

一,非关联子查询

先执行子查询,也就是内部查询;再执行外部查询

二,关联子查询

外部查询返回的第一行数据进入内部查询
内部查询接收到外部的第一条数据后,调出内部查询的数据,并根据条件进行筛选,然后返回内部select的值
外部根据条件看这个值是否要返回给外部select;
(此时外部查询返回的第一行数据处理完)
按顺序,外部查询返回的第二行数据进入内部查询
…
返回给外部select;
循环执行,直到外部查询的所有数据全部处理完

33..查询所有大于本部门平均工资的员工信息。

select * from emp e where sal > (select avg(sal) from emp 
where e.deptno = deptno group by deptno);

34.列出至少有三个员工的所有部门和部门信息。

select d.* from emp e ,dept d where e.deptno = d.deptno 
group by e.deptno,d.dname,d.loc having count(e.empno) >= 3;

数据操作与事务控制

1.有信员工信息如下：empno=200、ENAME=张三、JOB=软件工程师、HIREDATE=2008年4月16日、DEPTNO=10、SAL=3000，请将此员工信息插入EMP表中。

INSERT INTO emp1 (
   empno,
   ename,
   job,
   hiredate,
   deptno,
   sal
)
VALUES
   (
       200,
       '张三',
       '软件工程师 ',
       '2008-04-16 ',
       10,
       3000
   );

2.修改EMP表中的数据，为工资小于2000元的员工加500元工资。

update emp1 set sal = sal + 200 where sal < 2000;

3.修改薪资小于2000元的员工的入职日期为当日。

update emp1 set hiredate = SYSDATE() where sal < 2000;

4.删除所有入职日期小于2007年1月1日的员工信息。

 delete from emp1 where  hiredate < '2007-01-01';

5.删除所有薪资等于员工平均薪资的员工信息。

DELETE
FROM
    emp
WHERE
    empno IN (
        SELECT
            *
        FROM
            (
                SELECT
                    a.empno
                FROM
                    emp AS a,
                    (
                        SELECT
                            deptno,
                            AVG(sal) AS avgsal
                        FROM
                            emp
                        GROUP BY
                            deptno
                    ) AS b
                WHERE
                    a.deptno = b.deptno
                AND a.sal = b.avgsal
            ) AS QUERY
    )