http://www.cnblogs.com/fengmingyue/p/6037955.html

 

Table structure:

emp table:

dept table:

salgrade table:

(1) Find out which department has at least one employee. Display department number, department name, Department location and department number.

SELECT z.*,d.dname,d.loc
FROM dept d, (SELECT deptno, COUNT(*) cnt FROM emp GROUP BY deptno) z
WHERE z.deptno=d.deptno;

(2) List all employees whose salary is higher than Zhang San.

SELECT *
FROM emp e
WHERE e.sal > (SELECT sal FROM emp WHERE ename='Zhang San')

(3) List the names of all employees and their immediate superiors.

SELECT e.ename, IFNULL(m.ename, 'BOSS') AS lead
FROM emp e LEFT JOIN emp m
ON e.mgr=m.empno;

(4) List the number, name and department name of all employees whose employment date is earlier than the direct superior.

SELECT e.empno, e.ename, d.dname
FROM emp e LEFT JOIN emp m ON e.mgr=m.empno 
           LEFT JOIN dept d ON e.deptno=d.deptno
WHERE e.hiredate<m.hiredate;

(5) List Department names and employee information of these departments, as well as those departments without employees.

SELECT e.*, d.dname
FROM emp e RIGHT JOIN dept d
ON e.deptno=d.deptno;

(6) List the names of all clerks and their departments, and the number of departments.

SELECT e.ename, d.dname, z.cnt
FROM emp e, (SELECT deptno, COUNT(*) cnt FROM emp GROUP BY deptno) z, dept d
WHERE e.deptno=d.deptno AND z.deptno=d.deptno AND e.job='Clerk';

(7) List all kinds of work with a minimum salary of more than 15000 and the number of employees engaged in this work.

SELECT job, COUNT(*)
FROM emp e
GROUP BY job
HAVING MIN(sal) > 15000;

(8) List the names of employees working in the sales department, assuming they do not know the department number of the sales department.

SELECT e.ename
FROM emp e
WHERE e.deptno = (SELECT deptno FROM dept WHERE dname='Sales Department');

(9) List all employee information with salary higher than the average salary of the company, department name, superior leader, salary grade.

SELECT e.*, d.dname, m.ename, s.grade
FROM emp e NATURAL LEFT JOIN dept d
           LEFT JOIN emp m ON m.empno=e.mgr
           LEFT JOIN salgrade s ON e.sal BETWEEN s.losal AND s.hisal
WHERE e.sal > (SELECT AVG(sal) FROM emp);

(10) List the names of all employees and departments engaged in the same work as pangtong.

SELECT e.*, d.dname
FROM emp e, dept d
WHERE e.deptno=d.deptno AND e.job=(SELECT job FROM emp WHERE ename='Pang Tong');

(11) List the names of all employees whose salaries are higher than those of all employees working in department 30.

SELECT e.ename, e.sal, d.dname
FROM emp e, dept d
WHERE e.deptno=d.deptno AND sal > ALL(SELECT sal FROM emp WHERE deptno=30)

(12) List the number of employees working in each department and the average wage.

SELECT d.dname, e.cnt, e.avgsal
FROM (SELECT deptno, COUNT(*) cnt, AVG(sal) avgsal FROM emp GROUP BY deptno) e, dept d
WHERE e.deptno=d.deptno;