sqlcook sql经典实例 emp dept 创建语句

创建表语句

create table dept(

	deptno int primary key,

	dname varchar(30),

	loc varchar(30)

);

create table emp(

	empno int primary key,

	ename varchar(30),

	job varchar(30),

	mgr int,

	hiredate date,

	sal numeric(10,2),

	comm numeric(10,2),

	deptno integer references dept(deptno)

);

插入数据

插入dept数据

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');

插入emp数据

insert into emp(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,DEPTNO) values(7369,'SMITH','CLERK',7902,'1980-12-17',800.00,20);

insert into emp values(7499,'ALLEN','SALESMAN',7698,'1981-2-20',1600.00,300.00,30);

insert into emp values(7521,'WARD','SALESMAN',7698,'1981-2-22',1250.00,500.00,30);

insert into emp(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,DEPTNO) values(7566,'JONES','MANAGER',7839,'1981-4-2',2975.00,20);

insert into emp values(7654,'MARTIN','SALESMAN',7698,'1981-9-28',1250.00,1400.00,30);

insert into emp(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,DEPTNO) values(7698,'BLAKE','MANAGER',7839,'1981-5-1',2850.00,30);

insert into emp(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,DEPTNO) values(7782,'CLARK','MANAGER',7839,'1981-6-9',2450.00,10);

insert into emp(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,DEPTNO) values(7788,'SCOTT','ANALYST',7566,'1987--4-19',3000.00,20);

insert into emp(EMPNO,ENAME,JOB,HIREDATE,SAL,DEPTNO) values(7839,'KING','PRESIDENT','1981-11-17',5000.00,10);

insert into emp values(7844,'TURNER','SALESMAN',7698,'1981-9-8',1500.00,0.00,30);

insert into emp(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,DEPTNO) values(7876,'ADAMS','CLERK',7788,'1987-5-23',1100.00,20);

insert into emp(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,DEPTNO) values(7900,'JAMES','CLERK',7698,'1981-12-3',950,30);

insert into emp(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,DEPTNO) values(7902,'FORD','ANALYST',7566,'1981-12-3',3000,20);

insert into emp(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,DEPTNO) values(7934,'MILLER','CLERK',7782,'1982-1-23',1300,10);

insert into emp values(9999,'SHUNPING','CLERK',7782,'1988-5-5',2456.34,55.66,10);

查看数据

select * from dept;

select * from emp;