SELECT * FROM EMP 
WHERE (sal>500 OR job='MANAGER') 
AND ename LIKE 'J%';

SELECT * FROM EMP 
ORDER BY deptno, sal DESC;

SELECT ename, sal*12+IFNULL(comm,0) AS '年薪' 
FROM EMP 
ORDER BY 年薪 DESC;

SELECT deptno, FORMAT(AVG(sal),2) AS avg_sal, MAX(sal) AS max_sal 
FROM EMP 
GROUP BY deptno;

SELECT deptno, AVG(sal) AS avg_sal 
FROM EMP 
GROUP BY deptno 
HAVING avg_sal<2000;

SELECT EMP.ename, EMP.sal, DEPT.dname 
FROM EMP, DEPT 
WHERE EMP.deptno = DEPT.deptno;

SELECT ename, sal, dname 
FROM EMP, DEPT 
WHERE EMP.deptno=DEPT.deptno 
AND DEPT.deptno=10;

SELECT ename, sal, grade 
FROM EMP, SALGRADE 
WHERE EMP.sal BETWEEN losal AND hisal;

-- 別名leader代表領(lǐng)導，worker代表員工
SELECT leader.empno, leader.ename 
FROM emp leader, emp worker 
WHERE leader.empno = worker.mgr 
AND worker.ename='FORD';

SELECT ename, job 
FROM EMP 
WHERE sal = (SELECT MAX(sal) FROM EMP);

SELECT ename,job,sal,deptno 
FROM emp 
WHERE job IN (SELECT DISTINCT job FROM emp WHERE deptno=10) 
AND deptno<>10;

SELECT ename, sal, deptno 
FROM EMP 
WHERE sal > ALL(SELECT sal FROM EMP WHERE deptno=30);

-- 先查各部門平均工資（臨時表tmp），再關(guān)聯(lián)員工表
SELECT ename, deptno, sal, FORMAT(tmp.avg_sal,2) AS dept_avg_sal
FROM EMP, 
(SELECT AVG(sal) avg_sal, deptno dt FROM EMP GROUP BY deptno) tmp
WHERE EMP.sal > tmp.avg_sal 
AND EMP.deptno=tmp.dt;

SELECT ename, sal, job FROM EMP WHERE sal>2500 
UNION
SELECT ename, sal, job FROM EMP WHERE job='MANAGER';

SELECT ename, sal, job FROM EMP WHERE sal>2500 
UNION ALL
SELECT ename, sal, job FROM EMP WHERE job='MANAGER';