Setting Up Sample Schema in Oracle

Introduction

In this post, We would set up a sample schema with sample tables in the Oracle database. You will learn how to create an Oracle DB user, 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. Insert sample data into the tables.

Creating User

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

SQL> CREATE USER SCOTT IDENTIFIED BY tiger;

User created.

SQL>

GRANT CONNECT,RESOURCE,UNLIMITED TABLESPACE TO scott;

Scott Oracle User Login

Grant some privileges to the user.

Creating 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.

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

Sample Oracle Schema

 

Database Tutorials on this website:

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

More information about Oracle Database:

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

Related Posts