最常用的2個資料表emp dept

Michael_DD發表於2014-02-26
CREATE TABLE EMP (
  EMPNO number(4) NOT NULL,
  ENAME varchar2(10) NOT NULL,
  JOB varchar2(9) NOT NULL,
  MGR number(4),
  HIREDATE DATE NOT NULL,
  SAL number(7,2) NOT NULL,
  COMM number(7,2),
  DEPTNO number(2)
); 

資料庫會報告一個ORA-01843的錯誤:
alter session set nls_date_language='american';
alter session set NLS_DATE_FORMAT='DD-MON-YY';


INSERT INTO emp VALUES ('7369', 'SMITH', 'CLERK', '7902', '17-DEC-90', '13750', '', '20');
INSERT INTO emp VALUES ('7499', 'ALLEN', 'SALESMAN', '7698', '20-FEB-89', '19000', '6400', '30');
INSERT INTO emp VALUES ('7521', 'WORD', 'SALESMAN', '7698', '22-FEB-93', '18500', '4250', '30');
INSERT INTO emp VALUES ('7566', 'JONES', 'MANAGER', '7839', '02-APR-89', '36850', '', '20');
INSERT INTO emp VALUES ('7654', 'MARTIN', 'SALESMAN', '7698', '28-SEP-97', '15675', '3500', '30');
INSERT INTO emp VALUES ('7698', 'BLAKE', 'MANAGER', '7839', '01-MAY-90', '24000', '', '30');
INSERT INTO emp VALUES ('7782', 'CLARK', 'MANAGER', '7839', '09-JUN-88', '27500', '', '10');
INSERT INTO emp VALUES ('7788', 'SCOTT', 'ANALYST', '7566', '19-APR-87', '19500', '', '20');
INSERT INTO emp VALUES ('7839', 'KING', 'PRESIDENT', '', '17-NOV-83', '82500', '', '10');
INSERT INTO emp VALUES ('7844', 'TURNER', 'SALESMAN', '7698', '08-SEP-92', '18500', '6250', '30');
INSERT INTO emp VALUES ('7876', 'ADAMS', 'CLERK', '7788', '23-MAY-96', '11900', '', '20');
INSERT INTO emp VALUES ('7900', 'JAMES', 'CLERK', '7698', '03-DEC-95', '12500', '', '30');
INSERT INTO emp VALUES ('7902', 'FORD', 'ANALYST', '7566', '03-DEC-91', '21500', '', '20');
INSERT INTO emp VALUES ('7934', 'MILLER', 'CLERK', '7782', '23-JAN-95', '13250', '', '10');
INSERT INTO emp VALUES ('3258', 'GREEN', 'SALESMAN', '4422', '24-JUL-95', '18500', '2750', '50');
INSERT INTO emp VALUES ('4422', 'STEVENS', 'MANAGER', '7839', '14-JAN-94 ', '24750', '', '50');
INSERT INTO emp VALUES ('6548', 'BARENS', 'CLERK', '4422', '16-JAN-95', '11950', '', '50');
INSERT INTO emp VALUES ('7500', 'CAMPBELL', 'ANALYST', '7566', '30-OCT-92 ', '24500', '0', '40');



CREATE TABLE DEPT (
  DEPTNO number(2) NOT NULL,
  DNAME char(10) ,
  LOC char(10)
);

INSERT INTO dept VALUES ('10', 'ACCOUNTING', 'LONDON');
INSERT INTO dept VALUES ('30', 'SALES', 'LIVERPOOL');
INSERT INTO dept VALUES ('40', 'OPERATIONS', 'STAFFORD');
INSERT INTO dept VALUES ('50', 'MARKETING', 'LUTON');
INSERT INTO dept VALUES ('20', 'RESEARCH', 'PERSTON');


大批次生成資料：
INSERT INTO DEPT SELECT *  FROM DEPT;

INSERT INTO EMP SELECT　* FROM EMP;


oracle 11g 裡面的emp等sample表
執行以下語句即可：

$oracle_home/rdbms/admin/utlsampl.sql


