MySQL数据库综合练习二

 表信息：

-- 创建部门表
DROP TABLE IF EXISTS dept; 
CREATE TABLE dept(
    -- 部门编号
    deptno int PRIMARY KEY, 
    -- 部门名称
    dname VARCHAR(14), 
    -- 部门所在地
    loc VARCHAR(13)
  );

-- 向部门表插入数据
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');

-- 创建员工表
DROP TABLE IF EXISTS emp;
CREATE TABLE emp(
  -- 员工编号
  empno int PRIMARY KEY, 
  -- 员工姓名
  ename VARCHAR(10), 
  -- 工作岗位
  job VARCHAR(9), 
  -- 直属领导
  mgr int, 
  -- 入职时间
  hiredate DATE, 
  -- 工资
  sal double, 
  -- 奖金
  comm double, 
  -- 所属部门
  deptno int
);


-- 为员工表表添加外键约束
ALTER TABLE emp ADD CONSTRAINT FOREIGN KEY EMP(deptno) REFERENCES dept (deptno);

-- 向员工表插入数据
INSERT INTO emp VALUES(7369,'SMITH','CLERK',7902,"1980-12-17",800,NULL,20);
INSERT INTO emp VALUES(7499,'ALLEN','SALESMAN',7698,'1981-02-20',1600,300,30);
INSERT INTO emp VALUES(7521,'WARD','SALESMAN',7698,'1981-02-22',1250,500,30);
INSERT INTO emp VALUES(7566,'JONES','MANAGER',7839,'1981-04-02',2975,NULL,20);
INSERT INTO emp VALUES(7654,'MARTIN','SALESMAN',7698,'1981-09-28',1250,1400,30);
INSERT INTO emp VALUES(7698,'BLAKE','MANAGER',7839,'1981-05-01',2850,NULL,30);
INSERT INTO emp VALUES(7782,'CLARK','MANAGER',7839,'1981-06-09',2450,NULL,10);
INSERT INTO emp VALUES(7788,'SCOTT','ANALYST',7566,'1987-07-03',3000,NULL,20);
INSERT INTO emp VALUES(7839,'KING','PRESIDENT',NULL,'1981-11-17',5000,NULL,10);
INSERT INTO emp VALUES(7844,'TURNER','SALESMAN',7698,'1981-09-08',1500,0,30);
INSERT INTO emp VALUES(7876,'ADAMS','CLERK',7788,'1987-07-13',1100,NULL,20);
INSERT INTO emp VALUES(7900,'JAMES','CLERK',7698,'1981-12-03',950,NULL,30);
INSERT INTO emp VALUES(7902,'FORD','ANALYST',7566,'1981-12-03',3000,NULL,20);
INSERT INTO emp VALUES(7934,'MILLER','CLERK',7782,'1981-01-23',1300,NULL,10);


-- 创建工资等级表
DROP TABLE IF EXISTS salgrade;
CREATE TABLE salgrade(
  -- 等级
  grade int, 
  -- 最低工资
  losal double, 
  -- 最高工资
  hisal double
);

-- 向工资等级表插入数据
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);

练习题： 

-- 01、查询部门编号为30的部门的员工详细信息
select * from emp where deptno = 30

-- 02、查询从事clerk工作的员工的编号、姓名以及其部门号
select e.empno,e.ename,e.deptno from emp e where e.job = 'CLERK'

-- 03、查询奖金多于基本工资的员工的信息
select * from emp where  comm > sal

-- 奖金小于于基本工资的员工的信息
select * from emp where comm < sal or comm is null

-- 04、查询奖金多于基本工资60%的员工的信息
select * from emp where comm > (sal*0.6)

-- 05、查询部门编号为10的部门经理 (job=manager)   和部门编号为20的部门中工作为CLERK的职员信息
select * from emp where (deptno = 10 and job = 'manager') or (deptno = 20 and job = 'CLERK')


-- 06、查询部门编号为10的部门经理或部门编号为20的部门工
-- 作为CLERK的职员信息或者既不是经理也不是CLERK但是工资高于2000的员工信息
select * from emp where (deptno = 10 and job = 'manager') or (deptno = 20 and job = 'CLERK') or (job not in ('manager','CLERk') and sal > 2000)

-- 07、查询获得奖金的员工的信息(1、信息都在员工表中；获得奖金==comm is not null and comm>0)
select * from emp where comm is not null and comm > 0

-- 08、查询奖金少于100或者没有获得奖金的员工的信息
select * from emp where comm is null or comm<100

-- 09、查询姓名以A、B、S开头的员工的信息
select * from emp where ename like  'S%' or ename like 'A%' or ename like 'B%'

select * from emp where left(ename,1) in ('S','A','B')

-- 10、查询找到姓名长度为6个字符的员工的信息
select * from emp where ename like '______'

-- 11、查询姓名中不包含R字符的员工信息
select * from emp where ename not like '%R%'

-- 12、查询员工的详细信息并按姓名排序
select * from emp order by ename

-- 13、查询员工的信息并按工作降序工资升序排列
select * from emp order by job desc,sal asc

-- 14、计算员工的日薪(按30天计)
select (sal/30) '日薪' from emp 

