Warning: Parameter 1 to Language::getMagic() expected to be a reference, value given in /home/wikija5/public_html/w/includes/StubObject.php on line 58

Warning: Parameter 3 to renderSEO() expected to be a reference, value given in /home/wikija5/public_html/w/includes/parser/Parser.php on line 3243
Create db.sql - WikiJava
Thursday, 24th July 2014
Follow WikiJava on twitter now. @Wikijava

Create db.sql

From WikiJava

Jump to: navigation, search


This is an example of how to create a database schema and a user in oracle 9i.


Contents

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

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

create_company_table.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;

create_account_table.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;


Comments from the users

To be notified via mail on the updates of this discussion you can login and click on watch at the top of the page


Comments on wikijava are disabled now, cause excessive spam.