/*创建部门表*/
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));/*插入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);INSERT INTO emp
VALUES(1015,'西门大官人','经理',1009,'2000-12-17',9000,NULL,90);INSERT INTO dept
VALUES(50,'公关部','肖家河');/*创建工资等级表*/
CREATE TABLE salgrade (grade INT PRIMARY KEY,losal INT,hisal INT
);/*插入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);-- 查出至少有一个员工的部门。显示部门编号、部门名称、部门位置、部门人数。
CREATE VIEW v_deptcount AS
SELECT deptno,COUNT(empno) AS empcount
FROM emp
GROUP BY deptno
HAVING empcount>=1;SELECT v_deptcount.*,dept.dname,dept.loc
FROM v_deptcount LEFT JOIN dept
ON v_deptcount.deptno=dept.deptno;-- 列出所有员工的姓名及其直接上级的姓名。
SELECT e1.ename,e2.ename AS sjname
FROM emp AS e1 LEFT JOIN emp AS e2
ON e1.mgr=e2.empno;-- 列出受雇日期早于直接上级的所有员工的编号、姓名、部门名称。
SELECT e1.empno,e1.ename,dept.dname
FROM emp e1,emp e2,dept
WHERE e1.mgr=e2.empno AND e1.hiredate<e2.hiredate AND e1.deptno=dept.deptno;-- 列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门。
SELECT dept.dname,emp.*
FROM dept LEFT JOIN emp
on dept.deptno=emp.deptno;-- 列出最低薪金大于15000的各种工作及从事此工作的员工人数。
SELECT job,MIN(sal+IFNULL(COMM,0)) minsals,COUNT(empno) count
FROM emp
GROUP BY job
HAVING minsals>15000;-- 列出在销售部工作的员工的姓名，假定不知道销售部的部门编号。
SELECT dept.dname,emp.ename
FROM dept LEFT JOIN emp
ON dept.deptno=emp.deptno
WHERE dept.dname="销售部";-- 列出薪金高于公司平均薪金的所有员工信息，所在部门名称，上级领导，工资等级。SELECT e1.ename,dept.dname,e2.ename sjname,salgrade.grade
FROM emp e1,emp e2, dept,salgrade
WHERE e1.sal>(SELECT AVG(sal) FROM emp) AND e1.mgr=e2.empno AND e1.deptno=dept.deptno AND (e1.sal>salgrade.losal AND e1.sal<salgrade.hisal);-- 列出与庞统从事相同工作的所有员工及部门名称。CREATE VIEW v_ptjob AS
SELECT emp.ename,emp.job
FROM emp
WHERE emp.ename="庞统";SELECT *
FROM emp ,dept ,v_ptjob
WHERE emp.deptno=dept.deptno AND emp.job=v_ptjob.job;-- 列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金、部门名称。CREATE VIEW v_sal AS
SELECT deptno,MAX(sal+IFNULL(COMM,0)) maxsals
FROM emp
GROUP BY deptno
HAVING deptno=30;SELECT emp.ename,emp.sal+IFNULL(emp.COMM,0) sals,dept.dname
FROM emp ,dept ,v_sal
WHERE emp.deptno=dept.deptno AND emp.sal>v_sal.maxsals;
