oracle 建员工表,Oracle练习用表(DEPT、EMP、BONUS、SALGRADE)的建表语句

关注

转载最新推荐文章于 2024-12-19 11:01:13 发布 1.1k 阅读 AI 写同款· GEO 优化 ›

《Oracle练习用表(DEPT、EMP、BONUS、SALGRADE)的建表语句》由会员分享，可在线阅读，更多相关《Oracle练习用表(DEPT、EMP、BONUS、SALGRADE)的建表语句(5页珍藏版)》请在人人文库网上搜索。

1、Oracle自带的练习用表的建表语句文件：C:OracleOracle11g11.2.0dbhome_1RDBMSADMINutlsampl.sql -Rem Copyright (c) 1990, 2006, Oracle. All rights reserved.Rem NAMEREM UTLSAMPL.SQLRem FUNCTIONRem NOTESRem MODIFIEDRem lburgess 04/02/06 - lowercase passwordsRem menash 02/21/01 - remove unnecessary users for security reason。

2、sRem gwood 03/23/99 - make all dates Y2K compliantRem jbellemo 02/27/97 - dont connect as systemRem akolk 08/06/96 - bug : Adding date formatsRem glumpkin 10/21/92 - Renamed from SQLBLD.SQLRem blinden 07/27/92 - Added primary and foreign keys to EMP and DEPTRem rlim 04/29/91 - change char to varchar。

3、2Rem mmoore 04/08/91 - use unlimited tablespace privRem pritto 04/04/91 - change SYSDATE to 13-JUL-87Rem Mendels 12/07/90 - bug 30123;add to_date calls so language independentRemremrem $Header: utlsampl.sql 02-apr-2006.21:13:01 lburgess Exp $ sqlbld.sqlremSET TERMOUT OFFSET ECHO OFFrem CONGDON Invok。

4、ed in RDBMS at build time. 29-DEC-1988rem OATES: Created: 16-Feb-83DROP USER SCOTT CASCADE;DROP USER ADAMS CASCADE;DROP USER JONES CASCADE;DROP USER CLARK CASCADE;DROP USER BLAKE CASCADE;GRANT CONNECT,RESOURCE,UNLIMITED TABLESPACE TO SCOTT IDENTIFIED BY tiger;DROP PUBLIC SYNONYM PARTS;CONNECT SCOTT/。

