Edit Article Form: Create db.sql

You have to log in to edit pages.

Fill the table below with the information required. It is important to write correctly the keywords and the categories, so that your article will be easy to find to the other users. We also recommend to click on watch this page so you will be notified every time another user modifies your tutorial page.

Click on the help buttons close to each field if you need help in filling them.

Free text: This is an example of how to create a database schema and a user in oracle 9i. == the article == This simple script illustrates how one can create a user and grant privileges to the user and also how to create a database schema, after creating the database schema we shall generate scripts to create tables in the database and also insert some data into the tables. == create database == <source lang="Sql">--creating a user called 'amutuku' with a password 'amutuku' CREATE USER amutuku IDENTIFIED BY amutuku; COMMIT --Granting privileges to the new user GRANT CREATE SESSION, CREATE VIEW TO amutuku; COMMIT GRANT CONNECT TO amutuku; COMMIT GRANT RESOURCE TO amutuku; COMMIT --connect to the database as the new user CONN amutuku/amutuku; --create a database called 'hr_payroll' CREATE DATABASE hr_payroll2; --save the changes COMMIT ;</source>[[Language::Sql| ]] == create_company_table.sql == <source lang="Sql">REM START CREATE A TABLE CALLED company CREATE TABLE company ( COMP_NO NUMBER(5) NOT NULL, CNAME VARCHAR2(25) NOT NULL, REGNO VARCHAR2(25) NOT NULL, CPIN VARCHAR2(25) NOT NULL, CNHIF VARCHAR2(25) NOT NULL, CNSSF VARCHAR2(25) NOT NULL, CLOC VARCHAR2(100) , CADD VARCHAR2(25) , CPOS_CODE VARCHAR2(25) , C_CITY VARCHAR2(25) , C_COUNTRY VARCHAR2(25) , CPHONE VARCHAR2(20) , CCELL VARCHAR2(20) , CFAX VARCHAR2(15) , CMAIL VARCHAR2(25) , PRIMARY KEY (COMP_NO) ); COMMIT; REM END company REM START CREATE A SEQUENCE CALLED comp_seq CREATE SEQUENCE comp_seq MINVALUE 1 MAXVALUE 999 INCREMENT BY 1 START WITH 1 CACHE 20 ORDER NOCYCLE ; REM END comp_seq COMMIT; REM START CREATE A TRIGGER CALLED comp_trg CREATE OR REPLACE TRIGGER comp_trg before insert on COMPANY for each row begin if inserting then if :NEW.COMP_NO is null then select COMP_SEQ.nextval into :NEW.COMP_NO from dual; end if; end if; end; / ALTER TRIGGER comp_trg ENABLE; REM END comp_trg COMMIT; REM INSERTING DATA INTO THE COMPANY TABLE INSERT INTO company (CNAME,REGNO,CPIN,CNHIF,CNSSF,CLOC,CADD,CPOS_CODE,C_CITY,C_COUNTRY,CPHONE,CCELL,CFAX,CMAIL) VALUES ('Activate','45411','a15545g','8478478456','578979','Nairobi, Mombasa Rd, Visions Plaza Ground Flr','4953','00506','Nairobi','Kenya','020-52494','0722-259541','020-2155223','activate@activate.co.ke'); COMMIT;</source>[[Language::Sql| ]] == create_account_table.sql == <source lang="Sql">REM START CREATE A TABLE CALLED account THE TABLE HAS A FOREIGN KEY THAT REFERENCES THE PRIMARY KEY IN THE COMPANY TABLE. CREATE TABLE account ( ACC_ID NUMBER(5) NOT NULL, COMP_NO NUMBER(5) NOT NULL, BNK_NAME VARCHAR2(30) NOT NULL, BNK_BRANCH VARCHAR2(25) NOT NULL, ACC_NO VARCHAR2(25) NOT NULL, O_DATE DATE , PRIMARY KEY (ACC_ID), FOREIGN KEY (COMP_NO) REFERENCES COMPANY (COMP_NO) ); COMMIT; REM END account REM CREATE A SEQUENCE CALLED acc_seq CREATE SEQUENCE acc_seq MINVALUE 0 MAXVALUE 99 INCREMENT BY 1 START WITH 1 CACHE 20 ORDER NOCYCLE ; REM END acc_seq COMMIT; REM START CREATE A TRIGGER CALLED acc_trg CREATE OR REPLACE TRIGGER acc_trg before insert on ACCOUNT for each row begin if inserting then if :NEW."ACC_ID" is null then select ACC_SEQ.nextval into :NEW.ACC_ID from dual; end if; end if; end; / ALTER TRIGGER acc_trg ENABLE; REM END acc_trg COMMIT; REM INSERTING DATA INTO THE ACCOUNT TABLE INSERT INTO account (COMP_NO,BNK_NAME,BNK_BRANCH,ACC_NO,O_DATE) VALUES (1,'Stanbic','Industrial Area','45421158',to_date('12-JUN-06','DD-MON-RR')); COMMIT; INSERT INTO account (COMP_NO,BNK_NAME,BNK_BRANCH,ACC_NO,O_DATE) VALUES (1,'KCB','Kencom House','1545454',to_date('20-SEP-04','DD-MON-RR')); COMMIT; INSERT INTO account (COMP_NO,BNK_NAME,BNK_BRANCH,ACC_NO,O_DATE) VALUES (1,'Barclays','Queensway','1452546',to_date('15-FEB-05','DD-MON-RR')); COMMIT;</source>[[Language::Sql| ]]

Please note that all contributions to WikiJava are considered to be released under the GNU Free Documentation License 1.2 (see Project:Copyrights for details). If you don't want your writing to be edited mercilessly and redistributed at will, then don't submit it here.
You are also promising us that you wrote this yourself, or copied it from a public domain or similar free resource. DO NOT SUBMIT COPYRIGHTED WORK WITHOUT PERMISSION!

Summary:

This is a minor edit Watch this page

Cancel