點選(此處)摺疊或開啟

  1. Rem Copyright (c) 1990, 2006, Oracle. All rights reserved.
  2. Rem NAME
  3. REM UTLSAMPL.SQL
  4. Rem FUNCTION
  5. Rem NOTES
  6. Rem MODIFIED
  7. Rem lburgess 04/02/06 - lowercase passwords
  8. Rem menash 02/21/01 - remove unnecessary users for security reasons
  9. Rem gwood 03/23/99 - make all dates Y2K compliant
  10. Rem jbellemo 02/27/97 - dont connect as system
  11. Rem akolk 08/06/96 - bug 368261: Adding date formats
  12. Rem glumpkin 10/21/92 - Renamed from SQLBLD.SQL
  13. Rem blinden 07/27/92 - Added primary and foreign keys to EMP and DEPT
  14. Rem rlim 04/29/91 - change char to varchar2
  15. Rem mmoore 04/08/91 - use unlimited tablespace priv
  16. Rem pritto 04/04/91 - change SYSDATE to 13-JUL-87
  17. Rem Mendels 12/07/90 - bug 30123;add to_date calls so language independent
  18. Rem
  19. rem
  20. rem $Header: utlsampl.sql 02-apr-2006.21:13:01 lburgess Exp $ sqlbld.sql
  21. rem
  22. SET TERMOUT OFF
  23. SET ECHO OFF

  25. rem CONGDON Invoked in RDBMS at build time. 29-DEC-1988
  26. rem OATES: Created: 16-Feb-83
  27.  
  28. DROP USER SCOTT CASCADE;
  29. DROP USER ADAMS CASCADE;
  30. DROP USER JONES CASCADE;
  31. DROP USER CLARK CASCADE;
  32. DROP USER BLAKE CASCADE;
  33. GRANT CONNECT,RESOURCE,UNLIMITED TABLESPACE TO SCOTT IDENTIFIED BY tiger;
  34. DROP PUBLIC SYNONYM PARTS;

  36. CONNECT SCOTT/tiger
  37. CREATE TABLE DEPT
  38.        (DEPTNO NUMBER(2) CONSTRAINT PK_DEPT PRIMARY KEY,
  39.     DNAME VARCHAR2(14) ,
  40.     LOC VARCHAR2(13) ) ;
  41. CREATE TABLE EMP
  42.        (EMPNO NUMBER(4) CONSTRAINT PK_EMP PRIMARY KEY,
  43.     ENAME VARCHAR2(10),
  44.     JOB VARCHAR2(9),
  45.     MGR NUMBER(4),
  46.     HIREDATE DATE,
  47.     SAL NUMBER(7,2),
  48.     COMM NUMBER(7,2),
  49.     DEPTNO NUMBER(2) CONSTRAINT FK_DEPTNO REFERENCES DEPT);
  50. INSERT INTO DEPT VALUES
  51.     (10,\'ACCOUNTING\',\'NEW YORK\');
  52. INSERT INTO DEPT VALUES (20,\'RESEARCH\',\'DALLAS\');
  53. INSERT INTO DEPT VALUES
  54.     (30,\'SALES\',\'CHICAGO\');
  55. INSERT INTO DEPT VALUES
  56.     (40,\'OPERATIONS\',\'BOSTON\');
  57. INSERT INTO EMP VALUES
  58. (7369,\'SMITH\',\'CLERK\',7902,to_date(\'17-12-1980\',\'dd-mm-yyyy\'),800,NULL,20);
  59. INSERT INTO EMP VALUES
  60. (7499,\'ALLEN\',\'SALESMAN\',7698,to_date(\'20-2-1981\',\'dd-mm-yyyy\'),1600,300,30);
  61. INSERT INTO EMP VALUES
  62. (7521,\'WARD\',\'SALESMAN\',7698,to_date(\'22-2-1981\',\'dd-mm-yyyy\'),1250,500,30);
  63. INSERT INTO EMP VALUES
  64. (7566,\'JONES\',\'MANAGER\',7839,to_date(\'2-4-1981\',\'dd-mm-yyyy\'),2975,NULL,20);
  65. INSERT INTO EMP VALUES
  66. (7654,\'MARTIN\',\'SALESMAN\',7698,to_date(\'28-9-1981\',\'dd-mm-yyyy\'),1250,1400,30);
  67. INSERT INTO EMP VALUES
  68. (7698,\'BLAKE\',\'MANAGER\',7839,to_date(\'1-5-1981\',\'dd-mm-yyyy\'),2850,NULL,30);
  69. INSERT INTO EMP VALUES
  70. (7782,\'CLARK\',\'MANAGER\',7839,to_date(\'9-6-1981\',\'dd-mm-yyyy\'),2450,NULL,10);
  71. INSERT INTO EMP VALUES
  72. (7788,\'SCOTT\',\'ANALYST\',7566,to_date(\'13-JUL-87\',\'dd-mm-rr\')-85,3000,NULL,20);
  73. INSERT INTO EMP VALUES
  74. (7839,\'KING\',\'PRESIDENT\',NULL,to_date(\'17-11-1981\',\'dd-mm-yyyy\'),5000,NULL,10);
  75. INSERT INTO EMP VALUES
  76. (7844,\'TURNER\',\'SALESMAN\',7698,to_date(\'8-9-1981\',\'dd-mm-yyyy\'),1500,0,30);
  77. INSERT INTO EMP VALUES
  78. (7876,\'ADAMS\',\'CLERK\',7788,to_date(\'13-JUL-87\', \'dd-mm-rr\')-51,1100,NULL,20);
  79. INSERT INTO EMP VALUES
  80. (7900,\'JAMES\',\'CLERK\',7698,to_date(\'3-12-1981\',\'dd-mm-yyyy\'),950,NULL,30);
  81. INSERT INTO EMP VALUES
  82. (7902,\'FORD\',\'ANALYST\',7566,to_date(\'3-12-1981\',\'dd-mm-yyyy\'),3000,NULL,20);
  83. INSERT INTO EMP VALUES
  84. (7934,\'MILLER\',\'CLERK\',7782,to_date(\'23-1-1982\',\'dd-mm-yyyy\'),1300,NULL,10);
  85. CREATE TABLE BONUS
  86.     (
  87.     ENAME VARCHAR2(10) ,
  88.     JOB VARCHAR2(9) ,
  89.     SAL NUMBER,
  90.     COMM NUMBER
  91.     ) ;
  92. CREATE TABLE SALGRADE
  93.       ( GRADE NUMBER,
  94.     LOSAL NUMBER,
  95.     HISAL NUMBER );
  96. INSERT INTO SALGRADE VALUES (1,700,1200);
  97. INSERT INTO SALGRADE VALUES (2,1201,1400);
  98. INSERT INTO SALGRADE VALUES (3,1401,2000);
  99. INSERT INTO SALGRADE VALUES (4,2001,3000);
  100. INSERT INTO SALGRADE VALUES (5,3001,9999);
  101. COMMIT;
  102. EXIT






來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29500582/viewspace-1092353/，如需轉載，請註明出處，否則將追究法律責任。

相關文章