API tools faq
paste
Advertisement
safriansah

fp basdat

safriansah
Jul 16th, 2018
8,223
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
Oracle 11 13.97 KB | None | 0 0
raw download clone embed print report
  1. /*==============================================================*/
  2. /* DBMS name:      ORACLE Version 10g                           */
  3. /* Created on:     14/05/2016 08.00.21                          */
  4. /*==============================================================*/
  5.  
  6.  
  7. ALTER TABLE KELAHIRAN
  8.    DROP CONSTRAINT FK_KELAHIRA_MEMBUAT_KELUARGA;
  9.  
  10. ALTER TABLE KELUARGA
  11.    DROP CONSTRAINT FK_KELUARGA_MENEMPATI_WILAYAH;
  12.  
  13. ALTER TABLE KEMATIAN
  14.    DROP CONSTRAINT FK_KEMATIAN_MENGALAMI_PENDUDUK;
  15.  
  16. ALTER TABLE PENDUDUK
  17.    DROP CONSTRAINT FK_PENDUDUK_MEMBENTUK_KELUARGA;
  18.  
  19. ALTER TABLE PENDUDUK
  20.    DROP CONSTRAINT FK_PENDUDUK_MENGALAMI_KEMATIAN;
  21.  
  22. DROP INDEX MEMBUAT_FK;
  23.  
  24. DROP TABLE KELAHIRAN CASCADE CONSTRAINTS;
  25.  
  26. DROP INDEX MENEMPATI_FK;
  27.  
  28. DROP TABLE KELUARGA CASCADE CONSTRAINTS;
  29.  
  30. DROP INDEX MENGALAMI_FK;
  31.  
  32. DROP TABLE KEMATIAN CASCADE CONSTRAINTS;
  33.  
  34. DROP INDEX MENGALAMI2_FK;
  35.  
  36. DROP INDEX MEMBENTUK_FK;
  37.  
  38. DROP TABLE PENDUDUK CASCADE CONSTRAINTS;
  39.  
  40. DROP TABLE WILAYAH CASCADE CONSTRAINTS;
  41.  
  42. /*==============================================================*/
  43. /* Table: KELAHIRAN                                             */
  44. /*==============================================================*/
  45. CREATE TABLE KELAHIRAN  (
  46.    NIK                  CHAR(16)                        NOT NULL,
  47.    NO_LAHIR             CHAR(10)                        NOT NULL,
  48.    TEMPAT_LAHIR         VARCHAR2(16)                    NOT NULL,
  49.    HARI_LAHIR           VARCHAR2(8)                     NOT NULL,
  50.    TANGGAL_LAHIR        DATE                            NOT NULL,
  51.    KETERANGAN           VARCHAR2(8)                     NOT NULL,
  52.    CONSTRAINT PK_KELAHIRAN PRIMARY KEY (NIK, NO_LAHIR)
  53. );
  54.  
  55. /*==============================================================*/
  56. /* Index: MEMBUAT_FK                                            */
  57. /*==============================================================*/
  58. CREATE INDEX MEMBUAT_FK ON KELAHIRAN (
  59.    NIK ASC
  60. );
  61.  
  62. /*==============================================================*/
  63. /* Table: KELUARGA                                              */
  64. /*==============================================================*/
  65. CREATE TABLE KELUARGA  (
  66.    NIK                  CHAR(16)                        NOT NULL,
  67.    KODE_POS             CHAR(5),
  68.    NO_KELUARGA          CHAR(16)                        NOT NULL,
  69.    STATUS               VARCHAR2(16)                    NOT NULL,
  70.    JALAN                VARCHAR2(32)                    NOT NULL,
  71.    RT                   CHAR(2)                         NOT NULL,
  72.    RW                   CHAR(2)                         NOT NULL,
  73.    CONSTRAINT PK_KELUARGA PRIMARY KEY (NIK)
  74. );
  75.  
  76. /*==============================================================*/
  77. /* Index: MENEMPATI_FK                                          */
  78. /*==============================================================*/
  79. CREATE INDEX MENEMPATI_FK ON KELUARGA (
  80.    KODE_POS ASC
  81. );
  82.  
  83. /*==============================================================*/
  84. /* Table: KEMATIAN                                              */
  85. /*==============================================================*/
  86. CREATE TABLE KEMATIAN  (
  87.    NIK                  CHAR(16)                        NOT NULL,
  88.    KTP                  CHAR(16)                        NOT NULL,
  89.    NO_KEMATIAN          CHAR(10)                        NOT NULL,
  90.    TEMPAT               VARCHAR2(64)                    NOT NULL,
  91.    HARI                 VARCHAR2(8)                     NOT NULL,
  92.    TGL                  DATE                            NOT NULL,
  93.    SEBAB                VARCHAR2(64)                    NOT NULL,
  94.    CONSTRAINT PK_KEMATIAN PRIMARY KEY (NIK, KTP, NO_KEMATIAN)
  95. );
  96.  
  97. /*==============================================================*/
  98. /* Index: MENGALAMI_FK                                          */
  99. /*==============================================================*/
  100. CREATE INDEX MENGALAMI_FK ON KEMATIAN (
  101.    NIK ASC,
  102.    KTP ASC
  103. );
  104.  
  105. /*==============================================================*/
  106. /* Table: PENDUDUK                                              */
  107. /*==============================================================*/
  108. CREATE TABLE PENDUDUK  (
  109.    NIK                  CHAR(16)                        NOT NULL,
  110.    KTP                  CHAR(16)                        NOT NULL,
  111.    KEM_NIK              CHAR(16),
  112.    KEM_KTP              CHAR(16),
  113.    NO_KEMATIAN          CHAR(10),
  114.    NAMA                 VARCHAR2(32)                    NOT NULL,
  115.    JK                   VARCHAR2(12)                    NOT NULL,
  116.    AGAMA                VARCHAR2(16)                    NOT NULL,
  117.    PENDIDIKAN           VARCHAR2(3),
  118.    PEKERJAAN            VARCHAR2(16),
  119.    PENGHASILAN          NUMBER,
  120.    CONSTRAINT PK_PENDUDUK PRIMARY KEY (NIK, KTP)
  121. );
  122.  
  123. /*==============================================================*/
  124. /* Index: MEMBENTUK_FK                                          */
  125. /*==============================================================*/
  126. CREATE INDEX MEMBENTUK_FK ON PENDUDUK (
  127.    NIK ASC
  128. );
  129.  
  130. /*==============================================================*/
  131. /* Index: MENGALAMI2_FK                                         */
  132. /*==============================================================*/
  133. CREATE INDEX MENGALAMI2_FK ON PENDUDUK (
  134.    NIK ASC,
  135.    KTP ASC,
  136.    NO_KEMATIAN ASC
  137. );
  138.  
  139. /*==============================================================*/
  140. /* Table: WILAYAH                                               */
  141. /*==============================================================*/
  142. CREATE TABLE WILAYAH  (
  143.    KODE_POS             CHAR(5)                         NOT NULL,
  144.    KELURAHAN            VARCHAR2(32)                    NOT NULL,
  145.    KECAMATAN            VARCHAR2(32)                    NOT NULL,
  146.    KABUPATEN            VARCHAR2(32)                    NOT NULL,
  147.    PROVINSI             VARCHAR2(32)                    NOT NULL,
  148.    CONSTRAINT PK_WILAYAH PRIMARY KEY (KODE_POS)
  149. );
  150.  
  151. ALTER TABLE KELAHIRAN
  152.    ADD CONSTRAINT FK_KELAHIRA_MEMBUAT_KELUARGA foreign KEY (NIK)
  153.       references KELUARGA (NIK);
  154.  
  155. ALTER TABLE KELUARGA
  156.    ADD CONSTRAINT FK_KELUARGA_MENEMPATI_WILAYAH foreign KEY (KODE_POS)
  157.       references WILAYAH (KODE_POS);
  158.  
  159. ALTER TABLE KEMATIAN
  160.    ADD CONSTRAINT FK_KEMATIAN_MENGALAMI_PENDUDUK foreign KEY (NIK, KTP)
  161.       references PENDUDUK (NIK, KTP);
  162.  
  163. ALTER TABLE PENDUDUK
  164.    ADD CONSTRAINT FK_PENDUDUK_MEMBENTUK_KELUARGA foreign KEY (NIK)
  165.       references KELUARGA (NIK);
  166.  
  167. ALTER TABLE PENDUDUK
  168.    ADD CONSTRAINT FK_PENDUDUK_MENGALAMI_KEMATIAN foreign KEY (NIK, KTP, NO_KEMATIAN)
  169.       references KEMATIAN (NIK, KTP, NO_KEMATIAN);
  170.  
  171. /*modul 4*/
  172. INSERT ALL
  173. INTO wilayah VALUES('61111','Tlogopatut','Gresik','Gresik','Jawa Timur')
  174. INTO wilayah VALUES('61112','Sidokumpul','Gresik','Gresik','Jawa Timur')
  175. INTO wilayah VALUES('61113','Trate','Gresik','Gresik','Jawa Timur')
  176. INTO wilayah VALUES('61114','Bedilan','Gresik','Gresik','Jawa Timur')
  177. INTO wilayah VALUES('61121','Kebomas','Kebomas','Gresik','Jawa Timur')
  178. INTO wilayah VALUES('61122','Randuagung','Kebomas','Gresik','Jawa Timur')
  179. INTO wilayah VALUES('61123','Segoromadu','Kebomas','Gresik','Jawa Timur')
  180. INTO wilayah VALUES('61124','Giri','Kebomas','Gresik','Jawa Timur')
  181. INTO wilayah VALUES('61151','Yosowilangun','Manyar','Gresik','Jawa Timur')
  182. INTO wilayah VALUES('61152','Suci','Manyar','Gresik','Jawa Timur')
  183. SELECT*FROM dual
  184.  
  185. INSERT ALL
  186. INTO keluarga VALUES('3525100301770001','61111','3525101301970001','Kepala Keluarga','Jl.Surabaya no 1','01','01')
  187. INTO keluarga VALUES('3525100302770001','61112','3525101301970002','Kepala Keluarga','Jl.Malang no 2','02','09')
  188. INTO keluarga VALUES('3525100303770001','61113','3525101301970003','Kepala Keluarga','Jl.Sidoarjo no 3','03','08')
  189. INTO keluarga VALUES('3525100304770001','61114','3525101301970004','Kepala Keluarga','Jl.Pasuruan no 4','04','07')
  190. INTO keluarga VALUES('3525100305770001','61121','3525101301970005','Kepala Keluarga','Jl.Lamongan no 5','05','06')
  191. INTO keluarga VALUES('3525100306770001','61122','3525101301970006','Kepala Keluarga','Jl.Jombang no 6','06','05')
  192. INTO keluarga VALUES('3525100307770001','61123','3525101301970007','Kepala Keluarga','Jl.Kediri no 7','07','04')
  193. INTO keluarga VALUES('3525100308770001','61123','3525101301970008','Kepala Keluarga','Jl.Tuban no 8','08','03')
  194. INTO keluarga VALUES('3525100309770001','61151','3525101301970009','Kepala Keluarga','Jl.Banyuwangi no 9','09','02')
  195. INTO keluarga VALUES('3525100310770001','61152','3525101301970010','Kepala Keluarga','Jl.Purbalingga no 10','01','01')
  196. SELECT*FROM dual
  197.  
  198. INSERT ALL
  199. INTO kelahiran VALUES('3525100301770001','3525010001','Gresik','Rabu',TO_DATE('03-01-1977','DD-MM-YYYY'),'Normal')
  200. INTO kelahiran VALUES('3525100302770001','3525010002','Gresik','Selasa',TO_DATE('03-02-1977','DD-MM-YYYY'),'Normal')
  201. INTO kelahiran VALUES('3525100303770001','3525010003','Gresik','Senin',TO_DATE('03-03-1977','DD-MM-YYYY'),'Normal')
  202. INTO kelahiran VALUES('3525100304770001','3525010004','Gresik','Minggu',TO_DATE('03-04-1977','DD-MM-YYYY'),'Normal')
  203. INTO kelahiran VALUES('3525100305770001','3525010005','Gresik','Sabtu',TO_DATE('03-05-1977','DD-MM-YYYY'),'Normal')
  204. INTO kelahiran VALUES('3525100306770001','3525010006','Gresik','Jumat',TO_DATE('03-06-1977','DD-MM-YYYY'),'Normal')
  205. INTO kelahiran VALUES('3525100307770001','3525010007','Gresik','Kamis',TO_DATE('03-07-1977','DD-MM-YYYY'),'Normal')
  206. INTO kelahiran VALUES('3525100308770001','3525010008','Gresik','Rabu',TO_DATE('03-08-1977','DD-MM-YYYY'),'Normal')
  207. INTO kelahiran VALUES('3525100309770001','3525010009','Gresik','Selasa',TO_DATE('03-09-1977','DD-MM-YYYY'),'Normal')
  208. INTO kelahiran VALUES('3525100310770001','3525010010','Gresik','Senin',TO_DATE('03-10-1977','DD-MM-YYYY'),'Normal')
  209. SELECT*FROM dual
  210.  
  211. INSERT ALL
  212. INTO penduduk VALUES('3525100301770001','3525100301770001','','Mahardika','Laki-Laki','Islam','S-1','Programmer',3000000)
  213. INTO penduduk VALUES('3525100302770001','3525100302770001','','Aji','Laki-Laki','Islam','S-1','Db Admin',3000000)
  214. INTO penduduk VALUES('3525100303770001','3525100303770001','','Pangestu','Laki-Laki','Islam','S-1','Sys Analys',3000000)
  215. INTO penduduk VALUES('3525100304770001','3525100304770001','','Taufiq','Laki-Laki','Islam','S-1','Web Design',3500000)
  216. INTO penduduk VALUES('3525100305770001','3525100305770001','','Kriswanto','Laki-Laki','Islam','S-1','Web Analys',3500000)
  217. INTO penduduk VALUES('3525100306770001','3525100306770001','','Fikri','Laki-Laki','Islam','S-1','Dosen',3500000)
  218. INTO penduduk VALUES('3525100307770001','3525100307770001','','Hendika','Laki-Laki','Islam','S-1','Guru',3750000)
  219. INTO penduduk VALUES('3525100308770001','3525100308770001','','Pratama','Laki-Laki','Islam','S-1','Wiraswasta',3750000)
  220. INTO penduduk VALUES('3525100309770001','3525100309770001','','Bagas','Laki-Laki','Islam','S-1','SE',3750000)
  221. INTO penduduk VALUES('3525100310770001','3525100310770001','','Zamani','Laki-Laki','Islam','S-1','APP Tester',4000000)
  222. SELECT*FROM dual
  223.  
  224. INSERT ALL
  225. INTO kematian VALUES('3525100301770001','3525100301770001','3525101111','Gresik','Senin',TO_DATE('03-10-2042','DD-MM-YYYY'),'Tenggelam')
  226. INTO kematian VALUES('3525100302770001','3525100302770001','3525101112','Gresik','Selasa',TO_DATE('03-09-2042','DD-MM-YYYY'),'Kaget')
  227. INTO kematian VALUES('3525100303770001','3525100303770001','3525101113','Gresik','Rabu',TO_DATE('03-08-2042','DD-MM-YYYY'),'Jatuh dari lantai 2')
  228. INTO kematian VALUES('3525100304770001','3525100304770001','3525101114','Gresik','Kamis',TO_DATE('03-07-2042','DD-MM-YYYY'),'Muntaber')
  229. INTO kematian VALUES('3525100305770001','3525100305770001','3525101115','Gresik','Jumat',TO_DATE('03-06-2042','DD-MM-YYYY'),'Mencret')
  230. INTO kematian VALUES('3525100306770001','3525100306770001','3525101116','Gresik','Sabtu',TO_DATE('03-05-2042','DD-MM-YYYY'),'Maag')
  231. INTO kematian VALUES('3525100307770001','3525100307770001','3525101117','Gresik','Minggu',TO_DATE('03-04-2042','DD-MM-YYYY'),'Penyakit Kulit')
  232. INTO kematian VALUES('3525100308770001','3525100308770001','3525101118','Gresik','Senin',TO_DATE('03-03-2042','DD-MM-YYYY'),'Kelaparan')
  233. INTO kematian VALUES('3525100309770001','3525100309770001','3525101119','Gresik','Selasa',TO_DATE('03-02-2042','DD-MM-YYYY'),'Ledakan')
  234. INTO kematian VALUES('3525100310770001','3525100310770001','3525101120','Gresik','Rabu',TO_DATE('03-01-2042','DD-MM-YYYY'),'Keracunan')
  235. SELECT*FROM dual
  236.  
  237. SELECT*FROM kematian
  238. SELECT*FROM kelahiran
  239. SELECT*FROM penduduk
  240. SELECT*FROM keluarga
  241. SELECT*FROM wilayah
  242.  
  243. UPDATE penduduk SET pendidikan='S-1', pekerjaan='Pegawai Swasta' WHERE nik='3525100305770001'
  244.  
  245. DELETE wilayah WHERE kode_pos='61151'
  246.  
  247. ALTER TABLE penduduk
  248. MODIFY penghasilan NUMBER
  249.  
  250. DROP TABLE wilayah
  251.  
  252. /*modul 5*/
  253. SELECT SUM(penghasilan) FROM penduduk
  254. SELECT MIN(penghasilan) FROM penduduk
  255. SELECT MAX(penghasilan) FROM penduduk
  256. SELECT AVG(penghasilan) FROM penduduk
  257. SELECT penghasilan,COUNT(*) FROM penduduk GROUP BY penghasilan
  258.  
  259. /*modul 6*/
  260. SELECT penghasilan FROM penduduk GROUP BY penghasilan HAVING penghasilan > 3500000
  261. SELECT ktp,nama FROM penduduk WHERE ktp>='3525100308770001'
  262. SELECT nama FROM penduduk WHERE nama LIKE'%i%'
  263. SELECT nama FROM penduduk WHERE nama NOT LIKE'%i%'
  264. SELECT nama,ktp FROM penduduk WHERE ktp IN('3525100306770001','3525100307770001')
  265. SELECT nama,ktp FROM penduduk WHERE ktp BETWEEN '3525100301770001' AND '3525100304770001'
  266.  
  267. /*modul 7*/
  268. CREATE TABLE RECORD(
  269. nik CHAR(16),
  270. pendidikanOld VARCHAR(3),
  271. pekerjaanOld VARCHAR(16),
  272. penghasilanOld NUMBER,
  273. pendidikanNew VARCHAR(3),
  274. pekerjaanNew VARCHAR(16),
  275. penghasilanNew NUMBER,
  276. tgl DATE
  277. )
  278.  
  279. CREATE OR REPLACE TRIGGER  "UPDATE_PEND"
  280. after UPDATE ON penduduk FOR each ROW
  281. BEGIN
  282. INSERT INTO RECORD VALUES(:NEW.nik,:old.pendidikan,:old.pekerjaan,,:old.penghasilan:NEW.pendidikan,:NEW.pekerjaan,:NEW.penghasilan,CURRENT_DATE);
  283. END;
  284.  
  285. UPDATE penduduk SET pendidikan='S-1', pekerjaan='Pegawai Swasta' WHERE nik<='3525100305770001'
  286. UPDATE penduduk SET pendidikan='S-2', pekerjaan='Pegawai Negeri' WHERE nik>'3525100305770001'
  287.  
  288. CREATE OR REPLACE TRIGGER  "INSERT_KEM"
  289. after INSERT ON kematian FOR each ROW
  290. BEGIN
  291. UPDATE penduduk SET no_kematian=:NEW.no_kematian WHERE nik=:NEW.nik;
  292. END;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
Public Pastes
Advertisement
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
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!