学习记录336@MySQL经典习题

/*创建部门表*/
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;