-- 15、查询姓名中包含字符A的员工的信息
select * from emp where ename like '%A%'

-- 16、查询拥有员工的部门的部门名和部门号
select e.ename,d.deptno,d.dname from emp e,dept d where e.deptno = d.deptno

-- 17、查询工资多于smith的员工信息。
select * from emp where sal > (select sal from emp where ename = 'smith')

-- 18、查询员工和及其所属经理的姓名(同一个部门，job=manager)---自连接
select e2.ename '员工姓名',e1.ename '领导姓名' from emp e1,emp e2 where e1.empno = e2.mgr

-- 19、查询雇佣日期早于其经理雇佣日期的员工及其经理姓名
select e1.ename '员工姓名',e2.ename '领导姓名' from emp e1,emp e2 
where e1.mgr = e2.empno 
and e1.hiredate < e2.hiredate
and e2.empno is not null
and e2.job = 'manager'

-- 20、查询员工姓名及其所在的部门名称
-- 注意：一般在多表联查的时候，放在前面的表-称为主表，join的表-称为从表
-- 一般会将数据量小的表作为主表，数据多的作为从表--->加快查询效率
select e.ename,d.dname from emp e,dept d where e.deptno = d.deptno

select e.ename,d.dname from dept d join emp e on e.deptno = d.deptno

-- 21、查询工作为clerk的员工姓名及其所在部门名称
select e.ename,d.dname,e.job from dept d join emp e on e.deptno = d.deptno
where e.job = 'clerk'

-- 22、查询各部门号及其部门中的最低工资
select d.deptno,min(e.sal) from emp e,dept d 
where e.deptno  = d.deptno
group by d.deptno

-- 23、查询销售部sales的所有员工的姓名
select e.ename from emp e
where e.deptno = (select deptno from dept where dname = 'sales')

-- 24、查询工资水平大于平均工资的员工
select * from emp 
where  sal > (select avg(sal) from emp)

-- 查询员工信息，并查询出其工资等级
select e.*,s.grade  from emp e,salgrade s 
where e.sal between s.losal and s.hisal
-- and s.grade >= (select grade from salgrade where (select avg(sal) from emp)<=hisal and (select avg(sal) from emp)>=losal
-- )


-- 25、查询与SCOTT从事相同工作的员工。
select * from emp where emp.job = (select job from emp where ename = 'SCOTT')

-- 26、查询与部门编号为30的员工工资水平相同的员工信息
	-- 1）部门30的平均工资等级；
	select e.deptno,s.grade from emp e,salgrade s
	where (select avg(sal) from emp where emp.deptno = e.deptno) between s.losal and s.hisal
	group by e.deptno,s.grade
	having e.deptno = 30


	-- 2)查询员工信息以及员工的工资等级，然后再用员工的工资等级和平均等级进行比较
	select e.*,s.grade from emp e,salgrade s
	where e.sal between s.losal and s.hisal
	and s.grade >= (select grade from salgrade where (select avg(sal) from emp)<=hisal and (select avg(sal) from emp)>=losal)
	
	
-- 27、查询工资高于部门编号为30的部门所有员工工资水平的员工信息
-- 1）找到30部门最高工资水平
select e.*,s.grade from emp e,salgrade s
where e.sal between s.losal and s.hisal
and e.deptno = 30
order by s.grade desc limit 1


-- 28、查询部门号、部门名、部门所在位置及其每个部门的员工总数
select d.deptno,d.dname,d.loc,count(e.deptno) from emp e,dept d
where e.deptno = d.deptno
group by e.deptno

select d.deptno,d.dname,d.loc,count(e.deptno) from dept d left join emp e on e.deptno = d.deptno
group by e.deptno,d.dname,d.loc,d.deptno


-- 29、查询员工的姓名、工资及其所属部门
select e.ename,e.sal,d.dname '所属部门' from emp e,dept d
where e.deptno = d.deptno

-- 30、查询员工的详细信息(含部门名)
select e.*,d.*,s.grade from emp e,dept d,salgrade s
where e.deptno = d.deptno
and e.sal between s.losal and s.hisal


-- 31、查询各工作名称以及从事此工作的最低工资
select d.dname,min(e.sal) from emp e,dept d
where e.deptno = d.deptno
group by e.deptno

-- 32、计算员工的年薪并且以年薪排序
select e.ename,(e.sal)*12 a from emp e
order by a

-- 33、查询工资为第4级别的员工的姓名及其工资
select e.ename,e.sal from emp e,salgrade s
	where e.sal between s.losal and s.hisal
	and s.grade >= 4

-- 34、查询工资为第3等级的职员名字、工资、所属部门以及部门所在地
select e.ename,e.sal,d.* from emp e,salgrade s,dept d
	where e.sal between s.losal and s.hisal
	and e.deptno = d.deptno
	and s.grade = 3

-- 35、查询工资等级大于smith的员工的信息
select e.* from emp e,salgrade s
	where e.sal between s.losal and s.hisal
	and s.grade > (select grade from emp,salgrade where emp.ename = 'smith' and emp.sal between salgrade.losal and salgrade.hisal)


 -- smith的等级
	select grade from emp,salgrade where emp.ename = 'smith' and emp.sal between salgrade.losal and salgrade.hisal