编辑代码

CREATE DATABASE test;
use test;

CREATE TABLE emp (
   EMPNO int primary key,
   ename varchar(255),
   job varchar(255),
   mqr int,
   hiredate varchar(255),
   sal int,
   comm int,
   deptno int

);

INSERT INTO emp VALUES (7369, 'SMITH',  'CLERK', 7902 ,'1981.12.17' ,800 ,null ,20 );
INSERT INTO emp VALUES (7499, 'ALLEN',  'SALESMAN', 7698 ,'1981.12.17' ,1600,300 ,30 );
INSERT INTO emp VALUES (7521, 'WARD ',  'SALESMAN', 7698 ,'1981.12.17' ,1250 ,500 ,30 );
INSERT INTO emp VALUES (7566, 'JONES',  'MANAGER', 7839 ,'1981.12.17' ,2975,null ,20 );
INSERT INTO emp VALUES (7654, 'MARTIN',  'SALESMAN', 7698 ,'1981.12.17' ,1250 ,1400 ,30 );
INSERT INTO emp VALUES (7698, 'BLAKE',  'MANAGER', 7839 ,'1981.12.17' ,2850,null ,30 );
INSERT INTO emp VALUES (7782, 'CLARK',  'MANAGER', 7839 ,'1981.12.17' ,2450,null ,10 );
INSERT INTO emp VALUES (7788, 'SCOTT',  'ANALYST', 7566 ,'1981.12.17' ,3000,null ,20 );
INSERT INTO emp VALUES (7839, 'KING ',  'PRESIDENT', null ,'1981.12.17' ,5000,null ,10 );
INSERT INTO emp VALUES (7844, 'TURNER',  'SALESMAN', 7698 ,'1981.12.17' ,1500 ,0 ,30 );
INSERT INTO emp VALUES (7876, 'ADAMS',  'CLERK', 7788 ,'1981.12.17' ,1100 ,null ,20 );
INSERT INTO emp VALUES (7900, 'JAMES',  'CLERK', 7698 ,'1981.12.17' ,950,null ,30 );
INSERT INTO emp VALUES (7902, 'FORD ',  'ANALYST', 7566 ,'1981.12.17' ,3000 ,null ,20 );
INSERT INTO emp VALUES (7934, 'MILLER',  'CLERK', 7782 ,'1981.12.17' ,1300 ,null ,10 );

CREATE TABLE dept(
    deptno int primary key,
    dname varchar(255),
    loc varchar(255)
);

INSERT INTO dept VALUES(10, 'ACCOUNTING' , 'NEW YORK' );
INSERT INTO dept VALUES(20, 'RESEARCH   ', 'DALLAS' );
INSERT INTO dept VALUES(30, 'SALES     ' , 'CHICAGO' );
INSERT INTO dept VALUES(40, 'OPERATIONS' , 'BOSTON' );
INSERT INTO dept VALUES(50, 'advertisement' , 'beijing' );

CREATE TABLE SALGRADE(
    GARDE int primary key,
    LOSAL int,
    HISAL int
);
INSERT INTO SALGRADE VALUES(1, 700 , 1200  );
INSERT INTO SALGRADE VALUES(2, 1201 , 1400 );
INSERT INTO SALGRADE VALUES(3, 1401 , 2000 );
INSERT INTO SALGRADE VALUES(4, 2001 , 3000 );
INSERT INTO SALGRADE VALUES(5, 3001 , 9999 );


--  求出每个员工的姓名 部门编号 薪水 和 薪水的等级

--      select  "E".ename,"E".sal,"S".GARDE ,"E".deptno
--          from emp "E"
--           join SALGRADE "S"
--           on "E".sal >= "S".LOSAL and "E".sal<= "S".HISAL



--  查找每个部门的编号 该部门所有员工的平均工资 平均工资的等级

--      select "A".deptno, "A"."avg_sal" , "S".GARDE
--           from (
--               select deptno, avg(sal) as "avg_sal"
--                    from emp
--                  group by deptno
--                ) "A"
--                join SALGRADE "S" 
--                on "A".avg_sal between "S".LOSAL and "S".HISAL



--  查找每个部门的编号 部门名称 该部门所有员工的平均工资 平均工资的等级

--      select "A".deptno,"D".dname, "A"."avg_sal" , "S".GARDE
--           from (
--               select deptno, avg(sal) as "avg_sal"
--                    from emp
--                  group by deptno
--                ) "A"
--                join SALGRADE "S" 
--                on "A".avg_sal between "S".LOSAL and "S".HISAL
--                    join dept "D"
--                    on "D".deptno = "A".deptno

--  求出emp表中所有领导的信息 

--      select * from emp
--         where EMPNO in (select mqr from emp)

--  求出emp表中所非领导的信息    null 不能参与运算 需要改正
--      select * from emp
--         where EMPNO not in isnull((select mqr from emp),0)


--  求出平均薪水最高的部门的编号和部门的平均工资

--  select deptno "部门的编号",avg(sal)"平均工资"
--	  from emp
--	  group by deptno
--  	order by avg(sal) desc

--  第二种
--        select  "D".deptno,"A"."avg_sal"
--            from(
--                select deptno,avg(sal) as "avg_sal"
--                    from emp
--                    group by deptno
--                ) "A"
--                join dept "D"
--                on "A".deptno = "D".deptno
--                order by  "A"."avg_sal" desc

-- 有一个人工资最低 把这个人排除掉
-- 剩下的人中工资最低的前3个人的姓名 工资 部门编号 部门名称 工资等级 输出	
 select "E".ename,"E".sal,"E".deptno,"D".dname,"S".GARDE
    from (select *  from emp
            where sal>(select min(sal) from emp )
         ) "E"
         join dept "D"
         on "E".deptno = "D".deptno
         join SALGRADE "S"
         on "E".sal between "S".LOSAL and "S".HISAL
         order by "E".sal 
         LIMIT 3