生活不止眼前的苟且,还有诗和远方。
请根据给出的数据库表结构来回答相应问题:
DEPT (DEPTNO INT, DNAME VARCHAR(14),LOC VARCHAR(13));
EMP (EMPNO INT,ENAME VARCHAR(10), JOB VARCHAR(9), MGR INT, HIREDATE DATE,
SAL FLOAT, COMM FLOAT, DEPTNO INT);
BONUS (ENAME VARCHAR(10), JOB VARCHAR(9), SAL INT, COMM INT);
SALGRADE ( GRADE INT, LOSAL INT, HISAL INT);
其中表中包含如下数据:
DEPT表:
EMP表:
SALGRADE表:
BONUS表: 无数据
根据上面描述完成下面问题:
(注意:注意保存脚本,尤其是DDL和DML,以便进行数据还原)
DDL
1.写出上述表的建表语句。此外,在DEPT上创建名为”PK_DEPT”的主键约束,在EMP表上创建名为” PK_EMP”的主键约束以及指向表DEPT的外键约束” FK_DEPTNO”。
命令:
create table DEPT (DEPTNO INT,
DNAME VARCHAR(14),
LOC VARCHAR(13),
constraint pk_dept primary key (DEPTNO));
create table EMP (EMPNO INT,
ENAME VARCHAR(10),
JOB VARCHAR(9),
MGR INT,
HIREDATE DATETIME,
SAL FLOAT,
COMM FLOAT,
DEPTNO INT,
constraint pk_emp primary key (EMPNO),
constraint fk_deptno foreign key (deptno) references dept(deptno));
create table BONUS(ENAME VARCHAR(10),
JOB VARCHAR(9),
SAL INT,
COMM INT);
create table SALGRADE ( GRADE INT,
LOSAL INT,
HISAL INT);
DML
2.给出相应的INSERT语句来完成题中给出数据的插入。
命令:
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');
insert into emp values(7369, 'SMITH', 'CLERK', 7902, '1980-12-17 0:00:00', 800, NULL, 20);
insert into emp values(7499, 'ALLEN', 'SALESMAN', 7698, '1981-2-20 0:00:00', 1600, 300, 30);
insert into emp values(7521, 'WARD', 'SALESMAN', 7698, '1981-2-22 0:00:00', 1250, 500, 30);
insert into emp values(7566, 'JONES', 'MANAGER', 7839, '1981-4-2 0:00:00', 2975, NULL, 20);
insert into emp values(7654, 'MARTIN', 'SALESMAN', 7698,