Need a unique gift idea?
A Pastebin account makes a great Christmas gift
SHARE
TWEET

Untitled

a guest Jan 12th, 2018 275 Never
Upgrade to PRO!
ENDING IN00days00hours00mins00secs
 
  1. DROP TABLE PENDING_JOB_REQ;
  2. DROP TABLE PENDING_SIM_REQ;
  3. DROP TABLE AVAILABLE_PREPAID_SIM;
  4. DROP TABLE AVAILABLE_POSTPAID_SIM;
  5. DROP TABLE CALL_DATABASE;
  6. DROP TABLE SMS_DATABASE;
  7. DROP TABLE NET_USAGE_DATABASE;
  8. DROP TABLE POSTPAID_BILL ;
  9. DROP TABLE PREPAID_SIM ;
  10. DROP TABLE POSTPAID_SIM ;
  11. DROP TABLE SIM ;
  12. DROP TABLE SIM_TYPE ;
  13. DROP TABLE INTER_SLIP_BONUS;
  14. DROP TABLE ADDITIONAL_SALARY;
  15. DROP TABLE PAYSLIP;
  16. DROP TABLE EMPLOYEES;
  17. DROP TABLE JOBS;
  18. DROP TABLE USER_DATA;
  19. DROP TABLE PASSWORDS;
  20. DROP TABLE OFFER_PACKAGES;
  21.  
  22.  
  23. --------------------------------packages start---------------------------------------
  24.  
  25. CREATE TABLE OFFER_PACKAGES(
  26.     PACK_ID NUMBER PRIMARY KEY ,
  27.     PACK_NAME VARCHAR2(100) UNIQUE ,
  28.     START_TIME NUMBER , -- 24 clock hour
  29.     END_TIME NUMBER,
  30.     CALL_COST NUMBER ,
  31.     SMS_COST NUMBER ,
  32.     NET_COST NUMBER
  33. );
  34.  
  35. INSERT INTO OFFER_PACKAGES(PACK_ID, PACK_NAME, START_TIME, END_TIME, CALL_COST, SMS_COST, NET_COST) VALUES(1,'PAY-PER-USE', 0,23, 1, 1, 1);
  36. INSERT INTO OFFER_PACKAGES(PACK_ID, PACK_NAME, START_TIME, END_TIME, CALL_COST, SMS_COST, NET_COST) VALUES(2,'DESH', 10,18, 0.5, 2, 1);
  37. INSERT INTO OFFER_PACKAGES(PACK_ID, PACK_NAME, START_TIME, END_TIME, CALL_COST, SMS_COST, NET_COST) VALUES(3,'BIJOY',06,22, 1,1,0.5);
  38. INSERT INTO OFFER_PACKAGES(PACK_ID, PACK_NAME, START_TIME, END_TIME, CALL_COST, SMS_COST, NET_COST) VALUES(4,'EKUSH',00,12, 0.35, 1.5, 1);
  39.  
  40.  
  41.  
  42. --------------------------------packages end---------------------------------------
  43.  
  44.  
  45.  
  46.  
  47. ---------------------------------AVAILaBLE PASSWORD START --------------------------------
  48.  
  49. CREATE TABLE PASSWORDS(
  50.     USER_PASSWORD VARCHAR2(100) PRIMARY KEY
  51. );
  52.  
  53. ---------------------------------AVAILaBLE PASSWORD END --------------------------------
  54.  
  55. -------------------------USER Table start------------------------------------------
  56. CREATE TABLE USER_DATA (
  57.   USER_NAME VARCHAR2(100) NOT NULL,
  58.   NID_NO number CONSTRAINT USER_PK PRIMARY KEY,
  59.   DATE_OF_BIRTH VARCHAR2(100) NOT NULL,
  60.   NAME_OF_FATHER VARCHAR2(100) NOT NULL,
  61.   NAME_OF_MOTHER VARCHAR2(100) NOT NULL,
  62.   STREET_ADDRESS VARCHAR2(100) NOT NULL,
  63.   CITY VARCHAR2(100) NOT NULL,
  64.   IS_EMPLOYEE VARCHAR2(100) NOT NULL,
  65.   USER_PASSWORD VARCHAR2(100) UNIQUE
  66. );
  67.  
  68. INSERT INTO USER_DATA (USER_NAME,NID_NO,DATE_OF_BIRTH,NAME_OF_FATHER,NAME_OF_MOTHER,STREET_ADDRESS,CITY,IS_EMPLOYEE,USER_PASSWORD) VALUES ('Jacob',1878,'Feb 12, 1971','Leonard','Jayme','Ap #925-8567 Nulla Rd.','Montpellier','NO','U9RK1');
  69. INSERT INTO USER_DATA (USER_NAME,NID_NO,DATE_OF_BIRTH,NAME_OF_FATHER,NAME_OF_MOTHER,STREET_ADDRESS,CITY,IS_EMPLOYEE,USER_PASSWORD) VALUES ('Octavius',1879,'Apr 19, 1939','Vance','Debra','7308 Ligula Av.','Aguacaliente (San Francisco)','NO','U1CK9');
  70.                                                                                                                    
  71. INSERT INTO USER_DATA (USER_NAME,NID_NO,DATE_OF_BIRTH,NAME_OF_FATHER,NAME_OF_MOTHER,STREET_ADDRESS,CITY,IS_EMPLOYEE,USER_PASSWORD) VALUES ('Larissa',1880,'Jan 12, 1935','Leonard','Serina','863-3484 Urna. Avenue','Curacautín','NO','U5CU1');
  72. INSERT INTO USER_DATA (USER_NAME,NID_NO,DATE_OF_BIRTH,NAME_OF_FATHER,NAME_OF_MOTHER,STREET_ADDRESS,CITY,IS_EMPLOYEE,USER_PASSWORD) VALUES ('Winter',1881,'Aug 25, 1952','Holmes','Yael','Ap #534-3655 Nulla Rd.','Henderson','NO','H1MQ7');
  73.                                                                                                                    
  74. INSERT INTO USER_DATA (USER_NAME,NID_NO,DATE_OF_BIRTH,NAME_OF_FATHER,NAME_OF_MOTHER,STREET_ADDRESS,CITY,IS_EMPLOYEE,USER_PASSWORD) VALUES ('Valentine',1882,'Jun 3, 1940','Guy','Octavia','P.O. Box 190, 361 Lectus St.','Tobermory','NO','H0LH9');
  75. INSERT INTO USER_DATA (USER_NAME,NID_NO,DATE_OF_BIRTH,NAME_OF_FATHER,NAME_OF_MOTHER,STREET_ADDRESS,CITY,IS_EMPLOYEE,USER_PASSWORD) VALUES ('Molly',1883,'Nov 22, 1965','Hashim','Alexa','6502 Massa. Street','San Nicolás','NO','Y3SR3');
  76. INSERT INTO USER_DATA (USER_NAME,NID_NO,DATE_OF_BIRTH,NAME_OF_FATHER,NAME_OF_MOTHER,STREET_ADDRESS,CITY,IS_EMPLOYEE,USER_PASSWORD) VALUES ('Abbot',1884,'Dec 8, 1986','Benedict','Rina','Ap #577-8748 Tortor, Street','Sauvenire','NO','S3QK9');
  77.                                                                                                                    
  78. INSERT INTO USER_DATA (USER_NAME,NID_NO,DATE_OF_BIRTH,NAME_OF_FATHER,NAME_OF_MOTHER,STREET_ADDRESS,CITY,IS_EMPLOYEE,USER_PASSWORD) VALUES ('Tashya',1885,'Mar 30, 1954','Lev','Sasha','P.O. Box 331, 1269 Metus St.','Bolzano Vicentino','YES','J8FD7');
  79. INSERT INTO USER_DATA (USER_NAME,NID_NO,DATE_OF_BIRTH,NAME_OF_FATHER,NAME_OF_MOTHER,STREET_ADDRESS,CITY,IS_EMPLOYEE,USER_PASSWORD) VALUES ('Harriet',1886,'Jul 12, 1918','Rashad','Melyssa','2921 Sit St.','Millesimo','YES','P1PQ0');
  80. INSERT INTO USER_DATA (USER_NAME,NID_NO,DATE_OF_BIRTH,NAME_OF_FATHER,NAME_OF_MOTHER,STREET_ADDRESS,CITY,IS_EMPLOYEE,USER_PASSWORD) VALUES ('Salvador',1887,'Jul 26, 1975','Vance','Sara','P.O. Box 120, 2384 A St.','Mulhouse','YES','K5DG1');
  81. INSERT INTO USER_DATA (USER_NAME,NID_NO,DATE_OF_BIRTH,NAME_OF_FATHER,NAME_OF_MOTHER,STREET_ADDRESS,CITY,IS_EMPLOYEE,USER_PASSWORD) VALUES ('Tamara',1888,'Jan 21, 2001','Kaseem','Oprah','862 Nisl Road','Pelotas','YES','Z7UW2');
  82. INSERT INTO USER_DATA (USER_NAME,NID_NO,DATE_OF_BIRTH,NAME_OF_FATHER,NAME_OF_MOTHER,STREET_ADDRESS,CITY,IS_EMPLOYEE,USER_PASSWORD) VALUES ('Gannon',1889,'Jan 10, 1968','Price','Nola','6692 Consequat St.','Kanpur','YES','W0TD0');
  83. -------------------------USER Table end------------------------------------------
  84.  
  85. -------------------------SIM Type Start---------------------------------------
  86. CREATE TABLE SIM_TYPE(
  87.     TYPE_ID number PRIMARY KEY,
  88.     TYPE_NAME VARCHAR2(100) NOT NULL
  89. );
  90.  
  91. INSERT INTO SIM_TYPE(TYPE_ID,TYPE_NAME) VALUES (1,'PRE-PAID');
  92. INSERT INTO SIM_TYPE(TYPE_ID,TYPE_NAME) VALUES (2,'POST-PAID');
  93.  
  94. -----------------------SIM Type End---------------------------------------------
  95.  
  96. -----------------------SIM Table Start---------------------------------------------
  97.  
  98. CREATE TABLE SIM (
  99.   PHONE_NO varchar2(100) CONSTRAINT PK PRIMARY KEY,
  100.   NID_NO number NOT NULL,
  101.   TYPE_ID number NOT NULL,
  102.   ISSUE_DATE TIMESTAMP NOT NULL,
  103.   ISSUED_BY varchar2(100) NOT NULL,
  104.   CURRENT_PACKAGE_ID NUMBER DEFAULT 1,
  105.   CONSTRAINT NID_PK FOREIGN KEY (NID_NO) REFERENCES USER_DATA (NID_NO) ON DELETE CASCADE,
  106.   CONSTRAINT TYPE_ID_FK FOREIGN KEY (TYPE_ID) REFERENCES SIM_TYPE(TYPE_ID) ON DELETE CASCADE ,
  107.   CONSTRAINT CURRENT_PACK_FK FOREIGN KEY (CURRENT_PACKAGE_ID) REFERENCES OFFER_PACKAGES(PACK_ID) ON DELETE CASCADE
  108. );
  109.  
  110. INSERT INTO SIM (PHONE_NO,NID_NO,TYPE_ID,ISSUE_DATE,ISSUED_BY) VALUES ('01935849658',1878,1,TO_DATE('1997-01-31 09:26:50','yyyy/mm/dd hh24:mi:ss'),104);
  111. INSERT INTO SIM (PHONE_NO,NID_NO,TYPE_ID,ISSUE_DATE,ISSUED_BY) VALUES ('01992409859',1879,1,TO_DATE('1997-01-31 09:26:50','yyyy/mm/dd hh24:mi:ss'),105);
  112. ----------------------------------------just prepaid----------------------------------------
  113. INSERT INTO SIM (PHONE_NO,NID_NO,TYPE_ID,ISSUE_DATE,ISSUED_BY) VALUES ('01960850638',1880,2,TO_DATE('1997-01-31 09:26:50','yyyy/mm/dd hh24:mi:ss'),104);
  114. INSERT INTO SIM (PHONE_NO,NID_NO,TYPE_ID,ISSUE_DATE,ISSUED_BY) VALUES ('01965550938',1881,2,TO_DATE('1997-01-31 09:26:50','yyyy/mm/dd hh24:mi:ss'),104);
  115. ---------------------------------------- just post paid-------------------------------------
  116. INSERT INTO SIM (PHONE_NO,NID_NO,TYPE_ID,ISSUE_DATE,ISSUED_BY) VALUES ('01993969922',1882,1,TO_DATE('1997-01-31 09:26:50','yyyy/mm/dd hh24:mi:ss'),105);
  117. INSERT INTO SIM (PHONE_NO,NID_NO,TYPE_ID,ISSUE_DATE,ISSUED_BY) VALUES ('01980184234',1883,1,TO_DATE('1997-01-31 09:26:50','yyyy/mm/dd hh24:mi:ss'),104);
  118. INSERT INTO SIM (PHONE_NO,NID_NO,TYPE_ID,ISSUE_DATE,ISSUED_BY) VALUES ('01936994382',1884,1,TO_DATE('1997-01-31 09:26:50','yyyy/mm/dd hh24:mi:ss'),105);
  119. ---------------------------------------- mix prepaid ------------------------------      
  120. INSERT INTO SIM (PHONE_NO,NID_NO,TYPE_ID,ISSUE_DATE,ISSUED_BY) VALUES ('01976862069',1882,2,TO_DATE('1997-01-31 09:26:50','yyyy/mm/dd hh24:mi:ss'),104);
  121. INSERT INTO SIM (PHONE_NO,NID_NO,TYPE_ID,ISSUE_DATE,ISSUED_BY) VALUES ('01990275361',1883,2,TO_DATE('1997-01-31 09:26:50','yyyy/mm/dd hh24:mi:ss'),104);
  122. INSERT INTO SIM (PHONE_NO,NID_NO,TYPE_ID,ISSUE_DATE,ISSUED_BY) VALUES ('01937883634',1884,2,TO_DATE('1997-01-31 09:26:50','yyyy/mm/dd hh24:mi:ss'),105);
  123. -----------------------------------------mix postpaid-------------------------------------
  124. INSERT INTO SIM (PHONE_NO,NID_NO,TYPE_ID,ISSUE_DATE,ISSUED_BY) VALUES ('01937303098',1885,2,TO_DATE('1997-01-31 09:26:50','yyyy/mm/dd hh24:mi:ss'),104);
  125. INSERT INTO SIM (PHONE_NO,NID_NO,TYPE_ID,ISSUE_DATE,ISSUED_BY) VALUES ('01911071571',1886,2,TO_DATE('1997-01-31 09:26:50','yyyy/mm/dd hh24:mi:ss'),105);
  126. INSERT INTO SIM (PHONE_NO,NID_NO,TYPE_ID,ISSUE_DATE,ISSUED_BY) VALUES ('01990122106',1887,2,TO_DATE('1997-01-31 09:26:50','yyyy/mm/dd hh24:mi:ss'),105);
  127. INSERT INTO SIM (PHONE_NO,NID_NO,TYPE_ID,ISSUE_DATE,ISSUED_BY) VALUES ('01950134157',1888,2,TO_DATE('1997-01-31 09:26:50','yyyy/mm/dd hh24:mi:ss'),104);
  128. INSERT INTO SIM (PHONE_NO,NID_NO,TYPE_ID,ISSUE_DATE,ISSUED_BY) VALUES ('01967328196',1889,2,TO_DATE('1997-01-31 09:26:50','yyyy/mm/dd hh24:mi:ss'),104);
  129.  
  130. ------------------------------------------employee-------------------------------
  131. -----------------------------------SIM Table End-------------------------------------------
  132.  
  133.  
  134. -----------------------------------Pre-Paid SIM Table start-------------------------------------------
  135. CREATE TABLE PREPAID_SIM (
  136.   SIM_NO varchar2(100) PRIMARY KEY,
  137.   TOTAL_CALL_DURATION NUMBER NOT NULL,
  138.   TOTAL_MESSAGE_SENT NUMBER NOT NULL,
  139.   TOTAL_INTERNET_USAGE NUMBER NOT NULL,
  140.   BALANCE NUMBER NOT NULL CONSTRAINT BALANCE_CK CHECK(BALANCE >= 0) ,
  141.   CONSTRAINT FK3 FOREIGN KEY (SIM_NO) REFERENCES SIM(PHONE_NO) ON DELETE CASCADE
  142. );
  143.  
  144. INSERT INTO PREPAID_SIM (SIM_NO,TOTAL_CALL_DURATION ,TOTAL_MESSAGE_SENT,TOTAL_INTERNET_USAGE,BALANCE) VALUES ('01935849658',12,58,1251,373);
  145. INSERT INTO PREPAID_SIM (SIM_NO,TOTAL_CALL_DURATION ,TOTAL_MESSAGE_SENT,TOTAL_INTERNET_USAGE,BALANCE) VALUES ('01992409859',25,15,1329,98);
  146. ------------------just prepaid---------------------
  147. INSERT INTO PREPAID_SIM (SIM_NO,TOTAL_CALL_DURATION ,TOTAL_MESSAGE_SENT,TOTAL_INTERNET_USAGE,BALANCE) VALUES ('01993969922',192,44,3279,407);
  148. INSERT INTO PREPAID_SIM (SIM_NO,TOTAL_CALL_DURATION ,TOTAL_MESSAGE_SENT,TOTAL_INTERNET_USAGE,BALANCE) VALUES ('01980184234',227,60,2509,80);
  149. INSERT INTO PREPAID_SIM (SIM_NO,TOTAL_CALL_DURATION ,TOTAL_MESSAGE_SENT,TOTAL_INTERNET_USAGE,BALANCE) VALUES ('01936994382',554,62,56,211);
  150. --------------------mix prepaid-----------------------
  151. -----------------------------------Pre-Paid SIM Table End-------------------------------------------
  152.  
  153.  
  154.  
  155. -----------------------------------Post-Paid SIM Table Start-------------------------------------------
  156. CREATE TABLE POSTPAID_SIM (
  157.   SIM_NO varchar2(100) PRIMARY KEY,
  158.   TOTAL_CALL_DURATION NUMBER NOT NULL,
  159.   TOTAL_MESSAGE_SENT NUMBER NOT NULL,
  160.   TOTAL_INTERNET_USAGE NUMBER NOT NULL,
  161.   CONSTRAINT POST_FK FOREIGN KEY (SIM_NO) REFERENCES SIM(PHONE_NO) ON DELETE CASCADE
  162. );
  163.  
  164. INSERT INTO POSTPAID_SIM(SIM_NO,TOTAL_CALL_DURATION,TOTAL_MESSAGE_SENT,TOTAL_INTERNET_USAGE) VALUES ('01960850638',12,59,1251);
  165. INSERT INTO POSTPAID_SIM(SIM_NO,TOTAL_CALL_DURATION,TOTAL_MESSAGE_SENT,TOTAL_INTERNET_USAGE) VALUES ('01965550938',25,35,1329);
  166. --------------------------just post-----------------------                                                        
  167. INSERT INTO POSTPAID_SIM(SIM_NO,TOTAL_CALL_DURATION,TOTAL_MESSAGE_SENT,TOTAL_INTERNET_USAGE) VALUES ('01976862069',19,44,3279);
  168. INSERT INTO POSTPAID_SIM(SIM_NO,TOTAL_CALL_DURATION,TOTAL_MESSAGE_SENT,TOTAL_INTERNET_USAGE) VALUES ('01990275361',22,60,2509);
  169. INSERT INTO POSTPAID_SIM(SIM_NO,TOTAL_CALL_DURATION,TOTAL_MESSAGE_SENT,TOTAL_INTERNET_USAGE) VALUES ('01937883634',55,62,56  );
  170. ---------------------------mix post-------------------------
  171. INSERT INTO POSTPAID_SIM(SIM_NO,TOTAL_CALL_DURATION,TOTAL_MESSAGE_SENT,TOTAL_INTERNET_USAGE) VALUES ('01937303098',12,85,4088);
  172. INSERT INTO POSTPAID_SIM(SIM_NO,TOTAL_CALL_DURATION,TOTAL_MESSAGE_SENT,TOTAL_INTERNET_USAGE) VALUES ('01911071571',25,77,463);
  173. INSERT INTO POSTPAID_SIM(SIM_NO,TOTAL_CALL_DURATION,TOTAL_MESSAGE_SENT,TOTAL_INTERNET_USAGE) VALUES ('01990122106',62,1,10);
  174. INSERT INTO POSTPAID_SIM(SIM_NO,TOTAL_CALL_DURATION,TOTAL_MESSAGE_SENT,TOTAL_INTERNET_USAGE) VALUES ('01950134157',19,1,0);
  175. INSERT INTO POSTPAID_SIM(SIM_NO,TOTAL_CALL_DURATION,TOTAL_MESSAGE_SENT,TOTAL_INTERNET_USAGE) VALUES ('01967328196',22,1,0);
  176. --------------------------employee---------------------------                                                      
  177. -----------------------------------Post-Paid SIM Table End-------------------------------------------
  178.  
  179.  
  180.  
  181. -----------------------------------Call Database Start-------------------------------------------
  182. CREATE TABLE CALL_DATABASE (
  183.   CALL_ID NUMBER PRIMARY KEY,
  184.   CALLER_NO varchar2(100) NOT NULL,
  185.   RECEIVER_NO varchar2(100) NOT NULL,
  186.   STARTING_TIME TIMESTAMP,
  187.   ENDING_TIME TIMESTAMP,
  188.   CONSTRAINT FK1 FOREIGN KEY (CALLER_NO) REFERENCES SIM(PHONE_NO) ON DELETE CASCADE
  189. );
  190.  
  191. INSERT INTO CALL_DATABASE (CALL_ID,CALLER_NO,RECEIVER_NO,STARTING_TIME,ENDING_TIME) VALUES (1,'01935849658','01893969922', TO_DATE('1997-01-31 09:26:50','yyyy/mm/dd hh24:mi:ss'),TO_DATE('1997-01-31 09:26:50','yyyy/mm/dd hh24:mi:ss'));
  192. INSERT INTO CALL_DATABASE (CALL_ID,CALLER_NO,RECEIVER_NO,STARTING_TIME,ENDING_TIME) VALUES (2,'01960850638','01980184234', TO_DATE('1997-01-31 09:26:50','yyyy/mm/dd hh24:mi:ss'),TO_DATE('1997-01-31 09:26:50','yyyy/mm/dd hh24:mi:ss'));                                                                                                  
  193. INSERT INTO CALL_DATABASE (CALL_ID,CALLER_NO,RECEIVER_NO,STARTING_TIME,ENDING_TIME) VALUES (3,'01960850638','01936994382', TO_DATE('1997-01-31 09:26:50','yyyy/mm/dd hh24:mi:ss'),TO_DATE('1997-01-31 09:26:50','yyyy/mm/dd hh24:mi:ss'));
  194. INSERT INTO CALL_DATABASE (CALL_ID,CALLER_NO,RECEIVER_NO,STARTING_TIME,ENDING_TIME) VALUES (4,'01965550938','01710162106', TO_DATE('1997-01-31 09:26:50','yyyy/mm/dd hh24:mi:ss'),TO_DATE('1997-01-31 09:26:50','yyyy/mm/dd hh24:mi:ss'));
  195. INSERT INTO CALL_DATABASE (CALL_ID,CALLER_NO,RECEIVER_NO,STARTING_TIME,ENDING_TIME) VALUES (5,'01993969922','01943306287', TO_DATE('1997-01-31 09:26:50','yyyy/mm/dd hh24:mi:ss'),TO_DATE('1997-01-31 09:26:50','yyyy/mm/dd hh24:mi:ss'));
  196. INSERT INTO CALL_DATABASE (CALL_ID,CALLER_NO,RECEIVER_NO,STARTING_TIME,ENDING_TIME) VALUES (6,'01980184234','01504115381', TO_DATE('1997-01-31 09:26:50','yyyy/mm/dd hh24:mi:ss'),TO_DATE('1997-01-31 09:26:50','yyyy/mm/dd hh24:mi:ss'));
  197. INSERT INTO CALL_DATABASE (CALL_ID,CALLER_NO,RECEIVER_NO,STARTING_TIME,ENDING_TIME) VALUES (7,'01990275361','01600007434', TO_DATE('1997-01-31 09:26:50','yyyy/mm/dd hh24:mi:ss'),TO_DATE('1997-01-31 09:26:50','yyyy/mm/dd hh24:mi:ss'));
  198. INSERT INTO CALL_DATABASE (CALL_ID,CALLER_NO,RECEIVER_NO,STARTING_TIME,ENDING_TIME) VALUES (8,'01976862069','01862642555', TO_DATE('1997-01-31 09:26:50','yyyy/mm/dd hh24:mi:ss'),TO_DATE('1997-01-31 09:26:50','yyyy/mm/dd hh24:mi:ss'));                                                                                                                                                                                                                                                      
  199. INSERT INTO CALL_DATABASE (CALL_ID,CALLER_NO,RECEIVER_NO,STARTING_TIME,ENDING_TIME) VALUES (9, '01990275361','01976862069',TO_DATE('1997-01-31 09:26:50','yyyy/mm/dd hh24:mi:ss'),TO_DATE('1997-01-31 09:26:50','yyyy/mm/dd hh24:mi:ss'));
  200. INSERT INTO CALL_DATABASE (CALL_ID,CALLER_NO,RECEIVER_NO,STARTING_TIME,ENDING_TIME) VALUES (10,'01937883634','01990275361',TO_DATE('1997-01-31 09:26:50','yyyy/mm/dd hh24:mi:ss'),TO_DATE('1997-01-31 09:26:50','yyyy/mm/dd hh24:mi:ss'));
  201. INSERT INTO CALL_DATABASE (CALL_ID,CALLER_NO,RECEIVER_NO,STARTING_TIME,ENDING_TIME) VALUES (11,'01937883634','01937883634',TO_DATE('1997-01-31 09:26:50','yyyy/mm/dd hh24:mi:ss'),TO_DATE('1997-01-31 09:26:50','yyyy/mm/dd hh24:mi:ss'));
  202. INSERT INTO CALL_DATABASE (CALL_ID,CALLER_NO,RECEIVER_NO,STARTING_TIME,ENDING_TIME) VALUES (12,'01937883634','01793969922',TO_DATE('1997-01-31 09:26:50','yyyy/mm/dd hh24:mi:ss'),TO_DATE('1997-01-31 09:26:50','yyyy/mm/dd hh24:mi:ss'));
  203. INSERT INTO CALL_DATABASE (CALL_ID,CALLER_NO,RECEIVER_NO,STARTING_TIME,ENDING_TIME) VALUES (13,'01993969922','01980184234',TO_DATE('1997-01-31 09:26:50','yyyy/mm/dd hh24:mi:ss'),TO_DATE('1997-01-31 09:26:50','yyyy/mm/dd hh24:mi:ss'));
  204. INSERT INTO CALL_DATABASE (CALL_ID,CALLER_NO,RECEIVER_NO,STARTING_TIME,ENDING_TIME) VALUES (14,'01980184234','01936994382',TO_DATE('1997-01-31 09:26:50','yyyy/mm/dd hh24:mi:ss'),TO_DATE('1997-01-31 09:26:50','yyyy/mm/dd hh24:mi:ss'));
  205. INSERT INTO CALL_DATABASE (CALL_ID,CALLER_NO,RECEIVER_NO,STARTING_TIME,ENDING_TIME) VALUES (15,'01993969922','01837303098',TO_DATE('1997-01-31 09:26:50','yyyy/mm/dd hh24:mi:ss'),TO_DATE('1997-01-31 09:26:50','yyyy/mm/dd hh24:mi:ss'));
  206. INSERT INTO CALL_DATABASE (CALL_ID,CALLER_NO,RECEIVER_NO,STARTING_TIME,ENDING_TIME) VALUES (16,'01980184234','01911071571',TO_DATE('1997-01-31 09:26:50','yyyy/mm/dd hh24:mi:ss'),TO_DATE('1997-01-31 09:26:50','yyyy/mm/dd hh24:mi:ss'));                                                                                                                                                                                                                                                          
  207. INSERT INTO CALL_DATABASE (CALL_ID,CALLER_NO,RECEIVER_NO,STARTING_TIME,ENDING_TIME) VALUES (17,'01992409859','01990122106',TO_DATE('1997-01-31 09:26:50','yyyy/mm/dd hh24:mi:ss'),TO_DATE('1997-01-31 09:26:50','yyyy/mm/dd hh24:mi:ss'));
  208.  
  209. -----------------------------------Call Database End-------------------------------------------
  210.  
  211.  
  212. -----------------------------------SMS Database Start-------------------------------------------
  213. CREATE TABLE SMS_DATABASE (
  214.   SMS_ID varchar2(100) CONSTRAINT sms_PK PRIMARY KEY,
  215.   SENDER_NO varchar2(100) ,
  216.   RECEIVER_NO varchar2(100) NOT NULL,
  217.   SENDING_TIME TIMESTAMP,
  218.   CONSTRAINT sms_FK2 FOREIGN KEY (SENDER_NO) REFERENCES SIM(PHONE_NO) ON DELETE CASCADE
  219. );
  220.  
  221. INSERT INTO SMS_DATABASE (SMS_ID,SENDER_NO,RECEIVER_NO,SENDING_TIME) VALUES (1,'01935849658','01893969922', TO_DATE('1997-01-31 09:26:50','yyyy/mm/dd hh24:mi:ss'));
  222. INSERT INTO SMS_DATABASE (SMS_ID,SENDER_NO,RECEIVER_NO,SENDING_TIME) VALUES (2,'01960850638','01980184234', TO_DATE('1997-01-31 09:26:50','yyyy/mm/dd hh24:mi:ss'));
  223. INSERT INTO SMS_DATABASE (SMS_ID,SENDER_NO,RECEIVER_NO,SENDING_TIME) VALUES (3,'01960850638','01936994382', TO_DATE('1997-01-31 09:26:50','yyyy/mm/dd hh24:mi:ss'));
  224. INSERT INTO SMS_DATABASE (SMS_ID,SENDER_NO,RECEIVER_NO,SENDING_TIME) VALUES (4,'01965550938','01710162106', TO_DATE('1997-01-31 09:26:50','yyyy/mm/dd hh24:mi:ss'));
  225. INSERT INTO SMS_DATABASE (SMS_ID,SENDER_NO,RECEIVER_NO,SENDING_TIME) VALUES (5,'01993969922','01943306287', TO_DATE('1997-01-31 09:26:50','yyyy/mm/dd hh24:mi:ss'));
  226. INSERT INTO SMS_DATABASE (SMS_ID,SENDER_NO,RECEIVER_NO,SENDING_TIME) VALUES (6,'01980184234','01504115381', TO_DATE('1997-01-31 09:26:50','yyyy/mm/dd hh24:mi:ss'));
  227. INSERT INTO SMS_DATABASE (SMS_ID,SENDER_NO,RECEIVER_NO,SENDING_TIME) VALUES (7,'01990275361','01600007434', TO_DATE('1997-01-31 09:26:50','yyyy/mm/dd hh24:mi:ss'));
  228. INSERT INTO SMS_DATABASE (SMS_ID,SENDER_NO,RECEIVER_NO,SENDING_TIME) VALUES (8,'01976862069','01862642555', TO_DATE('1997-01-31 09:26:50','yyyy/mm/dd hh24:mi:ss'));                                                                                                                    
  229. INSERT INTO SMS_DATABASE (SMS_ID,SENDER_NO,RECEIVER_NO,SENDING_TIME) VALUES (9, '01990275361','01976862069',TO_DATE('1997-01-31 09:26:50','yyyy/mm/dd hh24:mi:ss'));
  230. INSERT INTO SMS_DATABASE (SMS_ID,SENDER_NO,RECEIVER_NO,SENDING_TIME) VALUES (10,'01937883634','01990275361',TO_DATE('1997-01-31 09:26:50','yyyy/mm/dd hh24:mi:ss'));
  231. INSERT INTO SMS_DATABASE (SMS_ID,SENDER_NO,RECEIVER_NO,SENDING_TIME) VALUES (11,'01937883634','01937883634',TO_DATE('1997-01-31 09:26:50','yyyy/mm/dd hh24:mi:ss'));
  232. INSERT INTO SMS_DATABASE (SMS_ID,SENDER_NO,RECEIVER_NO,SENDING_TIME) VALUES (12,'01937883634','01793969922',TO_DATE('1997-01-31 09:26:50','yyyy/mm/dd hh24:mi:ss'));
  233. INSERT INTO SMS_DATABASE (SMS_ID,SENDER_NO,RECEIVER_NO,SENDING_TIME) VALUES (13,'01993969922','01980184234',TO_DATE('1997-01-31 09:26:50','yyyy/mm/dd hh24:mi:ss'));
  234. INSERT INTO SMS_DATABASE (SMS_ID,SENDER_NO,RECEIVER_NO,SENDING_TIME) VALUES (14,'01980184234','01936994382',TO_DATE('1997-01-31 09:26:50','yyyy/mm/dd hh24:mi:ss'));
  235. INSERT INTO SMS_DATABASE (SMS_ID,SENDER_NO,RECEIVER_NO,SENDING_TIME) VALUES (15,'01993969922','01837303098',TO_DATE('1997-01-31 09:26:50','yyyy/mm/dd hh24:mi:ss'));
  236. INSERT INTO SMS_DATABASE (SMS_ID,SENDER_NO,RECEIVER_NO,SENDING_TIME) VALUES (16,'01980184234','01911071571',TO_DATE('1997-01-31 09:26:50','yyyy/mm/dd hh24:mi:ss'));                                                                                                                                                                    
  237. INSERT INTO SMS_DATABASE (SMS_ID,SENDER_NO,RECEIVER_NO,SENDING_TIME) VALUES (17,'01992409859','01990122106',TO_DATE('1997-01-31 09:26:50','yyyy/mm/dd hh24:mi:ss'));
  238.  
  239.  
  240. -----------------------------------SMS Database End-------------------------------------------
  241.  
  242.  
  243. -----------------------------------Net Usage Database Start-------------------------------------------
  244. CREATE TABLE NET_USAGE_DATABASE (
  245.   SESSION_ID NUMBER PRIMARY KEY ,
  246.   SIM_NO varchar2(100),
  247.   USING_DATE TIMESTAMP ,
  248.   USED_KB NUMBER,
  249.   CONSTRAINT NET_FK FOREIGN KEY (SIM_NO) REFERENCES SIM(PHONE_NO) ON DELETE CASCADE
  250. );
  251.  
  252. INSERT INTO NET_USAGE_DATABASE (SESSION_ID,SIM_NO,USING_DATE,USED_KB) VALUES (1,'01935849658', TO_DATE('1997-01-31 09:26:50','yyyy/mm/dd hh24:mi:ss'),2167);
  253. INSERT INTO NET_USAGE_DATABASE (SESSION_ID,SIM_NO,USING_DATE,USED_KB) VALUES (2,'01960850638', TO_DATE('1997-01-31 09:26:50','yyyy/mm/dd hh24:mi:ss'),870);
  254. INSERT INTO NET_USAGE_DATABASE (SESSION_ID,SIM_NO,USING_DATE,USED_KB) VALUES (3,'01960850638', TO_DATE('1997-01-31 09:26:50','yyyy/mm/dd hh24:mi:ss'),4808);
  255. INSERT INTO NET_USAGE_DATABASE (SESSION_ID,SIM_NO,USING_DATE,USED_KB) VALUES (4,'01965550938', TO_DATE('1997-01-31 09:26:50','yyyy/mm/dd hh24:mi:ss'),3627);
  256. INSERT INTO NET_USAGE_DATABASE (SESSION_ID,SIM_NO,USING_DATE,USED_KB) VALUES (5,'01993969922', TO_DATE('1997-01-31 09:26:50','yyyy/mm/dd hh24:mi:ss'),1424);
  257. INSERT INTO NET_USAGE_DATABASE (SESSION_ID,SIM_NO,USING_DATE,USED_KB) VALUES (6,'01980184234', TO_DATE('1997-01-31 09:26:50','yyyy/mm/dd hh24:mi:ss'),2175);
  258. INSERT INTO NET_USAGE_DATABASE (SESSION_ID,SIM_NO,USING_DATE,USED_KB) VALUES (7,'01990275361', TO_DATE('1997-01-31 09:26:50','yyyy/mm/dd hh24:mi:ss'),4663);
  259. INSERT INTO NET_USAGE_DATABASE (SESSION_ID,SIM_NO,USING_DATE,USED_KB) VALUES (8,'01976862069', TO_DATE('1997-01-31 09:26:50','yyyy/mm/dd hh24:mi:ss'),4218);                                                                                                          
  260. INSERT INTO NET_USAGE_DATABASE (SESSION_ID,SIM_NO,USING_DATE,USED_KB) VALUES (9, '01990275361',TO_DATE('1997-01-31 09:26:50','yyyy/mm/dd hh24:mi:ss'),3200);
  261. INSERT INTO NET_USAGE_DATABASE (SESSION_ID,SIM_NO,USING_DATE,USED_KB) VALUES (10,'01937883634',TO_DATE('1997-01-31 09:26:50','yyyy/mm/dd hh24:mi:ss'),4219);
  262. INSERT INTO NET_USAGE_DATABASE (SESSION_ID,SIM_NO,USING_DATE,USED_KB) VALUES (11,'01937883634',TO_DATE('1997-01-31 09:26:50','yyyy/mm/dd hh24:mi:ss'),3208);
  263. INSERT INTO NET_USAGE_DATABASE (SESSION_ID,SIM_NO,USING_DATE,USED_KB) VALUES (12,'01937883634',TO_DATE('1997-01-31 09:26:50','yyyy/mm/dd hh24:mi:ss'),1017);
  264. INSERT INTO NET_USAGE_DATABASE (SESSION_ID,SIM_NO,USING_DATE,USED_KB) VALUES (13,'01993969922',TO_DATE('1997-01-31 09:26:50','yyyy/mm/dd hh24:mi:ss'),3460);
  265. INSERT INTO NET_USAGE_DATABASE (SESSION_ID,SIM_NO,USING_DATE,USED_KB) VALUES (14,'01980184234',TO_DATE('1997-01-31 09:26:50','yyyy/mm/dd hh24:mi:ss'),4001);
  266. INSERT INTO NET_USAGE_DATABASE (SESSION_ID,SIM_NO,USING_DATE,USED_KB) VALUES (15,'01993969922',TO_DATE('1997-01-31 09:26:50','yyyy/mm/dd hh24:mi:ss'),4878);
  267. INSERT INTO NET_USAGE_DATABASE (SESSION_ID,SIM_NO,USING_DATE,USED_KB) VALUES (16,'01980184234',TO_DATE('1997-01-31 09:26:50','yyyy/mm/dd hh24:mi:ss'),4204);                                                                                                                                                
  268. INSERT INTO NET_USAGE_DATABASE (SESSION_ID,SIM_NO,USING_DATE,USED_KB) VALUES (17,'01992409859',TO_DATE('1997-01-31 09:26:50','yyyy/mm/dd hh24:mi:ss'),4644);
  269.  
  270.  
  271. -----------------------------------Net Usage Database End-------------------------------------------
  272.  
  273.  
  274.  
  275. -----------------------------------Post-paid Bill Start-------------------------------------------
  276. -- IF NON-EMPLOYEE
  277. CREATE TABLE POSTPAID_BILL (
  278.   BILL_ID NUMBER PRIMARY KEY,
  279.   SIM_NO varchar2(100),
  280.   CALL_BILL NUMBER,
  281.   SMS_BILL NUMBER ,
  282.   INTERNET_BILL NUMBER ,
  283.   TOTAL_COST NUMBER ,
  284.   CONSTRAINT POST_BILL_FK FOREIGN KEY(SIM_NO) REFERENCES POSTPAID_SIM(SIM_NO) ON DELETE CASCADE
  285. );
  286.  
  287. INSERT INTO POSTPAID_BILL(BILL_ID, SIM_NO, CALL_BILL, SMS_BILL, INTERNET_BILL, TOTAL_COST) VALUES (1,'01960850638' ,10,59,125, 194);
  288. INSERT INTO POSTPAID_BILL(BILL_ID, SIM_NO, CALL_BILL, SMS_BILL, INTERNET_BILL, TOTAL_COST) VALUES (2,'01965550938' ,124,35,132,291);
  289. --------------------------------------JUST POSTPAID------------------------------------------------,-------    
  290. INSERT INTO POSTPAID_BILL(BILL_ID, SIM_NO, CALL_BILL, SMS_BILL, INTERNET_BILL, TOTAL_COST) VALUES (3,'01976862069' ,51,44,327,422);
  291. INSERT INTO POSTPAID_BILL(BILL_ID, SIM_NO, CALL_BILL, SMS_BILL, INTERNET_BILL, TOTAL_COST) VALUES (4,'01990275361' ,143,60,250,453);
  292. INSERT INTO POSTPAID_BILL(BILL_ID, SIM_NO, CALL_BILL, SMS_BILL, INTERNET_BILL, TOTAL_COST) VALUES (5,'01937883634' ,1421,62,56 ,1539 );
  293. ---------------------------------------MIXED POSTPAID----------------------------------------------,---------      
  294. INSERT INTO POSTPAID_BILL(BILL_ID, SIM_NO, CALL_BILL, SMS_BILL, INTERNET_BILL, TOTAL_COST) VALUES (6,'01937303098' ,100,85,408,593);
  295. INSERT INTO POSTPAID_BILL(BILL_ID, SIM_NO, CALL_BILL, SMS_BILL, INTERNET_BILL, TOTAL_COST) VALUES (7,'01911071571' ,234,77,46,357);
  296. INSERT INTO POSTPAID_BILL(BILL_ID, SIM_NO, CALL_BILL, SMS_BILL, INTERNET_BILL, TOTAL_COST) VALUES (8,'01990122106' ,82,1,20,103);
  297. INSERT INTO POSTPAID_BILL(BILL_ID, SIM_NO, CALL_BILL, SMS_BILL, INTERNET_BILL, TOTAL_COST) VALUES (9,'01950134157' ,132,12,40, 184);
  298. INSERT INTO POSTPAID_BILL(BILL_ID, SIM_NO, CALL_BILL, SMS_BILL, INTERNET_BILL, TOTAL_COST) VALUES (10,'01967328196',15,42,30,87);
  299. --------------------------employee---------------------------
  300.  
  301.  
  302. -----------------------------------Post-paid Bill End-------------------------------------------
  303.  
  304.  
  305. -----------------------------------JOB Start-------------------------------------------
  306. CREATE TABLE JOBS(
  307.     JOB_ID NUMBER PRIMARY KEY ,
  308.     JOB_TITLE VARCHAR2(100) UNIQUE NOT NULL ,
  309.     MIN_SALARY NUMBER NOT NULL ,
  310.     MAX_SALARY NUMBER NOT NULL
  311. );
  312.  
  313. INSERT INTO JOBS(JOB_ID, JOB_TITLE, MIN_SALARY, MAX_SALARY) VALUES (101 , 'Administrator', 50000 , 80000) ;  
  314. INSERT INTO JOBS(JOB_ID, JOB_TITLE, MIN_SALARY, MAX_SALARY) VALUES (102 , 'Engineer', 40000 , 60000) ;  
  315. INSERT INTO JOBS(JOB_ID, JOB_TITLE, MIN_SALARY, MAX_SALARY) VALUES (103 , 'Human Resources Representative', 30000 , 40000) ;  
  316. INSERT INTO JOBS(JOB_ID, JOB_TITLE, MIN_SALARY, MAX_SALARY) VALUES (104 , 'Accounting Manager', 20000 , 30000) ;  
  317. INSERT INTO JOBS(JOB_ID, JOB_TITLE, MIN_SALARY, MAX_SALARY) VALUES (105 , 'Customer Care', 10000 , 20000) ;  
  318.  
  319. -----------------------------------Jobs Table End-------------------------------------------
  320.  
  321.  
  322. -----------------------------------Bonus table start-------------------------------------------
  323. CREATE TABLE ADDITIONAL_SALARY(
  324.     ADDSAL_ID NUMBER PRIMARY KEY CONSTRAINT ADDSAL_ID_CHK CHECK(ADDSAL_ID > 0) ,
  325.     ADDSAL_NAME VARCHAR2(100) UNIQUE NOT NULL ,
  326.     AMOUNT NUMBER CONSTRAINT ADD_SAL_AMNT CHECK(AMOUNT > 0)
  327. );
  328.  
  329. INSERT INTO ADDITIONAL_SALARY(ADDSAL_ID, ADDSAL_NAME, AMOUNT) VALUES (1 , 'HEALTH CARE', 5000);  
  330. INSERT INTO ADDITIONAL_SALARY(ADDSAL_ID, ADDSAL_NAME, AMOUNT) VALUES (2 , 'PERFORMANCE BONUS', 5000);  
  331. INSERT INTO ADDITIONAL_SALARY(ADDSAL_ID, ADDSAL_NAME, AMOUNT) VALUES (3 , 'FESTIVAL BONUS', 5000);
  332. INSERT INTO ADDITIONAL_SALARY(ADDSAL_ID, ADDSAL_NAME, AMOUNT) VALUES (4 , 'OVERTIME BONUS', 5000);
  333.  
  334. -----------------------------------Bonus table End-------------------------------------------
  335.  
  336.  
  337.  
  338. -----------------------------------Employee Start-------------------------------------------
  339. CREATE TABLE EMPLOYEES (
  340.   EMPLOYEE_ID number primary key ,
  341.   EMPLOYEE_NAME VARCHAR2(100) NOT NULL,
  342.   NID_NO NUMBER  UNIQUE  ,
  343.   EMAIL VARCHAR2(100) UNIQUE  ,
  344.   BANK_ACC_NO NUMBER UNIQUE ,
  345.   HIRE_DATE TIMESTAMP NOT NULL,
  346.   SSC_YEAR number NOT NULL,
  347.   SSC_GPA NUMBER NOT NULL,
  348.   HSC_YEAR NUMBER NOT NULL,
  349.   HSC_GPA NUMBER NOT NULL ,
  350.   UNIVERSITY VARCHAR2(100) NOT NULL,
  351.   CGPA NUMBER NOT NULL,
  352.   EXPERIENCE VARCHAR2(100) NOT NULL,
  353.   JOB_ID NUMBER NOT NULL ,
  354.   BASIC_SALARY NUMBER NOT NULL ,
  355.   CONSTRAINT EMP_FK FOREIGN KEY(NID_NO) REFERENCES USER_DATA(NID_NO) ON DELETE CASCADE,
  356.   CONSTRAINT EMPJOB_FK FOREIGN KEY(JOB_ID) REFERENCES JOBS(JOB_ID) ON DELETE CASCADE
  357. );
  358.  
  359. INSERT INTO EMPLOYEES (EMPLOYEE_ID,EMPLOYEE_NAME,NID_NO,EMAIL,BANK_ACC_NO,HIRE_DATE,SSC_YEAR,SSC_GPA ,HSC_YEAR,HSC_GPA ,UNIVERSITY ,CGPA ,EXPERIENCE ,JOB_ID,BASIC_SALARY) VALUES (101,'Tashya' ,1885,'Nam.ac.nulla@malesuadafames.org',3001,TO_DATE('1997-01-31 09:26:50','yyyy/mm/dd hh24:mi:ss'),2007, 5.00, 2009, 5.00, 'BUET', 3.67, '2 years as Network Engineer at BanglaPhone' ,101,79103);
  360. INSERT INTO EMPLOYEES (EMPLOYEE_ID,EMPLOYEE_NAME,NID_NO,EMAIL,BANK_ACC_NO,HIRE_DATE,SSC_YEAR,SSC_GPA ,HSC_YEAR,HSC_GPA ,UNIVERSITY ,CGPA ,EXPERIENCE ,JOB_ID,BASIC_SALARY) VALUES (102,'Harriet',1886,'In@ametdapibus.org',3002,TO_DATE('2015-12-07 09:26:50','yyyy/mm/dd hh24:mi:ss'),2008, 5.00, 2010, 5.00, 'Dhaka University', 3.57, '1 years as HR OFFICER at GRAMEENPHONE' ,102,51525);
  361. INSERT INTO EMPLOYEES (EMPLOYEE_ID,EMPLOYEE_NAME,NID_NO,EMAIL,BANK_ACC_NO,HIRE_DATE,SSC_YEAR,SSC_GPA ,HSC_YEAR,HSC_GPA ,UNIVERSITY ,CGPA ,EXPERIENCE ,JOB_ID,BASIC_SALARY) VALUES (103,'Salvado',1887,'Mauris.eu@massaQuisqueporttitor.net',3003,TO_DATE('2010-10-29 09:26:50','yyyy/mm/dd hh24:mi:ss'),2004, 5.00, 2006, 5.00, 'IUT', 3.67, '1 years as Customer Manager at BanglaPhone' ,103,44371);
  362. INSERT INTO EMPLOYEES (EMPLOYEE_ID,EMPLOYEE_NAME,NID_NO,EMAIL,BANK_ACC_NO,HIRE_DATE,SSC_YEAR,SSC_GPA ,HSC_YEAR,HSC_GPA ,UNIVERSITY ,CGPA ,EXPERIENCE ,JOB_ID,BASIC_SALARY) VALUES (104,'Tamara' ,1888,'vestibulum.lorem.sit@velit.net',3004,TO_DATE('2018-03-08 09:26:50','yyyy/mm/dd hh24:mi:ss'),2011, 5.00, 2013, 5.00, 'RUET', 3.64, '3 years as Accounting Manager at VaioPhone' ,104,42073);
  363. INSERT INTO EMPLOYEES (EMPLOYEE_ID,EMPLOYEE_NAME,NID_NO,EMAIL,BANK_ACC_NO,HIRE_DATE,SSC_YEAR,SSC_GPA ,HSC_YEAR,HSC_GPA ,UNIVERSITY ,CGPA ,EXPERIENCE ,JOB_ID,BASIC_SALARY) VALUES (105,'Gannon' ,1889,'justo.Praesent.luctus@Quisque.net',3005,TO_DATE('2016-04-11 19:26:50','yyyy/mm/dd hh24:mi:ss'),2009, 5.00, 2011, 5.00, 'BUET', 3.87, '2 years as Network Engineer at WePhone' ,105,25171);
  364.  
  365. -----------------------------------Employee End-------------------------------------------
  366.  
  367.  
  368. -----------------------------------Payslip Start-------------------------------------------
  369. CREATE TABLE PAYSLIP(
  370.     PAYSLIP_ID NUMBER PRIMARY KEY ,
  371.     EMPLOYEE_ID NUMBER ,
  372.     TOTAL_SALARY NUMBER NOT NULL ,
  373.     ISSUE_DATE TIMESTAMP NOT NULL,
  374.     CONSTRAINT SLIP_EMP_ID_FK FOREIGN KEY(EMPLOYEE_ID) REFERENCES EMPLOYEES(EMPLOYEE_ID) ON DELETE CASCADE
  375. );
  376.  
  377. INSERT INTO PAYSLIP(EMPLOYEE_ID, PAYSLIP_ID, TOTAL_SALARY, ISSUE_DATE) VALUES (101 , 1001, 20000, TO_DATE('1997-01-31 09:26:50','yyyy/mm/dd hh24:mi:ss'));  
  378. INSERT INTO PAYSLIP(EMPLOYEE_ID, PAYSLIP_ID, TOTAL_SALARY, ISSUE_DATE) VALUES (102 , 1002, 20000, TO_DATE('1997-01-31 09:26:50','yyyy/mm/dd hh24:mi:ss'));  
  379. INSERT INTO PAYSLIP(EMPLOYEE_ID, PAYSLIP_ID, TOTAL_SALARY, ISSUE_DATE) VALUES (103 , 1003, 20000, TO_DATE('1997-01-31 09:26:50','yyyy/mm/dd hh24:mi:ss'));
  380. INSERT INTO PAYSLIP(EMPLOYEE_ID, PAYSLIP_ID, TOTAL_SALARY, ISSUE_DATE) VALUES (104 , 1004, 20000, TO_DATE('1997-01-31 09:26:50','yyyy/mm/dd hh24:mi:ss'));
  381. INSERT INTO PAYSLIP(EMPLOYEE_ID, PAYSLIP_ID, TOTAL_SALARY, ISSUE_DATE) VALUES (105 , 1005, 20000, TO_DATE('1997-01-31 09:26:50','yyyy/mm/dd hh24:mi:ss'));
  382.  
  383. -----------------------------------Payslip End-------------------------------------------
  384.  
  385.  
  386. -------------------------------Inter slip Bonus start-----------------------------------
  387. CREATE TABLE INTER_SLIP_BONUS(
  388.     PAYSLIP_ID NUMBER  ,
  389.     BONUS_ID NUMBER  ,
  390.     CONSTRAINT PAYSLIP_ID_FK FOREIGN KEY(PAYSLIP_ID) REFERENCES PAYSLIP(PAYSLIP_ID) ,
  391.     CONSTRAINT BONUS_ID_FK FOREIGN KEY(BONUS_ID) REFERENCES ADDITIONAL_SALARY(ADDSAL_ID)
  392. );
  393.  
  394. INSERT INTO INTER_SLIP_BONUS(PAYSLIP_ID, BONUS_ID) VALUES (1001 , 1);  
  395. INSERT INTO INTER_SLIP_BONUS(PAYSLIP_ID, BONUS_ID) VALUES (1002 , 2);  
  396. INSERT INTO INTER_SLIP_BONUS(PAYSLIP_ID, BONUS_ID) VALUES (1003 , 3);
  397. INSERT INTO INTER_SLIP_BONUS(PAYSLIP_ID, BONUS_ID) VALUES (1004 , 4);
  398. INSERT INTO INTER_SLIP_BONUS(PAYSLIP_ID, BONUS_ID) VALUES (1005 , 2);
  399. INSERT INTO INTER_SLIP_BONUS(PAYSLIP_ID, BONUS_ID) VALUES (1001 , 4);
  400. INSERT INTO INTER_SLIP_BONUS(PAYSLIP_ID, BONUS_ID) VALUES (1003 , 2);
  401. INSERT INTO INTER_SLIP_BONUS(PAYSLIP_ID, BONUS_ID) VALUES (1002 , 1);
  402.  
  403. -------------------------------Inter slip Bonus end-----------------------------------
  404.  
  405. -------------------------------AVAILABLE_PREPAID_SIM START-----------------------------------
  406.  
  407. CREATE TABLE AVAILABLE_PREPAID_SIM(
  408.     SIM_NO VARCHAR2(100) PRIMARY KEY
  409. );
  410.  
  411. --************ PROCEDURE CAN MAKE 1000 RANDOM NUMBER AT A  TIME 019 + '1-5' + XXXXXXX = PREPAID NO.  
  412.  
  413.  
  414. -------------------------------AVAILABLE_PREPAID_SIM END-----------------------------------
  415.  
  416. -------------------------------AVAILABLE_POSTPAID_SIM START-----------------------------------
  417.  
  418. CREATE TABLE AVAILABLE_POSTPAID_SIM(
  419.     SIM_NO VARCHAR2(100) PRIMARY KEY
  420. );
  421. --************ PROCEDURE CAN MAKE 1000 RANDOM NUMBER AT A  TIME 019 + '6-9' + XXXXXXX = PREPAID NO.  
  422.  
  423.  
  424. -------------------------------AVAILABLE_POSTPAID_SIM END-----------------------------------
  425.  
  426.  
  427. ----------------------------------PENDING SIM TABLE START------------------------------
  428.  
  429.  
  430.  
  431. CREATE TABLE PENDING_SIM_REQ(
  432.     USER_NAME VARCHAR2(100) NOT NULL,
  433.     NID_NO number CONSTRAINT USER_PK3 PRIMARY KEY,
  434.     DATE_OF_BIRTH VARCHAR2(100) NOT NULL,
  435.     NAME_OF_FATHER VARCHAR2(100) NOT NULL,
  436.     NAME_OF_MOTHER VARCHAR2(100) NOT NULL,
  437.     STREET_ADDRESS VARCHAR2(100) NOT NULL,
  438.     CITY VARCHAR2(100) NOT NULL,
  439.     IS_EMPLOYEE VARCHAR2(100) NOT NULL,
  440.     SIM_TYPE_ID NUMBER NOT NULL,
  441.     ISSUED_BY NUMBER ,
  442.     CONSTRAINT PSTI_FK FOREIGN KEY (SIM_TYPE_ID) REFERENCES SIM_TYPE(TYPE_ID) ON DELETE CASCADE,
  443.     CONSTRAINT PSRIB_FK FOREIGN KEY (ISSUED_BY) REFERENCES EMPLOYEES(EMPLOYEE_ID) ON DELETE CASCADE
  444. );
  445.  
  446. INSERT INTO PENDING_SIM_REQ (USER_NAME,NID_NO,DATE_OF_BIRTH,NAME_OF_FATHER,NAME_OF_MOTHER,STREET_ADDRESS,CITY,IS_EMPLOYEE,SIM_TYPE_ID,ISSUED_BY) VALUES ('Dante',1924,'May 18, 1989','Stone','Pamela','P.O. Box 317, 4049 Hendrerit Street','Beez','NO',1,101);
  447. INSERT INTO PENDING_SIM_REQ (USER_NAME,NID_NO,DATE_OF_BIRTH,NAME_OF_FATHER,NAME_OF_MOTHER,STREET_ADDRESS,CITY,IS_EMPLOYEE,SIM_TYPE_ID,ISSUED_BY) VALUES ('Tanner',1925,'Jan 2, 1920','Hammett','Hilda','P.O. Box 682, 5083 Interdum St.','Yorkton','NO',1,101);
  448. INSERT INTO PENDING_SIM_REQ (USER_NAME,NID_NO,DATE_OF_BIRTH,NAME_OF_FATHER,NAME_OF_MOTHER,STREET_ADDRESS,CITY,IS_EMPLOYEE,SIM_TYPE_ID,ISSUED_BY) VALUES ('Samuel',1926,'Sep 27, 1971','Leroy','Cherokee','471-3004 Et, St.','Gianico','NO',2,102);
  449. INSERT INTO PENDING_SIM_REQ (USER_NAME,NID_NO,DATE_OF_BIRTH,NAME_OF_FATHER,NAME_OF_MOTHER,STREET_ADDRESS,CITY,IS_EMPLOYEE,SIM_TYPE_ID,ISSUED_BY) VALUES ('Chaney',1927,'Sep 28, 1953','Tate','Deirdre','855-2842 Consectetuer Ave','Tiruvottiyur','NO',2,102);
  450. ----------------------------------PENDING SIM TABLE END------------------------------
  451.  
  452. CREATE TABLE PENDING_JOB_REQ(
  453.     EMP_NAME VARCHAR2(100) NOT NULL,
  454.     FATHER_NAME VARCHAR2(100) NOT NULL,
  455.     MOTHER_NAME VARCHAR2(100) NOT NULL,
  456.     BIRTH_DATE VARCHAR2(100) NOT NULL,
  457.     STREET_ADDRESS VARCHAR2(100) NOT NULL,
  458.     CITY VARCHAR2(100) NOT NULL,
  459.     NID_NO NUMBER PRIMARY KEY ,
  460.     EMAIL VARCHAR2(100) NOT NULL,
  461.     ACC_NO NUMBER UNIQUE ,
  462.     SSC_YEAR NUMBER NOT NULL ,
  463.     SSC_GPA NUMBER NOT NULL ,
  464.     HSC_YEAR NUMBER NOT NULL ,
  465.     HSC_GPA NUMBER NOT NULL ,
  466.     UNIVERSITY VARCHAR2(100) NOT NULL,
  467.     CGPA NUMBER NOT NULL,
  468.     EXPERIENCE VARCHAR2(100) NOT NULL,
  469.     JOB_ID NUMBER NOT NULL ,
  470.    
  471.     ISSUED_BY NUMBER ,
  472.     HIRE_DATE TIMESTAMP ,
  473.     SALARY NUMBER ,  
  474.    
  475.     CONSTRAINT EMP_JID_FK FOREIGN KEY (JOB_ID) REFERENCES JOBS(JOB_ID) ON DELETE CASCADE ,
  476.     CONSTRAINT EMP_ISSUE_FK FOREIGN KEY (ISSUED_BY) REFERENCES EMPLOYEES(EMPLOYEE_ID) ON DELETE CASCADE
  477. );
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top