/*1. 查出至少有一個員工的部門。顯示部門編號、部門名稱、部門位置、部門人數。*/
/*
列：部門編號、部門名稱、部門位置、部門人數(分組)
列：dept、emp(部門人數沒有員工表不行)
條件：沒有
分組條件：人數>1
部門編號、部門名稱、部門位置在dept表中都有，只有部門人數需要使用emp表，使用deptno來分組得到。
我們讓dept和（emp的分組查詢），這兩張表進行連線查詢
*/
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. 列出薪金比關羽高的所有員工。*/
/*
列：所有
表：emp
條件：sal>關羽的sal，其中關羽的sal需要子查詢
*/
SELECT *
FROM emp e
WHERE e.sal > (SELECT sal FROM emp WHERE ename='關羽')
/**************************************************/
/*3. 列出所有員工的姓名及其直接上級的姓名。*/
/*
列：員工名、領導名
表：emp、emp
條件：領導.empno=員工.mgr
emp表中存在自身關聯，即empno和mgr的關係。
我們需要讓emp和emp表連線查詢。因為要求是查詢所有員工的姓名，所以不能用內連線，因為曾阿牛是BOSS，沒有上級，內連線是查詢不到它的。
*/
SELECT e.ename, IFNULL(m.ename, 'BOSS') AS lead
FROM emp e LEFT JOIN emp m
ON e.mgr=m.empno;
/**************************************************/
/*4. 列出受僱日期早於直接上級的所有員工的編號、姓名、部門名稱。*/
/*
列：編號、姓名、部門名稱
表：emp、dept
條件：hiredate < 領導.hiredate
emp表需要查。部門名稱在dept表中，所以也需要查。領導的hiredate需要查，這說明需要兩個emp和一個dept連線查詢
即三個表連線查詢
*/
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. 列出部門名稱和這些部門的員工資訊，同時列出那些沒有員工的部門。*/
/*
列：員工表所有列、部門名稱
表：emp, dept
要求列出沒有員工的部門，這說明需要以部門表為主表使用外連線
*/
SELECT e.*, d.dname
FROM emp e RIGHT JOIN dept d
ON e.deptno=d.deptno;
/**************************************************/
/*6. 列出所有文員的姓名及其部門名稱，部門的人數。*/
/*
列：姓名、部門名稱、部門人數
表：emp emp dept
條件：job=文員
分組：emp以deptno得到部門人數
連線：emp連線emp分組，再連線dept
*/z
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;
/**************************************************/
/*7. 列出最低薪金大於15000的各種工作及從事此工作的員工人數。*/
/*
列：工作，該工作人數
表：emp
分組：使用job分組
分組條件：min(sal)>15000
*/
SELECT job, COUNT(*)
FROM emp e
GROUP BY job
HAVING MIN(sal) > 15000;
/**************************************************/
/*8. 列出在銷售部工作的員工的姓名，假定不知道銷售部的部門編號。*/
/*
列：姓名
表：emp, dept
條件：所在部門名稱為銷售部，這需要通過部門名稱查詢為部門編號，作為條件
*/
SELECT e.ename
FROM emp e
WHERE e.deptno = (SELECT deptno FROM dept WHERE dname='銷售部');
/**************************************************/
/*9. 列出薪金高於公司平均薪金的所有員工資訊，所在部門名稱，上級領導，工資等級。*/
/*
列：員工所有資訊(員工表)，部門名稱(部門表)，上級領導(員工表)，工資等級(等級表)
表：emp, dept, emp, salgrade
條件：sal>平均工資，子查詢
所有員工，說明需要左外
*/
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.列出與龐統從事相同工作的所有員工及部門名稱。*/
/*
列：員工表所有列，部門表名稱
表：emp, dept
條件：job=龐統的工作，需要子查詢，與部門表連線得到部門名稱
*/
SELECT e.*, d.dname
FROM emp e, dept d
WHERE e.deptno=d.deptno AND e.job=(SELECT job FROM emp WHERE ename='龐統');
/**************************************************/
/*11.列出薪金高於在部門30工作的所有員工的薪金的員工姓名和薪金、部門名稱。*/
/*
列：姓名、薪金、部門名稱(需要連線查詢)
表：emp, dept
條件：sal > all(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.列出在每個部門工作的員工數量、平均工資。*/
/*
列：部門名稱, 部門員工數，部門平均工資
表：emp, dept
分組：deptno
*/
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;
SELECT * FROM emp;
SELECT *
FROM emp
LIMIT 4,4;
三.表和資料
/*建立部門表*/
CREATE TABLE dept(
deptno INT PRIMARY KEY,
dname VARCHAR(50),
loc VARCHAR(50)
);
/*建立僱員表*/
CREATE TABLE emp(
empno INT PRIMARY KEY,
ename VARCHAR(50),
job VARCHAR(50),
mgr INT,
hiredate DATE,
sal DECIMAL(7,2),
COMM DECIMAL(7,2),
deptno INT,
CONSTRAINT fk_emp FOREIGN KEY(mgr) REFERENCES emp(empno)
);
/*建立工資等級表*/
CREATE TABLE salgrade(
grade INT PRIMARY KEY,
losal INT,
hisal INT
);
/*建立學生表*/
CREATE TABLE stu(
sid INT PRIMARY KEY,
sname VARCHAR(50),
age INT,
gander VARCHAR(10),
province VARCHAR(50),
tuition INT
);
/*插入dept表資料*/
INSERT INTO dept VALUES (10, '教研部', '北京');
INSERT INTO dept VALUES (20, '學工部', '上海');
INSERT INTO dept VALUES (30, '銷售部', '廣州');
INSERT INTO dept VALUES (40, '財務部', '武漢');
/*插入emp表資料*/
INSERT INTO emp VALUES (1009, '曾阿牛', '董事長', NULL, '2001-11-17', 50000, NULL, 10);
INSERT INTO emp VALUES (1004, '劉備', '經理', 1009, '2001-04-02', 29750, NULL, 20);
INSERT INTO emp VALUES (1006, '關羽', '經理', 1009, '2001-05-01', 28500, NULL, 30);
INSERT INTO emp VALUES (1007, '張飛', '經理', 1009, '2001-09-01', 24500, NULL, 10);
INSERT INTO emp VALUES (1008, '諸葛亮', '分析師', 1004, '2007-04-19', 30000, NULL, 20);
INSERT INTO emp VALUES (1013, '龐統', '分析師', 1004, '2001-12-03', 30000, NULL, 20);
INSERT INTO emp VALUES (1002, '黛綺絲', '銷售員', 1006, '2001-02-20', 16000, 3000, 30);
INSERT INTO emp VALUES (1003, '殷天正', '銷售員', 1006, '2001-02-22', 12500, 5000, 30);
INSERT INTO emp VALUES (1005, '謝遜', '銷售員', 1006, '2001-09-28', 12500, 14000, 30);
INSERT INTO emp VALUES (1010, '韋一笑', '銷售員', 1006, '2001-09-08', 15000, 0, 30);
INSERT INTO emp VALUES (1012, '程普', '文員', 1006, '2001-12-03', 9500, NULL, 30);
INSERT INTO emp VALUES (1014, '黃蓋', '文員', 1007, '2002-01-23', 13000, NULL, 10);
INSERT INTO emp VALUES (1011, '周泰', '文員', 1008, '2007-05-23', 11000, NULL, 20);
INSERT INTO emp VALUES (1001, '甘寧', '文員', 1013, '2000-12-17', 8000, NULL, 20);
/*插入salgrade表資料*/
INSERT INTO salgrade VALUES (1, 7000, 12000);
INSERT INTO salgrade VALUES (2, 12010, 14000);
INSERT INTO salgrade VALUES (3, 14010, 20000);
INSERT INTO salgrade VALUES (4, 20010, 30000);
INSERT INTO salgrade VALUES (5, 30010, 99990);