TEXT   35

emp

Guest on 10th May 2022 05:21:35 PM

  1.  
  2. /*
  3. Below are examples of creating tables with SQL in and Oracle database and
  4. these include some examples of various ways to define columns and to set
  5. CONSTRAINTs for the tables in this relational database.
  6.  
  7. This simple database consists of a dept and emp tables. This database is used by an instructor
  8. in a systems analysis and design class to keep track of dept and emp data.  
  9.  
  10. Each dept has a dname, a deptno and a loc.
  11. Each emp is managed by a dept, initially, and an emp may not be managed by a dept .
  12. Each dept may manage one or many emps.
  13.  
  14. */
  15.  
  16.  
  17. /*
  18. This CREATE TABLE statement defines 3 columns in a dept table and defines the PRIMARY KEY field for this table.
  19. Even though the dname is not the primary key field, a unique CONSTRAINT has been assigned to this field.
  20. */
  21.  
  22. DROP TABLE emp;
  23. DROP TABLE dept;
  24. DROP TABLE salgrade;
  25.  
  26. CREATE TABLE salgrade
  27. (
  28.  grade           NUMBER                          NOT NULL,
  29.   losal           NUMBER                          NOT NULL,
  30.   hisal           NUMBER                          NOT NULL
  31. );
  32.  
  33.  
  34.  
  35. CREATE TABLE dept
  36. ( deptno        VARCHAR(2)      NOT NULL,
  37.   dname         VARCHAR(30)     NOT NULL,
  38.   loc           VARCHAR(30),
  39.   CONSTRAINT    pk_dept PRIMARY KEY (deptno),
  40.   CONSTRAINT    uniq_dname UNIQUE (dname)
  41. );
  42.  
  43.  
  44. /*
  45. The following SQL code illustrates how to designate a FOREIGN KEY CONSTRAINT
  46. that states which field is related to a PRIMARY KEY field in another table.  
  47. An entry in the deptno field must match a value in the related primary key
  48. field in the dept table.
  49. */
  50.  
  51. CREATE TABLE emp
  52. ( empno         VARCHAR(4)                      NOT NULL,
  53.   ename         VARCHAR(15)                     NOT NULL,
  54.   job           VARCHAR(15)                     NOT NULL,      
  55.   mgr           VARCHAR(4),
  56.   hiredate      DATE                            NOT NULL,
  57.   sal           NUMBER                          NOT NULL,
  58.   comm          NUMBER,
  59.   deptno        VARCHAR(2),
  60.   CONSTRAINT    pk_empno PRIMARY KEY(empno),
  61.   CONSTRAINT    fk_deptno FOREIGN KEY(deptno) REFERENCES dept(deptno),
  62.   CONSTRAINT    fk_mgr FOREIGN KEY(mgr) REFERENCES emp(empno));
  63.  
  64.  
  65. INSERT INTO DEPT VALUES (10,'ACCOUNTING','NEW YORK');
  66.  
  67. INSERT INTO DEPT VALUES (20,'RESEARCH','DALLAS');
  68.  
  69. INSERT INTO DEPT VALUES (30,'SALES','CHICAGO');
  70.  
  71. INSERT INTO DEPT VALUES (40,'OPERATIONS','BOSTON');
  72.  
  73.  
  74.  
  75. INSERT INTO EMP VALUES (7839,'KING','PRESIDENT',NULL,'17-NOV-81',5000,NULL,10);
  76.  
  77. INSERT INTO EMP VALUES (7698,'BLAKE','MANAGER',7839,'1-MAY-81',2850,NULL,30);
  78.  
  79. INSERT INTO EMP VALUES (7782,'CLARK','MANAGER',7839,'9-JUN-81',2450,NULL,10);
  80.  
  81. INSERT INTO EMP VALUES (7566,'JONES','MANAGER',7839,'2-APR-81',2975,NULL,20);
  82.  
  83. INSERT INTO EMP VALUES (7654,'MARTIN','SALESMAN',7698,'28-SEP-81',1250,1400,30);
  84.  
  85. INSERT INTO EMP VALUES (7499,'ALLEN','SALESMAN',7698,'20-FEB-81',1600,300,30);
  86.  
  87. INSERT INTO EMP VALUES (7844,'TURNER','SALESMAN',7698,'8-SEP-81',1500,0,30);
  88.  
  89. INSERT INTO EMP VALUES (7900,'JAMES','CLERK',7698,'3-DEC-81',950,NULL,30);
  90.  
  91. INSERT INTO EMP VALUES (7521,'WARD','SALESMAN',7698,'22-FEB-81',1250,500,30);
  92.  
  93. INSERT INTO EMP VALUES (7902,'FORD','ANALYST',7566,'3-DEC-81',3000,NULL,20);
  94.  
  95. INSERT INTO EMP VALUES (7369,'SMITH','CLERK',7902,'17-DEC-80',800,NULL,20);
  96.  
  97. INSERT INTO EMP VALUES (7788,'SCOTT','ANALYST',7566,'09-DEC-82',3000,NULL,20);
  98.  
  99. INSERT INTO EMP VALUES (7876,'ADAMS','CLERK',7788,'12-JAN-83',1100,NULL,20);
  100.  
  101. INSERT INTO EMP VALUES (7934,'MILLER','CLERK',7782,'23-JAN-82',1300,NULL,10);
  102.  
  103.  
  104.  
  105. INSERT INTO SALGRADE VALUES (1,700,1200);
  106.  
  107. INSERT INTO SALGRADE VALUES (2,1201,1400);
  108.  
  109. INSERT INTO SALGRADE VALUES (3,1401,2000);
  110.  
  111. INSERT INTO SALGRADE VALUES (4,2001,3000);
  112.  
  113. INSERT INTO SALGRADE VALUES (5,3001,9999);
  114.  
  115.  
  116.  
  117. COMMIT;
  118.  
  119. SET LINESIZE 150
  120.  
  121. SET PAGESIZE 80

Raw Paste

Login or Register to edit or fork this paste. It's free.