Create table examples

/* Tables */

--> CREATE A EMPLOYEE TABLE
CREATE TABLE temp
(
EMPNO NUMBER(5) constraint no_pk PRIMARY KEY,
ENAME VARCHAR2(15) not null,
JOB VARCHAR2(15) constraint job_chk check(job in('ANALYST','CLERK','SALESMAN','MANAGER','PRESIDENT')),
MGR NUMBER(5) CONSTRAINT MGR_FK FOREIGN KEY(MGR) REFERENCES EMPSTAGE(EMPNO) ,
HIREDATE TIMESTAMP,
SAL NUMBER(6,2) CONSTRAINT SAL_CHK CHECK(SAL BETWEEN(1000 AND 6000)),
COMM NUMBER(6,2),
DEPTNO NUMBER(3) CONSTRAINT MGR_FK FOREIGN KEY(DEPTNO) REFERENCES DEPT(DEPTNO)
);

CREATE TABLE emp
(
EMPNO NUMBER(5),
ENAME VARCHAR2(15),
JOB VARCHAR2(15),
MGR NUMBER(5),
HIREDATE date,
SAL NUMBER(6,2),
COMM NUMBER(6,2),
DEPTNO NUMBER(3)
);
--> INSERT THE VALUES INTO EMPLOYEE TABLE
INSERT INTO emp
(
EMPNO,
ENAME,
JOB,
MGR,
HIREDATE,
SAL,
COMM,
DEPTNO
)
VALUES
(
7369,
'SMITH',
'SALESMAN',
7902,
'17-DEC-80',
800,
NULL,
20
);
INSERT INTO emp VALUES(7499,'ALLEN','CLERK',7698,'20-FEB-81',1600,300,30);

INSERT INTO emp(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO)VALUES(7521,'WARD','SALESMAN',7698,'22-FEB-81',1250,500,30);

INSERT INTO emp(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO)VALUES(7566,'JONES','MANAGER',7839,'02-APR-81',2975,NULL,20);
--*explicit null value*/
INSERT INTO emp(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO)VALUES(7654,'MARTIN','SALESMAN',7698,'28-SEP-81',1250,1400,30);
INSERT INTO emp(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,DEPTNO)VALUES(7698,'BLAKE','MANAGER',7839,'01-MAY-81',2850,30);
--implcit null value*/
INSERT INTO emp(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO)VALUES(7782,'CLARK','MANAGER',7839,'09-JUN-81',2450,NULL,10);
INSERT INTO emp(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO)VALUES(7788,'SCOTT','ANALYST',7566,'09-DEC-82',3000,NULL,20);
INSERT INTO emp(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO)VALUES(7839,'KING','PRESIDENT',NULL,'17-NOV-81',5000,NULL,10);
INSERT INTO emp(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO)VALUES(7844,'TURNER','SALESMAN',7698,'08-SEP-81',1500,0,30);
INSERT INTO emp(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO)VALUES(7876,'ADAMS','CLERK',7788,'12-JAN-83',1100,NULL,20);
INSERT INTO emp(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO)VALUES(7900,'JAMES','CLERK',7698,'03-DEC-81',950,NULL,30);
INSERT INTO emp(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO)VALUES(7902,'FORD','ANALYST',7566,'03-DEC-81',3000,NULL,20);
INSERT INTO emp(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO)VALUES(7934,'MILLER','CLERK',7782,'23-JAN-82',1300,NULL,10);

SELECT * FROM emp;



--> CREATE A DEPARTMENT TABLE

CREATE TABLE Dept
(
DEPTNO NUMBER(3),
DNAME VARCHAR2(15),
LOC VARCHAR2(15)
);
--> INSERT THE VALUES INTO DEPARTMENT TABLE

INSERT INTO Dept
(
DEPTNO,
DNAME,
LOC
)
VALUES
(
10,
'ACCOUNTING',
'NEW YORK'
);
INSERT INTO Dept(DEPTNO,DNAME,LOC)VALUES(20,'RESEARCH','DALLAS');
INSERT INTO Dept(DEPTNO,DNAME,LOC)VALUES(30,'SALES','CHICAGO');
INSERT INTO Dept(DEPTNO,DNAME,LOC)VALUES(40,'OPERATION','BOSTON');

SELECT * FROM Dept;


--> CREATE SALGRADE TABLE

CREATE TABLE salgrade(grade number(2),losal number(4),hisal number(4));

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



-->CREATE DUEL TABLE

CREATE TABLE Dual
(
ENAME VARCHAR2(5)
);
INSERT INTO Dual VALUES('RAJU');

--> Create Dummy tables
create table s as SELECT * FROM salgrade;
create table dp as SELECT * FROM dept;
create table d as SELECT * FROM dual;
create table e as SELECT * FROM emp;

select * from emp;
select * from e;
select * from dept;
select * from dp;
select * from dual;
select * from d;
select * from salgrade;
select * from s;





INSERT INTO empstage(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO,status)VALUES(7902,'FORD','ANALYST',7566,'03-DEC-81',3000,NULL,20,'new');

--*explicit null value*/
INSERT INTO empstage(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,DEPTNO,status)VALUES(7698,'BLAKE','MANAGER',7839,'01-MAY-81',2850,30,'new');
--implcit null value*/
INSERT INTO empstage(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO,status)VALUES(7782,'CLARK','MANAGER',7839,'09-JUN-81',2450,NULL,10,'new');
INSERT INTO emp(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO,status)VALUES(7788,'SCOTT','ANALYST',7777,'09-DEC-82',3000,NULL,20,'new');

INSERT INTO empstage(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO,status)VALUES(7521,null,'SALESMAN',7678,'22-FEB-81',1250,500,60,'new');
INSERT INTO empstage(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO,status)VALUES(7839,'KING','PRESIDENT',NULL,'17-NOV-81',5000,NULL,10,'new');
INSERT INTO empstage(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO,status)VALUES(7844,'TURNER','SALESMAN',7698,'08-SEP-81',1500,0,30,'new');
INSERT INTO empstage(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO,status)VALUES(7876,'ADAMS','CLERK',7788,'12-JAN-83',1100,NULL,20,'new');

INSERT INTO empstage(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO,status)VALUES(null,'JONES','programmer',7839,'02-APR-81',9975,NULL,20,'new');
INSERT INTO empstage
(
EMPNO,
ENAME,
JOB,
MGR,
HIREDATE,
SAL,
COMM,
DEPTNO,
status
)
VALUES
(
7369,
'SMITH',
'SALESMAN',
7902,
'17-DEC-80',
1000,
NULL,
20,
'new'
)
/

INSERT INTO empstage(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO,status)VALUES(7902,'JAMES','CLERK',7698,'03-DEC-81',9950,NULL,30,'new');
INSERT INTO empstage(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO,status)VALUES(7654,'MARTIN',null,7698,'28-SEP-81',1250,1400,40,'new');
INSERT INTO empstage(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO,status)VALUES(7902,'FORD','ANALYST',7566,'03-DEC-81',3000,NULL,20,'new');
INSERT INTO empstage(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO,status)VALUES(7934,'MILLER','CLERK',7782,'23-JAN-82',1300,NULL,10,'new');