Sunday, September 16, 2018

Sample Oracle Database for Learning SQL

1- Introduction

LearningSQL is a small database, used as an example in the instructions of learning SQL on the website of  o7planning. There are 3 version on Databases:
  • Oracle
  • MySQL
  • SQLServer.
In this document, I will instruct you to create this database on Oracle.
This database is used in the documentation to learn SQL (Oracle) at:

2- Download Script

Download script at:
Direct Mediafire
Download Download
With Oracle, you just need to care about the file:
  • LearningSQL-Oracle-Script.sql

3- Run Script

There are two ways to create SCHEMA: LearningSQL
  1. Using SQL Plus
  2. Using visual tools.

3.1- Create SCHEMA LearningSQL using SQL Plus

Run 2 commands:

1
2
3
4
5
6
-- Create Schema named learningsql,  password 1234
Create user learningsql identified by 1234;
 
-- For learning purposes:
-- Grant highest privilege DBA (DB Admin) for the newly created user.
grant dba to learningsql;
Connect to the newly created SCHEMA:

1
2
3
4
-- Connect to Schema
-- Syntax: connect /@
 
connect learningsql/1234@db11g;

Run Script:


1
2
3
-- Run Script file in SQL Plus
 
@F:\LEARNING_SQL\LearningSQL-Oracle-Script.sql
Script run successfully:

3.2- Create SCHEMA LearningSQL using visual tool

On the visual tools (Here I use PL/SQL Developer), connected to the user with DBA authority (for example: system).
See more:
Execute script to create schema: learningsql.

1
2
3
4
5
6
7
8
-- Create schema named learningsql
 
Create user learningsql identified by 1234;
 
-- For learning purposes:
-- Grant highest privilege DBA (DB Admin) for the newly created user.
 
grant dba to learningsql;
Connect to Schema you just created:
Copy content of LearningSQL-Oracle-Script.sql into PL/SQL Developer and execute.
Create Schema success.

4- Overview LearningSQL Database

LearningSQL is a small database simulating the data of a bank:
Table Name Description
ACCOUNT The table saves the bank accounts. Each of customer can register multiple accounts each of which corresponds to a product type provided by the bank.
(See more: PRODUCT)
ACC_TRANSACTION The table saves the transaction history between the bank and a certain account.
BRANCH The branch of bank.
BUSSINESS  
CUSTOMER The table of customers
DEPARTMENT The department table of bank
EMPLOYEE The employee table of bank
OFFICER  
PRODUCT The products and services of bank, such as:
  • Savings account
  • The business lines of credit
  • The loans to small business
  • .....
PRODUCT_TYPE The types of products and services, such as:
  • Bank acounts
  • Loans to individual and business
  • The provider of insurance.

5- The structure of the tables


5.1- ACCOUNT

5.2- ACC_TRANSACTION

5.3- BRANCH

5.4- BUSINESS

5.5- CUSTOMER

5.6- DEPARTMENT

5.7- EMPLOYEE

5.8- INDIVIDUAL

5.9- OFFICER

5.10- PRODUCT

5.11- PRODUCT_TYPE

No comments:

Post a Comment