Setup Sample Schema in Oracle

In this post, we will set up a sample schema with sample tables in the Oracle database. You will learn how to create an Oracle DB user and tables and insert data into tables. SCOTT is a database user whose schema is used for the Oracle demo.

Error:

Copyright (c) 1982, 2010, Oracle. All rights reserved.

Enter user-name: scott
Enter password:
ERROR:
ORA-01017: invalid username/password; logon denied

 

Sample Schema

To set up the schema, we need to create a database user, connect to the schema, and then create some database tables and insert sample data into them.

Create User

To create a database user, connect to the database instance as SYSTEM or as SYSDBA and issue the CREATE USER command. Provide the username and password.

SQL> CREATE USER SCOTT IDENTIFIED BY tiger;

User created.

SQL>

GRANT CONNECT,RESOURCE,UNLIMITED TABLESPACE TO scott;

Grant some privileges to the user.

Create Table

Before creating tables in the Scott schema, we will change the default tablespace for Scott.

 

ALTER USER scott DEFAULT TABLESPACE  users;
ALTER USER scott TEMPORARY TABLESPACE temp;

We will create two tables DEPT and EMP as shown below:

CREATE TABLE DEPT
 (DEPTNO NUMBER(4) CONSTRAINT PK_DEPT PRIMARY KEY,
 DNAME VARCHAR2(20) ,
 LOC VARCHAR2(20) ) ;

CREATE TABLE EMP
 (EMPNO NUMBER(4) CONSTRAINT PK_EMP PRIMARY KEY,
 ENAME VARCHAR2(20),
 JOB VARCHAR2(10),
 MGR NUMBER(4),
 HIREDATE DATE,
 SAL NUMBER(7,2),
 COMM NUMBER(7,2),
DEPTNO NUMBER(4) CONSTRAINT FK_DEPTNO REFERENCES DEPT);

 

Issue the two CREATE TABLE statements to create the tables.

Insert Data

We will now populate the tables with sample data. This data is taken from the legacy Oracle Scott schema with slight modifications.

INSERT INTO DEPT VALUES
 (1000,'ACCOUNTING','NEW YORK');
INSERT INTO DEPT VALUES (2000,'RESEARCH','DALLAS');
INSERT INTO DEPT VALUES
 (3000,'SALES','CHICAGO');
INSERT INTO DEPT VALUES
 (4000,'OPERATIONS','BOSTON');
INSERT INTO EMP VALUES
(7369,'SMITH','CLERK',7902,to_date('17-12-1980','dd-mm-yyyy'),800,NULL,2000);
INSERT INTO EMP VALUES
(7499,'ALLEN','SALESMAN',7698,to_date('20-2-1981','dd-mm-yyyy'),1600,300,3000);
INSERT INTO EMP VALUES
(7521,'WARD','SALESMAN',7698,to_date('22-2-1981','dd-mm-yyyy'),1250,500,3000);
INSERT INTO EMP VALUES
(7566,'JONES','MANAGER',7839,to_date('2-4-1981','dd-mm-yyyy'),2975,NULL,2000);
INSERT INTO EMP VALUES
(7654,'MARTIN','SALESMAN',7698,to_date('28-9-1981','dd-mm-yyyy'),1250,1400,3000);

Query Data

To query data from the tables, run simple SELECT queries against the tables.

SQL> SELECT * FROM dept;

SQL> SELECT * FROM emp;

—

Database Tutorials on this website:

• https://www.testingdocs.com/oracle-database-tutorials-for-beginners/

More information about Oracle Database:

• https://www.oracle.com/database/