/*创建部门表*/
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;
学习记录336@MySQL经典习题
最新推荐文章于 2022-05-30 14:56:01 发布