{"id":1367,"date":"2011-10-14T07:00:22","date_gmt":"2011-10-14T07:00:22","guid":{"rendered":"http:\/\/gigihfordanama.wordpress.com\/?p=1367"},"modified":"2012-08-08T00:08:06","modified_gmt":"2012-08-08T00:08:06","slug":"kelas-praktikum-basis-data-mti","status":"publish","type":"post","link":"https:\/\/dosen.unila.ac.id\/gigih\/2011\/10\/14\/kelas-praktikum-basis-data-mti\/","title":{"rendered":"Kelas Praktikum Basis Data MTI"},"content":{"rendered":"

\"\"<\/a> Biasanya setiap Kamis sore ba’da perkuliahan selesai saya sudah bergegas bertolak pulang menuju Lampung, nah khusus untuk minggu ini ada perubahan jadwal perkuliahan dengan adanya praktikum yang diadakan tiap jum’at mulai\u00a0 14 Oktober (hari ini),\u00a0 jadwal pulang terpaksa dipending hingga jum’at sore. Praktikum jum’at ini diselenggarakan dalam rangka mensupport materi perkuliahan Teknologi System Basis Data (Oleh Pak Nizar\/Bu Yova). Info dari System Perkuliahan Online asisten Praktikum Basis data adalah M.Budi Utama dan Irene Ully Havsa, entah beliau-beliau ini merupakan dosen tetap di Laboratorium ini , ataukah mahasiswa perpanjangan tangan dari dosen tetap pengampu mata kuliah, di Unila biasanya tim asisten praktikum untuk Mata Kuliah tertentu melibatkan mahasiswa denga reputasi baik terhadap Mata Kuliah yang pernah mereka ambil, sekilas tadi bertemu dengan Ibu Irene terlihat masih muda mungkin lebih tepat dipanggil mbak kali yah… (kemungkinan besar adalah murid dari Bu Yova\/Pak Nizar). Its OK siapa pun asisten-nya tidak ada masalah yang penting bisa membantu rekan rekan selama praktikum berlangsung.<\/p>\n

Kali pertama saya memasuki ruangan Lab Komputer MTI nuansanya nyaman dan sangat kondusif untuk kegiatan praktikum, disini disediakan komputer sebanyak 50 Unit dengan layar LCD Flat Screen merk FUJIT*U lengkap dengan furniture meja yang nyaman untuk digunakan. Ditambah lagi 4 unit AC On semakin memanjakan saya selama waktu menunggu praktikum Basis Data.\u00a0 Aplikasi utama yang digunakan untuk praktikum System Basis Data ini adalah Oracle database 10g Express Edition, sebenernya sudah ada release terbaru yakni versi 11g Express Edition, mungkin pertimbangan asisten butuh waktu lagi apabila dilakukan upgrade ke versi oracle terbaru,\u00a0 o iya kenapa ya mereka tidak menggunakan oracle full version yah ????\u00a0 hmm mungkin karena biaya licence oracle yang selangit kali.<\/p>\n

\"\"<\/a>
\n<\/a>Selamat berpraktikum ria kawan-kawan..<\/p>\n

Update untuk Materi SQL on Oracle<\/strong><\/p>\n

1.\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Buat tabel INVOICE dengan data berikut:<\/strong><\/p>\n\n\n\n\n\n\n\n
KOLOM<\/td>\nTIPE<\/td>\nPANJANG<\/td>\n<\/tr>\n
ID<\/td>\nNUMBER<\/td>\n<\/td>\n<\/tr>\n
DATE<\/td>\nDATE<\/td>\n<\/td>\n<\/tr>\n
BILL_TO<\/td>\nVARCHAR2<\/td>\n500<\/td>\n<\/tr>\n
TOTAL<\/td>\nNUMBER<\/td>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
\u00a0CREATE TABLE INVOICE\n\u00a0\u00a0 (\n\u00a0\u00a0\u00a0\u00a0 ID NUMBER,\n\u00a0\u00a0\u00a0\u00a0 \"DATE\" DATE,\n\u00a0\u00a0\u00a0\u00a0 BILL_TO VARCHAR2(500),\n\u00a0\u00a0\u00a0\u00a0 TOTAL NUMBER\n\u00a0\u00a0 );<\/pre>\n
 <\/p>\n
2.\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Buat tabel INVOICE_ITEM dengan data berikut:<\/strong><\/p>\n\n\n\n\n\n\n\n\n\n\n
KOLOM<\/td>\nTIPE<\/td>\nPANJANG<\/td>\n<\/tr>\n
ID<\/td>\nNUMBER<\/td>\n<\/td>\n<\/tr>\n
INVOICE_ID<\/td>\nNUMBER<\/td>\n<\/td>\n<\/tr>\n
PRODUCT_ID<\/td>\nVARCHAR2<\/td>\n500<\/td>\n<\/tr>\n
DESCRIPTION<\/td>\nVARCHAR2<\/td>\n1000<\/td>\n<\/tr>\n
QUANTITY<\/td>\nNUMBER<\/td>\n<\/td>\n<\/tr>\n
UNIT_PRICE<\/td>\nNUMBER<\/td>\n<\/td>\n<\/tr>\n
LINE_TOTAL<\/td>\nNUMBER<\/td>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
\u00a0CREATE TABLE INVOICE_ITEM\n\u00a0\u00a0 (\n\u00a0\u00a0\u00a0\u00a0 ID NUMBER,\n\u00a0\u00a0\u00a0\u00a0 INVOICE_ID NUMBER,\n\u00a0\u00a0\u00a0\u00a0 PRODUCT_ID VARCHAR2(500),\n\u00a0\u00a0\u00a0\u00a0 DESCRIPTION VARCHAR2(1000),\n\u00a0\u00a0\u00a0\u00a0 QUANTITY NUMBER,\n\u00a0\u00a0\u00a0\u00a0 UNIT_PRICE NUMBER,\n\u00a0\u00a0\u00a0\u00a0 LINE_TOTAL NUMBER\n\u00a0\u00a0 );<\/pre>\n
 <\/p>\n
3.\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/strong>\u00a0\u00a0 Pada tabel INVOICE set kolom ID menjadi Primary Key<\/strong><\/p>\n
ALTER TABLE INVOICE\nADD CONSTRAINT INVOICE_PK PRIMARY KEY (ID);<\/pre>\n
4.\u00a0\u00a0\u00a0<\/strong>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Pada tabel INVOICE_ITEM set kolom ID menjadi Primary Key<\/strong><\/p>\n
ALTER TABLE INVOICE_ITEM\nADD CONSTRAINT INVOICE_ITEM_PK PRIMARY KEY (ID);<\/pre>\n
5.\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/strong>\u00a0 Jadikan kolom INVOICE_ID pada tabel INVOICE_ITEM sebagai Foreign Key yang mengacu ke kolom ID pada tabel INVOICE (ketika invoice dihapus, maka tabel invoice_item juga dihapus)<\/strong><\/p>\n
ALTER TABLE INVOICE_ITEM\nADD CONSTRAINT INVOICE_ITEM_FK FOREIGN KEY (INVOICE_ID)\nREFERENCES INVOICE(ID) ON DELETE CASCADE;<\/pre>\n
6.\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Masukkan data di bawah ini ke tabel INVOICE dan INVOICE_ITEM (data pada kedua tabel tersebut merupakan satu kesatuan)<\/p>\n\n\n\n\n\n\n\n
KOLOM<\/td>\nNILAI<\/td>\n<\/tr>\n
ID<\/td>\n1<\/td>\n<\/tr>\n
DATE<\/td>\n22-APR-2010<\/td>\n<\/tr>\n
BILL_TO<\/td>\nPT Sekali Jalan<\/td>\n<\/tr>\n
TOTAL<\/td>\n1100000<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n\n\n\n\n\n
ID<\/td>\nPRODUCT_ID<\/td>\nDESCRIPTION<\/td>\nQUANTITY<\/td>\nUNIT_PRICE<\/td>\nLINE_TOTAL<\/td>\n<\/tr>\n
1<\/td>\nT001<\/td>\nKursi Kayu<\/td>\n6<\/td>\n100000<\/td>\n600000<\/td>\n<\/tr>\n
2<\/td>\nT002<\/td>\nMeja Kayu<\/td>\n2<\/td>\n250000<\/td>\n500000<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
INSERT INTO INVOICE VALUES(1,'22-APR-2010','PT Sekali Jalan',1100000);\nATAU\nINSERT INTO INVOICE VALUES(1,TO_DATE('22-04-2010','DD-MM-YYYY'),'PT Sekali Jalan',1100000);\nBEGIN\nINSERT INTO INVOICE_ITEM VALUES(1,1,'T001','Kursi Kayu',6,100000,600000);\nINSERT INTO INVOICE_ITEM VALUES(2,1, 'TOO2','Meja Kayu',2,250000,500000);\nEND;<\/pre>\n
7.\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Pada tabel INVOICE set kolom ID, DATE, BILL_TO menjadi tidak boleh NULL<\/strong><\/p>\n
\u00a0ALTER TABLE INVOICE MODIFY\n\u00a0 (\n\u00a0\u00a0\u00a0\u00a0 ID NUMBER NOT NULL,\n\u00a0\u00a0\u00a0\u00a0 \"DATE\" DATE NOT NULL,\n\u00a0\u00a0\u00a0\u00a0 BILL_TO VARCHAR2(500) NOT NULL\n\u00a0 );<\/pre>\n
8.\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Coba masukkan data di bawah ini ke tabel INVOICE<\/strong><\/p>\n\n\n\n\n\n\n\n
KOLOM<\/td>\nNILAI<\/td>\n<\/tr>\n
ID<\/td>\n2<\/td>\n<\/tr>\n
DATE<\/td>\n23-APR-2010<\/td>\n<\/tr>\n
BILL_TO<\/td>\n<NULL><\/td>\n<\/tr>\n
TOTAL<\/td>\n300000<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
INSERT INTO INVOICE VALUES(2,'23-APR-2010',NULL,300000);<\/pre>\n
9.\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Pada tabel INVOICE_ITEM set kolom ID, INVOICE_ID, PRODUCT _ID, QUANTITY menjadi tidak boleh NULL<\/strong><\/p>\n
ALTER TABLE INVOICE_ITEM MODIFY\n\u00a0\u00a0 (\n\u00a0\u00a0\u00a0\u00a0 ID NUMBER NOT NULL,\n\u00a0\u00a0\u00a0\u00a0 INVOICE_ID NUMBER NOT NULL,\n\u00a0\u00a0\u00a0\u00a0 PRODUCT_ID VARCHAR2(500) NOT NULL,\n\u00a0\u00a0\u00a0\u00a0 QUANTITY NUMBER NOT NULL\n\u00a0\u00a0 );<\/pre>\n
10.\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Pada tabel INVOICE_ITEM set kolom PRODUCT_ID menjadi unik untuk setiap INVOICE<\/strong><\/p>\n
ALTER TABLE INVOICE_ITEM ADD CONSTRAINT\nINVOICE_ITEM_UNIQ UNIQUE (PRODUCT_ID,INVOICE_ID);<\/pre>\n
11.\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Coba tambahkan data di bawah ini ke tabel INVOICE_ITEM sesuai dengan data INVOICE pada soal No. 6<\/strong><\/p>\n\n\n\n\n
ID<\/td>\nINVOICE_ID<\/td>\nPRODUCT_ID<\/td>\nDESCRIPTION<\/td>\nQUANTITY<\/td>\nUNIT_PRICE<\/td>\nLINE_TOTAL<\/td>\n<\/tr>\n
3<\/td>\n1<\/td>\nT002<\/td>\nMeja Kayu<\/td>\n1<\/td>\n250000<\/td>\n250000<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
INSERT INTO INVOICE_ITEM VALUES(3,1, 'TOO2','Meja Kayu',1,250000,250000);<\/pre>\n
12.\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Set kolom TOTAL pada tabel INVOICE, UNIT_PRICE dan LINE_TOTAL pada tabel INVOICE_ITEM agar hanya dapat menerima nilai positif<\/strong><\/p>\n
ALTER TABLE INVOICE ADD CONSTRAINT\nINVOICE_CHK CHECK ( TOTAL > 0);\nALTER TABLE INVOICE_ITEM ADD CONSTRAINT\nINVOICE_ITEM_CHK CHECK ( UNIT_PRICE > 0 AND LINE_TOTAL > 0);<\/pre>\n
13.\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Coba tambahkan data di bawah ini ke tabel INVOICE<\/strong><\/p>\n\n\n\n\n\n\n\n
KOLOM<\/td>\nNILAI<\/td>\n<\/tr>\n
ID<\/td>\n23<\/td>\n<\/tr>\n
DATE<\/td>\n20-APR-2010<\/td>\n<\/tr>\n
BILL_TO<\/td>\nPT Jalan Terus<\/td>\n<\/tr>\n
TOTAL<\/td>\n-200000<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
INSERT INTO INVOICE VALUES(3,'20-APR-2010','PT Jalan Terus ',-200000);<\/pre>\n
14.\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Hapus data invoice dengan ID=1, kemudian Lihat (SELECT) INVOICE_ITEM yang terkait<\/strong><\/p>\n
DELETE INVOICE WHERE ID=1;\nSELECT * FROM INVOICE_ITEM WHERE INVOICE_ID=1;<\/pre>\n
15.\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Buat tabel EMPLOYEE dengan data berikut:<\/p>\n\n\n\n\n\n\n\n
KOLOM<\/td>\nTIPE<\/td>\nPANJANG<\/td>\n<\/tr>\n
ID<\/td>\nNUMBER<\/td>\n<\/td>\n<\/tr>\n
NAME<\/td>\nVARCHAR2<\/td>\n500<\/td>\n<\/tr>\n
POSITION<\/td>\nVARCHAR2<\/td>\n500<\/td>\n<\/tr>\n
SALARY<\/td>\nNUMBER<\/td>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
CREATE TABLE EMPLOYEE\n\u00a0\u00a0 (\n\u00a0\u00a0\u00a0\u00a0 ID NUMBER,\n\u00a0\u00a0\u00a0\u00a0 NAME VARCHAR2(20),\n\u00a0\u00a0\u00a0\u00a0 POSITION VARCHAR2(20),\n\u00a0\u00a0\u00a0\u00a0 SALARY NUMBER\n\u00a0\u00a0 );<\/pre>\n
16.\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Masukkan lima EMPLOYEE sesuai dengan data berikut:<\/strong><\/p>\n\n\n\n\n\n\n\n\n
id<\/td>\nname<\/td>\nPosition<\/td>\nSalary<\/td>\n<\/tr>\n
1<\/td>\nAndi<\/td>\nManager<\/td>\n200000000<\/td>\n<\/tr>\n
2<\/td>\nBoni<\/td>\nTeller<\/td>\n100000000<\/td>\n<\/tr>\n
3<\/td>\nChairi<\/td>\nTeller<\/td>\n100000000<\/td>\n<\/tr>\n
4<\/td>\nDimas<\/td>\nCustomer Service<\/td>\n75000000<\/td>\n<\/tr>\n
5<\/td>\nEki<\/td>\nSecurity<\/td>\n60000000<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
BEGIN\nINSERT INTO EMPLOYEE VALUES(1,'Andi','Manager',200000000);\nINSERT INTO EMPLOYEE VALUES(2,'Boni','Teller',100000000);\nINSERT INTO EMPLOYEE VALUES(3,'Chairi','Teller', 100000000);\nINSERT INTO EMPLOYEE VALUES(4,'Dimas','Customer Service', 75000000);\nINSERT INTO EMPLOYEE VALUES(5,'Eki','Security',60000000);\nEND;<\/pre>\n
 <\/p>\n","protected":false},"excerpt":{"rendered":"
Biasanya setiap Kamis sore ba’da perkuliahan selesai saya sudah bergegas bertolak pulang menuju Lampung, nah khusus untuk minggu ini ada perubahan jadwal perkuliahan dengan adanya praktikum yang diadakan tiap jum’at mulai\u00a0 14 Oktober (hari ini),\u00a0 jadwal pulang terpaksa dipending hingga jum’at sore. Praktikum jum’at ini diselenggarakan dalam rangka mensupport materi perkuliahan Teknologi System Basis Data … Continue reading “Kelas Praktikum Basis Data MTI”<\/span><\/a><\/p>\n","protected":false},"author":25,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1348,7],"tags":[554,815],"class_list":["post-1367","post","type-post","status-publish","format-standard","hentry","category-kiat-sukses-menjadi-seorang-network-engineer-2","category-world-of-academic","tag-kelas-praktikum-basis-data-mti","tag-oracle"],"_links":{"self":[{"href":"https:\/\/dosen.unila.ac.id\/gigih\/wp-json\/wp\/v2\/posts\/1367"}],"collection":[{"href":"https:\/\/dosen.unila.ac.id\/gigih\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/dosen.unila.ac.id\/gigih\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/dosen.unila.ac.id\/gigih\/wp-json\/wp\/v2\/users\/25"}],"replies":[{"embeddable":true,"href":"https:\/\/dosen.unila.ac.id\/gigih\/wp-json\/wp\/v2\/comments?post=1367"}],"version-history":[{"count":0,"href":"https:\/\/dosen.unila.ac.id\/gigih\/wp-json\/wp\/v2\/posts\/1367\/revisions"}],"wp:attachment":[{"href":"https:\/\/dosen.unila.ac.id\/gigih\/wp-json\/wp\/v2\/media?parent=1367"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/dosen.unila.ac.id\/gigih\/wp-json\/wp\/v2\/categories?post=1367"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/dosen.unila.ac.id\/gigih\/wp-json\/wp\/v2\/tags?post=1367"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}