• TestingDocs
TestingDocs.com
Software Testing website
  • Automation
    • Selenium
    • JBehave Framework
  • Tutorials
    • MySQL Tutorials
    • Testlink
    • Maven
    • Git
  • IDEs
    • IntelliJ IDEA
    • Eclipse
  • Flowcharts
    • Flowgorithm
    • Raptor
  • About

Oracle

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

Download SQL Developer

Oracle /

Download Oracle SQL Developer

Oracle Databse 19c Download

Oracle /

Install Oracle 19c Database On Windows 10

ORA-01109 Database Not Open

Oracle /

Fix ORA-01109: database not open Error

Oracle SQL Update Statement

Oracle /

Oracle SQL Update Statement

Oracle SQL Insert Statement

Oracle /

Oracle SQL INSERT Statement

‹ Oracle Integrity Constraints› Introduction to SQL(Structured Query Language)

Recent Posts

  • How to secure your SQL Database: Tips and Tricks
  • Shaping the Future of Development: Exploring Key Trends in Software Engineering
  • Improving Java Performance with Multithreading
  • Difference between PHP and JavaScript?
  • Bing Conversation Styles
  • ChatGPT Introduction
  • Open Source AI Frameworks
  • Artificial Intelligence Tools
  • Top AI Music Applications
  • Top AI Website Design Tools

Back to Top

Links

  • Contact
  • Privacy Policy
  • Cookie Policy

www.TestingDocs.com