Create db.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
;

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;

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;