5、tigerCREATE TABLE DEPT(DEPTNO NUMBER(2) CONSTRAINT PK_DEPT PRIMARY KEY,DNAME VARCHAR2(14),LOC VARCHAR2(13);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。

6、 EMP(EMPNO NUMBER(4) CONSTRAINT PK_EMP PRIMARY KEY,ENAME VARCHAR2(10),JOB VARCHAR2(9),MGR NUMBER(4),HIREDATE DATE,SAL NUMBER(7,2),COMM NUMBER(7,2),DEPTNO NUMBER(2) CONSTRAINT FK_DEPTNO REFERENCES DEPT);INSERT INTO EMP VALUES (7369,SMITH,CLERK,7902,to_date(17-12-1980,dd-mm-yyyy),800,NULL,20);INSERT I。

7、NTO EMP VALUES (7499,ALLEN,SALESMAN,7698,to_date(20-2-1981,dd-mm-yyyy),1600,300,30);INSERT INTO EMP VALUES (7521,WARD,SALESMAN,7698,to_date(22-2-1981,dd-mm-yyyy),1250,500,30);INSERT INTO EMP VALUES (7566,JONES,MANAGER,7839,to_date(2-4-1981,dd-mm-yyyy),2975,NULL,20);INSERT INTO EMP VALUES (7654,MARTI。

8、N,SALESMAN,7698,to_date(28-9-1981,dd-mm-yyyy),1250,1400,30);INSERT INTO EMP VALUES (7698,BLAKE,MANAGER,7839,to_date(1-5-1981,dd-mm-yyyy),2850,NULL,30);INSERT INTO EMP VALUES (7782,CLARK,MANAGER,7839,to_date(9-6-1981,dd-mm-yyyy),2450,NULL,10);INSERT INTO EMP VALUES (7788,SCOTT,ANALYST,7566,to_date(13。

9、-JUL-87,dd-mm-rr)-85,3000,NULL,20);INSERT INTO EMP VALUES (7839,KING,PRESIDENT,NULL,to_date(17-11-1981,dd-mm-yyyy),5000,NULL,10);INSERT INTO EMP VALUES (7844,TURNER,SALESMAN,7698,to_date(8-9-1981,dd-mm-yyyy),1500,0,30);INSERT INTO EMP VALUES (7876,ADAMS,CLERK,7788,to_date(13-JUL-87, dd-mm-rr)-51,110。

10、0,NULL,20);INSERT INTO EMP VALUES (7900,JAMES,CLERK,7698,to_date(3-12-1981,dd-mm-yyyy),950,NULL,30);INSERT INTO EMP VALUES (7902,FORD,ANALYST,7566,to_date(3-12-1981,dd-mm-yyyy),3000,NULL,20);INSERT INTO EMP VALUES (7934,MILLER,CLERK,7782,to_date(23-1-1982,dd-mm-yyyy),1300,NULL,10);CREATE TABLE BONUS。

11、(ENAME VARCHAR2(10),JOB VARCHAR2(9),SAL NUMBER,COMM NUMBER);CREATE TABLE SALGRADE(GRADE NUMBER,LOSAL NUMBER,HISAL NUMBER);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。

12、 SALGRADE VALUES (5,3001,9999);COMMIT;EXIT- 把Oracle自带的练习用表的建表语句中的NUMBER改为INT，VARCHAR2改为VARCHAR，to_date()函数直接去掉，设置主键和关联外键的语法修改一下，就是MySQL的建表语句： -CREATE DATABASE OracleDEFAULT CHARACTER SET UTF8;USE Oracle;CREATE TABLE DEPT(DEPTNO INT(2) PRIMARY KEY,DNAME VARCHAR(14),LOC VARCHAR(13);INSERT INTO DEPT VA。

13、LUES(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(4) PRIMARY KEY,ENAME VARCHAR(10),JOB VARCHAR(9),MGR INT(4),HIREDATE DATE,SAL INT(7),COMM INT(7),DEPTNO INT(2),C。

14、ONSTRAINT FK_DEPTNO FOREIGN KEY(DEPTNO) REFERENCES DEPT(DEPTNO);INSERT INTO EMP VALUES (7369,SMITH,CLERK,7902,1980-12-17,800,NULL,20);INSERT INTO EMP VALUES (7499,ALLEN,SALESMAN,7698,1981-2-20,1600,300,30);INSERT INTO EMP VALUES (7521,WARD,SALESMAN,7698,1981-2-22,1250,500,30);INSERT INTO EMP VALUES 。

15、(7566,JONES,MANAGER,7839,1981-4-2,2975,NULL,20);INSERT INTO EMP VALUES (7654,MARTIN,SALESMAN,7698,1981-9-28,1250,1400,30);INSERT INTO EMP VALUES (7698,BLAKE,MANAGER,7839,1981-5-1,2850,NULL,30);INSERT INTO EMP VALUES (7782,CLARK,MANAGER,7839,1981-6-9,2450,NULL,10);INSERT INTO EMP VALUES (7788,SCOTT,A。

16、NALYST,7566,1987-7-13,3000,NULL,20);INSERT INTO EMP VALUES (7839,KING,PRESIDENT,NULL,1981-11-17,5000,NULL,10);INSERT INTO EMP VALUES (7844,TURNER,SALESMAN,7698,1981-9-8,1500,0,30);INSERT INTO EMP VALUES (7876,ADAMS,CLERK,7788,1987-7-13,1100,NULL,20);INSERT INTO EMP VALUES (7900,JAMES,CLERK,7698,1981。

17、-12-3,950,NULL,30);INSERT INTO EMP VALUES (7902,FORD,ANALYST,7566,1981-12-3,3000,NULL,20);INSERT INTO EMP VALUES (7934,MILLER,CLERK,7782,1982-1-23,1300,NULL,10);CREATE TABLE BONUS(ENAME VARCHAR(10),JOB VARCHAR(9),SAL INT,COMM INT);CREATE TABLE SALGRADE(GRADE INT,LOSAL INT,HISAL INT);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。