How to create oracle scott user ...

GRANT CONNECT,RESOURCE,UNLIMITED TABLESPACE TO SCOTT IDENTIFIED BY TIGER;
ALTER USER SCOTT DEFAULT TABLESPACE USERS;
ALTER USER SCOTT TEMPORARY TABLESPACE TEMP;

CONNECT SCOTT/TIGER

DROP TABLE DEPT;

CREATE TABLE DEPT (DEPTNO NUMBER(2) CONSTRAINT PK_DEPT PRIMARY KEY,
DNAME VARCHAR2(14) ,
LOC VARCHAR2(13) ) ;

DROP TABLE EMP;

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


INSERT INTO EMP VALUES (7369,'SMITH','CLERK',7902,to_date('17-12-1980','dd-mm-yyyy'),800,NULL,20);
INSERT INTO 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,'MARTIN','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-JUL-87')-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')-51,1100,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);

DROP TABLE BONUS;

CREATE TABLE BONUS
(
ENAME VARCHAR2(10) ,
JOB VARCHAR2(9) ,
SAL NUMBER,
COMM NUMBER
) ;

DROP TABLE SALGRADE;

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 SALGRADE VALUES (5,3001,9999);
COMMIT;

SET TERMOUT ON ECHO ON
 

================================

Create EMP and DEPT tables in Oracle


create tablespace test datafile '+DATA' size 10M autoextend on maxsize  200M ;

Tablespace created.


create table dept(
  deptno number   (2,0),
  dname  varchar2 (14),
  loc    varchar2 (13),
  constraint pk_dept primary key (deptno)
)
 tablespace test
;
 
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 emp(
  empno    number(4,0),
  ename    varchar2(10),
  job      varchar2(9),
  mgr      number(4,0),
  hiredate date,
  sal      number(7,2),
  comm     number(7,2),
  deptno   number(2,0),
  constraint pk_emp primary key (empno),
  constraint fk_deptno foreign key (deptno) references dept (deptno)
) 
tablespace test
;

insert into emp values( 7839, 'KING',  'PRESIDENT', null, to_date('17-11-1981','dd-mm-yyyy'), 5000,   null, 10);
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( 7566, 'JONES',  'MANAGER',  7839, to_date('2-4-1981','dd-mm-yyyy'), 2975,     null, 20);
insert into emp values( 7788, 'SCOTT',  'ANALYST',  7566, to_date('13-JUL-87','dd-mm-rr') - 85, 3000, null, 20);
insert into emp values( 7902, 'FORD',   'ANALYST',  7566, to_date('3-12-1981','dd-mm-yyyy'), 3000,    null, 20);
insert into emp values( 7369, 'SMITH',  'CLERK',    7902, to_date('17-12-1980','dd-mm-yyyy'), 800,    null, 20);
insert into 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( 7654, 'MARTIN', 'SALESMAN', 7698, to_date('28-9-1981','dd-mm-yyyy'), 1250, 1400, 30);
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, 1100, 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( 7934, 'MILLER', 'CLERK',    7782, to_date('23-1-1982','dd-mm-yyyy'), 1300,     null, 10);
 

 ====

On default tablespace ..



create table dept(
  deptno number   (2,0),
  dname  varchar2 (14),
  loc    varchar2 (13),
  constraint pk_dept primary key (deptno)
)
-- tablespace test
;
 
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 emp(
  empno    number(4,0),
  ename    varchar2(10),
  job      varchar2(9),
  mgr      number(4,0),
  hiredate date,
  sal      number(7,2),
  comm     number(7,2),
  deptno   number(2,0),
  constraint pk_emp primary key (empno),
  constraint fk_deptno foreign key (deptno) references dept (deptno)
) 
--tablespace test
;

insert into emp values( 7839, 'KING',  'PRESIDENT', null, to_date('17-11-1981','dd-mm-yyyy'), 5000,   null, 10);
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( 7566, 'JONES',  'MANAGER',  7839, to_date('2-4-1981','dd-mm-yyyy'), 2975,     null, 20);
insert into emp values( 7788, 'SCOTT',  'ANALYST',  7566, to_date('13-JUL-87','dd-mm-rr') - 85, 3000, null, 20);
insert into emp values( 7902, 'FORD',   'ANALYST',  7566, to_date('3-12-1981','dd-mm-yyyy'), 3000,    null, 20);
insert into emp values( 7369, 'SMITH',  'CLERK',    7902, to_date('17-12-1980','dd-mm-yyyy'), 800,    null, 20);
insert into 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( 7654, 'MARTIN', 'SALESMAN', 7698, to_date('28-9-1981','dd-mm-yyyy'), 1250, 1400, 30);
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, 1100, 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( 7934, 'MILLER', 'CLERK',    7782, to_date('23-1-1982','dd-mm-yyyy'), 1300,     null, 10);