mysql基礎練習(二)

-- 創建表
drop table if exists emp;
create table emp(
empno int,
ename varchar(50),
job varchar(50),
mgr int,
hiredate date,
sal decimal(7,2),
comm decimal(7,2),
deptno int
)engine=innodb default charset=utf8;

drop table if exists dept;
create table dept(
deptno int,
dname varchar(14),
loc varchar(13)
)engine=innodb default charset=utf8;

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-04-19',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-05-23',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,'1982-01-23',1300,NULL,10);

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');

-- 1、按員工編號升序排列不在10號部門工作的員工信息
SELECT *
FROM emp
WHERE deptno != 10
ORDER BY empno;
-- 2、查詢姓名第二個字母不是”A”且薪水大於800元的員工信息，按薪水降序排列
SELECT *
FROM emp
where ename not LIKE "_A%" and CEIL(sal) > 800;
-- 3、求每個部門的平均薪水
SELECT deptno, avg(sal)
FROM emp
GROUP BY deptno;
-- 4、求各個部門的最高薪水
SELECT deptno, max(sal)
FROM emp
GROUP BY deptno;
-- 5、求每個部門每個職位的最高薪水
SELECT deptno, job, MAX(sal)
FROM emp
group by deptno, job
-- 6、求平均薪水大於2000的部門編號
SELECT deptno, avg(sal) avgSal
from emp
group by deptno
HAVING avgSal > 2000;
-- 7、將員工薪水大於1200且部門平均薪水大於1500的部門編號列出來，按部門平均薪水降序排列
select deptno, avg(sal) avgSal
from emp
where sal > 1200
GROUP BY deptno
HAVING avgSal > 1500
ORDER BY avgSal desc;

-- 8、求最高薪水的員工信息
SELECT *
from emp
where sal = (
SELECT max(sal)
FROM emp
)
-- 9、求多於平均薪水的員工信息
SELECT *
FROM emp
where sal > (
SELECT AVG(sal)
FROM emp
)

-- 10、求各個部門薪水最高的員工信息 顯示部門名稱
SELECT dept.dname, t2.*
FROM dept,(
select emp.*
from emp, (
SELECT deptno, MAX(sal) maxSal
FROM emp
GROUP BY deptno) t1
where emp.deptno = t1.deptno AND emp.sal = t1.maxSal) t2
where dept.deptno = t2.deptno


-- 11. 查詢和Scott相同部門的員工姓名和雇用日期

select ename, hiredate
FROM emp
WHERE ename != "SCOTT" and deptno = (
select deptno
from emp
where ename = "SCOTT");
-- 12.	查詢工資比公司平均工資高的員工的員工號，姓名和工資。
SELECT empno, ename, sal
FROM emp
WHERE sal > (
select AVG(sal)
FROM emp);
-- 13.	查詢各部門中工資比本部門平均工資高的員工的員工號, 姓名和工資

-- 13.1
SELECT empno, ename, sal
FROM emp,(
SELECT deptno, avg(sal) avgSal
from emp 
GROUP BY deptno
) t1
where emp.deptno = t1.deptno and emp.sal > t1.avgSal;

-- 13.2
select empno, ename, sal
FROM emp inner join (
SELECT deptno, avg(sal) avgSal
FROM emp
GROUP BY deptno
) t1
on emp.deptno = t1.deptno and emp.sal > t1.avgSal

-- 14.	查詢和姓名中包含字母u的員工在相同部門的員工的員工號和姓名
SELECT empno, ename
FROM emp
WHERE deptno in (
SELECT deptno
FROM emp
WHERE ename LIKE "%u%"
)

-- 15. 	查詢在部門的loc為 DALLAS 的部門工作的員工的員工號
SELECT empno
from emp
WHERE deptno in (
SELECT deptno
FROM dept
WHERE loc = "DALLAS");


-- 16.	查詢所有部門的名字，loc，員工數量 和 工資平均值 ()
-- 16.1
select dept.dname, dept.loc, t1.*
from dept, (
select avg(sal), count(empno), deptno
from emp
group by deptno
) t1
where t1.deptno = dept.deptno;

-- 16.2
select d.*, count(e.empno), avg(sal)
from dept d, emp e
where d.deptno = e.deptno
GROUP BY d.deptno, d.dname, d.loc


-- 17.查詢平均工資最低的部門信息
-- 17.1
select *
from dept
where deptno = (
select deptno
from emp
GROUP BY deptno
order by avg(sal)
limit 1)

-- 17.2
select *
from dept
where deptno = (
select deptno
from emp
group by deptno
having avg(sal) like (
select min(t1.avgSal)
from (
select deptno, avg(sal) avgSal
from emp
GROUP BY deptno
) t1
))

-- 18.查詢平均工資最低的部門信息和該部門的平均工資
select dept.*, avgSal
from dept, (
select deptno, avg(sal) avgSal
from emp
group by deptno) t1
where dept.deptno = t1.deptno


-- 19.查詢平均工資高於公司平均工資的部門有哪些?
select deptno
from emp
group by deptno
having avg(sal) > (
select avg(sal) avgSal
from emp
)

-- 20. 各個部門中 最高工資中最低的那個部門的 最低工資是多少	
select min(sal)
from emp
where deptno = (
select deptno
from emp
group by deptno
order by max(sal) asc
limit 1
)
-- 21.查詢部門號大於20 或者 姓名中包含 a的 員工信息
-- 21.1
select *
from emp
where deptno > 20 or ename Like "%a%";

-- 21.2
select *
from emp 
where deptno > 20
union
select *
from emp
where ename like "%a%";