CREATE DATABASE scott;

CREATE TABLE dept(

    deptno      INT UNSIGNED PRIMARY KEY COMMENT '部门编号',

    dname       VARCHAR(15) COMMENT '部门名称',

    loc         VARCHAR(50) COMMENT '部门所在位置'

) COMMENT='部门表';

INSERT INTO dept VALUES (10,'ACCOUNTING','NEW YORK');

INSERT INTO dept VALUES (20,'RESEARCH','DALLAS');

INSERT INTO dept VALUES (30,'SALES','CHICAGO');

INSERT INTO dept VALUES (40,'OPERATIONS','BOSTON');

CREATE TABLE emp(

    empno           INT UNSIGNED AUTO_INCREMENT PRIMARY KEY COMMENT '雇员编号',

    ename           VARCHAR(15) COMMENT '雇员姓名',

    job             VARCHAR(10) COMMENT '雇员职位',

    mgr             INT UNSIGNED COMMENT '雇员对应的领导的编号',

    hiredate        DATE COMMENT '雇员的雇佣日期',

    sal             DECIMAL(7,2) COMMENT '雇员的基本工资',

    comm            DECIMAL(7,2) COMMENT '奖金',

    deptno          INT UNSIGNED COMMENT '所在部门',

    FOREIGN KEY(deptno) REFERENCES dept(deptno)

) COMMENT='雇员表';

CREATE TABLE salgrade(

    grade       INT UNSIGNED COMMENT '工资等级',

    losal       INT UNSIGNED COMMENT '此等级的最低工资',

    hisal       INT UNSIGNED COMMENT '此等级的最高工资'

) COMMENT='工资等级表';

INSERT INTO salgrade VALUES (1,700,1200);

INSERT INTO salgrade VALUES (2,1201,1400);

INSERT INTO salgrade VALUES (3,1401,2000);

INSERT INTO salgrade VALUES (4,2001,3000);

INSERT INTO salgrade VALUES (5,3001,9999);

CREATE TABLE bonus(

    ename       VARCHAR(10) COMMENT '雇员姓名',

    job         VARCHAR(9) COMMENT '雇员职位',

    sal         DECIMAL(7,2) COMMENT '雇员工资',

    comm        DECIMAL(7,2) COMMENT '雇员资金'

) COMMENT='工资表';